Database migration safety

Nautilo uses Drizzle migration files as committed history and drizzle.__drizzle_migrations as the database record of what actually ran. Those states are related, but they are not interchangeable. A clean Git merge does not prove that a long-lived local database can safely run the merged migration sequence.

This decision tree is for developers synchronizing branches or resolving a migration collision.

Treat the migration record as one system

Inspect all four parts:

1. packages/db/src/migrations/NNNN_<name>.sql;
2. packages/db/src/migrations/meta/_journal.json;
3. packages/db/src/migrations/meta/NNNN_snapshot.json; and
4. the target database's drizzle.__drizzle_migrations rows.

drizzle-kit generate compares the TypeScript schema with the last committed snapshot, not with the live database. drizzle-kit migrate uses the committed migration files and the database ledger to determine what should run.

Before synchronizing a schema branch

Answer this first:

Has this branch's incoming migration already been applied to the database I intend to keep?

If the answer is unknown, stop. Inspect the SQL, journal, snapshot chain, database ledger, and resulting schema before choosing a repair. Do not infer the answer from whether the application currently starts.

For a local instance named feature-lab, first confirm its identity and exact Postgres container:

bun run infra:status -- --instance feature-lab

For a standard named instance, the reported application-database container is nautilo-feature-lab-postgres. Read its migration ledger without changing it:

docker exec nautilo-feature-lab-postgres \
  psql -U postgres -d nautilo \
  -c 'SELECT id, hash, created_at FROM drizzle.__drizzle_migrations ORDER BY id;'

Replace both occurrences of feature-lab together when using another reviewed named instance. If infra:status reports a different container, stop and use the reported identity rather than guessing.

The incoming migration has not been applied

Regenerate it on top of the updated parent history:

1. Back up the incoming SQL so you can compare its intent later.

2. Use the parent branch's SQL, journal, and snapshot chain as the base.

3. Remove the unapplied generated migration and its metadata from the incoming side.

4. Regenerate against the merged schema:

   bun run --cwd packages/db db:generate --name <descriptive-name>

5. Compare the regenerated SQL with the backup.

6. Stop if the regenerated migration is empty or materially changes the intended DDL. The updated parent may already implement the change or may invalidate the original assumption.

7. Apply the result to an isolated local instance and validate the chain:

   bun run --cwd packages/db db:check

Regeneration is the normal path because it creates the next migration from the new parent snapshot.

The incoming migration has already been applied

Do not regenerate it and do not edit its SQL body. The retained database has already executed that DDL.

1. Rename the incoming SQL to the next free migration number.
2. Keep its SQL body byte-for-byte unchanged.
3. Move its journal entry to the new contiguous idx; keep tags unique.
4. Chain its snapshot after the new current parent snapshot.
5. Validate the repository migration chain.
6. Separately inspect the retained database's drizzle.__drizzle_migrations row before running db:migrate again.

The repository can now be coherent while the retained database still records the old journal timestamp and hash relationship. Repairing that ledger or resetting/restoring the database is a separate operator decision. Neither is safe to guess or automate.

Both branches changed the same schema intent

Do not mechanically retain both migrations. Compare the resulting schema and decide whether one migration supersedes the other, a new reconciliation migration is required, or the incoming change should be dropped. An empty regenerated migration is evidence to investigate, not permission to silently delete work.

The shared default database is already divergent

Stop repeatedly running dev-stack; it invokes db:migrate again and will fail the same way.

Preserve the database, inspect the exact SQL hashes and schema shape, and choose one explicitly:

• reconcile the local ledger only when the applied SQL is proven identical and the operator approves;
• restore a known-good backup; or
• leave that database intact and use an isolated named instance whose starting migration lineage is known. A clone of default preserves its ledger; a fresh instance proves the migration chain from an empty database. Choose the one that answers the actual question.

Never reset, rewrite, or directly edit the default database ledger as an automatic conflict-resolution step.

Forbidden shortcuts

• Do not use drizzle-kit push or bun db:push; Nautilo uses generated, reviewed migrations.
• Do not edit or delete the SQL body of an already-applied migration.
• Do not mutate drizzle.__drizzle_migrations without explicit approval.
• Do not run a repair script without reading it and proving that it covers the current migrations.
• Do not assume matching migration numbers mean matching SQL.
• Do not assume a successful application boot means migration history is coherent.

Validate before continuing

bun run --cwd packages/db db:check
python3 -m json.tool packages/db/src/migrations/meta/_journal.json >/dev/null

Also verify:

• migration numbers and tags are unique;
• journal idx values are contiguous;
• every snapshot points to the intended previous snapshot;
• no conflict markers remain in SQL or metadata;
• regenerated SQL still implements the reviewed schema intent; and
• focused database tests and typecheck pass.

Only then apply the result to an isolated instance. Upgrade testing against a long-lived default database should be deliberate and backed up.

Apply through the named instance orchestrator, not a bare db:migrate with an instance ID environment variable:

bun run infra:start -- --instance feature-lab

infra:start resolves the instance connection, reads the database ledger, requires it to be an exact prefix of the checkout lineage, and only then runs db:migrate. NAUTILO_INSTANCE_ID alone is not a database connection. If the lineage preflight fails, do not edit ledger rows; reconcile with a forward migration or use a checkout whose journal contains the running lineage.

For the isolated and retained-instance run paths, see Run Nautilo from source.

Related scenario

See Reconcile a migration without sacrificing the developer database for the same decision as an outcome-led workflow with explicit failure and recovery boundaries.