Skip to content
Tabletop Tracker

WI-021: Game search

WI-021: Game search

Problem

There is no way to find a game. The catalogue has 333 records (WI-020) and nothing that reads them.

The backlog calls this a search endpoint, but apps/api does not exist yet — it is WI-012, blocked on WI-H06. So this item builds the query layer, which WI-012 exposes as a route. Splitting it this way means search is written and tested against real data now, rather than waiting on credentials.

What is already there, and what is wrong with it

Migration 0002 added a search_vector covering title and description only, and noted at the time that keeping it out of schema.ts made it invisible to pnpm --filter db check.

Two consequences, both found here:

  1. No fixture game has a description, so half the vector is empty and searching for a designer, mechanic or theme returns nothing.
  2. The drift probe could not see the column. Generating a migration against the current schema produced a duplicate ADD COLUMN, and the probe reported no drift. Only Postgres rejected it (ADR-0020).

In scope

  • Widen search_vector to title, designers, mechanics, themes and description, weighted A > B > C > D.
  • Declare the column in schema.ts via a customType, so the snapshot knows about it and the drift probe can see it.
  • searchGames(db, query, options) in packages/db — FTS or trigram, scored together.
  • Behavioural tests over the seeded 333-game corpus.

Out of scope

  • The HTTP route, rate limiting and caching — WI-012.
  • Ranking as a product: personalisation, popularity, recency — WI-041.
  • Search over anything but games. Lists and users have their own shapes.

Design notes

Two mechanisms, because neither suffices. FTS handles stemming, stop words and multi-word queries and knows a title match beats a mechanic match; it cannot handle a typo, because “glomhaven” stems to itself. Trigram similarity finds Gloomhaven from glomhaven and is useless for worker placement, comparing characters rather than meaning. A row qualifies on either.

The generated expression must be IMMUTABLE, and array_to_string is only STABLE — Postgres rejects the inline form outright. It is wrapped in an IMMUTABLE SQL function. Postgres marks array_to_string stable because array text output can depend on the element type’s output function; for text[] that is deterministic, which is what makes the wrapper honest rather than a promise we cannot keep.

The wrapper must not be STRICT. Strict returns NULL if any argument is NULL, and description is nullable — which would silently empty the vector for every game without one. That is all 333.

Definition of done

  • search_vector covers title, designers, mechanics and themes, weighted so a title match outranks a designer match, which outranks a mechanic or theme match.
  • The column is declared in schema.ts; pnpm --filter db check reports no drift, and would now notice its removal.
  • A game with a NULL description still has a non-null vector — asserted, since a STRICT wrapper is the easy mistake and fails silently.
  • Searching a designer, a mechanic and a theme each return sensible games.
  • A typo the FTS cannot reach is still found, and a test proves FTS alone returns zero for it.
  • limit honoured exactly; an empty query returns nothing without issuing a query.
  • Input that would raise in to_tsquery does not raise here.
  • Both indexes proven to exist by plan inspection, not by behaviour — behaviour cannot see a missing index, only latency.
  • Each check proven able to fail by a seeded defect.
  • Gate green.

Verification

Terminal window
pnpm --filter @tabletop/db test
pnpm gate

Notes

ts_rank and similarity are on different scales — ts_rank is typically well under 0.1 for a single hit, similarity runs 0..1 — so the FTS component is multiplied by 10 before they are added. That constant was chosen by looking at this corpus, not derived. It is pinned by a test asserting an exact title wins, so it can be retuned without guesswork.

The migration drops and recreates the generated column, because a generated expression cannot be ALTERed. That is destructive DDL and carries migration:destructive, though nothing is lost: a STORED generated column holds no independent data.