Israaelhouch/bac-scope

FastAPI + SQLite API over Tunisian Baccalaureate results — filters, statistics, chart specs, and Arabic natural-language → SQL with schema linking, read-only guardrails, and an execution-accuracy eval suite. # bac-scope A backend API to query and analyze **Tunisian Baccalaureate results** — filters, statistics, ready-to-render charts, and an Arabic **natural-language → SQL** endpoint. Built with FastAPI + SQLite, API-first so any frontend can sit on top. A demo web UI ships at `/` so you can try everything without writing a client. **Status: complete.** All six build phases are done — data layer, core REST, stats + charts, CSV upload, the `/ask` AI layer, and multi-format ingestion. 37 tests pass and the `/ask` eval suite scores 9/9. See `SOLUTION.md` for the design decisions and trade-offs behind it. ### What's interesting here - **Arabic text-to-SQL** with schema linking — the prompt carries the live schema, a dialect glossary (`mou3adel` → `total`), real categorical values, and worked examples. Guarded by SELECT-only validation *and* a read-only connection, so the model can never write. - **Measured, not assumed** — `/ask` is scored by *execution accuracy* against gold SQL, which caught a correlated subquery that looked right and wasn't. - **Schema-free subject storage** — long-format grades absorb any new stream or subject without a migration. - **Two CSV formats auto-detected**, with tatweel/diacritic normalization. --- ## Run it locally From inside the `bac-scope/` folder: ```bash # 1. (once) create a virtual environment and install dependencies python3 -m venv .venv source .venv/bin/activate # Windows: .venv\Scripts\activate pip install -r requirements.txt # 2. (once) build the database from the CSVs in data/sample/ python -m scripts.seed # 3. start the API uvicorn app.main:app --reload ``` Or with the Makefile: `make install && make seed && make run`. The repo ships an **anonymized sample dataset** (145 students across 7 streams), so it runs out of the box — no data files to hunt down. Real student data is never committed. Then open: - **127.0.0.1 — the **web UI**: tabs for filtering/search, statistics (charts), natur …