---
name: database-migration-safety-check
description: "Checks a database migration for table locks, backfill risk, reversibility, and deploy ordering so the schema change can ship without downtime. Use when a schema migration is about to run on a live database and needs a check for locks, backfills, reversibility, and deploy order. Do not use for designing the schema itself or moving data between database engines. Related topics: data, devops."
---

# Database Migration Safety Check

Checks a database migration for table locks, backfill risk, reversibility, and deploy ordering so the schema change can ship without downtime.

## When to use

Use this skill when a schema migration is about to run on a live database and needs a check for locks, backfills, reversibility, and deploy order. Do not use it for designing the schema itself or moving data between database engines.

## Workflow

1. Ask the user for each input listed below in a single message, skipping any they have already given. Where options are listed, offer them, but accept other answers too.
2. Read each statement and state the lock it takes and how long it will hold on the largest table.
3. Flag the risky operations, such as volatile defaults, not-null additions, type changes, and non-concurrent index builds.
4. Move large backfills out of the migration into batched jobs with pauses between batches.
5. Reorder the change into compatible steps so old and new application code both work at every moment of the rollout.
6. Write the down migration if required, naming steps that cannot be undone, and add a before and after checklist.

## Inputs

- **The migration** (longer free text)
- **Database** (choose one: Postgres, MySQL, SQL Server, Other)
- **Largest affected table** (free text, e.g. "Name and approximate row count")
- **Traffic** (choose one: Constant, Business hours only, Low, Offline window available)
- **Deploy style** (choose one: Rolling, Blue and green, Stop and start)
- **Rollback needed** (choose one: Yes, No)

## Output

A statement-by-statement risk table, a safer rewritten migration sequence, and a before and after deploy checklist.

## Prompt template

Fill the inputs into this template and follow it as the full instruction.

### Database Migration Safety Check

Check this migration before it runs against production.

- The migration: {{The migration}}
- Database: {{Database}}
- Largest affected table: {{Largest affected table}}
- Traffic: {{Traffic}}
- Deploy style: {{Deploy style}}
- Rollback needed: {{Rollback needed}}

For each statement, state what lock it takes and for how long on a table of that size. Flag adding a column with a volatile default, adding a not-null constraint, changing a type, building an index without the concurrent option, and adding a foreign key without validating separately. Plan large backfills in batches outside the migration transaction, with a pause between batches. Order the rollout in compatible steps (expand, migrate data, switch code, contract) so the old and new application versions both work against the schema at every moment. If a rollback is needed, write the down migration and say which steps cannot truly be reversed, such as dropped columns.

Return a statement-by-statement risk table, a safer rewritten sequence, and a checklist to run before and after the deploy.

## Pitfalls

- Testing the migration on a small dev table where the lock lasts milliseconds, then meeting a ten-minute lock in production.
- Dropping a column in the same release that stops using it, so a rolling deploy crashes the old instances.
- Running a huge backfill inside one transaction, which bloats the log and blocks vacuum.