# Pack: persistence & data model

Load when: the touch list exhibits Drizzle/D1/SQLite (`schema.ts`, `init.sql`, `sqliteTable(`, `drizzle-kit`), Postgres (`pgTable`, migrations, enums, pgvector), Redis caching, seeds (`*.seed.sql`, generators), or JSON columns.

## init-sql-drizzle-mirror-parity — CRITICAL · dual-authority
**Contract:** Hand-written canonical DDL (`db/seed/init.sql`) and the Drizzle mirror (`db/schema.ts`) independently declare the same tables/columns/indexes and must match byte-for-byte — the string in `sqliteTable("…")` and every index name equal the DDL.
**Detect:** `db/seed/init.sql`, `db/schema.ts`, `sqliteTable("`, `uniqueIndex("`, `index("`, `_idx`, `_unique`
**Ships green, breaks:** Each side validates only against itself — local/test DBs are typically built from `schema.ts` (push) while the real D1 is built from `init.sql`, so tests pass and prod 500s (`no such column`). Subtler drift is fully silent: a column present in DDL but missing from the mirror stays NULL forever (Drizzle never writes it); a DEFAULT that differs between the two gives Drizzle-inserted rows and seed rows different values; a drifted index *name* means a later `drizzle-kit push` DROPs the DDL's index and recreates it under the schema name — hand-tuned index definitions vanish.
**Safe change:** Edit both files in the same commit; run `npx drizzle-kit export --dialect=sqlite --schema=db/schema.ts` and diff the emitted DDL against `init.sql` (names, defaults, index names); alternatively apply `init.sql` to a scratch DB and confirm `drizzle-kit push` proposes zero statements; grep every `_idx`/`_unique` name on both sides.

## drizzle-push-vs-migrate-mixing — CRITICAL · generated-artifact
**Contract:** One database gets exactly one schema-application mechanism — either `drizzle-kit push` (stateless diff, schema.ts is total authority) or `generate`+`migrate` (journaled in `__drizzle_migrations`); the two do not compose.
**Detect:** `drizzle-kit push` in `package.json`/CI, `drizzle/` migrations folder, `__drizzle_migrations`, `tablesFilter` in `drizzle.config.ts`, `--force`
**Ships green, breaks:** `push` diffs schema.ts against the *entire* live DB and proposes `DROP TABLE` for anything not in schema.ts — including tables owned by other tools (Better Auth's `user`/`session`/`account`/`verification`, wrangler's `d1_migrations`); `push --force` in CI auto-approves those data-loss statements with no prompt. `push` records nothing in `__drizzle_migrations`, so a later `migrate` replays the whole folder against the already-pushed DB; `generate` diffs against its own snapshot journal, not the pushed DB, so the generated SQL silently disagrees with reality.
**Safe change:** Pick one workflow per database and enforce it in CI; set `tablesFilter` in `drizzle.config.ts` to only the tables Drizzle owns; never pass `--force` unattended; when converting push→migrate, generate a baseline migration and mark it applied before the first `migrate`.

## sqlite-alter-table-recreate — CRITICAL · lifecycle-protocol
**Contract:** SQLite `ALTER TABLE` only does RENAME TO / RENAME COLUMN / ADD COLUMN / DROP COLUMN — any type/NOT NULL/DEFAULT/constraint change makes drizzle-kit emit the rebuild dance (`PRAGMA foreign_keys=OFF` → `CREATE __new_<t>` → `INSERT INTO __new_ SELECT …` → `DROP TABLE` → `RENAME`), whose safety depends on FK state during execution.
**Detect:** `__new_` in `drizzle/*.sql`, `PRAGMA foreign_keys=OFF`, `PRAGMA defer_foreign_keys`, `ON DELETE CASCADE`, `__old_push_`
**Ships green, breaks:** `PRAGMA foreign_keys` is a no-op inside an open transaction — when the migration runs transaction-wrapped, the rebuild's `DROP TABLE` fires `ON DELETE CASCADE` and silently wipes every child table (a single column-default change has deleted all related rows, no error). On D1, `PRAGMA foreign_keys=OFF` is impossible entirely (every query is an implicit transaction); the sanctioned `PRAGMA defer_foreign_keys=on` defers checks but does NOT suppress `ON DELETE CASCADE`. Column-count mismatches in the copy step have left `__old_push_<t>` holding the data and the new table empty.
**Safe change:** Hand-review any generated migration containing `__new_`; on D1 add `PRAGMA defer_foreign_keys = on` as the first statement; back up (D1 Time Travel bookmark) before applying; assert child-table rowcounts after the rebuild.

