Database Schema Change Changelog Entry Prompt Template

This prompt is designed for backend engineers and release managers who need to convert raw database migration details into structured, operationally-focused changelog entries. The primary job-to-be-done is ensuring that downstream teams—such as SREs, QA, and other engineering squads—understand the full operational impact of a schema change before a release ships. The ideal user has access to migration scripts, knows the rollback procedure, and can estimate the expected downtime or locking behavior. Required context includes the raw DDL or migration file, the target database engine and version, and any known compatibility constraints with existing data or running services.

Use this prompt when you need a consistent, machine-readable changelog entry that includes the migration script, a verified rollback plan, and a quantified downtime estimate. It is particularly valuable for teams practicing continuous delivery where schema changes must be reviewed by multiple stakeholders. The output is designed to be embedded in release notes, fed into deployment approval systems, or archived for audit purposes. The prompt enforces a strict output schema, making it suitable for automated pipelines that parse changelog entries and flag high-risk changes for manual review.

Do not use this prompt for documenting application-level configuration changes, infrastructure-as-code modifications, or data migrations that do not alter the database schema itself. It is also not a substitute for a full migration testing suite; the prompt assumes the provided inputs are already tested and validated. If the schema change involves personally identifiable information (PII), regulated data, or changes to encryption or access control models, you must route the generated entry through a human security review before publication. For multi-service migrations where schema changes span several databases, run this prompt once per database and then use a separate consolidation prompt to merge the entries.

The prompt below is designed to be pasted directly into your AI harness. It uses square-bracket placeholders for all dynamic inputs, allowing you to inject migration scripts, PR context, issue tracker links, and output format constraints without modifying the core instruction set. The template enforces a strict output schema that includes migration scripts, rollback instructions, downtime estimates, and compatibility checks, ensuring every changelog entry is actionable for downstream consumers.

textCopy
You are a database release engineer responsible for documenting schema changes in a structured changelog.

Generate a changelog entry for the following database schema change. The entry must be suitable for inclusion in a release note read by backend engineers, SREs, and platform teams.

## INPUT

[MIGRATION_SCRIPT]

[PR_DESCRIPTION]

[ISSUE_TRACKER_LINK]

[DEPLOYMENT_CONTEXT]

## OUTPUT_SCHEMA

Return a JSON object with the following fields:
- change_id: string (unique identifier for this schema change)
- change_type: enum["TABLE_CREATE", "TABLE_ALTER", "TABLE_DROP", "INDEX_ADD", "INDEX_DROP", "CONSTRAINT_ADD", "CONSTRAINT_DROP", "COLUMN_ADD", "COLUMN_ALTER", "COLUMN_DROP", "DATA_MIGRATION", "BACKFILL", "VIEW_CREATE", "VIEW_ALTER", "VIEW_DROP", "FUNCTION_CREATE", "FUNCTION_ALTER", "FUNCTION_DROP", "TRIGGER_CREATE", "TRIGGER_ALTER", "TRIGGER_DROP", "EXTENSION_ADD", "EXTENSION_UPGRADE", "EXTENSION_REMOVE", "PERMISSION_CHANGE", "CONFIG_CHANGE"]
- affected_objects: array of strings (fully qualified table, column, index, constraint, view, function, or trigger names)
- breaking: boolean (true if this change is backward-incompatible with the previous schema version)
- migration_script: string (the exact migration SQL or DSL statement to apply the change)
- rollback_script: string (the exact SQL or DSL statement to reverse the change, or null if irreversible)
- data_impact: string (description of how existing data is affected: none, backfill required, transformation applied, or data loss possible)
- downtime_estimate: string (estimated downtime duration or "zero-downtime" if applicable)
- downtime_mitigation: string (strategy used to minimize downtime: online DDL, blue-green, shadow table, batched backfill, etc.)
- migration_ordering: object with fields `depends_on` (array of change_ids that must run before this one) and `blocks` (array of change_ids that must run after this one)
- compatibility_notes: array of strings (any ORM, driver, connection pool, or application-level compatibility concerns)
- linked_issues: array of strings (issue tracker references)
- contributors: array of strings (engineer handles or names)
- tested_on: array of strings (database versions this was tested against, e.g., ["PostgreSQL 16.2", "MySQL 8.0.36"])
- risk_level: enum["LOW", "MEDIUM", "HIGH", "CRITICAL"]
- human_review_required: boolean (true if this change requires manual review before deployment)

## CONSTRAINTS

1. If the migration script contains DROP operations, set breaking to true and risk_level to at least HIGH.
2. If no rollback is possible, set rollback_script to null and add a note explaining why.
3. If downtime_estimate is greater than "30 seconds", set human_review_required to true.
4. Migration ordering must reference only change_ids present in the same release batch.
5. Never invent issue tracker links; use only those provided in [ISSUE_TRACKER_LINK].
6. If [DEPLOYMENT_CONTEXT] indicates a multi-tenant or sharded environment, include sharding implications in compatibility_notes.
7. For DATA_MIGRATION or BACKFILL change types, include the estimated row count and batch size in data_impact.

