Routing and Text-to-SQL: When the Answer Lives in a Table

Picture a company assistant handling two questions a minute apart:

1. "What is the policy on carrying forward leave?"
2. "How many leave days do I have left?"

The first is answered by a paragraph in the HR handbook, which is a classic RAG question. The second is answered by one row in a database table. No chunk in any document contains your personal balance. Vector search over policies will retrieve the carry-forward rules and the model may confidently compute a wrong number from them. Some answers live in structured data, and retrieving text is the wrong tool for them.

An assistant that handles both needs two abilities: to route each question to the right source, and to query structured data in natural language.

The router

A router is the first decision node in the graph. It reads the question and outputs a route label as structured output: for example "rag" for policy, process and how-to questions, and "sql" for questions about specific records, balances, counts and totals. The routing prompt describes each route concretely:

• Use sql for questions about an employee's details, leave balances, insurance coverage, salary or counts and aggregates over records.
• Use rag for questions about company policies, procedures, entitlements and guidelines.

A conditional edge then sends the request down the corresponding branch. A cheap but useful extra route is small talk: "hi", "thanks", "tell me a joke" get a direct reply without touching any index or database. Routes can multiply as the system grows: web search for current events, a ticketing API for "what's the status of my request", a calculator, or several separate document indexes. Each new source is a new branch.

Routers can be built three ways, and production systems often layer them:

• LLM routing: flexible and good with ambiguous phrasing, at the cost of one fast LLM call.
• Semantic routing: embed the question and compare it with example questions per route. It is very fast and needs no LLM call.
• Rules: a question containing an employee ID or "my balance" goes to SQL. Rules are cheap and predictable for the obvious cases.

Text-to-SQL, step by step

The SQL branch turns a question into a database query and the result back into prose.

1. Sharpen the question. An LLM rewrites the user's wording into a precise, SQL-friendly request that names the tables involved ("products below reorder level" becomes "products joined with inventory where quantity < reorder_level, with warehouse and supplier email"). This is query transformation (Module 4), applied to SQL.
2. Give the model the schema. List the relevant tables, columns, types and how the tables join (leave_balances.employee_id → employees.id), plus a short description of what each column means. Column names like lv_bal_cf are meaningless without a description. A few example question–SQL pairs improve accuracy considerably.
3. Generate the query with a strict instruction to return only a single SQL SELECT, not SQL wrapped in explanation or Markdown.
4. Validate before running. Parse the SQL and reject anything that is not a single read-only SELECT, touches tables outside an allow-list, or lacks a row limit. A cheap extra check is to ask the database to EXPLAIN the query: a dry run that catches syntax errors and unknown columns without executing anything.
5. Execute with a database role that can only read the permitted tables.
6. Recover from errors. If validation or execution fails (wrong column name, bad join), a rewrite node gets the failing SQL, the error message and the schema, produces a corrected query, and goes back through validation. Cap the loop (a handful of retries) and, when the retries are used up, return an honest "I couldn't build a working query for that" rather than looping forever. This one loop fixes a large share of first-attempt failures.
7. Summarise the result. Raw rows (EMP001 | 14 | 3) are not an answer. A final LLM call turns the question, the SQL and the rows into a sentence: "You have 14 days of annual leave and 3 days of sick leave remaining."

Large schemas

Real databases have dozens or hundreds of tables, which is too many to put in every prompt. Treat the schema itself as a corpus: read each table's columns, types and foreign keys from the database's own catalogue, write one document per table, embed it, and retrieve the handful relevant to the (sharpened) question before generating SQL. It is RAG, applied to the schema rather than to documents, and like any index it must be re-ingested whenever the schema changes.

Ambiguity

"What is the employee's salary?" — which employee? A good assistant does not guess. When required parameters are missing, the graph should route to a clarifying question ("Which employee — can you give the name or ID?") and resume once the user answers. Conversation state, held in the graph, makes this natural.

Security is not optional

Text-to-SQL connects a language model to live business data, so it needs the strictest guardrails in the course:

• Read-only, least-privilege credentials. The model's database role cannot write, delete or alter anything, and can see only the tables it needs. Demo code often runs whatever SQL comes back and even commits non-SELECT statements. That is acceptable on a toy database and dangerous anywhere else, because a single misread question ("remove the duplicate reviews") becomes a DELETE.
• Authorisation outside the model. "What is the salary of EMP001?" should succeed only if the logged-in user is allowed to see EMP001's salary. Enforce this in the database (row-level security, or views scoped to the user) or by injecting a mandatory WHERE employee_id = :current_user from server code. Never rely on the prompt saying "only show the user their own data", because a prompt is not an access-control mechanism.
• Validate, limit, log. Parse and allow-list the SQL, cap the number of rows returned, set a statement timeout, and log every generated query for audit.