Add PostgreSQL as a supported sink backend #1497

Summary

Add PostgreSQL as a first-class sink backend alongside the existing fs and mongo backends.

The goal is additive support, not a backend replacement:

• keep fs and mongo working
• preserve the current API/UI contract
• support repos, users, pushes, and persistent UI sessions when sink.type === "postgres"

Proposed Scope

Implement:

• postgres as a new sink[] type in config/schema/generated types
• Postgres connection support via connectionString
• optional env fallback via GIT_PROXY_POSTGRES_CONNECTION_STRING
• a src/db/postgres adapter implementing the existing Sink interface
• Postgres-backed session storage for express-session
• schema bootstrap on startup for required tables/indexes
• docs and tests for the new backend

Non-goals for v1:

• no migration/import from existing fs or mongo data
• no dual-write/dual-read transition mode
• no broader auth or UI refactors unrelated to Postgres

Implementation Shape

Use a thin compatibility model to minimize churn:

• users table
• repos table
• pushes table
• session table for express-session

Keep external identifiers opaque strings so the HTTP/UI contract does not change.

Use a lightweight Postgres integration approach:

• pg for data access
• connect-pg-simple (or equivalent) for session persistence
• startup bootstrap for schema creation
• no ORM in the initial implementation

Must-Fix Requirements

Any Postgres implementation should explicitly cover these points:

• Ensure Postgres session persistence is actually active during service startup.
  The session store must not silently fall back to MemoryStore before the pool is initialized.

• Preserve current rejection semantics.
  reject() must store the rejection payload in the same field/shape used by existing backends.

• Keep user identity handling internally consistent.
  If _id remains part of backend behavior, the Postgres schema and adapter must support it correctly.
  Otherwise, the adapter contract should be normalized intentionally.

• Preserve array shape for repo permissions.
  Removing the last canPush or canAuthorise user must leave [], not null.

• Preserve current push listing behavior.
  Postgres getPushes() should return pushes in descending timestamp order, matching current backends.

• Add tests for parity with existing backends.
  At minimum:

  • config selection/env fallback
  • users CRUD/update behavior
  • repos CRUD and permission mutation behavior
  • pushes CRUD/approve/reject/cancel behavior
  • session-store initialization behavior

• Add Postgres to the integration test matrix as a supported backend.
  Initial CI coverage can target a single PostgreSQL version such as postgres:16; a broader version matrix can follow later.

• Add docs/examples for configuring PostgreSQL.

Open Questions

• Do we want startup bootstrap SQL only, or a formal migration mechanism?
• Should AWS RDS IAM auth be part of the first upstream Postgres scope, or follow-up work?
• Should repo permissions remain JSONB in v1, or be normalized relationally?
• Should v1 support only connectionString, or also document/support split PG env vars (HOST, PORT, DATABASE, USER, PASSWORD)?
• Do we require live Postgres integration tests in CI for the first PR, or is a single Postgres integration lane sufficient initially?