Migration Ordering Dependency Check Prompt

This prompt is designed for release engineers and platform teams who need to validate the execution order of database migration scripts across multiple services before a deployment. It ingests a migration manifest that lists every migration file, its target service, and its declared dependencies, then produces a dependency graph analysis. The output identifies circular dependencies, missing prerequisites, and execution order violations that would cause deployment failures or data corruption if the migrations ran in the wrong sequence.

Use this prompt when you have a structured manifest of planned migrations and need an automated pre-flight check before any migration tool executes the scripts. The manifest must be accurate and complete; garbage in, garbage out applies. The prompt assumes each entry includes a unique migration identifier, the service it targets, and an explicit list of migration IDs it depends on. It does not introspect live databases, parse SQL, or validate the content of migration files. It only reasons about the declared dependency relationships.

Do not use this prompt for runtime schema introspection, for generating migration scripts from scratch, or for reviewing the SQL content inside individual migration files. It is not a replacement for integration testing or dry-run execution. If your manifest is incomplete or contains incorrect dependency declarations, the analysis will be wrong. For high-risk deployments, always pair this automated check with a human review of the dependency graph and a dry-run against a production-like environment before proceeding.

The following prompt template is designed to be pasted directly into your AI workflow. It expects a structured migration manifest as input and produces a dependency graph analysis. The placeholders in square brackets must be replaced with your actual data and constraints before execution. This template is self-contained and can be used independently of the surrounding playbook, but you should always review its output against your known service architecture and deployment topology.

textCopy
You are a release engineering assistant specializing in database migration dependency analysis.

Your task is to analyze the provided migration manifest and produce a dependency graph report that identifies circular dependencies, missing prerequisites, and execution order violations.

## INPUT
[MIGRATION_MANIFEST]

## CONSTRAINTS
- Assume each migration entry includes at minimum: service name, migration ID, declared dependencies (list of migration IDs it requires), and target schema version.
- Treat the declared dependency list as the source of truth for intended ordering.
- Flag any migration that references a dependency not present in the manifest.
- Detect all cycles in the dependency graph, regardless of cycle length.
- Identify any migration that appears in the manifest but is not reachable from any entry point (orphaned migration).
- Do not invent dependencies. Only use what is declared in the manifest.

## OUTPUT_SCHEMA
Return a valid JSON object with the following structure:
{
  "dependency_graph": {
    "nodes": [
      {
        "migration_id": "string",
        "service": "string",
        "declared_dependencies": ["string"],
        "all_transitive_dependencies": ["string"]
      }
    ],
    "edges": [
      {
        "from": "string (dependency)",
        "to": "string (dependent migration)"
      }
    ]
  },
  "findings": {
    "circular_dependencies": [
      {
        "cycle": ["string"],
        "severity": "BLOCKER",
        "description": "string explaining the cycle"
      }
    ],
    "missing_prerequisites": [
      {
        "migration_id": "string",
        "missing_dependency": "string",
        "severity": "BLOCKER",
        "description": "string"
      }
    ],
    "execution_order_violations": [
      {
        "violation_type": "REQUIRES_REORDER | ORPHANED | DUPLICATE_ID",
        "migration_ids": ["string"],
        "severity": "BLOCKER | WARNING",
        "description": "string"
      }
    ],
    "valid_execution_order": ["string"]
  },
  "summary": {
    "total_migrations": 0,
    "blocker_count": 0,
    "warning_count": 0,
    "is_executable": false
  }
}

## RULES
1. The `valid_execution_order` must be a topologically sorted list of all migration IDs that respects all non-circular dependencies. If cycles exist, this field must be an empty array.
2. `is_executable` must be `true` only if there are zero BLOCKER findings.
3. For each circular dependency, include the full cycle path in the `cycle` array.
4. For missing prerequisites, the `missing_dependency` is the ID that is referenced but not present in the manifest.
5. Classify orphaned migrations (no other migration depends on them and they depend on nothing) as `execution_order_violations` with type `ORPHANED` and severity `WARNING`.
6. Classify duplicate migration IDs as `execution_order_violations` with type `DUPLICATE_ID` and severity `BLOCKER`.

## RISK_LEVEL
HIGH — Incorrect migration ordering can cause data corruption, service outages, and difficult rollbacks. Always validate this output against your deployment pipeline's actual execution plan before applying migrations.

To adapt this template, replace [MIGRATION_MANIFEST] with your actual migration data. The manifest should be a structured list, ideally JSON or YAML, containing at minimum the fields described in the CONSTRAINTS section. If your manifest uses different field names, update the prompt's assumptions accordingly. For high-risk production environments, always pair this prompt's output with a human review step and a dry-run execution in a staging environment before approving any migration sequence.

The Migration Ordering Dependency Check Prompt is designed to operate as a pre-deployment gate in a release pipeline, not as an ad-hoc chat interaction. The prompt expects a structured migration manifest—typically a JSON or YAML document listing migration files, their declared dependencies, target databases, and execution order metadata—as its primary input. The harness should extract this manifest from the repository's migration directory, version control metadata, or an orchestration tool's plan file before constructing the prompt. Because the output is a dependency graph analysis with specific violation types (circular dependencies, missing prerequisites, execution order violations), the harness must validate that the model's response conforms to the expected schema before allowing the pipeline to proceed.

Wire the prompt into a CI/CD workflow by adding a dedicated stage after migration artifact assembly but before any deployment approval step. The harness should: (1) assemble the migration manifest from the source of truth (e.g., parsing Flyway, Alembic, or Liquibase metadata files); (2) inject the manifest into the [MIGRATION_MANIFEST] placeholder along with any [KNOWN_CONSTRAINTS] such as service startup order or shared database dependencies; (3) call the model with a low temperature setting (0.0–0.2) to maximize deterministic graph analysis; (4) parse the JSON output and validate it against a schema that requires dependency_graph, violations[], cycle_details[], and recommended_order[] fields; (5) if validation fails, retry once with the validation error appended as additional context; (6) if the retry also fails, fail the pipeline stage and surface the raw output for human review. For high-risk migrations involving financial data, PII, or irreversible DDL, add a mandatory human approval gate that presents the dependency graph visualization and violation summary before the migration can execute.

Model choice matters here: use a model with strong structured reasoning capabilities and reliable JSON output. GPT-4o, Claude 3.5 Sonnet, and Gemini 1.5 Pro all perform well on dependency graph analysis when given explicit schema constraints. Avoid smaller or older models that may conflate dependency types or hallucinate non-existent cycles. Log every invocation—including the manifest hash, model response, validation result, and reviewer decision—to create an audit trail for post-migration incident analysis. The most common production failure mode is a manifest that contains implicit dependencies not declared in migration metadata (e.g., an application code assumption about table existence). Mitigate this by enriching the manifest with schema introspection data before prompt construction, and by treating the prompt's missing_prerequisites output as a signal to update migration metadata, not just a gate failure.