Implement PostgreSQL connection pooling with PgBouncer configuration and health checks

[Petah1]

Difficulty: Hard

Summary

The src/db/postgres-driver.ts uses the pg library with a basic connection pool but there is no PgBouncer configuration, no pool exhaustion handling, no connection health checks (dead connection detection), and no pool metrics. Under high load, connection exhaustion causes silent request queuing with no observability.

Task

Harden the PostgreSQL driver with proper pool sizing, connection health checks, pool exhaustion timeouts, PgBouncer compatibility (statement-mode), and Prometheus metrics for pool utilization.

Detailed Description

1. Pool sizing: Expose DB_POOL_MIN (default 2) and DB_POOL_MAX (default 10) env vars. Pass these to the pg.Pool constructor.
2. Connection health check: Configure idleTimeoutMillis: 30000 and connectionTimeoutMillis: 5000. On pool error events, log and increment a db_pool_error_total counter.
3. Pool metrics: Expose db_pool_active_connections (gauge) and db_pool_idle_connections (gauge) via the Prometheus endpoint.
4. PgBouncer compatibility: Set options: '--client_encoding=UTF8' and avoid SET commands in transaction scope to support PgBouncer statement mode.
5. Connection health probe: Add a poolHealth() function that checks out a connection, runs SELECT 1, and releases it. Use this in GET /ready instead of a raw query.
6. SSL: Respect DATABASE_SSL env var (true, no-verify, false) — test in tests/db/postgresDriverSsl.test.ts.

Acceptance Criteria

• Pool max is respected — requesting more than DB_POOL_MAX connections queues (not crashes).
• Pool error events are counted in Prometheus.
• DB_POOL_MIN and DB_POOL_MAX env vars are documented in README.
• SSL modes work correctly per tests/db/postgresDriverSsl.test.ts.
• PgBouncer-compatible SQL is verified in a Docker Compose test setup.
• Tests in tests/db/postgresDriverClose.test.ts all pass.

Notes

• See src/db/postgres-driver.ts for the current pool implementation.
• docs/postgres-migration.md has configuration guidance.
• Pool metrics should reuse the existing Prometheus registry from src/middleware/metrics.ts.

[Johnsource-hub]

Hi, I will like to work on this issue. why? Because I am well versed in the stack that will be use to solve this issue

[grantfox-oss]

🦊 GrantFox — @Johnsource-hub has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #794)
2. Your PR will be reviewed by the scout-off maintainers

Good luck! Track your progress on GrantFox.