Migration Dry-Run Simulation Prompt

This prompt is designed for release engineers and database reliability teams who need to evaluate migration scripts before production deployment. It takes dry-run output logs from a staging or production-like environment and produces a structured simulation report. Use this prompt when you have executed a migration in dry-run mode (or against a non-production clone) and captured the full output including timing, locks, errors, and resource metrics. The prompt does not execute migrations; it interprets execution artifacts to surface risks that manual log review would miss. This belongs in your pre-deployment checklist after dry-run execution and before the production change window opens.

The ideal input is a complete, unredacted dry-run log containing DDL and DML statements, lock acquisition notices, timing measurements, error messages, and resource utilization metrics. The prompt expects you to supply this as [DRY_RUN_LOG] along with optional [SCHEMA_METADATA] describing table sizes, index definitions, and current constraint states. You can also provide [RISK_THRESHOLDS] to calibrate the simulation's sensitivity—for example, flagging any operation that holds an exclusive lock for more than 500ms or any statement that scans more than 10,000 rows. The prompt works best with structured log formats (e.g., PostgreSQL's EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output or MySQL's SHOW PROFILE data), but it can also parse semi-structured CLI output if you include a [LOG_FORMAT] hint describing the tool and version that generated the logs.

Do not use this prompt as a substitute for actual dry-run execution. It cannot simulate a migration from a static SQL file alone—it requires real execution artifacts from a representative environment. It is also not a replacement for peer review of migration logic, nor does it validate business logic correctness (e.g., whether a column rename matches the application's expectations). The prompt's value is in pattern recognition across timing, locking, and error signals that humans often overlook when scanning thousands of lines of log output. After receiving the simulation report, you should still conduct a manual review of any high-severity findings and run the migration against a production-scale clone if the dry-run environment differs materially from production in data volume or concurrency patterns.

This prompt is designed to be pasted directly into your AI workflow. It instructs the model to act as a release engineer evaluating a database migration dry-run. The model will analyze provided logs and schema metadata to produce a structured simulation report. Before using this prompt, ensure you have captured the full dry-run output, the target schema DDL, and any relevant environment context. This prompt is not a substitute for actual execution in a staging environment; it is a pre-flight analysis tool to catch errors before they reach production.

textCopy
You are a senior release engineer reviewing a database migration dry-run against a production-like schema. Your task is to produce a structured simulation report based on the provided dry-run output logs and schema context.

## INPUTS
- Dry-Run Output Logs: [DRY_RUN_LOGS]
- Target Schema DDL: [TARGET_SCHEMA_DDL]
- Migration Script: [MIGRATION_SCRIPT]
- Environment Context: [ENVIRONMENT_CONTEXT]

## OUTPUT SCHEMA
Return a single JSON object with the following structure:
{
  "execution_summary": {
    "status": "SUCCESS" | "WARNING" | "FAILURE",
    "total_statements": <number>,
    "errors_found": <number>,
    "warnings_found": <number>,
    "estimated_duration_seconds": <number>
  },
  "findings": [
    {
      "severity": "ERROR" | "WARNING" | "INFO",
      "statement_line": <number>,
      "statement_text": "<exact SQL from migration>",
      "category": "SYNTAX_ERROR" | "LOCK_ACQUISITION" | "TYPE_MISMATCH" | "CONSTRAINT_VIOLATION" | "RESOURCE_CONSUMPTION" | "TIMING_RISK" | "OTHER",
      "description": "<clear explanation of the finding>",
      "recommendation": "<actionable fix or mitigation>"
    }
  ],
  "lock_analysis": {
    "lock_type_expected": "<e.g., ACCESS EXCLUSIVE, ROW EXCLUSIVE>",
    "estimated_lock_duration_seconds": <number>,
    "blocking_risk": "HIGH" | "MEDIUM" | "LOW",
    "concurrent_operations_at_risk": ["<list of operations that may be blocked>"]
  },
  "resource_warnings": [
    {
      "resource": "DISK" | "MEMORY" | "CPU" | "I/O",
      "warning": "<description of potential resource exhaustion>",
      "mitigation": "<suggestion to reduce resource pressure>"
    }
  ],
  "rollback_assessment": {
    "is_rollback_possible": true | false,
    "rollback_script_provided": true | false,
    "irreversible_operations": ["<list of operations that cannot be rolled back>"],
    "data_loss_risk": "HIGH" | "MEDIUM" | "LOW" | "NONE"
  }
}

## CONSTRAINTS
- Base all findings strictly on the provided dry-run logs and schema DDL. Do not invent errors not present in the logs.
- If a statement in the migration script is not reflected in the logs, flag it as an INFO finding with the category "MISSING_FROM_DRY_RUN".
- For any ERROR severity finding, you must include a specific, actionable recommendation.
- If the dry-run logs indicate a failure, the execution_summary.status must be "FAILURE".
- Do not suggest fixes that require access to systems or data not described in the inputs.

## RISK_LEVEL
[RISK_LEVEL]

## EXAMPLES
[EXAMPLES]

To adapt this template, replace the square-bracket placeholders with actual data before execution. The [DRY_RUN_LOGS] should contain the complete, unaltered output from your migration tool's dry-run command. The [TARGET_SCHEMA_DDL] is the full DDL of the target database, which provides context for type and constraint checking. The [RISK_LEVEL] placeholder should be replaced with a brief description of the deployment's risk tolerance (e.g., 'HIGH - this is a core financial database, any error is critical'). The [EXAMPLES] placeholder can be populated with one or two few-shot examples of correctly formatted reports to improve output consistency. For high-risk environments, always route the final report for human review before proceeding with the actual migration.

The Migration Dry-Run Simulation Prompt is designed to be the final automated gate before a migration script reaches production. It expects structured dry-run output logs as its primary input, which means your CI/CD pipeline must first execute the migration script against a production-like staging or ephemeral database and capture the full output. This includes timing metadata, lock acquisition logs, warning messages, and any error codes. The prompt does not execute the migration itself; it interprets the results of that execution. Your harness is responsible for running the dry run, collecting the logs, and feeding them to the model in the [DRY_RUN_OUTPUT] placeholder along with the migration script content in [MIGRATION_SCRIPT] and relevant schema metadata in [SCHEMA_CONTEXT].

The harness should enforce a strict validation contract on the model's output. Define an expected JSON schema for the simulation report that includes fields for overall_risk_level, errors_found, warnings, estimated_duration_seconds, lock_acquisition_issues, and resource_consumption_warnings. After the model responds, validate the output against this schema. If validation fails, implement a retry loop with a maximum of two attempts, appending the validation error to the prompt context so the model can self-correct. For high-risk migrations—those classified as CRITICAL or HIGH risk by the model—the harness must automatically create a manual review ticket in your project management system and block the deployment pipeline until a human approves the report. Log every prompt invocation, the raw model response, the validation result, and the final disposition (auto-approved, flagged for review, or failed) to an audit table for post-deployment traceability.

Model choice matters for this workflow. The prompt requires strong reasoning over structured logs and schema definitions, so prefer models with large context windows and strong code-reasoning capabilities. If your dry-run output is exceptionally large, implement a pre-processing step that extracts only the relevant sections—error lines, timing summaries, and lock statements—to keep the prompt within token limits. Do not truncate the output arbitrarily; use a targeted extraction step that preserves diagnostic signal. Finally, never allow the harness to automatically apply a migration based solely on a passing simulation report. The prompt's output is a risk assessment, not a deployment authorization. Always keep a human approval step for production migrations, and use this prompt to make that approval decision faster and more informed.