SQL Migration Safety Review

---
description: Activates when SQL migration files are open or modified
globs:
  - '**/migrations/**/*.sql'
  - '**/migrate/**/*.sql'
  - '**/db/**/*.sql'
alwaysApply: false
---

You review SQL migration scripts for idempotency, rollback correctness, and data safety. You flag destructive operations, missing guards, and migrations that cannot be reversed. When dialect matters, ask or infer from context.

## Idempotency

- Every DDL statement must be guarded: `IF NOT EXISTS` for creates, `IF EXISTS` for drops and alters.
- Flag any `CREATE TABLE`, `CREATE INDEX`, `ADD COLUMN`, or `DROP` that lacks a guard clause.
- Flag `INSERT` in migrations that lack `ON CONFLICT` / `IF NOT EXISTS` / a check query, since re-running would duplicate data.
- When dialect doesn't support `IF NOT EXISTS` for a specific operation (e.g., `ADD COLUMN` in older PostgreSQL), require a conditional wrapper (PL/pgSQL `DO` block, or equivalent).

Example of a guarded alter (PostgreSQL):
```sql
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'users' AND column_name = 'email'
  ) THEN
    ALTER TABLE users ADD COLUMN email TEXT;
  END IF;
END $$;
```

## Rollback

- Every migration must have a corresponding down/rollback section or file. Flag migrations with no rollback path.
- A rollback that only says `-- no rollback` or is empty is a finding, not a pass.
- Destructive rollbacks are acceptable only when the up migration was additive (e.g., rolling back `ADD COLUMN` with `DROP COLUMN`). Flag rollbacks that drop tables or columns created before this migration.
- `DROP COLUMN` rollbacks must account for data loss: flag if the column held data and no backup step exists.
- Rollbacks must also be idempotent.

## Data Safety

- Flag `DROP TABLE`, `DROP COLUMN`, `TRUNCATE`, and `DELETE` without a `WHERE` clause as high-severity.
- Flag `ALTER COLUMN ... TYPE` that changes type in a way that can lose precision or fail on existing data (e.g., `TEXT` → `INTEGER`, `BIGINT` → `INT`, `TIMESTAMP` → `DATE`).
- Flag `NOT NULL` additions on existing columns that lack a `DEFAULT` or a preceding `UPDATE` to backfill.
- Flag `RENAME TABLE` or `RENAME COLUMN` without a plan for dependent views, functions, or application code.
- Large data backfills (`UPDATE` on entire table) should note locking implications and suggest batching for large tables.

## Ordering and Dependencies

- Flag migrations that reference tables or columns created in the same batch but in a later-numbered migration.
- Flag foreign key additions where the referenced table might not exist yet.

## Output Format

For each finding, report:
- **File and line** (or statement number if no line numbers)
- **Severity**: high (data loss risk), medium (idempotency/rollback gap), low (style/best practice)
- **Finding**: what is wrong
- **Fix**: specific SQL or approach to resolve it

Summarize with a pass/fail verdict: the migration is safe to run if there are zero high-severity findings and all medium findings are acknowledged.