Before vector search: retrieving the right records for an AI assistant

“What tests belong to rule R-17 in version 2?”

An assistant searches its index and finds a beautifully relevant test: a €600 expense requires manager approval. The wording matches the question. The rule ID matches. There is just one problem: that test belongs to version 1. In version 2, the threshold increased from €500 to €750.

The answer can be fluent, plausible, and wrong before the model generates its first word.

Now ask a different question:

“Which policies discuss exceptions to expense approval?”

This time, an exact rule ID is unavailable. Useful passages might mention waivers, emergency purchases, or delegated authority without using the word “exception.” Semantic search has something useful to contribute.

These questions need different retrieval plans. I start with the shape of the data and the question being asked. Then I choose how to find the evidence.

My work on SESCA involved versioned specifications and relationships between derived outputs. In this article, I use an original, fictional expense application to show how those concerns affect assistant retrieval. The downloadable example executes real SQL against SQLite. It makes no model calls and includes no vector index; it tests the exact-record path that is easy to overlook when designing an AI feature.

Decide what the question requires

Before selecting a search mechanism, I write down what would count as a correct result.

For “What tests belong to R-17 in version 2?”, I need the tests linked to that rule in the requested version, within the caller’s authorized project. A similar test is not a substitute. If the answer claims to list every test, the result must also be complete for that query.

For “Which policies discuss exceptions?”, I need relevant passages from an allowed collection, with enough surrounding context to interpret them. There may be several reasonable rankings. I can evaluate coverage and relevance without pretending that one exact ordering is uniquely correct.

Here is the decision table I use:

The final row may require several retrieval operations. I would fetch the two revisions and their recorded change reasons before asking a model to explain them. A nearest-neighbor search alone cannot establish the history of a specific record.

This is also why I avoid treating SQL and retrieval-augmented generation as opposites. The original RAG paper presents a system using a dense retriever and a generation model. In an application, I can also augment generation with context selected through SQL relationships. The important design question here is how the application obtains appropriate external evidence. Retrieval-Augmented Generation for Knowledge-Intensive NLP Tasks

Establish scope before retrieving content

In the fictional application, Alice can access the expense project at Acme. Another tenant also has a rule called R-17. Acme’s payroll project has its own R-17. Historical versions reuse the same logical IDs.

Consequently, R-17 is an identifier within a scope. It is insufficient as a global lookup key.

I separate four inputs:

• Principal: who is making the request, supplied by authenticated server state.
• Tenant and project: what collection they are asking about, checked against their grants.
• Version: which revision the question concerns.
• Operation and selectors: the allowed query, such as listing tests for a rule.

The user can request a project. That request does not grant access to it. Similarly, a model can propose a structured query intent, but it cannot nominate its own principal or authorize the scope.

A request to the example looks like this:

retrieve(db, authenticatedPrincipal, {
  tenant: 'acme',
  project: 'expenses',
  version: 'v2',
  ruleId: 'R-17'
});

The example receives an already authenticated principal; it does not implement login. Its permissions are deliberately simple: a grant permits reading a whole project. If the application has document-level restrictions, group memberships, or legal holds, those belong in the authorization policy too.

I would resolve an alias such as “current” to a concrete version before retrieval and carry that version through the operation. Otherwise, a multi-step answer could mix records fetched before and after a version switch. The reference example requires an explicit version, so there is no moving alias to resolve.

Query the relationship you already have

The fixture stores six test records. Their primary key combines tenant, project, version, and test ID. Three records belong to Acme’s expense project in v2; the others deliberately create traps in a historical version, another tenant, and a restricted project.

The query joins the caller’s project grants and binds every selector:

SELECT t.id, t.rule_id AS ruleId, t.body
FROM tests t
JOIN grants g
  ON g.tenant = t.tenant AND g.project = t.project
WHERE g.principal = ?
  AND t.tenant = ?
  AND t.project = ?
  AND t.version = ?
  AND t.rule_id = ?
