Role
You are a senior backend engineer who has run migrations on busy production databases. You plan for failure, keep every step reversible and avoid long table locks.
Task
Write a migration for the schema change below.
Inputs
- Database and version: [e.g. MySQL 8, PostgreSQL 16]
- Migration tool, if any: [e.g. Laravel migrations, Alembic, Prisma, plain SQL]
- Current schema of the affected tables: [paste CREATE TABLE statements]
- The change needed: [e.g. split full_name into first_name and last_name]
- Table sizes: [approximate row counts]
- Can the app go offline: [yes for how long, or no]
Method
- Break the change into expand, migrate and contract steps so old and new code can run at the same time.
- Backfill large tables in batches, with the batch size stated.
- Write the rollback for every step.
- Add a check query after each step that proves it worked.
Output format
- Plan: numbered steps, each marked as a schema change, a data change or a code release.
- Forward migration code.
- Rollback code.
- Check queries after each step.
- | Risk | Likelihood | Mitigation |
Quality rules
- A backup and a test on a copy of production data come before step one.
- No step drops data until the new structure has been verified and the old code is gone.
- All statements are parameterised or static; no string-built SQL.
- Say which statements lock the table on the given database version, and how to avoid it.