## seed-regen-upsert-clobber — CRITICAL · dual-authority
**Contract:** Generated seed files (`db/seed/*.seed.sql`, UUIDv5 of the natural key, idempotent `ON CONFLICT(slug) DO UPDATE`) and the live DB co-own vocabulary rows — the generator owns `slug/label/description`, the runtime owns `count`, admins own `weight`.
**Detect:** `gen-seeds.mjs`, `gen-product-domains.mjs`, `*.seed.sql`, `ON CONFLICT(slug) DO UPDATE`, `excluded.count`, `excluded.weight`, `excluded.id`
**Ships green, breaks:** If the generator's `DO UPDATE SET` list ever includes `count`/`weight`/`id`/`created_at`, the next deploy's re-seed silently resets live drift counters and admin-tuned weights to CSV values — no error, data just reverts. Changing the uuid5 namespace or the natural-key string format re-mints every id: the slug-targeted upsert still matches, but `SET id = excluded.id` dangles every external record of the old UUIDs; if the conflict target were `id` instead of `slug`, re-seed duplicates the whole vocabulary. Mixing UUIDv7 (runtime, time-sortable) and UUIDv5 (seed, hash) ids also means `ORDER BY id` ≠ insertion order.
**Safe change:** Edit the CSV and rerun the generator, never the `.seed.sql`; diff the regenerated file for id churn before committing; keep `DO UPDATE SET` restricted to label/description/status and always exclude `count`, `weight`, `id`, `created_at`; treat the uuid5 key-format string as frozen API.

## pg-on-conflict-arbiter-index — CRITICAL · config-elsewhere
**Contract:** Every upsert's conflict target is backed by a live unique (or partial-unique) index declared in a migration far from the query; dedupe correctness lives in the index, not the code.
**Detect:** `onConflictDoUpdate`, `onConflictDoNothing`, `ON CONFLICT`, `INSERT OR IGNORE`, `uniqueIndex(`, `targetWhere`, `_unique`
**Ships green, breaks:** Targeted `ON CONFLICT (col) DO UPDATE` at least errors loudly if the constraint is dropped — but targetless `ON CONFLICT DO NOTHING` and SQLite `INSERT OR IGNORE` keep returning success and silently insert duplicates once the unique index is gone (e.g. a rebuild migration recreated the table with `_idx` instead of `_unique`). Partial unique indexes need the predicate restated for arbiter inference — in Drizzle, `onConflictDoUpdate({ target, targetWhere })` must repeat the index's `WHERE`; omit it and PG throws `there is no unique or exclusion constraint matching the ON CONFLICT specification` even though "the index exists".
**Safe change:** Before dropping/renaming any `_unique` index, grep for upserts naming those columns; prefer explicit conflict targets over targetless DO NOTHING; for partial indexes, mirror the predicate in `targetWhere`; after SQLite rebuild migrations, verify uniques survived (`PRAGMA index_list(<table>)`).

