A retrieval baseline makes the handoff visible.
Imagine a project assistant answering who owns a launch checklist. Our three invented notes include a launch record, an archive record for the same project, and another project. Search should preserve source IDs so you can check which note supports the answer.
We will inspect the retrieval stage before any answer generation. This is a practical starting point for retrieval-augmented generation (RAG): select evidence first, then assess any answer against it. The exercise below implements only local lexical retrieval.
The authored facts are simple: Harbor’s launch checklist belongs to Morgan; its review meeting is October 14, 2026; pending review does not authorize publication. Riley owns archive maintenance. Alex owns Cedar’s review. All records are fictional.
Use an empty practice folder.
Install Python, open PowerShell in an empty practice folder, and check the interpreter:
python --version
python -c "import sqlite3; c=sqlite3.connect(':memory:'); c.execute('CREATE VIRTUAL TABLE probe USING fts5(body)'); print(sqlite3.sqlite_version)"We ran Python 3.13.15 with SQLite 3.50.4 on Windows. The code uses only the Python standard library; no pip installation, AI account, or API key is required. FTS5 availability depends on your SQLite build. If the probe reports no such module: fts5, use a Python distribution whose bundled SQLite includes it; this guide does not enable or load external extensions.
Installing Python and downloading this exercise require network access. The search script itself contains no network or model call and builds its index in memory.
Download the exact exercise.
Download the exercise ZIP, extract it, and read the code and notes:
Invoke-WebRequest -Uri 'https://jabsystems.io/ai-anonymous/Social/search-before-chat-exercise.zip' -OutFile .\search-exercise.zip
Expand-Archive -LiteralPath .\search-exercise.zip -DestinationPath .\exercise
Get-Content -LiteralPath .\exercise\search-evidence.py
Get-Content -LiteralPath .\exercise\search-notes.jsonThe ZIP includes the Python script, three notes, the observed report, a README, and an optional untested prompt. The following source is the complete search script:
"""Search three authored notes locally. No model or network calls."""
import argparse
import json
import re
import sqlite3
import sys
from pathlib import Path
def search(notes, query, limit=3):
terms = re.findall(r"[^\W_]+", query, flags=re.UNICODE)
if not terms:
raise ValueError("Query must contain at least one letter or number")
# Each term is a quoted FTS phrase; AND/OR are treated as words.
expression = " AND ".join('"' + term + '"' for term in terms)
with sqlite3.connect(":memory:") as db:
db.row_factory = sqlite3.Row
db.execute("CREATE VIRTUAL TABLE notes USING fts5(source_id UNINDEXED, title, body)")
db.executemany("INSERT INTO notes VALUES (?, ?, ?)",
[(n["source_id"], n["title"], n["body"]) for n in notes])
rows = db.execute("""
SELECT source_id, title, body,
snippet(notes, 2, '[', ']', ' ... ', 24) AS excerpt,
bm25(notes) AS score
FROM notes WHERE notes MATCH ?
ORDER BY score, source_id LIMIT ?
""", (expression, limit)).fetchall()
return {"query": query, "match_expression": expression,
"sqlite_version": sqlite3.sqlite_version,
"scope": "authored notes; lexical search only",
"hits": [dict(row) for row in rows]}
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument("notes", type=Path)
parser.add_argument("query")
parser.add_argument("--limit", type=int, choices=range(1, 11), default=3)
args = parser.parse_args()
try:
notes = json.loads(args.notes.read_text(encoding="utf-8-sig"))
if not isinstance(notes, list) or not notes:
raise ValueError("Notes must be a nonempty JSON array")
ids = set()
for note in notes:
if not isinstance(note, dict) or any(
not isinstance(note.get(k), str) or not note[k].strip()
for k in ("source_id", "title", "body")
):
raise ValueError("Each note needs nonempty source_id, title, and body strings")
if note["source_id"] in ids:
raise ValueError("Duplicate source_id")
ids.add(note["source_id"])
report = search(notes, args.query, args.limit)
except (OSError, UnicodeError, ValueError, sqlite3.Error) as error:
parser.error(str(error))
print(json.dumps(report, ensure_ascii=False, indent=2))
return 0 if report["hits"] else 1
if __name__ == "__main__":
sys.exit(main())
Read the source behind each hit.
python .\exercise\search-evidence.py .\exercise\search-notes.json 'Harbor review'
$LASTEXITCODEOn the provided files, this returns two hits and exit 0: SOURCE-01 first, then SOURCE-02. The first supports Morgan and the meeting date; the second is a distracting match about archive maintenance. Read each full body alongside its excerpt. A snippet may omit an exception or warning.
{
"exit": 0,
"query": "Harbor review",
"match_expression": "\"Harbor\" AND \"review\"",
"sqlite_version": "3.50.4",
"scope": "authored notes; lexical search only",
"hits": [
{
"source_id": "SOURCE-01",
"title": "Harbor launch review",
"body": "Harbor launch requires human review. Morgan owns the checklist. The review meeting is October 14, 2026. A pending review does not authorize publication.",
"excerpt": "[Harbor] launch requires human [review]. Morgan owns the checklist. The [review] meeting is October 14, 2026. A pending [review] does not authorize publication.",
"score": -3.021494144446713e-06
},
{
"source_id": "SOURCE-02",
"title": "Harbor archive note",
"body": "Harbor archive maintenance is assigned to Riley. Archive cleanup is separate from launch review. Do not infer the launch owner from this note.",
"excerpt": "[Harbor] archive maintenance is assigned to Riley. Archive cleanup is separate from launch [review]. Do not infer the launch owner from this note.",
"score": -2.321756811381035e-06
}
]
}Download all four observed query reports. These are outputs from our search script on authored notes, not model outputs. Changing the corpus, tokenizer, or query can change the ordering.
No match is a search result, not proof of absence.
python .\exercise\search-evidence.py .\exercise\search-notes.json 'signoff'
$LASTEXITCODEThis returns an empty hits array and exit 1. The notes use “review”, not “signoff”. The absence of that word does not establish the absence of a review requirement. Try 'Cedar review' and 'Harbor archive' to isolate the other notes; both returned one hit and exit 0 in our run.
Exit 2 means an input or runtime error, including a missing file, invalid notes, or unavailable FTS5. Read the error message. Do not treat it as an ordinary empty search.
Keep matching rules explicit.
SQLite’s FTS5 documentation describes token-based matching, auxiliary excerpts, and BM25 ranking. Our quoted terms are joined with AND, so every term must match somewhere in the indexed title or body. Source IDs are stored but unindexed. FTS5’s BM25 orders better matches with numerically smaller scores; the score is not a probability of truth.
The code binds query values using SQL placeholders, as documented by Python’s sqlite3 guide. SQL binding and FTS expression syntax are separate concerns. We construct the latter from letter/number terms, quote each term, and treat OR as a literal word. Advanced operators, exact user-entered phrases, and semantic synonyms are outside this starter.
The default tokenizer provides lexical matching. This exercise does not add stemming, embedding search, an automatic synonym map, or a reranker. It creates a fresh memory index each run; it does not crawl a folder or update a persistent database.
Review the evidence before composing an answer.
- Inspect the full source. Confirm the project, owner, date, and review condition. A matching note can describe a different responsibility.
- Keep stable source IDs. If you later change the notes, also retain versions or dates so an old citation can be traced.
- Handle missing evidence. Revise the query or ask for another approved source. An empty lexical result cannot justify a fabricated answer.
- Test retrieval separately. Build a representative set of questions and expected supporting sources before comparing lexical, semantic, or hybrid retrieval.
- Then test generation. If you choose an AI tool, provide only reviewed, approved material and grade whether each claim is supported. A citation label alone is insufficient.
The optional starter prompt asks an assistant to cite the owner and date from reviewed excerpts and state missing evidence. It is a proposal and was not executed. A prompt instruction is not a security control: retrieved text can contain misleading facts or hostile instructions. Decide separately how your application enforces data access and tool permissions.
A small working baseline, with visible limits.
Actually verified: the exact script on Windows/Python 3.13.15/SQLite 3.50.4; four fictional query outcomes; source ordering; a one-hit limit; Unicode matching; empty-query rejection; and literal treatment of operator/SQL-like input. Package integrity and website downloads were also checked.
Not tested: AI answers, model quality, speed, cost savings, production search quality, large corpora, other operating systems, or confidential data. The teaching script is not a hardened service and has no authentication or resource quota. Running locally does not audit other software on your machine.
The current primary-source review is dated October 12, 2026. This is a practical tutorial using an established capability, not a claim that FTS5 launched today.
Keep the guide with the evidence.
Primary sources: SQLite FTS5 reference, SQLite bound parameters, Python sqlite3 reference, and Python downloads.
Complete exercise · Caption · Alt text · Posting ZIP
Manual social delivery: the original graphic, teaser, alt text, and article URL are prepared for the user to post. Nothing is scheduled or published to social media; no Instagram profile-link change is claimed.