Database Migrations

The database is a dumb persistence layer. It stores rows and enforces shape. It does not generate values, and it holds no business logic. Every id, timestamp, and default is produced by the domain, so the schema mirrors the domain types and adds nothing of its own. We use Drizzle for the schema and its migrations.

No defaults in the schema. Not defaultRandom(), not defaultNow(), not $onUpdate(), not default(). If the database fills a value in, that value bypassed the domain, and now two places decide what a row means. The entity sets the id and the timestamps when it is created. The column is just a column.

The flow

Schema lives in apps/api/src/db/schema/, migrations in apps/api/drizzle/.

Edit the schema. Change the table definition to match the domain type. Use extensionless imports in schema files (./users.schema, not ./users.schema.js) so drizzle-kit can read them.
Generate the migration. pnpm --filter @systeric/api db:generate. Drizzle diffs the schema against the last migration and writes a new SQL file plus a journal entry.
Read the SQL. Open the generated file and confirm it does what you meant. A generated DROP or a column rename that Drizzle read as drop-then-add is exactly what you want to catch here, before it runs.
Apply it. pnpm --filter @systeric/api db:migrate runs the pending migrations in order.

Migrations are forward-only. Never edit a migration that has already been applied anywhere; the change would never re-run. To fix or undo one, add a new migration.

Two gotchas

CI checks the journal, not the timestamps. db:check (which runs on every PR) verifies that every SQL file has a journal entry, every journal entry has a SQL file, and the indices are sequential. It catches a hand-edited, deleted, or renamed migration. So always commit the generated SQL and the _journal.json change together. It does not check timing.

A new migration must be newer than the last one. The migrations table was re-baselined with synthetic timestamps, so Drizzle applies a migration only when its when is greater than the latest already recorded. Generate normally and this is automatic. But if you resurrect an old branch, or hand-write a migration with a stale timestamp, its when can land below the current head, and db:migrate will silently skip it. The table looks in sync and your change never ran. If a migration does not apply, check its when against the last entry in _journal.json first.

Changing data safely

Additive changes (a new nullable column, a new table, a new index) are two-way doors: ship them freely. Destructive and data-shaping changes are one-way doors and belong in Code Review’s list of changes that need a second set of eyes:

Dropping a column or table loses data. Confirm nothing reads it first, and consider deprecating before deleting.
Backfills run separately. Add the column in one migration, backfill it as its own step, then enforce constraints once the data is in place. One migration that adds a NOT NULL column to a full table and expects it populated will fail.
Renames read as drop-then-add unless written deliberately. Check the generated SQL.

db:studio opens a browser UI on the database when you need to look. db:push syncs the schema without a migration file; it is a local scratchpad only, never a path to production.