# 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:

```bash
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:

```bash
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

```bash
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.