Agent Harness Lab for Enterprise Text-to-SQL

Orientation doc. If you've lost the thread, read this top to bottom — it's the whole project in plain language. PROJECT-BRIEF.md is the contract (thesis, rules, day plan); notes/decisions.md is the log of why things are the way they are; this file is the map that ties them together.

Last updated: 7 August 2026 — held-out numbers are in (§4). Everything from brief §8 has landed except the demo recording.

---

1. What this project is, in one paragraph

Everyone assumes that if an AI agent fails at a task, you need a smarter model. This project tests the opposite claim: that most of the reliability comes from the scaffolding around the model, not the model itself. So we freeze the model, change only the scaffolding, and measure what happens. The task we measure on is "turn an English question into a SQL query that returns the right answer" over big, messy, real-world databases — a job where the right answer is objectively checkable and where current AI is bad enough (~15–25%) that there's room to show movement.

The one-sentence thesis: hold the model fixed, vary only the harness, measure.

2. Why anyone should care (the two faces)

• The lab is the proof. It produces a table with real numbers and real statistics: here is the harness improvement, here is the paired significance test, here is what actually broke.
• The guarded semantic-layer MCP server is the product. The tedious work — curating what each table means, and making it physically impossible for a generated query to damage the database — is exactly what a company would pay someone else to own. The benchmark is that product's marketing.

3. The benchmark, in plain terms

Spider 2.0-Lite, SQLite portion — 135 questions. Runs 100% on your laptop, no cloud warehouse, no credentials, no bill.

Each of the 135 tasks is:

• a natural-language question ("what fraction of cities are capitals?"),
• sometimes a paragraph of "external knowledge" (a formula or definition),
• a SQLite database file, often with hundreds or thousands of columns,
• and a precomputed CSV of the correct answer.

We never run the gold SQL. We run our SQL and compare result sets to that CSV.

Splits. The 135 tasks are sorted then shuffled with seed 13 and cut 40/95. The 40-task dev split is where all iteration happens. The 95-task test split is untouched until final numbers. This was fixed once and never changes — that's what makes the numbers honest.

The metric: execution accuracy (EX). Did our query return the same rows as the gold CSV? Pass or fail, no partial credit.

4. Where things stand right now

The full ladder, on the split we developed against. The held-out numbers are the subsection below it — that is the table to read if you only read one.

Rung	What it adds	EX (dev, n=40)	95% CI	median tokens/task
1 — naive	Whole schema dumped in the prompt, one call	42.5%	[28.5, 57.8]	3.1k
2 — +MCP tools	Agent discovers schema via tools instead	25.0%	[14.2, 40.2]	20.4k
3 — +semantic layer	Curated per-DB docs injected into the prompt	32.5%	[20.1, 48.0]	44.7k
4 — +verifier	Draft → run it → review → repair	62.5%	[47.0, 75.8]	~13k
5 — +model routing	Stronger model on one layer only	65.0% (verifier-on-pro) — does not survive held-out, see below	[49.5, 77.9]	11.8k
diagnostic (off-ladder)	Semantic docs, one call, no tools	45–55%	—	5.0k

results/SCOREBOARD.md is the authoritative version of this table and names the exact canonical run file per rung.

Provenance note. Every number above was produced between 27 and 31 July at max_tokens = 6144. The committed configs now say 16384 and therefore no longer reproduce these runs — a deliberate trade made on 7 August, when a provider-side change in DeepSeek's reasoning length turned that cap into a silent bottleneck (§10). Runs now record model and max_tokens in their result summary so this is never ambiguous again.

Read that table carefully — it does not say what you'd expect. Adding tools hurt. Adding a semantic layer on top of tools only partly recovered. The thing that actually worked was making the agent check its own work.

The held-out result (this is the one that counts)

Everything above is the dev split — the 40 tasks we iterated on, so it is the split we could have overfitted. On 7 August the ladder was run on the 95 tasks never touched during development, alongside a same-day control on dev so that split and calendar date wouldn't be confounded:

Rung	dev control (n=40)	held-out (n=95)	vs previous rung, held-out
1 — naive	37.5%	43.2%	—
4 — +verifier	57.5%	56.8%	+20/−7, p=0.0192 ✓
5b — +routing	—	53.7%	+3/−6, p=0.508 — no

Two things fall out, one good and one that costs us the headline:

• The verifier generalizes. 57.5% on dev vs 56.8% on 95 unseen tasks is the same number, and on the larger split the gain over naive is significant on its own — which n=40 never had the power to show for that pair. The zero-damage property held too: the control repaired 8 tasks and broke 0.
• The routing rung doesn't. Rung 5b was the best cell on dev (65.0%). On held-out data it lands below rung 4, and it's the one ladder step whose gain over naive fails significance. The dev-split recommendation did not survive contact with unseen data. It's reported rather than dropped, because a ladder where every rung wins is a ladder someone tuned until it did.

Two caveats, both in results/SCOREBOARD.md in full: residual truncation still costs 15–32 of 95 tasks per run even at the raised cap (§10), so these are a floor; and one task in the 5b row sits on the 30-second execution boundary, which puts that row at 53.7% or 52.6% depending on machine load. Neither moves a conclusion.

And the result we didn't go looking for. 31 of the 95 held-out tasks run against databases that appear in no dev task — genuinely unfamiliar schemas, profiled by the same blind offline pass and never inspected. Splitting held-out accuracy by whether we had ever seen the database:

Rung	DB seen during dev (n=64)	DB never seen (n=31)	penalty
1 — naive, no semantic layer	46.9%	35.5%	−11.4 pts
4 — +semantic +verifier	57.8%	54.8%	−3.0 pts

The naive baseline loses ~11 points on an unfamiliar database; the full harness loses ~3. That is the §2 commercial claim — the curated semantic layer is the labour worth paying for — with a number behind it for the first time, and it isolates portability rather than raw accuracy. Directional only at n=31, but it's the measurement that would decide whether this is a product.

And rung 5 is a null result, reported as one. All four routing cells ran; no pair differs significantly (McNemar p ≥ 0.375). Putting the expensive model everywhere was the worst configuration:

draft \ verify	flash (cheap)	pro (strong)
flash (cheap)	62.5% — rung 4	65.0% — best on dev
pro (strong)	62.5%	55.0% — worst

And the held-out split retired that "best". The 65.0% cell scored 53.7% on the 95 unseen tasks, below rung 4's 56.8%. A cell that was never significant on dev turned out not to be real. This is what the significance testing was for — and it is why the recommended configuration is now rung 4, not rung 5b.

Two things fall out of that grid, and neither is visible in an EX column:

1. The verifier catches more when it is a different model from the writer. Same model in both roles: recall 0.50 and 0.47. Different models: 0.56 and 0.63. A reviewer that shares the writer's blind spots is the obvious explanation. Suggestive, not proven — n=40 can't confirm it.
2. Identical scores can hide total churn. Pro-on-writer scored exactly the same as rung 4 while flipping 14 of 40 tasks. It solved 4 of the 11 failures that had resisted every previous rung — then broke 7 that the cheap model had right. A stronger model is not a superset of a weaker one.

5. The five rungs, explained simply

The "ablation ladder" is the experiment: five versions of the harness, each adding one thing to the last, same model throughout (rungs 1–4).

Rung 1 — Naive. Paste the entire database schema into the prompt, ask for a query, take whatever comes back. Deliberately dumb. It's the floor everything else has to beat. (Code: harness/naive.py)

Rung 2 — Add a tool interface. Instead of dumping the schema, give the agent six tools (list_tables, describe_table, search_schema, sample_values, explain_query, run_sql) and let it explore, up to 8 turns. This is what "agentic" normally means. It scored worse than the dumb baseline — 33 of 40 tasks burned all 8 turns poking around and never submitted an answer. Knowledge sitting behind a tool costs turns to retrieve. (Code: harness/mcp_agent.py, mcp_server/server.py)