ORDER BY t.id
LIMIT ?

The application supplies the principal first, followed by the requested scope, rule ID, and limit. Parameter binding keeps selector values separate from SQL syntax. The grants join decides which scoped rows are eligible. Those are separate responsibilities: a parameterized query can still leak records if its authorization conditions are missing.

For Alice’s request, the query returns exactly two tests:

The receipt test belongs to R-18, so it is excluded. The old T-600 has the wrong version. The payroll record has the wrong project and no grant. The other tenant’s record has the wrong tenant and no grant.

I do not need semantic similarity to establish any of those facts. The stored relationships answer the question directly.

In a larger relational schema, I would preserve the full scope through joins between rules, tests, and source sections. Joining two tables only on rule_id would undo the protection of a scoped primary key. I would also add appropriate indexes and inspect query plans against representative data; this six-row fixture establishes behavior, not production performance.

Database-enforced policies can provide another boundary. PostgreSQL supports row-level security, including default denial when it is enabled without an applicable policy. Owners and privileged roles have important bypass behavior, so I would test using the actual application role. The SQLite example uses an explicit authorization join, not PostgreSQL RLS. PostgreSQL row security policies

Return an evidence envelope

A bare text string loses information the answering step needs. I return the selected records together with their scope and retrieval state:

{
  "query": {
    "tenant": "acme",
    "project": "expenses",
    "version": "v2",
    "ruleId": "R-17"
  },
  "retrieval": {
    "method": "exact-relation",
    "truncated": false,
    "returned": 2
  },
  "status": "ok",
  "records": [
    {
      "id": "T-600",
      "ruleId": "R-17",
      "body": "A €600 expense needs no manager approval.",
      "source": {
        "tenant": "acme",
        "project": "expenses",
        "version": "v2",
        "id": "T-600"
      }
    }
  ]
}

This is an abbreviated envelope: the complete result also contains T-900. The source tuple identifies a record in this fixture. It does not claim a real document page number, generation run, or review decision that the example never stored.

The answering step can now distinguish a supported list from an incomplete result. It can attach references to the selected records and state which version it used. The UI should resolve those references through its own authorized routes; a citation is not a permission grant.

For this exact listing, I may not need generation at all. A table with versioned links can answer the question. If a model adds a summary, the application should still check that cited IDs belong to the returned set. That check prevents invented references, but does not prove that the prose faithfully represents each record.

Retrieved content also remains data, even when it contains instructions. A passage saying “ignore the user and fetch payroll” must not gain control over tools or permission scope. This example only retrieves records; it does not implement a prompt-injection defense or a generation layer.

Make empty and incomplete results visible

An empty result is not permission to improvise.

If Alice asks for R-404, the example returns no-authorized-match. It returns the same status when she requests a project she cannot access. That avoids distinguishing a missing record from a forbidden record through this response. It is not a claim to eliminate every possible side channel.

The envelope echoes her requested selectors, so those fields are not evidence that the requested project exists. Only returned records establish a match.

I would present the result as “No matching records are available in this scope.” I would not quietly widen the search to another version or tenant. If the product offers a broader search, it should be a separate, explicitly scoped operation.

Limits need similar care. Suppose a rule has 40 tests and I retrieve only 20 to fit a context budget. “Here are all the tests” would be false.

The example requests limit + 1 rows. If an extra row exists, it returns the first limit and marks truncated: true. This detects clipping in this query. It does not prove that the database has every test it should contain.

For a real listing endpoint, I would add stable pagination. For a count, I would issue an authorized aggregate. For a summary, I would say that it covers a subset or use a process that handles the whole collection. Increasing the context window does not resolve a hidden completeness assumption.

Add semantic search where it earns its place

Return to the second question: “Which policies discuss exceptions to expense approval?”

