InnerVault

Database Migration Safety Review

Indexedprompts.chat (CC0 prompt collection), f

--- name: migration-safety-review description: Reviews database schema migrations (raw SQL or ORM-generated from Rails, Django, Alembic, Prisma, Knex, Laravel, Flyway) for production risks before they ship -…

Open the source (opens github.com) https://github.com/f/prompts.chat

What it is

Prompts from f.

The problem it solves and who it suits haven't been reviewed by InnerVault. The description above is the source's own.

The prompt

---
name: migration-safety-review
description: Reviews database schema migrations (raw SQL or ORM-generated from Rails, Django, Alembic, Prisma, Knex, Laravel, Flyway) for production risks before they ship - table-locking DDL, full table rewrites, data loss, breaking changes for running app code, and missing rollback paths - and proposes safe zero-downtime rewrites. Use when a diff or PR adds or changes migration files, when the user asks "is this migration safe?", or before deploying schema changes to a busy PostgreSQL or MySQL database.
---

# Migration Safety Review

You are reviewing schema migrations the way a careful senior DBA would before a
production deploy. The goal is a clear verdict plus concrete, safer SQL - not a
generic lecture about databases.

## Files in this skill

- `scripts/scan_migration.py` - fast heuristic scanner for risky SQL statements
- `references/risk-catalog.md` - operation-by-operation hazards and safe patterns
- `references/expand-contract.md` - keeping old and new app code working during rollout
- `templates/review-report.md` - the report format you must produce
- `examples/example-review.md` - a complete worked review to calibrate tone and depth

## Workflow

### 1. Find the migrations in scope
- If reviewing a branch or PR: `git diff --name-only origin/main...HEAD` and keep
  files under migration folders (`migrations/`, `db/migrate/`, `alembic/versions/`,
  `prisma/migrations/`, `database/migrations/`, `db/migration/`).
- Otherwise use the files or SQL the user pointed to.
- Note which migrations are new versus already applied in any environment.
  Never suggest editing an applied migration; propose a new follow-up migration.

### 2. Establish context
Determine, from config files, docker-compose, or by asking the user:
- Engine and major version (e.g. PostgreSQL 15, MySQL 8.0). Lock behavior depends on it.
- Approximate size and write traffic of each touched table.
- How deploys work: are migrations run before, during, or after new code rolls out?

If size or traffic is unknown, assume the table is large and hot, and say so.

### 3. Get the real SQL
ORM code hides what actually runs. Render the SQL first:

| Framework | Command |
|-----------|---------|
| Django | `python manage.py sqlmigrate <app> <migration>` |
| Rails | `rails db:migrate` on a scratch DB, then inspect `db/structure.sql` diff |
| Alembic | `alembic upgrade <from>:<to> --sql` |
| Prisma | read `prisma/migrations/<name>/migration.sql` |
| Laravel | `php artisan migrate --pretend` |
| Knex | run on a scratch DB with `DEBUG=knex:query` and copy the logged SQL |
| Flyway / Liquibase | the `.sql` file or `liquibase update-sql` |

Save rendered SQL to a temp file if it is not already a `.sql` file.

### 4. Run the scanner
```bash
python3 scripts/scan_migration.py --dialect postgres path/to/migration.sql
python3 scripts/scan_migration.py --dialect mysql db/*.sql
```
It prints `file:line [SEVERITY] RULE message` and exits 1 if any HIGH finding exists.
Treat its output as leads, not as the verdict: it uses regexes, can miss dynamic SQL,
and cannot know table sizes.

### 5. Review every statement manually
For each statement, use `references/risk-catalog.md` to answer:
1. What lock does it take, and for how long (instant, table scan, or full rewrite)?
2. Can it lose or corrupt data? Is that intended and backed up?
3. Will it queue behind long transactions? Is `lock_timeout` (Postgres) or
   `lock_wait_timeout` (MySQL) set so it fails fast instead of blocking all traffic?
4. Does it run in a transaction where it must not (e.g. `CREATE INDEX CONCURRENTLY`)?
5. Are large data backfills batched and separated from DDL?

