okb

Local Memory Retrieval

research · #memory#sqlite#fts5#sqlite-vec#embeddings#retrieval

Summary

Local agent memory can combine inspectable source files with rebuildable SQLite indexes: FTS5 for lexical retrieval and sqlite-vec for semantic vector recall.

Key ideas

  • FTS5 handles exact language: It is useful for commands, file paths, package names, error messages, and phrases.
  • sqlite-vec handles semantic similarity: Its vec0 virtual tables support vector search with KNN-style queries.
  • Metadata matters: Scope, tags, paths, kind, timestamps, hashes, and model metadata should be queryable or joinable.
  • Indexes are derived: Source memory should remain inspectable; SQLite tables can be rebuilt when stale or corrupt.
  • Hybrid retrieval is safer: Lexical and semantic retrieval fail in different ways, so both should stay available.

Claims and evidence

FTS5 is suited to local full-text search.

SQLite FTS5 supports full-text indexes, ranking, prefix and phrase queries, and snippet-like result display. That makes it a good fit for local memory text and source-derived notes.

Confidence: high.

sqlite-vec supports vector search inside SQLite.

sqlite-vec's vec0 table supports vector columns, KNN queries using MATCH, metadata columns, partition keys, and auxiliary columns.

Confidence: high, with the caveat that the documentation labels parts of the project as work in progress.

Metadata filters need deliberate schema design.

sqlite-vec metadata columns can be used in KNN WHERE constraints, but only for supported types and operators. More complex filters may need ordinary SQLite tables and joins.

Confidence: high.

Important terms

Term Meaning
FTS5 SQLite extension for full-text indexing and search.
BM25 Ranking function commonly used for full-text relevance.
sqlite-vec SQLite extension for vector storage and search.
vec0 sqlite-vec virtual table for vector search.
Derived index Rebuildable cache generated from durable source files.

Questions for review

  • Which memory fields belong in ordinary metadata tables versus vec0 metadata columns?
  • Should memory chunks be whole notes, headings, paragraphs, or explicit sections?
  • How should FTS5 and vector results be merged?
  • What diagnostics should appear when embeddings are stale or unavailable?

Connections

  • Related ideas: context ledger, archival memory, /memory recall, context doctor, session audit.
  • Related sources: Letta memory notes, context-control notes, SQLite docs, sqlite-vec docs.

Open questions

  • Which embedding model should be the first supported model?
  • Should embeddings be produced locally, by a provider API, or both?
  • Should project memory use a separate index per workspace hash?

Takeaways

  • Use FTS5 and sqlite-vec together.
  • Keep memory source inspectable and indexes rebuildable.
  • Treat semantic recall as a context-control capability, not hidden prompt magic.