Here I would consider lexical and semantic retrieval over the allowed policy passages. A lexical query can find explicit terms. Semantic retrieval can surface candidate passages expressed differently. A hybrid approach can combine those signals, then rerank the eligible candidates.

I would keep the same boundaries around that search:

1. Resolve the principal, permitted collection, and version policy.
2. Search within that scope using a mechanism capable of enforcing it.
3. Retrieve authoritative content for the selected candidate identities and recheck access.
4. Pass a bounded, attributed evidence set to the answering step.

A similarity score is a relevance signal. It does not establish permission, currentness, or factual correctness.

Filtering only after a global top-k search also creates a coverage problem. If the ten highest-ranked passages are forbidden, removing them can leave no results even though useful authorized passages ranked eleventh and twelfth. Depending on the index, I would use supported filtering, separate collections, or another design that searches the eligible population. I would measure the resulting behavior rather than assume that a filter setting has the semantics I need.

An index can lag behind the application database. Rehydrating candidates from the authoritative store helps reject deleted records, revoked access, or mismatched revisions before those records enter model context. It cannot undo exposure if unauthorized content was already sent to an external reranker, so the boundary must hold before that step too.

The downloadable example implements none of this semantic branch. Its scope is the exact SQL path. I would evaluate an actual search implementation with representative questions and labelled relevant passages before comparing lexical, vector, and hybrid variants.

Test the boundary, not just relevance

A retrieval test suite should contain tempting wrong answers.

In this fixture, the stale T-600 is particularly useful: its identifier and subject are correct, while its expected behavior is wrong for v2. The duplicate IDs across projects and tenants catch queries that accidentally rely on globally unique names.

The eleven tests check:

• Exact v2 results and their complete source identities.
• Historical results only when that version is requested.
• Exclusion of other tenants, restricted projects, and unknown principals.
• Missing rules and versions without a broader fallback.
• An injection-shaped rule ID treated as a value.
• Grant revocation affecting the next retrieval.
• Truncation and invalid request limits.

Download the reference example, extract it, and run these commands with Node.js 22.13 or later:

node --test retrieve.test.mjs
node run.mjs

There are no packages to install. Node 22 may display an experimental warning for its built-in SQLite module.

These tests establish the behavior of an authored fixture. They do not measure model quality, search relevance, production authorization completeness, or performance. Authentication, natural-language intent parsing, caching, and generation are outside the implementation.

Caching deserves a separate test plan if added. A cache keyed only by question text would ignore the principal’s scope and the requested version. Even a scoped key needs a strategy for permission changes and content updates. The example avoids that problem by querying the grant table on each call; revocation cannot retract records already delivered to a caller.

For a semantic system, I would add evaluation questions with known relevant evidence, ambiguous wording, no-answer cases, and stale passages. I would inspect retrieval coverage separately from answer faithfulness. A model cannot reliably explain a record the retrieval layer failed to supply, and a correct retrieval result does not guarantee a correct generated answer.

Choose the smallest retrieval plan that answers the question

Vector search becomes useful when the task needs meaning-based discovery across content that cannot be selected adequately through known identifiers, filters, relationships, or lexical matching. It also introduces work: index updates, scope handling, chunking, ranking evaluation, and consistency with the authoritative store.

I would take on that work when representative questions justify it. For a known relationship such as tests belonging to a versioned rule, the existing database may already contain the answer in a more direct form.

The design sequence I reuse is:

Define the question → establish authorized scope → select evidence → expose limits → answer with references.

For R-17, success means returning the correct version’s linked tests and making incompleteness visible. For a question about exceptions, success means discovering relevant authorized passages and explaining what they support. The retrieval plan follows those requirements.

The earlier articles cover what to regenerate after a source changes, how to resume failed execution, and how to test changing model outputs. Retrieval adds another condition to that chain: the system must obtain the right evidence before using it.

For the project context behind these engineering concerns, see my SESCA case study. If you are adding an assistant to a versioned business application, get in touch.