ADR-0021: Maintain the shelf rating projection with a database trigger

Status: Accepted
Date: 2026-07-31
Supersedes: —
Superseded by: —

Context

ADR-0006 makes ratings a property of the session, with shelf_entries.rating a projection of the most recent session rating. It states plainly that any write path setting the shelf rating directly is a bug.

That is an invariant, not a preference — it is the thing separating this product from a shelf-first tracker like Goodreads, where a reread overwrites the previous rating and the history of how an opinion changed is destroyed. WI-010 had to choose how it is enforced.

Option	Upside	Downside
Application-layer write	Visible and testable in TypeScript	Bypassable. Every future write path must remember
Computed on read, no stored column	Cannot drift by construction	A join on every shelf read, against a 10ms CPU ceiling (ADR-0001)
Database trigger	Impossible to bypass, including from `psql` or a migration	Product logic lives in the database

Decision

Maintain shelf_entries.rating with a database trigger on sessions, firing on insert, update and delete, recomputing the projection for the affected (user, game) pair.

The trigger is the only writer. Application code never sets shelf_entries.rating.

Rationale

The decisive argument is that the invariant is the product. An application-layer write is correct exactly until someone adds a second write path and forgets — a bulk import, a data fix, an admin tool, an agent working from a spec that does not mention it. That is precisely how Goodreads-style drift happens, and it degrades silently: the data looks plausible and nobody notices for months.

Computed-on-read is attractive because it cannot drift, but shelf reads are on the hot path for collection and profile pages, and every one would pay a join to find the most recent session. Under the free tier’s 10ms CPU budget that is the wrong place to spend.

Putting product logic in the database is a real cost, accepted deliberately here and not as a general licence. The rule is narrow: triggers enforce invariants that ADRs declare; they do not implement features.

Consequences

Drizzle does not model triggers, so this lives in a hand-written custom migration (drizzle-kit generate --custom). It is therefore invisible to Drizzle’s snapshot — meaning pnpm --filter db check will not notice if the trigger is dropped. That gap must be closed by a behavioural test asserting the projection actually updates, not by trusting the schema check. WI-010’s invariant tests cover it.
The trigger must handle deletes: removing the most recent session re-projects to the next most recent, or to null if none remain. A trigger that only handles inserts leaves a stale rating behind.
Writing shelf_entries.rating directly becomes a no-op or an error rather than a silent corruption. WI-010 decides which; an error is preferable, since a silent no-op hides the caller’s bug.
Logic in two languages. Anyone changing the projection must read SQL, and the migration is the source of truth for its behaviour.
Tests for the projection are necessarily database tests — they cannot be unit tests. That is consistent with ADR-0011, which already requires real Postgres.

Revisiting

If the trigger becomes hard to reason about, the sound alternative is computed-on-read plus a cache, not an application-layer write — that option loses the guarantee without buying simplicity.

Alternatives considered

Application-layer write. Visible, debuggable, ordinary TypeScript. Rejected: it is forgettable by construction, and the failure mode is silent data drift discovered long after the fact.

Computed on read. Cannot drift. Rejected on the CPU ceiling — a join on every shelf read is the wrong cost under a 10ms budget.

Generated column. Not possible: Postgres generated columns cannot reference other tables.