FTS5 Search

SQLite's built-in full-text search engine for exact term matching, boolean queries, and prefix search.

Capabilities

• Term matching: Exact word and phrase search
• Boolean logic: AND, OR, NOT operators
• Prefix search: data* matches "database", "datastore"
• Ranking: BM25-based relevance scoring

How FTS5 is Used

FTS5 search is performed automatically as part of the recall() method. There is no separate text_search() API. The FTS5 index is queried internally and its results are fused with vector search results.

from mnemosyne import Memory

mem = Memory()

# recall() internally performs vector + FTS5 hybrid search
results = mem.recall("database migration strategy", top_k=5)

BM25 Ranking

FTS5 uses BM25 for relevance scoring:

score = log(N / df) * (f * (k1 + 1)) / (f + k1 * (1 - b + b * dl / avgdl))

Where:

• N: Total documents
• df: Documents containing term
• f: Term frequency in document
• dl: Document length
• avgdl: Average document length

Integration with Hybrid Search

FTS5 scores are normalized and fused with vector scores using a fixed weighting:

• Vector score: weight 0.5
• FTS5 score: weight 0.3
• Importance: weight 0.2

These weights are not configurable through the recall() API. recall() accepts only query and top_k parameters.

Performance

Index Schema

Two separate FTS5 virtual tables are maintained:

fts_episodes (synced to Episodic Memory)

-- Synced to the episodic_memory table via triggers
CREATE VIRTUAL TABLE fts_episodes USING fts5(
  content,
  source,
  content_rowid=rowid
);

-- Triggers keep fts_episodes in sync with episodic_memory
CREATE TRIGGER episodic_ai AFTER INSERT ON episodic_memory BEGIN
  INSERT INTO fts_episodes(rowid, content, source)
  VALUES (new.id, new.content, new.source);
END;
CREATE TRIGGER episodic_ad AFTER DELETE ON episodic_memory BEGIN
  INSERT INTO fts_episodes(fts_episodes, rowid, content, source)
  VALUES ('delete', old.id, old.content, old.source);
END;

fts_working (standalone for Working Memory)

-- Standalone FTS5 index for working memory
-- Uses content= option to avoid duplicating data
CREATE VIRTUAL TABLE fts_working USING fts5(
  id UNINDEXED,
  content,
  content='',
);

The fts_episodes index mirrors the content and source fields from the episodic_memory table and stays synchronized via triggers. The fts_working index is standalone with id UNINDEXED and content columns. FTS5 indices are maintained automatically — no manual rebuild is required.