## d1-batch-not-a-transaction — HIGH · lifecycle-protocol
**Contract:** Code needing multi-statement atomicity on D1 must use `db.batch([...])` — D1 has no interactive transactions; `BEGIN`/`SAVEPOINT` are rejected and Drizzle's `db.transaction()` fails at runtime on the D1 driver.
**Detect:** `db.transaction(`, `BEGIN TRANSACTION`, sequential `await db.insert(…).run()` chains, `env.DB.batch`, `db.batch(`
**Ships green, breaks:** Sequential awaited writes read as transactional in review but each auto-commits — a mid-sequence failure leaves permanent partial writes with no rollback. `batch()` is atomic (a failing statement rolls back the sequence) but non-interactive: a later statement cannot branch on an earlier statement's result, so read-modify-write logic ported into a "batch transaction" carries a TOCTOU race between the pre-read and the batch. Code sharing a service layer with a Node/Postgres path (where `db.transaction` works) compiles and unit-tests green, then throws `D1_ERROR` only on the Workers runtime.
**Safe change:** Express each multi-write invariant as one `db.batch([...])`; hoist all reads before the batch and encode invariants as SQL conditions (`WHERE`-guarded UPDATE) instead of JS branches; never let `db.transaction` reach a D1 code path.

## d1-foreign-keys-always-on — HIGH · config-elsewhere
**Contract:** D1 enforces `PRAGMA foreign_keys = on` for every query and migration and does not allow turning it off (only `PRAGMA defer_foreign_keys` within a transaction), while local SQLite harnesses (better-sqlite3 et al.) default foreign_keys OFF.
**Detect:** `better-sqlite3`, `new Database(`, missing `pragma('foreign_keys = ON')` in test setup, `PRAGMA foreign_keys = OFF` in `migrations/*.sql`, `defer_foreign_keys`
**Ships green, breaks:** Tests that insert children before parents, delete referenced parents, or import fixtures out of order pass locally (orphans silently accepted) and then throw `FOREIGN KEY constraint failed` only on production D1. Conversely, orphan rows accumulated under local FK-off dev later block a table-rebuild migration on D1. A migration file containing `PRAGMA foreign_keys = OFF` is silently ineffective on D1 — and the sanctioned `defer_foreign_keys = on` still lets `ON DELETE CASCADE` fire.
**Safe change:** Enable `foreign_keys = ON` in every local SQLite test/dev harness; order seed and migration statements parent-first; use `PRAGMA defer_foreign_keys = on` (not `foreign_keys = OFF`) in D1 migrations that transiently violate FKs.

## d1-session-bookmark-consistency — HIGH · lifecycle-protocol
**Contract:** With D1 read replication enabled, cross-request read-your-writes holds only if `session.getBookmark()` is round-tripped (cookie/header) into the next request's `env.DB.withSession(bookmark)`; the constant `"first-unconstrained"` permits arbitrarily stale replica reads.
**Detect:** `withSession(`, `getBookmark(`, `first-primary`, `first-unconstrained`, `read_replication` in `wrangler.jsonc`/dashboard
**Ships green, breaks:** Enabling replication and adopting `withSession("first-unconstrained")` makes POST-then-redirect-GET flows intermittently render pre-write state — only for users near a lagging replica, never reproducible in local dev (no replicas exist there). Sessions guarantee *sequential* consistency within one session object; two independent sessions with no bookmark hand-off share no ordering at all. Non-session `env.DB.prepare()` calls still route to the primary — a partial migration to Sessions creates mixed-consistency paths inside one handler.
**Safe change:** Return the bookmark to the client after every write and feed it into the next request's `withSession()`; default to `"first-primary"` for flows that read after writing; keep a write and its dependent reads on the same session object.

## slug-rendezvous-vocabulary-drift — HIGH · rendezvous-string
**Contract:** `slug` is the org's cross-reference key — enforced refs are real FKs `REFERENCES <table>(slug)`, but drift-tolerant vocabulary columns deliberately have NO FK (paired with a `<thing>_in_taxonomy` boolean), and the vocabulary's source of truth is the taxonomy package, not the D1 snapshot table.
**Detect:** `REFERENCES sources(slug)`, `_slug` columns without `FOREIGN KEY`, `_in_taxonomy`, `taxonomy/seed/*.csv`
**Ships green, breaks:** Renaming a slug in the taxonomy CSV regenerates the seed, and the `ON CONFLICT(slug)` upsert *inserts a new row* under the new slug instead of renaming — the old row survives, every existing `_slug` value still points at the now-deprecated slug, joins quietly return empty sets, and `_in_taxonomy` flags go stale. Nothing errors: the columns have no FK by design. FK-enforced references fail loudly instead — but only at write time, long after the rename shipped.
**Safe change:** Treat slugs as immutable once referenced — change `label`, never `slug`; if a rename is unavoidable, ship an `UPDATE` of every referencing `_slug` column plus old-row retirement in the same migration; recompute `_in_taxonomy` flags afterward.

