Contradictory Schema Change Detection Prompt

This prompt is designed for database migration agents and CI/CD pipelines that receive schema change requests containing conflicting column types, incompatible constraints, or contradictory migration directions. Its primary job is to act as a safety interlock: it analyzes a proposed schema change against the current database state and produces a structured conflict report before any Data Definition Language (DDL) is generated or executed. The ideal user is an AI agent or an automated workflow orchestrator that must prevent destructive schema changes, such as altering a column to two different data types in the same migration or adding a unique constraint to a column that is also being dropped.

Use this prompt when the cost of an incorrect schema change is high—for example, in production databases, multi-tenant systems, or regulated environments where data integrity is non-negotiable. It is appropriate when the input includes both the current schema (as a CREATE TABLE or equivalent DDL) and a proposed change expressed in natural language or a structured migration spec. The prompt is not a general-purpose SQL reviewer; do not use it for performance tuning, index recommendations, or syntax validation. It should be wired into a pre-commit or pre-deployment gate, where a failure to resolve conflicts blocks the pipeline and escalates to a human reviewer. The output is a machine-readable conflict report, not a corrected schema, ensuring that the resolution decision remains with a human operator.

Before integrating this prompt, ensure your harness can provide the current schema as ground truth and parse the structured conflict report to gate the next step. If your system cannot reliably extract the current schema or if schema changes are always applied by a human DBA with full context, this prompt adds unnecessary friction. The next step is to pair this detection prompt with a human review queue, where unresolved conflicts are packaged with the report and presented for adjudication. Avoid the temptation to let the model resolve conflicts autonomously; the prompt's value is in its refusal to guess.

This prompt template is designed for a database migration agent that receives schema change requests. Its job is to act as a safety gate: before any CREATE, ALTER, or DROP statements are generated, it must analyze the requested changes for internal contradictions. Contradictions include conflicting column types for the same column, mutually exclusive constraints (e.g., NOT NULL and DEFAULT NULL), or migration directions that cancel each other out (e.g., adding and dropping the same index in one request). The prompt instructs the model to produce a structured conflict report and halt execution, requesting human resolution. It does not generate DDL.

textCopy
You are a database schema safety agent. Your only job is to detect contradictory instructions in a schema change request before any DDL is generated.

You will receive a [SCHEMA_CHANGE_REQUEST] that may contain one or more proposed changes to a database schema. The current schema is provided in [CURRENT_SCHEMA] for reference.

Analyze the request for the following contradiction types:
1. **Type Conflicts**: The same column is assigned two or more incompatible data types.
2. **Constraint Conflicts**: Mutually exclusive constraints are applied to the same column (e.g., NOT NULL and DEFAULT NULL, UNIQUE and a non-unique index).
3. **Directional Conflicts**: The request contains operations that cancel each other out (e.g., ADD COLUMN and DROP COLUMN for the same column, ADD INDEX and DROP INDEX for the same index).
4. **Dependency Conflicts**: A change references an object that another change in the same request drops.

If no contradictions are found, output ONLY the following JSON:
{
  "status": "clean",
  "conflicts": []
}

If contradictions are found, output ONLY the following JSON. Do not generate any DDL, SQL, or migration code:
{
  "status": "conflict",
  "conflicts": [
    {
      "type": "TypeConflict | ConstraintConflict | DirectionalConflict | DependencyConflict",
      "description": "A clear, specific explanation of the contradiction.",
      "conflicting_instructions": [
        "The first conflicting instruction from the request.",
        "The second conflicting instruction from the request."
      ],
      "affected_object": "The column, table, or constraint name.",
      "suggested_resolution_question": "A specific question asking the user to resolve this conflict."
    }
  ],
  "blocked_actions": ["List of specific DDL actions that are blocked due to conflicts."]
}

[CONSTRAINTS]
- Never generate DDL, SQL, or migration code under any circumstances.
- Never guess or assume a resolution. Your only output is the conflict report.
- If the request is ambiguous but not strictly contradictory, treat it as clean and do not flag it.
- Base all analysis on the provided [CURRENT_SCHEMA]. If a referenced object does not exist in the current schema, flag it as a DependencyConflict only if another instruction in the same request creates it.

To adapt this template, replace the placeholders with your application's actual inputs. [SCHEMA_CHANGE_REQUEST] should contain the raw user or system request, which may be natural language or a structured change list. [CURRENT_SCHEMA] should be a machine-readable representation of the existing schema, such as a CREATE TABLE dump or a JSON schema descriptor. The [CONSTRAINTS] section is critical for high-risk environments: it explicitly forbids DDL generation and resolution guessing. For additional safety, wire the output into a validator (see the Implementation Harness section) that confirms the JSON structure and blocks any downstream migration runner if status is conflict. If your system allows partial application of non-conflicting changes, extend the output schema to include a clean_changes array, but only after a human has resolved the flagged conflicts.

This prompt is designed to sit before any DDL generation in a database migration agent. When a user requests a schema change—whether through natural language, a PR comment, or a migration ticket—the agent must first pass the request through this prompt. The prompt's job is to detect contradictions in column types, constraints, migration directions, or naming that would produce destructive or invalid DDL if executed. The output is a structured conflict report, not SQL. The application harness must enforce that no DDL is generated until the conflict report is empty or resolved.

Wire the prompt into a pre-generation gate in your agent's tool-calling loop. The agent receives a schema change request and immediately calls a detect_schema_conflicts function that wraps this prompt. The function's input is the raw user request plus the current schema state (from INFORMATION_SCHEMA or a schema registry). The prompt returns a JSON object with a conflicts array and a resolution_required boolean. The harness must validate this output against a strict schema: each conflict must have a conflict_type (e.g., type_mismatch, constraint_conflict, direction_conflict), locations (affected tables/columns), and a description. If resolution_required is true, the agent must halt and present the conflict report to the user with a structured clarification request—never proceed to DDL generation. If resolution_required is false and the conflicts array is empty, the agent can proceed to the next step (DDL generation prompt). Implement a retry budget of 2 attempts for malformed JSON; if the prompt fails to produce valid JSON twice, escalate to a human reviewer with the raw response and the original request.

For production deployment, log every invocation with the input request, the current schema snapshot, the conflict report, and the resolution decision. This creates an audit trail for schema changes. Add an eval harness that tests the prompt against a golden dataset of contradictory and non-contradictory schema change requests. Measure precision (did we flag real conflicts?) and recall (did we miss any destructive conflicts?). Common failure modes include: false positives on legitimate type widening (e.g., INT to BIGINT), missed conflicts when the contradiction spans multiple tables, and hallucinated column names not present in the schema. Run these evals on every prompt version change. For high-risk environments (production databases, regulated data), require a second human approval after conflict resolution before any DDL executes, even if the prompt reports zero conflicts. The prompt is a safety net, not a replacement for migration review.