Rung 3 — Add a semantic layer. An offline pass over each database produces a curated document: what each table means, its grain, join hints, gotchas, and the actual values that appear in each column. Generated blind to the benchmark questions, so it isn't cheating. That document is injected straight into the prompt rather than hidden behind a tool. It helped — but only from 25% to 32.5%, still below the naive baseline, because the agent loop was still there dragging it down. (Code: semantic_layer/profile.py, harness/semantic_agent.py)

The diagnostic that unlocked everything. Rung 3 changed two things at once (better context and an agent loop), so its number was uninterpretable. We ran the missing cell — good context, single call, no tools — and it scored 45–55%. That gave a clean 2×2:

	raw schema dump	curated docs
single call	42.5%	45–55%
agent loop	25.0%	32.5%

Read down the columns: the loop costs 12.5–17.5 points at both context levels. Read across the rows: curation buys 2.5–7.5 points at both loop levels. The agent loop was the problem, not the context. So rung 4 dropped free exploration and kept the good context. (Code: harness/semantic_oneshot.py)

Rung 4 — Add a verifier. Draft one query from the curated docs → actually run it read-only → show a second LLM call the question, the query, and the result → it replies ACCEPT or REVISE + a specific repair instruction → up to 2 repair rounds → submit whatever's last. 62.5%. Best rung by a distance, and 3.5× cheaper than rung 3. The verifier was measured, not assumed: precision 1.00 (it never rejected a correct query) and recall 0.50 (it caught half the wrong ones). (Code: harness/verifier_agent.py)

Rung 5 — Per-layer model routing. Rungs 1–4 use deepseek-v4-flash everywhere. Rung 5 swaps the stronger deepseek-v4-pro into one layer at a time to find where the money is worth spending: on the writer (5a), on the verifier (5b), or everywhere (5c, the ceiling). This is a config change only — a [model.roles] table in the TOML. No new solver code, which is the cleanest proof that "configs are the experiment" actually holds.

The answer: nowhere, significantly. No cell beat any other at n=40, and paying for the strong model in both roles was the worst of the four. The useful signal was in the verifier's recall, which rose whenever the reviewer was a different model from the writer — see §4. (Code: harness/llm.py's model_for, configs/rung5_route_*.toml)

6. What we learned when we read the failing traces

Day 4 stopped and hand-labelled all 15 rung-4 failures, verifying every root cause with a probe query against the real database instead of guessing from the SQL. Full writeup: notes/failure-taxonomy.md. The headline:

The biggest failure category (5 of 15) is the model inventing values the database doesn't use. Not reasoning failures — grounding failures:

• filtered capital = 'primary' when the column is an integer 0/1 flag
• filtered gender IN ('Male','Female') when the values are 'M' and 'F'
• LIKE '%helmet%' also matched the string 'motorcycle helmet not used'
• stripped $ and , from salaries but not the ₹ actually present, so CAST silently returned 0
• parsed coordinates assuming a space after the comma; there wasn't one, so every latitude quietly lost its first digit

Every one of these is fixable by a single SELECT DISTINCT probe.

Two more things worth knowing:

1. 11 of the 15 failures fail in every single rung. There's a hard core no harness change has moved, and its labels are grounding, aggregation grain, and output shape — not the things the harness did fix (schema-linking misses went 2→0, timeouts went 2→0).
2. Roughly 2–4 of the 15 "failures" are the metric being wrong, not us. One returned identical values with different capitalisation; one picked a legitimate member of a 3-way tie. True capability is probably ~67–72% against a measured 62.5%. This is the honest caveat on EX and it goes in the final report.

The prediction we wrote down before running rung 5: a stronger model cannot know a database's value encodings, so routing should improve reasoning-type failures and leave the grounding category flat — meaning the rung-5 gain should land below the noise floor. Recorded in advance so the result is a test, not a story told afterwards.

