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

1. Dependencies

• 1.1 Remove github.com/mattn/go-sqlite3 from go.mod
• 1.2 Add github.com/jackc/pgx/v5 to go.mod
• 1.3 Run go mod tidy to clean up dependencies

2. Database Connection

• 2.1 Update internal/db/database.go to import pgx/v5/stdlib and register the driver
• 2.2 Remove SQLite-specific isRetryableError and retryOperation functions
• 2.3 Update openAndConfigureDB to use pgx driver name and remove directory creation logic
• 2.4 Update DefaultDBConfig to use a PostgreSQL DSN default (e.g., postgres://localhost:5432/member_console)
• 2.5 Simplify Connect and ConnectAndMigrate to remove retry logic wrapper

3. Migrations

• 3.1 Delete existing SQLite migration files in internal/db/migrations/
• 3.2 Create fresh PostgreSQL migration 00001_init.sql with users, sites, payments tables
• 3.3 Use SERIAL for primary keys, PostgreSQL timestamp types, and proper trigger syntax
• 3.4 Update internal/db/migrations.go to set goose dialect to "postgres"

4. sqlc Configuration

• 4.1 Update internal/db/sqlc.yaml to use engine: "postgresql"
• 4.2 Update SQL queries in internal/db/queries/ for PostgreSQL syntax (e.g., $1 instead of ?)
• 4.3 Run sqlc generate to regenerate Go code

5. Development Environment

• 5.1 Add PostgreSQL service to docker-compose.yml (if exists) or create one
• 5.2 Update --db-dsn flag default and help text in cmd/start.go

6. Verification

• 6.1 Run the application against a local PostgreSQL instance
• 6.2 Verify migrations apply successfully
• 6.3 Test basic CRUD operations for users, sites, and payments