Skip to content

About

Interactive text-to-SQL CLI agent — schema-grounded generation, read-only SQL safety, consensus/ensemble voting, execution-match eval harness

Resources

Stars

0 stars

Watchers

0 watching

Forks

Latest commit

 

History

1 Commit

Folders and files

Repository files navigation

Text-to-SQL CLI on Fireworks

An interactive terminal agent that turns natural-language questions into SQL, runs them against a database, and shows both the query and the results. Built on open-weight models served by Fireworks AI, demonstrated against the classic Chinook SQLite database.

A naive baseline prompt (Convert this question to SQL: {question}, no schema) hallucinated non-existent tables on 9 of 10 test questions. This agent returns the correct data on all 10, under 3 seconds, at roughly a thousandth of a cent per query.


Setup

./setup.sh                          # downloads data/Chinook.db (skips if already present)
uv sync                             # installs dependencies
echo "FIREWORKS_API_KEY=<your-key>" > .env.local   # or: export FIREWORKS_API_KEY=<your-key>

The CLI reads FIREWORKS_API_KEY from .env.local (preferred) or the environment.

Run

uv run cli                          # interactive session
python -m src.cli                   # identical, second entry point
uv run cli --question "How many artists are there?"   # one-shot, non-interactive

Inside the session, type a question in plain English or use a command:

Command What it does
help show the command list and example questions
schema [table] print the database schema, or one table's columns
clear reset the conversation (start a fresh context)
exit / quit leave the CLI (Ctrl-D also works)

Follow-ups work automatically. Ask "top 5 genres by sales", then "now only for the USA".

Flags and modes

Flag Effect
--model <name> pick the model: deepseek-v4-flash-0731 (default), gpt-oss-120b, kimi-k2p7-code-fast
--consensus N run N generations in parallel and return the majority answer (higher reliability)
--ensemble run all three models in parallel and vote across them (highest confidence)
--verbose show per-stage timings, token counts, cache hits, and cost
--db <path> point at a different SQLite database (defaults to data/Chinook.db)
--question "..." answer one question and exit (scriptable)
--no-log disable the per-query log at logs/queries.jsonl

When to use which

  • Default (single call, deepseek): everyday questions. Fast, cheap, accurate.
  • --model gpt-oss-120b: when latency matters most; it is the fastest model (~0.8s P50) at a small accuracy cost.
  • --consensus 3: high-stakes or ambiguous questions where you want reliability. Because the calls run in parallel, this costs about +0.1s of latency (3x the tokens).
  • --ensemble: maximum confidence via model diversity. Latency is gated by the slowest model (~3.2s with all three; drop kimi to stay under 3s).

Consensus and ensemble reduce random variation between runs. They do not fix systematic issues (for example, an output-formatting choice the model makes every time), so the default single call is the right tool for most queries.

What each mode shows

Default (clean, no flags) shows the SQL, a safety line, the results, a plain-English answer, and the total time:

❯ What are the top 5 best-selling genres by total sales?
  <syntax-highlighted SQL panel>
  ✓ read-only · tables: InvoiceLine, Track, Genre
  ✓ Executed in 0.02s · 5 rows
  <results table>
  Answer: Rock leads with $826.65, followed by Latin ($382.14), Metal ($261.36), ...
  ✓ 0.91s total

--verbose keeps everything above and adds [1/4]…[4/4] stage labels plus a stats line:

  [4/4] ✓ 0.91s total
    3381 in / 89 out tokens (cached: 3104) · $0.00056 · 1 attempt · ttft 0.62s · deepseek-v4-flash-0731

--consensus 3 adds an agreement line so you can see how many of the parallel runs matched:

  Generating SQL (consensus N=3)…
  <SQL panel>
  consensus: 3/3 candidates agreed
  ...

--ensemble names the models it ran and shows their agreement:

  Generating SQL (ensemble: gpt-oss-120b, kimi-k2p7-code-fast, deepseek-v4-flash-0731)…
  <SQL panel>
  consensus: 3/3 candidates agreed
  ...

When a query fails it never shows a traceback. A retry prints a warning with the exact sqlite error and tries again; an exhausted retry prints a plain message with the last error.

Results (10 dev questions, execution-match scoring)

