eval(sql): the natural-language→SQL path has no accuracy measurement and no test file #13415

Problem

AutoBot ships a natural-language→SQL path and has never measured whether it is
correct. autobot-backend/services/nl_database_service.py converts NL to SQL and
executes it against the local infrastructure DB or an external connection resolved from
the secrets manager. It has no test file at all, and no eval.

This is a user-facing correctness surface where the failure mode is silent: a query that
runs cleanly and returns a plausible-but-wrong number is indistinguishable from a
correct one at the UI.

Evidence

$ rg -l 'nl_database' --glob='!.claude/worktrees' --glob='!.worktrees'
docs/developer/audits/datetime-producer-audit.md
autobot-frontend/src/types/generated/api.ts
autobot-backend/services/nl_database_service.py
autobot-backend/initialization/router_registry/feature_routers.py
autobot-backend/api/schemas_knowledge.py
autobot-backend/api/schemas_agent.py
autobot-backend/api/nl_database.py

No test file, no golden set, no eval. The service does enforce read-only
(_SAFE_SQL_PREFIXES, _DANGEROUS_KEYWORDS) — that is a safety guard, not an
accuracy measurement, and it is also untested.

AutoBot's existing eval harness (autobot-backend/eval/) measures something else:
eval/runner.py replays golden agent trajectories, scoring an ordered tool-sequence
match plus an RLM quality score against a recorded baseline_score, with
regression/improvement classification in eval/report.py. The three goldens in
eval/golden/ are code_fix_null_guard, qa_deploy_role, triage_502_websocket —
none exercise SQL generation.

Proposed approach

Extend the existing harness rather than building a second one.

1. Add eval/golden/sql/*.json: each entry is a natural-language question, the schema it
   runs against, and a reference SQL query whose result set is the ground truth.
2. Grade on execution accuracy, not SQL text. Many statements answer a question
   correctly; comparing strings scores paraphrases as failures and tunes the prompt
   toward one author's style rather than toward being right. Compare result sets:
   • strip column names (an alias change is not a wrong answer)
   • match columns by position, not by sorted key name — sorting by name means
     renaming a column reorders the comparison and scores a correct answer wrong
   • compare values exactly after canonicalisation, numbers to 2 dp
   • honour row order only for questions whose answer is a ranking
3. Split verdicts into pass / wrong / error / refused. A refusal to emit SQL and
   SQL that ran and returned the wrong thing need different fixes and must not be one
   bucket.
4. Enforce a floor in a test so the corpus cannot be quietly narrowed to raise the score.

Risks / constraints

• The corpus must include joins and window functions from the start. A question set
  of single-table aggregates produces a flattering number that measures the question set,
  not the system.
• Reference queries need maintaining against schema drift; tie them to a fixed fixture
  schema rather than live infrastructure tables.
• Publishing an accuracy number invites being held to it. That is the point, but the
  first number should be labelled provisional until it has been run more than once —
  run-to-run variance on a single pass is not distinguishable from a real change.

Acceptance criteria

• eval/golden/sql/ corpus exists with ≥40 questions, including ≥6 requiring a join
  and ≥5 requiring a window function or CTE.
• A sql_candidate runner plugs into the existing CandidateRunner contract.
• Grading is execution-accuracy with position-matched, name-stripped columns and
  4-way verdicts.
• A test asserts both a minimum pass rate and a minimum corpus size/composition,
  so neither can be relaxed silently.
• nl_database_service.py gains its first unit tests covering the read-only guard
  (_SAFE_SQL_PREFIXES / _DANGEROUS_KEYWORDS), including keywords embedded inside
  an otherwise-valid SELECT.
• The measured number is recorded with the date and the corpus version.

Part of #13251.

Part of #13413.

Sequencing

Wave 1 — fully independent. Corpus authoring is the long pole, so starting early is free.

Sequencing for the whole set: #13413.