Build a Schema Migration Review Agent: Catch Locking ALTERs, Unsafe Backfills, and Missing CONCURRENTLY Before Postgres Goes Down

What This Agent Does

A schema migration review agent runs on every pull request that touches a migrations directory and answers three questions about each statement: what lock does it take, how long will it hold that lock on this database, and is there a rewrite that does the same thing without blocking traffic. A deterministic linter finds the dangerous statements. A read-only query against a replica turns "ALTER TABLE orders" into "ALTER TABLE on 412 million rows that receive 900 writes a second." The LLM gets only that enriched finding and writes the PR comment: the lock, the expected blast radius, and the expand-and-contract rewrite, as SQL the author can paste. The agent never connects the model to the database, never applies a migration, and never approves its own rewrite. It blocks the merge on critical findings until a human either fixes the migration or accepts the risk with a label.

The deployment risk scoring agent treats "a migration is in the diff" as one risk flag and stops there. That is the right level for a score. It is the wrong level for the review, because a safe migration and an outage-grade migration look identical at the path level. This agent reads the SQL.

Why Migrations Take Databases Down

Almost every migration outage on Postgres is a lock queue, not a slow statement. ALTER TABLE needs an ACCESS EXCLUSIVE lock. If one long-running SELECT holds a conflicting lock, the ALTER waits, and every new query on that table queues behind the ALTER, including the sub-millisecond reads your API depends on. The migration itself might take 4 milliseconds once it runs. The 40 seconds it spent waiting took the service down.

The second cause is a statement that holds a strong lock for as long as it takes to touch every row.

Every row in that table has a known safe form. The problem is not knowledge. The problem is that the knowledge lives in one or two engineers' heads and the migration PR gets reviewed by whoever is free. The agent moves the knowledge into the pipeline.

Step 1: Deterministic Lint With squawk

squawk is a Postgres migration linter that parses the SQL with the real Postgres grammar and emits one finding per violated rule. It is the detection layer, and it has to stay deterministic, because a model that sometimes misses CREATE INDEX without CONCURRENTLY is worse than no check at all.

# .github/workflows/migration-review.yml (excerpt)
on:
  pull_request:
    paths: ["db/migrations/**"]

jobs:
  review:
    runs-on: ubuntu-latest
    permissions:
      contents: read
      pull-requests: write
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }
      - name: Changed migration files
        id: files
        run: |
          git diff --name-only --diff-filter=A origin/${{ github.base_ref }}...HEAD \
            -- 'db/migrations/*.sql' > changed.txt
          echo "count=$(wc -l < changed.txt)" >> "$GITHUB_OUTPUT"
      - name: Lint
        if: steps.files.outputs.count != '0'
        run: |
          npm i -g squawk-cli
          xargs squawk --pg-version 16.0 --reporter json < changed.txt > lint.json || true

Two details matter here. --diff-filter=A restricts the review to newly added files, so editing a comment in an old migration does not re-trigger a critical finding. And the || true is deliberate: squawk exits non-zero on findings, but the merge decision belongs to a later step that has the table stats, not to the linter.

The rules that correspond to the outage table above are require-concurrent-index-creation, adding-field-with-default, changing-column-type, adding-foreign-key-constraint, adding-not-nullable-field, constraint-missing-not-valid, ban-drop-column, and prefer-robust-stmts. Each JSON finding carries the rule name, the file, the line, and the statement text, which is exactly the shape the next step needs.

Step 2: Size the Lock Against the Real Table

A CREATE INDEX without CONCURRENTLY on a 2,000-row lookup table is a style nit. On a 400-million-row events table it is a write outage. The linter cannot tell the difference, so the enricher asks the database. It connects to a read replica with a role that can see catalog statistics and nothing else, and it uses the same safety posture as the read-only Postgres MCP server: statement timeout, no write grants, no row data.

-- One-time setup on the replica (or primary; grants replicate)
CREATE ROLE migration_reviewer LOGIN PASSWORD '...' NOINHERIT;
ALTER ROLE migration_reviewer SET statement_timeout = '5s';
ALTER ROLE migration_reviewer SET default_transaction_read_only = on;
GRANT pg_read_all_stats TO migration_reviewer;
-- No GRANT SELECT on any user table. Catalog views are enough.

# enrich.py — read-only; runs in CI with DATABASE_REVIEW_URL pointing at a replica
import json, os, re, psycopg

STATS = """
SELECT c.relname,
       c.reltuples::bigint                                   AS est_rows,
       pg_total_relation_size(c.oid)                         AS bytes,
       s.n_tup_ins + s.n_tup_upd + s.n_tup_del               AS writes_total,
       EXTRACT(EPOCH FROM now() - s.stats_reset)             AS stats_age_s,
       (SELECT count(*) FROM pg_index i WHERE i.indrelid = c.oid) AS index_count
FROM pg_class c
JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE c.relname = %s AND c.relkind = 'r'
"""

