docs/BACKLOG.md

# Backlog

Single living tracker for code/eval work items. Supersedes the scattered per-audit
lists — new findings land here with a status so nothing rots undone.

Status: ✅ done · ⛔ gated (needs a human decision or an external host) · 🔎 won't-fix (verified)

## Done (2026-07-11)

Security / public surface
- ✅ AST guard: `SELECT … INTO` denied; `pg_sleep_for/until`, `set_config`, `setval` banned; adversarial tests per vector.
- ✅ Streamlit `Sentence` renderer HTML-escaped (stored-XSS class closed).
- ✅ API: rate limit unconditional (anonymous bucketed by IP); key via `Settings` (`.env` honoured); `secrets.compare_digest`; 500s redacted to a `trace_id`; `truncated` surfaced in `AskResponse`.

Metric integrity
- ✅ BIRD rescue hints gated behind `PipelineConfig.enable_bird_rescue_hints` (default **off**); product path no longer serves canned per-question answers. `eval_baseline.py --bird-rescue-hints` applies them to the single-run — measured at 62.5%, *not* the archive's 94.0% (see the audit section below).
- ✅ Reproducible single-run measured and committed (config E, codestral — `eval/baselines/reproducible_n200.json`; 57.5% as measured then, 58.0% after the comparator fix below).
- ✅ Metric split into labelled tiers in README + methodology §13, each marked by whether it runs in the product. (Corrected in the third pass below: the tiering claimed the hints flag reproduced 94.0%. It does not — it yields 62.5%.)
- ✅ Machine-readable artifacts synced: v31 report summary 185/0.925 → 188/0.94; `/eval/latest` serves the reproducible baseline (not a stale 60.5%); FastAPI description honest; regression test pins `overall == recompute(records)`.

Postgres path
- ✅ Read-only made real: `postgresql_readonly` execution option (was a no-op `SET` inside an open transaction); DSN normalised to `psycopg` v3 (path had never run under psycopg2). Loader `scripts/load_postgres.py`; registry registers PG from `NL_SQL_PG_DSN`. Live PG16 proof in CI (`tests/test_postgres_readonly.py`).

Docs / hygiene / CI
- ✅ README/DEPLOY truthful: Langfuse + vcr.py claims removed; unused langfuse compose service dropped; DEPLOY.md rewritten for the real HF Space (tracked-only + prune).
- ✅ CWD-independent paths (`nl_sql.paths`); `ProviderName` synced with the factory (+`perplexity`, +`gracekelly`).
- ✅ Nine voting scripts on the unsafe comparator frozen in `scripts/archive/` + CI gate (`check_no_raw_compare.py`).
- ✅ CI: mypy over `src app scripts`; coverage floor (`--cov-fail-under=85`, currently 91%); aligned on Python 3.13.
- ✅ `docs/SESSION_HANDOFF.md` un-tracked (kitchen); eval trace moved into methodology §13.

## Done (2026-07-11, second pass — the Postgres claim, actually run)

- ✅ **PG data load + PG-backed eval.** `codebase_community` (StackExchange-mini, 741 646 rows, 385 MB) loaded into a live Postgres 16 from BIRD's own PG dump via `scripts/extract_pg_dump_slice.py` (the dump ships all eleven databases in one `public` schema; the slice keeps one). Row counts verified against SQLite table by table. The product pipeline then ran on BIRD's **Postgres gold**: **49.0% EA (24/49)** vs 44.9% on SQLite for the same questions, 100% validity, 88% agreement between engines — the engine ports. Artefact: `eval/baselines/postgres_codebase_community_n49.json`; method: `docs/03_eval_methodology.md` §14.
- ✅ **Two product bugs that only a live Postgres could expose** (both invisible on SQLite):
  - `execute_readonly` ran statements through `exec_driver_sql`, which hands the driver an empty parameter set — psycopg (`pyformat`) then parsed `%` as a placeholder and killed every `LIKE '%x%'` and every `%` modulo, *including BIRD's gold* (qids 586/587 couldn't even be scored). Now executed on a DBAPI cursor with no parameters, so neither `%` (psycopg) nor `:__` (SQLAlchemy `text()`) is interpreted; driver exceptions are re-wrapped into `sqlalchemy.exc` so error classification is unchanged.
  - `_normalise_cell` never converted `Decimal` → `float` (its docstring claimed it did), so Postgres `numeric` results (`AVG`, `SUM`, any division) skipped the float-tolerance grid and correct answers scored misses. Cost 4 EA points on the PG run (40.8% → 49.0%).
- ✅ SQLite headline unaffected by *these two* fixes: re-measured n=200 → still 57.5% at the time. (A third comparator bug, found in the audit below, later moved it to 58.0%.)

## Done (2026-07-11, third pass — adversarial audit of the fixes themselves)

The code written during the two passes above had not itself been reviewed. Five real defects, all found by reading it back — the first one by *running* the command the docs told reviewers to run:

