The head of studies, quality assurance or a tutor ask in natural language and get a summary, the table and the SQL that ran. Four independent layers guarantee read-only: nobody modifies or deletes data.
Scholarium lets any teacher query the school's academic data in natural language and get, without going through a technician, a one-sentence summary, the table and the SQL that ran.
The goal is to give that agility without an AI being able to touch the database. A Medallion pipeline with Apache Airflow and dbt builds a star schema in PostgreSQL, and a FastAPI gateway translates the question into SQL, validates it across four layers and runs it with read-only permissions. Quality is measured in CI with a golden set, not promised.
Querying the school's data always runs into the same barriers, and blindly handing the problem to an AI introduces new risks. Each one is a problem our solution takes care of.
The grades come out of the management system as an XML that only a technician knows how to query; every question goes through a middleman.
Letting a model write SQL against the database opens the door to deleting, leaking or escalating beyond a read query.
It invents columns and literals ('Superior' instead of 'GRADO SUPERIOR') and returns a plausible but false result labelled as correct.
Every LLM call spends tokens and seconds; a rewording of something already answered would pay twice.
The data comes in as XML and ends up as a natural-language answer. In between, decoupled layers: Airflow orchestrates by events (each DAG starts when the previous one publishes its asset, with no chained cron), a single PostgreSQL serves everyone but with a different role per consumer, and the AI gateway is isolated behind the backend. Changing one piece doesn't force you to touch the others.
Three Airflow DAGs chained by events (each starts when the previous one publishes its asset, not by cron) take the data from raw XML to a star schema. The DAGs only orchestrate; the logic lives in separate modules, tested on their own.
Ingestion (bronze): validates the academic XML and drops it by year into an S3-compatible data lake (MinIO).
Transformation (silver): cleans, anonymises the NIA with SHA-256 and flattens the academic hierarchy to typed Parquet.
Load (gold): loads to staging, dbt build builds the star and tests it with 15 data tests, and exports the marts.
Refresh: notifies the AI gateway, which reloads its schema and invalidates the cache.
dbt materialises staging as views (typing and renaming only) and the marts as tables. The star schema looks like this:
The consumer isn't an analyst: they don't read SQL. Over the already-executed result, the model writes a one-sentence summary that names the highest and lowest value. If the shape of the data allows it (one category and one metric), a chart also appears, chosen by a heuristic, not by the model.
The conversation has memory: after "average grade by field?", the question "and in intermediate level?" is rewritten into a standalone question before touching the cache and the guardrails. While the model works, the frontend receives via streaming (SSE) which phase the query is in and paints it as it happens.
A destructive query would have to get through all four. The generated SQL never reaches the database raw.
The model refuses to generate SQL that isn't read-only.
Allowlist over the AST: a single statement, SELECT only, the analysis schema only, no system functions, with an enforced LIMIT.
An EXPLAIN against the warehouse first, before running anything.
The executing user is read-only at the role level (it can't be disabled per query), with a 15 s statement_timeout, and only sees the three analysis tables.
When the guardrail blocks a query, a webhook fires an alert to n8n that distributes it to Slack and email with the forensic context: the question, the rejected SQL and the reason.
A golden set of 32 verified questions across 6 categories runs in CI. The metric is execution accuracy: the results of running the generated SQL and the reference SQL are compared, not the text strings (two correct queries can be written a thousand ways).
The jump from the baseline came from a per-category diagnosis: the failures were concentrated in the filters, where the model invented literals. Enumerating the real values of the categorical columns in the schema fixed it, and accuracy went from 83.3% to 100%. Re-running the golden set after changing a dimension's grain also uncovered a live bug in production (a JOIN that multiplied rows) that no unit test saw. The evals are the net that caught it.
The provider isn't deterministic, so the 100% is measured over the golden set: when a run fails, the SQL noise is separated from a real bug.
Questions are indexed as vectors in pgvector and searched by cosine distance (HNSW). A rewording that's already been answered is served in ~15 ms and with no tokens.
Similarity alone is dangerous: swapping "2024" for "2023" gives 0.976, the same as a legitimate rewording. No threshold separates them, and the cache would serve the SQL for the wrong year labelled as correct. That's why the year doesn't travel in the embedding: it goes into the key with an exact match, just like the literals the question names.
Two Grafana dashboards, provisioned as code: the gateway one (cache hit rate, P50/P95 latency by origin, cost and tokens, blocked queries) and the pipeline one (failed DAGs, durations, pool usage). Prometheus collects every metric, including Airflow's.
The real cost of each query, the cache's performance and any spike in errors or blocks are visible at a glance, not guessed.
The same star schema feeds an executive dashboard in Power BI with a read-only role that only sees the analysis schema. Four pages (overview, vocational field, modules and student profile) answer, without Excel, the questions that used to need a manual export and a spreadsheet.
The rigour is in the details: a zero isn't a fail, it's a "did not sit" (24% of the grades). And the school has 1,500 students, not 1,613: the difference is the distance between COUNT (enrolments, with repeaters counted twice) and DISTINCTCOUNT(nia) (unique people). Measuring badly is deciding badly.
Each obstacle from the start, solved end to end.
