Letting an LLM Query Enterprise Data Without Trusting It
Why RAG breaks down on millions of rows, and how governed Postgres views, a parse-gated SQL guard, and a cited knowledge catalog let a model answer in plain English without ever holding the keys.
A sales rep between two calls types "which of my accounts have gone quiet this quarter?" and wants an answer before the next call starts. The data behind that question is millions of rows across a multi-tenant database, and the rep is only allowed to see a slice of it. Getting an LLM to answer was never the hard part. Getting it to answer correctly, from only the rows that rep is allowed to see, without handing the model the keys to the database, was the whole job.
This is a write-up of the engineering behind the AI assistant I build at work, inside a pharma sales intelligence SaaS product. I've kept it at the level of architecture and lessons. Customer data, table names, and business rules are left out or changed, and the code snippets are simplified illustrations rather than the production source.
A living write-up: this system is still being built, and the numbers in it move with every eval run. I'll keep updating this article as it grows and as I learn new lessons from it. What changed and when is logged in the updates at the end.
Why RAG Was the Wrong Tool
The reflex for "LLM over company data" is retrieval-augmented generation: chunk the data, embed it, retrieve the closest chunks, and let the model summarize them. That works for documents. It does not work for questions like these:
- What was my revenue through this purchasing group last quarter, compared with the quarter before?
- Which of my accounts bought last year but have not ordered in 90 days?
- Which product am I closest to hitting my goal on?
Every one of those is an aggregation: a sum, a comparison, or an anti-join across thousands to millions of rows. You cannot retrieve your way to a SUM. The top-k chunks are a sample, and a sample of sales rows gives you a confidently wrong total.
The system before mine had already learned a quieter version of the same lesson. It used a deterministic pipeline: an intent parser, a planner the LLM filled in, and a compiler that turned the plan into SQL. The business knowledge (what a column means, which join fans out, which date is the real one) lived in a catalog that was retrieved by embedding similarity on each question. When retrieval missed, nothing errored. The plan just came out missing the rule that would have made the answer right. A retrieval miss on knowledge is invisible, and it shows up as a plausible number that happens to be wrong.
The pipeline also had to be taught every question shape in advance. When I looked at its history, most substantive commits were steering the planner toward one more question shape, and a meaningful share of them broke an earlier fix. That is not a bug a patch fixes. It is the shape of the system.
So the rebuild inverted it. The database already knows how to aggregate millions of rows in milliseconds. Let the model write the SQL itself, and put all the engineering into making that safe and making it right.
The Model Is Untrusted Input
The moment a model writes SQL, its output is untrusted input to your database, the same as a form field on a public web page. It can be wrong, it can be steered by a cleverly worded question, and it will occasionally do something nobody predicted. None of the prompt, the persona, or the instructions counts as a security control. Only four things do:
Two database roles
The role that runs model-written SQL can read a set of governed views and nothing else. The service's own role handles sessions and audit logs. Separate credentials, never crossed.
Governed views
Tenant and per-user row scoping enforced inside Postgres, so the model never writes an access filter and cannot forget one.
A parse gate
Every statement is parsed by Postgres's own parser and rejected unless it is exactly one SELECT over allowlisted views and functions.
A post-execution assertion
After the query runs, the service proves the query didn't tamper with the session context the views scope on. If it did, the rows are thrown away.
There is also no write path, at all. A model that cannot write cannot be talked into writing. Anything with a side effect, like logging a note or creating a task, belongs in the application's own API under the user's own identity, where the application's existing permissions already govern it.
Security Lives in the Database
Before any model-written SQL runs, the service sets a handful of session settings inside the same transaction: the tenant, the user, and the user's scope (their own rows, their own plus their team's, or the whole tenant). They are set with parameterized set_config(..., true) calls, never string interpolation, and the true makes them transaction-local so nothing leaks across a pooled connection.
Every governed view reads those settings. Scope is decided in exactly one view, and every other view joins it. A simplified version:
-- one place decides who "I" can see
CREATE VIEW v_visible_users
WITH (security_barrier = true, security_invoker = false) AS
SELECT u.user_id
FROM users u
WHERE u.tenant_id = current_setting('app.tenant_id')::uuid
AND CASE current_setting('app.scope')
WHEN 'tenant' THEN true
WHEN 'team' THEN u.user_id = current_setting('app.user_id')::uuid
OR u.manager_id = current_setting('app.user_id')::uuid
WHEN 'self' THEN u.user_id = current_setting('app.user_id')::uuid
END; -- no ELSE: an unknown scope is NULL, which means zero rows
-- every domain view joins it instead of re-implementing scope
CREATE VIEW v_orders
WITH (security_barrier = true, security_invoker = false) AS
SELECT o.order_date, o.account_id, o.product_id, o.revenue, o.units
FROM orders o
JOIN v_visible_users v ON v.user_id = o.rep_id;A few details carry most of the weight:
It fails closed, two ways
If the tenant or user setting is missing, the uuid cast raises and the query dies. On a pooled connection a missed setting leaves an empty string behind, not an undefined parameter, and an empty string is not a valid uuid. If the scope is anything other than the three known values, including a different casing, the CASE with no ELSE arm returns NULL and the view returns nothing. The standing rule: never write scope <> 'self', and never give that CASE an arm that widens.
Views run with their owner's privileges
security_invoker = false is the Postgres default, and it's written out anyway because it is the privilege model. The agent's role needs no grant on any base table, so it has no way to reach around the scope join. security_barrier = true stops a caller's own WHERE clause from being pushed below the scope filter. The views are owned by the data team's migration role, never by the service, because an owner can redefine its own view.
Grants are asserted, not assumed
The role migration ends with a block that lists every write grant both roles hold and fails the deploy if one strays outside the service's own tables. The deploy checklist also includes a negative test: connect as the agent role and read an audit table. That query must fail. If it succeeds, containment is gone and the deploy stops.
I considered Postgres row-level security and scope filters in application code first. RLS meant more surface area to reason about per tenant. Filters in application code were a non-starter, because the model writes the SQL, so the model would be writing its own access filter.
A Parse Gate, Not a Regex
The guard is the most important file in the service. It takes the model's statement and returns either rows or a rejection the model can act on. Nothing the model writes can make it raise. In order:
Bind, never inline
Named placeholders become positional parameters. Entity names never appear as SQL literals: the model resolves a name to an id first, and the id is bound.
Parse with the server's own parser
pglast wraps libpg_query, the same parser Postgres uses, pinned to an exact version. Exactly one statement, and it must be a SELECT. SET, DO, COPY, DDL, DML, SELECT INTO, FOR UPDATE and recursive CTEs are rejected by construction, not by pattern.
Allowlist relations and functions
Every relation must be a governed view or a CTE defined earlier in the same statement. Each CTE body is checked seeing only the CTEs before it, so a base table cannot hide behind a shadowing name. Functions come from an allowlist, and session-setting functions get their own rejection message.
Wrap, cost-check, execute
The model's text is sliced out by the parser's own offsets, never edited, and wrapped with a row limit. It runs in a read-only transaction with a statement timeout, after an EXPLAIN that rejects absurd plans.
Assert the context didn't move
Re-read the session settings and compare. On any difference the rows are discarded and the rejection is flagged as a security event. The transaction always rolls back.
BEGIN READ ONLY;
SELECT set_config('app.tenant_id', $1, true); -- x each setting
SET LOCAL statement_timeout = '15s';
EXPLAIN (FORMAT JSON) <wrapped statement>; -- cost gate
SELECT * FROM (<model's SELECT, untouched>) q LIMIT 5001;
SELECT current_setting('app.tenant_id'), ...; -- did anything move?
ROLLBACK;Why step 5 exists: custom settings like app.user_id are user-settable, and set_config() is callable inside a SELECT. The database itself will not stop a statement from rewriting the very context the views scope on. The function allowlist blocks it, and the assertion is the second lock in case the allowlist ever has a gap. This is a hole grants alone cannot close.
Using Postgres's own parser matters more than it looks. A regex, or even a different SQL parser, gives you a check that can disagree with the thing that executes. When the validator and the executor share a parser, "it parses one way here and another way there" can't happen.
Two smaller lessons from tuning it. First, a cost ceiling is a bad latency clock. My first ceiling was about 15x the heaviest query I had measured, and it rejected a whole family of legitimate account queries. Their views carry per-row subqueries that the planner multiplies into huge estimated costs, even though the real scan takes under a second. The cost gate now only stops genuinely absurd plans like accidental cross joins, and the statement timeout is the real latency budget.
Second, a rejection is a message to the model, so write it for the model. Two SQL constructs look legal and get rejected by Postgres itself, costing a full round trip each time. The guard now catches them before execution and includes the fix in the rejection: "select it under an alias and ORDER BY that alias." That isn't security. It turns a wasted turn into a correction.
Knowledge Without Retrieval
Safety answers "can the model hurt anything?" It doesn't answer "will the number be right?" A model reading raw schema will happily count contract line items when the rep asked how many contracts they have. Every number in the answer is individually true, and the answer is wrong.
The fix is a curated catalog: one brief per business domain (10+ of them, including sales, accounts, contracts, products, purchasing groups, distributors, compensation plans and contacts) with a fixed structure. Each view states its grain, meaning what one row represents, and the build fails without it. Rules are numbered, and each one must cite its source: a section of the data team's dictionary or a file and line in the application's code. A rule with no citation fails review. A rule reference that points nowhere fails the build.
An offline build step validates all of it (columns against the view SQL, grains, cross-references, every example query through the same guard) and compiles one ~60K-token document. Then comes the decision that surprises people:
The whole catalog goes into every prompt. It is never selectively loaded and never retrieved by similarity.
About a third of the reference questions span two domains. Loading "the relevant domain" drops rules the question needs, and it drops them silently, which is exactly the failure that sank the retrieval-based system. A 60K-token prompt sounds expensive, but the catalog is a stable prefix, so prompt caching absorbs it. Across a full eval battery, 98.6% of prompt tokens were cache hits. Cost is not a reason to shrink the prompt. Rule bloat is.
Retrieval does still exist, in one place where a miss is harmless. Worked examples (question plus correct SQL) are retrieved per turn, five at a time, as few-shot hints. A missed example costs relevance. A missed rule costs a silently wrong answer. Turning the examples on improved known question shapes noticeably and changed performance on unseen questions by exactly zero. That was a useful result in itself: examples help the model imitate, the catalog teaches it.
Names get the same treatment. The model never writes ILIKE '%acme%'. It calls an entity resolver with a kind and the text the user typed, and the service runs fixed, parameterized SQL through the same governed views, in tiers: exact, separator-insensitive, prefix, token coverage, and finally a gated trigram match. Digits match whole and never approximately, and a lone fuzzy hit never binds silently: it comes back as a spelling to confirm. Two same-named accounts in different cities trigger a clarifying question, not a guess. And because the resolver reads through the governed views, a candidate outside the user's scope can't come back at all.
The Rule Spiral, and Deleting My Way Out
A catalog invites a trap. Every failing eval question tempts you to add a rule. The rule makes that question pass, and the catalog grows. At one point the rule count grew from 290 to 320 in two days, and one rule written to make ranking questions pass ("a list with no number means top ten") quietly broke 17 unseen questions.
So I ran an audit. Every rule went into one of three bins: schema facts (keep), general answering conventions (move into the persona once), and rules written for a specific test question (delete). Of 239 rules, 61 were deleted and 17 were consolidated. The catalog shrank from about 48K to 35K tokens.
That pair of numbers is the most useful thing the project taught me. The deleted rules had been making the tested questions pass while teaching the model nothing it could transfer. They were tailoring, not knowledge. I accepted the lower known-question score as the honest floor. The test for any new rule is now a single question: would a competent analyst reading these views cold need this sentence? If not, it doesn't go in. And adding a rule is paid for by deleting one.
Measuring the Failure a User Can't See
Users can see an error. They can see "I'm not sure, did you mean X or Y?" What they cannot see is a confident answer with a wrong number in it. So the headline metric isn't accuracy in the usual sense. It's the silently wrong rate: answers that looked fine and weren't.
The eval harness has four modes, and they measure different things on purpose:
Offline
No database. Validates the corpus and pushes every reference query through the guard, so a golden the guard would reject never reaches a run.
Baseline
Executes every reference query against real data and records row counts and values. The model is graded against this, value by value.
Model
80 golden questions, 240 paraphrases (one sloppy, one leaning into a word that means something else in another domain), and multi-turn conversations. Gated in CI.
Cold
Questions with no golden behind them, many kept verbatim from what real users typed, typos included. Deliberately ungated, so nobody can tune toward it.
The cold set is protected mechanically. The catalog build never reads it, it never enters the example pool, and a test fails if any cold question shares more than 60% of its words with a known one. A cold question that turns into a paraphrase of a known one has stopped measuring anything.
Column names are never graded. The model writes its own SELECT list, so accounts_aligned and aligned_account_count are the same answer. Users read the answer, not the query.
The $0 that should have been six figures
The worst class of bug here is a query that runs, returns nothing, and gets read as a true zero. One revenue question came back as $0. A paraphrase of the same question returned six figures. The model had bound a list of ids inside ARRAY[...], and since a list already binds as an array, that nested it into a two-dimensional array that matched nothing. The query ran without complaint and returned a perfectly confident zero.
Once an empty result comes back, it looks exactly like a real zero, so the fix had to happen before execution and around it:
- Type-shaped guards that check what a bound value is, never what the question was: a list nested inside an array, or a resolved id compared against a name column.
- An empty-result explainer that reports the statement's own filters, date windows and inner joins, whether each view it read holds any rows at all, and which bound value matched nothing.
- An explicit instruction that a diagnostic re-run is a diagnostic: the user's date window stays, and a figure from a wider re-run is never presented as the narrower one.
Most "regressions" were instrumentation
Twice the headline number jumped by about ten points for reasons that had nothing to do with the model. The first time, one view on the eval environment was two revisions behind the catalog, which produced dozens of query failures blamed on the model's SQL. The second time, a handful of reference questions had zero-row baselines and had been passing 0 == 0 for free. When a data fix made them real, a genuine improvement showed up as a regression.
Both produced permanent fixes to the harness rather than to the catalog: a schema preflight that compares the live views against what the catalog claims at every entry point, a source hash that fails loudly if the compiled catalog is older than its sources, and zero-row baselines that are reported, never quietly graded. Run-to-run churn turned out to be around ten turns either way, so I stopped reading anything into smaller deltas.
Shipping It
The earlier system ran on Lambda. This one runs on ECS Fargate, with Aurora PostgreSQL behind RDS Proxy. A turn streams for several seconds over Server-Sent Events and holds a database connection the whole time, and Lambda behind API Gateway handles neither of those well.
RDS Proxy is a requirement
The guard deliberately opens a fresh connection per statement, so model SQL never inherits a pooled session that could carry state between statements. A turn that runs six queries is six connect/disconnect cycles. The proxy absorbs that churn. It does not enforce security; the roles, the parser and the assertion do.
Timeouts are part of correctness
Between the first streamed line and the first answer token, the connection can sit quiet for several seconds while a query runs. Any idle timeout in the path that is shorter than the turn budget cuts healthy turns, and only on slow questions, which is the worst way to find out. Draining tasks get enough stop time to finish the turn they're on.
The catalog ships in the image
A new catalog is a new image and a deploy, tagged with the git sha, never latest. Every turn records which catalog version answered it, so "which version said that?" always has an answer.
Logs stay outside the boundary
Row data, questions, answers and SQL never go to the log pipeline. They live in an audit table behind the same database permissions as the data itself. A log line carries a turn id, not a sample row "for debugging".
Where it stands: about 90% accuracy across a graded battery of 270 turns (measured as answers with no silently wrong number), a ~5 second median response, zero errored turns, and 890+ offline tests covering the guard's rejection classes, the resolver, streaming, and the identity contract. The accuracy gate I set is far stricter than that, and the system doesn't pass it yet, deliberately. A confident wrong number is the one failure a user cannot detect, so it's the one I hold to the hardest standard.
What I'd Do Next
Multi-Turn Conversations
Single questions are much stronger than long conversations. Follow-ups that lean on earlier results are where most remaining errors live, and the next round of work is there.
A Real Security-Event Alarm
A settings-moved rejection discards the rows and goes back to the model, but nothing pages a human yet. Containment works either way; visibility should match it.
Lower Judge Variance
Multi-turn and cold answers are graded by an LLM judge whose phrasing sensitivity moves scores by a few turns. That noise has to come down before small deltas mean anything.
Actions as Proposals
Notes and tasks, done as a proposal the user confirms, executed by the application's own API under the user's identity. The agent still never gets a write grant.
Updates
New lessons, numbers and design changes get added here as the system evolves.
First version
The architecture as it stands: governed views, the SQL guard, the whole-catalog prompt, the rules audit, and the eval harness, with results from the latest graded battery.
Built at Work
A production system in a private codebase, so there's no public repo. Happy to talk through the design in more depth.