38% of an analyst's questions get "no records found" when the data is right there
Your text-to-SQL agent has a failure mode that logs nothing, alerts nothing, and returns a confidently wrong answer. I finally put a number on how often it can happen, and on a small schema with an ordinary role split th
Your text-to-SQL agent has a failure mode that logs nothing, alerts nothing, and returns a confidently wrong answer. I finally put a number on how often it can happen, and on a small schema with an ordinary role split the number is 38.5%.
The failure
Someone asks a question whose answer lives in a table they are not allowed to read.
The model is handed the whole schema, because that is what nearly every text-to-SQL stack does. It writes a perfectly correct query against that table. The query executes. Row-level security removes every row. The application receives an empty result set and reports, truthfully as far as it knows, that there are no records.
"No rows matched" and "you are not allowed to see the rows that matched" arrive at the application as the same thing: an empty list. There is no exception to catch, so no alert fires, no retry triggers, and nothing appears in the logs to review later.
And the two readings lead somewhere different. "No unpaid invoices" is an answer someone might act on. "You cannot see the unpaid invoices" is a reason to go ask someone else. Collapsing the second into the first is not a degraded answer. It is a confidently wrong one.
Why nobody measures it
Because there is nothing to count. Every observable signal -- exit status, row count, latency, logs -- is identical to the honest empty result.
So I stopped trying to catch it at runtime and defined it structurally instead.
A question is unanswerable for a caller when the tables its correct answer needs include at least one the caller may not read.
That needs gold labels -- question to tables -- which is the same thing Spider and BIRD already give you. What it does not need is an LLM. No model is run and no SQL is executed. The rate is a property of your schema, your role model and your question mix, not of whichever model you happen to have wired up this week.
silent-denial rate = unanswerable questions / total questions
The number
A 42-object demo schema. Thirteen labelled questions. An unremarkable role model: finance tables to a finance role, salary to payroll, everything else open.
caller answerable unanswerable silent-denial
analyst 8 5 38.5%
finance 12 1 7.7%
hr 9 4 30.8%
cfo 13 0 0.0%
detected before any SQL runs: naive 0 of 10 scoped 10 of 10
Two questions in five that the analyst asks come back "no records found" while the data sits there. Not because retrieval failed. Not because the model is weak. Because the permission check happens after generation instead of before it.
The last line is the part I care about most. A full-schema pipeline detects none of them, because an empty result is its only signal and the empty result is indistinguishable from the honest one. A pipeline that scopes the schema by caller identity detects all ten, for free, before any SQL is written: the table the answer needs is simply not in the set this caller may see, so the system knows the question is unanswerable and can say so.
That is not a smarter model. It is the same information, one step earlier.
When you have no gold labels
Most databases do not have them, and a useful answer is still available.
For each restricted table, build a probe question out of that table's own words -- its name, its hint, its description -- and ask whether an unscoped selection puts it in the top k. If it does, then a question phrased the way your schema describes itself lands on a table the caller cannot read, and a full-schema pipeline will write SQL against it.
On the same schema, all five restricted tables come back at rank 1.
The probe is generated from the schema rather than written by hand, deliberately. A hand-written probe proves only that its author could think of a question, which makes the result a property of the author.
The bug I shipped in the first draft
Worth writing down, because it is the failure this whole area keeps producing.
My first reachability run reported 0 of 5 reachable. Total confidence, clean output, completely wrong.
The cause: I ran the probe with principal=None. But None is not "unscoped" -- a caller with no roles is denied, because absence of a role is absence of permission. So the probe removed exactly the tables it was looking for, and reported that nothing was reachable.
A measurement whose apparatus deletes the thing being measured will report zero, every time, with no error. Which is the same shape as the bug the tool exists to find. There is now a named regression test for it, and every test in the suite is paired: one case where the metric must be zero, one where it must not, so the metric can be shown to move at all.
What this does not claim
- A reachability "no" is weak evidence. It means a table is hard to reach by its own vocabulary, not that no question reaches it. Only a gold set gives you a rate.
- No rate here is a model score. Whether a particular LLM picks the denied table on a given run is a different question. This measures whether the path exists, which is the part you can actually fix.
- A bad label is not a finding. Gold tables the catalog has never heard of are reported separately and excluded from every rate, so a typo cannot inflate your number.
- This is not a security control. It is a measurement. Your database's grants and RLS policies remain the thing that enforces access. What scoping buys you is that the model stops generating queries RLS then has to reject -- which is where the silence comes from.
Reproduce it
The whole measurement is about fifteen lines on top of schemagate, which is the thing that already knows your catalogue and its roles. Nothing else is needed and no model is called:
from schemagate import Catalog, Principal
from schemagate.models import allowed
def unanswerable(catalog, gold, principal):
"""Questions whose gold tables include one this caller may not read."""
leaf = lambda n: n.rsplit(".", 1)[-1]
denied = {leaf(q) for q, d in catalog._docs.items()
if not allowed(d.roles, principal)}
return [q for q, needs in gold if {leaf(n) for n in needs} & denied]
# your database, your roles, your labelled questions
cat = Catalog().bootstrap("postgresql://host/db")
cat.restrict("hr_compensation", ["payroll"])
gold = [("salary and pay grade by employee", {"hr_compensation", "hr_employee"}),
("headcount per department", {"v_employee_headcount"})]
hits = unanswerable(cat, gold, Principal("okta:analyst", frozenset()))
print(f"silent-denial rate: {len(hits)}/{len(gold)} = {len(hits)/len(gold):.1%}")
On the two questions above that prints 1/2 = 50.0% for the analyst and 0/2 = 0.0% for a caller holding payroll, which is the sanity check worth running first: if the number does not move when you change the roles, it is not measuring the roles.
The fuller version I ran for the table -- multiple callers, the unlabelled reachability mode, and the guard that stops a mistyped gold label inflating the rate -- is a few hundred lines around that core. Say so in the comments if you want it packaged and I will put it on PyPI; I would rather know someone will run it than publish another thing nobody installs.
If you want the fix rather than the measurement, the scoping in that last column is what schemagate does -- it takes the caller's identity at the schema-selection step instead of filtering rows afterwards. pip install schemagate, Apache-2.0.
I would genuinely like to see this number from a real warehouse rather than a demo schema. If you run it on yours, post what you get -- especially if it is 0%, because I would like to know what a role model that avoids this looks like.
Originally published by Dev.to AI. Aggregated on AIWithGhost for educational purposes โ full credit and traffic to the original publisher.