mancala/docs/specs/game-history/design.md

Game history and live games: design

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.

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 LEGACY_USERKEY_AUTH (see TODO.md).

Shared types (packages/core)

interface PlayerSummary { userId: string; name?: string; isGuest: boolean }
interface MoveRecord { playerId: string; moveIndex: number; boardSnapshot: number[]; gameSteps: GameStep[]; at: number }
type GameResult = "player1" | "player2" | "draw";
type EndReason = "completed" | "left" | "timeout" | "inactive";

interface LiveGameSummary {
  id: string; player1: PlayerSummary; player2: PlayerSummary; board: number[];
  turnPlayerId: string; spectatorCount: number; moveCount: number; startedAt: number;
}
interface FinishedGameSummary {
  id: string; player1: PlayerSummary; player2: PlayerSummary; board: number[];
  result: GameResult | null; endReason: EndReason; leftUserId?: string;
  startedAt: number; endedAt: number; moveCount: number; peakSpectators: number;
}
interface GameRecord extends FinishedGameSummary { moves: MoveRecord[]; totalSpectators: number }   // replay payload

Live Game messages gain startedAt and moveTimes: number[] (one per history entry), and UserConnectionInfo gains offlineSince?: number. TURN_TIMEOUT_MS (5 minutes) is exported so the client can show the countdown.

Backend

Modules

src/db/postgres.ts                    pool, migrations
src/history/GameHistoryRepository.ts  interface + PostgresGameHistoryRepository
src/live/LiveGameStore.ts             interface + ValkeyLiveGameStore (snapshots, pending saves)
src/cache/Cache.ts                    JSON get/set/delete with TTL in Valkey
src/game/GameSerialization.ts         BackendGame <-> snapshot, rebuilding the engine objects
src/game/GameRecorder.ts              turns a finished game into a GameRecord and saves it, with retry
src/game/LiveGamesBroadcaster.ts      top live games, subscriptions, throttled push
src/game/publicIds.ts                 publicUserId and helpers

Game lifecycle (GameManager)

• Start: as today, plus startedAt, empty moveTimes, spectator counters; the snapshot is written to Valkey.
• Move: the time is appended to moveTimes; the snapshot is written to Valkey; live previews are scheduled.
• End (R3): the outcome is decided, the game is removed from memory and from Valkey, players and spectators get the final update, and GameRecorder saves the record.
  • completed: mancalaGame.getWonPlayerId(), draw if none.
  • left: the leaving player loses (left_user_id is set).
  • timeout / inactive: decided by a watcher that runs every 10 seconds, using the time each player went offline (offlineSince, kept per user and cleared when they reconnect).
• New game while playing (R2.3): MatchMaker is not called; the user gets on_new_game_rejected with the game id.
• Restart (R1.2): on startup all snapshots are loaded from Valkey and put back into memory; both players count as offline from that moment, so the timeout rules start fresh. Snapshots of games that are already saved are dropped instead. If Valkey is unreachable at startup, the backend starts without restoring.
• Messages: players and spectators get a trimmed game (id, mancalaGame, gameUsersConnectionInfo, startedAt, moveTimes, spectatorCount), never the spectator ids. When a game ends for any reason they also get on_game_end (GameEndMessage); a player leaving still sends on_game_user_leave as before.
• Fixes needed by this feature: spectators are removed when they disconnect (the for…in loop in GameStoreImpl), finished games are removed from memory, and the connection info is sent once at game start.

Saving with retry (R1.3)

GameRecorder.save(record): on failure the record is stored in Valkey under mancala:pending-save:{id} (and kept in memory if Valkey is down too) and retried every 30 seconds and on startup.

Valkey keys

Key	Content
mancala:live:{gameId}	snapshot of a live game (JSON)
mancala:pending-save:{gameId}	game record that could not be saved yet
mancala:cache:popular-finished	popular finished games, 10 s TTL
mancala:cache:recent:{userId}	a user's recent games, 10 s TTL

Caches are deleted when a game ends. The client uses enableOfflineQueue: false so commands fail at once instead of queueing while Valkey is down.

HTTP API

Route	Auth	Response
GET /games/me/active	Bearer token	{ game: LiveGameSummary | null }
GET /games/me?limit=3	Bearer token	{ games: FinishedGameSummary[] } (limit 1–20)
GET /games/popular?limit=5	none	{ kind: "live" | "finished", games: [...] }; live games if any, else finished
GET /game/:id	none	a live game as today, else { status: "finished", record: GameRecord }, else 404

The token is the same Keycloak access token used for the WebSocket, checked with the existing TokenVerifier.

WebSocket channels (added to channel_names)

Channel	Direction	Payload
subscribe_live_games / unsubscribe_live_games	client → server	–
on_live_games	server → client	{ games: LiveGameSummary[] }, sent on subscribe and at most once per second when the top 5 change
on_new_game_rejected	server → client	{ gameId }

Ordering for live games: spectator count, then most recent move. Preview subscribers are tracked per user and are not spectators (R4.5).

Web app

Board size

BoardView gets an optional width (px). All board components size themselves from a CSS variable --board-unit (default 1vw, else width / 92 px, the board being 92 units wide) instead of vw, so the same board renders at full width in a game and small in previews.

Home page (routes/Home.tsx)

1. ActiveGameBanner: from GET /games/me/active; opponent, whose turn, Continue.
2. LiveGamesSection: GET /games/popular on load, then subscribe_live_games; a grid of GamePreviewCards (read-only board, names, spectator count). Title "Live games" or "Popular games" by kind.
3. RecentGamesSection: GET /games/me?limit=3; cards with opponent, result for this user, score, date, board.

Lobby

On on_new_game_rejected the lobby shows "You have a game in progress" with Continue.

Move history (shared by R6 and R7)

• packages/core/src/history/: getInitialBoard(), getBoardAfterMove(moves, n) (n = 0 is the start position), describeMove(moves, n) → { number, playerId, pit (1–6), extraTurn, capture, storeGain, gameOver }, and createGameAtMove(game, n), which builds the engine objects PitAnimator needs to animate move n.
  • extra turn: a GAME_STEP_LAST_STONE_IN_BANK step; capture: GAME_STEP_LAST_STONE_IN_EMPTY_PIT or GAME_STEP_DOUBLE_STONE_IN_PIT; game over: GAME_STEP_BOARD_CLEARED; store gain: the mover's store after the move minus before.
• MoveList: the list, highlighting the shown move; clicking selects a move.
• Live game (GamePage): viewedMove state. When set, the board shows getBoardAfterMove and pits are disabled; ReviewBanner shows the move number, "Your turn" when it applies, and Back to live. ← / → keys step.
• Countdown: if the opponent is offline on their turn, the info panel shows the time left from offlineSince.

Replay (ReplayView, R7)

GamePage renders ReplayView when GET /game/:id returns a finished record. It starts at the last move with the result and Watch from start. Controls: ⏮ ◀ ▶/⏸ ▶| ⏭, speed 1x/2x/4x, a range input as the timeline, the MoveList, keyboard (← → space). Forward steps go through PitAnimator with the games from createGameAtMove; jumps set the board directly. ?move=n is read on load and updated with history.replaceState.

Testing

• packages/core: unit tests for the history helpers against games played with the engine.
• Backend: unit tests for outcomes, timeouts and public ids with in-memory fakes; integration tests for the Postgres repository and the Valkey store against real containers (testcontainers); the existing WebSocket tests keep passing.
• End to end in Chrome: two guests play a full game; the home page shows it in recent games and as popular; replay works; reviewing an earlier move during a live game; restarting the backend in the middle of a game.