QueryMind AI

Natural language analytics over an e-commerce database. Type a question in plain English, get back a table, a natural-language summary, and (optionally) the SQL that produced it — no SQL required.

Built for Assignment 3 (Text-to-SQL Analytics Engine with Admin UI). Full spec: SPEC.md. Design/planning doc: PRD.md. Evaluation results: EVALUATION_REPORT.md.

Domain choice: e-commerce

The assignment left the business domain open. E-commerce was chosen because it gives natural, high-signal examples of every required schema property without contrivance:

• 8 tables with real relational structure — a many-to-many join (orders ↔ products via order_items) and a self-referencing FK (categories.parent_category_id for nested categories).
• A genuine ambiguity case: "revenue" plausibly means orders.total_amount (includes tax + shipping) or SUM(order_items.quantity * unit_price_at_purchase) (pure product revenue, excludes tax/shipping) — these differ on every order in the seed data, so the Schema Linker's ambiguity detection has something real to catch.
• Natural aggregate and temporal patterns — revenue/order-count/rating aggregates, and two independent temporal fields (orders.order_date for placement-date questions, shipments.shipped_date/delivered_date for delivery-time arithmetic).

Full schema + column dictionary: backend/data/data_dictionary.md.

Architecture

NL Query → Query Classifier → Schema Linker → Examples Retriever → SQL Generator
         → SQL Validator → SQL Executor → Result Formatter

• Backend: FastAPI + SQLite, Claude Sonnet 5 for SQL generation, ChromaDB (local embedding model, no API key needed) for few-shot example retrieval.
• Frontend: Next.js — a query chat page plus 5 Admin panels (Schema Manager, Examples Manager, Query Logs, Guardrails Config, Model Config), all backed by the real API (no mock data).
• Guardrails: destructive SQL (DELETE/UPDATE/DROP/INSERT/TRUNCATE/ALTER) is blocked at the Query Classifier stage by default, with the SQL Validator and a read-only DB connection as defense-in-depth layers behind it.

See PRD.md for the full component-by-component design and the 8-table schema rationale.

Setup

Prerequisites

• Python 3.10+
• Node.js 18+ (developed against Next.js 16 / Node 24)
• An Anthropic API key (console.anthropic.com)

Backend

cd backend
python -m venv .venv
./.venv/Scripts/python.exe -m pip install -r requirements.txt   # Windows
# source .venv/bin/activate && pip install -r requirements.txt  # macOS/Linux

cp .env.example .env
# edit .env and set ANTHROPIC_API_KEY=sk-ant-...

./.venv/Scripts/python.exe -m app.db.seed_db          # creates + seeds data/querymind.sqlite3
./.venv/Scripts/python.exe -m app.vectorstore.build_index   # indexes few-shot examples into Chroma

./.venv/Scripts/python.exe -m uvicorn app.main:app --port 8000

Backend is now live at http://localhost:8000 (interactive API docs at /docs).

Port note: if 8000 is already in use on your machine (Docker Desktop, WSL, or another dev server commonly claim it), use --port 8010 instead and update frontend/.env.local's NEXT_PUBLIC_API_BASE_URL to match.

Frontend

cd frontend
npm install
cp .env.local.example .env.local   # defaults to http://localhost:8000 — edit if you used another port
npm run dev

Frontend is now live at http://localhost:3000 — the Query Chat page, with Admin panels under /admin/schema, /admin/examples, /admin/logs, /admin/guardrails, /admin/config.

Running the test suite

cd backend
./.venv/Scripts/python.exe -m pytest tests/ -v

74 tests: unit tests for all 7 pipeline components (mocked LLM calls, no API key needed), live integration tests against the real Claude API (skipped automatically if ANTHROPIC_API_KEY is unset), and FastAPI route tests against a temp DB copy (never touches your real data).

Running the evaluation harness

cd backend
./.venv/Scripts/python.exe eval_harness.py

Runs the 25-query eval set + 8 adversarial guardrails probes through the live pipeline and writes data/eval_report.json. See EVALUATION_REPORT.md for the latest results (96% Execution Accuracy, 100% Guardrails Compliance).

