Drizzle schema: lobbies, lobby_players + migration #30

New issue

Open

opened 2026-04-19 07:23:35 +00:00 by forgejo-lila · 0 comments

forgejo-lila commented

2026-04-19 07:23:35 +00:00

Owner

Context

Multiplayer requires database tables for game lobbies and their participants. See documentation/game-modes.md for game mode details.

What to do

Add lobby-related tables to the Drizzle schema and generate a migration.

Files to change

packages/db/src/db/schema.ts — add lobbies and lobby_players tables.
Run pnpm --filter db generate to create migration SQL.
Run pnpm --filter db migrate to apply locally.

Schema design

lobbies:
  id: uuid, PK, default random
  code: varchar(10), unique, not null — human-readable join code (e.g. 'WOLF-42')
  host_user_id: text, FK to user.id, not null
  status: varchar(20), not null — CHECK: 'waiting', 'in_progress', 'finished'
  is_private: boolean, default false
  game_mode: varchar(30), not null — CHECK: 'tv_quiz', 'race_to_top', 'chain_link', 'elimination'
  settings: jsonb, nullable — mode-specific config (e.g. target score for race_to_top)
  max_players: integer, default 4
  created_at: timestamp with timezone, default now

lobby_players:
  lobby_id: uuid, FK to lobbies.id (cascade), not null
  user_id: text, FK to user.id (cascade), not null
  score: integer, default 0
  joined_at: timestamp with timezone, default now
  PK: (lobby_id, user_id)

Acceptance criteria

Migration runs on fresh and existing production databases
Schema follows existing conventions (CHECK constraints over pgEnum — see documentation/decisions.md)
Foreign keys reference Better Auth user table (user.id is text, not uuid)
Indexes on lobbies.code (unique) and lobbies.status
Game mode values come from a constant in packages/shared/src/constants.ts

## Context Multiplayer requires database tables for game lobbies and their participants. See `documentation/game-modes.md` for game mode details. ## What to do Add lobby-related tables to the Drizzle schema and generate a migration. ## Files to change - `packages/db/src/db/schema.ts` — add `lobbies` and `lobby_players` tables. - Run `pnpm --filter db generate` to create migration SQL. - Run `pnpm --filter db migrate` to apply locally. ## Schema design ``` lobbies: id: uuid, PK, default random code: varchar(10), unique, not null — human-readable join code (e.g. 'WOLF-42') host_user_id: text, FK to user.id, not null status: varchar(20), not null — CHECK: 'waiting', 'in_progress', 'finished' is_private: boolean, default false game_mode: varchar(30), not null — CHECK: 'tv_quiz', 'race_to_top', 'chain_link', 'elimination' settings: jsonb, nullable — mode-specific config (e.g. target score for race_to_top) max_players: integer, default 4 created_at: timestamp with timezone, default now lobby_players: lobby_id: uuid, FK to lobbies.id (cascade), not null user_id: text, FK to user.id (cascade), not null score: integer, default 0 joined_at: timestamp with timezone, default now PK: (lobby_id, user_id) ``` ## Acceptance criteria - Migration runs on fresh and existing production databases - Schema follows existing conventions (CHECK constraints over pgEnum — see `documentation/decisions.md`) - Foreign keys reference Better Auth `user` table (`user.id` is `text`, not uuid) - Indexes on `lobbies.code` (unique) and `lobbies.status` - Game mode values come from a constant in `packages/shared/src/constants.ts`