Skip to content
Tabletop Tracker

ADR-0008: Drizzle as query layer and schema source of truth

ADR-0008: Drizzle as query layer and schema source of truth

  • Status: Accepted
  • Date: 2026-07-30
  • Supersedes: —
  • Superseded by: —

Context

ADR-0003 commits to Postgres and ADR-0004 to a standard pg-family driver. Above that driver sits a choice: ORM, query builder, or raw SQL. This blocks the very first schema work item (WI-010).

Three things must stay in lockstep or the project rots: the database schema, the TypeScript types, and the Zod validation schemas that packages/shared exports to web and mobile.

Decision

Use Drizzle with drizzle-kit for migrations, and derive Zod schemas via drizzle-zod into packages/shared.

The Drizzle schema definition in packages/db is the single source of truth for the data model.

Rationale

  • Schema-as-TypeScript gives one source of truth rather than three that drift.
  • drizzle-kit generate produces migrations from schema diffs, and drizzle-kit check detects drift and conflicting migration ordering — a gate step agents cannot talk their way past.
  • drizzle-zod derives the Zod schemas that ADR-0002 makes necessary, directly from the tables. Clients cannot drift from the database without the typecheck failing.
  • The raw sql template escape hatch covers Postgres FTS and pg_trgm fully, so ADR-0003’s capabilities remain reachable.
  • Runs cleanly on Workers with a modest bundle footprint, which matters under the 10ms CPU ceiling.

Consequences

  • Migrations are generated, then reviewed by a human — generated does not mean correct. Destructive DDL requires the migration:destructive label (see docs/standards/ci-and-gates.md).
  • Hand-written SQL is still expected for the FTS and discovery queries; that is the intended use of the escape hatch, not a workaround.
  • packages/shared must never hand-define a type that drizzle-zod could derive.

Alternatives considered

Kysely. Sharper, more honest SQL and excellent inference. Rejected: no schema source of truth, so every migration is hand-written and types are maintained separately from the database — more rope for an agent to get migration ordering wrong.

Raw SQL + pg. Total control and the smallest bundle. Rejected: hand-rolled migrations, types and Zod schemas are three artefacts that can silently diverge, which is precisely the failure this project’s gating is designed to prevent.