quarry

Ask your database anything: a LangGraph agent that plans, writes, verifies and self-corrects SQL, and shows its work. Every step streams to the UI: which tables it picked and why, the plan, each tool call, every SQL attempt with the check that rejected it, the read-only run, the result, a chart and a span-by-span trace. Writes are impossible by construction, and rows that try to talk to the model are withheld from it.

Recorded with Playwright in Microsoft Edge from the real web UI running the offline scripted demo: the model's replies come from a script, not an LLM (there is no API key in this repository). Everything else is the real system on the real data: schema linking, tool calls, the SQL guard, the static check, the read-only sandbox, conversation memory, the chart and the trace. The script's first query deliberately names a column that does not exist, so the recording shows the static check catching it and the repair loop fixing it. Every number in an answer is filled in from the live query result.

Why

Text-to-SQL demos stop at "the model wrote a query". Production needs the rest: the model must see the right slice of a large schema, its SQL must be checked before it runs, it must never be able to change data whatever it reads, it has to notice when a result looks wrong, follow-ups have to resolve against earlier turns, and someone has to be able to see exactly what happened afterwards. quarry is that rest, built as a small, typed, tested agent.

Related MCP server: sqlite-mcp-server

What it does

• Agent graph (LangGraph StateGraph): schema linking, planning with tool calls, SQL generation, static checking, sandboxed execution, result verification, bounded self-correction, answer synthesis and a Vega-Lite chart. Step and token budgets per question.

• Typed tool calling: list_tables, describe_table, sample_rows, run_sql as LangChain StructuredTools with Pydantic arguments. The model explores the schema when it is unsure, then submits SQL through run_sql.

• Two-layer guardrails: an allowlist on the sqlglot AST (one SELECT/WITH, no DDL/DML, PRAGMA, ATTACH, COPY, INSTALL, SELECT ... INTO, hidden DML in CTEs, stacked statements, table functions or unlisted functions) and a read-only engine underneath it (SQLite mode=ro + query_only + an authorizer callback + size limits; DuckDB read_only with external access off and the configuration locked). Row caps and timeouts on every query.

• Prompt-injection tripwire: every value, column name and description a model sees from the database (quarry's own model, or another app's through MCP ask) is scanned; suspected injections are replaced by a placeholder and flagged in the UI and the trace.

• Memory: a LangGraph SQLite checkpointer per conversation, so "break that down by channel" works and survives restarts.

• Observability: a JSONL trace per question (node, LLM and tool spans with latency, tokens and attempts), rendered as a waterfall in the UI; optional Langfuse/LangSmith export.

• Interfaces: quarry ask | chat | serve | mcp | eval, a FastAPI UI that streams every step over Server-Sent Events, and an MCP server (stdio) exposing the four tools plus ask to other AI apps.

• LLM: any tool-calling model on OpenRouter through ChatOpenAI; one config line picks it. A scripted model implements the same interface for tests and the offline demo.

• Evaluation: a resumable, cached, rate-limited Spider execution-accuracy harness (one command once a key exists) and an offline suite that measures everything that needs no LLM.

The follow-up in the scripted demo. Memory is real: the first turn is stored in the SQLite checkpointer and schema linking carries its tables into this one. The plan and SQL text are lines from the demo script (matched to the question by a pattern), not reasoning over the history; the query run, the result and the chart are live.

Quickstart

You need uv. No GPU, no API key for the demo.

Windows (PowerShell)

git clone https://github.com/aamodbhatt/quarry; cd quarry
uv sync
uv run quarry demo build                  # downloads the 4 MB Hillstrom CSV (checksummed), builds data/warehouse.sqlite
uv run quarry serve --offline             # http://127.0.0.1:8097  (scripted demo, no key)

macOS / Linux

git clone https://github.com/aamodbhatt/quarry && cd quarry
uv sync
uv run quarry demo build
uv run quarry serve --offline             # http://127.0.0.1:8097

