Skip to content

Database and migrations

Postgres on NeonDB. Every table lives in the web schema and is declared with webSchema.table() — never bare pgTable().

Terminal window
# 1. Edit packages/db/src/schema.ts
bun run db:generate # writes SQL + updates the journal
# 2. READ the generated SQL in packages/db/migrations/
# 3. Merge. CI applies it: push to beta → beta DB, push to main → production DB.

The migrate job in .github/workflows/build.yml runs before either app deploys, on every push to beta and main. With nothing pending it is a read-only check; if it fails, neither app deploys. By hand (same script):

Terminal window
bun run db:status:beta # read-only: applied / pending / stranded
bun run db:migrate:beta # apply to beta
bun run db:migrate # apply to production

Custom SQL that Drizzle cannot infer:

Terminal window
npx drizzle-kit generate --custom

db:migrate reads DATABASE_URL_DIRECT — the non-pooler host — and the script aborts if it is given a -pooler URL.

Two worktrees both generating migration 00NN collide in the journal. Coordinate the number before you generate.

Drizzle applies only migrations newer than the newest one already in the database. A migration generated on an older branch and merged after a newer one has landed is stranded: drizzle-kit would skip it forever, silently. The migrate script (and so CI) fails on it instead. Fix it by regenerating the migration on top of the current beta.

  • updatedAt is not auto-managed. Set updatedAt: new Date().toISOString() in every update.
  • Hold inserts take pg_advisory_xact_lock(hashtext(id)) inside the same transaction as the insert. On the pooled endpoint, a separate execute-then-insert can land on different connections and re-open double booking.
  • bayarcash_transactions and stripe_charges_imported exist in production but not in schema.ts. Read-only.
  • Supabase tooling is for the legacy mobile project only. It never points at the web schema.

The drizzle-schema skill covers adding a table or column end to end.