7. Persistence — Prisma and Postgres

Postgres stores only what must outlive a match. Nothing about movement, live scores, or which orb is still available ever reaches it.

A movement update touching Postgres would cost ~1–5 ms against a 50 ms tick budget shared by every lobby. A match writes roughly 21 rows once, at the end — instead of the ~360,000 position updates "save everything" would mean for a 3-minute 10-player game.

---

prisma/schema.prisma

Game ──< GameParticipant          PlayerStat  (standalone aggregate)

Game — one row per completed match

Indexed on endedAt for the "recent games" query.

Written once, at the end, never updated. A row for an in-flight game would be a lie needing constant maintenance — the running game lives in memory.

GameParticipant — one row per player per match

username, score, rank, plus a gameId relation with onDelete: Cascade — deleting a game takes its participants with it, which is what makes npm run db:clean a single deleteMany.

Indexed on gameId and username.

PlayerStat — the all-time leaderboard

Keyed by username, with gamesPlayed, totalScore, bestScore, wins, lastPlayedAt. Indexed on totalScore.

Denormalised on purpose. The same numbers could come from:

SELECT username, SUM(score) FROM "GameParticipant" GROUP BY username ORDER BY ... LIMIT 10

That is correct, and simpler. But it scans every participant row ever written and gets slower forever. Upserting once per player per match keeps the leaderboard read to one indexed ORDER BY ... LIMIT 10 at any history size.

The trade-off worth naming: an aggregate can drift from its source rows. That is why both are written in the same transaction.

There is no User table. Usernames are unique within a lobby but are display labels, not identities — no auth was in scope. Two different people who both play as alice share one PlayerStat row. Real accounts would need a User table, and PlayerStat.username would become userId.

---

src/db/prisma.js

export const prisma = new PrismaClient({
  log: process.env.NODE_ENV === 'development' ? ['warn', 'error'] : ['error'],
});

One PrismaClient for the whole process. Each instance opens its own connection pool, so creating them per-request (a common mistake) exhausts Postgres connections under load. A single shared instance is the documented pattern.

disconnectPrisma() is called during graceful shutdown.

---

src/services/gamePersistence.js

The only place a finished game reaches Postgres.

persistGame(game)

1. Decide whether it is worth recording.

if (leaderboard.length === 0 || totalScore === 0) {
  return { saved: false, reason: 'NOTHING_TO_RECORD' };
}

An empty lobby, or an abandoned one where nobody scored, produces no row. It would be noise in the match history and would credit a "win" for zero points.

2. Build every write, then commit them as one transaction.

await prisma.$transaction(writes);

The Game (with nested participants: { create: [...] }), plus one upsert per player, commit together or not at all. Without this, a crash mid-save could leave a game whose participants are missing, and the all-time leaderboard would silently disagree with the match history.

3. The PlayerStat upsert is raw SQL — deliberately.

INSERT INTO "PlayerStat" (...) VALUES (...)
ON CONFLICT ("username") DO UPDATE SET
  "gamesPlayed" = "PlayerStat"."gamesPlayed" + 1,
  "totalScore"  = "PlayerStat"."totalScore"  + EXCLUDED."totalScore",
  "bestScore"   = GREATEST("PlayerStat"."bestScore", EXCLUDED."bestScore"),
  "wins"        = "PlayerStat"."wins"        + EXCLUDED."wins",
  "lastPlayedAt"= EXCLUDED."lastPlayedAt"

bestScore needs GREATEST(existing, new), which Prisma's update syntax cannot express. Doing it in JavaScript would mean read-then-write — two round trips with a gap in between where a concurrent game could overwrite the value. A single INSERT ... ON CONFLICT does it atomically in the database, in one round trip.

This is the same check-then-write hazard as tryCollect, one layer down. In memory the fix is "don't await in the middle"; in SQL the fix is "let the database do the comparison".

4. A win only counts if the player actually scored.

const wins = row.rank === 1 && row.score > 0 ? 1 : 0;

Otherwise everyone in an abandoned 0–0 game would be credited with a victory.

5. Failures are caught, never thrown.

P2002 (unique violation, meaning already saved) is logged as a warning and treated as success-ish. Anything else logs and returns { saved: false }. A database failure must never take the game down — the players have already seen their final leaderboard by the time this runs.

---

src/services/leaderboardService.js

Read-side queries. No writes.

getAllTimeLeaderboard({ limit })

const safeLimit = Math.min(Math.max(Number(limit) || 10, 1), 100);

The limit is clamped, so ?limit=99999 cannot force an unbounded scan. There is an integration test asserting exactly that.

Orders by totalScore desc, then wins desc, then username asc — the last key makes ties deterministic, which matters because tests assert on rank order. Ranks are assigned in JavaScript from the array index.

getRecentGames({ limit })

Match history: recent Game rows with participants included, ordered by rank, and a convenience winner field (participants[0].username).

Uses Prisma's include rather than a separate query per game — one round trip instead of N+1.

---

Migrations

prisma/migrations/ is committed on purpose and explicitly not in .gitignore. Those files are the schema history: without them npx prisma migrate dev has nothing to replay, and a reviewer who clones the repo gets a database with no tables.

---

Maintenance scripts

npm run db:seed (scripts/seed.js) — writes 12 plausible finished games so the all-time leaderboard is not empty on a fresh clone. It uses the same one-transaction-per-game shape as the real code, including the raw upsert.

npm run db:clean (scripts/clean-test-data.js) — deletes rows whose username starts with bot or e2e_. A stress run plays hundreds of real games, and as far as Postgres is concerned those are real results sitting on the all-time leaderboard next to actual players. Games are deleted first; participants cascade.