Sample queries

Try these in the Query Chat (or POST /query):

Question	Type
"What are the top 5 products by revenue?"	Aggregate
"How many orders were delivered in the last 90 days?"	Temporal
"Which country do most of our customers come from?"	Aggregate
"List the product name, category name, and unit price for all products in the Laptops category"	Join
"What is the total revenue by category?"	Aggregate (product revenue)
"What is the total revenue including tax and shipping?"	Aggregate (order-level total — contrast with the above)
"Delete all orders from last year"	Blocked — destructive request, returns a helpful refusal instead of SQL

The last two rows are deliberately paired: they demonstrate the schema's built-in "revenue" ambiguity (order total vs. product revenue) resolving to two different, both-correct answers depending on phrasing.

Known limitations

• Only Anthropic + SQLite are fully wired end-to-end. The Model Config panel lets you select OpenAI/Ollama as a provider and PostgreSQL/MySQL as a dialect, and those choices persist to the database, but only the Anthropic Claude + SQLite path actually executes — selecting another provider will not currently generate SQL. Multi-dialect transpilation (a bonus feature) is not implemented.
• Result Formatter's NL summary is a deterministic template, not LLM-generated. It handles scalar results, single rows, and 2-column ranked lists well; anything wider falls back to "The query returned N rows." This keeps the component fast and free of an extra API call, at some cost to summary quality on wide result sets.
• Exact Match Rate (16%) is expected to read low — it's a strict AST-level comparison (sqlglot.diff), so a query that returns identical data but with a different alias, table-alias style, or the auto-appended LIMIT 100 guardrail counts as a miss. Execution Accuracy (96%) is the metric that reflects real correctness; see EVALUATION_REPORT.md for the full explanation.
• The SQL Validator's schema check skips unqualified columns in multi-table queries (e.g. a bare status column when both orders and payments are joined) — resolving that fully would require star-expansion logic. Qualified references (o.status) are always checked.
• No authentication. Single-admin assumption throughout — anyone with network access to the backend can read/write all Admin panels. Fine for this assignment's scope; would need an auth layer before any real deployment.
• Bonus challenges not implemented: multi-dialect transpile, ambiguity clarification prompts in the chat UI, query explanation mode toggle, chart auto-generation, semantic caching, voice input. The one self-healing behavior that is implemented is the SQL Generator's single retry with the validator's error injected back into the prompt (required by the base spec, not the bonus list).
• Windows dev note: ports 3000/8000 may already be bound by Docker Desktop/WSL on some machines — see the port note under Setup above.

Project structure

QueryMind AI/
├── README.md                # this file
├── PRD.md                   # design/planning doc
├── SPEC.md                  # assignment spec
├── EVALUATION_REPORT.md      # eval harness results
├── backend/
│   ├── app/
│   │   ├── main.py           # FastAPI app + route registration
│   │   ├── config.py         # Settings (env vars, DB path, defaults)
│   │   ├── db/                # schema.sql, seed_db.py, SchemaRepository, AdminRepository
│   │   ├── pipeline/           # all 7 components + orchestrator.py
│   │   ├── admin/              # /admin/* FastAPI routers
│   │   ├── routes/             # /query FastAPI router
│   │   ├── models/              # Pydantic request/response schemas
│   │   ├── vectorstore/          # ChromaDB store + indexing
│   │   └── eval_scoring.py        # execution_match / exact_match (used by eval_harness.py)
│   ├── data/
│   │   ├── querymind.sqlite3      # seeded DB (business + _admin_* tables)
│   │   ├── data_dictionary.md     # full column-level schema docs
│   │   ├── few_shot_examples.json # 33 curated (question → SQL) pairs
│   │   ├── eval_set.json          # 25-query ground-truth test set
│   │   └── eval_report.json       # latest eval_harness.py run output
│   ├── eval_harness.py
│   └── tests/                     # 74 tests (pytest)
└── frontend/
    └── src/
        ├── app/                   # Query Chat + 5 Admin panel pages (Next.js App Router)
        ├── components/DataTable.tsx
        └── lib/api.ts              # typed API client