More retrieval patterns

One SQL plan returns the finished table

• One SQL statement retrieves, filters, joins, aggregates, and returns the finished table.
• A count, group by, or window function runs over the full candidate set inside the query plan.
• Hybrid retrieval hands back the rows a single retriever drops: the exact error code and the paraphrase of it, fused into one ranking.

Enforce the cohort in the query

The query defines and enforces the cohort: this tenant, this quarter, these priorities. The model receives the resulting rows.

SELECT   _id, subject, score
FROM     hybrid_search('tickets', 'body', 'disk full on ingest',
                       'embedding', :q, 500)
WHERE    tenant_id = :tenant                        -- enforced
  AND    created_at >= date_trunc('quarter', now())
  AND    priority IN ('p0', 'p1')
ORDER BY score DESC LIMIT 20;

-- → exactly this cohort, nothing outside it

Join summary-level and chunk-level retrieval

Two retrieval granularities, one statement. A table of document-summary embeddings picks the right documents; a chunk-level search picks the right passages inside them; the join ranks on both.

-- stage one: which documents are about this, by their summary embedding
WITH docs AS (
  SELECT _id AS doc_id, score AS doc_score
  FROM   vector_search('doc_summaries', 'embedding', :q, 50)
)
-- stage two: the best passages, but only inside those documents
SELECT   c._id, c.doc_id, c.text,
         round(0.4 * d.doc_score + 0.6 * c.score, 4) AS blended
FROM     vector_search('chunks', 'embedding', :q, 500) c
JOIN     docs d ON d.doc_id = c.doc_id              -- two searches, one join
ORDER BY blended DESC
LIMIT    10;

-- → whole-document context and passage precision, in one pass

The full query surface →

Route only borderline scores to an LLM

A CASE expression can send only the borderline score band to an LLM for verification. The application supplies both thresholds.

SELECT   _id, subject, score,
         CASE
           WHEN score >= :trust_threshold THEN 'trust'
           WHEN score >= :check_threshold THEN 'llm_check'
           ELSE 'drop'                            -- never reaches the context window
         END AS action
FROM     hybrid_search('tickets', 'body', :question,
                       'embedding', :q, 200)
WHERE    created_at > now() - interval '90 days'
ORDER BY score DESC;

-- → only rows between the two thresholds require a model check

The routing thresholds live in the query, so they are versioned, reviewed, and tuned like the rest of your SQL.

Debug the retriever before it ships

• Join bm25_search against vector_search on _id and the rows on only one side are what a single retriever would have silently missed.
• Run it per query class in CI: when the "meaning only" bucket grows, your embeddings and your vocabulary are drifting apart, and you find out from a query.

The disagreement query →

One consistent read, even while writes land

• Every query runs on one pinned snapshot, so a count and the rows behind it always come from the same version of the table, even while writes land concurrently.
• A retained snapshot lets you rerun yesterday’s query against yesterday’s data when the result changes.

Retrieval pattern questions