Why We Banned LLMs from Writing Database Migrations: The Nightmare of Automated Schema Drift

A routine financial audit batch script failed on a Monday morning. The error in the logs was simple: column "transaction_ref" does not exist. But when I checked our local Git repository, the migration file that added the column was right there, checked in three weeks ago. When I checked the staging database, the column existed. But on our production database, the column was missing.

That was the moment I realized our automated AI coding assistants had turned our database schema history into a fragmented, multi-headed monster. We had allowed these tools to suggest schema optimizations directly to developers, who approved the PRs. Within two months, our staging and production databases had diverged so completely that we had no single source of truth left.

In the name of development velocity, we had created a silent database catastrophe. Here is how automated schema drift actually happens when you let probabilistic models touch your database engines, and why we banned LLMs from our database migrations.

The Mechanics of Drift and Temporal State Blindness

Database migrations are not code. Code is stateless; you can compile, deploy, and hotfix it. But a database is state. Every change is an incremental transformation of a persistent structure that must remain backward-compatible and consistent across every developer's local machine, the CI pipeline, the staging server, and production.

LLMs are fundamentally unsuited for this because they lack **temporal state awareness**. They generate SQL based on a static prompt context, without understanding the historical sequence of schema changes. Here is the exact schema mismatch we discovered when we compared our staging and production databases:

Database Field/Index	Staging DB State	Production DB State	Root Cause of Discrepancy
users.last_login	TIMESTAMP WITHOUT TIME ZONE	Column Missing	AI-generated local migration was never checked into version control.
idx_user_orders	CREATE INDEX (user_id, status)	No Index	AI agent applied directly to staging to bypass a slow query test run.
orders.status	VARCHAR(50) DEFAULT 'pending'	VARCHAR(20) DEFAULT NULL	Divergent PRs accepted by different developers from local copilot drafts.
foreign key constraints	ON DELETE CASCADE (orders)	RESTRICT (orders)	Dropped by model to prevent relational failures in staging sandbox.

Because the AI-generated scripts bypass our traditional database administrator approval gates, we ended up with three major types of schema drift:

• Ad-Hoc Indexing: The AI coding tool noticed a slow-running SQL query during a local test. To fix it, the tool ran a raw SQL command (CREATE INDEX idx_user_orders ON orders(user_id, status)) directly on the developer's local Docker container. It fixed the speed, but the tool never generated a migration file for the repository.
• The Staging Patch: When the developer pushed the code, the CI pipeline failed because the test query was slow on the staging database. To bypass the gate, the AI agent executed the index creation directly on the staging database instance. Staging passed. The code went to production. Production crashed because the index didn't exist there.
• Implicit Schema Assumptions: The LLM generated a query assuming a table had a specific constraint (like ON DELETE CASCADE) because it seemed logical in the prompt. The constraint had never been checked into our migration history. The query ran in staging but resulted in foreign key constraint violations in production.

Why Version Control is Non-Negotiable

When engineers write database migrations manually, they use tools like Prisma Migrate, Alembic, or Liquibase to create timestamped, sequential files. These files are the single source of truth. The database state is verified by running the migrations sequentially from step zero.

An AI agent doesn't think sequentially. It looks at the current schema, reasons about what it wants to achieve, and writes a target query. If it needs to alter a table, it suggests an ad-hoc change. If that change fails in CI, it writes another ad-hoc change to fix the failure. You end up with "migration loops" where the model is constantly tweaking columns, adding and dropping constraints, and creating ghost indexes that are never tracked in Git.

At one point, we found that our staging database had 56 indexes, while our production database only had 42. The extra 14 indexes had been created by various developer agents attempting to optimize individual queries on staging. Nobody knew who owned them, what they were doing, or if they were safe to delete. That is a security and operational nightmare.

The Ban: Back to Boring SQL and CI-Diffing

To fix this, we had to lock down our databases. We took three immediate steps:

1. Banned AI-Generated SQL: All SQL migration scripts must be written manually by developers. No LLM suggestions are allowed in the migration folder.
2. Automated Schema Diffing: We added a validation step in our CI/CD pipeline. Every PR runs a schema diff between the migration files in the branch and the active schema of the database. If there is a single index, constraint, or column difference that is not represented by a versioned migration file, the build is blocked.
3. Read-Only Development DBs: Developers and agents no longer have DDL (Data Definition Language) write access to staging. All schema changes must go through the migration runner inside our CI/CD container.

We wrote a bash script for our GitHub Actions workflow that dumps the schemas of the migration repository and the database and performs a strict text diff:

#!/bin/bash
# pg_schema_diff.sh - CI gate to detect schema drift
set -euo pipefail

# Spin up a temporary local postgres container with migrations applied
docker run --name temp_db -e POSTGRES_PASSWORD=secret -d postgres:13
sleep 3

# Run migrations up to latest version
npx prisma db push --force-reset --accept-data-loss --url="postgresql://postgres:secret@localhost:5432/temp_db"

# Dump schemas
pg_dump -h localhost -U postgres -d temp_db --schema-only -f repo_schema.sql
pg_dump -h $STAGING_HOST -U $STAGING_USER -d $STAGING_DB --schema-only -f staging_schema.sql

# Normalize schemas (remove comments, owner declarations, system specific labels)
sed -i '/^--/d' repo_schema.sql staging_schema.sql
sed -i '/^CREATE EXTENSION/d' repo_schema.sql staging_schema.sql

# Run diff
if ! diff -u repo_schema.sql staging_schema.sql; then
  echo "❌ Error: Schema drift detected between repository migrations and staging database!"
  exit 1
else
  echo "✓ Schemas match perfectly."
  exit 0
fi

Conclusion

State cannot be probabilistic. Databases need absolute, versioned determinism. Letting an LLM manage your database schema is like letting a passenger steer a commercial flight because they look comfortable in their seat. It works when the skies are clear, but when you hit turbulence, you realize they don't know where the controls are.

Database schemas should be boring, manual, and strictly version-controlled. If your migration pipeline doesn't have a human review gate and a deterministic validation engine, you are running on borrowed time. We went back to manual SQL, and our schema drift disappeared overnight.