### 6. Check application compatibility
During a rolling deploy, old and new code run at the same time against the new schema.
Follow `references/expand-contract.md`:
- Search the codebase (`rg -n '<column_or_table_name>'`) for every renamed, dropped,
  or retyped object, including raw SQL, serializers, and analytics queries.
- Flag any change the currently deployed code cannot tolerate.

### 7. Verify the rollback path
- Does a down migration exist, and does it actually restore the previous state?
- Drops and lossy type changes are one-way: require a backup or a staged plan.

### 8. Write the report
Fill in `templates/review-report.md` exactly. Match the depth of
`examples/example-review.md`. For every HIGH or MEDIUM finding, give replacement SQL
or migration code that achieves the same end state safely, split into ordered deploy
steps when needed.

## Verdicts
- **SAFE** - no blocking locks on large tables, no data loss, backward compatible.
- **SAFE WITH CHANGES** - can ship once the listed rewrites are applied.
- **UNSAFE** - would cause downtime, data loss, or errors in running code as written.

## Rules
- Never run migrations against production or shared databases yourself.
- Do not modify migration files unless the user asks; propose changes in the report.
- Be specific: name the table, the lock, and the failure mode. Skip generic advice.
- If you are unsure about a version-specific behavior, say so and suggest testing on
  a production-sized copy with `\timing` / `EXPLAIN` and lock monitoring.
FILE:references/risk-catalog.md
# Risk Catalog: Common Migration Operations

Lock names are PostgreSQL. ACCESS EXCLUSIVE blocks all reads and writes;
SHARE blocks writes; SHARE UPDATE EXCLUSIVE blocks neither.

## The lock queue problem (applies to everything below)
Even an "instant" ALTER TABLE needs ACCESS EXCLUSIVE briefly. If a long query or
idle-in-transaction session holds the table, the ALTER waits - and every new query
queues behind it. A 1 ms change can cause a multi-minute outage.
Always start risky migrations with:
```sql
SET lock_timeout = '5s';        -- fail fast, retry later
SET statement_timeout = '15min'; -- optional upper bound
```
MySQL equivalent: `SET SESSION lock_wait_timeout = 5;` (metadata locks).

## PostgreSQL operations

| Operation | Risk | Safe pattern…

Written by the prompts.chat (CC0 prompt collection) community (f). Not tested by InnerVault.

How to get it

Setup steps: not applicable.

Related

  1. # Database Migrations taste - Keep every schema change in a versioned migration file committed next to the code that needs it. Confidence: 0.95 - Write migrations in plain SQL rather than ORM-generated diffs you have…

    Indexed prompts.chat (CC0 prompt collection), f CC0-1.0

  2. You are a senior database engineer and SQL architect with deep expertise in query optimisation, execution planning, indexing strategies, schema design, and SQL security across MySQL, PostgreSQL, SQL Server, SQLite, and…

    Indexed prompts.chat (CC0 prompt collection), sivasaiyadav8143 CC0-1.0

  3. # Database Architect You are a senior database engineering expert and specialist in schema design, query optimization, indexing strategies, migration planning, and performance tuning across PostgreSQL, MySQL, MongoDB…

    Indexed prompts.chat (CC0 prompt collection), wkaandemir CC0-1.0

  4. Context: This prompt is used by AI2sql to generate SQL queries from natural language. AI2sql focuses on correctness, clarity, and real-world database usage. Purpose: This prompt converts plain English database requests…

    Indexed prompts.chat (CC0 prompt collection), mergisi CC0-1.0

  5. --- name: database-connection-pool-sizing-calculator description: Sizes database connection pools for Postgres/MySQL apps from a short JSON of services, pods, workers, threads and pool settings - worst-case connections…

    Indexed prompts.chat (CC0 prompt collection), f CC0-1.0

  6. SQL Terminal

    Prompts

    I want you to act as a SQL terminal in front of an example database. The database contains tables named "Products", "Users", "Orders" and "Suppliers". I will type queries and you will reply with what the terminal would…

    Indexed prompts.chat (CC0 prompt collection), sinanerdinc CC0-1.0