Operations

Database Migration Plan Prompt

Ship a schema change without downtime or data loss — an expand-migrate-contract migration plan with backward compatibility, ordering, and a reversal path.

Open in Markdown Output Builder

Overview

A schema change deployed naively locks tables, breaks the running version, or loses data on rollback. This prompt plans a safe migration using the expand/contract pattern: the backward-compatible expand step, the data backfill, the cutover, and the later contract — each ordered so the old and new code can both run during deploy, with a reversal path at every stage.

How to use this resource

Describe the schema change

Note the change you are making, the tables and data it touches, and the versions that must keep running during deploy. The plan orders it so old and new code coexist.
Open this resource in Markdown Output Builder

Load the prompt into Markdown Output Builder and fill in the change. It returns the expand-migrate-contract plan as clean Markdown you can drop into your runbook.
Walk through the migration stages

Read the backward-compatible expand step, the backfill, the cutover, and the later contract, confirming each carries a reversal path.
Fill the gaps before you migrate

Resolve any stage without a clean reversal and confirm the ordering keeps both versions working, then run the migration behind it.

Why This Works

Expand/contract keeps both code versions working through the deploy
Putting drops in a later deploy makes the change reversible when it matters
Calling out locking steps prevents the migration that takes the site down

Best for

Schema changes on tables with live traffic and real data
Deploys where the migration and code ship together
Large tables where a naive ALTER would lock

Not for

Designing the schema in the first place — use a database design workflow
Tuning a slow query — use a SQL optimization prompt

Use cases

Planning a zero-downtime schema change
Sequencing a migration so old and new code coexist
De-risking a destructive column or table change

FAQ

Why does the plan split a schema change into expand, migrate, and contract instead of one migration?

Splitting it lets old and new code both run during the deploy window. The EXPAND step is additive only — new nullable columns and tables, no drops — so the old version keeps working. BACKFILL and CUTOVER move data and reads over, and the destructive CONTRACT (dropping old columns) is deferred to a separate later deploy once nothing uses the old shape.

How does the generated plan keep a large-table migration from locking and taking the site down?

The prompt has a dedicated LOCK & PERFORMANCE section that flags any step that could lock a large table and describes how to avoid it, and the BACKFILL step is specified as batched, resumable, and non-locking rather than a single ALTER. The Markdown Output Builder writes these into your runbook; you still run the actual migration wherever your database lives.

Which migration steps get a rollback path, and what happens with irreversible ones like dropping a column?

The REVERSIBILITY section tags every step as reversible or not and states the reversal action for the ones that are. For irreversible steps it specifies a safeguard instead — a backup or a dual-write window — and the RULES push destructive drops into a separate last deploy, so a failed cutover reverses without having already dropped data.

Customize This Resource

Opens this setup in Markdown Output Builder. Generate to get the full markdown prompt — then adjust the sections, table rules, and consistency level.

Open in Markdown Output Builder

Prompt Template

Copy it as-is, or use Open in Markdown Output Builder to load it pre-filled and customize it with your own context.

ROLE
You are planning a database migration that must deploy without downtime or data loss, while old and new code run simultaneously.

INPUT
The schema change:
[Current schema, desired schema, and the data involved]

PRODUCE A MIGRATION PLAN (expand / migrate / contract)
1. EXPAND: the additive, backward-compatible schema change that lets the OLD code keep working (new nullable columns, new tables, no drops).
2. BACKFILL: how existing data is migrated to the new shape — batched, resumable, non-locking.
3. DUAL-WRITE / CUTOVER: how the app writes during the transition and when reads switch to the new shape.
4. CONTRACT: the cleanup (drop old columns/tables) that runs only AFTER all code uses the new shape.
5. REVERSIBILITY: for each step, whether it's reversible and the reversal action; for irreversible ones, the safeguard (backup, dual-write window).
6. LOCK & PERFORMANCE: any step that could lock a large table, and how to avoid it.

RULES
- Old and new code must both work during the deploy window — no breaking step.
- Destructive steps (drops) come last, in a separate deploy.

OUTPUT
The ordered migration steps grouped expand → migrate → contract, each with its reversal/safeguard.

More resources from Markdown Output Builder

Resource

Markdown Output Prompt — Lock the Document Structure

The contract that stops AI documents from restructuring themselves: a pinned section skeleton, forced tables, and strict consistency rules.

Prompt Engineering

Resource

README Prompt Template

Overview, Installation, Usage, Examples, Configuration — the README skeleton with required, runnable code examples.

Engineering

Resource

API Documentation Prompt

Overview, Authentication, Endpoints, Error Handling, Rate Limits — endpoint docs in an identical structure, with parameter tables and runnable examples forced.

Engineering

Resources that pair well

Resource

Executive Summary Prompt

Summary, Why It Matters, What Happens Next — the executive summary contract for readers who will never open the source.

Operations

Resource

Force JSON Output from AI

Stop getting 'Sure, here is the JSON…' — the output-contract pattern that forces models to return only parseable JSON: schema, example, and a strict rule block.

Prompt Engineering

Resource

Fix Invalid JSON from AI

The JSON won't parse and you can't see why. Deterministic cause-sniffing — trailing commas, single quotes, unclosed brackets — and the repair prompt that fixes it.

Engineering

Related tools

Tool

Markdown Output Builder

Build prompts that produce documents in a fixed structure — headings, sections, and tables.

Structured Output

Guides for this resource

Guide

How to Plan a Database Migration With AI Safely

The ALTER is one line. The migration is everything around it — because for a while, two versions of your code share one database. Here's how to turn a schema change on a live table into phases that stand alone, a backfill that can resume, and one step you can never take back.

Software Development with AI

Guide

How to Plan a Zero-Downtime Deployment With AI

"We do rolling deploys, so we have zero downtime" is true about the mechanism and often false about the change. Here is how to plan a zero-downtime deployment with AI: work out whether the old and new versions can actually run at once, across the database, the queue, and the traffic switch, before you ship.

Software Development with AI

Tip: Save time by exploring related resources and tools that integrate with this resource.