Schema Migration Rollback Plan Prompt

This prompt is for database reliability engineers (DBREs), platform engineers, and backend teams who are about to execute a schema migration that carries non-trivial risk. The ideal user is someone who already has a proposed migration script (DDL) and understands the current schema, but needs a rigorous, verifiable rollback plan before they can proceed with confidence. The prompt forces the model to act as a skeptical reviewer, reasoning about preconditions, data recovery, backward compatibility, and verification steps rather than generating a generic DROP COLUMN or RENAME TABLE undo script. Use this when the migration modifies existing columns, changes data types, splits or merges tables, or operates on tables that are subject to a recovery time objective (RTO) or recovery point objective (RPO) SLO.

You should not use this prompt for trivial, additive-only migrations where a simple DROP statement suffices and no data transformation occurs. Adding a new nullable column, creating a new index, or adding a new table without foreign key dependencies from existing tables are examples where the overhead of a full AI-generated plan is unnecessary. Similarly, do not use this prompt if you lack a complete, version-controlled representation of the current schema and the proposed migration; the model's reasoning is only as good as the input context. The prompt is designed for relational databases (PostgreSQL, MySQL, SQL Server, etc.) and may require adaptation for NoSQL schemas where the concept of a transactional DDL rollback does not apply.

Before using this prompt, gather the full DDL of the affected tables, the proposed migration script, any known dependent application queries or services, and your RTO/RPO requirements. The output is a structured plan, not an executable script, so you must still review every step against your actual environment, test it in a staging or restored backup, and ensure it aligns with your transaction boundaries and locking behavior. If the migration involves personally identifiable information (PII), regulated data, or financial records, a human DBA or staff engineer must approve the plan before execution. The next section provides the copy-ready prompt template you will populate with these inputs.

This prompt template is designed to be pasted directly into your AI harness. It instructs the model to act as a database reliability engineer and produce a structured rollback plan from a provided migration script and its context. The output is a detailed, actionable procedure that prioritizes data safety and minimal downtime.

textCopy
You are a senior database reliability engineer. Your task is to generate a safe, step-by-step rollback plan for the provided schema migration.

# INPUTS
- Migration Script: [MIGRATION_SCRIPT]
- Migration Intent: [MIGRATION_INTENT]
- Current Schema DDL: [CURRENT_SCHEMA_DDL]
- Database System & Version: [DATABASE_SYSTEM]
- Table Data Volume (rows): [TABLE_VOLUME]
- Downtime Tolerance: [DOWNTIME_TOLERANCE]

# CONSTRAINTS
- The rollback plan must be a numbered, sequential list of steps.
- Each step must include the exact SQL or command to execute.
- For destructive operations (e.g., DROP TABLE, DELETE), include a mandatory pre-check step to verify data existence or backup.
- Prioritize strategies that avoid data loss and minimize locking.
- Flag any step that requires a full table lock or significant I/O.
- If a rollback step is not possible without data loss, explicitly state this and recommend a recovery-from-backup procedure.

# OUTPUT_SCHEMA
{
  "rollback_plan": [
    {
      "step_number": 1,
      "action": "Description of the action",
      "command": "EXACT SQL OR COMMAND",
      "pre_checks": ["Check 1", "Check 2"],
      "expected_outcome": "What should happen if successful",
      "failure_mode": "What could go wrong and how to detect it",
      "lock_impact": "None | Row-Level | Table-Level"
    }
  ],
  "data_loss_risk": "None | Partial | Full",
  "recommended_recovery_strategy": "A summary of the overall approach or fallback plan."
}

# PROCEDURE
1. Analyze the [MIGRATION_SCRIPT] to identify all DDL changes.
2. For each change, determine the inverse operation.
3. Construct the rollback steps in reverse order of the migration.
4. For each step, define pre-checks to ensure the environment is in the expected state before executing the rollback.
5. Output the plan strictly according to the [OUTPUT_SCHEMA].

To adapt this template, replace the square-bracket placeholders with concrete values before execution. The [MIGRATION_SCRIPT] should be the full DDL you intend to run. For high-risk migrations where data_loss_risk is 'Full', the generated plan should be treated as a draft and must undergo a formal peer review. The [OUTPUT_SCHEMA] is critical for programmatic consumption; ensure your application's parser is strict and rejects any response that doesn't conform to this structure.

The rollback plan prompt is designed to be called programmatically as a gate in a database migration pipeline. Before a migration is applied to staging or production, the proposed DDL and a description of the change are sent to the model. The output is a structured rollback plan that must pass automated validation before the migration can proceed. This harness transforms a useful prompt into a safety-critical control point that prevents irreversible data loss.

Integrate the prompt into your CI/CD system by wrapping it in a script or microservice that accepts the migration DDL and context as input. The service should call the LLM with a low temperature (0.0–0.2) to maximize deterministic, safe output. Parse the response against a strict JSON schema that requires preconditions, rollback_steps (an ordered array), data_recovery_steps, compatibility_verification, and estimated_downtime. If parsing fails, retry once with a repair prompt that includes the schema and the raw output. If the second attempt fails, fail the pipeline and alert the on-call engineer. For high-risk migrations (those involving DROP, TRUNCATE, or column removal), require explicit human approval by posting the generated plan to a review channel and blocking deployment until an authorized reviewer signs off.

Log every prompt invocation, the full model response, the validation result, and the human approval decision to an audit trail. This is critical for post-incident review and compliance. Avoid wiring this prompt directly into an automated deployment without a human-in-the-loop for destructive operations. The model can reason about rollback steps, but it cannot guarantee the absence of edge cases in your specific data. The harness should treat the model's output as a high-quality draft that still requires engineering judgment before execution.