§ case
Text-to-SQL Analytics Agent
Sep 2026 — present
AI · Data · Backend · Senior Software Developer · Architect
- —An internal text-to-SQL analytics agent on Mastra + Gemini that answers business questions — in English or Hindi — with one validated, read-only query against a production MySQL replica, streamed over SSE and published to other apps as an MCP server.
- —A paced schema-sync pipeline builds a deterministic, role-scoped knowledge base — schema, routines, derived joins, curated notes and verified business measures — rendered per role with hidden tables and columns removed.
- —Context engineering: the agent explores that knowledge base through tool calling, steered by an IDF-weighted router, so the system prompt names no table and stays at ~5k tokens as the schema grows (the predecessor sent a 70–88k-token catalog on every call).
- —Verified SQL measures and curated stored-procedure calls cut benchmark tokens 76% (874k → 205k), cost 63% and wrong answers 2 → 0; explicit Gemini context caching cut measured input cost 46–62%.
- —Safety in depth: a static SQL validator, always-rolled-back transactions, server timeouts with a KILL QUERY backstop on the agent's own connections, RBAC with hidden-column guards, and answers rendered without the model ever seeing result rows.
- —Every question is traced in Langfuse with per-step token and cost accounting, conversations persist in PostgreSQL, and the module is backed by 650 Vitest tests.
Mastra AIGoogle GeminiVercel AI SDKMCPMySQLPostgreSQLLangfuseZodVitestTypeScriptNode.jsExpress.js
◼ Challenges
- →Answer open business questions over a large production schema on a cheap model, without sending the whole schema on every call
- →Match the dashboards' own numbers instead of plausible paraphrases of their SQL
- →Query a shared production replica safely — read-only, time-limited, stoppable and role-restricted — with no personal data reaching the model
◼ Solutions
- →A role-scoped knowledge base explored via tool calling and an IDF-weighted router — the prompt stays at ~5k tokens (was 70–88k) and benchmark tokens fell 76%
- →Verified SQL measures and ready-to-run stored-procedure calls — wrong answers 2 → 0 on a fixed benchmark
- →Six SQL safety layers (validator, rolled-back transactions, timeouts, KILL QUERY backstop, row cap, hidden-column guard) and a presenter that fills answers from rows the model never sees