Skip to content
Tabletop Tracker

WI-010: Domain schema

WI-010: Domain schema

Reviewed. All four design questions were resolved at spec review; question 1 produced ADR-0021. Questions 2–4 were settled on the recommendations recorded below. Ready to implement.

Problem

packages/db has a placeholder table. Everything in M1–M4 — collection management, lists, session logging, the feed, discovery — builds on the real schema, and two parts of it are expensive to change later:

  • The Player/User split. docs/architecture.md §3 calls retrofitting it “very painful”; adding it now “costs one nullable foreign key”.
  • Ratings on sessions, not shelves (ADR-0006). This is the difference between this product and a shelf-first tracker, and it cannot be bolted on — Goodreads is the cautionary example.

In scope

Tables, with the constraints each ADR requires:

TableNotes
usersUUID PK. clerk_user_id as an external reference only — nothing foreign-keys to it (ADR-0005). Created lazily on first authenticated request.
gamesCatalogue entry. Array columns for mechanics/tags, JSONB for cached source payloads (ADR-0003).
game_field_provenancePer-field source, licence, confidence (ADR-0016, ADR-0017). Per-field, not per-record: one row legitimately mixes CC0 and CC BY-SA.
playersNot users. Optional user_id, supports claim-and-merge. Minimal PII — display name only until claimed (security-and-secrets.md).
shelf_entriesStanding relationship: owned/wishlist, current rating, review. Rating is a projection, never written directly.
sessionsOne play. Everything nullable except game_id and played_at (ADR-0007). Carries its own rating.
session_participantsJoin to players. Must tolerate a null user_id throughout.
lists / list_entriesCurated lists, ordered.

Plus: FTS (tsvector) and pg_trgm indexes on game titles for typo-tolerant search, and the migration generated via drizzle-kit generate.

Out of scope

  • follows and the feed — WI-040.
  • Zod schemas via drizzle-zod — WI-011.
  • Any API route, query layer or repository — WI-012 onward.
  • The catalogue seeder’s own tables — WI-025.
  • Backfilling real game data — WI-020.

Design decisions (resolved at spec review)

1. How is the shelf rating projection maintained? → Database trigger. Decided at review; recorded as ADR-0021.

Consequence that lands on this work item: Drizzle does not model triggers, so it goes in a hand-written custom migration and is invisible to pnpm --filter db check — a dropped trigger would not register as drift. The invariant tests below are what actually guard it. The trigger must also handle deletes, re-projecting to the next most recent session or to null.

2. Does a user deletion anonymise or cascade? → Anonymise. security-and-secrets.md says deleting a user must not orphan sessions other people participated in — anonymise the participant, keep the session. So ON DELETE SET NULL on players.user_id plus a tombstone on users, not a cascade. This pairs with decision 3: the player row is what survives, carrying the session history with it.

3. What does claim-and-merge actually do to history? → The player row survives. When a player is claimed by a user, the player row persists with user_id set, rather than session rows being rewritten to point at the user. Past sessions stay attributed and nothing is destroyed — rewriting history is irreversible and would damage other people’s session records.

4. Is games.id our UUID, or a natural key from the source? → Our own UUID. ADR-0016 has multiple catalogue sources with their own identifiers, and ADR-0010 requires them to be swappable. Source identifiers become separate nullable columns; otherwise the CatalogueSource seam leaks into every foreign key in the schema.

Definition of done

Behavioural lines each need a test (testing.md); the four invariant tests below are named there as never-weaken tests.

  • All tables exist with the constraints above; migration generated by drizzle-kit generate, not hand-written.
  • pnpm --filter db check passes (drift + journal integrity).
  • Invariant 1: a second session rating updates the shelf projection and leaves the first session’s rating intact.
  • Invariant 2: a session_participant whose player has a null user_id round-trips through every participant query.
  • Invariant 3: INSERT into sessions with only game_id and played_at succeeds.
  • Invariant 4: schema assertion — no table foreign-keys to clerk_user_id.
  • The projection is maintained by a trigger; writing shelf_entries.rating directly raises an error rather than silently no-op’ing, so a caller’s bug surfaces.
  • Deleting the most recent session re-projects to the next most recent, or to null if none remain — with a test. A trigger handling only inserts leaves a stale rating.
  • A test asserts the trigger’s behaviour, not its existence — check-schema.mjs cannot see triggers (ADR-0021).
  • Deleting a user leaves co-participants’ sessions intact — with a test.
  • Trigram search returns a match for a misspelt game title.
  • FTS returns a match on a title word.
  • Seed data covers each table; seeding twice is idempotent.
  • The placeholder _pipeline_check table and its migration are removed.

Verification

Terminal window
pnpm --filter db db:up
pnpm --filter db migrate
pnpm --filter db seed && pnpm --filter db seed   # idempotent
pnpm --filter db check
pnpm gate

Then, for the invariants specifically:

Terminal window
pnpm --filter db test -t "invariant"

Notes

This spec deliberately does not include column-level DDL. The four open questions change the shape of at least three tables, and writing the columns first would bury those decisions in a diff rather than surfacing them for review. That is the point of needs:spec-review.

The _pipeline_check table from WI-006 exists only to prove the harness; removing it here is part of the work, not an afterthought.