How it scored: right on the headline, wrong on the mechanism. Every routing cell landed inside the noise floor, so the prediction held. The reasoning behind it did not, in two specific ways worth stating plainly:

• "A stronger model can't fix grounding errors." It fixed one. The pro verifier spotted the truncated-latitude bug from the string arithmetic alone — no data access needed — and its repair changed the offset from +2 to +1, the same fix we found by probing. Grounding failures that are arithmetically visible in the query are not model-proof; the ones that require knowing a column's actual values (gender, capital, ₹) still are.
• "Routing will move the reasoning categories." It did, and it cost exactly as much elsewhere — 4 hard-core tasks solved, 7 previously-passing tasks broken, net zero.

Getting a prediction half-wrong in public is the point of writing it down first.

Two hypotheses we tested and killed. Both looked plausible; both were wrong, and finding that out cost one run each.

"The token cap is holding the models back." max_tokens covers hidden reasoning plus output, and the expensive model reasons more — so the cap tuned for the cheap model looked like it might be strangling the expensive one. It had the most truncated calls of any cell. We re-ran that cell at 16384: truncation went to zero, and EX went down 55.0% → 52.5%. All three previously truncated failing tasks still failed. Truncation was a symptom of a model that had already lost the plot, not the reason it lost it. A bigger budget just buys a longer wrong query.

"The failures are formatting, not correctness." The benchmark ships no output spec, so this was a fair worry. Re-labelled, rung 1's 23 failures are 12 wrong values, 5 wrong row counts, 2 schema-link, 2 timeouts, 1 dialect, 1 empty — and zero label-only or missing-column failures. Presentation does dominate in rung 2 (8 missing-column + 15 row-count of 30 failures), where the tool-using agent returns exploratory result sets instead of answers. But the naive baseline fails by being wrong.

7. How a run actually works, step by step

configs/rung4_verifier.toml
        │  (the config IS the experiment — swapping rungs never edits code)
        ▼
eval/runner.py                    reads the TOML, picks the solver by name
        │
        ├── eval/tasks.py         loads 135 tasks, applies the seed-13 40/95 split
        │
        ├── harness/<solver>.py   ← the only thing that varies between rungs
        │        │
        │        └── harness/llm.py   EVERY model call goes through here:
        │                             token cap · timeout · retry · cache · trace log
        │                             (calls out to api.deepseek.com)
        │
        ├── eval/score.py         runs the predicted SQL, compares to the gold CSV
        │        └── mcp_server/guards.py   read-only + timeout + row cap
        │
        └── writes  results/<name>-<runid>.json   ← the number
                    traces/<name>-<runid>.jsonl   ← one line per LLM call

Then eval/compare.py takes two result files and does the statistics.

8. What's in each directory

Directory	What it holds
harness/	The solvers — one file per rung — plus llm.py, the single door every model call goes through
mcp_server/	The tools the agent can call, and guards.py, the safety layer. This is "the product"
semantic_layer/	profile.py generates the per-database docs; artifacts/ holds the committed output
eval/	Task loading, the runner, the scorer, statistics, comparison tools
traces/	store.py plus one JSONL file per run (every LLM call) and .cache/
configs/	One TOML per rung. Nothing else varies between rungs
results/	Committed result JSON per run + SCOREBOARD.md, the canonical number per rung
notes/	decisions.md (why), failure-taxonomy.md (what broke), plan-rung4.md
data/	Gitignored — the Spider 2.0 tasks and the SQLite databases

9. The load-bearing design decisions

Four things in this repo carry more weight than their line count suggests.

harness/llm.py — the choke point. Every single model call in the project goes through one function. It caps max_tokens, enforces a timeout, retries on timeout or a no-tool-call reply, caches responses on the full request body, and writes a trace line whether the call was cached or not. Nothing calls the API around it. That's what makes "log every call" enforceable rather than aspirational.

