# How do I make an AI agent check database invariants before and after a migration?

**Short answer:** Have the invariants written down before the agent writes the migration. The agent captures them before and after, and the migration fails if they don't hold. Invariants are facts that must survive the change: row counts per table (or the exact change you expect), sums of money and quantity columns, counts of NULLs, orphans and duplicates, and which constraints exist and are validated. Use exact values from count(*) and sum(), not planner statistics. PostgreSQL documents pg_class.reltuples as only an estimate used by the planner. Run the migration and checks on a copy first. PostgreSQL can run most schema changes inside a transaction, so the after-checks can run before COMMIT and a failure rolls everything back. In MySQL, DDL such as ALTER TABLE commits implicitly, so plan to restore or fix forward instead. Have CI or the migration runner enforce the checks, not the agent's own report, and keep both reports for the reviewer.

## Checklist
1. Have the agent write the invariant list first, in the migration's pull request, and have a person approve it before any migration code. Spell out what stays equal and what changes by how much, for example: orders row count unchanged, sum of orders.total_cents unchanged, 0 rows with NULL customer_id.
2. Capture exact numbers per table, such as count(*), sum(amount) and count(*) FILTER (WHERE col IS NULL). Group them by a meaningful key (tenant, month) so a loss in one group can't hide behind a gain in another.
3. Check relationships: count orphans with LEFT JOIN parent ... WHERE parent.id IS NULL, count duplicates on columns that must be unique, and on SQLite run PRAGMA foreign_key_check.
4. Snapshot constraints on the affected tables (in PostgreSQL, pg_constraint with convalidated) before and after, and diff them so a dropped or never-validated constraint shows up.
5. Run on a copy first (a restored backup, staging or a database branch) with the same checks, and compare its before and after reports with what you approved.
6. PostgreSQL: run the migration and the after-checks in one transaction, and have a failed check raise an error so nothing commits. If you use deferrable constraints, run SET CONSTRAINTS ALL IMMEDIATE before the checks.
7. MySQL: ALTER TABLE, CREATE INDEX, DROP TABLE, RENAME TABLE and TRUNCATE TABLE commit implicitly, so ROLLBACK can't undo them. Take a backup or snapshot first, and treat failed checks as a decision to restore or fix forward.
8. Make the checks the gate: CI or the migration runner executes them and fails the job on any mismatch, and the agent can't mark them passed.
9. Adding a constraint to a large PostgreSQL table: ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT, then confirm convalidated is true.

## Why exact counts and transactions matter
- PostgreSQL's pg_class.reltuples is documented as only an estimate used by the planner. VACUUM, ANALYZE and a few DDL commands update it, and it is -1 for a table that has never been vacuumed or analyzed. It's fine for sizing, but it can't prove that no rows were lost.
- The PostgreSQL wiki on transactional DDL explains that changes in a transaction block apply atomically or not at all. Creating or dropping a database or tablespace can't be rolled back. CREATE INDEX CONCURRENTLY can't run inside a transaction block either, so put steps like that in a separate migration with their own checks.
- MySQL's manual lists DDL statements, including ALTER TABLE, CREATE INDEX, DROP TABLE, RENAME TABLE and TRUNCATE TABLE, as implicitly ending any active transaction as if COMMIT had run.
- In PostgreSQL, NOT NULL and CHECK constraints are always checked as soon as a row is inserted or modified. Only UNIQUE, PRIMARY KEY, foreign key and EXCLUDE constraints can be deferred, and SET CONSTRAINTS can force deferred checks at a chosen point in the transaction.

## Constraints that exist but haven't checked old rows
- In PostgreSQL, ADD CONSTRAINT ... NOT VALID (allowed for foreign key, CHECK and not-null constraints) skips the scan of existing rows. New and updated rows are checked, but the database doesn't assume old rows comply until VALIDATE CONSTRAINT succeeds. pg_constraint.convalidated shows which state each constraint is in. An agent that adds a NOT VALID constraint and stops hasn't proved anything about existing data.
- SQLite turns foreign key enforcement off by default. It has to be enabled on each connection with PRAGMA foreign_keys = ON, and that pragma does nothing inside a transaction. PRAGMA integrity_check doesn't find foreign key errors, so run PRAGMA foreign_key_check as well.

