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

# 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:

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:

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

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.