# HOW TO BUILD DeTLeng ProofSQL
### Three repositories. One intelligent analytics platform. One stubborn question: “Who verifies the AI?”

**Engineering case study | Updated 14 November 2026**  
**Project owner and implementation lead:** Muhammad Naveed Ishaque, DeTLeng  
**Build approach:** Human-led, AI-assisted; independently tested in production  
**Live:** https://casestudy.detleng.com/ai-tasks.html · **Code:** https://github.com/Navid-Ishaq/detleng-case-studies

> **The story in one line:** AI helped write and debug the system; the human behind DeTLeng kept asking harder questions, running the tests, checking the live results, and refusing to call it finished too early.

## 01 · START — Unify the moving parts

The project began with **three repositories**: the case-study website, a FastAPI/OpenAI chatbot backend, and BigQuery MCP analytics tools. The engineering goal was a single maintainable codebase—not three services to babysit. The components were consolidated into **`Navid-Ishaq/detleng-case-studies`**, while keeping the website, approved SQL analytics, chatbot behavior and deployments intact. The unified backend now serves **100 AI Task cards** from the processed **`cs003_olist_analytics`** BigQuery layer.

## 02 · THE NEW REQUIREMENT — The cards demand proof

All 100 cards were answering questions and looking very pleased with themselves. Then came the owner's inconvenient question: **“A polished AI explanation is fine—but can a visitor independently reproduce the numbers?”**

The answer became **DeTLeng ProofSQL**: a **Verify with SQL** button below a successful AI answer, opening a popup that keeps the original response and independently calculates the relevant KPIs in the visitor's browser.

**Two separate paths:**

- **Live answer:** Task card → allowlisted FastAPI tool → BigQuery → structured result → OpenAI explanation.
- **Independent check:** Verify button → versioned CSV snapshot + SHA-256 → DuckDB-Wasm SQL in browser → JavaScript comparison → MATCH / MISMATCH / PARTIAL / NOT VERIFIABLE.

The verification click does **not** need another BigQuery query or OpenAI call. Matching numbers are evidence of reproducibility, **not** proof that every AI interpretation is correct.

## 03 · BUILD — From proof of concept to 100 queries

1. **Export and protect data.** Download processed analytics tables to a private D: drive. Review columns and redistribute only approved, reduced snapshots—never service-account keys. Keep source attribution and licensing conditions visible.
2. **Prove the concept.** Prepare four compact CSVs; validate five KPIs (revenue, orders, products, average order value, on-time delivery rate). The first live browser check matched **15,843,553.24** in both BigQuery and DuckDB-Wasm.
3. **Build the browser engine.** Use allowlisted SQL, integrity-checked CSVs, a responsive popup, original-answer preservation, numerical tolerances, and visible provenance.
4. **Expand carefully.** Produce **100 mapped SQL definitions** and a **nine-table full-v2 static snapshot**. Add structured backend evidence for scalar and multirow responses. Run Python/JavaScript regression checks and SQL validation before release.
5. **Deploy with evidence.** Stage reviewed Git changes, commit normally, push to GitHub; confirm GitHub Pages and Render separately. Test the real path in a browser—not just the automated tests.

**Key files:** `backend/analytics/tools/*.py` · `backend/proofsql_metadata.py` · `chat-widget.js` · `proofsql/engine/` · `proofsql/queries/full-v2/` · `proofsql/data/public/full-v2/manifest.json`.

**Reproducible release checks:**
```powershell
$env:PYTHONPATH = "backend"
python -m pytest -q backend/tests
node --test proofsql/tests/*.test.mjs
node --check chat-widget.js
```


## 04 · THE INCIDENT — One condition blocked the queue

Just before the employer-facing demonstration, the owner tested beyond the initial pilot. Many tasks produced answers **without the Verify button**. The code and data were present; the **frontend gate** was wrong. It demanded the original pilot's `verification.metrics`, while the 100-task backend also returned `verification.records`.

**Before:**
```javascript
if (evidence && evidence.tool && evidence.metrics && (!tool || evidence.tool === tool)) {
```
**Targeted patch:**
```javascript
if (evidence && evidence.tool &&
    (evidence.metrics || Array.isArray(evidence.records)) &&
    (!tool || evidence.tool === tool)) {
```

This was a **compatibility fix**, not a rebuild: one reviewed change to `chat-widget.js`, followed by syntax checks, automated tests, Git diff inspection, a normal commit/push, and production retesting. Allowlisting and downstream comparison safeguards remained in place.

## 05 · FINAL EVIDENCE — What was actually observed

| Live check | Evidence seen | Outcome |
|---|---|---|
| Original revenue pilot | BigQuery 15,843,553.24 vs browser SQL 15,843,553.24; integrity/provenance shown | **MATCH** |
| Task #92 — Month Over Month Growth | Browser SQL executed; SHA-256 verified; 24 live rows, 24 browser rows and 120 numeric comparisons | **MATCH** |
| Task #96 — Yearly Revenue | Browser SQL completed; SHA-256 checked; six numeric comparisons | **MATCH** |
| Task #100 — Top Products | Browser SQL completed; ten numeric comparisons | **MATCH** |

**Status on 14 November 2026:** The missing-button condition was patched; late-numbered tasks visibly reached the full verifier. **This is not a claim that all 100 tasks have individually passed live verification.** A complete task-by-task acceptance matrix, mobile/performance checks and snapshot refresh controls remain good follow-up work.

## 06 · WHO DID THE ENGINEERING?

**Human project lead:** Defined the architecture and success criteria; managed the original repos, BigQuery exports and local PowerShell/Git workflow; insisted on independent proof; personally tested production; found the pre-presentation defect; and controlled each deployment decision.

**AI engineering assistant:** Helped design the verification path, generate and refine implementation code, propose patches and automated tests, inspect failure conditions, and document the build. Suggestions were **reviewed, executed and accepted by the human lead**—not autonomously shipped by an AI.

**Employer takeaway:** This is evidence of **requirements ownership, system integration, data engineering, verification design, iterative QA, incident diagnosis, and disciplined human–AI collaboration**. The most important skill on display was not prompting an AI to write code; it was knowing **what to question, how to test it, and when not to declare victory**.

**Engineering principle:** *100 SQL mappings ≠ 100 verified user journeys.* Build → test → challenge → patch → verify → document.