- ✅ **The docs claimed a number the command does not produce.** README, methodology §13 and this backlog all said the hint-assisted tier reproduces with `eval_baseline.py --config E --n 200 --bird-rescue-hints`. Run it: **62.5%** (125/200), not 94.0%. The 94.0% artefact (`eval/reports/2026-05-26/v31-v30-plus-p3f-q37-merged.json`) is a *merge of ~20 runs* — its own `sql_model` field lists codestral + Sonnet 4.6 + GPT-5.2 + Grok-4.1 + Kimi-K2 + Llama-4-Scout + Qwen3 + gpt-oss voting across Groq/OpenRouter/Perplexity bridges, plus archive-rescore, plus the hints. Presenting that as "the pipeline with hints on" both overstated what the flag does and *understated* what the number cost. Tiers are now four, each labelled reproducible-or-archive, and the two reproducible ones carry the numbers their commands actually print.
- ✅ **The rate limit could be dodged with one header.** `require_api_key` bucketed on `X-API-Key` even when *no* key was configured — which is exactly the keyless public deploy the limiter was added to protect. The header is then unauthenticated, caller-controlled input: a fresh random key per request minted a fresh bucket, so the 60/min limit never fired, and `_hits` grew without bound (an OOM vector driven by remote input). It now buckets on the header only when a key is configured, and therefore verified; otherwise it falls back to the client IP. Stale buckets are swept once per window.
- ✅ **One accented byte in `X-API-Key` returned 500 instead of 401.** `secrets.compare_digest` raises `TypeError` on a `str` holding any codepoint above 127, and Starlette decodes headers as latin-1. Keys are compared as bytes now.
- ✅ **The comparator scored correct answers as misses (int vs float).** `_hashable` quantised floats onto the tolerance grid but passed ints through untouched: gold `5` hashed to `5`, pred `5.0` to `5_000_000`, and the set comparison called it a mismatch. That is *stricter than BIRD's own scorer*, which sets raw Python tuples — where `5 == 5.0` collapses — and it contradicted the apples-to-apples claim in the docstring. The ordered path compares through a tolerance and always got these right, so one (gold, pred) pair scored two different ways depending on whether gold had `ORDER BY`. Every number — int, float, and (post-`_normalise_cell`) Decimal — now shares one grid. Re-measured: **SQLite n=200 → 58.0% (116/200), up from 57.5%**; the recovered question is a `challenging` one (41.2% → 44.1%). Postgres n=49 unchanged (49.0%), SQLite control n=49 unchanged (44.9%), Chinook still 100%. The fix is monotone — it can only merge buckets that were wrongly split — so no previously-passing question can regress.
- ✅ **Engine leak on the hot path.** `DatabaseSpec.make_engine()` called `create_engine` on *every* invocation — once per `/ask`, once per eval question — so no pool was ever reused and none disposed, while `connect()`'s docstring promised reuse. On Postgres that opens a fresh backend per question and walks straight into `max_connections` under sustained load. Engines are built once per spec and pooled now (`dispose_engines()` clears the cache). Paired fix: the SQLite statement-timeout progress handler is disarmed on the way out — pooled connections get handed to the next caller (schema introspection never arms one of its own), and a handler left behind carries an already-expired deadline that would interrupt that caller's first non-trivial query.

## In progress — lifting the reproducible number (2026-07-11)

The reproducible single-run is the only number that matters here: 58.0% at the
start of this pass. Every step below is measured on the same n=200 BIRD Mini-Dev
slice, config E, codestral, hints off.

| Step | EA | Δ | Verdict |
|---|---:|---:|---|
| Baseline (after the comparator fix) | 58.0% | — | — |
| **Question + `evidence` moved to the top of the prompt** | **61.0%** | **+3.0** | ✅ kept |
| + BIRD's `database_description/*.csv` in the schema block | 59.5% | −1.5 | ❌ gated off |
| + self-consistency (config F: 4 candidates at T=0.2–0.8, execution voting) | 58.0% | −3.0 | ❌ not taken |
| **+ few-shot from BIRD train (k=3)** | **61.5%** | **+0.5** | ✅ kept — **the product number** |

The few-shot result is one question, which is noise on n=200 — but it is worth
keeping for a reason that has nothing to do with the delta: **the product was
already running with it.** `api/main.py` builds its pipeline with
`fewshot_top_k=3`, while config E hard-coded 0 despite being named
`E_dense_fewshot_repair`. The eval harness was measuring a pipeline the product
does not run, and undercounting itself. Now the command in the README and the
live demo evaluate the same thing.