## EXAMPLES

Example input:
MIGRATION_SCRIPT: ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP WITH TIME ZONE;
PR_DESCRIPTION: Adds last_login_at column to track user login timestamps for analytics.
ISSUE_TRACKER_LINK: PROJ-1234
DEPLOYMENT_CONTEXT: Single PostgreSQL 16 instance, no sharding.

Example output:
{
  "change_id": "2024-03-15-add-last-login-at",
  "change_type": "COLUMN_ADD",
  "affected_objects": ["public.users.last_login_at"],
  "breaking": false,
  "migration_script": "ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP WITH TIME ZONE;",
  "rollback_script": "ALTER TABLE users DROP COLUMN last_login_at;",
  "data_impact": "None. New column added with NULL default. Existing rows unaffected.",
  "downtime_estimate": "zero-downtime",
  "downtime_mitigation": "Online DDL. Column addition does not require table rewrite on PostgreSQL 16.",
  "migration_ordering": { "depends_on": [], "blocks": [] },
  "compatibility_notes": ["Application ORM must be updated to include new column in SELECT queries if needed."],
  "linked_issues": ["PROJ-1234"],
  "contributors": [],
  "tested_on": ["PostgreSQL 16.2"],
  "risk_level": "LOW",
  "human_review_required": false
}

## RISK_LEVEL

[RISK_LEVEL] should be set to "HIGH" if the deployment context indicates a production database with no staging validation step.

To adapt this template, replace each square-bracket placeholder with the actual data from your migration workflow. The [MIGRATION_SCRIPT] should contain the exact SQL or DSL your migration tool will execute. The [PR_DESCRIPTION] provides engineering intent, while [ISSUE_TRACKER_LINK] grounds the entry in your project management system. The [DEPLOYMENT_CONTEXT] is critical for accurate risk assessment: include database version, hosting model, sharding strategy, and whether a staging environment exists. The [RISK_LEVEL] override at the bottom lets you escalate risk based on deployment context rather than change type alone.

Before wiring this into production, validate the output against your actual migration tooling. The migration_script and rollback_script fields must be executable as-is. Run a dry-run migration in a staging environment using the generated scripts, and verify that migration_ordering does not create circular dependencies. If your team uses a migration framework like Alembic, Flyway, or Prisma Migrate, consider adding a post-generation validation step that parses the generated SQL and confirms it matches the framework's expected format. For high-risk changes where human_review_required is true, route the generated entry to a release manager or DBA for approval before it enters the changelog.

This prompt is designed to be called programmatically as part of a database migration pipeline, not as a one-off manual copy-paste operation. The typical integration point is a CI/CD workflow that triggers when a new migration file is merged to the main branch. The application harness should extract the migration SQL, the previous schema state, and any linked issue tracker context, then assemble the prompt with these inputs before sending it to the model API. The output is a structured changelog entry that must pass validation before it can be appended to CHANGELOG.md or published to a release notes system.

API call structure: Use the Chat Completions endpoint with response_format set to json_object and a JSON Schema constraint matching the expected output shape. The schema should enforce required fields: change_type (enum: breaking, additive, backfill, deprecation), migration_script, rollback_script, downtime_estimate, compatibility_notes, and linked_issues. Set temperature=0.1 to reduce variance in structured output. Validation layer: Before accepting the output, run a deterministic validator that checks: (1) the migration_script and rollback_script are valid SQL with matching table and column references, (2) the downtime_estimate is a non-empty string with a concrete duration or none, (3) linked_issues contains valid issue tracker references matching the input context, and (4) the change_type is consistent with the migration content—a DROP COLUMN should never produce additive. If validation fails, retry once with the validation errors appended to the prompt as [PREVIOUS_ERRORS]. If the second attempt also fails, route to a human review queue.

Retry and fallback logic: Implement a two-strike retry policy. On the first validation failure, append the specific field-level errors to the next prompt attempt so the model can self-correct. On the second failure, log the full prompt, raw output, and validation errors to an observability system (e.g., a dedicated Slack channel or Datadog event) and create a ticket in the migration review backlog. Do not automatically retry beyond two attempts—schema change documentation errors can mislead downstream consumers about breaking changes, so false confidence is worse than a missing entry. Human review gate: For any migration classified as breaking or backfill, flag the generated entry for mandatory human review before publication. The review gate should present the original migration SQL, the generated changelog entry, and a diff against the previous schema. The reviewer must explicitly approve the rollback_script and downtime_estimate fields. For additive changes with no downtime, auto-approval is acceptable if all validators pass.

Model choice and cost: This task benefits from a model with strong SQL comprehension and structured output discipline. GPT-4o or Claude 3.5 Sonnet are appropriate defaults. For high-volume pipelines with dozens of migrations per day, consider routing additive changes to a faster, cheaper model like GPT-4o-mini while reserving the full model for breaking and backfill entries. Observability: Log every prompt invocation with a correlation ID that ties the migration file hash, the model response, validation results, and the final published entry. This trace is essential for debugging when a changelog entry misrepresents a schema change—you need to know whether the error came from the prompt input, the model, or the validation layer.