Run Deterministic SQL Before Asking a Local LLM to Plan

“How many patients are there?” is not a semantic-search question. Retrieving six relevant patient chunks and asking a language model to infer the total would be fast, fluent, and wrong. It is also unnecessary: a database already knows how to count.

Yet sending every structured question directly to text-to-SQL is not ideal either. Local planning adds latency, consumes scarce accelerator time, and introduces a probabilistic interpretation step for requests that often fit a small set of exact shapes. On a hospital server that may only have an integrated GPU – or only a CPU – that accelerator time is the system’s scarcest resource, and a question the parser can answer is capacity returned to the questions it cannot. Part 7 uses a different order: route structured intent, link one source scoped to the asking service line, parse the question deterministically into a typed query specification, bind it to that source’s schema, compile dialect SQL, and invoke the local planner only on a clean miss.

The first version of this prototype implemented the deterministic step as SQL templates keyed on the development seed’s table names. That worked on one schema and on no other. This article describes the design that replaces it – a QuerySpec intermediate representation with a grammar, a binder, and a compiler – and is honest about status: the IR is the accepted target design, specified in plan 03, and the code that runs today is still the template matcher.

Every emitted statement – deterministic or model-generated – still passes through the same AST policy, table allowlist, row bounds, cost preflight, read-only execution, and timeout controls. A deterministic parse is a planning shortcut, never a safety bypass.

There is a privacy argument for this order as well as a correctness one. Beyond the residency rule that keeps the whole pipeline inside one facility, data-protection regimes generally require an impact assessment before high-risk processing begins, and regulators treat AI-powered clinical systems as exactly that. A count answered by SELECT count(*) puts no record content into a prompt at all, and a deterministic parse means no model saw the question’s data either. Data minimisation is easier to evidence in an impact assessment when whole classes of question never reach an inference engine – and when everything that does reach one is already inside the boundary Part 1 describes.

The system and examples use synthetic health data. This is a prototype architecture discussion, not clinical advice, compliance guidance, or a claim that arbitrary natural language can be answered accurately.

The reference implementation is in Rust, while the pattern can be implemented through the Foundry Local SDK Reference in C#, JavaScript, Python, or Rust; Part 2 provides all language links.

Problem statement

Semantic top-k retrieval cannot establish an exact count, complete grouping, or authoritative trend from an operational database. Asking a model to infer those results from selected chunks produces an answer that may sound precise while omitting records outside the retrieval window.

Sending every structured question directly to an LLM-generated SQL path is also insufficient. Local planning consumes scarce inference capacity, adds latency, and can propose unsafe, overbroad, or schema-invalid statements. Prompt instructions alone cannot constrain database authority, and retries can silently expand both cost and behavior.

The structured path must recognize deterministic question shapes first, then use a local planner only for genuine misses. It must do so against any relational hospital schema, not the one it was developed on. Every proposal must enter the same parsed safety envelope and bounded read-only executor, with typed fallbacks that preserve provenance rather than relabeling semantic retrieval as exact analytics. Because the whole pipeline is confined to one facility, the local planner is a scarce shared resource rather than an elastic service, which is a further reason to reach for it last.

Success criteria

• Supported exact questions compile to reproducible SQL without an LLM call.


• The deterministic path contains no physical table or column names; those come from a per-source binding.


• Deterministic and model-proposed statements pass the same AST and allowlist checks.


• Execution enforces source scope, row caps, cost limits, read-only behavior, and timeouts.


• Model planning permits at most one bounded repair within the original deadline.


• Results expose SQL, rows, and the path taken; fallback paths remain visibly distinct and typed.

Keep exact and semantic questions on different paths

Narrative questions benefit from hybrid retrieval and grounded generation. Counts, groups, trends, rankings, and bounded listings should prefer structured execution. The router therefore follows a cheapest-first path:

1. resolve cheap references (“her”, “that ward”, “same period”) from typed conversation focus, without a model;


2. handle conversational and conversation-meta questions with anchored rules;


3. attempt a deterministic QuerySpec parse – a successful parse is a structured routing decision;


4. use lexical rules for remaining clear structured or semantic intent;


5. call a small local classifier only for ambiguous or mixed requests;


6. ask one bounded clarifying question when intent is clearly structured but a required slot is missing; and


7. fail open to semantic retrieval if nothing above decides.

A structured result does not mean “let the model choose any database.” The server selects the backend from binding coverage: live-source SQL is chosen only when a connected source has tables bound to the concepts the question mentions; otherwise aggregation over ingested copies; otherwise semantic retrieval.

 