Self-consistency is worth a word, because it is the technique everyone reaches
for. Config F turns *repair off* (`Repair fired: 0/200`) on the theory that
voting replaces it, and samples at T=0.2–0.8. Both halves hurt: every candidate
is worse than the greedy pass, and the vote does not recover the difference. F
tops out at its own first pass (58.0%), while E's first pass is 59.5% and repair
carries it to 61.0%. Diversity through *temperature* does not pay on codestral.
Diversity through *different models* did — that is what took the archive to
85.5% — and it is now blocked on provider keys, not on code:
`scripts/ensemble_providers.py` is written and waiting (Groq 403, GitHub Models
401, OpenRouter's free tier 429).

**What the two results say together.** The win did not come from telling the
model something new — `evidence` was always in the prompt. It came from moving it
out of the middle, where models attend least: it sat after the schema block and
the few-shot block. The project's own residue analysis had said so
(`docs/kimi_v9_residue_analysis.md:27`) and nobody had acted on it.

The column-descriptions result is the same mechanism running backwards. They are
real information — BIRD's own gloss on its cryptic identifiers — and feeding them
is standard practice in BIRD systems. But they add thousands of characters per
table, the schema block swells, and the question drifts back toward the middle.
The information gained did not pay for the attention lost. Loader and renderer
are kept and tested; the flag is off. The version worth trying is column-level:
retrieve descriptions only for the columns a question plausibly touches.

Rule of thumb this pass earned: **space at the top of the prompt is a scarce
resource, and any lever that lengthens the prompt must pay for the room it takes.**

## Next levers (research, 2026-07-12 — see `docs/quality_levers_research_2026-07.md`)

After the lever-exhaustion day (eight levers measured dead or parity), three
research passes mapped what is left: training-free techniques through July 2026,
free API tiers, and a completeness sweep over the established named systems
(DIN/DAIL/C3/MAC/PET/TA/E-SQL/RSL/MCS/CHASE/OpenSearch/Alpha/XiYan + the
NL2SQL360 taxonomy). Ranked worklist — every measurement is a full n=200,
flag-gated, one lever per run:

- ⛔ **Gemini 3 Flash as generator** — AI Studio free tier fits n=200 in a day
  (1,500 req/day, no card); `_openai_compat.py` already speaks the protocol.
  Gated on creating an API key. Highest-variance option; pilot rule binding.
- **Question-driven value retrieval (CHESS-style)** — ~5 pp in CHESS's ablation;
  honest estimate here +0.5–2 pp (static top-K samples are already in the
  prompt; only part of the miss class is literal grounding). No keys needed.
- **DAIL-style few-shot selection** — mask schema tokens before embedding,
  optional SQL-skeleton re-rank (MCS-SQL ablation +4.8%). Cheap upgrade of the
  existing dense top-3 retrieval.
- **Instance-aware synthetic few-shot** (CHASE, +9.3 in its single-generator
  ablation — the largest legal number in the literature, discounted by the
  DAC transfer precedent: their +6.2 measured −6.5 here).
- **Question enrichment** (E-SQL, ~+5% on challenging, single-candidate by
  design) and **error-class micro-prompts** (output-column completeness, yes/no
  format, LIMIT discipline — priced at 1–2 questions each by the v18 audit).
- **Intermediate representation / query-plan CoT** — held back on codestral
  (CoT transfer risk); first in line for re-test if the generator changes.
- ⛔ **Qwen3 Coder 480B via OpenRouter `:free`** — 50 req/day makes n=200 a
  multi-day run; gated on an account/key and an endpoint canary. Devstral —
  a cheap probe on the existing key, low prior.
- Not worth runs (evidence in the research doc): DecoSearch-style decomposition,
  reflective self-refinement, semantic post-repair, SEED auto-evidence,
  deterministic AST normalization, and every component that maps onto an
  already-measured-dead channel (MAC/RSL/PET/Alpha/SQL-o1 parts).

Also queued: an Arcwise rescore of the current reproducible baseline (no-LLM,
~90 s) so the honest triplet exists for the product number, and a public
ceiling-anatomy write-up. Side-fact for the latter: ReViSQL's BIRD-Verified
found defects in 61.1% of BIRD Train instances — a second team independently
confirming the annotation-noise thesis behind the ~61–62% free-stack ceiling.

## Gated (needs a decision or an external host)

- ⛔ **Sustained-load benchmark.** The data-migration half is done (above); a throughput/latency run under sustained load still wants a host that isn't the Windows dev box.
- ⛔ **Space cleanup + git history.** `SESSION_HANDOFF.md` is un-tracked now, so future deploys won't republish it; removing it from the live Space and any git-history rewrite are force/deploy actions — owner's call.
- ⛔ **External pen-test of the public API.** Self-attestation is not a substitute; needs an outside pass.

## Won't-fix (verified)

- 🔎 `execution_accuracy._hashable` float-vs-float bucket boundaries — 0 set-mismatch across v22–v31 baselines; O(n²) replacement not justified. (This verdict looked only at float-vs-float. The int-vs-float half of the same function *was* broken — see the audit section above.)
- 🔎 `cache.py` miss/fill race — fires only under parallel eval workers, which aren't used.
- 🔎 Chasing BIRD annotation-quirk residue above 94% — saturation shown by 3-model reasoning sweeps.