Skip to content
Tabletop Tracker

WI-006: Build packages/db

WI-006: Build packages/db

Problem

ADR-0008 makes the Drizzle schema the single source of truth for the data model, and ADR-0011 splits testing between Docker Postgres locally and an ephemeral Neon branch in CI. Neither exists, so WI-010 has nowhere to put the schema.

In scope

  • packages/db with Drizzle and drizzle-kit configured for Postgres.
  • docker-compose.yml running Postgres with pg_trgm and FTS configured, on a pinned version matching Neon’s.
  • Migration commands: generate, apply, check.
  • A seed harness with a transaction-per-test helper that rolls back at teardown.
  • Gate step 2 (schema integrity) wired into CI, replacing the WI-003 placeholder.
  • Gate step 3: Docker locally, ephemeral Neon branch in CI, torn down even on failure.
  • One trivial table purely to prove the pipeline end to end; WI-010 replaces it with the real schema.

Out of scope

  • The real domain schema — that is WI-010, and this item must not pre-empt its modelling decisions.
  • Any query code or repository layer.
  • Hyperdrive wiring, which belongs to apps/api in WI-012.

Definition of done

  • docker compose -f packages/db/docker-compose.yml up -d starts Postgres; SELECT extname FROM pg_extension includes pg_trgm.
  • The Docker Postgres major version matches Neon’s, and the pin is documented in packages/db with the reason.
  • pnpm --filter db migrate applies cleanly to an empty database.
  • pnpm --filter db seed loads without error, and is idempotent on a second run.
  • pnpm --filter db check exits zero on a clean tree.
  • Seeded failures: schema drift, a duplicate journal index, and an orphan migration file each make pnpm --filter db check fail, and the tree is left clean afterwards.
  • Note: drizzle-kit check was found not to detect any of these, so it is not the gate step — see ADR-0020.
  • A database test using the transaction helper leaves no rows behind — verified by running the suite twice and getting identical results.
  • CI provisions a Neon branch, runs migrations plus tests against it, and deletes the branch even when the tests fail (verified by forcing a failure).
  • No local test run requires network access or credentials.

Verification

Terminal window
docker compose -f packages/db/docker-compose.yml up -d
psql "$LOCAL_DATABASE_URL" -c "SELECT extname FROM pg_extension;"
pnpm --filter db migrate
pnpm --filter db seed && pnpm --filter db seed   # idempotent
pnpm --filter db exec drizzle-kit check
pnpm --filter db test && pnpm --filter db test   # no leakage between runs

For the offline check, disconnect the network and confirm pnpm --filter db test still passes.

For the CI teardown check, push a branch with a deliberately failing DB test and confirm via the Neon dashboard that no branch is left behind.

Notes

The teardown-on-failure check is the one most likely to be skipped and the most expensive to get wrong — Neon’s free tier caps branch count, so a leaked branch per failed CI run silently exhausts the quota and then every subsequent run fails for an unrelated-looking reason.

The version pin matters for the same class of reason: a Docker/Neon mismatch is a silent parity bug that only shows up in production.