database-migrations

name	database-migrations
description	Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments across PostgreSQL, MySQL, and common ORMs (Prisma, Drizzle, Kysely, Django, TypeORM, golang-migrate).
mcp	codebase-memory-mcp

/supergraph:database-migrations

Safe, reversible database schema changes for production systems.

Announce: "🗄️ /supergraph:database-migrations — checking blast radius and migration safety..."

When to Activate

• Creating or altering database tables
• Adding/removing columns or indexes
• Running data migrations (backfill, transform)
• Planning zero-downtime schema changes
• Setting up migration tooling for a new project

Steps

1. Check blast radius (MANDATORY before writing any migration)

Use CBM_PROJECT: search_graph schema/model symbols, trace_path inbound and data-flow, then validated dependencies, hubs, and test-gaps recipes. Schema changes to hub tables (e.g. users, orders) ripple through repositories, queries, services. If blast radius > 20 files → STOP and discuss with user.

Serena symbol-level impact (optional):

mcp__serena__find_referencing_symbols(symbol=<column_or_model_name>)

Finds ORM field references that graph tools see only at file level — e.g. a Prisma field rename that query_graph sees as "file touched" but Serena sees as "12 usages in service layer". Skip if Serena unavailable.

2. Choose migration pattern

Select the appropriate pattern from the sections below (PostgreSQL, Prisma, Drizzle, etc.) based on detected project type from .supergraph-env.

3. Write migration

Follow Migration Safety Checklist before writing SQL/ORM migration code.

4. Verify flows

After migration written: Use trace_path(project=CBM_PROJECT, mode="data_flow") and the dependencies recipe. Empty results are unavailable evidence requiring Serena/filesystem fallback, not invented flows. All data flows still intact? Application code updated to match schema?

5. Report

✅ /supergraph:database-migrations
- Pattern: [expand-contract | add-column | add-index | data-migration | ...]
- Blast radius: N files | Hub tables: [list/none]
- Safety checklist: PASS | BLOCKED (list issues)
- Next: /supergraph:tdd → /supergraph:fix → /supergraph:verify

Core Principles

1. Every change is a migration — never alter production databases manually
2. Migrations are forward-only in production — rollbacks use new forward migrations
3. Schema and data migrations are separate — never mix DDL and DML in one migration
4. Test migrations against production-sized data — a migration that works on 100 rows may lock on 10M
5. Migrations are immutable once deployed — never edit a migration that has run in production

Migration Safety Checklist

Before applying any migration:

• Migration has both UP and DOWN (or is explicitly marked irreversible)
• No full table locks on large tables (use concurrent operations)
• New columns have defaults or are nullable (never add NOT NULL without default)
• Indexes created concurrently (not inline with CREATE TABLE for existing tables)
• Data backfill is a separate migration from schema change
• Tested against a copy of production data
• Rollback plan documented

Full ORM examples (PostgreSQL, Prisma, Drizzle, Kysely, Django, golang-migrate): REFERENCE.md

Anti-Patterns

Anti-Pattern	Why It Fails	Better Approach
Manual SQL in production	No audit trail, unrepeatable	Always use migration files
Editing deployed migrations	Causes drift between environments	Create new migration instead
NOT NULL without default	Locks table, rewrites all rows	Add nullable, backfill, then add constraint
Inline index on large table	Blocks writes during build	CREATE INDEX CONCURRENTLY
Schema + data in one migration	Hard to rollback, long transactions	Separate migrations
Dropping column before removing code	Application errors on missing column	Remove code first, drop column next deploy

Rules

• ALWAYS check blast radius before writing any migration — hub table changes need user approval
• NEVER alter production databases manually — every change goes through migration files
• NEVER mix DDL and DML in one migration — separate schema changes from data migrations
• NEVER add NOT NULL column without a default to existing tables — locks and rewrites all rows
• ALWAYS create indexes with CONCURRENTLY on live tables
• ALWAYS test against production-sized data before deploying
• NEVER edit a migration that has already run in production — create a new one
• Use expand-contract pattern for zero-downtime column renames and removals