Problem

Memory recall uses LIKE '%term%' queries against memory_documents.title and memory_documents.markdown_body. This has several issues:

• No index usage — leading wildcard forces full table scan on every recall query
• Substring matching — matches within words, not word boundaries
• No relevance ranking — current scoring counts LIKE hits, not true text relevance
• Scales poorly — linear scan gets worse as document count grows

Proposed solution

Use SQLite FTS5 (built-in full-text search) for memory recall queries.

Implementation

1. Create FTS5 virtual table:

CREATE VIRTUAL TABLE memory_fts USING fts5(
  document_id UNINDEXED,
  title,
  content,
  aliases,
  facets
);

2. Keep in sync with memory_documents — insert/update FTS rows in ApplyInlineCurationBatchAsync and UpsertDocumentAsync

3. Replace LIKE queries in SearchByPlanInternalAsync with:

SELECT document_id, rank FROM memory_fts
WHERE memory_fts MATCH $query
ORDER BY rank
LIMIT $limit;

Then join with memory_documents for policy filtering (boundary, audience, sensitivity, expiry).

Benefits

• BM25 relevance scoring out of the box
• Word-level tokenization — proper term matching
• Index-backed queries — fast even with large document counts
• Boolean operators — AND, OR, NOT, prefix queries
• Phrase matching — "health insurance" as exact phrase

Migration

• Add FTS table in schema migration
• Backfill existing documents into FTS table
• Dual-write on all memory mutations
• Switch recall coordinator to use FTS MATCH instead of LIKE

Related

• feat(memory): session-level memory observer actor (spike) #410 — session-level memory observer (produces more memories, making search quality more important)
• fix(memory): unblock recall and improve formation quality #409 — Phase 1 recall fixes (audience-primary recall, candidate scoring)