DB migrations & package-owned tables (Drizzle + NuxtHub + D1)

Most tables are app collections — use the crouton skill/CLI, not this one. Edit apps/<app>/schemas/*.json, ensure it's in crouton.config.js, run crouton config. That generates the layer + Drizzle schema. This skill is for the two things the CLI does not do:

1. The migrate step — turning a schema change (even a CLI-generated one) into a committed Drizzle migration, including the gotcha that makes pnpm db:generate fail on a clean tree.
2. Package-owned infra tables — non-collection tables shipped by a package (flow_configs, yjs_collab_states, sales_sync_outbox) that are hand-defined and never touch crouton config.

If you're adding a user-facing CRUD collection, stop and use the crouton CLI.

The pipeline (how a table reaches the DB)

Since #1445 WS2 there are two shapes. Which one an app is on is decided by its drizzle.config.ts — check that file first, the rest follows from it.

Current (CLI-owned resolver) — no Nuxt in the loop. New scaffolds, apps/velo, apps/kassa:

schema.ts (app layer OR package)            ← Drizzle table definitions
   └─ resolveSchemaSourcesSync(process.cwd())     ← walks the layer graph directly
        (the same set NuxtHub globs, resolved at drizzle-kit runtime — NO build)
   └─ drizzle-kit generate                        → server/db/migrations/sqlite/NNNN_*.sql
                                                    + meta/_journal.json + meta/NNNN_snapshot.json
   └─ wrangler d1 migrations apply (db:migrate*)  → applies the .sql to D1 (local/remote/staging)

Measured on a cold scaffold (#1457): crouton config 0.8s, pnpm db:generate 1.8s, pnpm dev boots in ~6s. No nuxt build, so the build-first gotcha below cannot occur.

Legacy (bundled .mjs) — no app in this repo is on it any more (#1456 was the last). Kept because an EXTERNAL consumer on an older scaffold still hits it. This is the world the build-first recipe further down exists for:

schema.ts → NuxtHub scans layers → writes the bundle at BUILD time:
             .nuxt/hub/db/schema.entry.ts            (after `nuxt prepare`)
             …/.nuxt/hub/db/schema.mjs               (ONLY after `nuxt build`)
   └─ drizzle-kit generate  (reads the .mjs bundle)  → …/NNNN_*.sql + meta/
   └─ wrangler d1 migrations apply (db:migrate*)     → applies to D1

Both runtimes are SQLite (local better-sqlite3/libSQL via node-server, D1 on Cloudflare), so one schema + one set of migrations serves both — never write D1-only or node-only SQL in a migration.

Key paths (per app, e.g. apps/kassa):

• drizzle.config.ts — the tell for which shape you're on. Current apps call resolveSchemaSourcesSync(process.cwd()) (no .nuxt, no build). Legacy apps point schema at the bundled file, trying .nuxt/hub/db/schema.mjs then node_modules/.cache/nuxt/.nuxt/hub/db/schema.mjs. Either way out: server/db/migrations/sqlite.
• server/db/migrations/sqlite/ — generated NNNN_*.sql + meta/ (journal + per-migration snapshots). All committed. wrangler applies the .sql; drizzle-kit needs meta/ to diff the next migration.
• package.json scripts: db:generate (drizzle-kit), db:migrate (wrangler, --local), db:migrate:prod / db:migrate:staging (--remote), db:seed* (crouton-seed). NB: never run npx nuxt db generate from repo root.

Squashing a migration history — run the schema diff FIRST (required)

A squash regenerates the baseline from the schema source. A hand-written corrective migration that fixed the database but was never reflected back into that source is therefore silently reverted.

This is not hypothetical. kassa's 0019_nullable_printer_location made sales_printers.locationId nullable, healing a 0016 regression. The schema source still said .notNull(), so the #1455 squash brought the bug straight back — caught only when the production restore died on real rows:

NOT NULL constraint failed: sales_printers.locationId

A table-NAME diff cannot see this — the table exists on both sides. Compare columns:

# concatenate the OLD history in order, then diff against the regenerated baseline
for f in $(git show HEAD:apps/<app>/server/db/migrations/sqlite | grep '\.sql$'); do
  git show HEAD:apps/<app>/server/db/migrations/sqlite/$f
done > /tmp/old_history.sql

node scripts/schema-diff.mjs /tmp/old_history.sql \
  apps/<app>/server/db/migrations/sqlite/0000_*.sql \
  --live <the wrangler d1 export dump>

Exit 0 = a data-only restore should load cleanly. Exit 1 = it will fail; fix the schema source and regenerate before touching any database.

Always pass --live. The migration files can have drifted from the database that actually exists; the live dump is what governs the restore. Without it the script can only flag the shape of the problem, not confirm it.

Restoring afterwards: data only — never the old d1_migrations rows (that silently un-does the squash) — and insert in foreign-key order, because D1's export is alphabetical, so account precedes user and a naive reload dies on a constraint.

Adding an APP collection (the common case)

Use the crouton skill / CLI — don't hand-write the table. Edit the schema JSON (apps/<app>/schemas/*.json), make sure it's in crouton.config.js (collections + targets), run crouton config to regenerate the layer, then generate + apply the migration (see "Generate the migration" below).

Adding a PACKAGE-OWNED infra table (the crouton-flow pattern)

For a table that ships with a package and should appear in every consuming app (an outbox, a config table, a CRDT store) — NOT a user-facing CRUD collection. Mirror packages/crouton-flow exactly:

1. packages/<pkg>/server/database/schema.ts — define the table with sqliteTable(...) from drizzle-orm/sqlite-core. This is the source of truth.
2. packages/<pkg>/server/db/schema.ts — one-line re-export: export { myTable } from '../database/schema'. NuxtHub scans server/db/schema.ts across extended layers to build the bundle, so this is what makes consuming apps auto-discover the table (confirm: .nuxt/hub/db/schema.entry.ts lists your package after a nuxt prepare).
3. packages/<pkg>/server/database/migrations/NNNN_*.sql — hand-write the CREATE TABLE IF NOT EXISTS … + indexes. This is the package's own copy (for direct apply / docs); the consuming app still generates its own numbered migration in step "Generate the migration".
4. Editing packages/ is behind a hard gate — get user approval, then echo '<pkg-name>' >> .claude/.package-edit-approved, edit, and rm .claude/.package-edit-approved when done. (See root CLAUDE.md.)
5. Document it in packages/<pkg>/CLAUDE.md (Key Files + a table/section).

Naming: package tables use the package's prefix (flow_*, sales_*); column names in package tables follow that table's own convention (flow uses snake_case).

The consuming app needs NO CLI step for a package table. The crouton CLI (crouton config) has zero migration logic and is not involved here — don't add the table to the app's crouton.config.js. NuxtHub auto-discovers it from the package's server/db/schema.ts re-export; the app's only job is the normal migrate step below (db:generate auto-includes it, then db:migrate) — the same step any schema change needs. (A package may also ship its own .sql for direct apply via wrangler d1 execute --file=node_modules/@fyit/<pkg>/server/database/migrations/<file>.sql.)

Generate the migration

On a current app (resolver drizzle.config.ts) there is no gotcha — the whole section below does not apply. pnpm --filter <app> db:generate resolves the layer graph itself and writes the migration in ~2s:

pnpm --filter <app> db:generate   # → server/db/migrations/sqlite/NNNN_*.sql + meta/
pnpm --filter <app> db:migrate    # apply locally

One behaviour worth knowing (#1457): on a cold scaffold crouton config does not produce the migration in the same run — it prints ↻ Migration deferred — resolve the app's layers first, then re-run: pnpm db:generate and exits 0. That is the designed soft-defer (reason: 'deferred', see packages/crouton-cli/CLAUDE.md), not a failure. Run db:generate and carry on.

The build-first gotcha — LEGACY scaffolds only (no app in this repo, since #1456)

Applies only where drizzle.config.ts still points at the bundled .mjs. pnpm --filter <app> db:generate reads .nuxt/hub/db/schema.mjs. On a freshly cloned / prepared tree that file does not exist yet — nuxt prepare only writes schema.entry.ts; the .mjs bundle is emitted during nuxt build. Two failure modes you'll hit:

• Error No schema files found for path config ['.nuxt/hub/db/schema.mjs'] → the bundle hasn't been built.
• Pointing drizzle-kit at schema.entry.ts directly fails with ERR_PACKAGE_PATH_NOT_EXPORTED … not defined by "exports" — drizzle-kit can't resolve the cross-package aliases (e.g. crouton-auth subpaths) that only Nuxt's bundler rewrites. Don't go down this path.

The fix — build once to emit the bundle, then generate:

cd apps/<app>
# Emit the schema bundle. node-server preset is fine; the bundle is written
# early (before the slow Nitro stage), so you can stop the build once it exists.
NITRO_PRESET=node-server nuxt build &
# wait until the bundle appears, then stop the build:
until [ -f .nuxt/hub/db/schema.mjs ] \
   || [ -f node_modules/.cache/nuxt/.nuxt/hub/db/schema.mjs ]; do sleep 2; done
pkill -f "nuxt build"

# Sanity: confirm your new table is in the bundle
grep -l "<yourTableName>" node_modules/.cache/nuxt/.nuxt/hub/db/schema.mjs .nuxt/hub/db/schema.mjs 2>/dev/null

pnpm db:generate            # → server/db/migrations/sqlite/NNNN_*.sql (+ meta updates)

Then inspect the generated .sql — it should contain ONLY your change. If it includes unrelated drift, the committed meta/ snapshots were stale; reconcile before committing (don't blindly ship the drift).

Apply locally: pnpm db:migrate (wrangler --local). Commit the .sql, the new meta/NNNN_snapshot.json, and the updated meta/_journal.json together with the schema change.

Validate without booting Nuxt (fast check)

To prove a migration's SQL is valid and a table accepts the rows your code writes, run the migration against an in-memory DB with the repo's better-sqlite3 (run the script from inside the repo so node_modules resolves):

cd /path/to/repo-root   # so imports resolve
node ./.check.mjs        # import Database from 'better-sqlite3'; db.exec(readFileSync(<migration.sql>)); insert + query
rm ./.check.mjs

Gotchas / rules

• hub: { db: 'sqlite' } in nuxt.config — never database: true (breaks the schema entry + local migrations). See root CLAUDE.md.
• Migrations are append-only and committed — never edit an applied NNNN_*.sql; add a new one.
• meta/ is required for correct future diffs — always commit it with the .sql.
• Drizzle $default/$onUpdate are app-level defaults; they do NOT emit SQL DEFAULT clauses. If a column must have a value when written outside your code, set the SQL default in the migration (the package's hand-written .sql can; the drizzle-generated app .sql won't). Keep your insert code setting the values explicitly so both paths agree.
• Typecheck after: pnpm -r --filter './apps/*' typecheck (or --filter <app>).