Boston Query Compiler

A system for answering natural-language questions about Boston's open city data — 311 service requests, Vision Zero crash records, and building permits — by compiling each question into a deterministic execution plan rather than answering it from retrieved text.

Originally built in a single day for Track A of the "RAG the City" hackathon; now continued as a personal/learning project.

The thesis

Boston's city data is fragmented across datasets that describe different dimensions of the same city: resident complaints, real-world safety incidents, physical development. Retrieval alone — even a good, contextual RAG pipeline with hybrid search and reranking — can't reliably answer most interesting questions about this data, because they require computation (counts, aggregates, comparisons), joins across datasets, temporal reasoning, or knowing when the data simply doesn't support an answer.

So instead of routing every question through vector search, an LLM planner first classifies what kind of question it is and routes it to the engine suited to that intent:

User Question
     │
     ▼
Query Understanding (LLM planner) — classifies intent, emits a typed QueryPlan, never answers directly
     │
     ┌───────────────┼────────────────┐
     ▼               ▼                ▼
  lookup          aggregation     relationship
  (retrieval)     temporal        multi-source
     │            geospatial          │
     ▼               │                ▼
Contextual RAG        ▼           cross-dataset join
BM25 + dense       DuckDB / SQL   (proximity + time as
+ reranker         (deterministic  universal join keys)
     │              calculation)      │
     └───────────────┼────────────────┘
                      ▼
                   Evidence
                      ▼
              LLM Synthesizer → Final Answer
                (never asked to compute — only to phrase
                 numbers it's handed, verbatim)

Division of responsibility (deliberately not blurred):

• LLM planner — understands the question, emits a structured QueryPlan (JSON). Never invents numbers, never writes SQL itself.
• DuckDB executor — does all deterministic calculation (counts, aggregates, comparisons, joins) via a small set of parameterized SQL templates, directly against Parquet files.
• Contextual RAG — BM25 + dense embeddings + Reciprocal Rank Fusion + cross-encoder reranking, used only for genuinely unstructured free-text questions (e.g. "find permits involving accessibility upgrades").
• LLM synthesizer — turns computed evidence into prose, guarded by a numeric-fidelity check that rejects any answer mentioning a number not present in the evidence it was given.

Why not let the LLM write SQL directly?

The planner is constrained to emit a small typed QueryPlan (operation, dataset, metric, filters, group_by, time_range, ...) that Python translates into known, parameterized SQL templates — never free-form LLM-generated SQL. This trades some flexibility for a hard guarantee: the planner cannot invent a column that doesn't exist or produce an injectable query. Supported operations are deliberately small: lookup, count, rank, compare, trend, cross_dataset, search. Sophistication comes from correct routing, not from covering every conceivable query shape.

Datasets

Three independently-schemaed City of Boston open datasets (2022–2024):

Dataset	Rows	Role
311 Service Requests	887,478	Primary/backbone — dates, category, department, status, neighborhood, coordinates
Vision Zero Crash Records	10,293	Spatial+temporal like 311, but a different signal (safety incidents vs. resident reports)
Approved Building Permits	115,811	Structured fields (valuation, type, date) + free-text project descriptions — the home for contextual RAG

time and location are the universal join keys across all three — cross_dataset joins are lat/lon proximity (haversine, computed in plain SQL, no GIS extension) + a time window, not JOIN a ON a.id = b.id. Each dataset also uses a genuinely different neighborhood taxonomy (e.g. service_requests.neighborhood = "Back Bay" vs. building_permits.city = "Boston/Back Bay", and Vision Zero has no neighborhood field at all) — see data/schema_notes.md for the full mismatch table and every other real-data gotcha discovered while building this (rare vs. common category names, coarse-vs-free-text fields, etc.).

Evidence handling

• Conflict detection — when two sources disagree, the design surfaces both rather than silently picking one (not yet implemented; tracked as future work).
• Abstention — when the data doesn't support an answer (e.g. a causal "why did X happen?" question), the planner says so rather than letting the LLM invent an explanation. This is a first-class output type, not an error path — examples/questions.yaml includes hand-verified abstain cases like "Why do noise complaints spike in the summer?" and "Which contractor is responsible for filling potholes?" (not tracked in this data at all).
• Numeric fidelity guard — the synthesizer's output is checked against the evidence it was given; any number in the answer that doesn't appear verbatim in the evidence triggers one retry, then a fail-closed fallback to the raw evidence rather than a confident hallucination.

Architecture / cost constraints

Everything runs at zero API spend:

• LLM calls (planner, synthesizer) use Groq's free tier — openai/gpt-oss-120b for planning (the only model family on Groq with constrained-decoding JSON support, as of this build), openai/gpt-oss-20b for prose synthesis.
• Retrieval (embeddings + reranking) runs locally on CPU via sentence-transformers (all-MiniLM-L6-v2 for embeddings, cross-encoder/ms-marco-MiniLM-L-6-v2 for reranking) — no API cost, no local LLM generation (the laptop this was built on can't run that well, but small embedding/reranking models are cheap enough to run fine).
• Everything else — the DuckDB executor, SQL templates, BM25 via DuckDB's fts extension — is local and free by construction.

CLI only; no web frontend.

Setup

python3 -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"

# One-time data acquisition (~530MB of CSVs to data/raw/, gitignored):
curl -sL -o data/raw/311_2022.csv "https://data.boston.gov/dataset/8048697b-ad64-4bfc-b090-ee00169f2323/resource/81a7b022-f8fc-4da5-80e4-b160058ca207/download/lagan_311_open_data_2022_rvsd.csv"
curl -sL -o data/raw/311_2023.csv "https://data.boston.gov/dataset/8048697b-ad64-4bfc-b090-ee00169f2323/resource/e6013a93-1321-4f2a-bf91-8d8a02f1e62f/download/lagan_311_open_data_2023_rvsd.csv"
curl -sL -o data/raw/311_2024.csv "https://data.boston.gov/dataset/8048697b-ad64-4bfc-b090-ee00169f2323/resource/dff4d804-5031-443a-8409-8344efd0e5c8/download/lagan_311_open_data_2024_rvsd.csv"
curl -sL -o data/raw/vision_zero_crashes.csv "https://data.boston.gov/dataset/7b29c1b2-7ec2-4023-8292-c24f5d8f0905/resource/e4bfe397-6bfc-49c5-9367-c879fac7401d/download/tmpo50ge3my.csv"
curl -sL -o data/raw/building_permits.csv "https://data.boston.gov/dataset/cd1ec3ff-6ebf-4a65-af68-8329eceab740/resource/6ddcd912-32a0-43df-9908-63574f8c7e77/download/tmpgenxpedd.csv"

python3 -m boston_query.data_prep   # builds data/processed/{service_requests,vision_zero_crashes,building_permits}.parquet
python3 -m boston_query.retrieval   # builds data/processed/permits_rag_index.duckdb (embeddings + FTS, ~20s)

cp .env.example .env   # fill in GROQ_API_KEY (free tier: https://console.groq.com)

Usage

boston-query "how many potholes were reported in Dorchester in 2023?"
boston-query "find building permits involving accessibility upgrades"
boston-query "compare noise complaints in Roxbury vs Back Bay in 2024"
boston-query "which neighborhoods have noise complaints near a Vision Zero crash?"

boston-query "..." --json                # machine-readable output
boston-query "..." --model <groq-model>   # override planner/synthesizer model
boston-query "..." --plan-file plan.json  # skip the LLM planner, run a hand-built plan offline

Every run prints the compiled QueryPlan, the generated SQL (or retrieval query) and its parameters, the raw evidence, and finally the synthesized prose answer — the intermediate steps are deliberately visible, not hidden behind the final answer.

Testing

pytest              # fast, offline: no Groq calls (local retrieval models run, cached after first use)
pytest -m llm        # live planner accuracy eval against examples/questions.yaml, needs GROQ_API_KEY

examples/questions.yaml is a hand-written, hand-verified set of 37 questions spanning every operation type (count, rank, trend, compare, lookup, cross-dataset, search) plus 6 deliberate abstain cases — the questions were worked out by hand into their expected execution plan before any planner/executor code was written, to drive the QueryPlan schema design rather than the other way around.

Evaluation: does the compiler architecture actually help?

src/boston_query/benchmark.py runs a 3-way, LLM-judged comparison over the same 37 questions:

1. query_compiler — this project's actual planner → SQL → evidence → synthesizer pipeline.
2. naive_rag — a baseline with no SQL: bare field-dump text per row, dense-embedding-only retrieval, top-K handed directly to an LLM to answer from.
3. contextual_rag — a stronger baseline: the same contextual-retrieval technique used for the real search operation (narrative-template text per row, BM25 + dense + RRF + cross-encoder rerank), still with no SQL — the LLM still has to answer from a handful of retrieved rows, just better-chosen ones.

Both baselines index the full corpus (all three datasets, ~1.01M rows, in src/boston_query/baseline_rag.py) so the comparison isn't an artifact of a smaller corpus. Metrics: answer accuracy, numeric accuracy (restricted to questions with a hard numeric ground truth), abstention rate, and citation hallucination rate (baselines only — the query compiler's numeric-fidelity guard structurally prevents that class of error).

python3 -m boston_query.baseline_rag   # builds data/processed/baseline_rag_index.duckdb (~5GB, ~2M row-encodes)
python3 -m boston_query.benchmark      # runs the 3-way comparison, checkpointed to
                                        # data/processed/benchmark_results.json (safe to re-run
                                        # after a quota interruption — already-graded pairs are skipped)

Preliminary results (a 15-question, category-representative subset covering every operation type — count/rank/trend/compare/lookup/cross_dataset/search/abstain — with the full 37-question run planned as a follow-up):

System	Answer accuracy	Numeric accuracy	Abstention rate	Citation hallucination
query_compiler	86.7%	83.3%	13.3%	n/a (guarded structurally)
naive_rag	20.0%	0.0%	46.7%	0.0%
contextual_rag	20.0%	0.0%	53.3%	0.0%

On the questions requiring real computation (count/rank/trend/compare), the query compiler got 83.3% right while both RAG baselines got 0%. Neither ever cited a record ID that wasn't actually retrieved (0% citation hallucination), but they still fail in two different ways: sometimes by confidently reporting a wrong count derived from just the retrieved sample, sometimes by correctly recognizing the sample can't answer the question and declining instead (47–53% of the time). A few real examples from the run, verbatim:

"How many pothole repair requests were made in 2022?" (ground truth: 12,996)

• ✅ query_compiler: "There were 12,996 pothole repair requests made in 2022."
• ❌ naive_rag: "Based on the 20 retrieved records provided, there are 5 pothole repair requests with an open_dt in 2022... This count only reflects the small sample of 20 records provided."
• ❌ contextual_rag: "Based on the provided sample of 20 records, 8 pothole repair requests were opened in 2022... this count does not reflect the actual total."

Both baselines correctly flag that their sample is incomplete — but answer with a specific, confidently wrong number anyway (5 and 8, both nowhere near 12,996).

"Compare sanitation complaints between Roxbury and Back Bay in 2024." (ground truth: Roxbury 3,707 / Back Bay 568)

• ✅ query_compiler: "In 2024, Roxbury had 3,707 sanitation complaints, while Back Bay had 568."
• ⚠️ naive_rag: "it is not possible to compare sanitation complaints... The retrieved sample contains zero sanitation complaints for either Roxbury or Back Bay in 2024... there are no records for Back Bay at all in this dataset."
• ⚠️ contextual_rag: "I cannot answer this question. The retrieved sample contains 14 sanitation complaints, but they are all dated 2022 or 2023 (none from 2024)... a comparison for 2024 cannot be made from the data provided."

Here both baselines correctly decline rather than guess — but only because their retrieved sample happened to contain no matching rows, not because they reasoned about the dataset's actual size.

"Which contractor filed the most building permits in 2023?" (ground truth: not queryable — no contractor field in this dataset at all)

• ✅ query_compiler: "The building_permits dataset does not include a contractor or applicant field, so the requested information cannot be retrieved."
• ✅ naive_rag: "The provided records do not contain any contractor or applicant information, and the sample of 20 permits is far too small to reliably determine which contractor filed the most permits across the full dataset."
• ✅ contextual_rag: "None of the 20 retrieved snippets list a contractor name, and the sample is far too small and incomplete to determine which contractor filed the most building permits citywide in 2023."

All three correctly abstain — this is the one category where RAG's "answer from what I can see" framing naturally converges with the right behavior.

What's not built yet

• Conflict detection (the evidence-ledger / disagreeing-sources design above) is designed but not implemented.
• The 3-way benchmark harness is built and tested, with preliminary results above from a subset run; the full 37-question run is planned as a follow-up.

Project layout

src/boston_query/
  schema.py          QueryPlan — the typed contract between planner and executor
  planner.py         LLM planner: question -> QueryPlan (Groq, JSON mode)
  sql_templates.py   QueryPlan -> parameterized SQL (per-operation templates)
  executor.py        Runs SQL / retrieval against DuckDB views over the Parquet files
  retrieval.py       Contextual RAG over building_permits' free-text comments
  synthesizer.py     Evidence -> prose, with a numeric-fidelity guard
  cli.py             `boston-query "..."` entry point
  data_prep.py       Raw CSV -> cleaned Parquet
  baseline_rag.py    Full-corpus naive + contextual RAG baselines, for evaluation only
  benchmark.py        3-way (compiler vs. two RAG baselines) evaluation harness
  groq_utils.py      Retry-with-backoff for Groq's per-minute/per-day rate limits
data/
  schema_notes.md    Confirmed real-data schema, gotchas, and cross-dataset mismatches
examples/
  questions.yaml     37 hand-verified example questions + expected plans
tests/