The fallback order is deliberate: live-source deterministic SQL, local model-planned SQL, constrained aggregation over ingested copies, then semantic retrieval. It preserves availability without presenting top-k retrieval as exact analytics.

Stage 1: link a versioned schema, not raw imagination

PostgreSQL, MySQL, and SQL Server form the External Clinical Databases group. Each remains an independent operational source. The system does not generate a cross-source federated query.

A separate Internal DocumentDB Hybrid Store (documents + vectors + metadata/state) contains versioned schema cards as well as ingested records and application state. For every source, a catalog refresh introspects tables, columns, primary/foreign keys, bounded samples, and approximate profiles. It builds card text, embeds it locally with BGE-M3, writes a complete UUIDv7 catalog version, and then moves the active pointer. A failed refresh preserves the previous last-known-good catalog.

Schema linking:

1. embeds the question locally;


2. restricts candidate cards to the tables the asking service line owns (plus shared concepts such as Patient and Encounter) when the question came through a service-line agent;


3. ranks the remaining active cards, promoting explicit table names and administrator-defined aliases;


4. chooses the source owning the strongest card;


5. takes a bounded number of seed cards; and


6. adds each seed’s outgoing one-hop foreign-key targets.

An explicit mention cannot escape scope: a Maternity question that names the billing table gets a redirect to the Revenue agent, not a join. Incoming edges and recursive expansion are not automatic. That limitation matters: if the needed relationship is visible only in the reverse direction, the deterministic binder or planner may miss. The correct response is a miss or fallback, not invented schema.

Versioned cards make the planning input inspectable. They are still a bounded copy: samples and profiles are not authoritative statistics, and source drift between refreshes can invalidate assumptions.

The cards also feed a second artifact that the rest of this article depends on: a schema binding. At catalog time a binder scores every table against a fixed set of hospital entity concepts – Patient, Encounter, Admission, Delivery, Prescription, Bill, and so on – using normalized name tokens, column vocabulary, required roles, BGE-M3 similarity between the card and the concept description, and foreign-key shape. Every column receives a role: PatientRef, EventTime, StartTime/EndTime, Status, Amount, Quantity, Code, Flag, and a few more. Low-cardinality enum columns keep their observed values. Administrators can override any concept or role and the binding is rebuilt. Part 1 describes why this exists; here it is the thing the deterministic path binds to.

Stage 2: templates keyed on table names do not travel

The first deterministic compiler in this prototype was a set of SQL templates. Each recognized a closed question frame and emitted SQL for the development seed: patients, encounters, deliveries. It passed its benchmark and it was still the wrong shape, because it fused three concerns that change at different rates:

A template that says COUNT(*) FROM deliveries WHERE delivery_mode = ‘caesarean’ has all three baked into one string. Move it to a hospital whose table is OB_Delivery and whose column is mode_of_delivery, and every template must be rewritten in Rust. Add a question shape, and every hospital needs a release. The design that replaces it separates the concerns with a typed intermediate representation.

The QuerySpec IR

The grammar’s output is a QuerySpec. Trimmed from the plan:

pub struct QuerySpec {
pub subject: Subject, // the concept rows are drawn from; table filled at bind
pub shape: Shape,
pub measures: Vec<Measure>, // empty for List / Lookup
pub dimensions: Vec<Dimension>, // GROUP BY
pub filters: Vec<Filter>, // AND-ed
pub time: Option<TimeScope>, // range on EventTime/StartTime + optional bucket
pub order: Vec<Order>,
pub limit: Option<u32>,
pub joins: Vec<JoinRef>, // resolved at bind time from FK paths
pub projection: Vec<ColumnRef>, // List / Lookup display columns
pub provenance: SpecProvenance, // which rule fired, which focus substitutions
}

pub enum Shape { Scalar, Grouped, Trend, TopN, List, Lookup, Rate, Exists }

/// Logical column: concept + role (+ optional name hint). Resolved to table.column at bind.
pub struct ColumnRef {
pub concept: EntityConcept,
pub role: ColumnRole,
pub name_hint: Option<String>,
pub physical: Option<(String, String)>,
}

Nothing in a freshly parsed QuerySpec names a table or a column. A ColumnRef says “the EventTime of Delivery” and leaves physical empty. Shape is the closed set of result shapes the compiler knows how to emit; it maps back onto the existing QueryIntent the router and UI already use, so the contract above the IR does not change.

