member-console/openspec/changes/migrate-sqlite-to-postgres/proposal.md

Why

SQLite lacks features needed for this application (e.g., advanced data types, better JSON support, full-text search, array types). PostgreSQL provides these capabilities out of the box.

What Changes

• BREAKING: Replace SQLite database with PostgreSQL
• Replace mattn/go-sqlite3 driver with pgx
• Update sqlc configuration from engine: "sqlite" to engine: "postgresql"
• Delete existing migrations and create fresh PostgreSQL migrations (no production data to preserve)
• Update database connection configuration for PostgreSQL DSN format
• Remove SQLite-specific retry logic (busy/locked errors)
• Regenerate all sqlc-generated code for PostgreSQL

Capabilities

New Capabilities

• postgres-database: PostgreSQL database connection, configuration, and query execution

Modified Capabilities

None (no existing specs)

Impact

• Database layer (internal/db/): Rewrite connection setup, fresh migrations, regenerated query code
• Configuration: New environment variables/flags for PostgreSQL connection (host, port, user, password, dbname, sslmode)
• Dependencies: Remove github.com/mattn/go-sqlite3, add github.com/jackc/pgx/v5
• Deployment: Requires PostgreSQL server
• Docker/CI: Update to include PostgreSQL for development and testing