AI-Generated DDL: A Structured Debate on Autonomy, Locks, and a Reproducible Tripwire

A few weeks ago I wrote about the night an AI-generated query locked a production table, and the fix was a rollback plus a stricter review rule. The same class of failure reappears one level deeper when the AI produces a migration instead of a query. A CREATE INDEX on a busy table sounds harmless until every insert behind it waits on an exclusive lock. The useful question is not whether generated DDL is correct, but whether your pipeline can fail before the database does.

Why DDL Is a Different Risk Class

Writing queries with an LLM is a cheap mistake because a wrong result is visible in the data. Writing schema changes with an LLM is a different risk class because the mistake changes the contract that every future query depends on. The asymmetry is even sharper on engines like MySQL, where most DDL causes an implicit commit and cannot be rolled back. Even on Postgres, where transactional DDL is possible, an ALTER TABLE still needs an exclusive lock on the table, and lock acquisition ignores how elegant the migration text looks.

There is also a context problem. An LLM trusts whatever schema summary you place in its context window, so a stale catalog produces a confident migration that preserves the staleness. The migration is correct with respect to the prompt and wrong with respect to the database; that gap is where production incidents live. That mechanism is the same one behind the current conversation about models trusting everything they remember.

The Debate: Who Owns the Schema Boundary?

The community is split on what to do about this gap, and both positions have credible evidence behind them. I summarize the two views, then give a decision rule that does not depend on which side you find more convincing.

Position A: The schema boundary is the wrong place for AI autonomy

Proponents of this position argue that a migration is the worst possible artifact to generate unsupervised. The payoff is a draft saved in a few minutes, while the failure mode is a table locked for the duration of a deploy window. Even with human review, a plausible-looking migration is hard to reject because the reviewer reads intent, not lock behavior. Production incidents routinely show that destructive clauses enter through the path of least resistance: a generated draft, a tired reviewer, and a schedule that demands speed. Under this view, the AI should explain a migration that a human already wrote, never draft a new one.

Position B: Constrain the draft, then let a tripwire decide

The pragmatic counter-argument is that short, additive migrations are the safest class of DDL and the easiest to verify. If you enforce a contract before generation, such as no DROP, no RENAME, and no column type changes, the remaining drafts are small enough to review in seconds. On Postgres you can run the entire migration inside a transaction with a short lock_timeout, so a statement that would block simply fails and rolls back. The trick is that the AI only writes the draft; an automated tripwire decides whether the draft is allowed to touch the real schema.

The Artifact: A Guard That Proves the Migration Can Finish

The middle ground is a small script that takes a generated migration and answers one question before anything is applied: can this run without blocking? The first layer is a static deny-list that rejects destructive patterns regardless of how the model phrased them. The second layer replays the migration inside a Postgres transaction with lock_timeout set, so a statement that would wait on a competing lock fails fast and the whole run rolls back. The script never commits, because the goal is proof, not application.

# ddl_guard.py — a dry-run tripwire for AI-generated Postgres migrations
import os
import re
import sys

import psycopg2

DENY = [
    re.compile(r"\bdrop\s+(table|column|index|schema|view)\b", re.I),
    re.compile(r"\btruncate\b", re.I),
    re.compile(r"\brename\b", re.I),
    re.compile(r"\balter\s+column\s+.+\btype\b", re.I),
]


def static_check(path: str) -> list:
    sql = open(path).read()
    return [pattern.pattern for pattern in DENY if pattern.search(sql)]


def dry_run(path: str, dsn: str, timeout_ms: int = 2000) -> dict:
    conn = psycopg2.connect(dsn)
    conn.autocommit = False
    results = []
    with conn.cursor() as cur:
        cur.execute(f"SET lock_timeout = {timeout_ms}")
        for statement in open(path).read().split(";"):
            if not statement.strip():
                continue
            try:
                cur.execute(statement)
                results.append({"sql": statement.strip(), "ok": True})
            except Exception as exc:
                results.append({"sql": statement.strip(), "ok": False, "error": str(exc)})
                break
    blocked = [result for result in results if not result["ok"]]
    conn.rollback()
    conn.close()
    return {"ok": not blocked, "statements": results}


if __name__ == "__main__":
    migration = sys.argv[1]
    policy = static_check(migration)
    if policy:
        print("rejected by policy:", policy)
        sys.exit(2)
    report = dry_run(migration, os.environ["DATABASE_URL"])
    print(report)
    sys.exit(0 if report["ok"] else 1)

A cron entry on the smallest server turns this into a nightly check that runs before anyone is awake:

0 2 * * * cd /srv/migrate && DATABASE_URL=postgresql://app:***@127.0.0.1/app python ddl_guard.py generated_20260901.sql >> guard.log 2>&1

Because even a short dry run acquires locks, point the guard at a fresh clone when the production table cannot tolerate a two-second attempt; the cron line above assumes a maintenance window or a low-traffic replica. Two Postgres details matter here as well. CREATE INDEX CONCURRENTLY cannot run inside a transaction, so a generated index needs a manual apply step and stays outside this guard. The deny-list is deliberately naive: it matches SQL tokens rather than model intent, so a draft that says remove column and then emits DROP COLUMN is still caught at the token layer.

The Decision Rule

Where the Free Tier Fits

This loop is a good use of free model access because drafts are disposable; the value sits in the tripwire, not in the generated text. A zero-cost version can pair the draft-generation step with MonkeyCode's free model access and run this guard on its free server option; the project is open source, and the current free tier includes 10 million tokens. The guard does not care which model produced the SQL, so swapping the provider later costs nothing but a config line. Token counts and server limits change over time, so verify the current terms in the repository before you rely on them. Disclosure: This article was prepared as part of MonkeyCode's product outreach.

Limitations and Who Should Not Use This

The guard proves that a migration can finish within a lock budget on an idle connection, which is not the same as proving safety under production load. A long-running transaction that appears after the dry run can still create a queue that the timeout cannot foresee. The deny-list approach is pattern-based, so it can be bypassed by statements it does not recognize; treat it as a policy layer, not a security boundary. Teams on MySQL should not adopt this script at all, because implicit commits make the transaction layer meaningless; they need online schema change tooling instead. If your database has no point-in-time recovery, no review capacity, or no one awake when the cron fires, the tripwire becomes theater rather than protection.

Conclusion

The two positions in this debate converge on a single rule: give the model autonomy over drafting, never over applying. A generated migration is a cheap, disposable hypothesis, and the guard script is the cheapest way to test that hypothesis against the real schema. The lock queue does not care whether the SQL was written by a human or a model; it only cares who ran it without checking. Fix the checking, and the debate stops being about trust and starts being about budgets. If you want to see whether generated DDL survives contact with your own schema, the guard is a ten-minute setup, and the free tier described above is enough to find out.