# Database changes

> Keep old code working while the schema changes.

Engineering safeguards · /migrations/ · 2026-11-05

A code rollback cannot restore a dropped column or undo a backfill.

## Ask the agent to plan the migration

**Prompt**

```text
Review [Jira key, PR URL or diff] for its schema and data changes.
For a Jira ticket, read its description, acceptance criteria and comments through the connected Jira MCP or authenticated acli; ask for pasted details if access is unavailable.
Read the repository's migration and deployment instructions. Propose stages that keep old and new app versions working, with bounded database work and how each stage is undone.
Report the stages, the evidence needed to advance each one, and anything irreversible.
Report which of this page's tests exist in the project's test suite. Add the missing ones that can be automated, and list the rest.
Do not run migrations or backfills.
```

## Change a column across releases

- **MIG-1** (high risk; checked by code review): **Old code must run against the new schema.** Migrate first, then deploy the code that needs it.

  Caution: This has to hold for the whole rolling deploy, while old and new versions run side by side.
- **MIG-2** (high risk; checked by code review): **Never rename or drop in one release.** Expand, switch and contract each ship in their own release; the backfill runs between expand and switch. Drop the old column only once no running code uses it.

For example, replacing `display_name` with `full_name`:

1. **Expand** Add `full_name` and write both columns.
2. **Backfill** Copy old values in resumable batches that never overwrite a newer value, until every row matches.
3. **Switch** Read `full_name`. Keep writing both while rollback is still possible.
4. **Contract** Stop using `display_name` (in Rails, [`ignored_columns`](https://api.rubyonrails.org/classes/ActiveRecord/ModelSchema/ClassMethods.html#method-i-ignored_columns-3D)), then drop it a release later.

- [ ] **MIG-T3**: The previous app version still passes its smoke tests against the new schema.

## Keep each migration safe to deploy

- **MIG-5** (high risk; checked by code review): **Migrations run once per deploy, as their own step.** Never from the app's start-up command.

  Caution: Otherwise every instance and every restart runs the migration path and competes for locks.

* Give migrations a short lock timeout, so one waiting on a busy table gives up instead of blocking every query behind it. [PostgreSQL](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT).
* A migration that runs outside a transaction, such as a concurrent index build, must be safe to rerun after a partial failure. A failed concurrent build leaves an invalid index behind. [PostgreSQL](https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY).
* Run backfills as resumable jobs outside schema migrations.

- [ ] **MIG-T1**: For a large or busy table, run the migration on staging with production-sized data and record its duration and lock behaviour in the release record.

## Prove what deletion will remove

- Preview the exact rows and count, including related rows, files and queued work. Check tenant scope and cascades.
- Decide whether the change hides or permanently deletes, and state what can be restored and from which recovery point.
- Delete large sets in restartable batches, and stop on an unexpected count. Production deletion needs [explicit approval](/security/#r-ai-2).