Skip to content
aaethrox
EN·TR

Building Persistent Agent Memory with SQLite, FTS5, and the Python Standard Library

python · sqlite · local-first

Conversation history is not the same thing as memory. A useful agent should be able to retain a small fact, find it later, and carry it between sessions without loading an entire transcript into every prompt.

hermes-fact-store is my small answer to that problem: one Python program, one SQLite database, and no third-party Python packages in the core application.

Why one SQLite file was enough

The data is local, structured, and modest enough that a service would add more operations than value. SQLite already provides transactions, indexes, portable backups, and full-text search in a file that can move with the rest of the agent’s data.

The main facts table stores the text plus practical metadata:

  • category and tags;
  • source session;
  • importance;
  • creation and access timestamps.

That is enough to answer both direct searches and operational questions such as which categories exist or which facts were added recently.

Full-text search without a second system

An FTS5 virtual table indexes the fact text, tags, and category. SQLite triggers keep that index synchronized after inserts, updates, and deletes, so the CLI does not need a separate indexing workflow.

Search input is cleaned before it reaches FTS5 because characters meaningful to the query language should not turn ordinary user text into a broken expression. If an FTS query still fails, the implementation falls back to a LIKE search. The fallback is less capable, but returning a useful result is better than turning a memory lookup into an exception.

This is keyword search, not vector or semantic search. That constraint keeps the store understandable and dependency-free, but it also defines the point at which another approach may become worthwhile.

A deliberately boring CLI

The command line supports the operations the store actually needs:

add · search · recent · delete · stats · export · import

JSON export and import provide a backup format that is not tied to the internal SQLite schema. The database remains the working representation; JSON is the portable one.

There is no server process, account system, or synchronization layer. Those features would only be justified if the memory had to serve multiple machines or users.

Extract facts in batches

An optional extraction script reads recent Hermes sessions from its state.db, sends a batch to an OpenAI-compatible endpoint, and writes extracted facts into the store. The endpoint can be a hosted provider or a local model.

Batching is important. Extraction does not need to interrupt every message, and running it later makes the cost and data boundary visible. A scheduled job can automate the process, but the store itself does not depend on that schedule.

LLM extraction is also where “no dependencies” needs precision. The scripts use Python’s standard library, but automated extraction still requires an LLM endpoint. A hosted endpoint can cost money, and extracted facts can be wrong or duplicated. The database provides persistence, not truth.

The useful ceiling

For a single local agent, SQLite plus FTS5 covers a surprising amount:

  • durable storage;
  • fast keyword retrieval;
  • transactional updates;
  • inspectable data;
  • simple backup and restore.

I would add embeddings only after real searches fail because wording differs, and add a service only when more than one process or machine genuinely needs coordinated access. Until then, the single file is not a prototype limitation; it is the smallest complete system.

The core is available in fact_store.py, with batch extraction in extract_facts.py.

← posts