Enterprise Text-to-SQL Analytics

Overview

Operational teams answer dozens of routine questions per week — throughput by line, batch genealogy, deviation counts. Each answer lives in one of 30+ manufacturing databases, and asking for it means an engineer, a SQL editor, and a queue. This platform lets users ask questions in natural language and returns validated, guarded, explainable SQL analytics with a human-readable summary of the results.

The engineering problem

Naive text-to-SQL is demo-grade: point an LLM at a schema, hope for the best. Across 30+ real databases — heterogeneous naming, site-specific semantics, duplicated column names — that approach produces confidently wrong answers, which in manufacturing are worse than

Architecture

<rect x="260" y="20" width="180" height="60" style="fill:var(--paper);stroke:var(--red)"></rect>
<text x="350" y="42" text-anchor="middle" style="fill:var(--red)">Semantic Discovery</text>
<text x="350" y="58" text-anchor="middle" style="fill:var(--ink)">vector match → database,</text>
<text x="350" y="74" text-anchor="middle" style="fill:var(--ink)">schema, business terms</text>

<rect x="520" y="20" width="240" height="60" style="fill:var(--paper);stroke:var(--ink)"></rect>
<text x="640" y="42" text-anchor="middle" style="fill:var(--ink)">SQL Generation</text>
<text x="640" y="58" text-anchor="middle" style="fill:var(--ink)">grounded schema + examples</text>
<text x="640" y="74" text-anchor="middle" style="fill:var(--dim)">LLM with strict output schema</text>

<rect x="520" y="110" width="240" height="60" style="fill:var(--paper);stroke:var(--ink)"></rect>
<text x="640" y="132" text-anchor="middle" style="fill:var(--ink)">Validation &amp; Guardrails</text>
<text x="640" y="148" text-anchor="middle" style="fill:var(--ink)">parse · explain · restrict</text>
<text x="640" y="164" text-anchor="middle" style="fill:var(--dim)">read-only, bounded, audited</text>

<rect x="260" y="110" width="180" height="60" style="fill:var(--paper);stroke:var(--ink)"></rect>
<text x="350" y="132" text-anchor="middle" style="fill:var(--ink)">Execution</text>
<text x="350" y="148" text-anchor="middle" style="fill:var(--dim)">30+ manufacturing DBs</text>
<text x="350" y="164" text-anchor="middle" style="fill:var(--dim)">timeouts · row limits</text>

<rect x="20" y="110" width="160" height="60" style="fill:var(--paper);stroke:var(--ink)"></rect>
<text x="100" y="132" text-anchor="middle" style="fill:var(--ink)">Summarization</text>
<text x="100" y="148" text-anchor="middle" style="fill:var(--dim)">LLM summary of results,</text>
<text x="100" y="164" text-anchor="middle" style="fill:var(--dim)">cited to executed SQL</text>

<rect x="260" y="200" width="240" height="50" style="fill:var(--paper);stroke:var(--ink)"></rect>
<text x="380" y="230" text-anchor="middle" style="fill:var(--ink)">Evaluation Harness — correctness test sets</text>

<path d="M180,68 L260,50" style="stroke:var(--ink);fill:none;marker-end:url(#ah5)"></path>
<path d="M440,50 L520,50" style="stroke:var(--ink);fill:none;marker-end:url(#ah5)"></path>
<path d="M640,80 L640,110" style="stroke:var(--red);fill:none;marker-end:url(#ah5)"></path>
<path d="M520,140 L440,140" style="stroke:var(--ink);fill:none;marker-end:url(#ah5)"></path>
<path d="M260,140 L180,140" style="stroke:var(--ink);fill:none;marker-end:url(#ah5)"></path>
<path d="M380,170 L380,200" style="stroke:var(--ink);fill:none;marker-end:url(#ah5)"></path>

Technical design

• Semantic discovery — questions are matched (vector search) against a curated semantic layer describing each database: what it contains, how business terms map to columns, which site owns it. Discovery, not generation, is where correctness is won or lost.
• Grounded generation — the LLM sees only the discovered schema fragment plus verified example queries, with strict structured output.
• Guardrails before execution — generated SQL is parsed, plan-checked, restricted to read-only operations, bounded by row limits and timeouts, and audited. Invalid or suspicious SQL never touches a database.
• Honest summarization — the summary stage describes executed results only; it cannot invent numbers, and it links back to the SQL for full transparency.
• Evaluation harness — curated question/answer sets per domain gate every prompt or model change.

no answers. The system must know which database, which semantics, and refuse what it cannot ground.

My role

Key engineering decisions

• Why a semantic layer instead of raw schemas? Column names do not carry business meaning (“LOT vs BATCH vs ORDER”). Translating business language → schema is the actual problem; a curated semantic layer makes that translation explicit and testable.
• Why guardrails beat trust? A wrong query on manufacturing data is an operational incident. Read-only, bounded, audited execution is what made the system deployable.
• Why refuse instead of guess? When discovery confidence is low, the system says so and suggests the closest databases. Reliability is the product.

Challenges & solutions

• Ambiguous questions — “deviations last month” means different things per site. Solved with clarification prompts when discovery is ambiguous, using site context.
• Query cost explosions — bounded execution plus plan inspection stopped pathological queries from impacting shared databases.

Results

• Natural-language analytics across 30+ enterprise manufacturing databases.
• Routine data requests moved from engineering tickets to self-service.
• Zero correctness incidents traced to executed SQL after the guardrail layer shipped.

What I learned

Text-to-SQL is an information-retrieval problem wearing an LLM costume. The generation step is nearly trivial once discovery, semantics, and guardrails are right.

What I would change today

I would expose the semantic layer as MCP tools (as later done in SIRIUS MCP) from the start — the two systems converged on the same pattern, and one shared surface would have saved duplicated work.

Technologies

Python · LangGraph · Azure OpenAI · PostgreSQL · pgVector · Semantic Retrieval · FastAPI

I designed the semantic discovery layer, the SQL generation and validation pipeline, the execution guardrails, and the summarization stage — plus the evaluation harness that measured answer correctness before anything shipped.