Summary
On SQLite, full-text search (search_type="text") cannot find any content located beyond the first ~6000 characters of a note. Large notes are silently unsearchable past that point, even though the full text is sitting in an indexed FTS5 column.
Root cause
Two pieces interact:
- Indexing —
services/search_service.py truncates content_stems to MAX_CONTENT_STEMS_SIZE = 6000 chars (introduced in c3678a11, 2025-11-30, to fix the Postgres 8KB btree index-row limit). Title/permalink/tag variants are prepended, so effective content coverage is less than 6000 chars. The full content still goes untruncated into content_snippet.
- Query —
repository/sqlite_search_repository.py (_build_fts_conditions, around line 757) only matches two columns:
"(search_index.title MATCH :text OR search_index.content_stems MATCH :text)"
content_snippet IS part of the FTS5 table and is never matched, so any keyword past the truncation point is invisible to text search.
The truncation was Postgres-motivated; SQLite FTS5 has no 8KB row limit, so on SQLite this trade-off buys nothing and silently loses recall.
Impact (measured on a real vault, v0.22.1)
- 214 of 583 entity rows (37%) had truncated
content_stems.
- Repro: a term at char 96,386 of a 98,682-char note:
- direct SQLite:
SELECT count(1) FROM search_index WHERE search_index MATCH 'term' → 2 rows
bm tool search-notes "term" → total: 0
- Hybrid search degrades too: its FTS leg has the same blindness, so results become semantic-only and exact-keyword files drop out of the top ranks.
Suggested fix
Minimal, verified locally (no reindex needed since content_snippet is already indexed):
"(search_index.title MATCH :text OR search_index.content_stems MATCH :text"
" OR search_index.content_snippet MATCH :text)"
With this one-line change, both repro queries return the expected rows with sane bm25 scores. Ranking shifts slightly for small notes (snippet duplicates stems), no correctness issues observed.
Alternatively, skip the 6000-char truncation entirely on the SQLite backend and keep it Postgres-only.
Environment
- basic-memory 0.22.1 (uv tool install), Windows 11, SQLite backend
- semantic search enabled (fastembed, multilingual mpnet, 768 dim)
Summary
On SQLite, full-text search (
search_type="text") cannot find any content located beyond the first ~6000 characters of a note. Large notes are silently unsearchable past that point, even though the full text is sitting in an indexed FTS5 column.Root cause
Two pieces interact:
services/search_service.pytruncatescontent_stemstoMAX_CONTENT_STEMS_SIZE = 6000chars (introduced inc3678a11, 2025-11-30, to fix the Postgres 8KB btree index-row limit). Title/permalink/tag variants are prepended, so effective content coverage is less than 6000 chars. The full content still goes untruncated intocontent_snippet.repository/sqlite_search_repository.py(_build_fts_conditions, around line 757) only matches two columns:"(search_index.title MATCH :text OR search_index.content_stems MATCH :text)"content_snippetIS part of the FTS5 table and is never matched, so any keyword past the truncation point is invisible to text search.The truncation was Postgres-motivated; SQLite FTS5 has no 8KB row limit, so on SQLite this trade-off buys nothing and silently loses recall.
Impact (measured on a real vault, v0.22.1)
content_stems.SELECT count(1) FROM search_index WHERE search_index MATCH 'term'→ 2 rowsbm tool search-notes "term"→total: 0Suggested fix
Minimal, verified locally (no reindex needed since
content_snippetis already indexed):With this one-line change, both repro queries return the expected rows with sane bm25 scores. Ranking shifts slightly for small notes (snippet duplicates stems), no correctness issues observed.
Alternatively, skip the 6000-char truncation entirely on the SQLite backend and keep it Postgres-only.
Environment