Skip to content
All work

Case study

Conversational Analytics Agent

Text-to-SQL agent turning plain-English operational questions into validated, read-only queries against a live database.

Role
Sole developer
Domain
Generative AI & LLMs · Backend & APIs · Machine Learning & Data Science
Stack
Python · Flask · Google Gemini · Groq / Llama 3 · LangChain
Result
343 learned query patterns
Still: a plain-English question becoming a validated read-only SQL query, executed and explained back in business language.

Overview

An agent that lets managers at a transport operator ask questions of their operational database in plain English. It writes the SQL, proves the SQL is read-only before running it, and explains the answer in business terms.

The problem

Every question about fuel, routes or budgets needed someone who could write SQL. Managers waited on engineers for answers that were already in the database.

From question to answer

  1. Find the tables

    The question is matched against a hand-annotated description of the schema to pick what is relevant.

  2. Write the query

    A model generates SQL, grounded in the business's own rules for fuel, mileage and budgets.

  3. Validate

    The query is parsed and checked before it can touch the database — see below.

  4. Run and explain

    Results are returned with a one- or two-line explanation in plain language.

  5. Remember

    Successful question-and-query pairs are kept and offered back as examples for similar questions.

What the validator refuses

Running model-written SQL against production data is only acceptable if the model cannot do damage. The check is in code, not in the prompt.

The model might writeAn UPDATE, DELETE or DROP.
The validatorRejected. Only SELECT reaches the database.
The model might writeSyntax from a different SQL dialect.
The validatorDetected by parsing, then repaired or refused.
The model might writeA sorted query with no limit.
The validatorA limit is added before it runs.

Designed versus shipped

The original design was considerably more ambitious. What shipped was narrowed on purpose — worth stating plainly.

DesignedA router sending questions to different models by complexity.
ShippedOne model for every question. Complexity is still scored, by rules — a trained classifier was scaffolded but never trained.
DesignedVector search over the schema.
ShippedKeyword matching over a carefully annotated schema description.
DesignedEmbedding similarity for past examples.
ShippedKeyword overlap.

My Role

Role

Sole developer

Contribution

  • Built the pipeline: complexity routing, keyword schema retrieval over a hand-authored domain knowledge base, SQL generation, validation, execution, then a second pass for a plain-language explanation
  • Built a SELECT-only validation and repair layer that blocks destructive SQL and auto-applies limits — the safeguard that makes running model-generated SQL on production data acceptable
  • Added multi-key rotation with exponential backoff and an alternate model provider path for failover
  • Built a self-reinforcing example store that feeds captured query patterns back by similarity matching
  • Narrowed an ambitious multi-model, vector-retrieval design down to a single-model keyword-retrieval implementation that actually shipped

Team Context

Sole developer.

Keeping it running

Rate limits
Several API keys rotated with exponential backoff, and an alternate model provider kept ready.
Privacy
Personal data in the source tables was replaced with consistent dummy values before any of it reached a model.
History
Every conversation logged daily, then archived to the database on a schedule.

Results

343

learned query patterns

8

tables grounded

Limits

There is no evaluation harness, so there is no measured accuracy — the success rate I observed is an estimate, and deliberately not published. Two sequential model calls per answer make responses take several seconds. Usage was a small management team, so it has not been tested under real load.

What I'd Improve

  • Turn the captured question-and-query pairs into an execution-accuracy test set. The data to measure it already exists. First fix.
  • Either train the complexity classifier or delete it — scaffolding that never runs misleads the next reader.
  • Fold the explanation into the first model call to halve response time.
Back to all work