Your AI Needs Sources. Start With Search You Can Inspect.

Before an AI assistant can answer from your business procedures, you need a way to retrieve the right source. Start with a baseline whose inputs, results and misses you can inspect. A small SQLite FTS5 search can make that retrieval stage concrete without a model call or an embedding download.

This guide builds an in-memory keyword search over three fictional procedures. It returns source identifiers and text, not an AI-generated answer. The useful result is a reproducible baseline you can compare against a later retrieval system using the same questions and source set.

A search box, not a question-answering system

Fictional Orbit Desk keeps procedures for backup/restore, access review and campaign publishing. A staff member enters a few known terms, such as backup restore. The application returns the matching procedure so the staff member can inspect it. The example contains authored sample policy text, not actual My Biz Heroes operating instructions.

SQLite’s FTS5 documentation describes full-text virtual tables, query syntax and BM25 ranking. This implementation uses a separate unindexed source ID plus searchable title and body columns. It orders candidates by FTS5’s BM25 value, where lower values rank first, and uses rowid as a tie-breaker.

The title weight of five and body weight of one are authored starting values, not tuned results. The three-document fixture cannot establish that these weights improve real retrieval quality. Do not interpret a matching row as a confidence score or an authorized instruction.

Keep SQL values separate from search grammar

A SQL placeholder protects the SQL statement structure, but the bound MATCH text still has its own search language. This example does not expose that language directly. It extracts one through eight ASCII letter/number terms, quotes each one and joins them with AND. Every extracted term must match. Punctuation becomes a separator; phrases, wildcards and boolean operators are not a user feature.

Binding values is documented in Python’s sqlite3 placeholder guidance. The code binds both the constructed MATCH expression and result limit. The text extraction policy is intentionally narrow and English-oriented; it is not a general multilingual tokenizer or a way to preserve arbitrary product codes.

Build the local baseline

Prerequisite: Python with SQLite FTS5 enabled. The tested environment was Windows/Python 3.14.3/SQLite 3.50.4. Your Python distribution may link a different SQLite build. The program attempts to create the virtual table and stops with an explanatory error if that fails. No extension loading, package installation, network service or model is required.

Save this as procedure_search.py. It creates only an in-memory database, inserts the three fixtures and returns up to five candidates. It neither reads your procedure folders nor persists an index. The main program closes its connection after printing results.

"""In-memory keyword baseline over three fictional English procedures."""
import json
import re
import sqlite3
from contextlib import closing

ROWS = [
    ('SOP-01', 'Backup and restore',
     'Run a backup before changing configuration. Restore a copy in a separate test environment.'),
    ('SOP-02', 'Access review',
     'Review access permissions each month. Record an audit entry for every approved change.'),
    ('SOP-03', 'Campaign publishing',
     'Review the campaign artwork and captions before publishing. Keep the approved creative version.')]


def build():
    db = sqlite3.connect(':memory:')
    try:
        db.execute('CREATE VIRTUAL TABLE procedures USING fts5(doc_id UNINDEXED, title, body)')
    except sqlite3.OperationalError as exc:
        db.close()
        raise RuntimeError('This Python SQLite build needs FTS5 support') from exc
    db.executemany('INSERT INTO procedures VALUES (?, ?, ?)', ROWS)
    return db


def search(db, text, limit=3):
    if type(text) is not str or not 1 <= len(text) <= 128:
        raise ValueError('Use a query string of 1 through 128 characters')
    if type(limit) is not int or not 1 <= limit <= 5:
        raise ValueError('limit must be an integer from 1 through 5')
    terms = re.findall(r'[A-Za-z0-9]+', text)
    if not 1 <= len(terms) <= 8:
        raise ValueError('Use 1 through 8 ASCII word/number terms')
    expression = ' AND '.join('"' + term + '"' for term in terms)
    rows = db.execute('''SELECT doc_id, title, body FROM procedures
        WHERE procedures MATCH ?
        ORDER BY bm25(procedures, 0, 5, 1), rowid LIMIT ?''', (expression, limit))
    return [dict(zip(('doc_id', 'title', 'body'), row)) for row in rows]


if __name__ == '__main__':
    with closing(build()) as db:
        print(json.dumps({query: search(db, query) for query in
                          ('backup restore', 'campaign captions', 'disaster recovery')}, indent=2))

Reproduce the hits and the miss

Save these tests as test_procedure_search.py:

import unittest
from procedure_search import build, search


