Blog /
A Reliable NL2SQL Architecture: Separate Generation, Validation, Execution, and Explanation
Published . Written for nlqueries-core 0.4.
architecture validation text-to-sql
It's the Monday after month-end close. Someone on the finance team opens the internal ops dashboard, types "What was paid revenue for tenant A in October?", and gets back a tidy sentence: Revenue was $0. Nobody panics at first, because the dashboard has been right before. An hour later someone checks the warehouse by hand. The real number is $300. The assistant's answer came from a query that timed out, and the error was formatted as an empty result.
Natural-language-to-SQL (NL2SQL) systems fail like this when the whole pipeline collapses into one string. "Revenue was $0" could mean the SQL was generated but never run, rejected by a policy check, run and matched nothing, or never finished at all. Those are four different situations with four different correct responses, and a single answer field erases the difference. The fix is architectural: treat generation, validation, execution and explanation as separate stages, each with its own guarantee and its own failure state, and make your application contract carry all four.
NLQueries Core 0.4.1 makes a useful worked example, because its interfaces already draw most of those lines. Everything below comes from its source and docs at a pinned commit, plus a small synthetic SQLite fixture. The two application helpers further down were exercised against Core 0.4.1 on SQLite with a scripted model standing in for a live LLM; the preview call and everything PostgreSQL-specific weren't run, so read those parts as untested sketches.
Four stages, four different promises
The easiest way to keep the stages honest is to write down what each one can actually promise.
Generation turns a question into candidate SQL. Its only promise is that you have a statement to look at. A model can produce SQL that's syntactically perfect and semantically wrong, so generation guarantees nothing about the answer.
Validation decides whether that statement is allowed to run. It can tell you the SQL parses, references known objects and uses only permitted constructs. It can't tell you the SQL answers the question the user asked.
Execution runs an allowed statement against the database under a specific role and time budget. It can tell you what rows came back, or that the database refused, errored or truncated the result.
Explanation turns rows into prose. It's only as trustworthy as the rows it was written from, and it can go stale independently of them.
If your API returns one answer string, every one of those distinctions has to be guessed by the caller. If it returns the stages separately, the caller can branch on facts instead.
Generate a preview without running it
Start with the most common request that goes wrong: "show me the SQL, but don't run it." In NLQueries this is an explicit permission object rather than a boolean flag. ExecutionPolicy is a frozen dataclass with two modes, generate_only and execute_read_only, and the default policy, applied when a caller passes nothing, is generation-only (execution.py). The source is explicit about why it's frozen: receiving code can't widen the permission in place, so a retry, sub-agent or cache replay that executes SQL must have been handed a policy that already allowed it.
There's one exception to know about before you promise anyone "preview touches nothing." Literal grounding, which looks up the stored values a literal is compared to (so 'paid' can be matched to 'PAID'), mints its own read-only policy for those lookups. It never runs the generated statement, but it does open a connector. The same file documents NLQ_LITERAL_GROUNDING=false for a mode that opens no connector at all. If your security reviewer asks "does preview read the database?", the honest answer depends on that setting.
Here's what the preview call looks like for the finance question. The function and parameter names come from the pinned sync_runner.py; the agent ID is a placeholder for an agent you've already configured:
# Untested sketch against NLQueries Core 0.4.1 (commit 2fbf276).
# Assumes: an agent "ops-orders" with an exported knowledge base and a
# PostgreSQL connector; NLQ_LITERAL_GROUNDING=false set before import
# if preview must open no connector.
from nlqueries.execution import ExecutionPolicy
from nlqueries.orchestrator.sync_runner import run_query
async def preview(question: str):
return await run_query(
question,
agent_id="ops-orders",
dialect="postgres",
execution=ExecutionPolicy.generate_only(),
use_cache=False, # neither read nor write the semantic cache
explain=True, # attach provenance to the result
)
The result is an AgentQueryResult, not a string. It carries sql, sql_is_valid, execution_mode, sql_result, answer, from_cache and an optional provenance record, among other fields (sync_runner.py). A comment on sql_is_valid records why the field exists: without it, a caller could only infer validity from sql_result is None, which is also what a generation-only run and a rejected statement look like. That's the "$0" problem in miniature, and it's the reason to branch on fields rather than on the presence of rows.
Validation tells you "allowed," not "right"
Before a generated statement reaches a connector, NLQueries parses it with sqlglot in the connector's dialect and allows only the constructs it lists for that dialect (database hardening). The policy module's evaluate(sql, dialect) returns a PolicyDecision with an allowed flag, a list of reasons for refusal and the policy version that made the call. It never raises; a statement it can't parse is refused, on the grounds that SQL it can't read is SQL it can't vouch for (sql_policy.py).
That's a strong structural check, and it's still not a correctness check. A small fixture makes the gap concrete. It's synthetic data: six orders across two tenants, including a cancelled order, one dated just past the end of October, one with a NULL amount, and one (order 101) with two line items, which is a deliberate trap for join fan-out (when a join multiplies rows and inflates an aggregate). Load it into an in-memory SQLite database:
-- Synthetic fixture (SQLite syntax). Expected answer: 30000 cents, 3 orders.
CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);
INSERT INTO customers VALUES (1,'A','East'),(2,'A','West'),(3,'B','North');
INSERT INTO orders VALUES
(101,1,'2026-10-01',10000,'PAID'), (102,1,'2026-10-02',5000,'CANCELLED'),
(103,2,'2026-10-31',20000,'PAID'), (104,3,'2026-10-02',70000,'PAID'),
(105,1,'2026-11-01',40000,'PAID'), (106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES (1,101,'a'),(2,101,'b'),(3,103,'c');
The reference answer for tenant A's paid October orders is 30,000 cents across three orders. Here's the reference query next to a plausible generated variant:
-- Sample data, SQLite. Order 101 has two order_items rows.
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
-- Plausible generated variant: same filters, plus a join to order_items.
SELECT SUM(o.amount_cents), COUNT(*)
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS i ON i.order_id = o.id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
Against this fixture on SQLite 3.45.3, the reference returns (30000, 3). The join variant returns (40000, 3): order 101's amount is counted twice, while the count still looks right because the join also drops order 106, which has no line items. It's a read-only SELECT against known tables using ordinary constructs, so any structural policy has every reason to allow it, and it overstates revenue by a third.
The same caution applies to the database side. The hardening guide is careful to say a PostgreSQL read-only transaction still permits pg_sleep, advisory locks and SELECT on anything the role can reach, and that read-only isn't the same as least privilege. Validation and grants are separate safeguards, and neither one knows what "paid revenue" means to your finance team. That's why the contract needs a stage whose job is to say what actually happened, not just what was allowed.
Empty, failed and truncated are different answers
Execution is where the "$0" bug usually lives, and the fixture shows a subtler version of it. Run the reference query with the date range moved to October 2027, where nothing matches, and SQLite doesn't return zero rows; it returns one row, (None, 0). An aggregate over nothing is NULL, not zero. If your formatter turns None into $0, you've already blurred "no sales" and "unknown," and that's before any timeout enters the picture.
NLQueries' QueryResult gives you the fields to keep those apart: columns, rows, row_count, execution_time_ms, an error string, and a truncated flag with a truncation_reason such as a row budget or byte budget (connectors/base.py). The source comment on truncation makes the point well: silently returning the first N rows of a larger answer is a wrong answer, not a partial one. There's also ExecutionNotPermitted, which is raised rather than skipped precisely so a caller asking for rows can tell a policy refusal from an empty result (execution.py).
Deadlines need the same care, and it's easy to read more into them than the code promises. run_query takes two different budgets. timeout_seconds is a per-statement budget handed to the connector. On PostgreSQL it's applied as SET LOCAL statement_timeout, which asks the server to abort a runaway statement rather than leave it running after the caller has given up (sync_runner.py). Connectors that don't support a statement-level timeout ignore it (connectors/base.py), and the docstring says it doesn't currently bound the LLM call at all. So it isn't a universal cancellation guarantee, and the PostgreSQL behaviour isn't exercised in this article. deadline is a monotonic timestamp for the whole turn; it gates whether the SQL sub-agent attempts its one correction after a database error, and nothing more. If your API gateway gives up after 20 seconds, set both: a statement timeout at or slightly below that budget, as the docstring recommends, and a turn deadline that stops retries you'll never deliver. Then check your connector actually honours the first one, and keep your own wall clock around the whole call.
With those fields, the response state becomes something you compute rather than guess. This mapping is application code, not part of NLQueries:
# Custom application code (not an NLQueries API). Untested sketch.
from enum import Enum
from nlqueries.execution import ExecutionNotPermitted
class State(str, Enum):
REJECTED = "rejected" # SQL failed validation
PREVIEW = "preview" # valid SQL, not executed by design
FAILED = "failed" # execution attempted and errored
TRUNCATED = "truncated" # rows returned, but not all of them
EMPTY = "executed_empty" # ran, zero rows
ROWS = "executed_rows"
def classify(result=None, exc: Exception | None = None) -> State:
if isinstance(exc, ExecutionNotPermitted):
return State.REJECTED # treat as a calling-path defect; log loudly
if exc is not None:
return State.FAILED # provider fault, timeout, cancellation
if not result.sql_is_valid:
return State.REJECTED
if result.execution_mode == "generate_only":
return State.PREVIEW
r = result.sql_result
if r is None or r.error:
return State.FAILED
if r.truncated:
return State.TRUNCATED
return State.EMPTY if r.row_count == 0 else State.ROWS
The comment's "untested sketch" label is still the right one for production purposes. Against Core 0.4.1 on SQLite, with a scripted model and the real pipeline and connector, this function sorted a preview, rows, an empty result, a rejected statement and a driver error into the states you'd expect, and a synthetic truncated result into TRUNCATED. It was written for the SQL route, though: a synthetic hybrid result also lands in ROWS, so if your agent can answer from documents too, define those states yourself rather than assuming this mapping covers them.
Notice what EMPTY doesn't cover: the (None, 0) aggregate row is ROWS by this definition, and the UI layer still has to render NULL as "no paid revenue recorded" rather than "$0." A state machine can't make that call for you, but it stops a timeout from ever reaching the formatter in the first place.
Explain only what the rows support
Explanation is the stage people trust most and inspect least. Two details in the NLQueries source make the case for treating it as its own stage.
The first is caching. The semantic cache stores generated SQL and answer text, not result rows, because rows go stale. On a hit for a SQL answer, the orchestrator re-checks the cached statement against the SQL policy and re-executes it, but only if the request's ExecutionPolicy permits execution; a cache hit is described in the source as "a shortcut past generation, not past permission" (multi-agent orchestrator). The stored answer text, though, is replayed as written. So on a cache hit you can get fresh rows next to prose written about last week's rows. With a simulated cache hit against the fixture, updating the data moved the re-executed rows to (32000, 3) while the replayed sentence still said 30,000 cents. The architecture doc adds that cache context has to carry differences in entitlement when users of the same agent can see different data (architecture).
The second is the answer field itself. Pass explain=True and the result carries a provenance record. Provenance records route, context, checks and timings; it does not establish sentence-level support. It tells a reviewer how the turn was processed, not which row backs which sentence. And the single-agent orchestrator streams natural-language reasoning first and yields the validated SQL afterwards (orchestrator.py), so SQL-only answer text may be pre-execution model text or empty. In a scripted-model run against the fixture, the SQL path returned rows of [[30000, 3]] and an empty answer string. Neither outcome tells you the prose matches the numbers.
The practical rule follows from that. Render authoritative values from a successful QueryResult, and treat any separately generated explanation as another output to verify. Show from_cache when it's set. Never put an answer next to a result in the FAILED or REJECTED state, and never let text written before the query ran override rows that came back afterwards. Prose about a failed query is how "$0" gets written.
Running the same question twice isn't approval
Many teams add a human review step: show the SQL, let someone approve it, then run it. The tempting implementation is to call run_query again with execute_read_only() once the reviewer clicks approve. That doesn't do what it looks like. Each call is an independent request that may generate SQL or replay cached SQL; neither behavior binds it to the exact statement a human approved. The cache is on by default (use_cache=True), and a fresh generation can produce different SQL from the statement the reviewer saw.
If approval matters, bind it to the exact bytes. NLQueries' PolicyDecision already records a SHA-256 sql_digest of the statement it evaluated, and its authorises(sql, dialect) method returns true only if the decision allowed the statement, the digest matches the SQL you're about to run, and the dialect matches. Whitespace and comments count (sql_policy.py). You can build the approval record on top of that:
# Custom approval binding (application code). Untested sketch.
from dataclasses import dataclass
from nlqueries.sql_policy import evaluate
@dataclass(frozen=True)
class Approval:
sql: str
dialect: str
approved_by: str
policy_version: str
def execute_approved(approval: Approval, run_readonly):
# Re-validate at execution time: the policy may have changed since review.
decision = evaluate(approval.sql, approval.dialect)
if not decision.authorises(approval.sql, approval.dialect):
raise PermissionError(f"Approved SQL no longer passes: {decision.summary()}")
# run_readonly is YOUR function: a least-privilege role, read-only
# transaction and statement_timeout. It must run approval.sql verbatim.
return run_readonly(approval.sql)
With a recording callback in place of run_readonly, the callback receives exactly the approved SQL after the digest and policy check. That's the whole of what's been shown. The executor here is deliberately yours: there's no documented "execute this approved statement" call in the run_query signature, so the path for reviewed SQL is application code. Where approvals are stored, how the reviewer's identity is checked and which database role runs the statement are all still yours to build, with the same read-only transaction and timeout discipline as everything else.
Put the contract in writing
Pull it together and the API contract for the finance dashboard looks something like this: every response carries a state (one of the six above), the exact sql that was generated, whether it was valid, the execution_mode it ran under, the raw rows with truncation metadata, whether it came from cache, and answer text only when the state supports one and the text has been checked against the rows. Approved executions carry the digest of the statement that actually ran. None of that is exotic. It's what keeps "the query timed out" from becoming "revenue was $0," and it's what lets you answer "which SQL produced this number?" a month later.
NLQueries Core gives you a lot of the raw material: an immutable execution permission that defaults to preview, a validity flag that doesn't depend on rows, structured results with error and truncation fields, separate statement and turn budgets, and a digest-bound policy decision. The domain checks, identity, response states, answer verification and approval binding are still yours to build.
For a next step, load the six-order fixture above, wire up classify, and try to break it. Confirm you get (30000, 3) from the reference, (40000, 3) from the join variant and (None, 0) for October 2027. Run a preview with literal grounding on and off and watch the database logs. On PostgreSQL, push a statement past its timeout and confirm the response lands in FAILED, not $0. Hand your renderer a result whose answer text names the wrong number and check that the rows win. Then change one order's amount between two cached requests and see whether the sentence still matches the table. Each of those is a short test, and each one tells you whether your stages are actually separate or just separately named.
References
- NLQueries Core 0.4.1. nlqueries/execution.py. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. nlqueries/orchestrator/sync_runner.py. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. nlqueries/orchestrator/orchestrator.py. Commit 2fbf276. Accessed 2026-11-07.
- NLQueries Core 0.4.1. nlqueries/sql_policy.py. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Hardening the database NLQueries reads. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Multi-agent orchestrator. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Architecture. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. nlqueries/connectors/base.py. Commit 2fbf276. Accessed 2026-11-05.
- NLQueries. Documentation. v0.4.1, docs 2fbf276. Accessed 2026-11-05.