Schema Migration Plan Generation Prompt

This prompt is designed for backend engineers and API platform teams who have a concrete, breaking schema change to roll out and need a structured, step-by-step migration plan. The ideal user has already completed the schema design, understands the specific differences between the old and new contracts (a schema diff), and has a known inventory of internal and external consumers. The core job-to-be-done is translating a technical breaking change into a safe, sequenced operational plan that minimizes consumer downtime and provides clear rollback checkpoints.

Before using this prompt, you must assemble three critical inputs: a concrete schema diff (e.g., an OpenAPI or GraphQL diff), a list of known consumers and their current usage patterns, and a target deprecation timeline. The prompt uses these to generate a plan that includes dual-write periods, deprecation windows, and explicit rollback steps. It will also validate that migration steps are ordered correctly—for example, ensuring a dual-write phase begins before any old field is deprecated—and that no consumer in your provided inventory is left without a migration path. The output is a structured plan meant for human review and adaptation, not an auto-executed script.

Do not use this prompt for non-breaking changes, greenfield API design, or when your consumer inventory is unknown or incomplete. If you are simply adding a new optional field, a full migration plan is unnecessary overhead. Similarly, if you cannot enumerate your consumers, the prompt cannot validate that no one is stranded, making the output dangerously incomplete. The generated plan is a starting point for your runbook; it requires an engineer to verify sequencing, adjust timelines, and add operational details like monitoring dashboards and communication templates before execution.

This prompt template is designed to produce a structured, actionable migration plan when you need to roll out a breaking change to a database schema, API contract, or data model. It forces the model to reason about ordering, consumer impact, and safety checkpoints rather than generating a generic list of steps. Before using it, you must gather the current and target schemas, a list of known consumers, and any operational constraints like maintenance windows or data volume thresholds.

textCopy
You are a senior infrastructure engineer planning a safe, phased schema migration. Your goal is to produce a step-by-step migration plan that minimizes risk to downstream consumers and allows rollback at any stage.

## INPUTS
- Current Schema: [CURRENT_SCHEMA]
- Target Schema: [TARGET_SCHEMA]
- Known Consumers: [CONSUMERS]
- Operational Constraints: [CONSTRAINTS]
- Risk Tolerance: [RISK_LEVEL]

## OUTPUT SCHEMA
Return a JSON object with the following structure:
{
  "migration_name": "string",
  "summary": "string (one-paragraph overview of the change and its impact)",
  "breaking_changes": [
    {
      "change": "string (description of what is breaking)",
      "affected_consumers": ["string"],
      "severity": "CRITICAL | HIGH | MEDIUM | LOW"
    }
  ],
  "phases": [
    {
      "phase_number": "integer",
      "name": "string",
      "objective": "string",
      "steps": [
        {
          "step_number": "integer",
          "action": "string (precise, executable instruction)",
          "owner": "string (team or role)",
          "verification": "string (how to confirm the step succeeded)",
          "rollback_action": "string (how to undo this step if verification fails)",
          "consumer_impact": "NONE | READ_ONLY | DOWNTIME | DEGRADED"
        }
      ],
      "gate_criteria": "string (conditions that must be met before proceeding to the next phase)"
    }
  ],
  "dual_write_periods": [
    {
      "description": "string",
      "start_phase": "integer",
      "end_phase": "integer",
      "affected_fields": ["string"]
    }
  ],
  "deprecation_windows": [
    {
      "field_or_endpoint": "string",
      "announcement_phase": "integer",
      "removal_phase": "integer",
      "consumer_migration_guide": "string"
    }
  ],
  "rollback_checkpoints": [
    {
      "after_phase": "integer",
      "rollback_plan": "string (concise plan to return to the previous state)",
      "data_integrity_risk": "string"
    }
  ],
  "stranded_consumers": [
    {
      "consumer": "string",
      "reason": "string (why this consumer cannot migrate without changes)",
      "recommended_action": "string"
    }
  ]
}