A grammar of slot-filling rules

The grammar is a small ordered set of rules. Each states the anchor tokens it consumes and the slots it fills; a question parses only when a rule matches and every remaining token is in a filler allow-list (“the”, “of”, “show me”, “how many”, “currently”). That full-consumption requirement generalizes the rule the old templates already used for identifier lookups: extra qualifiers are a miss, not a guess.

Rules compose. R1 plus a location modifier plus a status modifier plus a time phrase yields “how many patients are currently admitted in ICU” as one QuerySpec: subject Admission, shape Scalar, a filter on the WardRef join’s name column, a filter that reads EndTime IS NULL or a Status enum value, and no time bucket.

Concept resolution is vocabulary-driven: a noun phrase maps to a concept when it matches the concept’s singular, plural, or synonyms, a bound table name, or an admin alias. Enum values become filters through the binding’s observed values, so “caesarean” resolves to (delivery_mode, caesarean) on this hospital and to whatever the equivalent column is called on the next. A dimension such as “by ward” resolves through the subject’s WardRef role to the ward table’s name column, or to a Location column when the schema has no ward table.

Domain predicates live in roles

Some phrases carry clinical or operational meaning that is not a literal value in any column. The design keeps them in a fixed predicate table expressed only in roles and name hints:

If the hospital’s schema has no column playing the required role, the predicate is unbound and the parse reports a missing slot. It does not silently pick a nearby column.

Bind errors become questions, not guesses

bind resolves every ColumnRef against the source’s binding: the subject’s table, each role on that table or a one-hop foreign-key neighbour (two hops for dimensions only), and the patient path when the question names a patient. It fails typed: NoSubject, NoRole(role), NoPath(a, b), Ambiguous(role, candidates). The router turns a missing subject into one clarifying question – “Which records do you mean: appointments, admissions, or surgeries?” – and turns Ambiguous into a clarify whose options are the candidate columns. The rule is at most one clarification per turn and never two in a row; the second time, the system falls through to semantic retrieval rather than interrogating the user.

The governing principle of the old templates survives intact:

A false negative is acceptable; a false positive is not.

A miss moves to the local planner. A guessed binding can execute successfully and return the wrong answer with high confidence.

The compiler owns the dialect

compile takes a bound QuerySpec and a SourceKind and emits SQL text plus a one-sentence explanation (“Counted deliveries with delivery_mode = caesarean between 2026-08-01 and 2026-08-31, grouped by ward”). Identifiers are always quoted for the dialect; values are inlined as escaped literals and the validator re-parses everything, so no user text is spliced outside a literal. Time buckets compile to DATE_TRUNC on PostgreSQL, DATE_FORMAT on MySQL, and DATETRUNC or DATEFROMPARTS on SQL Server; durations to EXTRACT(EPOCH …), TIMESTAMPDIFF, or DATEDIFF; anti-joins to NOT EXISTS. Relative ranges are computed server-side and inlined as ISO literals so the SQL is self-contained provenance. A shape the dialect cannot express – a median on MySQL – is a compile miss that goes to the planner, not an approximation.

Deterministic output can still be wrong because code has bugs or metadata can be stale. It therefore enters exactly the same validator and executor as model output.

Stage 3: the local LLM speaks the same IR

If the grammar cannot parse the question, the server obtains the planning model role and asks Foundry Local to fill a QuerySpec – a tool call whose JSON schema is the IR, with concepts and roles as enums and physical names forbidden. The proposal then goes through the same bind and compile as a grammar parse.

This changes what a bad proposal can do. A small local model is markedly more reliable filling a typed schema than writing dialect SQL, and a QuerySpec that names a concept the binding lacks, or a role the subject does not have, is rejected by bind before any SQL exists and before any database is touched. Only if the IR path fails does the design fall back to the existing raw-SQL planner as a last resort.

Foundry Local is the local generative runtime. It does not execute SQL and it does not decide the security policy. The model’s output is untrusted data in either form.

A single planning deadline covers the first plan and at most one repair. That prevents “retry until something executes,” which can turn safety failures into prompt-driven exploration. The current raw-SQL planner already enforces that shape; the IR planner keeps the same deadline and repair budget:

let deadline = Instant::now() + plan_timeout;
let mut repaired = false;
let mut proposal = timeout_at(deadline, plan_sql(…)).await??;

