Production Database Migration Safety Rules

## Purpose

Use these rules when an AI coding assistant, ORM generator, migration tool, or
human contributor proposes a database schema or data migration that may reach a
shared environment.

The goal is to make risky migrations boring to review. A migration should name
the operational risk, prove that old and new application versions can coexist,
show how the team tested the path, and explain what rollback really means for
the affected data.

## Migration Risk Classes

Classify the migration before reviewing style or generated-file formatting.

1. **Additive schema change.** Adds nullable columns, tables, indexes,
   constraints marked for later validation, or compatibility fields.
2. **Backfill or data rewrite.** Updates existing rows, copies data between
   columns, changes data shape, or runs an application job that touches many
   records.
3. **Constraint or index change.** Adds uniqueness, foreign keys, not-null
   requirements, check constraints, partial indexes, or generated columns.
4. **Destructive cleanup.** Drops columns, tables, indexes, values, or old
   compatibility paths after a release has already stopped using them.
5. **Rename or type change.** Renames objects, changes enum semantics, changes
   column types, or rewrites application assumptions about stored values.
6. **Operational migration.** Changes replication, partitions, extensions,
   permissions, database roles, retention, archives, triggers, or scheduled
   jobs.

If the class is unclear, treat the migration as high risk until the submitter
can show the SQL or engine behavior that will actually run.

## Required Evidence

A production migration PR should include enough evidence for a reviewer to
understand the release decision later.

- The target environment, database engine, migration tool, and reviewed SQL or
  generated diff are named.
- The migration is tied to the application deploy order, including whether old
  and new app versions can read and write during rollout.
- Lock risk, table rewrite risk, index-build behavior, and long-transaction
  behavior are checked against the target engine documentation.
- Staging, dry-run, shadow database, or equivalent validation ran after the
  latest migration change.
- Backfills have batching, retry, idempotency, progress monitoring, stop
  conditions, and ownership.
- Backup, restore, rollback, or forward-fix limits are stated honestly,
  especially when data loss or irreversible cleanup is possible.
- Privacy-sensitive logs, row samples, metrics, explain plans, and schema
  details are redacted before public review.

Do not approve a production migration when the PR only says that the ORM
generated it successfully. The reviewer needs operational evidence, not only a
schema diff.

## Safe Rollout Rules

- Prefer expand-contract rollout for required fields, renames, type changes,
  and data moves: add the new shape, deploy compatible code, backfill, switch
  reads and writes, then clean up later.
- Keep destructive cleanup in a separate PR or later migration after production
  code no longer depends on the old object.
- Build large indexes with the database engine's non-blocking option when
  available and document any remaining write-lock window.
- Add constraints in a way that avoids surprising table scans or long blocking
  locks, then validate after data is known to satisfy the rule.
- Split large data backfills from schema changes when a failure would require
  retry logic, progress tracking, throttling, or operator intervention.
- Avoid application startup migrations in production unless the deployment
  platform, lock behavior, concurrency, and rollback path are explicit.
- Review generated ORM migrations before apply, especially drops, renames,
  not-null changes, enum changes, default changes, and implicit data rewrites.

## Reviewer Rules

- Start by asking what SQL or engine operation will run, not what the ORM model
  looked like before generation.
- Confirm that the migration can run safely while old and new application
  versions overlap during rolling deploys.
- Require a data owner or service owner for migrations that touch customer
  records, tenant boundaries, billing, auth, audit logs, or retention policy.
- Re-check risk after rebases, regenerated migrations, dependency upgrades, or
  framework-version changes that can alter generated SQL.
- Treat migration credentials, backup locations, table samples, row counts, and
  production metrics as sensitive review material.
- Ask for a forward-fix plan when rollback cannot restore the previous data
  shape without backup restore.

## Merge Blockers

Block merge until resolved when:

- the migration drops, truncates, renames, rewrites, or changes required data
  without an expand-contract or cleanup-window explanation;
- generated SQL, migration tool output, or database-engine behavior is not
  visible to reviewers;
- the PR can lock writes, scan large tables, rebuild indexes, or hold long
  transactions without mitigation;
- old and new application versions cannot coexist during the deployment order;
- a backfill lacks batching, idempotency, monitoring, retry, or stop criteria;
- tests or dry-run evidence are stale after the latest migration edit;
- rollback claims ignore irreversible data loss, constraint side effects, or
  backup restore requirements;
- public comments include production rows, raw logs, connection strings,
  hostnames, tenant identifiers, or private schema details.

## Review Checklist

- [ ] {"task": "Risk class named", "description": "The PR classifies additive, backfill, constraint, destructive, rename, type, or operational migration risk"}
- [ ] {"task": "SQL reviewed", "description": "Reviewers can inspect the SQL, engine operation, or generated migration diff that will run"}
- [ ] {"task": "Deploy order safe", "description": "Old and new application versions can coexist, or the rollout window and owner exception are explicit"}
- [ ] {"task": "Lock risk checked", "description": "Table locks, scans, index builds, long transactions, and constraint validation are reviewed against the target engine"}
- [ ] {"task": "Backfill controlled", "description": "Large data changes have batching, idempotency, progress monitoring, retry behavior, and stop conditions"}
- [ ] {"task": "Privacy protected", "description": "Logs, samples, row counts, hostnames, backup paths, and schema details are redacted or kept in approved private channels"}

## AI Review Rules

AI assistants can help classify a migration, but they should not invent
operational certainty.

- Ask the assistant to list risky SQL operations and unknowns separately.
- Require file paths, migration names, and engine-specific behavior for each
  warning.
- Have the assistant compare generated migrations against model changes rather
  than trusting either side alone.
- Do not let the assistant run production migration commands, connect to live
  databases, or inspect real rows without explicit operator approval.
- Re-run the review after generation, rebase, or manual SQL edits.

## Troubleshooting

- **A rename is needed:** add the new object, dual-write or copy data, switch
  reads, then remove the old object after compatible code has shipped.
- **A not-null constraint is needed:** backfill first, enforce application
  writes, then add or validate the constraint with engine-aware locking review.
- **An index build is slow:** choose the non-blocking index path supported by
  the engine, monitor progress, and document the write-lock tradeoff.
- **The rollback is not reversible:** say so. Use backup restore, forward-fix,
  or delayed cleanup instead of promising a simple revert.
- **Logs contain real data:** move evidence to a private channel, redact public
  notes, and leave only the verification class, owner, and result in the PR.

## Duplicate And History Check

Checked existing rules, hooks, agents, skills, guides, collections, open PRs,
and closed PR history for database migration safety, production schema-change
rules, zero-downtime migration review, backfill review, safe migration hooks,
and ORM migration workflows.

Adjacent content includes a database migration safety hook, a database migration
runner hook, database expert agents, Drizzle migration skill content, and a
high-risk code review escalation rule. This entry is distinct because it is a
portable rules policy for merge review: it decides what production migration
evidence must exist, when expand-contract rollout is required, how reviewers
classify lock and data-loss risk, and what privacy constraints apply to
migration evidence.

## Sources

- GitLab Docs: Avoiding downtime in migrations - https://docs.gitlab.com/development/database/avoiding_downtime_in_migrations/
- PostgreSQL Docs: ALTER TABLE - https://www.postgresql.org/docs/current/sql-altertable.html
- PostgreSQL Docs: CREATE INDEX - https://www.postgresql.org/docs/current/sql-createindex.html
- Prisma Migrate: Development and production - https://www.prisma.io/docs/orm/prisma-migrate/workflows/development-and-production
- Rails Guides: Active Record Migrations - https://guides.rubyonrails.org/active_record_migrations.html