#!/usr/bin/env python3 """Build a deliberately messy SQLite database for evaluating NL2SQL tools. Reproducible companion to https://infinisynapse.com/use-cases/nl2sql Why this exists: vendor accuracy numbers are quoted against academic benchmarks (Spider, BIRD) whose schemas are clean. Your schema is not clean. This script generates a small database that carries the specific defects that break NL2SQL in production, so you can measure a tool against your own conditions instead of trusting a leaderboard. The database is intentionally SMALL (a few thousand rows). The difficulty here is schema ambiguity, not data volume - an NL2SQL tool that gets these wrong will get them wrong at any scale. Traps planted, each mapping to a documented failure mode: 1. Ambiguous join keys - orders.customer_id, orders.user_id and orders.external_ref all plausibly link to a customer; only one is correct. 2. Duplicate-looking metrics - customers.ltv_estimate (a stale snapshot column) vs the true value derivable from order_items. 3. Soft deletes - customers.is_deleted and orders.void_flag must be excluded from every count, and nothing says so. 4. Ambiguous time semantics - orders has created_at, booked_at and closed_at; "last quarter revenue" depends on which you pick. 5. Unit mismatch - order_items.unit_price_cents is in CENTS while orders.total_amount is in DOLLARS. 6. Legacy naming - a live table named customers_v2 alongside a deprecated but non-empty customers table. 7. Status enum drift - status values 'complete', 'completed' and 'COMPLETE' all occur and all mean the same thing. 8. Grain trap - counting orders is not counting customers; the data makes the difference large enough to notice. Usage: python make_messy_schema.py # writes nl2sql-eval.db python make_messy_schema.py --seed 7 --out mydb.sqlite python make_messy_schema.py --print-schema # dump DDL to paste into a tool Standard library only - no dependencies. Released under CC BY 4.0. """ from __future__ import annotations import argparse import os import random import sqlite3 from datetime import date, timedelta DDL = """ -- Deprecated in 2024 but never dropped, and still holds rows. -- A tool that picks this table will return plausible, stale, wrong numbers. CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT, signup_date TEXT, ltv_estimate REAL ); -- The live customer table. CREATE TABLE customers_v2 ( customer_id INTEGER PRIMARY KEY, external_ref TEXT, -- id used by the billing system display_name TEXT, plan_tier TEXT, -- 'free' | 'pro' | 'enterprise' acquisition_channel TEXT, signup_date TEXT, ltv_estimate REAL, -- STALE SNAPSHOT, recomputed quarterly at best is_deleted INTEGER DEFAULT 0 ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER, -- correct FK -> customers_v2.customer_id user_id INTEGER, -- the ACTING user, not the account owner external_ref TEXT, -- billing-system ref, NOT unique per customer created_at TEXT, -- row inserted booked_at TEXT, -- revenue recognition date <- usually correct closed_at TEXT, -- fulfilment complete, often NULL status TEXT, -- enum drift: complete/completed/COMPLETE total_amount REAL, -- DOLLARS void_flag INTEGER DEFAULT 0 ); CREATE TABLE order_items ( item_id INTEGER PRIMARY KEY, order_id INTEGER, sku TEXT, qty INTEGER, unit_price_cents INTEGER -- CENTS, unlike orders.total_amount ); CREATE TABLE users ( user_id INTEGER PRIMARY KEY, customer_id INTEGER, email TEXT, role TEXT ); """ CHANNELS = ["paid_search", "organic", "partner", "outbound", "referral"] TIERS = ["free", "pro", "enterprise"] STATUS_COMPLETE = ["complete", "completed", "COMPLETE"] STATUS_OTHER = ["pending", "cancelled", "refunded"] def build(out: str, seed: int) -> None: if os.path.exists(out): os.remove(out) rng = random.Random(seed) con = sqlite3.connect(out) con.executescript(DDL) n_cust = 400 base = date(2025, 1, 1) # customers_v2: the live table for cid in range(1, n_cust + 1): con.execute( "INSERT INTO customers_v2 VALUES (?,?,?,?,?,?,?,?)", ( cid, f"BIL-{rng.randint(1000, 1400)}", # deliberately NOT unique f"Customer {cid}", rng.choice(TIERS), rng.choice(CHANNELS), (base + timedelta(days=rng.randint(0, 300))).isoformat(), round(rng.uniform(100, 9000), 2), # stale snapshot 1 if rng.random() < 0.08 else 0, # 8% soft-deleted ), ) # legacy customers table: overlapping ids, different (stale) values for cid in range(1, n_cust // 2): con.execute( "INSERT INTO customers VALUES (?,?,?,?)", (cid, f"Customer {cid}", (base - timedelta(days=rng.randint(0, 700))).isoformat(), round(rng.uniform(50, 4000), 2)), ) # users: several per customer uid = 1 for cid in range(1, n_cust + 1): for _ in range(rng.randint(1, 3)): con.execute("INSERT INTO users VALUES (?,?,?,?)", (uid, cid, f"u{uid}@example.invalid", rng.choice(["admin", "member"]))) uid += 1 max_uid = uid - 1 # Order volume and value scale with plan tier, so aggregates look like a real # business. The grain trap still bites: a few heavy accounts dominate. tier_of = dict(con.execute("SELECT customer_id, plan_tier FROM customers_v2").fetchall()) tier_orders = {"free": (0, 2), "pro": (1, 6), "enterprise": (3, 12)} tier_value = {"free": (20, 400), "pro": (200, 2500), "enterprise": (1500, 9000)} oid = 1 item_id = 1 for cid in range(1, n_cust + 1): tier = tier_of[cid] lo, hi = tier_orders[tier] n_orders = rng.randint(lo, hi) if rng.random() < 0.05: # heavy account n_orders += rng.randint(20, 40) for _ in range(n_orders): created = base + timedelta(days=rng.randint(0, 420)) booked = created + timedelta(days=rng.randint(0, 5)) closed = booked + timedelta(days=rng.randint(1, 20)) done = rng.random() < 0.75 total = round(rng.uniform(*tier_value[tier]), 2) con.execute( "INSERT INTO orders VALUES (?,?,?,?,?,?,?,?,?,?)", ( oid, cid, rng.randint(1, max_uid), # unrelated acting user f"BIL-{rng.randint(1000, 1400)}", created.isoformat(), booked.isoformat(), closed.isoformat() if rng.random() < 0.6 else None, rng.choice(STATUS_COMPLETE) if done else rng.choice(STATUS_OTHER), total, 1 if rng.random() < 0.06 else 0, # 6% voided ), ) # items priced in CENTS, roughly consistent with total_amount n_items = rng.randint(1, 4) for _ in range(n_items): con.execute( "INSERT INTO order_items VALUES (?,?,?,?,?)", (item_id, oid, f"SKU-{rng.randint(1, 60):03d}", rng.randint(1, 5), int(total * 100 / n_items / rng.randint(1, 4))), ) item_id += 1 oid += 1 con.commit() stats = {t: con.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] for t in ("customers", "customers_v2", "users", "orders", "order_items")} con.close() print(f"wrote {out} (seed={seed})") for t, n in stats.items(): print(f" {t:<14} {n:>7,} rows") print("\nEight traps are planted. See the module docstring, or run with") print("--print-schema to get DDL you can paste into a tool that needs it.") print("\nNext: ask the 10 questions in nl2sql-eval-questions.csv, save each") print("tool's SQL, then score with: python score_nl2sql.py --db", out) def main() -> None: ap = argparse.ArgumentParser( description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter) ap.add_argument("--out", default="nl2sql-eval.db", help="output path (default: %(default)s)") ap.add_argument("--seed", type=int, default=2026, help="RNG seed (default: %(default)s)") ap.add_argument("--print-schema", action="store_true", help="print DDL and exit") args = ap.parse_args() if args.print_schema: print(DDL.strip()) return build(args.out, args.seed) if __name__ == "__main__": main()