Configuration Strict Semantic P50 latency Cost / query
deepseek-v4-flash-0731 (default) 8/10 10/10 ~2.6s $0.00059
gpt-oss-120b (--model) 7/10 9/10 ~0.8s $0.00058
kimi-k2p7-code-fast (--model) 7/10 9/10 ~2.2s $0.00278
Naive baseline prompt 0/10 1/10 ~2.4s n/a

Strict = exact result-set match. Semantic = correct data, allowing output-shape differences (the default returns the right data on all 10; see below and results/eval_report.md).

The baseline fails by inventing tables that do not exist (orders, sales, products) — the classic text-to-SQL failure mode, reproduced on demand. The schema-grounded agent produced zero hallucinated table or column names across all runs, and every query executed on the first attempt. Its remaining "misses" return the correct data in a different shape than the answer key expects (for example, a customer name split into two columns where the key concatenates it, and the key is itself inconsistent about this between questions). Per-question detail, including strict vs. semantic scoring and the optional-modes comparison, is in results/eval_report.md.

Model accuracy on 10 questions varies by about one question between measurement sessions (serverless routing nondeterminism), so no single run reliably ranks models this close. A larger evaluation set is the first item in "what's next".

Reproduce it (writes results/eval_report.md and dev_answers.json, ~$0.04):

uv run python -m src.eval --models deepseek-v4-flash-0731,gpt-oss-120b,kimi-k2p7-code-fast --baseline

Design decisions

  • How the agent learns the schema: it introspects the database at startup and injects the full CREATE TABLE DDL, foreign-key relationships, and a few sample rows per table into a fully static system prompt. The 11-table schema fits in ~3k tokens, so there is no retrieval step. Keeping the prompt static means Fireworks caches the prefix across turns (observed live). Schema assembly sits behind one function, so larger databases can swap in retrieval later without touching the agent.
  • When SQL fails: the SQLite error is fed back to the model for correction, up to two retries, and only on execution errors. A zero-row result is a valid answer, never retried. The database itself is the validator, which is cheaper and more reliable than a second LLM reviewing the query. In practice, grounding was good enough that the retry rarely fired.
  • Safety (trust is everything): model SQL runs only through a read-only connection plus a SQLite authorizer allowlist plus the driver's single-statement rule, verified against CTE-wrapped deletes, comment-hidden drops, and stacked statements. Query cost is bounded too (cell-size limit, VM-step budget, 10,000-row fetch cap) so no query can hang or exhaust memory. The SQL is always shown, and ambiguous interpretations surface as a note: line rather than a silent guess.
  • Open-weight vs. proprietary: open models on Fireworks land the accuracy in range while costing roughly 10 to 40x less per token than a frontier proprietary model, and they can be self-hosted so sensitive data need not leave your boundary. That headroom is what makes even 3x consensus cheaper than a single proprietary call.
  • Conversation context: the full turn history is passed each time; the model decides whether a message refines the last query or starts fresh. clear resets it.

Validation

The evaluation harness (src/eval.py) runs all 10 questions through the agent and the naive baseline prompt, executes both the generated and gold SQL, and compares result sets (not SQL text, since different SQL can be equally correct). It reports per-question pass/fail with a reason, latency, and cost, and writes dev_answers.json. --demo-retry exercises the self-correction loop on a deliberately broken query.

Repository map

src/agent.py    agent core: schema grounding, generation, read-only safety, retries, consensus
src/cli.py      interactive CLI: rendering, streaming, commands, transparency
src/eval.py     evaluation harness (validation evidence + dev_answers.json)
src/utils.py    database helpers
dev_answers.json          the 10 answers from the default configuration
results/eval_report.md    full evaluation evidence
docs/demo-script.md       example questions to try, graded by complexity
docs/scaling-playbook.md  how this scales beyond the PoC

AI assistance

Built with Claude Code as an AI pair: planning, implementation, an adversarial security review, and the evaluation were done under my direction. Every performance and cost number here comes from live measured runs, not estimates.

License

MIT — see LICENSE.

About

Interactive text-to-SQL CLI agent — schema-grounded generation, read-only SQL safety, consensus/ensemble voting, execution-match eval harness

Resources

Stars

0 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages