Document auto-reconnection behavior in ensureConnection()

[sgaunet]

Problem

The ensureConnection() method in internal/app/app.go (lines 307-325) implements automatic reconnection logic when database pings fail, but this behavior is not documented anywhere.

Current implementation:

func (a *App) ensureConnection() error {
    if a.client == nil {
        return a.tryConnect()
    }

    // Ping the database to check if connection is still alive
    if err := a.client.Ping(); err != nil {
        logger.Log().Warn().Err(err).Msg("Database connection lost, attempting to reconnect")
        
        // Try to reconnect
        if err := a.tryConnect(); err != nil {
            return fmt.Errorf("failed to reconnect: %w", err)
        }
        logger.Log().Info().Msg("Successfully reconnected to database")
    }

    return nil
}

Issues

1. Undocumented behavior: Users don't know automatic reconnection happens
2. No visibility: Applications using this MCP server won't expect reconnections
3. Reconnection semantics unclear: What happens to in-flight operations?
4. Configuration missing: Can't disable auto-reconnect if desired
5. Retry logic not specified: How many retries? What backoff strategy?

User Impact

Scenario: User's database server restarts or connection is dropped.

Current behavior (undocumented):

• Next operation detects connection loss via ping
• Automatically attempts reconnection
• If successful, operation proceeds
• If failed, operation fails with error

Users might expect:

• Explicit reconnection required
• Connection pooling to handle this automatically
• Configuration of retry behavior

Proposed Solution

1. Document the Behavior

Add documentation in multiple places:

In Code (GoDoc):

// ensureConnection checks if the database connection is alive and attempts
// automatic reconnection if the connection has been lost.
//
// This method is called before every database operation to provide transparent
// connection recovery. If the connection ping fails, it will attempt to
// reconnect once using the original connection parameters.
//
// Reconnection behavior:
//   - Single reconnection attempt (no retries)
//   - Uses original connection string from environment
//   - Logs reconnection attempts and results
//   - Returns error if reconnection fails
//
// Users should be aware that operations may experience slight delays during
// reconnection. For long-running operations, consider implementing application-level
// retry logic.
func (a *App) ensureConnection() error {
    // ... implementation ...
}

In README.md:

## Connection Management

The PostgreSQL MCP server automatically manages database connections:

- **Connection pooling**: Reuses connections for efficiency
- **Health checks**: Pings database before each operation
- **Auto-reconnection**: Automatically reconnects if connection is lost
- **Single retry**: One reconnection attempt per operation

### Auto-Reconnection Behavior

If the database connection is lost (e.g., database restart, network issue):

1. The server detects connection loss on the next operation
2. Logs a warning: "Database connection lost, attempting to reconnect"
3. Attempts to reconnect using the original connection parameters
4. On success, proceeds with the operation
5. On failure, returns an error to the client

**Note:** Only one reconnection attempt is made per operation. If you need
more robust retry logic, implement it at the application level.

In CLAUDE.md:

Update the architecture section to mention:

**Connection Management**
- Auto-reconnection in `ensureConnection()` for connection recovery
- Health checks via `Ping()` before operations

2. Add Configuration Options

Consider adding configuration for reconnection behavior:

type ConnectionConfig struct {
    AutoReconnect      bool          // Enable/disable auto-reconnection
    MaxRetries         int           // Maximum reconnection attempts
    RetryBackoff       time.Duration // Delay between retries
    PingBeforeOperation bool         // Ping before each operation
}

3. Add Metrics/Observability

Log reconnection events for monitoring:

logger.Log().Info().
    Str("event", "connection_recovered").
    Dur("downtime", time.Since(connectionLostTime)).
    Msg("Successfully reconnected to database")

Impact

• Severity: LOW
• Type: Documentation
• Location: internal/app/app.go:307-325
• Benefits: Users understand connection behavior, better troubleshooting

Checklist

• Add comprehensive GoDoc comments to ensureConnection()
• Document auto-reconnection in README.md
• Update CLAUDE.md with connection management details
• Add logging for reconnection events with context
• Consider adding configuration options for reconnection behavior
• Add metrics/observability for connection health
• Document what happens to in-flight operations during reconnection
• Add FAQ section about connection handling