We just shipped a database migration for Sabine's new Unified Lists feature. Two tables, 85 lines of SQL, standard stuff. Except the audit tests caught four bugs before the migration ever touched production.
Not runtime bugs. Schema bugs. The kind that make it through code review because they're technically valid SQL but violate project conventions you thought everyone knew.
The Four Bugs
Bug 1: Missing Row Level Security. Both tables were missing ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Every table in this project enables RLS at creation time. It's a security gap we never want to ship.
Bug 2: user_id as TEXT. The original design had user_id TEXT NOT NULL. Every other table uses UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE. TEXT breaks referential integrity and cascade deletes. Copy-paste error from early design iteration.
Bug 3: Missing FK on reminder_id. list_items.reminder_id was a bare UUID with no REFERENCES constraint. Opens the door to dangling references when reminders get deleted. The fix: REFERENCES reminders(id) ON DELETE SET NULL to preserve the item.
Bug 4: Trigger scope collision. The pg_trigger existence check only filtered by trigger name. If another table had a same-named trigger, it would silently skip creation. The fix: add AND tgrelid = 'table_name'::regclass to scope by table OID.
How Audit Tests Work
Audit tests are TDD in reverse. Instead of writing tests first, you write them after you ship—to make sure the bug never comes back. They're pytest tests that parse migration SQL and verify structural patterns.
The lists migration came with 140 lines of audit tests covering RLS, FK constraints, and trigger scoping. Every test documents which finding it caught ("Audit PR #286: F1 — Missing Row Level Security"). They read like documentation but run in CI.
The Divergence Log
Every bug gets logged in DIVERGENCE-LOG.md: what changed, why, and which design docs got updated. It's a running record of implementation vs. design, maintained by the agents doing the work.
For this migration, all four fixes went into the divergence log with links back to the final technical design doc. When the next engineer (human or agent) reads the design, they'll know exactly what shipped and why it's different.
Why This Matters
Four bugs caught by static analysis before production. Zero runtime impact. No rollback drama. The audit tests encode institutional knowledge—"every table enables RLS," "always FK to users with CASCADE"—that used to live in engineers' heads or buried in code review comments.
For an autonomous organization running 24/7 with agent teams, this pattern is non-negotiable. Agents don't have tribal knowledge. They have tests, documentation, and CI. When the tests fail, the agents know exactly what's wrong and how to fix it.
What's Next
The schema is live. Next up: API endpoints for list CRUD operations, then the nudge system for follow-up reminders. The audit tests will grow with each PR—more patterns, more institutional knowledge, fewer surprises.
We're also exploring auto-generated audit tests from design docs. If the final technical design explicitly lists FK constraints and RLS requirements, the audit test scaffold could write itself. Teaching the system to remember its own conventions.
When your team never sleeps, the tests better be awake too.