mcp_server/guards.py — safety that doesn't depend on the model behaving. Read-only is enforced at the connection: mode=ro in the URI, plus PRAGMA query_only, plus a timer that calls conn.interrupt(), plus a row cap. A DROP TABLE cannot execute even if the model emits one and the prompt says nothing about it. mcp_server/test_guards.py proves it by running destructive SQL and hashing the database file before and after. The scorer uses the same guard as the agent's tools — one gate, two consumers.

The statistics, built on day 1 rather than day 7. At n=40 the confidence interval is roughly ±15 points, so a 5-point "improvement" means nothing. We measured the actual noise floor by re-running the identical config: 42.5% vs 52.5%. Any single-run delta under ~10 points is noise. So every rung-to-rung claim uses McNemar's paired test on per-task outcomes, which compares the same 40 tasks and has far more power than comparing two percentages. Every absolute rate gets a Wilson interval. Nothing goes in the report without one.

Configs are the experiment. If switching rungs required editing code, the comparison would be meaningless — you could never be sure what else changed. Rung 5 is the proof this holds: an entire new rung with zero new solver code, just a [model.roles] table.

10. Gotchas that have already bitten us

• A hosted model's token appetite is not a constant, and a stale cap fails silently. deepseek-v4-flash is a thinking model — it burns hidden reasoning tokens before answering, so max_tokens must cover reasoning plus output. At 1k it capped mid-thought and returned empty on 34 of 40 tasks. We set 6144, tested it, and wrote "settled" next to it. Then between 27 July and 7 August DeepSeek's reasoning length grew 5–7× — the same dev tasks that emitted a median 1,981 completion tokens with zero truncations started wanting 10–16k. The first held-out run scored 27.4% / 38.9% / 38.9% and looked like a generalization failure. It wasn't: every empty prediction was a truncated call (53/53, 38/38, 42/42), and the harness's own guard was the bottleneck. Now 16384. Check the finish_reason distribution in the traces after any provider or backbone change — a result file alone cannot show you this, which is why runs now record model and max_tokens in their summary.
• The response cache keys on the exact request body. Any prompt change is a full-price re-run; re-running an unchanged config is free. Delete traces/.cache/ to force fresh calls. This is also used deliberately: rung 4's draft prompt is byte-identical to the one-shot diagnostic's, so drafts replay from cache and the two runs are exactly paired.
• DeepSeek doesn't enforce the tools array server-side. The model will hallucinate calls to tools you didn't offer it. The harness tolerates this rather than crashing.
• Temperature 0 is not reproducible. EX survives this (result-set matching ignores phrasing); individual traces don't.
• Count total_tokens from the API, not prompt + completion — they differ, because reasoning tokens are hidden. And report the median, since one runaway turn (~19k tokens) wrecks a mean.
• A read timeout doesn't always raise Timeout. If the connection stalls while the response body is streaming, requests raises ConnectionError instead. Our guard caught only Timeout, so the exception escaped and killed a 40-task sweep at task 31. It now catches the whole RequestException family. Four rungs never exposed this — only a slower model did. A guard is only proven by the failure it was written for.
• requests' timeout is per-read, not a wall-clock budget. Calls legitimately ran 121 seconds under timeout=60, because it caps the gap between chunks, not the total. Budget accordingly.
• The runner's total_tokens is the last call only. For a draft→verify→repair task that's the tail, not the total — real tokens/task has to be summed from the trace JSONL. That's what eval/rung5_report.py is for.
• Judge a run's verifier against its own drafts. Our first rung-5 report scored pro's drafts against flash's draft labels and reported P=0.69 where the truth was 0.92. It now rescores each run's round-0 SQL directly.
• The 30-second execution guard makes a few tasks genuinely nondeterministic, and eval.rescore will flag them as disagreements. A query that finishes in 29s during a run and 31s during a rescore flips from match to exec error with nothing about the scorer having changed — local100 on the held-out rung 5b run does exactly this, moving it 53.7% → 52.6%. So rescore's exit-1 gate has a known false-positive mode: read the disagreement before believing it, and check whether the re-execution hit the timeout at exactly the guard's limit. A real scorer regression changes a result, not an execution outcome.

