It was a Saturday evening, and I was confident. A new model had just dropped, and the leaderboard said it was the best thing since window functions. I pointed it at a real migration task, reviewed the SQL briefly, and scheduled it to run at 2 AM when traffic was low. At 3:14 AM, my phone lit up with a pager alert: the orders table was locked, the replica lag had spiked, and every checkout request was timing out. The AI-generated SQL was correct in the sense that it returned the right rows. It was also catastrophically wrong in the sense that it scanned the entire table, held a write lock for eleven minutes, and brought a production service to its knees.
This is not a story about a bad model. It is a story about a bad evaluation strategy, and it is the reason I now treat every AI-generated SQL query as guilty until proven innocent.
The Mistake I Made
The query looked fine in isolation. It joined two tables, filtered on a timestamp column, and returned about four thousand rows. What I did not check was the execution plan, because the test database had a fraction of the production data volume. On a 10,000-row test table, the query planner chose an index scan and the query finished in 40 milliseconds. On a 40-million-row production table, the same query triggered a full table scan, escalated to a table-level lock, and blocked every concurrent write.
The root cause was not the SQL syntax. The root cause was that my evaluation pipeline measured correctness but never measured cost. I compared result sets, confirmed they matched, and shipped it. I never asked how many rows the query touched, whether the planner chose a sensible join order, or what happened under concurrent load.
What I Changed After the Incident
My evaluation workflow now has three distinct gates, and only the first one checks correctness. The second gate checks the execution plan for red flags: sequential scans on large tables, missing index hints, cartesian products, and implicit type conversions. The third gate runs the query against a frozen fixture that mimics production data distribution, not just production schema.
# plan_gate.py — reject SQL with dangerous execution plans
import sqlite3
import sys
DANGEROUS_OPERATIONS = ["SCAN TABLE", "TEMP B-TREE", "CARTESIAN"]
def check_plan(db_path, sql):
conn = sqlite3.connect(db_path)
plan = conn.execute(f"EXPLAIN QUERY PLAN {sql}").fetchall()
for row in plan:
detail = row[3]
for marker in DANGEROUS_OPERATIONS:
if marker in detail:
print(f"REJECT: {marker} in {detail}")
return False
return True
if __name__ == "__main__":
sql = sys.argv[1]
ok = check_plan("fixture.db", sql)
sys.exit(0 if ok else 1)
The plan gate is deliberately conservative. It rejects queries that touch too much data, and it forces a human to read the SQL and decide whether the cost is justified. This single check would have caught my Saturday night disaster, because the production plan would have shown a sequential scan on a table that the test plan indexed.
Building the Defense on a Zero Budget
The full pipeline now runs on free infrastructure. The fixture database is committed to the repository. The evaluation script runs on a schedule, and every model drop triggers a fresh pass through the correctness gate and the plan gate. When both pass, the SQL is sent to a staging environment for a real execution against a data-distribution replica.
The open-source MonkeyCode project provides free model access (10 million tokens) and a free server option, which is how I run this pipeline without paying for API calls or a cloud instance. Disclosure: This article was prepared as part of MonkeyCode's product outreach. I have not benchmarked their models here, and the pipeline works with any OpenAI-compatible endpoint.
What This Approach Still Cannot Catch
Execution plans catch cost problems, but they do not catch every failure mode. The plan gate misses deadlocks under concurrency, lock contention from long transactions, and performance degradation that only appears at a specific load threshold. It also misses queries that are correct on the fixture but wrong on production data because of NULL distribution or collation differences. For those cases, I rely on a shadow replay: logging production queries and replaying them against a staging copy.
The Lesson
The leaderboard told me the model was smart. The test database told me the SQL was correct. Neither told me the query would lock a production table for eleven minutes. The only thing that would have caught it was a plan gate, a realistic fixture, and a refusal to trust correctness alone. I still use AI-generated SQL every week, but nothing ships without passing all three gates. The cost of that pipeline is zero dollars. The cost of skipping it was a Saturday night I do not want to repeat.
Top comments (0)