## CONSTRAINTS
1. Every phase must have a verification step and a rollback action.
2. No phase should strand a consumer without documenting it in `stranded_consumers`.
3. Dual-write periods must precede any removal of old fields or endpoints.
4. Deprecation windows must include a consumer migration guide.
5. Steps must be ordered so that no irreversible action occurs before its rollback checkpoint is defined.
6. If a consumer cannot be migrated, flag it in `stranded_consumers` and do not proceed with a plan that silently breaks it.
7. If the risk level is HIGH or CRITICAL, include a manual approval gate before any irreversible step.

To adapt this template, replace each square-bracket placeholder with concrete data. [CURRENT_SCHEMA] and [TARGET_SCHEMA] should be the full schema definitions in a machine-readable format like SQL DDL, OpenAPI YAML, or protobuf. [CONSUMERS] should list every known service, client, or team that reads from the schema, including internal and external dependencies. [CONSTRAINTS] should capture maintenance windows, data volume limits, compliance requirements, and team availability. [RISK_LEVEL] accepts LOW, MEDIUM, HIGH, or CRITICAL and controls the strictness of gating and rollback requirements. After generating the plan, validate that all phases are ordered correctly, no consumer is missing from the impact analysis, and every irreversible step has a preceding rollback checkpoint. For high-risk migrations, route the output through a human review step before any phase begins execution.

This prompt is designed to be called programmatically as part of an API change management pipeline, not as a one-off chat interaction. The typical integration point is a CI check that triggers when a pull request modifies an OpenAPI, GraphQL, or protobuf schema file. The harness should extract the schema diff, assemble the required inputs (current schema, proposed schema, consumer registry, deployment topology), and invoke the model with the prompt template. Because migration plans carry operational risk, the harness must treat the model output as a draft requiring structured validation before it reaches a human reviewer or an automated rollout system.

Start by building a thin orchestration layer that: (1) detects schema changes in version control, (2) fetches the current and proposed schema artifacts, (3) queries your API consumer registry for affected clients, and (4) populates the prompt's [CURRENT_SCHEMA], [PROPOSED_SCHEMA], [CONSUMER_LIST], and [DEPLOYMENT_CONTEXT] placeholders. The model should be called with temperature=0.2 or lower to reduce variance in plan structure. Parse the output against a strict JSON schema that validates required fields: migration_steps (ordered array), dual_write_period_days, deprecation_window_days, rollback_checkpoints (array), and stranded_consumers (array). Reject and retry any response that fails schema validation, up to three attempts, with the validation error message appended to the retry prompt as [PREVIOUS_ERROR]. Log every attempt and the final validated output for auditability.

After validation, the harness should run automated sanity checks before surfacing the plan for human review. Verify that migration steps are topologically ordered (no step references a later step's artifact), that every consumer in the input list appears in either the migration path or the stranded-consumers list, and that rollback checkpoints correspond to reversible steps. Flag any plan that recommends a dual-write period shorter than your organization's minimum (commonly 7 days) or a deprecation window shorter than your API versioning policy allows. These checks prevent the model from producing a plan that is syntactically valid but operationally dangerous. The harness should attach these automated findings as annotations on the pull request, alongside the full migration plan, so the reviewing API platform engineer can see both the plan and the harness's concerns in one view.

Human review remains mandatory for any migration plan that includes breaking changes, affects consumers outside your team's ownership, or modifies authentication or authorization scopes. The harness should enforce this by checking the [RISK_LEVEL] input and the presence of breaking changes in the diff. If the risk level is high or the diff contains breaking changes, the pipeline must block merge until an authorized reviewer from the API platform team approves the plan. Store the approved plan as an artifact in your API registry or developer portal so consumers can discover it. For lower-risk changes, the harness can auto-approve the plan if all automated checks pass and the plan contains no stranded consumers. Avoid wiring this prompt directly to an automated rollout system without a human-in-the-loop step for any change that modifies the wire format or removes fields.