feat: PostgreSQL StateStore backend package

[raykao]

Background

The pluggable StateStore interface shipped in v0.13.0 (#176, PR #179) provides the architecture for swappable database backends. The async store migration (PR #177) made all 34 store functions await-safe. The original feature request for PostgreSQL support (#167) is now resolved at the interface level.

This issue tracks the remaining concrete work: building an actual PostgreSQL backend that implements StateStore, so containerized deployments can use the existing Compose PostgreSQL service instead of a state.db bind mount.

Scope

A standalone npm package (e.g. copilot-bridge-postgres-store) that:

1. Implements the StateStore interface (34 methods + initialize, close, ping, withTransaction) using PostgreSQL
2. Handles schema creation on first run via initialize() - maps the 9 SQLite tables to PostgreSQL equivalents
3. Handles dialect differences:
   • INTEGER PRIMARY KEY AUTOINCREMENT -> BIGSERIAL PRIMARY KEY
   • datetime('now') -> NOW()
   • INTEGER booleans (0/1) -> BOOLEAN
   • SQLite PRAGMAs (WAL, foreign_keys) -> not applicable
   • SQLite migrations (column adds via try/catch ALTER TABLE) -> clean schema, not needed
4. Loads via the existing plugin mechanism:

   {
     "database": {
       "module": "copilot-bridge-postgres-store",
       "options": { "url": "${DATABASE_URL}" }
     }
   }

Implementation options

Approach	Pros	Cons
Kysely + pg	Type-safe query builder, dialect plugins for both SQLite and PG, strong TypeScript integration	Extra dependency, learning curve
Raw pg (node-postgres)	Minimal dependencies, full control, well-understood	Manual query building, more boilerplate
Drizzle ORM	Good TypeScript support, schema-as-code	Heavier abstraction than needed for 34 methods

Docker Compose integration

When complete, the bridge Compose stack gains:

copilot-bridge:
  environment:
    - DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/copilot_bridge
  # No state.db bind mount needed

Deliverables

• PostgreSQL StateStore implementation with full method coverage
• Schema auto-creation on initialize() for all 9 tables
• withTransaction() using PG BEGIN/COMMIT on a dedicated connection
• ping() with connection health check
• Connection pooling configuration
• Docker Compose example updated with PG backend config
• README with setup instructions and configuration reference
• Tests against a real PostgreSQL instance (e.g. via Docker in CI)

Context

• Predecessor issue: feat: configurable state database backend (SQLite or PostgreSQL) #167 (original Kysely-based proposal, closed after v0.13.0 shipped the interface layer)
• Interface definition: PR Pluggable StateStore interface (#176) #179
• Async migration: PR refactor: make all store functions async #177
• StateStore contract: src/state/types.ts
• Plugin loading: src/index.ts (dynamic import with 8-method validation)
• Reference implementation: src/state/sqlite-store.ts (SqliteStateStore)