An agent memory archive needs a search path, not a bigger prompt

Shipped

This release grew my fitness agent’s memory system in two directions: journal entries past the hot cap are now archived instead of deleted, and the coach got a recall_coach_memories tool that searches the whole archive on demand. It also folded a trailing-3-week report-card aggregate into the coach’s standing memory. The recall half is the teachable part: full-text search over an agent’s own history, built entirely inside the SQLite file the app already has. That’s what this guide walks through.

Why an archive plus recall beats a bigger prompt

An agent journal grows without bound, and the prompt it feeds cannot. My cap is 60 hot entries; everything older gets archived = 1 on write, never deleted. But an archive you can’t search is a graveyard. The retrieval contract in the system prompt says: search before claiming you don’t remember. That only works if search is cheap, ranked, and safe against whatever string the model passes in.

You don’t need a search service for this. SQLite ships FTS5, a full-text index that lives in the same database file, supports BM25 ranking, and can index a table you already have without copying its text.

The schema: an external-content index, kept out of the main script

Start with a plain journal table:

CREATE TABLE IF NOT EXISTS coach_journal (
    entry_id    INTEGER PRIMARY KEY,
    created_at  TEXT NOT NULL,
    entry_date  TEXT,
    source      TEXT NOT NULL,
    source_key  TEXT,
    seq         INTEGER,
    text        TEXT NOT NULL,
    archived    INTEGER NOT NULL DEFAULT 0
);

The index goes in a separate DDL script, not the one that creates your tables. This placement is load-bearing, and the gotchas section explains why.

# fts_schema.py
FTS_SCHEMA = """
CREATE VIRTUAL TABLE IF NOT EXISTS coach_journal_fts USING fts5(
    text,
    content='coach_journal',
    content_rowid='entry_id',
    tokenize='porter unicode61'
);
CREATE TRIGGER IF NOT EXISTS coach_journal_fts_ai AFTER INSERT ON coach_journal BEGIN
    INSERT INTO coach_journal_fts(rowid, text) VALUES (new.entry_id, new.text);
END;
CREATE TRIGGER IF NOT EXISTS coach_journal_fts_ad AFTER DELETE ON coach_journal BEGIN
    INSERT INTO coach_journal_fts(coach_journal_fts, rowid, text)
    VALUES ('delete', old.entry_id, old.text);
END;
CREATE TRIGGER IF NOT EXISTS coach_journal_fts_au AFTER UPDATE OF text ON coach_journal BEGIN
    INSERT INTO coach_journal_fts(coach_journal_fts, rowid, text)
    VALUES ('delete', old.entry_id, old.text);
    INSERT INTO coach_journal_fts(rowid, text) VALUES (new.entry_id, new.text);
END;
"""

Three choices worth naming. content='coach_journal' makes this an external content table: the index stores only FTS entries and reads column values from your real table when it needs them, so the text isn’t duplicated. The Datasette docs describe the same mechanism as associating a search virtual table “with the contents of another SQLite table”; it’s the standard shape when the data already lives somewhere. Second, the triggers are the sync pattern straight from the SQLite docs, including the odd-looking VALUES('delete', ...) special command; with external content, keeping index and table consistent is explicitly your job. Third, the update trigger fires on UPDATE OF text only. My archive flip is an UPDATE ... SET archived = 1, and scoping the trigger means archiving never churns the index for a column the index doesn’t hold.

On the tokenizer: porter unicode61 stems words so “running” matches “run”, which suits short journal lines. If you outgrow it, the APSW full-text-search guide covers richer tokenizer stacks and per-query ranking; for a few hundred rows of agent memory, stemming plus BM25 is plenty.

Attach it, then let it heal itself

Run the FTS script inside its own guarded block during schema init:

import logging
import sqlite3

log = logging.getLogger(__name__)

def init_fts(conn: sqlite3.Connection) -> None:
    try:
        conn.executescript(FTS_SCHEMA)
        n_rows = conn.execute(
            "SELECT COUNT(*) FROM coach_journal").fetchone()[0]
        n_fts = conn.execute(
            "SELECT COUNT(*) FROM coach_journal_fts_docsize").fetchone()[0]
        if n_fts != n_rows:
            conn.execute(
                "INSERT INTO coach_journal_fts(coach_journal_fts) "
                "VALUES('rebuild')")
    except sqlite3.OperationalError:
        log.warning("FTS5 unavailable; recall degrades to substring search")

The count check is the self-heal. A fresh index on an upgraded database has indexed zero of the existing rows, and a database written by a build without FTS5 drifts silently. When the counts disagree, the 'rebuild' command re-indexes from the content table; it’s idempotent, so running it on mismatch is safe. Note which table the indexed count comes from. That detail is a gotcha below.

The search function: quote everything the model sends