TABLE_RE = re.compile(
    r"\b(?:ALTER\s+TABLE|CREATE\s+(?:UNIQUE\s+)?INDEX(?:\s+CONCURRENTLY)?\s+\S+\s+ON|UPDATE)"
    r"\s+(?:ONLY\s+)?(?:IF\s+EXISTS\s+)?(?:\"?\w+\"?\.)?\"?(\w+)\"?", re.I)

def table_of(stmt: str) -> str | None:
    m = TABLE_RE.search(stmt)
    return m.group(1) if m else None

def enrich(findings: list[dict]) -> list[dict]:
    out = []
    with psycopg.connect(os.environ["DATABASE_REVIEW_URL"]) as conn:
        for f in findings:
            t = table_of(f["statement"])
            row = None
            if t:
                row = conn.execute(STATS, (t,)).fetchone()
            if row:
                relname, est_rows, nbytes, writes, age_s, idx = row
                f["table"] = {
                    "name": relname, "est_rows": est_rows,
                    "gb": round(nbytes / 1e9, 2), "index_count": idx,
                    "writes_per_s": round(writes / max(age_s, 1), 1),
                }
            else:
                f["table"] = {"name": t, "unknown": True}   # new table in this PR: safe by definition
            out.append(f)
    return out

if __name__ == "__main__":
    raw = json.load(open("lint.json"))
    findings = [{"rule": v["rule_name"], "file": r["file"], "line": v["line"],
                 "statement": v.get("statement", ""), "message": v["messages"][0]["Note"]}
                for r in raw for v in r["violations"]]
    json.dump(enrich(findings), open("findings.json", "w"), indent=1)

The regex table extractor is intentionally simple. It only needs the table name for a catalog lookup, and a miss degrades to "unknown table," which the scorer treats as a new table. reltuples is an estimate refreshed by ANALYZE and autovacuum, which is fine for a review and avoids a COUNT(*) that would be its own slow query. If your stats look stale, the Postgres tuning guide covers the autovacuum settings that keep them fresh.

Step 3: Score Before the Model Sees Anything

Severity is a function of the lock class and the table facts, computed in code, so the same migration gets the same verdict every run.

# score.py
REWRITE   = {"adding-field-with-default", "changing-column-type"}
FULL_SCAN = {"require-concurrent-index-creation", "adding-foreign-key-constraint",
             "adding-not-nullable-field", "constraint-missing-not-valid"}
IRREVERSIBLE = {"ban-drop-column", "ban-drop-table"}

def severity(f: dict) -> str:
    t = f["table"]
    if t.get("unknown"):
        return "info"                       # table created in this PR
    big   = t["est_rows"] > 1_000_000 or t["gb"] > 1.0
    busy  = t["writes_per_s"] > 50
    if f["rule"] in REWRITE and big:
        return "critical"
    if f["rule"] in FULL_SCAN and (big or busy):
        return "critical" if big and busy else "high"
    if f["rule"] in IRREVERSIBLE:
        return "high"
    if "lock_timeout" not in f.get("file_text", "").lower():
        return "medium"                     # safe statement, but no lock_timeout guard
    return "low"

The thresholds are the part you will tune. One million rows and 50 writes a second are where, on the clusters I have run, a full-scan lock stops being a blip and starts showing up in the error budget. The point is not the numbers. The point is that a human agreed to them once, in a PR, and the model cannot move them.

Step 4: The Model Writes the Rewrite, Not the Verdict

The LLM receives the enriched findings for one file and returns a structured review. It does not decide severity, and it does not get a database connection. Its job is to turn a finding like require-concurrent-index-creation on a 1.9 TB table into a comment a backend engineer can act on in five minutes.

# review.py
import json, os, anthropic

SYSTEM = """You review PostgreSQL schema migrations for a team that deploys without downtime.
You receive lint findings, each with the offending statement and live statistics for the
table it touches. For each finding, explain the lock it takes and what traffic it blocks,
estimate the impact using ONLY the provided row count, size and write rate, and provide a
rewritten migration that achieves the same schema change without a long-held strong lock.
Rules:
- Every rewrite must start with SET lock_timeout and be split into separate files where
  CONCURRENTLY or VALIDATE CONSTRAINT requires running outside a transaction.
- Prefer expand-and-contract: add nullable, backfill in batches by primary key, then constrain.
- Never invent table facts. If a fact is missing, say so.
- Do not change the severity you are given."""