11. Commands

Setup

data/ is gitignored (the SQLite databases are large and are not ours to redistribute), so a fresh clone needs it built once:

pip install -r requirements.txt

# 1. The benchmark: tasks, gold CSVs, and the evaluation suite.
#    Upstream is https://github.com/xlang-ai/Spider2 — only the spider2-lite
#    subtree is used, so a sparse or partial clone into data/Spider2 is fine.

# 2. The SQLite databases, which upstream distributes separately from the repo
#    (they are large binaries). Follow the spider2-lite local-database download
#    that Spider2's own instructions point to, and unpack it so each database is
#    a flat file: data/spider2-localdb/<db>.sqlite

We deliberately do not script step 2: the hosting location has moved before, and a download script that rots is worse than a pointer to the upstream instructions.

The layout the loader expects (eval/tasks.py is the authority):

data/Spider2/spider2-lite/spider2-lite.jsonl                              tasks
data/Spider2/spider2-lite/evaluation_suite/gold/spider2lite_eval.jsonl    compare params
data/Spider2/spider2-lite/evaluation_suite/gold/exec_result/*.csv         gold results
data/spider2-localdb/<db>.sqlite                                          databases

Then put a DeepSeek key in .deepseek_key at the repo root (gitignored) or DEEPSEEK_API_KEY, and verify everything resolves:

python -m eval.tasks              # 135 tasks load, 40/95 split, all db+gold present
python -m mcp_server.test_guards  # the destructive-query proof passes

python -m eval.tasks failing is always a data-layout problem, and its message names the missing file. There is no Docker image: three pure-Python dependencies and python -m eval.runner <config> is already the one command, and a container proving that would be ceremony.

Running

python -m eval.runner configs/rung4_verifier.toml       # run a rung
python -m eval.compare results/A.json results/B.json    # paired comparison: Wilson + McNemar
python -m eval.rung5_report results/rung5_*.json        # true tokens/task by layer + verifier P/R
python -m eval.failure_log results/<run>.json          # per-run failure log + results/failures/INDEX.md
python -m eval.failure_log                             # ...every run at once
python -m eval.rescore                                 # re-judge all committed results with the current scorer (no API calls)
python -m traces.report                                # ablation table + trace drill-down -> traces/report.html

python -m eval.tasks                  # self-check: 135 tasks load, 40/95 split, files present
python -m eval.stats                  # self-check: Wilson + McNemar math
python -m harness.llm                 # self-check: role routing + cache keying
python -m mcp_server.test_guards      # PROOF: destructive SQL cannot execute

python -m semantic_layer.profile           # regenerate semantic docs (skips up-to-date)
python -m semantic_layer.profile --force   # ...regenerate everything

API key goes in a .deepseek_key file at the repo root (gitignored) or the DEEPSEEK_API_KEY env var.

12. What's left

• Three-minute demo recording — the only remaining deliverable.

Everything else from brief §8 has landed. Since the last revision: the held-out runs on the 95-task test split with a same-day dev control (§4), the static HTML report over the trace store (python -m traces.report), the OTel GenAI mapping (notes/otel-mapping.md), and setup instructions so a fresh clone can actually run (§11 — previously data/ was gitignored with no word on how to rebuild it).

Two things deliberately not done, so nobody goes looking for them: rungs 2 and 3 were never run on the test split (their negative results are already significant on dev; re-confirming them would have cost ~6h of sweep), and there is no Docker image (§11 explains why).

Slip rule: if time runs out, cut a rung — never cut the statistics or the trace store. A three-rung table with confidence intervals beats a five-rung table without them.