Agentic BI / DataOps Copilot
Local BI copilot: schema-aware Text2SQL with three-pass SQL guardrails
A bilingual (EN/ZH) natural-language analytics platform over a retail DuckDB warehouse. Users ask in plain language; the system runs schema retrieval, Text2SQL, three-pass SQL safety validation, read-only execution, and chart recommendation. A rule-based adapter runs with zero API key, paired with a 16-stage DataOps guided React frontend. The focus is safety, explainability, and evaluability.
Verified Demo Recording 1
Copied by the portfolio quality pass from existing project demo media.
Media overview
Browse the project screenshots and recorded walkthroughs.
Project links and demo readiness
live
Live Demo
Open the verified live demo when available, or use the internal run guide for manual demo preparation.
github
View GitHub
Open the public repository when available, or review the source-access status captured by this case study.
video
Demo Recording
Open the demo recording when available, or use this section as the recording checklist for the project.
documentation
View README
Open the project README when available, or use this case study as the documentation baseline.
Project overview
## Overview Agentic BI / DataOps Copilot is a natural-language analytics platform over a retail data warehouse. Users ask questions in English or Chinese, and the system performs schema retrieval, Text2SQL generation, SQL safety validation, query execution, chart recommendation, query history, and data-quality checks. Its guiding principle is safety, explainability, and evaluability — not making the agent look flashy. ## Safety Architecture Every SQL string (including future LLM output) must clear the three-pass validator in `validator.py`: Pass 1 uses regex to block DROP/DELETE/UPDATE and 20+ dangerous keywords plus comment injection; Pass 2 parses the AST with sqlparse to reject multi-statement, non-SELECT, and comment-token injection; Pass 3 permits only whitelisted retail tables. Validated queries run on a `read_only=True` DuckDB connection with injected LIMIT and a thread timeout, forming layered defense — the LLM is never trusted directly. ## Tech Stack & Evaluability The backend uses Python 3.11, FastAPI, Pydantic v2, and in-process DuckDB (OLAP), managed with uv; SQL parsing relies on sqlparse and sqlglot. The frontend is React + TypeScript + Vite + TailwindCSS, visualized with Recharts and verified by Playwright across a 16-stage tour and feature pages. Evaluation ships benchmark YAML and metrics such as `unsafe_rejection_rate`, `valid_sql_rate`, `execution_accuracy`, and `false_positive_rate`. ## Honest Completeness The rule-based Text2SQL path, validator, query executor, evaluation harness, and frontend all run end-to-end with 20+ pytest tests; data is fully synthetic with no real PII. The OpenAI adapter is currently a stub, and authentication, rate limiting, semantic retrieval, and external benchmarks (Spider/BIRD) are tracked as production follow-ups.
My role
Full-stack developer: backend architecture, SQL safety validator, evaluation harness, and React frontend
Problem
Letting non-technical users query a warehouse in natural language is compelling, but trusting LLM-generated SQL directly risks destructive writes, injection attacks, and runaway queries — and most demos lack any evaluable, verifiable safety layer.
Solution
A schema-aware Text2SQL pipeline where every SQL string (including LLM output) passes a three-pass validator — regex blacklist, sqlparse AST analysis, and table whitelist — then executes on a read-only DuckDB connection with injected LIMIT and timeouts, backed by benchmark YAML and quantitative metrics.
Current outcome
A working end-to-end, portfolio-grade platform: zero-cost rule-based adapter, 20+ pytest tests, a 12-case benchmark (targeting 100% unsafe-query rejection), and a Playwright-verified 16-stage React guided tour.
Highlights
- Defense-in-depth: three-pass SQL validation plus read-only DuckDB
- Full demo with zero API key via rule-based Text2SQL
- 12-case benchmark with quantified safety/accuracy metrics
- Playwright-verified 16-stage guided React frontend
- Clean FastAPI REST API with interactive docs
- Bilingual EN/ZH querying over a semantic catalog (catalog.yaml)
Engineering challenges
- Blocking dangerous statements while keeping the false-positive rate low
- Keyword-scored schema retrieval trades simplicity against recall
- Sharing one validation layer across both rule-based and LLM paths
Target users
- Portfolio reviewers and interviewers
- Technical readers who need a quick view of purpose, stack, and maturity
Technical highlights
- Detected technical signals: Python 3.11, FastAPI, DuckDB, Pydantic v2, sqlparse / sqlglot, React + TypeScript, Vite, Tailwind CSS, Recharts, Playwright
- README evidence exists and can support a fuller reviewed case study
- A public GitHub repository is linked for source traceability
Architecture
This case study is generated from the portfolio catalog pipeline using README, Git metadata, package/build configuration, and media signals. The final architecture narrative still needs source-level review. Current detected technology signals include: Python 3.11, FastAPI, DuckDB, Pydantic v2, sqlparse / sqlglot, React + TypeScript, Vite, Tailwind CSS, Recharts, Playwright.
Data flow
A public data-flow narrative is not fully reviewed yet. If the project includes data processing, AI pipelines, or backend APIs, the next pass should document input, processing, storage, and UI/output flow end to end.
Project structure
agentic-bi-dataops-copilot/ README.md # project documentation, when available source files # implementation reviewed by local audit package/build config # detected capability signals
Setup / Run guide
This project does not expose a verified runnable web command yet. Review the README/source tree and add exact install, run, test, and build commands before interview use. No verified build command was detected. Treat the current portfolio page as a case-study placeholder until build steps are reviewed.
Future improvements
- Complete the production-quality README, screenshots, and demo recording
- Add architecture diagrams, data-flow notes, and key technical decisions
- Verify build/test status and update the portfolio release report
Interview notes
- State the current maturity and demonstrable scope first
- Focus on verified stack, source structure, and completed behavior
- Do not claim unverified deployment, video, or test coverage as finished
Next steps
- Complete the OpenAI adapter (currently a stub) with real API calls
- Replace keyword scoring with sentence-transformer semantic retrieval
- Add JWT/OAuth auth and rate limiting for production readiness