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

Context

The application currently uses SQLite (mattn/go-sqlite3) with:

• sqlc for type-safe query generation (configured for sqlite engine)
• goose for migrations (3 migration files)
• Custom retry logic for SQLite busy/locked errors
• Connection pool configuration tuned for SQLite

The codebase is in active development with no production deployments, so we can replace the database layer cleanly without migration tooling.

Goals / Non-Goals

Goals:

• Replace SQLite with PostgreSQL using the pgx driver
• Maintain the same data model (users, sites, payments)
• Keep using sqlc for query generation and goose for migrations
• Provide a development-friendly PostgreSQL setup

Non-Goals:

• Data migration from existing SQLite databases
• Supporting both databases simultaneously
• Changing the application's data model or schema

Decisions

1. Use pgx/v5 with stdlib compatibility mode

Use github.com/jackc/pgx/v5/stdlib to register the driver with database/sql. This allows us to keep the existing code structure that uses *sql.DB while gaining access to pgx features when needed.

Alternatives considered:

• Pure pgx without stdlib: Would require rewriting all database code to use pgx native types
• lib/pq: Older driver, less actively maintained, fewer features

2. Fresh migrations instead of conversion

Delete all existing SQLite migrations and create a single fresh PostgreSQL migration. The schema is simple (3 tables) and there's no production data.

Alternatives considered:

• Convert migrations line-by-line: More work for no benefit since there's no data to preserve

3. PostgreSQL connection string format

Use standard PostgreSQL connection string: postgres://user:password@host:port/dbname?sslmode=disable

Configuration will accept this via the existing --db-dsn flag, with environment variable support for sensitive values.

4. Remove SQLite-specific retry logic

PostgreSQL handles concurrency differently (MVCC) and doesn't have the same busy/locked errors. Remove isRetryableError and simplify connection logic.

Alternatives considered:

• Add PostgreSQL-specific retry logic: Not needed for typical transient errors; connection pool handles reconnection

5. Use SERIAL for auto-increment primary keys

PostgreSQL uses SERIAL (or BIGSERIAL) instead of SQLite's INTEGER PRIMARY KEY AUTOINCREMENT.

6. Triggers for updated_at

Keep the same pattern of using database triggers for updated_at columns, but use PostgreSQL trigger syntax.

Risks / Trade-offs

Development environment complexity → Developers now need PostgreSQL running locally. Mitigate with docker-compose setup and clear documentation.

Connection string with credentials → Sensitive data in config. Mitigate by supporting environment variable interpolation and documenting secure practices.

sqlc regeneration → All generated code will change. Low risk since it's a clean replacement and tests will verify correctness.