member-console/openspec/changes/migrate-sqlite-to-postgres/specs/postgres-database/spec.md

ADDED Requirements

Requirement: PostgreSQL connection via pgx driver

The system SHALL connect to PostgreSQL using the pgx/v5 driver with stdlib compatibility mode (pgx/v5/stdlib).

Scenario: Successful connection

• WHEN the application starts with a valid PostgreSQL DSN
• THEN a database connection pool is established and ready for queries

Scenario: Connection failure

• WHEN the application starts with an invalid or unreachable PostgreSQL DSN
• THEN the application fails to start with a descriptive error message

Requirement: PostgreSQL connection string configuration

The system SHALL accept a PostgreSQL connection string via the --db-dsn flag in the format postgres://user:password@host:port/dbname?sslmode=disable.

Scenario: DSN from command line flag

• WHEN the application is started with --db-dsn "postgres://user:pass@localhost:5432/mydb"
• THEN the application connects to the specified PostgreSQL database

Scenario: DSN with SSL mode

• WHEN the DSN includes ?sslmode=require
• THEN the connection uses SSL/TLS encryption

Requirement: Connection pool configuration

The system SHALL configure the connection pool with sensible defaults for PostgreSQL (max open connections, max idle connections, connection lifetime).

Scenario: Default pool settings applied

• WHEN the application connects to PostgreSQL
• THEN the connection pool is configured with max open connections, max idle connections, and connection lifetime limits

Requirement: Database migrations via goose

The system SHALL use goose to manage PostgreSQL database migrations with embedded migration files.

Scenario: Migrations run on startup

• WHEN the application starts and connects to PostgreSQL
• THEN pending migrations are automatically applied

Scenario: Migration CLI commands

• WHEN the user runs migrate up, migrate down, or migrate status
• THEN the corresponding goose operation is executed against the PostgreSQL database

Requirement: sqlc code generation for PostgreSQL

The system SHALL use sqlc configured with engine: "postgresql" to generate type-safe Go code from SQL queries.

Scenario: Query generation

• WHEN sqlc generate is run
• THEN Go code is generated using PostgreSQL syntax and types

Requirement: Schema with users, sites, and payments tables

The system SHALL maintain the existing data model with users, sites, and payments tables using PostgreSQL data types.

Scenario: Tables created by migration

• WHEN migrations are applied to a fresh database
• THEN the users, sites, and payments tables exist with appropriate columns, constraints, and indexes

Scenario: Foreign key constraints enforced

• WHEN a site or payment references a non-existent user
• THEN the database rejects the operation with a foreign key violation error

Scenario: Cascading deletes

• WHEN a user is deleted
• THEN their associated sites and payments are automatically deleted