def review(findings: list[dict], file_text: str) -> dict:
    client = anthropic.Anthropic()
    msg = client.messages.create(
        model="claude-sonnet-5", max_tokens=4000, system=SYSTEM,
        tools=[{
            "name": "migration_review",
            "description": "Structured review of one migration file",
            "input_schema": {
                "type": "object",
                "properties": {
                    "findings": {"type": "array", "items": {"type": "object", "properties": {
                        "line": {"type": "integer"},
                        "lock": {"type": "string"},
                        "blocks": {"type": "string",
                                   "description": "What traffic waits, e.g. 'all writes to events'"},
                        "impact": {"type": "string",
                                   "description": "Duration estimate grounded in the given stats"},
                        "rewrite_sql": {"type": "array", "items": {"type": "string"},
                                        "description": "One entry per migration file"}},
                        "required": ["line", "lock", "blocks", "impact", "rewrite_sql"]}},
                    "summary": {"type": "string"}},
                "required": ["findings", "summary"]}}],
        tool_choice={"type": "tool", "name": "migration_review"},
        messages=[{"role": "user", "content":
                   "Migration file (SQL):\n" + file_text + "\n\nFindings (JSON):\n"
                   + json.dumps(findings, indent=1)}])
    return next(b.input for b in msg.content if b.type == "tool_use")

For a CREATE INDEX idx_events_account ON events (account_id) finding on that 1.9 TB table, the rewrite the model should produce is a three-file sequence, and in practice this is what a well-prompted model returns:

-- 0043_events_account_idx.sql   (run outside a transaction; most tools use a marker
-- such as "-- +goose NO TRANSACTION" or a .no-txn suffix)
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events_account ON events (account_id);

-- 0044_events_account_idx_verify.sql
-- CONCURRENTLY can leave an INVALID index if it is interrupted. Fail loudly instead of
-- shipping a half-built index that the planner ignores.
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
             WHERE c.relname = 'idx_events_account' AND NOT i.indisvalid) THEN
    RAISE EXCEPTION 'idx_events_account is INVALID; drop and recreate concurrently';
  END IF;
END $$;

The lock_timeout is the single most valuable line in that output. With it set, the migration fails fast instead of building a queue, and a failed migration is a retry. Without it, the same statement is an incident.

Step 5: Post, Gate, and Let a Human Override

The last job turns the structured review into a PR comment and a status check. Critical findings fail the check. A maintainer who has read the comment and still wants to proceed, perhaps because the deploy is in a maintenance window, adds the label migration-risk-accepted, and the check passes with that fact recorded. This is the same escalation shape as the approval gates post: the agent recommends, the human decides, the decision is attributable.

      - name: Review and gate
        if: steps.files.outputs.count != '0'
        env:
          ANTHROPIC_API_KEY: ${{ secrets.ANTHROPIC_API_KEY }}
          DATABASE_REVIEW_URL: ${{ secrets.DB_REVIEW_REPLICA_URL }}
          GH_TOKEN: ${{ github.token }}
        run: |
          python enrich.py && python score.py && python review.py > review.md
          gh pr comment ${{ github.event.pull_request.number }} --body-file review.md
          if jq -e '.[] | select(.severity=="critical")' findings.json > /dev/null; then
            if gh pr view ${{ github.event.pull_request.number }} --json labels \
               | jq -e '.labels[] | select(.name=="migration-risk-accepted")' > /dev/null; then
              echo "::warning::critical migration risk accepted by label"
            else
              echo "::error::critical migration findings; fix or add migration-risk-accepted"
              exit 1
            fi
          fi

Make the check a required status in branch protection. Without that, the comment is advice, and advice at 17:50 on a Friday loses to the merge button. The posted comment should also name who accepted the risk when the label path is taken, which the audit trail post covers in detail.

What It Catches on Day One

Running this against a year of merged migrations on a mid-sized monolith is a useful calibration exercise before enabling the gate. Expect to find three recurring patterns, in roughly this order of frequency: indexes created without CONCURRENTLY on tables that were small when the migration was written and are not small now, NOT NULL columns added with a default in one statement, and backfill UPDATEs with no WHERE clause and no batching. The first two have direct rewrites. The third is where the model's comment earns its keep, because the batched version has to be written against the real primary key and the real row count, and that is exactly the context the enricher supplies.

The same pattern of deterministic detection, read-only enrichment, and a model that explains and rewrites but never decides, is the one the Terraform plan review agent uses for infrastructure. Schema changes deserve it at least as much, because a bad terraform apply is usually reversible and a bad ALTER TABLE on a billion rows is not.

Honest Limits

The linter only sees SQL. Migrations written in an ORM's DSL (Alembic operations, Rails change blocks, Prisma schemas) have to be rendered to SQL first, which most tools support with a dry-run or --sql flag, and that step belongs in the workflow before squawk runs. Statistics from a replica reflect the replica's autovacuum schedule and can lag the primary, so treat row counts as order-of-magnitude. The agent reasons about Postgres locks specifically; MySQL's online DDL and its ALGORITHM=INSTANT rules are a different table and a different linter. And no review catches a migration that is safe in isolation and dangerous because of what the application does in the same deploy. That interaction is still a human's job, and the agent's best contribution is to make the human's five minutes count.