docs/database/POSTGRES_SETUP.md

# PostgreSQL Connection Infrastructure Setup

## Overview
This document describes the PostgreSQL connection infrastructure implemented for the SCM microservice sync service.

## Components Implemented

### 1. Dependencies
- **asyncpg 0.31.0**: Async PostgreSQL driver for Python
- Added to `requirements.txt`

### 2. Configuration (`app/core/config.py`)
Added PostgreSQL configuration with the following environment variables:
- `POSTGRES_HOST`: PostgreSQL server host (default: localhost)
- `POSTGRES_PORT`: PostgreSQL server port (default: 5432)
- `POSTGRES_DB`: Database name (default: cuatrolabs)
- `POSTGRES_USER`: Database user (default: postgres)
- `POSTGRES_PASSWORD`: Database password (default: empty)
- `POSTGRES_MIN_POOL_SIZE`: Minimum connections in pool (default: 5)
- `POSTGRES_MAX_POOL_SIZE`: Maximum connections in pool (default: 20)

### 3. Connection Pool Manager (`app/postgres.py`)
Implemented `PostgreSQLConnectionPool` singleton class with:

#### Features
- **Connection Pool Management**: Async connection pool with configurable min/max connections
- **Health Checks**: Automatic connection health verification before use
- **Automatic Reconnection**: Dead connections are automatically replaced
- **Graceful Shutdown**: Proper cleanup of all connections
- **Error Handling**: Comprehensive error logging and handling

#### Public API
```python
# Initialize connection pool (call on startup)
await connect_to_postgres()

# Get a connection from the pool
conn = await get_postgres_connection()

# Use the connection
result = await conn.fetch("SELECT * FROM table")

# Release connection back to pool
await release_postgres_connection(conn)

# Close all connections (call on shutdown)
await close_postgres_connection()

# Check if pool is initialized
if is_postgres_connected():
    # Pool is ready
```

### 4. Environment Configuration (`.env.example`)
Updated with PostgreSQL configuration template:
```bash
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=cuatrolabs
POSTGRES_USER=postgres
POSTGRES_PASSWORD=your_postgres_password
POSTGRES_MIN_POOL_SIZE=5
POSTGRES_MAX_POOL_SIZE=20
```

## Usage

### Startup Integration
To integrate with the application startup, add to `app/main.py`:

```python
from app.postgres import connect_to_postgres, close_postgres_connection

@app.on_event("startup")
async def startup_event():
    logger.info("Starting SCM Microservice")
    await connect_to_mongo()
    await connect_to_postgres()  # Add this line
    logger.info("SCM Microservice started successfully")

@app.on_event("shutdown")
async def shutdown_event():
    logger.info("Shutting down SCM Microservice")
    await close_mongo_connection()
    await close_postgres_connection()  # Add this line
    logger.info("SCM Microservice shut down successfully")
```

### Using Connections in Sync Handlers
```python
from app.postgres import get_postgres_connection, release_postgres_connection

async def sync_merchant(merchant_id: str):
    conn = await get_postgres_connection()
    try:
        # Perform database operations
        await conn.execute(
            "INSERT INTO trans.merchants_ref (merchant_id, ...) VALUES ($1, ...)",
            merchant_id, ...
        )
    finally:
        await release_postgres_connection(conn)
```

## Connection Pool Behavior

### Health Checks
- Every connection acquisition includes a health check (`SELECT 1`)
- Dead connections are automatically removed and replaced
- Ensures reliability under network issues

### Timeouts
- Command timeout: 30 seconds
- Connection timeout: 30 seconds
- Prevents hanging operations

### Pool Sizing
- Minimum connections maintained: 5 (configurable)
- Maximum connections allowed: 20 (configurable)
- Balances resource usage with performance

## Error Handling

### Connection Failures
- Logged with full context (host, port, database)
- Automatic retry through pool mechanism
- Graceful degradation

### Pool Exhaustion
- Waits for available connection
- Timeout prevents indefinite blocking
- Logged for monitoring

## Requirements Satisfied

This implementation satisfies the following requirements from the design document:

- ✅ **Requirement 5.1**: Connection pool initialization with configurable min/max connections
- ✅ **Requirement 5.2**: Connection acquisition from pool
- ✅ **Requirement 5.3**: Connection release back to pool
- ✅ **Requirement 5.4**: Connection health check and automatic reconnection
- ✅ **Requirement 5.5**: Graceful connection cleanup on shutdown
- ✅ **Requirement 11.1**: PostgreSQL configuration via environment variables
- ✅ **Requirement 11.4**: Connection pool size configuration

## Next Steps

1. **Database Setup**: Create PostgreSQL database and schema (trans schema)
2. **Integration**: Add connection initialization to main.py startup
3. **Sync Services**: Implement merchant, catalogue, and employee sync handlers
4. **Testing**: Test with actual PostgreSQL server
5. **Monitoring**: Add connection pool metrics

## Testing

The implementation has been verified for:
- ✅ Module imports successfully
- ✅ Configuration loads correctly
- ✅ Connection pool lifecycle (init, close)
- ✅ Error handling for uninitialized pool
- ✅ Type safety and defaults

Actual connection tests require a running PostgreSQL server.