The query string comes from a language model. FTS5 MATCH syntax treats bare input as a query language, so NEAR(, col:, *, and stray quotes are all live syntax. The fix is to turn every token into a quoted phrase; inside double quotes, FTS5 treats the content as data, and embedded quotes escape SQL-style by doubling.

def fts_query(raw: str) -> str:
    """Every whitespace token becomes a quoted phrase (implicit AND)."""
    tokens = [t for t in (raw or "").split() if any(c.isalnum() for c in t)]
    if not tokens:
        raise ValueError("query has no searchable words")
    return " ".join('"' + t.replace('"', '""') + '"' for t in tokens)

def search_entries(conn: sqlite3.Connection, query: str, limit: int = 8):
    """Returns (matches, mode); mode tells the caller which path ran."""
    match = fts_query(query)
    try:
        rows = conn.execute(
            "SELECT j.entry_id, j.entry_date, j.text, j.archived "
            "FROM coach_journal_fts "
            "JOIN coach_journal j ON j.entry_id = coach_journal_fts.rowid "
            "WHERE coach_journal_fts MATCH ? ORDER BY rank LIMIT ?",
            (match, int(limit)))
        return [dict(zip([c[0] for c in rows.description], r)) for r in rows], "fts"
    except sqlite3.OperationalError:
        like = f"%{query.strip()}%"
        rows = conn.execute(
            "SELECT entry_id, entry_date, text, archived FROM coach_journal "
            "WHERE text LIKE ? ORDER BY entry_id DESC LIMIT ?",
            (like, int(limit)))
        return [dict(zip([c[0] for c in rows.description], r)) for r in rows], "like"

ORDER BY rank gives you BM25 ordering; FTS5 arranges the sign so better matches sort first ascending. The except path is the degraded mode for SQLite builds without FTS5: a plain LIKE, newest first. Returning the mode alongside the rows lets the tool layer tell the model which quality of search it got.

Try it

Save the journal DDL above as schema.sql, then seed a few entries and search:

import sqlite3

conn = sqlite3.connect("memory.db")
conn.executescript(open("schema.sql").read())  # the journal table DDL above
init_fts(conn)
for i, text in enumerate([
    "Nate said the 5k goal moved to October.",
    "Third easy day in a row run too hot.",
    "New shoes; right knee felt fine after 4 miles.",
]):
    conn.execute(
        "INSERT INTO coach_journal (created_at, entry_date, source, text) "
        "VALUES (datetime('now'), date('now'), 'chat', ?)", (text,))
conn.commit()
matches, mode = search_entries(conn, "shoe knee")
print(mode, [m["text"] for m in matches])
matches, mode = search_entries(conn, 'NEAR( knee')
print("hostile:", mode, matches)

Running that prints:

fts ['New shoes; right knee felt fine after 4 miles.']
hostile: fts []

The first line shows the porter stemmer earning its keep: the singular “shoe” matches the stored “shoes”. The second is the quoting doing its job; unquoted, NEAR( knee is a MATCH syntax error, and quoted it’s a phrase that matches nothing, so the tokens combine under the implicit AND and exclude everything. An empty result beats an exception from model-authored input. Then verify the self-heal: drop the FTS table alone (DROP TABLE coach_journal_fts), commit, run init_fts again, and the rebuild re-indexes so the first query returns the same row.

Gotchas

Putting the virtual table in your main schema script bricks everything on the wrong build. executescript aborts the whole script on the first error. If CREATE VIRTUAL TABLE ... USING fts5 sits in the same script as your ordinary tables and the host SQLite lacks FTS5, the error takes every table creation down with it; a fresh install fails entirely, not just search. The escape is structural: FTS DDL lives in its own script behind its own try/except, and the app boots without recall rather than not at all.

SELECT COUNT(*) on an external-content table lies to your self-heal. My first instinct for the drift check was counting the FTS table itself. On an external content table that query reads through to the content table, so index and table always “match” and the heal never fires. Count the _docsize shadow table instead; it holds one row per actually-indexed document.

Unquoted model input is a query-language injection. Before the phrase-quoting step, a model asking about NEAR( my knee is a MATCH syntax error at best. Quote every token, escape embedded quotes by doubling them, and reject queries with no alphanumeric tokens so an all-punctuation string fails with a clear message instead of a cryptic one.

An unscoped update trigger churns the index for writes that don’t touch text. AFTER UPDATE ON coach_journal fires on the archive flip too, deleting and re-inserting index entries to no effect. AFTER UPDATE OF text scopes it to the one column the index holds.

Sources

SQLite FTS5 documentation — external content tables, the trigger sync pattern, phrase syntax, and bm25
Datasette: full-text search — the external-content mechanism and populating an index over an existing table
APSW: full text search — trigger-based sync for external content tables and tokenizer trade-offs beyond unicode61

Changelog

release: 0.34.0 — coach memory v2 and report-card ledger aggregate (dev → main) (#153) (8edfb70)