schema migrations

• applied migrations = the shared diary every environment replays to reach the current schema.
• rewriting one makes local history diverge from what production already executed.
• forward-only history keeps the deployed database and source schema provably in sync, and data evolution traceable.
• a rewritten migration turns simple schema drift into a blocked deployment and a manual reconciliation incident.

schema history is forward-only evidence of how authoritative data changed:

• change the owned schema
• generate the migration
• review the concrete SQL and data assumptions
• apply through the deployment path
• verify the live boundary
• never rewrite an applied migration to make history look cleaner

one migration creates a process_step_record and adds trace columns to prior event rows:

• backfills trace columns from existing JSON payload before the new index is used
• new table carries state, storage, attempt, timing, and uniqueness constraints — later process retries and UI reads depend on those facts

the order is the point:

1. preserve/backfill existing evidence
2. create the new durable fact with invariants
3. add indexes that serve the new read paths

safer and more explainable than rewriting old migration files or relying on an application deploy to silently repair historical state.

1. update the owning schema and its specification first.
2. generate—not hand-write where the project owns a migration generator—the migration.
3. read every statement for authority, data loss, lock/size risk, backfill, constraints, indexes, and reverse behavior.
4. apply through the project's owned deployment path in order.
5. query the live schema and representative existing/new rows to prove the invariant, not merely that the migration command exited successfully.
6. record commands, timestamps, and evidence in the slice or execution ledger.

• edit an applied historical migration.
• ship a schema change without reviewing its generated SQL.
• add a required constraint before reconciling existing rows.
• add indexes because a table feels important rather than for a known query.
• hide irreversible loss behind a compatibility fallback or an undocumented deployment step.

bad

-- local migration 0007_create_workflow_run.sql (already applied to production on 2026-08-20)
-- original content:
-- create table workflow_run (
--   id text primary key,
--   workflow_id text not null references workflow(id),
--   status text not null
-- );

-- developer realizes they need completed_at and rewrites the file:
alter table workflow_run add column completed_at text;

the developer rewrites the applied migration to "clean up" local history:

1. production already has the old schema without completed_at
2. the next deployment generates a new migration from the rewritten schema
3. that migration runs against a database that already has the column from a different 0007 migration — "column already exists" error
4. deployed database and source schema are out of sync; deployment blocked

to debug, teams must query production directly, read git history to find the rewrite, and manually reconcile the state — simple schema drift becomes an incident.

good

-- 0007_create_workflow_run.sql (unchanged, applied to production)
create table workflow_run (
  id text primary key,
  workflow_id text not null references workflow(id),
  status text not null
);

-- 0008_add_workflow_run_completed_at.sql (new, generated from schema, applied next)
alter table workflow_run add column completed_at text not null default '';

the new migration is generated from the updated schema, reviewed for existing rows and default values, then applied and verified through the deployment path:

• every environment follows the same immutable sequence; production and source schema stay in sync
• future developers reading migrations see the exact order changes happened and can trace data evolution

database design · table naming · deployment and UAT · durable process-step record