Wherehouse

An LLM agent that answers "where is this data?" over a SQL warehouse — locating tables and columns, explaining joins, and running the query safely if you ask.

Overview

Every data team has the same problem: nobody remembers which of 200 tables holds what, or what ss_ext_sales_price actually means. This project is a chatbot you can ask "where do I find sales by store and month?" and get back the right tables, the right columns, and the join path between them — with the option to just run the query and show you the answer. It's also a hands-on project in LLM tool use, retrieval, and letting a model touch a real database without letting it break anything.

Example

You:    Where do I find sales by store and month?
Agent:  Use store_sales (fact table, 2.9M rows) joined to:
        - date_dim on ss_sold_date_sk = d_date_sk  (gives month: d_moy, d_year)
        - store on ss_store_sk = s_store_sk         (gives store name: s_store_name)
        Amount columns: ss_net_paid (after discounts), ss_ext_sales_price (before).
        Confidence: high.  [Show sample rows]  [Run this as a query]

How it works

1. Catalog builder — profiles every table and column: row counts, null rates, sample values, and inferred relationships (foreign keys are rarely declared, so joins are inferred from naming patterns and confirmed by containment checks).
2. Descriptions — an LLM writes a plain-English description of each table and column; a glossary file lets you override or clarify terms ("revenue" → ss_net_paid).
3. Hybrid search — embeddings plus keyword (BM25) search over the catalog, reranked by the LLM, so both vague and exact-name questions work.
4. Agent loop — the model calls tools (search_catalog, describe_table, sample_rows, run_sql) to answer, validating every table/column name it mentions against the real catalog before showing it.
5. SQL guardrails — single-statement SELECT only, table allowlist, row limit, timeout, read-only connection. The agent can answer questions; it can't change or exfiltrate data.

  Database
     |
     v
  Catalog builder (schema, stats, samples)
     |
     v
  LLM descriptions + glossary
     |
     v
  Search index (embeddings + keywords)
     ^
     |
  Agent loop (Claude tool use)  <----  Chat UI / MCP client
     |
     v
  SQL guard (parse + validate)  ---->  back to Database

Tech stack

Python · FastAPI · DuckDB (or Postgres + pgvector) · sqlglot · Claude API (tool use) · sentence-transformers + BM25 · Next.js (chat UI) · MCP Python SDK · pytest · Docker

Interfaces

• Web chat — streaming chat UI, shows the SQL it ran alongside the answer
• CLI — same agent, terminal-first
• MCP server — exposes find_tables, describe_table, and run_query, so Claude Desktop (or any MCP client) can use it directly

Data

Demo data is public and synthetic: DuckDB's generated TPC-DS sample warehouse (24 tables, intentionally cryptic column names) plus one real, very wide public dataset (NYC Open Data's building energy disclosure table) to test locating one field among many.

Safety

• Every identifier the model returns is validated against the real catalog before display — invented tables/columns are caught, not shown.
• SQL is parsed (not just pattern-matched) and restricted to a single read-only SELECT against an allowlist of tables, with a row limit and timeout.
• Data read from table values is treated as untrusted text, never as instructions — tested against a dedicated prompt-injection suite.
• Likely-sensitive columns (emails, names) are flagged during profiling and masked in sample output.

Getting started

git clone https://github.com/<your-username>/db-agent.git
cd db-agent
docker compose up

# or, for local development
pip install -r requirements.txt
python -m dbagent.catalog build --db tpcds.duckdb
python -m dbagent.serve

Evaluation

A held-out set of 60–100 questions (locate / join / answer) and a separate attack suite (write attempts, injection strings, out-of-scope requests) are used to score the agent: top-k retrieval recall, exact join-path match, execution accuracy, and — critically — a 100% block rate on every attack test.