let mut sql = match validate_sql(&proposal.sql, dialect, cap, allowed) {
Ok(sql) => sql,
Err(error) => {
repaired = true;
proposal = timeout_at(
deadline,
plan_sql_repair(…, &proposal.sql, &error.to_string()),
).await??;
validate_sql(&proposal.sql, dialect, cap, allowed)?
}
};

If first execution fails and no validation repair was used, the single repair can address that execution error. The repaired statement is parsed and validated again. There is never an unbounded autonomous loop.

This is a practical local-inference use: reserve probabilistic planning for the long tail while common exact requests stay fast and reproducible. The Foundry Local quickstart demonstrates local model lifecycle and inference; in this architecture, those capabilities sit behind a server-owned policy boundary.

Stage 4: enforce policy on the parsed AST

String checks alone are not a SQL sandbox. The validator parses according to the selected source dialect and requires exactly one query statement. It rejects DDL, DML, locking clauses, relations outside the linked-table allowlist, and dangerous function or text signatures such as sleep, file access, dynamic SQL, OPENROWSET, or XP_CMDSHELL.

It then normalizes from the AST and enforces an outer row bound:

• PostgreSQL and MySQL receive a constant LIMIT, inserted or clamped.


• SQL Server receives a constant, non-percent, non-ties TOP; unsupported outer set operations decline.


• The connector applies a second result cap during execution.

let validated = validate_sql(
proposed_sql,
source_kind,
config.nl2sql_max_rows,
&linked_tables,
)?;

let (columns, rows) = run_select(
&db,
&config,
source_id,
&validated.sql,
).await?;

Table names come from the linked schema cards, restricted to the service line’s scope. User literals enter only through the grammar’s typed filters or remain part of model output subjected to the parser and policy. Deployment should still use a least-privileged, read-only source account: application validation and database permissions are complementary controls.

Stage 5: preflight cost and bound execution

Before execution, connectors attempt an EXPLAIN or equivalent estimate when cost gating is enabled. A successful estimate above the configured maximum is rejected. If estimation itself fails, the current prototype logs the failure and continues to bounded execution.

That fail-open choice is a tradeoff, not an invisible detail. Availability is preserved, but a broken estimate cannot enforce the cost threshold. The remaining layers – AST policy, row cap, database timeout, Tokio timeout, and read-only credentials – still apply.

Execution returns columns and exact rows. For small results the server produces a deterministic summary rather than making a second narration-model call. The persisted result keeps source_id, executed SQL, columns, rows, the QuerySpec that produced them, and a provenance record listing each rung attempted and whether it hit or missed, so the UI can show the evidence type and the path directly.

This architecture avoids fake capabilities. It does not promise that the system can answer arbitrary relational questions, that every planner query will be performant, or that EXPLAIN guarantees safe runtime. It promises a bounded attempt under explicit controls.

Stage 6: fall back without changing the truth claim

A final SQL failure does not become a fabricated SQL answer. The next path is a constrained aggregation or listing over active records in the Internal DocumentDB Hybrid Store, restricted to the same service-line scope. Where the subject table has been ingested, the bound QuerySpec translates directly into the aggregation specification – count, group, bucket, filter – without a model; otherwise the model proposes a typed specification and Rust validates fields and builds the pipeline. Chunked rows are deduplicated by row_pk before aggregation, preventing chunk count inflation.

That result describes the latest successfully ingested snapshot, not necessarily current source state. If structured aggregation also fails, the system uses hybrid semantic retrieval and grounded generation, filtered to the same tables. The answer now carries passage citations rather than SQL provenance and must not claim exact whole-database counts.

“Bounded failure” is defined precisely in the design: a parse miss, bind error, validation error, connector error, timeout, cost rejection, or an empty result on an existence question. An empty result on a list or grouping is a hit with an honest “none”. Falling back there would silently swap an exact answer for a fuzzy one.

The evidence changes with the backend:

The router recognizes hybrid cohort-plus-narrative intent. In the target design the cohort is a QuerySpec forced to a list shape, its row keys become an explicit retrieval filter, and narration receives both the cohort summary and citations. The current build does not yet run that path; hybrid-class requests fall back to ordinary semantic retrieval.

Useful deterministic patterns – and deliberate refusals

A robust grammar documents what it declines as carefully as what it accepts.

R1 can accept “How many patients are there?” and decline “How many active patients are there?” unless “active” resolves to a Status enum value or Flag role on the bound Patient table. R4 can accept a closed trend frame and decline a subject with no EventTime. R5 compiles only when the binding has a foreign-key path from the subject to the dimension concept.