## sqlite-text-timestamp-format — HIGH · serialized-shape
**Contract:** Every writer of `created_at`/`updated_at` (`TEXT NOT NULL DEFAULT (datetime('now'))`) agrees on the format that default emits: `YYYY-MM-DD HH:MM:SS`, UTC, space separator, no `T`/`Z`/milliseconds.
**Detect:** `datetime('now')`, `toISOString()`, `$onUpdate`, `text("created_at")`, `new Date(` on timestamp columns
**Ships green, breaks:** App code writing `new Date().toISOString()` (`2026-07-04T09:00:00.000Z`) into the same column creates mixed formats: TEXT comparison is bytewise, `' '` < `'T'`, so `ORDER BY created_at` and range filters silently drop or misorder rows across the two formats. Reading back, `new Date("2026-07-04 09:00:00")` parses as *local* time in JS while the stored value is UTC — silent per-timezone offsets. And SQLite has no `ON UPDATE`: Drizzle's `$onUpdate` fires only on Drizzle-issued updates, so seed upserts and raw SQL leave `updated_at` stale unless they SET it explicitly.
**Safe change:** Standardize on the DDL default's format via one shared formatter for app-side writes; when parsing, restore `T`/`Z` (`s.replace(' ','T') + 'Z'`); include `updated_at = datetime('now')` in every raw UPDATE and every seed `DO UPDATE SET`.

## sqlite-affinity-boolean-mapping — HIGH · serialized-shape
**Contract:** All writers store literal `0`/`1` in `is_*`/`has_*` INTEGER columns, because Drizzle's `integer(…, { mode: "boolean" })` deserializes with exactly `Number(value) === 1`.
**Detect:** `{ mode: "boolean" }`, `is_` / `has_` columns in `init.sql`, quoted booleans `'true'`/`'TRUE'` in seed SQL, `typeof(` audits
**Ships green, breaks:** SQLite INTEGER affinity rejects nothing — a seed or raw SQL writing `'true'` (quoted) stores the TEXT `'true'` inside the INTEGER column; `Number('true')` is `NaN`, so Drizzle silently reads the row as `false`. Any stored value other than exactly `1` (a `2`, a `-1`, `'yes'`) also reads as `false`. Unquoted `TRUE`/`FALSE` keywords are safe (they are 1/0), which makes the quoted variant an easy copy-paste landmine; D1 tables aren't STRICT, so nothing validates at write time.
**Safe change:** Write literal `1`/`0` in all hand-written SQL; audit with `SELECT typeof(is_col), is_col, COUNT(*) FROM t GROUP BY 1,2`; ensure every `is_*`/`has_*` column carries `{ mode: "boolean" }` in `schema.ts`.

## json-column-shape-versioning — HIGH · serialized-shape
**Contract:** Readers of a JSON column (`TEXT` holding JSON in SQLite — `design_tokens`, `bbox`, `scorecard`, `meta` — or `jsonb` in PG) must parse every shape ever written; old rows keep their old shape forever, and Drizzle's `{ mode: 'json' }` + `.$type<T>()` is a compile-time assertion never validated at read.
**Detect:** `mode: 'json'`, `.$type<`, `jsonb(`, `JSON.parse(` on column values
**Ships green, breaks:** Adding a required field to `T` type-checks green while every pre-existing row lacks it — `undefined` propagates into arithmetic (NaN scores) and skipped branches with no exception; renaming a field silently orphans all historical data under the old key; during a rolling deploy, new-shape writes crash still-running old readers. Tests pass because fixtures are always written by the current code.
**Safe change:** Make every added field optional with a read-time default, or embed a `v` version field and branch on it; backfill a migration before making a field required; validate at the boundary with a zod schema instead of trusting `$type`.

