docs/database/POSTGRES_LOGGING_GUIDE.md

# PostgreSQL Connection Logging Enhancement

## Changes Made

Enhanced PostgreSQL connection logging in `app/sql.py` to provide detailed diagnostic information during application startup and connection attempts.

## New Logging Features

### 1. Connection Configuration Logging

When the application starts, it now logs:

```
[POSTGRES] Connection String: postgresql+asyncpg://trans_owner:***@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
[POSTGRES] Host: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[POSTGRES] Port: 5432
[POSTGRES] Database: cuatrolabs
[POSTGRES] SSL Mode: require
```

**Key Features:**
- ✅ Password is automatically masked (`***`) for security
- ✅ Shows parsed connection details (host, port, database)
- ✅ Displays SSL mode configuration
- ✅ Logs to both logger and console (stdout) for visibility in deployment logs

### 2. Connection Attempt Logging

During connection attempts, detailed progress is logged:

```
[POSTGRES] Attempting to connect (max retries: 30)...
[POSTGRES] ✅ Connection successful after 1 attempt(s)
```

**On Failure:**
```
[POSTGRES] ❌ Connection attempt 1/30 failed
[POSTGRES] Error: TimeoutError: Connection timeout
[POSTGRES] Retrying in 0.80s...
```

### 3. Final Error Reporting

If all connection attempts fail, comprehensive error information is provided:

```
[POSTGRES] ❌ FATAL: Failed to connect after 30 attempts
[POSTGRES] Last error: TimeoutError: Connection timeout after 60s
[POSTGRES] Please check:
[POSTGRES]   1. Database host is reachable: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[POSTGRES]   2. Database credentials are correct
[POSTGRES]   3. SSL mode is appropriate: require
[POSTGRES]   4. Firewall allows connections to port 5432
```

## Security Considerations

### Password Masking

The logging implementation includes two helper functions to ensure passwords are never exposed:

1. **`mask_connection_string(uri: str)`**
   - Parses the connection URI
   - Replaces password with `***`
   - Handles various URI formats safely

2. **`parse_connection_details(uri: str)`**
   - Extracts host, port, and database name
   - Does not expose credentials
   - Provides structured data for logging

### Example Masking

**Original URI:**
```
postgresql+asyncpg://trans_owner:BookMyService7@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
```

**Logged URI:**
```
postgresql+asyncpg://trans_owner:***@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
```

## Deployment Benefits

### 1. Easier Troubleshooting

When deployment fails with connection errors, you can now immediately see:
- Which database host is being used
- What port is being attempted
- SSL configuration
- Exact error messages
- Number of retry attempts

### 2. Configuration Verification

The logs confirm:
- Whether `POSTGRES_URI` was provided or built from components
- All connection parameters being used
- SSL/TLS settings

### 3. Error Diagnosis

Detailed error logging helps identify:
- Network connectivity issues
- Authentication problems
- SSL/TLS configuration errors
- Timeout issues
- DNS resolution failures

## Usage in Deployment

### Viewing Logs

In your deployment platform, you'll now see clear connection information:

```
INFO:     Waiting for application startup.
[CONFIG] Using provided POSTGRES_URI
[POSTGRES] Connection String: postgresql+asyncpg://trans_owner:***@ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech:5432/cuatrolabs
[POSTGRES] Host: ep-sweet-surf-a1qeduoy.ap-southeast-1.aws.neon.tech
[POSTGRES] Port: 5432
[POSTGRES] Database: cuatrolabs
[POSTGRES] SSL Mode: require
[POSTGRES] Attempting to connect (max retries: 30)...
[POSTGRES] ✅ Connection successful after 1 attempt(s)
INFO:     Application startup complete.
```

### Debugging Connection Failures

If you see repeated connection failures:

1. **Check the logged host** - Ensure it matches your database
2. **Verify SSL mode** - Some databases require specific SSL settings
3. **Review error messages** - Look for authentication, timeout, or network errors
4. **Check retry count** - If it's hitting max retries, there's a persistent issue

## Testing

Run the diagnostic script to test logging locally:

```bash
source venv/bin/activate
python diagnose_postgres.py
```

This will show you exactly what will be logged in your deployment environment.

## Configuration Reference

The logging respects these environment variables:

| Variable | Purpose | Default |
|----------|---------|---------|
| `POSTGRES_URI` | Full connection string | Built from components |
| `POSTGRES_CONNECT_MAX_RETRIES` | Max connection attempts | 30 |
| `POSTGRES_CONNECT_INITIAL_DELAY_MS` | Initial retry delay | 800ms |
| `POSTGRES_CONNECT_BACKOFF_MULTIPLIER` | Delay increase factor | 1.8 |
| `POSTGRES_SSL_MODE` | SSL/TLS mode | require |

## Next Steps

1. **Deploy the changes** to your environment
2. **Monitor startup logs** to verify connection details
3. **Add `POSTGRES_URI`** to your deployment environment variables
4. **Verify successful connection** in logs

The enhanced logging will make it much easier to diagnose and fix any connection issues in your deployment.