src/persistence/PrismaGameRepository.ts — the real PostgreSQL writing

In one sentence: the actual database implementation, written so that saving a game costs the same five queries whether one player or twenty were in it.

Size: 127 lines. Depends on: Prisma, and the GameRepository interface.

---

Why this file exists

GameRepository says what saving means. This says how, using PostgreSQL.

The headline decision: five queries per finished game, regardless of player count — not two queries per player.

The naive version would loop over players:

for (const r of results) {
  await prisma.player.upsert(...);   // find or create the player
  await prisma.gameResult.create(...); // save their result
}

For a 20-player game that is 41 queries. When 100 lobbies finish in the same second, roughly 4,000 queries. This version does 500 instead.

---

What Prisma is

An ORM — object-relational mapper. It lets you write prisma.player.findMany(...) instead of raw SQL, and it generates TypeScript types from your schema, so a typo in a column name fails at compile time rather than at 3am.

---

The code, line by line

1. The class (lines 17–18)

export class PrismaGameRepository implements GameRepository {
  constructor(private readonly prisma: PrismaClient) {}

implements GameRepository — TypeScript verifies every method from the interface exists with the right shape.

The Prisma client is handed in, not created here. That means a test could pass a different one, and the connection is managed in one place (index.ts).

2. saveGameResult — the five queries

async saveGameResult(input: GameResultInput): Promise<void> {
  if (input.results.length === 0) {
    return;
  }
  const usernames = input.results.map((r) => r.username);

  await this.prisma.$transaction(async (tx) => {

The early return skips abandoned rooms that never admitted anyone — not worth a row.

$transaction is important. Everything inside either all succeeds or all fails. Without it, a crash halfway through could leave a GameSession row with no results attached, or player totals updated for a game that was never recorded.

tx is the transaction handle. Every query inside must use tx, not this.prisma, or it would run outside the transaction.

Query 1 — insert any new players

    await tx.player.createMany({
      data: usernames.map((username) => ({ username })),
      skipDuplicates: true,
    });

createMany inserts every row in one statement.

skipDuplicates: true is what makes this safe. Most players already exist from earlier games, and username has a unique constraint — without this flag, the insert would fail. With it, existing rows are silently left alone.

This also makes the operation safe when several games finish at once: two transactions both inserting "alice" cannot conflict.

Query 2 — look up their ids

    const players = await tx.player.findMany({
      where: { username: { in: usernames } },
      select: { id: true, username: true },
    });
    const idByUsername = new Map(players.map((p) => [p.username, p.id]));

where: { username: { in: usernames } } becomes SQL WHERE username IN (...) — all twenty looked up in one query rather than twenty.

select: { id: true, username: true } fetches only those two columns. Without it, Prisma returns every column including totals we do not need.

The Map gives instant username → id lookup for the next step.

Query 3 — the session row

    const session = await tx.gameSession.create({
      data: {
        roomCode: input.roomCode,
        status: input.status,
        playerCount: input.playerCount,
        collectiblesTotal: input.collectiblesTotal,
        startedAt: input.startedAt,
        finishedAt: input.finishedAt,
        durationMs: input.durationMs,
      },
      select: { id: true },
    });

One row describing the game. select: { id: true } because the generated id is the only thing needed next.

Query 4 — every player's result

    const resultRows = input.results
      .map((r) => {
        const playerId = idByUsername.get(r.username);
        return playerId ? { sessionId: session.id, playerId, username: r.username, score: r.score, rank: r.rank } : null;
      })
      .filter((r): r is NonNullable<typeof r> => r !== null);

    if (resultRows.length === 0) return;
    await tx.gameResult.createMany({ data: resultRows, skipDuplicates: true });

Build all the rows, then insert them in one statement.

.filter((r): r is NonNullable<typeof r> => r !== null) looks intimidating but does something simple: drop the nulls, and tell TypeScript the result contains no nulls. Without that type annotation, TypeScript would still think the array might contain null and complain on the next line.

username is stored on the result row even though it is on Player too. That is deliberate denormalisation: it preserves the name as it was at the time, and lets the leaderboard for one game be read without a join.

Query 5 — roll up lifetime totals

    const values = Prisma.join(
      resultRows.map((r) => Prisma.sql`(${r.playerId}::text, ${r.score}::int)`),
    );
    await tx.$executeRaw`
      UPDATE "Player" AS p
      SET "gamesPlayed" = p."gamesPlayed" + 1,
          "totalScore"  = p."totalScore" + v.score,
          "bestScore"   = GREATEST(p."bestScore", v.score),
          "updatedAt"   = NOW()
      FROM (VALUES ${values}) AS v(id, score)
      WHERE p.id = v.id
    `;

The only raw SQL in the project, and it earns its place.

Each player needs a different increment — alice scored 7, bob scored 3. Prisma cannot express "update twenty rows, each with its own value" in one call, so without this it would be twenty separate UPDATE statements.

This builds a temporary table of pairs:

FROM (VALUES ('abc123', 7), ('def456', 3)) AS v(id, score)
WHERE p.id = v.id

and joins it against Player, so one statement updates every row correctly.

• GREATEST(p."bestScore", v.score) — PostgreSQL's "larger of the two". Keeps the best single game, not the most recent.
• ::text and ::int — tell PostgreSQL the column types, which it cannot infer from a bare VALUES list.
• Prisma.sql and Prisma.join build the query safely. The values are sent as parameters, not pasted into the string, so this is not vulnerable to SQL injection.

Why keep running totals at all? So /api/leaderboard is a simple sorted read instead of a scan over every result ever recorded. The cost is paid once when a game ends rather than every time someone looks.

3. Reading

async getTopPlayers(limit: number): Promise<TopPlayer[]> {
  const rows = await this.prisma.player.findMany({
    orderBy: [{ totalScore: 'desc' }, { bestScore: 'desc' }],
    take: limit,
    select: { username: true, gamesPlayed: true, totalScore: true, bestScore: true },
  });
  return rows;
}

One query, no joins, no calculation — because the totals are already current.

orderBy with two entries: highest total first, then best single game as a tiebreak. take: limit becomes SQL LIMIT, so the database sends only what is needed.

async getRecentSessions(limit: number): Promise<SessionSummary[]> {
  const rows = await this.prisma.gameSession.findMany({
    orderBy: { finishedAt: 'desc' },
    take: limit,
    include: {
      results: { orderBy: { rank: 'asc' }, select: { username: true, score: true, rank: true } },
    },
  });

include fetches the related results alongside each session. Prisma does this efficiently rather than one query per session — the classic "N+1 queries" problem that makes list pages slow.

---

Verified against a real database

After running games against PostgreSQL:

 sessions | players     results     distinct_players | games_played | total_score
       40 |     400         400                  400 |          400 |        1000

total_score = 1000 = 40 lobbies × 25 collectibles — exactly. Not one point extra, not one missing. This is the durable proof of the exactly-once guarantee, independent of anything the server claims about itself.

After a second round with the same usernames:

 distinct_players | games_played | total_score
              400 |          800 |        2000

Still 400 players (no duplicate rows — skipDuplicates works), 800 games played, and totals accumulated correctly.

---

If an interviewer asks

"How many queries does saving a game take?"

"Five, regardless of player count — bulk insert players, one lookup for ids, insert the session, bulk insert results, and one set-based statement to roll up lifetime totals. The naive version is two per player, so a 20-player game would be 41."

"Why raw SQL for the last one?"

"Each player needs a different increment, and an ORM can't express 'update twenty rows each with its own value' in a single call. A VALUES list joined against the table does it in one statement instead of twenty. It's parameterised, so it's not an injection risk."

"Why store the username on both Player and GameResult?"

"Deliberate denormalisation. It preserves the name as it was at the time, and lets one game's leaderboard be read without a join."