A natural-language-to-SQL chatbot for MS SQL Server: schema pruning, two LLMs and the guard rails it still needs

The pipeline

Four small classes carry it: a SchemaLoader that parses the schema file once, a QueryProcessor that finds relevant tables and builds the prompt, an AIAgent that talks to the two providers, and a ChatHistory. The version documented here is the first one, a Flask app with a server-rendered chat page.

The decisions

Prune the schema before prompting. A real schema does not fit a prompt, and even when it does, an LLM given forty tables picks the wrong join. Only tables whose column names appear in the question are sent. It is crude, and it is enough to keep the prompt small and the joins plausible.

Two models, two jobs. Writing correct T-SQL and writing a friendly sentence are different skills with different latency budgets. The SQL step uses a code-capable model through OpenRouter; the answer step uses a fast model on Groq, because the user is already waiting on the database round trip.

Force TOP 5 in the prompt. Without it, "list customers" returns the table. With it, every answer stays readable and the second model never sees more rows than it can summarise.

Extract SQL from a fenced block, or refuse. The model is told to answer in a ```sql block; the code takes exactly that block with a regex and errors if it is missing. Free-text SQL extraction is where injection and hallucinated columns sneak in.

Greetings never touch the database. A handful of exact-match phrases ("hello", "thanks"…) short-circuit the whole pipeline.

What I would harden before exposing it

I would rather be precise about this than pretend the prototype is production:

• The read-only guard is a startswith("select") check. That stops nothing: a SELECT can call a function with side effects, and T-SQL allows more than one statement. The real fix is a database login with read-only rights plus a parser-level allow-list — the guard in code is a convenience, the guard in the database is the security boundary.
• Chat history is one in-memory list for the whole process, so two users would share context. It needs a session id and a store.
• The database connection is opened per request. A pool (or a single connection with reconnection) removes a few hundred milliseconds per question.
• Schema pruning by keyword misses synonyms ("clients" vs. Customers). An embedding index over column names and descriptions is the natural upgrade — the same retrieval idea as in my RAG chatbot platform, applied to a schema instead of documents.
• No evaluation set. A dozen question/SQL pairs run in CI would catch model or prompt regressions on day one.

Why it is still on this page

Because the shape of the problem is the one I keep meeting: put a language model between a person and a system, and most of the engineering is in what you feed the model and what you refuse to let it do. The prototype answers real questions on a real database; the list above is exactly what separates it from something I would hand to a client.