See `requirements.md` for what is built; requirement ids (R1.1, …) are referenced below.
## Decisions
| Decision | Reason |
|---|---|
| **PostgreSQL** for saved games, not MongoDB | MongoDB moved to SSPL. Postgres already runs for Keycloak, so it adds no new technology; `jsonb` stores the move list as a document and the queries (a user's games, most spectated games) are relational. FerretDB was considered (Mongo API on Postgres) but adds a translation layer and compatibility gaps for no gain. |
| One Postgres server, separate databases and users for Keycloak and the game | One container to run and back up on a small server. |
| **Valkey** for live game state and short-lived caches, not Redis | Redis changed its license in 2024; Valkey is the BSD-licensed fork and is protocol compatible. Client: `iovalkey` (MIT). |
| The backend's memory stays the source of truth for live games; Valkey is a write-through copy | The backend runs as a single instance; reading from memory keeps moves fast and simple. Valkey only matters on restart (R1.2). |
| A game is written to Postgres once, when it ends | Live state is already durable in Valkey; one write per game keeps the database simple. |
| Database and cache failures never stop a game | R1.3. Writes are best effort with retry; reads fall back to empty lists. |
| Legacy ids are replaced with a hash before anything is saved or listed | R8.3. The database never holds a legacy userKey. |
## Infrastructure
`apps/compose.yaml`:
-`postgres` (`postgres:17.6`) replaces `keycloak-db`. An init script (`apps/postgres/init/01-databases.sh`) creates
the `keycloak` and `mancala` databases, each with its own user, from `KEYCLOAK_DB_PASSWORD` and
`MANCALA_DB_PASSWORD`. Exposed on `127.0.0.1:5433` for local development.
-`valkey` (`valkey/valkey:9.1.2`, append-only file on so live games survive a Valkey restart). Exposed on
`127.0.0.1:6379`.
- Backend configuration: `DATABASE_URL` (default `postgres://mancala:mancala@localhost:5433/mancala`) and
`VALKEY_URL` (default `redis://localhost:6379`).
## Data model (PostgreSQL)
Migrations live in `apps/backend/src/db/migrations/` as TypeScript files exporting SQL, applied in order at startup
and recorded in `schema_migrations`.
```sql
CREATE TABLE games (
id uuid PRIMARY KEY,
player1_id text NOT NULL,
player2_id text NOT NULL,
result text CHECK (result IN ('player1', 'player2', 'draw')), -- NULL: no winner (inactive)
end_reason text NOT NULL CHECK (end_reason IN ('completed', 'left', 'timeout', 'inactive')),
left_user_id text,
started_at timestamptz NOT NULL,
ended_at timestamptz NOT NULL,
move_count integer NOT NULL,
final_board jsonb NOT NULL, -- 14 stone counts
moves jsonb NOT NULL, -- MoveRecord[]
peak_spectators integer NOT NULL DEFAULT 0,
total_spectators integer NOT NULL DEFAULT 0
);
CREATE TABLE game_players (
game_id uuid NOT NULL REFERENCES games (id) ON DELETE CASCADE,
seat smallint NOT NULL CHECK (seat IN (1, 2)),
user_id text NOT NULL, -- public id (see "Public ids")
name text,
is_guest boolean NOT NULL,
PRIMARY KEY (game_id, seat)
);
CREATE INDEX game_players_user_id ON game_players (user_id);
CREATE INDEX games_popular ON games (peak_spectators DESC, ended_at DESC);
```
Saving uses `INSERT … ON CONFLICT (id) DO NOTHING` in one transaction, so retries are safe.
## Public ids (R8.3)
`publicUserId(id)`: Keycloak ids (`kc:…`) are returned unchanged; any other id is a legacy userKey and becomes
`legacy:` + the first 20 hex characters of its SHA-256. Applied to every id in saved games, popular lists and live
previews. Live game messages to the players and spectators of a game, and `GET /game/:id` for a live game, keep raw
ids because the current mobile app compares its userKey with them; this exposure already exists and goes away with
