prisma/schema.prisma — the three tables

In one sentence: describes the database structure. Prisma reads this and generates both the SQL to create the tables and the TypeScript types to query them.

---

Why only three tables

Because PostgreSQL stores results, not gameplay.

Player positions change 20 times a second. At 2,000 players that is 40,000 changes per second, and nobody ever asks where a player stood three seconds ago. Writing those to a database would be the fastest possible way to destroy the system.

So live state lives in memory, and only the outcome is durable:

---

The code, block by block

1. Setup

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

• generator client — generate a JavaScript/TypeScript client from this file. That is what makes prisma.player.findMany() exist and be type-checked: get a column name wrong and it fails to compile rather than at runtime.
• datasource db — we are using PostgreSQL, and the connection string comes from the DATABASE_URL environment variable. It is deliberately not written here, because this file is committed to git and that string contains a password.

2. Player — a durable identity

model Player {
  id          String       @id @default(cuid())
  username    String       @unique
  gamesPlayed Int          @default(0)
  totalScore  Int          @default(0)
  bestScore   Int          @default(0)
  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt
  results     GameResult[]

  @@index([totalScore])
}

Line by line:

• id String @id @default(cuid()) — the primary key. cuid is a collision- resistant unique id, generated by the application rather than the database. Preferred over an auto-incrementing number because sequential ids leak information (anyone can tell how many users you have) and are awkward across multiple servers.
• username String @unique — no two players share a name. The database enforces this; it is not just an application rule. That is what makes createMany({ skipDuplicates: true }) safe when several games finish at once.
• gamesPlayed, totalScore, bestScore — running totals, kept current as games finish.
• createdAt DateTime @default(now()) — set automatically on insert.
• updatedAt DateTime @updatedAt — set automatically on every update.
• results GameResult[] — the relation. Not a real column; it lets you write include: { results: true }.
• @@index([totalScore]) — an index for sorting the leaderboard.

Why keep running totals rather than calculating them?

Without them, the all-time leaderboard means scanning every GameResult row ever recorded, grouping by player and summing — slower every single day the server runs.

Keeping them current means /api/leaderboard is one sorted read with no calculation. The cost is paid once when a game ends, not every time someone looks.

That is a deliberate trade: write a little more, read a lot less. Reads massively outnumber writes here.

3. GameSession — one finished game

model GameSession {
  id                String       @id @default(cuid())
  roomCode          String
  status            String // "completed" | "abandoned"
  playerCount       Int
  collectiblesTotal Int
  startedAt         DateTime?
  finishedAt        DateTime
  durationMs        Int
  createdAt         DateTime     @default(now())
  results           GameResult[]

  @@index([finishedAt])
  @@index([roomCode])
}

• roomCode String — not unique, and not a foreign key. Room codes are reused: the same code can host many games over time. This is a label, not a link.
• status String — "completed" or "abandoned". Abandoned games are recorded too, so the history is honest rather than only showing tidy endings.
• startedAt DateTime? — the ? means nullable. A game abandoned before the countdown finished genuinely never started.
• collectiblesTotal Int — how many were in play. This is what makes the audit query possible: the sum of all scores must equal lobbies × collectibles.
• @@index([finishedAt]) — for "show me recent games", which sorts by this.

4. GameResult — one player's standing

model GameResult {
  id        String      @id @default(cuid())
  sessionId String
  session   GameSession @relation(fields: [sessionId], references: [id], onDelete: Cascade)
  playerId  String
  player    Player      @relation(fields: [playerId], references: [id], onDelete: Cascade)
  username  String
  score     Int
  rank      Int

  @@unique([sessionId, playerId])
  @@index([sessionId])
  @@index([playerId])
  @@index([score])
}

The join table connecting players to games, plus what they achieved.

• session GameSession @relation(fields: [sessionId], references: [id], …) — a foreign key. sessionId must match a real GameSession.id; the database rejects anything else.
• onDelete: Cascade — delete a session and its results go too. Without it you would accumulate orphaned rows pointing at nothing.
• username String — stored again, even though it is on Player. This is deliberate denormalisation with two benefits: it preserves the name as it was at the time, and one game's leaderboard can be read without a join.
• @@unique([sessionId, playerId]) — the important one. A player can appear at most once per session. The database itself makes a double-insert impossible.

That last constraint is worth pointing at in an interview. The exactly-once guarantee is enforced in the game logic and backed by a database constraint. If a bug ever tried to save a player twice for one game, PostgreSQL would reject it rather than silently corrupting the record.

---

How they connect

Player ──────< GameResult >────── GameSession
  one            many               one
  │                                  │
  username                       roomCode
  gamesPlayed                    status
  totalScore                     finishedAt
  bestScore                      durationMs

One player has many results. One session has many results. GameResult sits between them holding the score and rank.

---

Useful queries

-- Recent games with their standings
SELECT s."roomCode", s.status, s."playerCount",
       round(s."durationMs"/1000.0, 1) AS secs, s."finishedAt"
FROM "GameSession" s
ORDER BY s."finishedAt" DESC
LIMIT 20;

-- All-time leaderboard (pre-aggregated, what /api/leaderboard serves)
SELECT username, "gamesPlayed", "totalScore", "bestScore"
FROM "Player"
ORDER BY "totalScore" DESC
LIMIT 20;

-- The exactly-once audit: points awarded must equal collectibles in play
SELECT SUM(s."collectiblesTotal") AS collectibles_in_play,
       (SELECT SUM(score) FROM "GameResult") AS points_awarded
FROM "GameSession" s
WHERE s.status = 'completed';

Note the double quotes. Prisma creates PascalCase table names, and PostgreSQL folds unquoted identifiers to lowercase — so SELECT * FROM GameSession fails with relation "gamesession" does not exist. Quote them.

That audit query is the durable proof of the exactly-once guarantee. After 80 games it returned exactly 2000 on both sides — 80 lobbies × 25 collectibles, not one point extra or missing.

---

Creating the tables

docker compose up -d     # start PostgreSQL on port 5433
npx prisma db push       # create the tables from this file

db push compares the schema with the database and applies the difference. It is right for a prototype.

For a real deployment you would use prisma migrate instead, which records each change as a numbered, reviewable file. db push cannot tell you what changed between two versions or roll anything back. Worth volunteering as a known limitation.

---

If an interviewer asks

"Why don't you store player positions?"

"They change 20 times a second — 40,000 writes a second at full load — and nobody reads them back. Live state belongs in memory; the database holds results, which are read often and must survive a restart."

"Why keep running totals on Player?"

"So the leaderboard is one sorted read instead of scanning every result ever recorded. Reads massively outnumber writes here, so paying a little more on write is the right trade."

"How does the database help with exactly-once?"

"@@unique([sessionId, playerId]) makes it impossible to record a player twice for one game. The guarantee is enforced in the game logic and backed by a constraint, so a bug would be rejected rather than silently corrupting data."