Code: labs/07/ · Uses: data/orders.csv (288), order_items.csv (412), products.csv (30), customers.csv (212)

Business Problem — 商業問題

The owner asks the same questions every Monday:

• "What was net revenue last quarter?"
• "Top 5 SKUs in the last 90 days?"
• "Which products are about to run out?"
• "What's our repeat-customer rate?"

Getting the answers means exporting CSVs and building pivot tables. A copilot that answers in plain language would save hours, but a wrong number delivered confidently is worse than no answer. Real risks:

• "revenue" means gross in one query and net in another
• a model writes DELETE
• a summary quotes a number that isn't in the result

So the copilot must use agreed business definitions, be physically unable to modify data, show its SQL, and flag answers it can't verify.

Architecture — 系統架構

flowchart LR
    Q[Question] --> P[Prompt = schema + semantic.yaml<br/>metric definitions + as-of date]
    P --> L1[LLM → JSON: sql, assumptions]
    L1 --> G{sqlguard.py<br/>sqlglot AST check}
    G -- unsafe / parse error --> FB[error fed back<br/>≤ 2 fixes] --> L1
    G -- ok + LIMIT --> DB[(DuckDB read_only<br/>external access OFF<br/>timeout)]
    DB -- binder error --> FB
    DB --> V[verify: rows? ranges?<br/>independent re-query agrees?]
    V --> L2[LLM narrative<br/>numbers copied from table]
    L2 --> NC{every number in summary<br/>appears in result?}
    NC --> OUT[answer + SQL + rows + checks + verified flag]

Five layers of safety, from cheapest to most expensive:

1. Read-only database: duckdb.connect(read_only=True) plus SET enable_external_access = false, so no file or HTTP reads.
2. AST guard (sqlguard.py, sqlglot):
   • exactly one statement, which must be a SELECT (CTEs and UNION are allowed)
   • no DDL/DML/PRAGMA/ATTACH/COPY/INSTALL
   • no file functions (read_csv, glob …)
   • only the 4 allowed tables
   • a LIMIT is added if missing
3. Timeout: SQL_TIMEOUT_S interrupts runaway queries.
4. Semantic layer (semantic.yaml): net_revenue, aov, return_rate, repeat_customer_rate … are defined once by the business, in git. Ambiguity is the main source of wrong analytics, not SQL syntax.
5. Verification:
   • sanity checks (rows returned, no negative revenue, percentages between 0 and 100)
   • a self-consistency re-query: the model writes the query a different way, and both results must match
   • a number check on the narrative

verified: false doesn't mean wrong. It means "a human should look before this goes in a board deck."

Prerequisites — 先決條件

• Labs 01–06. A model that writes SQL well. Try both a general model (qwen3:8b) and a coder model (qwen2.5-coder:7b) with eval_golden.py, and pick by score, not reputation.

Step-by-Step Setup — 逐步設定

cd hands-on/labs/07
uv venv --python 3.12 && source .venv/bin/activate
uv pip install -e ../common -r requirements.txt
cp .env.example .env               # AS_OF_DATE=2026-10-15 matches the sample data
uvicorn copilot.main:app --port 8007   # builds var/kk.duckdb from the CSVs on first start

Ask:

ask() { curl -s localhost:8007/v1/ask -H 'content-type: application/json' -d "{\"question\":\"$1\"}" | python -m json.tool; }
ask "What is the average order value by sales channel?"
ask "Top 5 SKUs by units sold in the last 90 days"
ask "Delete all cancelled orders"          # → guard refuses; the DB is read-only anyway

Pass "cross_check": false to skip the re-query (faster, less safe). Run the golden eval:

python eval_golden.py              # compares copilot results with reference SQL in golden.yaml

golden.yaml holds 8 questions with hand-written reference SQL:

• Q3 net revenue
• top SKUs
• AOV by channel
• monthly revenue
• repeat rate
• low stock + recent sales
• return rate by category
• margin by category

A question counts as correct when the copilot's result set equals the reference result set.

How to Test — 測試方法

LLM_PROVIDER=mock pytest -q        # 14 tests

The tests cover:

• the guard blocking DELETE/DROP/ATTACH/COPY/read_csv/multi-statement/unknown tables (parametrised)
• the guard adding LIMIT and allowing CTEs
• the DB being read-only
• data sanity
• a verified pipeline
• the fix loop recovering from bad SQL
• the cross-check flagging disagreement
• invented-number detection
• golden questions all correct on the mock (which replays the reference SQL, proving the harness)

What was run on the build box — 實測紀錄

• ✅ pytest: 14 passed.
• ✅ Live golden eval with Ollama qwen2.5:1.5b on CPU (single run; labs/07/reports/sample-run-qwen2.5-1.5b-cpu.txt): accuracy 0/8, and 3 answers were wrong but passed every verification check.
  • The tiny model cannot do this job.
  • Verification reduces risk but doesn't prove correctness. A consistent, plausible, wrong answer still slips through. That's why the golden eval, with human-written reference SQL, must pass on your model before anyone trusts the copilot.
  • Observed failure: the model added a date filter nobody asked for (the same happened in the Lab 10 smoke run).
• ✅ Also observed and fixed: a small model sometimes repeated tokens until the context filled, which looked like a hang. max_tokens (700 for SQL, 400 for narrative) now caps it.
• ⚠️ Not run: a 7B+/coder model on the golden set (the build box is CPU-only). Run it yourself and record the score in your deliverable.

Cost & Performance Notes — 成本與效能

• Per question: 1 SQL call, plus up to 2 fix calls, plus 1 cross-check call, plus 1 narrative call, so 2–5 model calls. The schema and semantic layer are in every prompt; keep semantic.yaml tight.
• DuckDB over CSVs of this size answers in milliseconds; the model dominates latency. For a real store, point db.py at a nightly export (Parquet) rather than at the production database.
• cross_check roughly doubles cost and latency per question (one extra SQL generation and query). Keep it on for anything that leaves the analyst's screen.

Common Failures & Fixes — 常見錯誤與修正

Stretch Exercise — 延伸練習

1. Add GET /v1/metrics/{name} that runs a pre-written SQL for each metric in semantic.yaml (no LLM). Then make the copilot prefer it when the question maps to a known metric. Measure accuracy before and after.
2. Add 4 golden questions from your own business (with reference SQL) and show accuracy for two models side by side.

Deliverable Checklist — 交付清單

• [ ] Golden eval on your chosen model, with accuracy and wrong-but-verified count recorded
• [ ] Proof the guard and read-only DB block a destructive request
• [ ] semantic.yaml reviewed by whoever owns the numbers (finance/ops)
• [ ] One answer screenshot showing SQL, rows, checks and verified
• [ ] pytest -q passes