Persistence

The application owns a RoomRepository port with Create, Get, and Save operations. SQLite and PostgreSQL adapters implement that contract.

Stored representation

Each room is serialized as JSON and stored as one row:

Column	SQLite	PostgreSQL
code	TEXT PRIMARY KEY	TEXT PRIMARY KEY
state	BLOB	JSONB
updated_at	TEXT	TIMESTAMPTZ

This design matches the room aggregate and keeps a complete vote mutation atomic within the single-process service. Revealed-round history is retained for the room lifetime, including final votes and pre-reveal revision counts; an ended room also stores its calculated summary. These are additive JSON fields, so existing rows need no schema migration. The design favors simplicity over cross-room reporting and fine-grained SQL queries.

Because history is unbounded, each revealed round increases the room row and the size of real-time snapshots. Long-running rooms should be monitored, and a separate normalized history store should be introduced if room lifetimes or reporting requirements grow substantially.

The schema is managed by Goose migrations embedded in both the server and migration binaries. Goose records applied versions in goose_db_version. SQLite and PostgreSQL have separate SQL files under backend/internal/database/migrations; matching filenames keep the two dialects at the same logical version.

The initial migration uses CREATE TABLE IF NOT EXISTS. On the first upgraded start it therefore adopts an existing unversioned database without replacing its rooms table and records migration version 1.

Applying migrations

The server applies pending migrations before accepting traffic by default. This preserves the simple single-process deployment:

cd backend
go run ./cmd/server

For production, run migrations as a separate deployment step and disable automatic migration on application replicas:

cd backend
DATABASE_AUTO_MIGRATE=false go run ./cmd/migrate up
DATABASE_AUTO_MIGRATE=false go run ./cmd/server

The migration command accepts only safe, forward-only operations:

go run ./cmd/migrate up
go run ./cmd/migrate status
go run ./cmd/migrate version

The same commands are available as just migrate, just migrate-status, and just migrate-version. All commands use DATABASE_DRIVER, DATABASE_PATH, and DATABASE_URL.

The production image includes /app/migrate. To migrate the Compose SQLite volume without starting the server:

DATABASE_AUTO_MIGRATE=false just worktree-migrate

For the optional Compose PostgreSQL profile, start the database first and pass the same driver selection to the one-shot command:

DATABASE_DRIVER=postgres just worktree-postgres-up
DATABASE_DRIVER=postgres DATABASE_AUTO_MIGRATE=false \
  just worktree-migrate

Adding a schema change

1. Choose the next version and descriptive filename, for example 00002_create_sessions.sql.
2. Add a file with that exact name to both migrations/sqlite and migrations/postgres.
3. Put forward SQL after a -- +goose Up annotation. Do not add a Down section.
4. Account for dialect differences explicitly. SQLite may require creating a replacement table, copying data, and renaming it for changes that PostgreSQL can perform with ALTER TABLE.
5. Test upgrades from both an empty database and the previous released schema.

Migration files are immutable after release. Fix an incorrect released migration with a new migration rather than editing the old file.

Rollback policy

Migrations are forward-only. Back up the database before deploying destructive changes, correct schema problems with a new migration, and restore the backup when a destructive deployment must be rolled back. The migration command intentionally does not expose Goose's down operations.

SQLite

SQLite is selected by default:

cd backend
DATABASE_PATH=data/data.db go run ./cmd/server

The adapter creates the parent directory and limits the SQL connection pool to one connection. DATABASE_PATH=:memory: uses a named, shared-cache in-memory database and is suitable for tests or disposable demos.

The container stores /data/data.db in the backlog-data volume.

PostgreSQL

Select PostgreSQL with:

DATABASE_DRIVER=postgres \
DATABASE_URL='postgres://poker:poker@localhost:5432/poker?sslmode=disable' \
go run ./cmd/server

The Compose file includes PostgreSQL under an optional profile:

COMPOSE_PROFILES=postgres DATABASE_DRIVER=postgres just worktree-up

Concurrency and durability

The application service currently serializes mutations with one process-wide mutex. Repository Save operations do not compare expected versions, so running multiple backend replicas can cause lost updates. Before horizontal scaling, add optimistic concurrency to the repository port and move event fan-out to a distributed adapter.

Back up the SQLite volume/file for durable deployments. Switching drivers does not automatically migrate existing room data between SQLite and PostgreSQL.