Follow-ups are handled by focus, not by re-parsing. The last QuerySpec is kept in typed conversation state; “and by ward?” mutates its dimension, “only caesareans” adds a filter, “as a percentage” changes the shape to Rate. Each mutation is a normal QuerySpec that binds and compiles like any other, and none of it calls a model. General coreference remains outside the claim.

Tests should cover positive and refusal fixtures against more than one binding:

assert_shape(“How many patients are there?”, &dev, Shape::Scalar);
assert_shape(“How many patients are there?”, &alt_schema, Shape::Scalar); // PatientMaster
assert_no_parse(“How many active patients are there?”, &dev_without_status_role);
assert_no_parse(“Delete all patients”, &dev);
assert_bind_error(“admissions by ward”, &schema_without_ward_ref, BindError::NoPath);

A high refusal rate can be healthy if fallback is safe and observable.

Tradeoffs and anti-patterns

A grammar and binder require maintenance too, but of a different kind. Phrasings, filler lists, predicate definitions, and dialect rules are documented and tested once; a new hospital is a binding to review, not code to write. First-match semantics still mean a broad rule can shadow a safer specialized one, so rule order is part of the specification. Binder confidence is a heuristic; the design requires the dev seed and a second, differently named synthetic schema to bind correctly without overrides, and treats an admin override as the normal fix when a real schema binds badly.

The local planner covers more language but adds latency and uncertainty. Schema linking limits prompt size and table access, but directional one-hop expansion can omit a necessary relationship. One repair improves resilience without creating an open-ended agent.

Avoid these anti-patterns:

• using top-k semantic retrieval for exact totals;


• executing deterministic SQL without the shared validator;


• naming physical tables or columns anywhere in the deterministic path;


• trusting model-generated table names, or letting the model emit SQL when it could emit a typed spec;


• letting the model choose among operational sources without server policy;


• accepting extra unconsumed words in a grammar rule;


• guessing a column when a role is missing instead of asking or missing;


• retrying planning until some query runs;


• presenting a DocumentDB snapshot result as live-source data;


• claiming arbitrary query support or guaranteed performance.

Prototype status

Implemented today: tiered structured intent; one-source schema linking; versioned last-known-good schema cards; deterministic SQL templates before the model; local TextToSql planning on miss; dialect-aware AST validation; linked-table allowlists; read-only-only policy; limits; EXPLAIN cost preflight; connector and async timeouts; one repair within one deadline; SQL provenance; fallback to constrained DocumentDB aggregation and then semantic RAG. The committed A-series benchmark of ten exact questions on the synthetic seed resolves 10/10 through the deterministic templates.

Planned and specified, not yet in code: the QuerySpec IR, grammar, domain predicates, binder, per-dialect compiler, model fallback in the same IR, service-line-scoped linking, the single executor with a provenance event, and focus-driven follow-up mutation (plans 01–04, 06). The design’s acceptance gates – A-series unchanged, a broader golden suite parsing on both the dev seed and a differently named synthetic schema, and zero physical names in the IR module – are targets, not results.

Current limits include PostgreSQL-only specialized healthcare templates, directional one-hop relationship expansion, bounded rather than authoritative catalog samples, fail-open estimate errors, no arbitrary multi-hop or free-form aggregate guarantee, and no production performance certification. The recorded prototype evaluation uses synthetic data; deterministic benchmark passes do not validate the local planner’s quality.

Key takeaways

1. Route exact questions away from semantic top-k retrieval.


2. Link one source, scoped to the asking service line, and a bounded versioned schema before compiling anything.


3. Separate grammar, binding, and compilation so phrasing, hospital, and dialect can change independently.


4. Prefer a deterministic typed parse and treat an unbound slot as a question or a miss, never a guess.


5. Use the local LLM only for the long tail, ask it for the same typed spec, and allow one bounded repair.


6. Apply the same AST, allowlist, row, cost, timeout, and read-only controls to every query.


7. Preserve backend-specific provenance and freshness claims through fallback.

Continue the series

• Previous: Part 6 – Streaming grounded chat to Desktop App and Mobile App


• Next: Part 8 – Bounded Local Agentic Patterns for Clinical RAG

Learn more on Microsoft Learn

• What is Foundry Local?


• Get started with Foundry Local


• Foundry Local SDK Reference – Rust on Windows


• Design and develop a RAG solution

The code behind this series is open source: github.com/kevin-gatimu/onprem-health-rag. It is a synthetic-data research prototype, not a clinical or compliance-ready system – issues and corrections are welcome.