Enum Value Modification Impact Prompt

This prompt is designed for backend and data engineering teams who are planning to modify an existing enum type in a production database schema. The job-to-be-done is a structured, pre-flight impact analysis that surfaces every downstream dependency before a migration PR is opened. The ideal user is a senior engineer or tech lead who understands the schema but needs a systematic way to map the blast radius of adding, removing, renaming, or reordering enum values. Required context includes the current enum definition, the proposed change, the database dialect (PostgreSQL, MySQL, etc.), and access to application code or ORM model definitions that reference the enum.

You should use this prompt when the change is more complex than appending a new value to the end of an existing enum. Specifically, run it before reordering values (which changes ordinal positions), removing or renaming values (which breaks existing data rows and application constants), or adding values in a position-sensitive way. The prompt is also appropriate when the enum is exposed through an API contract—REST, GraphQL, or gRPC—where client code may deserialize values by string or integer representation. Do not use this prompt for trivial documentation-only changes or for enums that are purely internal with no persistence, no API exposure, and no sort-order dependency.

Before running this prompt, gather the current enum definition, the proposed migration DDL, and a representative sample of application code that references the enum. If your ORM generates enum mappings automatically, include those generated files. The prompt works best when you can provide concrete code references rather than abstract descriptions. After receiving the impact analysis, validate each identified risk surface against your actual codebase and test suite. The analysis is a starting point for your migration checklist, not a replacement for integration tests or staging environment validation. Avoid using this prompt as a post-incident forensic tool—it is designed for prevention, not root cause analysis after a breaking change reaches production.

This prompt template is designed to produce a structured JSON impact analysis when you are planning to add, remove, or reorder enum values in a database schema. It forces the model to consider application code references, existing data compatibility, sort order changes, and client deserialization risks across API boundaries. The output is a risk-ranked list of findings with explicit remediation steps, suitable for inclusion in a migration plan review.

textCopy
You are a senior backend engineer and database reliability specialist. Your task is to perform a rigorous impact analysis for a proposed modification to an enum type in a database schema.

## INPUT CONTEXT
- **Enum Definition (Current):**
  [CURRENT_ENUM_DEFINITION]
- **Enum Definition (Proposed):**
  [PROPOSED_ENUM_DEFINITION]
- **Database Table and Column:**
  [TABLE_NAME].[COLUMN_NAME]
- **Application Code References:**
  [CODE_REFERENCES]
- **API Contract (if applicable):**
  [API_CONTRACT]
- **Existing Data Sample (first 20 rows):**
  [EXISTING_DATA_SAMPLE]

## OUTPUT SCHEMA
Return a single JSON object with the following structure:
{
  "change_summary": "string describing the net change (ADDED, REMOVED, REORDERED)",
  "compatibility_assessment": "BACKWARD_COMPATIBLE | BREAKING | REQUIRES_DATA_MIGRATION",
  "findings": [
    {
      "id": "F-001",
      "category": "CODE_REFERENCE | DATA_INTEGRITY | SORT_ORDER | CLIENT_DESERIALIZATION | API_CONTRACT",
      "severity": "CRITICAL | HIGH | MEDIUM | LOW",
      "location": "file:line or table:row reference",
      "description": "clear explanation of the risk",
      "remediation": "specific fix or migration step required"
    }
  ],
  "migration_script_required": true/false,
  "recommended_migration_steps": ["ordered list of steps"],
  "rollback_risk": "LOW | MEDIUM | HIGH",
  "rollback_notes": "explanation of rollback complexity"
}

## CONSTRAINTS
- If an enum value is being REMOVED, you MUST check the existing data sample for rows that currently use that value. Flag every such row as a CRITICAL finding.
- If the sort order is changing, you MUST identify any application code that relies on ordinal position (e.g., `.ordinal()` in Java, `enum_index` in ORMs) and flag it as HIGH severity.
- If the enum is exposed in an API contract (REST, GraphQL, gRPC), you MUST assess client deserialization risk. Unknown enum values may cause client crashes or data loss.
- Do not assume the application code handles unknown enum values gracefully. Explicitly check for default cases, exception handlers, and fallback logic.
- If no code references are provided, state that as a limitation and recommend a full codebase search before proceeding.

## RISK LEVEL
[HIGH_RISK]

## INSTRUCTIONS
1. Compare the current and proposed enum definitions value by value.
2. Classify the change type (add, remove, reorder).
3. For each code reference provided, determine if it is affected by the change.
4. Analyze the existing data sample for compatibility with the proposed definition.
5. Assess API contract and client deserialization risks.
6. Produce the JSON output with no additional commentary outside the JSON object.

To adapt this template, replace the square-bracket placeholders with your specific context. The [CURRENT_ENUM_DEFINITION] and [PROPOSED_ENUM_DEFINITION] should include the full enum type definition, including any comments. For [CODE_REFERENCES], provide a grep or search result showing every file and line where the enum is referenced in application code, ORM models, and serialization logic. The [EXISTING_DATA_SAMPLE] should be the output of SELECT [COLUMN_NAME] FROM [TABLE_NAME] LIMIT 20; to give the model real data to validate against. If this is a high-risk migration on a production table, set [RISK_LEVEL] to HIGH_RISK to trigger stricter analysis; otherwise, use MEDIUM_RISK. After pasting the adapted prompt into your AI harness, validate the output JSON against the schema before using it in your migration review. For production-critical changes, always have a human DBA or senior engineer review the findings before executing any migration script.

The Enum Value Modification Impact Prompt is designed to be integrated into a pre-merge CI check for database migration pull requests. The core job of the harness is to gather the necessary structured context—the proposed migration SQL, the current application codebase, and the existing database schema—and feed it into the prompt in a repeatable way. The harness should not simply dump raw files into the prompt; it must pre-process the inputs to extract the specific enum definition being modified, all references to that enum in the codebase (ORM models, API serializers, client libraries, and raw SQL queries), and the current state of the data in the affected column. This targeted context assembly is critical because a generic codebase dump will exceed context windows and dilute the model's focus, leading to missed references and a false sense of security.

A concrete implementation involves a script that runs on every PR containing a *.sql or migration file with an ALTER TYPE or equivalent enum-modifying statement. The script should: (1) parse the migration to identify the target enum name and the specific change (add, remove, reorder); (2) use grep or a structural search tool like ast-grep to find all code references to that enum, including switch statements, if-else chains, and serialization annotations; (3) query the target database (or a production-like staging copy) to get a distinct count of values in the column, flagging any data that would become invalid after the change; (4) assemble these findings into the [CODE_REFERENCES], [CURRENT_DATA_PROFILE], and [PROPOSED_MIGRATION] placeholders. The harness should then call the LLM with a strict JSON output schema and validate the response. If the JSON is malformed or the severity field is missing, retry once with a repair prompt. Log the full input and output for auditability, especially for BLOCKER findings that should automatically fail the CI check and require a human override to merge.

The primary failure mode of this harness is an incomplete code reference scan. If the search tool misses a dynamic enum reference (e.g., a value constructed via string interpolation or read from a configuration file), the impact analysis will be dangerously incomplete. To mitigate this, the harness should include a secondary check: after the LLM returns its analysis, run a separate validation step that cross-references the LLM's list of identified references against a comprehensive text search for the enum values as string literals. Any string literal match not present in the LLM's analysis should be flagged as a REVIEW_REQUIRED item in the final report. Do not treat this prompt as a replacement for a staging environment migration test; it is a pre-flight risk assessment that reduces, but does not eliminate, the need for integration testing before production deployment.