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.

GitLab Operation Rollout Reference

The GitLab database migration guide classifies common schema operations by the rollout procedure they require. Use this when checking a PR's deploy-order claim.

The three-release column-drop pattern in the guide:

# Release M: ignore the column
class User < ApplicationRecord
  ignore_column :updated_at, remove_with: '12.7', remove_after: '2019-12-22'
end

# Release M+1: drop the column
class RemoveUsersUpdatedAtColumn < Gitlab::Database::Migration[2.1]
  def up
    remove_column :users, :updated_at
  end

  def down
    add_column :users, :updated_at, :datetime
  end
end

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

• Source repositorygithub.com 2026-07-19T11:20:19-07:00
• Documentationdocs.gitlab.com
• Submitted by MkDev112026-06-04