class SearchTests(unittest.TestCase):
    def setUp(self):
        self.db = build()
        self.addCleanup(self.db.close)

    def test_backup(self):
        self.assertEqual([r['doc_id'] for r in search(self.db, 'backup restore')], ['SOP-01'])

    def test_campaign(self):
        self.assertEqual([r['doc_id'] for r in search(self.db, 'campaign captions')], ['SOP-03'])

    def test_synonym_miss(self):
        self.assertEqual(search(self.db, 'disaster recovery'), [])

    def test_operator_is_literal(self):
        self.assertEqual(search(self.db, 'backup OR access'), [])

    def test_query_cannot_execute_sql(self):
        search(self.db, "backup'); DROP TABLE procedures; --")
        self.assertEqual(self.db.execute('SELECT count(*) FROM procedures').fetchone()[0], 3)

    def test_limit(self):
        self.assertEqual(len(search(self.db, 'review', limit=1)), 1)
        for limit in (True, 0, 6):
            with self.assertRaises(ValueError): search(self.db, 'review', limit)

    def test_query_contract(self):
        for text in ('', None, 'x' * 129, 'one two three four five six seven eight nine'):
            with self.subTest(text=text):
                with self.assertRaises(ValueError): search(self.db, text)

    def test_no_ascii_terms(self):
        for text in ('***', '\u65e5\u672c\u8a9e'):
            with self.assertRaises(ValueError): search(self.db, text)

    def test_case(self):
        self.assertEqual(search(self.db, 'BACKUP RESTORE'), search(self.db, 'backup restore'))

    def test_punctuation(self):
        self.assertEqual(search(self.db, 'backup,restore'), search(self.db, 'backup restore'))


if __name__ == '__main__':
    unittest.main()

From the folder containing both files, run:

python -X utf8 -m unittest -v test_procedure_search.py
python -X utf8 procedure_search.py

Ten test methods passed: backup and campaign matches, the synonym miss, literal operator handling, one SQL-shaped input with table preservation, result-limit rules, query bounds/types, inputs without ASCII terms, case handling and punctuation splitting. The final HTML’s files were extracted and both commands executed again in a disposable directory. The sample program prints:

{
  "backup restore": [
    {
      "doc_id": "SOP-01",
      "title": "Backup and restore",
      "body": "Run a backup before changing configuration. Restore a copy in a separate test environment."
    }
  ],
  "campaign captions": [
    {
      "doc_id": "SOP-03",
      "title": "Campaign publishing",
      "body": "Review the campaign artwork and captions before publishing. Keep the approved creative version."
    }
  ],
  "disaster recovery": []
}

Turn misses into evaluation cases

The disaster recovery miss is useful evidence. It shows that a relevant topic can be present under different wording while this lexical query finds nothing. It does not prove the business lacks a procedure. Add that case to an evaluation set before changing search behavior.

For a real baseline, collect representative staff queries, expected source IDs, acceptable alternatives and cases that should return nothing. Keep the source snapshot and search policy fixed while comparing versions. Inspect both missed sources and irrelevant matches, not just whether a result list is nonempty. If you later add embeddings, compare against the same set rather than assuming semantic retrieval wins. See the FastEmbed procedure-search example for a different retrieval approach; no head-to-head experiment was performed here.

Passing a full natural-language question directly to this AND policy is usually a different task: every extracted word becomes required, including incidental words. This is a keyword interface. Query rewriting, stop-word selection, synonym expansion and hybrid ranking require explicit design and their own tests; none is implemented in this example.

Put source review before the answer

A retrieved body is evidence to inspect, not an instruction that can override the user’s request or the application’s rules. Preserve the source ID, actual text and relevant version with any later answer. If evidence is missing or conflicting, return that state for review rather than manufacture a supported-looking response. Schema checks on a generated answer cannot repair a missing source; the raw JSON boundary is a separate concern.

A production corpus also needs ownership, publication state and access controls. This fixture has no users, tenants or permissions, so it is not a safe recipe for mixing restricted documents into one shared index. Apply authorization before returning candidates or putting them into a model’s context. If rendering source text on a website, escape it for that output context; this program only prints JSON.

The tests do not establish latency, scale, ranking quality, multilingual behavior, freshness, persistent-index maintenance, ingestion correctness or end-to-end assistant behavior. The SQL-shaped test is one regression case, not a security audit. No real business documents, model API or production integration were used.

Jason Ead publishes AlgoThoughts AI News and connects practical AI engineering with My Biz Heroes: websites, automation, workflows, AI tools and training. Explore Jason’s work. The cover is generated conceptual artwork; the interior table reports actual fixture queries.