## A simple shape for the check script
- Before the migration, write each invariant to a table or file as phase, check name and value, along with the expected change (0 for 'unchanged'). After the migration, compute the same checks and list every check where after minus before isn't the expected change. If the list isn't empty, fail the job, and inside a PostgreSQL transaction, raise an error before COMMIT.
- Keep the before and after reports with the migration's pull request, so the reviewer sees the numbers rather than the agent's summary of them.

## Ask Rightshold (free, no key)
Add the MCP server https://www.rightshold.com/mcp to Claude, Cursor, ChatGPT, Grok or VS Code (one-click setup: https://www.rightshold.com/start?ref=seo), then ask: "Record a signed receipt for my agent's migration run: a hash of the migration file and of the before and after invariant reports, so a reviewer can check later that the reports weren't changed."

```json
{
  "tool": "record_ai_run",
  "arguments": {
    "model": "<model the agent used>",
    "provider": "<provider>",
    "prompt_sha256": "<sha256 of the migration file>",
    "output_sha256": "<sha256 of the after-migration invariant report>",
    "params": {
      "migration": "<migration name>",
      "before_report_sha256": "<sha256 of the before report>"
    },
    "ran_at": "2026-10-30T14:00:00Z"
  }
}
```
- Needs an API key. Only the hashes and the fields you send are recorded. The receipt proves what was recorded and when, not that the migration was correct.
- verify_ai_run is free. A reviewer sends the receipt id, or the receipt and its signature, and can add the report text to compare it with the recorded hash.
- precall_check (free, no key) reports what is on record about a database MCP server before you connect your agent to it.

_Rightshold doesn't connect to your database or run your migrations or checks. It records hashes of reports you send and verifies them later. Not legal advice._

## Sources
- [PostgreSQL docs: ALTER TABLE (NOT VALID, VALIDATE CONSTRAINT)](https://www.postgresql.org/docs/current/sql-altertable.html)
- [PostgreSQL docs: pg_class (reltuples is an estimate)](https://www.postgresql.org/docs/current/catalog-pg-class.html)
- [PostgreSQL docs: pg_constraint (convalidated)](https://www.postgresql.org/docs/current/catalog-pg-constraint.html)
- [PostgreSQL docs: SET CONSTRAINTS](https://www.postgresql.org/docs/current/sql-set-constraints.html)
- [PostgreSQL docs: CREATE INDEX (CONCURRENTLY and transaction blocks)](https://www.postgresql.org/docs/current/sql-createindex.html)
- [PostgreSQL wiki: Transactional DDL in PostgreSQL](https://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Competitive_Analysis)
- [MySQL 8.4 Reference Manual: Statements That Cause an Implicit Commit](https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html)
- [SQLite: PRAGMA statements (integrity_check, foreign_key_check)](https://www.sqlite.org/pragma.html)
- [SQLite: Foreign key support (disabled by default)](https://www.sqlite.org/foreignkeys.html)

## Related
- [How to stop an AI coding agent from deleting your production database](https://www.rightshold.com/answers/how-to-stop-an-ai-coding-agent-from-deleting-your-production-database)
- [How to restrict an MCP database tool to read-only queries](https://www.rightshold.com/answers/how-to-restrict-an-mcp-database-tool-to-read-only-queries)
- [How to require AI agent commits to go through pull requests](https://www.rightshold.com/answers/how-to-require-ai-agent-commits-to-go-through-pull-requests)
- [How to log every AI agent tool call for audit](https://www.rightshold.com/answers/how-to-log-every-ai-agent-tool-call-for-audit)

_Not legal advice. Updated 2026-10-30. JSON: https://www.rightshold.com/answers/how-to-make-an-ai-agent-check-database-invariants-before-and-after-a-migration.json_