## pg17-enum-lifecycle — HIGH · lifecycle-protocol
**Contract:** A Postgres enum's value list and declaration order are shared DDL across all writers, readers, and in-flight migrations; PG (through 17) has no `ALTER TYPE … DROP VALUE` — removal requires a full rebuild (columns→text, drop type, recreate, cast back).
**Detect:** `pgEnum(`, `ALTER TYPE`, `ADD VALUE`, `USING (`…`::text::`, exhaustive `switch` on enum unions
**Ships green, breaks:** `ADD VALUE` runs fine inside a transaction (since PG12) but *using* the value in the same transaction throws `unsafe use of new value … of enum type` (55P04) — this bites exactly the migration that adds a value and then seeds rows with it, and only at deploy time. Enum `ORDER BY` follows declaration order, so appending without `BEFORE`/`AFTER` silently reorders sorted output. Mixed-version deploys: new pods write the new value while old pods' exhaustive switches receive an unknown variant. The removal rebuild fails mid-migration if any row still holds the removed value.
**Safe change:** Split ADD VALUE and its first use into separate migrations (separate transactions); use `BEFORE`/`AFTER` when sort order matters; deploy readers tolerant of the new value before writers emit it; before removal, count rows holding the value and review the generated rebuild SQL.

## pgvector-opclass-operator-match — HIGH · rendezvous-string
**Contract:** The query's distance operator, the index's operator class, and the embedding model/dimension must agree: `<->`↔`vector_l2_ops`, `<=>`↔`vector_cosine_ops`, `<#>`↔`vector_ip_ops`, column `vector(N)` fixed to the model's output size.
**Detect:** `vector_cosine_ops`, `vector_l2_ops`, `vector_ip_ops`, `<=>`, `<->`, `<#>`, `vector({ dimensions:`, `.op('vector_`, `USING hnsw`, `USING ivfflat`
**Ships green, breaks:** Operator/opclass mismatch produces no error or warning — Postgres just seq-scans (≈5ms indexed → 30s at 1M rows); the only tell is `EXPLAIN` showing Seq Scan. In Drizzle the rendezvous is the literal `.op('vector_cosine_ops')` string on the index vs the `sql`-template operator in a query file that never imports it. Swapping embedding models at the *same* dimension inserts cleanly and returns confidently wrong neighbors — cross-model similarity is meaningless; a different dimension at least errors.
**Safe change:** Grep every distance operator and match it to an index opclass; `EXPLAIN` the hot query after any index or query change; record the embedding model per row (or per table) and re-embed the entire table on model change — never mix.

## redis-key-ttl-serialization-versioning — HIGH · serialized-shape
**Contract:** Cache writers and readers across deploys rendezvous on literal key strings and an agreed value encoding; Redis outlives every deploy, so pre-deploy keys, shapes, and TTLs keep serving after the code changes.
**Detect:** key-template literals (`` `user:${id}` ``), `JSON.parse(` on `GET` results, `set(` without `EX`/`KEEPTTL`, `maxmemory-policy`, hardcoded prefixes in more than one module
**Ships green, breaks:** Renaming a key schema without invalidation leaves old keys serving stale data to any code path still reading the old name — both paths "work". Changing the value shape breaks only on cache *hits*: cold-cache tests and fresh staging pass, production readers throw or mis-parse on surviving pre-deploy entries. Plain `SET key val` (Redis ≥6) silently *removes* an existing TTL unless `KEEPTTL` or `EX` is given — a refresh path without `EX` turns expiring keys immortal. Default `maxmemory-policy noeviction` means a full instance rejects writes (`OOM command not allowed`) instead of evicting.
**Safe change:** Embed a schema version in the key prefix (`v2:user:{id}`) and bump it with any shape change — old entries expire naturally; always pass `EX`/`PX` (or `KEEPTTL`) on every SET; treat deserialization failure as a cache miss, never an error; set `allkeys-lru` (or similar) for cache workloads.