Private RAG Across SQL Databases and Document Repositories

Ask an internal assistant “what is our retention obligation for closed claim files?” and you have asked a document question. Ask it “how many closed claim files are past their retention date?” and you have asked a database question. Ask it “which of our closed claim files are past the retention obligation in the policy?” and you have asked both, in one sentence, and the answer requires stitching a numeric result to a cited paragraph.

Most enterprise RAG projects begin with the first kind of question, because policies and manuals are where the obvious pain is. They meet the second and third kinds within weeks of going live, because users do not think in terms of where their answer happens to be stored. What follows is usually one of two mistakes: bolting structured data into the vector index because that is the machinery already built, or handing a model open SQL access to a production schema because that is the fastest path to a demo.

Both are avoidable. Spanning documents and relational data is a design problem with a reasonably well-understood answer, and the answer is mostly about restraint on one of the two paths.

Why embedding rows is not the general solution

Vector retrieval returns things that resemble the query. That is exactly right for prose, where the answer is a passage and relevance is semantic. It is a poor fit for most questions people ask of a database, because those are about sets and arithmetic rather than resemblance.

No amount of embedding quality lets similarity search compute a sum, count a filtered population, or rank by a numeric field with a date constraint. Worse, it fails silently: it returns rows that look topically related, the model summarises them, and the user gets a confident number derived from an arbitrary subset. A wrong document citation is visible. A wrong aggregate is not.

There is a legitimate narrow case. Where a record reads like a short document — an incident description, a case note, a product specification — projecting it into text and indexing it works well, and it is often the best way to make free-text fields buried in a table findable at all. That is a document problem that happens to live in a database.

For everything else, the retrieval mechanism has to be a query. The design question is who writes it.

Three patterns, applied deliberately

1. Document projection. Selected records are rendered into text — a templated narrative per row or per logical entity — chunked, embedded, and indexed alongside real documents, carrying the source table, key, and timestamp as metadata. Use this for the free-text-heavy records above, and for cases where a user needs to find the relevant handful of entities before doing anything precise with them. Do not use it as a substitute for aggregation.

2. Governed query tools. Rather than exposing a schema, expose a small set of reviewed, parameterised queries as tools: open_claims_by_age(days, branch), outstanding_balance(account_id, as_of). Each is written once by someone who knows the schema, reviewed like any other production query, and executed with bound parameters under a scoped identity. The model chooses which tool to call and with what arguments — a much smaller decision than authoring SQL, and one whose failure modes are visible in the arguments rather than hidden in a join.

3. Constrained generation over a curated surface. Where the question space is too broad to enumerate, allow generated SQL — but against a purpose-built reporting view rather than the operational schema, read-only, with row limits, statement timeouts, and mandatory display of the executed query next to the result. The view is where the semantics live: sensible column names, resolved joins, pre-applied business definitions. Generating a correct query against five well-named columns is a different task from generating one against four hundred tables named after a 1990s mainframe.

Most enterprise deployments need all three, with the second doing the majority of the work.

Be honest about text-to-SQL accuracy

The temptation to skip straight to pattern three is strong, and the evidence does not support it. BIRD, the benchmark built specifically to test text-to-SQL against real databases, spans 95 databases across more than 37 professional domains and reports human performance at 92.96% execution accuracy. Leading systems sit some distance below that — on schemas that are smaller, better documented, and cleaner than the ones an enterprise actually runs. The benchmark’s own framing names the reasons: values retain their original, frequently messy formats, and many questions require domain knowledge that is nowhere in the schema.

That last point is the one that matters most in practice. The gap between a plausible query and a correct one is usually institutional knowledge, not SQL syntax: which status codes count as “open”, whether cancelled policies belong in the denominator, which of three date columns represents the event the business means. A model cannot infer those, and no reasoning improvement will supply them. Encoding them once — in a view, in a query tool, in a documented definition the model is given — is the actual work. Recent database-research work has also found meaningful annotation error in these benchmarks, which cuts both ways: headline scores deserve less weight than the structural point that real schemas are harder than test ones.

Routing, and what the router needs

With both paths available, something must decide which to use. A classifier in front of retrieval is enough for most traffic: definitional and procedural questions go to document retrieval, quantitative and record-specific questions go to the query tools, and mixed questions fan out to both and compose.

Two rules keep this from becoming a source of quiet errors. First, when the router is unsure it should ask rather than guess — a clarifying question costs five seconds and saves an answer nobody can trust. Second, mixed answers must keep their provenance separate: the number carries the query and rows it came from, the policy statement carries its document citation. Merging them into one fluent paragraph with a single vague source destroys what made the answer verifiable.

Routing is also a governance boundary, not just an efficiency one — the same distinction that separates agent routing from model routing. A question routed to the database path executes code against production data; one routed to document retrieval does not. That asymmetry deserves its own logging.

Permissions, enforced twice

The most common serious defect in a mixed pipeline is a permissions model that exists on one path only.

On the database path, queries should execute under an identity scoped to the rows and columns the requesting user is entitled to see — row-level security or an equivalent, enforced by the database. A broad service account with a filter injected by the model is not access control; it is a filter that fails open the first time the model is confused or manipulated.

On the document path, the equivalent control is metadata filtering applied at retrieval time, so material outside the user’s scope is never a candidate. Metadata filters in private RAG are what make department and customer separation real rather than nominal.

Both paths must resolve to the same user identity and the same entitlement model. If one is enforced in the platform and the other in a prompt, the pipeline behaves as though only the weaker one exists.

Freshness, and the failure nobody notices

Documents change monthly. Database rows change continuously. A pipeline spanning both inherits two staleness problems, and the one that hurts is the quiet one: an embedded projection of a record that was accurate at index time and is now wrong.

The mitigations are unglamorous. Anything time-sensitive is answered by a live query, never by an embedded projection. Projections carry an as-of timestamp that is surfaced in the answer. Re-indexing is driven by change data capture rather than a nightly full rebuild. And index lag is a monitored metric with a threshold, not something discovered when a user disputes a figure — the operational side of synchronising enterprise knowledge sources.

How VDF AI builds this

VDF AI treats structured and unstructured retrieval as one governed pipeline rather than two projects. Data Suite connects relational sources — see connecting an enterprise database for private RAG and the supported database types — so a connection is a registered, scoped asset rather than a credential in an agent’s configuration. Query access is exposed as governed tools, not open schema access, and document retrieval applies metadata filters resolved from the requesting user’s entitlements. VDF AI Agents compose across both paths and return answers with their provenance intact: the query and rows behind a number, the citation behind a statement. Every retrieval and query is logged against the user and workflow that caused it, and because the platform runs inside your own environment, the embeddings, indexes, queries, and logs stay within the boundary the underlying systems already sit behind.