Persistence

The application owns a RoomRepository port implemented by the dialect-aware eventstore.Repository. It exposes bounded current-state reads for normal commands and an explicit consistent GetWithHistory read for commands such as session ending. The concrete repository additionally exposes event-stream and projection-maintenance operations to administrative commands.

Stored representation

Room events are the durable source of truth. The database also maintains current-state and revealed-round projections in the same transaction as every append:

| Table | Purpose | | --- | --- | --- | | room_events | Append-only, ordered domain facts keyed by room and sequence | | room_current | Bounded JSON current-state projection used for command validation and snapshots | | room_rounds | One reporting row for every revealed round | | room_round_votes | Queryable final votes and revision counts for revealed rounds |

Each stream sequence is also the public room version. Event payloads and the current projection use BLOB with SQLite and JSONB with PostgreSQL. Event envelopes include an event ID, type, schema version, and occurrence time. The initial event types are RoomCreated, ParticipantJoined, VoteSubmitted, VotesRevealed, RoundReset, TopicChanged, and SessionEnded.

The current projection omits historical rounds, so its size is bounded by current room and participant state. The repository hydrates round history from normalized projections when the domain needs to calculate a final summary. Public real-time snapshots also omit history; reporting can query the normalized tables without parsing versioned event payloads.

Migration 2 renames the original rooms table to room_current, adds a scalar version for compare-and-swap updates, creates the event and reporting tables, and backfills existing data. Each legacy room receives one RoomImported baseline event at its existing version. Its prior individual actions cannot be reconstructed, so detailed audit history starts at the migration; existing revealed rounds and votes are retained in reporting projections.

Events are immutable during a room's lifetime. Evolve payloads by adding a new event schema version and a decoder/upcaster rather than rewriting stored events. A replay that encounters an unsupported version fails instead of silently constructing incorrect state.

Room retention

The server permanently deletes rooms when either retention deadline is reached:

Environment variable	Default	Deadline
ROOM_IDLE_TTL	6h	Time since the last persisted event
ROOM_ENDED_TTL	1h	Time since the session ended

Both settings accept positive Go durations, such as 30m or 8h. Invalid, zero, and negative durations prevent server startup. The earlier deadline wins, even when the ended-session retention is configured longer than the idle timeout. Reads, heartbeat traffic, and open connections do not extend retention; new persisted events reset the idle deadline.

Cleanup runs at startup and every minute, applying to existing and new rooms. Deletion happens on the first successful sweep after expiry, normally within one minute. Failures are logged and retried on the next sweep. The room's event stream, current state, rounds, and votes are deleted atomically through foreign-key cascades. Deleted rooms cannot be replayed or rebuilt, and subsequent requests return the existing not-found response. Connected pages receive no separate expiry notification.

The OpenTelemetry counter backlog_holdem.room.deleted counts committed room deletions with a reason label of idle_timeout or session_ended. Each room is counted once under its earlier deadline; equal deadlines use session_ended.

Projection rebuilding

The current and reporting projections are disposable. Stop application writers and rebuild every room from its event stream with cd backend && go run ./cmd/rebuild, or pass one room code as the final argument. The command refuses to run until all schema migrations are applied.

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 00003_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

Every mutation loads a room version, validates the command, and appends its events while updating projections with that expected version in one transaction. A stale writer receives ErrVersionConflict; the application reloads and revalidates a bounded number of times. This prevents silent lost updates without a process-wide room mutex.

SQLite remains the single-process deployment mode and uses the in-memory event hub. PostgreSQL appends also call transactional pg_notify. Every backend replica listens on room_events, reloads the authoritative current projection after commit, and publishes the sanitized snapshot to its local SSE/WebSocket subscribers. PostgreSQL notifications are invalidations rather than the source of truth: reconnecting clients always load a fresh snapshot, so durable replay remains in room_events.

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