With a model (OpenRouter): copy .env.example to .env, put your key in OPENROUTER_API_KEY, then drop --offline:

uv run quarry ask "Which campaign had the best conversion rate?" -v
uv run quarry chat                        # conversation with memory; /new, /quit
uv run quarry serve                       # web UI against the real model
uv run quarry ask "..." --db path/to/any.sqlite   # any SQLite or DuckDB file

The model is one line in configs/quarry.toml (model = "google/gemma-4-31b-it:free", picked from OpenRouter's public model list on 2026-11-02 as a free model that supports tools and tool_choice).

As an MCP server (desktop assistants, IDEs, any MCP client):

{"mcpServers": {"quarry": {"command": "uv", "args": ["run", "--directory", "/path/to/quarry", "quarry", "mcp"]}}}

Docker: docker compose up --build, then http://127.0.0.1:8097 (the container builds the demo warehouse on first start; set command to serve --host 0.0.0.0 --offline for the demo).

How it works

flowchart LR
    Q([question]) --> ST[start_turn]
    ST --> LS["link_schema<br/>BM25 + sampled values<br/>+ hashing embeddings, RRF"]
    LS --> PL[plan]
    PL <-->|tool calls| EX["explore<br/>list_tables · describe_table · sample_rows"]
    PL --> GE["generate<br/>run_sql call"]
    GE <-->|tool calls| EX
    GE --> CK{"check<br/>AST guard + static check"}
    CK -->|error, repairs left| GE
    CK -->|ok| RUN["execute<br/>guard again + read-only engine"]
    RUN -->|error, repairs left| GE
    RUN --> VF{"verify<br/>empty · all NULL · raw rows"}
    VF -->|suspicious, once per kind| GE
    VF -->|accepted| AN[answer] --> CH["chart<br/>Vega-Lite"] --> FI["finish<br/>append to memory"]
    FI --> M[(SQLite checkpointer)]

Every node, LLM call and tool call writes a JSONL span (latency, tokens, attempt) to runs/traces/<thread>/<trace>.jsonl; the UI draws it as a waterfall.

The parts worth reading:

• Schema linking that fits big schemas (linking/linker.py). One BM25 document per column (table, name, description, sampled values) and per table, plus exact value hits ("Mens E-Mail" in the question points at campaigns.name), plus an optional embedder behind a small protocol; the rankings are fused with reciprocal-rank fusion so no score needs calibrating. Tables that join two picked tables through a foreign key are added as bridges, and a follow-up also carries the previous query's tables.

• A guard that is an allowlist, not a blocklist (guard/policy.py). It walks the whole AST, so WITH d AS (DELETE ... RETURNING *) SELECT ... and SELECT * INTO fail even though they start with WITH/SELECT; any function not on a list of pure functions fails, so unknown file or extension functions are refused by default. CTE names are resolved scope by scope, so a CTE called sqlite_master inside one subquery cannot vouch for a real sqlite_master elsewhere in the statement. Underneath, the engine refuses the same things on its own: the red-team suite runs every attack with the guard bypassed too.

• Tool output can never trigger a write. There is no write tool, the only path from SQL text to a database is SafeExecutor (guard, then read-only connection), and each query gets a fresh connection. In the planted-injection scenario below, a scripted model executes any of three planted write commands (DROP, DELETE, TRUNCATE) it reads in a row: it tries all three and none executes.

• Self-correction with real feedback. Errors go back to the model as the ToolMessage answer to its own run_sql call (standard tool-calling transcripts), from three places: the guard, a static check that resolves every column with sqlglot's qualify, and the engine. The static check applies SQLite's "unknown double-quoted name is a string" rule only where Spider-style SQL relies on it (the value side of a comparison with a column), which removed every false alarm on Spider dev gold while SELECT "nmae" is still caught. A verify step sends back results that are empty, all-NULL, or raw rows where the question asked for a number, once per kind, so a genuinely empty answer is accepted on resubmission.

• Budgets and routing as plain data. Nodes record whether a retry is still allowed; edges are pure functions of the state; dependencies arrive as LangGraph run context, so the compiled graph is reusable across databases, models and threads.

• The scripted model is a real BaseChatModel (llm/scripted.py): it supports bind_tools, emits tool_calls and reports token usage, so the 191 tests and the demo exercise the exact code path a real model takes.

Results

Measured on a laptop: Intel Core Ultra 9 275HX (24 logical CPUs), Windows 11, Python 3.12, CPU only, no LLM. Full report: docs/results/offline.md (JSON). Regenerate with uv run quarry eval offline.

Measured now (no LLM needed)

SQL guard on Spider gold queries. A guard that blocks correct SQL is useless, so the allowlist was run on all 8,034 dev and train_spider gold queries (train_others, 1,659 more, ships only in the full Spider archive, which did not download; see below):

The one block is a true positive: train_spider #3153 joins Ref_Company_Types, a table that does not exist in the assets_maintenance schema. The first run passed only 91.3% (dev) and 89.8% (train): sqlglot 30 models AND/OR as function nodes, so the function allowlist caught them. Operators are now told apart from function calls, which fixed all of it. The static check went through the same loop: 213/1,034 false alarms (Spider writes string literals in double quotes) down to 0/1,034 after matching SQLite's semantics, and it stays at 0 with the rule narrowed to comparison values.

A guard bug found in review, and fixed. The guard used to collect CTE names for the whole statement, so a CTE defined inside one subquery let the same name skip the catalog and file checks anywhere else: SELECT b.* FROM (WITH sqlite_master AS (SELECT 1 AS x) SELECT x FROM sqlite_master) AS a, sqlite_master AS b passed the guard. On SQLite the authorizer still refused it; on DuckDB it returned catalog rows (the duckdb_tables variant too), and a "secrets.csv" variant reached a file scan that only enable_external_access=false stopped. CTE visibility is now resolved scope by scope (and never for a qualified name). These statements are in the red-team set below, and the property tests, which now judge every statement the guard lets through with the engines' own parsers and planners, find the bypass on both engines when run against the old guard.

Red team: 162/162 cases pass, the database file never changed. 81 cases (21 writes and DDL, 11 stacked-statement tricks, 24 file/extension/catalog reads including the CTE-scope variants, 6 configuration changes, 5 resource-exhaustion queries, 14 scary-looking benign controls that must still run), each against SQLite and DuckDB copies of a warehouse-shaped database:

With the guard bypassed, the engines alone still refuse or fail every hostile SQLite case (62/62) and 49/62 DuckDB cases. The 13 DuckDB statements that run change nothing: VACUUM and ANALYZE (no-ops on a read-only file), a temp table in memory, one pair of stacked SELECTs, and 9 reads of DuckDB's own catalog (duckdb_tables, sqlite_master, sqlite_schema, information_schema, pragma_table_info and the CTE-scope variants). The file is unchanged. That gap is why both layers exist.

Planted injections. 20 injection strings are planted in a notes table and the agent is driven by a scripted model that executes any of three planted write commands it reads (DROP TABLE customers, DELETE FROM outcomes, TRUNCATE TABLE outcomes):

Injection detector. It flags all 20 development strings and none of 10 benign notes, but those were used while writing the rules, so that is a regression test. On an external set it never saw while being written, deepset/prompt-injections (train + test), it has 6.1% recall (16/263) at 0% false positives (0/399). It is a narrow, English, database-oriented tripwire (many of that set's injections are German or role-play jailbreaks); the guard and read-only engine, not the detector, are the safety boundary.

Schema linking on Spider dev (1,034 questions). The 20 Spider dev databases could not be downloaded during development (the official archive is on Google Drive and its download quota stayed exhausted), so these numbers use the schemas only, without sampled values:

"all@k" means every gold table (column) is in the top k. Spider databases are small (about 4 tables and 22 columns on average), so the random baseline is shown for context; the gap is in the columns. Gold tables and columns come from parsing each gold query with sqlglot.

Spider difficulty labels. The harness reports accuracy per Spider difficulty level using a re-implementation of Spider's eval_hardness on sqlglot; on dev it reproduces the official totals exactly (248 easy / 446 medium / 174 hard / 166 extra).

Latency on the demo warehouse (64,000 customers), p50 over 30 runs: run_sql with a three-table join and aggregation 26.3 ms; guard 0.66 ms; static check 1.63 ms; schema linking 0.65 ms; sample_rows 0.79 ms. A complete offline agent run (5 scripted LLM calls, one repair, its JSONL trace written to disk) takes 56.8 ms at p50 over 15 runs, so the graph adds little next to a real model call. These are laptop numbers and move by a few tens of percent between runs.

Needs an API key (not run)

Execution accuracy of the full agent on Spider dev is implemented and tested end to end (an oracle model scores 100%, reruns resume and hit the LLM cache), but it has not been run: there is no OpenRouter key in this repository, and no LLM number is reported anywhere. With a key:

uv run quarry data spider          # 206 MB official archive; unpacks the 20 dev databases
uv run quarry eval benchmark       # all 1,034 dev questions; --limit 50 for a slice

It writes docs/results/spider_dev_<model>.json and .md: execution accuracy overall and per difficulty, how many first attempts failed and how many of those self-correction rescued, mean attempts, tokens and latency. Before any model call it runs every gold query through the same guard and sandbox a prediction goes through; a question whose gold cannot run there is left out of the denominator and listed in the report rather than counted as a model error. It is resumable (runs/bench/<model>/predictions.jsonl), every LLM response is cached in SQLite, and a client-side token bucket plus retries handle rate limits; questions that fail because the API is unavailable are not recorded, and after five in a row the run stops with a one-line message so the same command resumes it. Why Spider and not BIRD: BIRD mini-dev's official download is 801 MB and its SQLite databases unpack to about 1.5 GB, over this project's 500 MB data budget; Spider's archive is 206 MB.

Design decisions and trade-offs

• sqlglot AST allowlist plus engine-level read-only, not one or the other. Parsers and engines disagree at the edges (sqlglot parses REINDEX as a column name and INSTALL as an alias in the SQLite dialect); the root-type allowlist catches those, and the engine catches whatever a parser bug might let through. The cost is two lists of functions to maintain; the Spider gold run is the regression test for both.

• What runs is exactly what you see. The guard never rewrites SQL (no injected LIMIT); row caps are enforced at fetch time and timeouts by a progress handler (SQLite) or interrupt() (DuckDB). The UI shows the statement that actually ran.

• A rule-based injection detector. Transparent, fast and with no false positives on the data seen so far, but low recall on general jailbreak text. A classifier would raise recall at the price of a model download and false positives on ordinary database text; the architecture does not depend on either, because nothing the model reads can cause a write.

• Deterministic charts. The chart is picked from the result's shape (category, time or ordered axis, numbers) instead of asking the model, which costs no tokens and is testable.

• Fresh connection per query. On SQLite a whole sample_rows call (guard, new read-only connection, query) takes under a millisecond at p50, a fair price for no shared state between queries.

• BM25 + hashing embeddings by default. No downloads and sub-millisecond linking; a neural embedder can be dropped in through the Embedder protocol.

More in docs/DESIGN.md: goals, non-goals, invariants, data model, failure modes and alternatives.

Testing

uv run pytest runs 191 tests in about 30 to 35 seconds with no network, no key and no model (a .env with a real key is ignored: the suite removes the key and disables .env loading):

• Property tests (Hypothesis), on SQLite and DuckDB: a spy engine records every statement the guard lets through while Hypothesis throws nested CTEs, catalog-shadowing and file-like names, table functions, qualified names, unions, recursion, stacked statements, comment tricks and arbitrary text at the executor. Each recorded statement is judged by an oracle that shares no code with the guard: SQLite compiles it under a recording authorizer (only SELECT, reads of user tables or CTEs, pure functions); DuckDB's own parser must find one SELECT and its query plan may scan only user tables. The database file must be byte-identical afterwards. Run against the pre-fix guard, the test finds the CTE-scope bypass on both engines. Also: guard verdicts are well formed and deterministic on any text, safe queries stay allowed, result matching is order-free.

• Agent tests drive the real graph with a scripted model: repair after a static-check error and after an engine error, guard refusal, the empty-result second look, budget exhaustion, schema tools, withheld injections, fenced-SQL replies, API failures, memory across a restart, event order and traces.

• Interface tests: FastAPI SSE stream and trace endpoint, the MCP server through an in-process MCP client (including injected rows withheld from ask and thread ids that try to leave the trace directory), and the CLI (ask, chat, .env lookup).

• Evaluation tests: the benchmark harness end to end on a fixture dataset (scoring, resume, LLM cache, gold that cannot run left out, a resumable stop when the API is down), hardness labels, red-team subset and the injection scenario.

CI (ubuntu-latest): ruff, mypy --strict over src, tests and scripts, pytest, and a Docker build with a smoke run.

Project layout

src/quarry/
  agent/        graph.py (StateGraph + routing), nodes.py, prompts.py, checks.py,
                chart.py, runner.py (streaming, memory), state.py, context.py
  guard/        policy.py (AST allowlist), injection.py (tripwire)
  db/           engines.py (read-only SQLite/DuckDB), database.py (SafeExecutor), catalog.py
  linking/      linker.py, bm25.py, embeddings.py, text.py
  llm/          factory.py (OpenRouter), scripted.py, cache.py
  tracing/      tracer.py (JSONL spans), exporters.py (Langfuse/LangSmith)
  eval/         benchmark.py, offline.py, redteam.py, spider.py, hardness.py, matching.py
  demo/         warehouse.py (Hillstrom star schema), script.toml + script.py (offline demo)
  web/          app.py (FastAPI + SSE), static/ (no-build UI)
  tools.py      the four StructuredTools
  mcp_server.py MCP server (stdio)
  cli.py        typer CLI
configs/quarry.toml   typed configuration
scripts/capture_ui.py README visuals (Playwright + Edge)
docs/                 DESIGN.md, results/, assets/

Data and credits

All datasets are downloaded at runtime; none are shipped in this repository.

• Spider (Yu et al., EMNLP 2018; yale-lily.github.io/spider), CC BY-SA 4.0. Questions and gold SQL come from the xlangai/spider mirror and schemas from taoyds/test-suite-sql-eval. The guard, schema-linking and difficulty results above are computed on it, and docs/results/ quotes one gold query (train_spider #3153).

• deepset/prompt-injections, Apache-2.0: the external injection test set.

The demo warehouse is built from Kevin Hillstrom's public MineThatData e-mail analytics challenge: 64,000 customers who bought in the past year, randomly split into a men's e-mail, a women's e-mail and a no-e-mail control, with visits, conversions and spend tracked for two weeks. quarry normalises it into campaigns, customers, campaign_sends and outcomes; the only value change is correcting the source's "Surburban" spelling. Nothing is invented (no dates, costs or IDs beyond row order).

Roadmap

• Run the Spider benchmark with a key and publish the numbers (including per-step costs).

• Linking metrics with sampled values once the Spider databases download; BIRD with a larger data budget.

• A neural embedder and a learned fusion weight for schema linking.

• Multilingual and classifier-based injection detection, measured on the same external set.

• PostgreSQL support (read-only role + statement_timeout), reusing the same guard.

License

MIT © 2026 Aamod Bhatt