feat: configurable state database backend (SQLite or PostgreSQL)

[raykao]

Problem

The bridge currently uses SQLite (state.db) for all operational state via better-sqlite3 (synchronous). This works well for single-host deployments, but requires a bind mount for persistence when running in Docker. In the standard Docker Compose stack (Mattermost + PostgreSQL + bridge), a PostgreSQL instance is already running -- pointing the bridge at that service would eliminate the bind mount for state entirely.

Implementation: Kysely

The right abstraction is Kysely -- a TypeScript-native SQL query builder with dialect plugins for both SQLite (better-sqlite3) and PostgreSQL (pg). It presents a unified async interface over both backends.

Dependencies to add

• kysely -- query builder
• pg + @types/pg -- PostgreSQL client (optional dep, only loaded when type=postgres)

Files to create

• src/state/db.ts -- initializes Kysely from config, exports getDb()
• src/state/schema.ts -- TypeScript types for all 9 tables (required by Kysely generic)

Files to update

• src/state/store.ts -- replace all better-sqlite3 calls with Kysely queries; make all 30+ exported functions async
• src/config.ts -- add optional database config section
• src/core/session-manager.ts -- await all store calls (largest file)
• src/core/command-handler.ts -- await store calls
• src/core/inter-agent.ts -- await store calls
• src/core/onboarding.ts -- await store calls
• src/core/workspace-manager.ts -- await store calls
• src/core/scheduler.ts -- await store calls
• src/index.ts -- await store calls

SQL dialect differences to handle

SQLite	PostgreSQL
INTEGER PRIMARY KEY AUTOINCREMENT	BIGSERIAL PRIMARY KEY
datetime('now')	NOW()
INTEGER for booleans (0/1)	BOOLEAN
PRAGMA journal_mode = WAL	Not applicable
PRAGMA foreign_keys = ON	Default behavior
migrateChannelPrefsNullable migration	Not needed (clean schema)
Try/catch ALTER TABLE for missing cols	Not needed (clean schema)

Configuration (fully backward compatible)

config.json (optional -- defaults to SQLite):

{
  "database": {
    "type": "postgres",
    "url": "${DATABASE_URL}"
  }
}

Environment variables (override config file):

DATABASE_TYPE=postgres
DATABASE_URL=postgresql://mmuser:password@postgres:5432/copilot_bridge

Default (no config): SQLite at ~/.copilot-bridge/state.db -- no behavior change for existing deployments.

Docker Compose integration

When DATABASE_TYPE=postgres, the bridge uses the existing postgres service in the Compose stack. No bind mount needed for state:

copilot-bridge:
  environment:
    - DATABASE_TYPE=postgres
    - DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/copilot_bridge

Acceptance Criteria

• SQLite default behavior unchanged (no config = SQLite at existing path)
• PostgreSQL backend works with DATABASE_TYPE=postgres + DATABASE_URL
• All store functions are async (await-safe throughout codebase)
• Schema created automatically on first run for both backends
• Existing SQLite migrations preserved and applied on SQLite only
• Docker Compose example updated with PostgreSQL config option
• Deployment docs updated

[ChrisRomp]

@raykao what do you think of #176?

[ChrisRomp]

Pluggable storage implemented in v0.13.0.