Build a scoped evidence memory with SQLite and a ranking seam

Pattern 08 companion: Python example · Behavioral tests · Example license

Run the evidence retrieval fixture

Download pattern_08.py and the companion tests together. Use Python 3.11 or newer: py -3 -B pattern_08.py on Windows, or python3 -B pattern_08.py on macOS. The module uses only the standard library, creates a temporary database and prints deterministic JSON. Windows execution was checked; the macOS command is guidance for a separate local check.

The demonstration inserts a current queue runbook, an expired incident record and another team's private working note. It queries team-a at revision v1 and logical time one hundred. Expect one hit with the citation runbook://queue/v1. Those citation schemes are synthetic identifiers, not live URLs. They make provenance visible without uploading private documents or invoking a search service.

Architecture and request sequence

Component diagram for Build a scoped evidence memory with SQLite and a ranking seam — Original reference architecture by Sandeep Belgavi. Arrows show the responsibilities and transitions described below.

Request sequence for Build a scoped evidence memory with SQLite and a ranking seam — Illustrative sequence for this design; failure and recovery variants are explained in the article.

Understand the storage key

EvidenceStore creates a table keyed by tenant and record identifier. Its fields include revision, kind, body, source and expiry. The put method validates the kind against working, incident and runbook; requires nonempty strings for the identifying and content fields; and checks that expiry is a nonnegative integer. It then inserts or replaces the active row in one local operation.

Replacement is a deliberate simplification. It does not retain the previous body or provide a tamper-evident history. If an incident report must remain reproducible after a runbook update, store immutable revisions and record the exact revision used by the report. Keep the current pointer separate from the historical record. Do not describe the reference's source string as proof that the source exists or that a future reader will retain permission to open it.

Follow retrieval before scoring

The retrieve method requires a nonempty tenant and revision and restricts the result limit to one through ten. Its SQL selects only rows matching those boundaries whose expiry is strictly greater than the supplied time. A record expiring exactly at the current time is excluded. Passing time explicitly makes the boundary deterministic in tests and prevents a clock-dependent demonstration from producing different results tomorrow.

Only after this selection does the method call scorer.score for each candidate. The adapter receives the query and body, returns a relevance number, and records with positive scores are ranked. Ties are broken by identifier, making repeated runs predictable. This separation is useful when introducing a provider later: eligibility remains owned by application code, while the provider contributes only a ranking signal inside the allowed candidate set.

Use the adapter seam honestly

TokenScorer extracts lowercase alphanumeric tokens and counts their intersection. It does not understand synonyms, causality or operational intent. A query for latency may miss a document using only response time, and a document repeating an unrelated keyword may still rank. Those limitations are visible and testable, which is preferable to implying semantic intelligence that the module does not implement.

A replacement scorer could call an embedding service or reranker, but would need timeout handling, output validation, batching and cost accounting. Keep a deterministic fallback only if its degraded behavior is acceptable for the task. Otherwise return an explicit unavailable result rather than silently substituting a different retrieval policy. Never send filtered-out candidates to a remote ranking provider, and document how the provider handles the eligible text it receives.

Inspect deletion and the tests

The forget method deletes one identifier within one tenant. The scoped-deletion test inserts the same identifier for two tenants, removes one and verifies the other survives. Other tests cover tenant isolation, revision filtering, expiry equality, tie order, no matches, reopening storage, hostile text remaining inert, limit validation and the expected demonstration. Run them with py -3 -B -m unittest test_pattern_08, or the equivalent python3 invocation.

The injection fixture deliberately returns text containing a malicious instruction, then confirms storage remains unchanged. This proves only that the deterministic retriever does not execute document text. It does not prove that a future language model will ignore that instruction. Once a model adapter is attached, add adversarial end-to-end tests at the context and tool boundaries, while retaining the storage-level checks as independent evidence.

A reference deployment boundary

A deployed service would authenticate the caller, derive tenant scope from trusted identity and translate the caller's task into an authorized retrieval request. Do not accept a free-form tenant value from model-generated tool arguments without binding it to that identity. Add payload limits, durable storage management, backup policy and access-controlled source resolution. These are deployment requirements, not features supplied by the teaching script.

The memory concepts documentation offers a framework comparison for short- and long-lived state. Our implementation remains framework-independent and deliberately lexical. Before using it to support incident answers, evaluate whether returned evidence is sufficient, current and correctly cited. An empty result should lead to a controlled next step such as gathering fresh telemetry; it should not be converted into an unsupported statement that no problem exists.