name: db-migration-review description: Reviews a database migration pull request and writes a report to /work/outputs/db-review.md. Use when asked to review a migration PR, a schema change, or a SQL migration file. Runs unattended and read-only; never posts to the PR, never modifies the database, never escalates outside the report file.

DB migration review

Unattended, read-only review of a database migration. The only artifact is the report file /work/outputs/db-review.md (canonical path; never write to /tmp/db-review.txt). Reviewing means producing this report. Everything the agent would normally do interactively — comment on the PR, ping anyone, execute SQL — is replaced by a line in the report.

Safety invariants (hold for the whole run)

• Read-only: inspect files and schema only. Do not execute migration SQL against any database. Do not run pg_dump without --schema-only.
• Do not post comments to the pull request or contact any human. Escalation is expressed only inside the report (see step 4).
• Destructive DDL stops further analysis and goes straight to escalation: dropping a column or renaming a table always requires DBA sign-off.

Steps

1. Collect the change. Read the migration SQL from the PR (or the file path given in the request). List every statement with its object and type (CREATE / ALTER / DROP / RENAME / index). Done when every statement in the diff appears in that list.

2. Capture the current schema. Run pg_dump --schema-only against the target database to snapshot the existing schema. Done when the dump succeeds and you can answer, for each touched object, whether it already exists and what its current columns/indexes are. If the dump fails, record the failure in the report and continue with static analysis only.

3. Check each statement against the rules. Every rule below must be applied to every statement; the checklist in the report shows each rule with pass/fail per statement.

   • lock_timeout: every ALTER TABLE must set SET lock_timeout inside the same transaction as the ALTER. Flag any migration missing it. Note whether the timeout value is bounded (a small number of seconds), since an unbounded timeout defeats the purpose.
   • Lock impact: for each statement, state the lock it takes and the table's current size (row count or on-disk size from the schema dump). Long transactions on large tables are the classic incident; a lock on a hot table blocks reads and writes for its full duration.
   • Indexes: for every new or changed query pattern visible in the migration, note whether a supporting index exists in the dumped schema. Flag index builds that cannot run CONCURRENTLY.
   • Backwards compatibility: flag any change that breaks the running application version before the deploy completes (renamed columns, changed types, dropped defaults, added NOT NULL without a default on a populated table).

4. Escalate destructive DDL. If the migration drops a column or renames a table, write the exact phrase ESCALATE: destructive DDL as the first line of the report body, state which statement triggered it, and end the review there — remaining checks are skipped, because DBA sign-off overrides them. Done when the phrase appears verbatim in the report.

5. Write the report to /work/outputs/db-review.md with, in order:

   • ESCALATE: destructive DDL line (only when step 4 fired)
   • Statement inventory from step 1
   • Checklist: each rule with pass/fail and one-sentence evidence per statement
   • Findings: one numbered line per flagged item, most severe first
   • Verdict: APPROVE, REQUEST_CHANGES (any finding), or ESCALATE (step 4 fired)

   Done when the file exists at the canonical path, contains all five sections (minus the escalate line if N/A), and every statement from step 1 has a checklist row. The run ends with the report; no further action.