Database and Migrations

Supported databases

Database	When used	Notes
PostgreSQL 16	DATABASE_URL or POSTGRES_* configured	Production; primary target
SQLite	No PostgreSQL configuration	Local development and tests

Both drivers implement the same database interface (src/db.js), so routes are driver-agnostic. GET /healthz reports "database": "postgres" or "sqlite".

PostgreSQL schema

users

Column	Type	Notes
id	TEXT PK	UUID
username	TEXT	Unique, case-insensitive
email	TEXT	Optional, unique case-insensitive when present
email_verified	BOOLEAN	Default false
email_token_hash	TEXT	SHA-256 of the confirmation token
email_token_expires_at	TEXT	ISO timestamp
email_token_sent_at	TEXT	ISO timestamp
password_hash	TEXT	bcrypt-sha256$…
created_at	TEXT	ISO timestamp
updated_at	TEXT	ISO timestamp
auth_version	INTEGER	Default 0; bumped on password reset
password_reset_token_hash	TEXT	SHA-256 of the reset token
password_reset_expires_at	TEXT	ISO timestamp
password_reset_sent_at	TEXT	ISO timestamp

Indexes:

• Unique lower(username).
• Unique lower(email) where email IS NOT NULL.
• Partial index on password_reset_token_hash.

leaderboard_scores

Column	Type	Notes
id	TEXT PK	UUID
user_id	TEXT	FK → users(id) ON DELETE CASCADE
game_id	TEXT	One of the ten ranked games
mode	TEXT	Difficulty/mode label
seconds	INTEGER	Completion time
moves	INTEGER	Move count
detail	TEXT	Optional detail line
achieved_at	TEXT	ISO timestamp
updated_at	TEXT	ISO timestamp

Constraints and indexes:

• UNIQUE (user_id, game_id, mode) — one best score per user, game and mode.
• Ranking index on (game_id, mode, seconds, moves, achieved_at, user_id).

chess_matches

Column	Type	Notes
id	TEXT PK	UUID
white_user_id	TEXT	FK → users(id) ON DELETE CASCADE
black_user_id	TEXT	FK → users(id) ON DELETE CASCADE
status	TEXT	pending, active, declined, cancelled, finished
moves	TEXT	JSON array of UCI moves
turn	TEXT	w or b
result	TEXT	white, black, draw or NULL
version	INTEGER	Optimistic concurrency counter
created_at	TEXT	ISO timestamp
updated_at	TEXT	ISO timestamp
finished_at	TEXT	ISO timestamp or NULL
last_move_at	TEXT	ISO timestamp or NULL

Index on (white_user_id, black_user_id, updated_at).

schema_migrations

Column	Type	Notes
version	INTEGER PK	Migration number
name	TEXT	Human-readable name
applied_at	TEXT	ISO timestamp

Migration policy

Migrations live in src/migrations.js as POSTGRES_MIGRATIONS.

Current versions:

Version	Name
1	Baseline accounts, leaderboard and chess schema
2	Password reset tokens and session versioning

Rules:

1. Migrations are ordered and applied by ascending version.
2. Each migration runs in its own transaction.
3. Statements are idempotent (IF NOT EXISTS, ADD COLUMN IF NOT EXISTS).
4. schema_migrations records what has run.
5. Never edit a released migration. Add the next integer version.
6. Migrations run automatically when the backend connects to PostgreSQL, and explicitly through node src/migrate.js.

update.sh runs the migration runner before starting the new container:

docker compose run --rm accounts node src/migrate.js

The runner uses a single pooled client so BEGIN/COMMIT wraps the DDL correctly.

Adding a migration

export const POSTGRES_MIGRATIONS = [
  // existing migrations…
  {
    version: 3,
    name: 'add example column',
    statements: [
      `ALTER TABLE users ADD COLUMN IF NOT EXISTS example TEXT`,
    ],
  },
];

Then:

1. Test against a disposable PostgreSQL database.
2. Add or update a test in test/migrations.test.js.
3. Bump the backend version.
4. Run ./update.sh in production; it takes a backup first.

SQLite fallback

Without PostgreSQL configuration the backend uses DATABASE_PATH (default ./data/accounts.db) and creates the schema automatically. This is intended for local development and tests, not production.

SQLite-specific notes:

• Foreign keys and cascades are enabled.
• The same logical tables and constraints are created.
• Older SQLite databases receive missing columns through ALTER TABLE checks.

Backups

Manual dump

docker compose exec -T db \
  sh -c 'pg_dump -U "$POSTGRES_USER" -d "$POSTGRES_DB" -Fc' \
  > "backups/manual-$(date -u +%Y%m%dT%H%M%SZ).dump"
chmod 600 backups/*.dump

Automatic pre-update dump

update.sh writes backups/pre-update-<UTC>.dump before pulling code or applying migrations, provided the db service is running.

Restore

Stop the backend, restore into the running database, then restart:

docker compose stop accounts

docker compose exec -T db \
  sh -c 'pg_restore -U "$POSTGRES_USER" -d "$POSTGRES_DB" --clean --if-exists' \
  < backups/pre-update-YYYYMMDDTHHMMSSZ.dump

docker compose start accounts
curl -fsS http://127.0.0.1:6767/healthz

Test restores on a separate database before relying on them.

Data durability

The base Compose file uses the named volume postgres-data. Production deployments may use a host bind mount through a local, gitignored override:

services:
  db:
    volumes:
      - ./postgres-data:/var/lib/postgresql/data

Never delete the PostgreSQL volume or data directory to fix a startup or authentication problem. It contains every account, score and chess match. Change the role password with ALTER ROLE instead.

Account deletion

Deleting an account through the API cascades to:

• leaderboard_scores
• chess_matches

There is no soft-delete or recovery window.