01 / TEXT-TO-SQL AGENT
A text-to-SQL agent that reasons over a role-scoped knowledge base instead of a schema dump.
A Mastra agent on Gemini that turns business questions into one validated, read-only query against the production replica — context-engineered so the prompt stays at ~5k tokens as the schema grows, and exposed to other apps over MCP.
Dashboard numbers live in complex stored procedures, so every new business question needed an engineer to write SQL. The predecessor sent the entire schema catalog (70–88k tokens) on every call, re-sent it on every repair, and still paraphrased the dashboards' logic instead of matching it. The replica is production and shared, so every query must be read-only, time-limited and stoppable; access differs by team; personal data must never reach the model; and it all had to run on a cheap model.
An offline pipeline syncs the replica's schema, routines, previews and column values at a paced, production-safe rate, then deterministically builds a knowledge base: a page per table and routine, derived joins, curated notes and verified business measures. Each role gets its own rendered view with hidden tables and columns removed. A Mastra agent on Gemini explores that view through tool calling (search, read, join-path discovery, column values). The system prompt names no table, so it stays fixed as the schema grows, and an IDF-weighted router places hints under the question so the prompt cache stays stable. The agent submits one query or a curated stored-procedure call; a static validator, rolled-back transactions, server timeouts and a KILL QUERY backstop on the agent's own connection guard the replica, and a presenter fills a labelled answer from real rows the model never sees. Answers stream over SSE, conversations persist in PostgreSQL, every question is traced in Langfuse, and the same agent is published to other apps as a stateless MCP server.
- Tools over a knowledge base instead of the whole schema in the prompt — a ~5k-token prompt vs. a 70–88k catalog, so a repair costs one small turn, not another full call
- Verified SQL measures and ready-to-run stored-procedure calls instead of letting the model paraphrase procedure code — answers match the dashboards' own numbers
- The model never sees result rows — it names columns and a headline template and the server fills them, keeping personal data and tokens out of the model
- KILL QUERY only on the agent's own verified connections, because the database account is shared across apps
On a fixed 8-question benchmark, tokens fell 874k → 205k (−76%), cost fell 63%, agent steps 40 → 16 and wrong answers 2 → 0; explicit Gemini context caching cut measured input cost 46–62% — backed by 650 tests.