How I Manage 39 Database Migrations With an AI Coding Agent
Database schema changes are the one area where people get genuinely nervous about AI-generated code. A bad migration can corrupt data, break foreign keys, or take down a production database. There's no "undo" for a migration that drops a column with live user data in it.
I've had Claude Code generate 39 Alembic migrations for a production SaaS application with zero data loss incidents. Not because Claude is perfect — it's not — but because the rules system makes certain categories of mistakes structurally impossible.
The Core Rule: Alembic Only
The single most important database rule in my project is four words long: Alembic migrations only.
This means Claude is explicitly prohibited from using db.create_all(), modifying tables through the ORM directly, or running raw SQL against the schema. Every structural change goes through a migration file that gets reviewed before it runs.
In my CLAUDE.md, it looks like this:
### Database — Alembic migrations ONLY
# CORRECT
flask db migrate -m "add user preferences table"
flask db upgrade
# WRONG — never use create_all or raw DDL
db.create_all()
CREATE TABLE users (...)Without this rule, Claude will occasionally try to be "helpful" by running db.create_all() to quickly set up a new table. That's catastrophic in a production environment with existing data — it can silently fail, create duplicate schemas, or conflict with the migration chain.
Naming Conventions That Prevent Conflicts
Database naming is one of those things that seems trivial until it causes a production bug at 2am. I have a dedicated rules file (DATABASE_RULES.md) that Claude is directed to read before touching any model or migration. It covers three things that Claude commonly gets wrong.
Table names: Always plural, always snake_case. Claude sometimes generates singular table names (user instead of users) or camelCase names, depending on whatever pattern it's seen most recently in its training data. A simple rule eliminates the drift.
Column names: All snake_case, all TIMESTAMPTZ for dates (never naive datetimes), all foreign keys following the parent_table_id pattern. The timestamp rule alone has prevented at least a dozen timezone bugs.
Index names: Follow the pattern ix_tablename_columnname. This sounds pedantic, but when you're debugging a failed migration on a live server and need to find a specific index, predictable naming is invaluable.
The JSONB Trap
If you're using PostgreSQL with JSONB columns (and you probably should be for flexible metadata), there's a specific gotcha that trips up Claude repeatedly: the flag_modified() requirement.
When you update a key inside a JSONB column, SQLAlchemy doesn't detect the change automatically. You have to explicitly call flag_modified(instance, 'column_name') or the change silently disappears on commit. Claude will happily write code that modifies JSONB data without this call, and everything looks fine until you check the database and find your changes didn't persist.
My database rules file includes this as a bolded, highlighted rule with a code example. Since adding it, Claude has never missed a flag_modified() call.
The Migration Review Workflow
Even with all these rules, I never let Claude run a migration without review. The workflow looks like this:
First, Claude generates the migration using flask db migrate. Second, I review the generated migration file — checking that the upgrade and downgrade functions are correct, that it's not dropping anything unintentionally, and that the operations are in the right order. Third, I either approve it or ask Claude to modify it. Fourth, the migration runs.
This two-step process — generate then review — is the whole philosophy of working with AI on risky operations. You let the AI do the tedious work of writing the migration boilerplate, but you keep a human in the loop for the judgment call of "is this safe to run."
ADRs for Schema Decisions
Architectural Decision Records become critically important for database work. When you decide to store user preferences as a JSONB column instead of a separate table, that decision needs to be documented — not just what you decided, but why, and what alternatives you considered.
Without this, three months later Claude might suggest normalizing your JSONB preferences into a relational table because "that's better database design." And technically it might be, but you already evaluated that option and chose JSONB for specific reasons (fewer joins, flexible schema, simpler queries for your use case). The ADR prevents Claude from relitigating settled decisions.
In my project, I have ADRs for decisions like: why we use Alembic instead of Django-style auto-migrations, why certain columns use TIMESTAMPTZ instead of TIMESTAMP, why metadata is stored in JSONB rather than separate tables, and why we enforce UTF-8 encoding at the database level.
What 39 Migrations Taught Me
After 39 migrations, a few lessons stand out.
Rules eliminate entire categories of mistakes. Before the database rules file, about one in three Claude-generated migrations needed corrections. After the rules, it's closer to one in ten — and those remaining issues are edge cases rather than pattern violations.
Downgrade functions are worth the effort. Claude will skip the downgrade function if you don't tell it not to. I added a rule requiring every migration to have a working downgrade. This has saved me twice when a migration needed to be rolled back in production.
Review before run, always. The rules catch most problems, but "most" isn't good enough for database operations. The combination of rules (to catch the predictable mistakes) plus human review (to catch the unpredictable ones) is what gets you to zero data loss.
Document the "why" behind schema choices. Claude can see your schema. It can't see the reasoning behind it. ADRs close that gap, and the investment of writing a 5-line decision record pays for itself every time Claude works on a related feature.
The Agent Playbook Pro guide includes the complete database rules template, ADR format, and migration workflow — plus the full case study of building a production SaaS with 39 migrations and zero data loss. Get the guide.