AI-Generated Migrations: The Data Loss Pattern

Database migrations are where AI-generated code fails quietly and expensively. An AI assistant will produce a migration file that passes linting and applies cleanly on an empty development database. Then it ships to production — where it either fails at apply time or silently truncates values that don’t fit the new column type. The linter saw nothing wrong. Neither did the code review. The problem was structural, not syntactic.

Why AI Assistants Generate Plausible but Wrong Migrations

AI coding assistants generate migrations by pattern-matching against training data — they produce syntactically valid Alembic or Django migration files, but they don’t inspect the live database schema, existing data distribution, or production engine differences that make a migration safe. BrassCoders scans migration files for security patterns (hardcoded connection strings, raw SQL with format strings) but does not evaluate whether the schema change itself is structurally safe.

The gap is inherent to how the tools work. An AI assistant sees the model definition you’ve described in this conversation, plus some ORM boilerplate from your imports. It doesn’t know what data already sits in the users table, and it has no view into the production PostgreSQL version or the downstream foreign key constraints on the column you’re changing. Alembic’s autogenerate mode closes part of this gap by inspecting the live database to generate diff-based migrations, but AI assistants frequently bypass autogenerate entirely and write op.add_column() calls from scratch. That’s where the structural errors enter.

The Three Migration Patterns That Corrupt Data

BrassCoders flags the Python code that produces migrations, but the data-loss risk lives in three structural patterns: adding a NOT NULL column to a populated table without a server_default, changing a column type that truncates existing values, and generating a migration that runs correctly on SQLite test data but fails on PostgreSQL production schema.

The NOT NULL pattern is the most common. An AI assistant sees a model field added with nullable=False and generates op.add_column(table, Column('status', String, nullable=False)). On an empty table, this applies fine. On a table with 50,000 rows, PostgreSQL rejects it at apply time: existing rows have no value for the new column, and the constraint can’t be satisfied. The correct migration takes two steps: add the column as nullable first, then run a separate data migration to backfill existing rows before converting it to NOT NULL. AI assistants generate the column definition and miss the backfill.

Column type changes are subtler. Narrowing a String(500) to String(100) in SQLAlchemy generates an ALTER TABLE ... ALTER COLUMN under PostgreSQL, which silently truncates values over 100 characters. Some databases throw an error; PostgreSQL, depending on version and configuration, may just truncate. The AI assistant generated code that is syntactically correct and semantically destructive.

The SQLite-to-PostgreSQL Gap AI Misses

SQLite accepts almost any column type and coerces data silently; PostgreSQL enforces types strictly and rejects operations that would lose data — an AI assistant running tests against SQLite sees a passing migration that will corrupt or reject data when deployed against a PostgreSQL production database. BrassCoders’s security scanner flags hardcoded SQLite paths in production configuration, but the type-coercion gap is a runtime behavior difference, not a source pattern.

A concrete example: SQLite has no native boolean type. It stores True as 1 and False as 0 in an integer column. If an AI assistant writes a migration that changes a boolean column to a string, SQLite coerces the integer to a string silently. PostgreSQL’s type system is stricter. The same migration may error or produce unexpected casts. The test suite passes on SQLite; production fails on PostgreSQL. This isn’t a static-analysis problem. Static analysis can flag the column type change, but whether it’s safe depends on what data is already in the column. Only a live database can answer that.

The pattern repeats with JSON columns, ARRAY types, and timestamp precision. SQLite’s loose typing absorbs mismatches that PostgreSQL surfaces as errors or data changes.

Adding a Migration Review Gate to Your CI Pipeline

BrassCoders scans the Python migration files for security patterns (hardcoded connection strings, raw SQL with string formatting), but the structural safety check belongs in a separate step: run migrations against a production-schema clone in CI before they touch production.

The concrete setup: spin up a PostgreSQL instance in CI (most CI providers offer native PostgreSQL services) and seed it from a production dump with production-representative row counts but anonymized PII. Then run your migration suite against that database on every pull request. For Django, this is python manage.py migrate --database=ci. For Alembic, alembic upgrade head against a connection string pointing to your CI database. If the migration fails or produces unexpected results on realistic data, you find out before the deploy.

Django’s migrations documentation covers the test database connection patterns for CI, and Alembic’s documentation on autogenerate covers how the tool inspects live schemas to generate diffs. Both approaches catch the structural errors that AI assistants miss — but they require a real database with real data.

Run BrassCoders on the migration files in the same CI step. It catches the security issues a structural test won’t: op.execute(f"UPDATE users SET role = '{role}'") in a data migration is a SQL injection vector even when the schema change is structurally sound. Both checks together cover what neither handles alone.

Install the OSS core with pip install brasscoders and run brasscoders scan . — it picks up migration files alongside the rest of your Python codebase and flags the security patterns without sending any code offsite. The structural database review requires a live CI database; the security review runs locally, zero outbound network calls.