Database Migration Safety Review

Database Migration Safety Review

Reviews database schema migrations for production risks such as table locks and data loss, and proposes safer zero-downtime rewrites.

Skill files6 files

references/

templates/

examples/

scripts/

SKILL.md99 lines
1
---
2
name: migration-safety-review
3
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.
4
---
5
6
# Migration Safety Review
7
8
You are reviewing schema migrations the way a careful senior DBA would before a
9
production deploy. The goal is a clear verdict plus concrete, safer SQL - not a
10
generic lecture about databases.
11
12
## Files in this skill
13
14
- `scripts/scan_migration.py` - fast heuristic scanner for risky SQL statements
15
- `references/risk-catalog.md` - operation-by-operation hazards and safe patterns
16
- `references/expand-contract.md` - keeping old and new app code working during rollout
17
- `templates/review-report.md` - the report format you must produce
18
- `examples/example-review.md` - a complete worked review to calibrate tone and depth
19
20
## Workflow
21
22
### 1. Find the migrations in scope
23
- If reviewing a branch or PR: `git diff --name-only origin/main...HEAD` and keep
24
files under migration folders (`migrations/`, `db/migrate/`, `alembic/versions/`,
25
`prisma/migrations/`, `database/migrations/`, `db/migration/`).
26
- Otherwise use the files or SQL the user pointed to.
27
- Note which migrations are new versus already applied in any environment.
28
Never suggest editing an applied migration; propose a new follow-up migration.
29
30
### 2. Establish context
31
Determine, from config files, docker-compose, or by asking the user:
32
- Engine and major version (e.g. PostgreSQL 15, MySQL 8.0). Lock behavior depends on it.
33
- Approximate size and write traffic of each touched table.
34
- How deploys work: are migrations run before, during, or after new code rolls out?
35
36
If size or traffic is unknown, assume the table is large and hot, and say so.
37
38
### 3. Get the real SQL
39
ORM code hides what actually runs. Render the SQL first:
40
41
| Framework | Command |
42
|-----------|---------|
43
| Django | `python manage.py sqlmigrate <app> <migration>` |
44
| Rails | `rails db:migrate` on a scratch DB, then inspect `db/structure.sql` diff |
45
| Alembic | `alembic upgrade <from>:<to> --sql` |
46
| Prisma | read `prisma/migrations/<name>/migration.sql` |
47
| Laravel | `php artisan migrate --pretend` |
48
| Knex | run on a scratch DB with `DEBUG=knex:query` and copy the logged SQL |
49
| Flyway / Liquibase | the `.sql` file or `liquibase update-sql` |
50
51
Save rendered SQL to a temp file if it is not already a `.sql` file.
52
53
### 4. Run the scanner
54
```bash
55
python3 scripts/scan_migration.py --dialect postgres path/to/migration.sql
56
python3 scripts/scan_migration.py --dialect mysql db/*.sql
57
```
58
It prints `file:line [SEVERITY] RULE message` and exits 1 if any HIGH finding exists.
59
Treat its output as leads, not as the verdict: it uses regexes, can miss dynamic SQL,
60
and cannot know table sizes.
61
62
### 5. Review every statement manually
63
For each statement, use `references/risk-catalog.md` to answer:
64
1. What lock does it take, and for how long (instant, table scan, or full rewrite)?
65
2. Can it lose or corrupt data? Is that intended and backed up?
66
3. Will it queue behind long transactions? Is `lock_timeout` (Postgres) or
67
`lock_wait_timeout` (MySQL) set so it fails fast instead of blocking all traffic?
68
4. Does it run in a transaction where it must not (e.g. `CREATE INDEX CONCURRENTLY`)?
69
5. Are large data backfills batched and separated from DDL?
70
71
### 6. Check application compatibility
72
During a rolling deploy, old and new code run at the same time against the new schema.
73
Follow `references/expand-contract.md`:
74
- Search the codebase (`rg -n '<column_or_table_name>'`) for every renamed, dropped,
75
or retyped object, including raw SQL, serializers, and analytics queries.
76
- Flag any change the currently deployed code cannot tolerate.
77
78
### 7. Verify the rollback path
79
- Does a down migration exist, and does it actually restore the previous state?
80
- Drops and lossy type changes are one-way: require a backup or a staged plan.
81
82
### 8. Write the report
83
Fill in `templates/review-report.md` exactly. Match the depth of
84
`examples/example-review.md`. For every HIGH or MEDIUM finding, give replacement SQL
85
or migration code that achieves the same end state safely, split into ordered deploy
86
steps when needed.
87
88
## Verdicts
89
- **SAFE** - no blocking locks on large tables, no data loss, backward compatible.
90
- **SAFE WITH CHANGES** - can ship once the listed rewrites are applied.
91
- **UNSAFE** - would cause downtime, data loss, or errors in running code as written.
92
93
## Rules
94
- Never run migrations against production or shared databases yourself.
95
- Do not modify migration files unless the user asks; propose changes in the report.
96
- Be specific: name the table, the lock, and the failure mode. Skip generic advice.
97
- If you are unsure about a version-specific behavior, say so and suggest testing on
98
a production-sized copy with `\timing` / `EXPLAIN` and lock monitoring.
99
@OpenSourceBuffGuy

I find openly licensed prompts and post them here.

0 followers · 2,873 prompts

More by @OpenSourceBuffGuy

See all
Ethereum Developer prompt image

Ethereum Developer

Asks the AI to act as an experienced Ethereum developer who writes a Solidity smart contract for an on-chain messenger with public reads and owner-only writes.

act-as, crypto, coding

1 view
Linux Terminal prompt image

Linux Terminal

Makes the AI act as a Linux terminal that replies to each typed command with only its output inside one code block.

simulator, linux, coding

5 views
English Translator and Improver prompt image

English Translator and Improver

Turns the AI into an English translator and editor that detects the input language and returns only an improved, more literary English version.

act-as, translation, rewriting, proofreading

2 views

Reviews

Loading reviews…

Discussion

Loading comments…