Repository navigation
[BUG] Duplicate rows in search_index on the SQLite backend — no uniqueness guard, non-atomic reindex, and the stale-row purge cannot remove duplicates #1771
Description
On the SQLite backend, search_index is an FTS5 virtual table. Unlike the Postgres backend — which has uix_search_index_permalink_project (a UNIQUE index on (permalink, project_id)) — the SQLite table has no unique constraint and no secondary indexes at all, so nothing at the database level prevents duplicate rows.
The index update path is also non-atomic across transactions: SearchService.index_entity() (background task on every note write) first calls repository.delete_by_entity_id() in one committed session, then bulk_index_items() (plain INSERTs) in another. Multiple independent processes write the same database concurrently — every MCP server instance does on-demand indexing, plus the file watcher. When two writers interleave (A deletes → B deletes → A inserts → B inserts), both inserts survive as duplicate rows.
Observed in practice: the same (project_id, 'entity', id) appearing 2–3 times, byte-identical content, adjacent rowids, timestamps within 1 ms; the visible count for a single note fluctuating between 1/2/3 across consecutive writes. Once observed, 34 entities across all projects were affected simultaneously.
Additionally, purge_stale_search_index_rows() cannot fix this: its three DELETE statements only remove rows whose backing entity/observation/relation row no longer exists. Duplicate rows have a live backing row, so they survive — a reindex reports 0 stale search rows purged while duplicates demonstrably exist. There is also no dedupe-only maintenance mode, and reindex (the documented remedy for index issues) does not dedupe either.
Impact
recent_activity/ search results list the same note N times; counts are inflated.- The
0 stale search rows purgedoutput actively misleads diagnosis while duplicates exist. - Users who try the documented remedy (
reindex --full) cannot clean the duplicates and, per the separatecreated_atissue, lose creation timestamps as a side effect.
Steps to reproduce
Intermittent (timing-dependent), so reproducible only probabilistically:
- Run several MCP server instances against the same basic-memory home (one per client session, which is the normal multi-client setup).
- Write/update the same note from two clients near-simultaneously (or write while the file watcher processes the same file).
- Inspect:
SELECT project_id, type, id, count(*) FROM search_index WHERE type='entity' GROUP BY project_id, type, id HAVING count(*) > 1;
Actual
Duplicate, byte-identical rows accumulate silently; no maintenance command removes them.
Expected
A re-index of an entity replaces its previous index rows exactly ("last write wins"), or at minimum a maintenance command exists that deduplicates.
Versions
basic-memory 0.23.2, Windows 11, SQLite backend.
Activity
rirkeyou-maker commentedon Oct 17, 2026
Cross-referencing two reports from the same installation: #1772 and #1773.
They are separate root causes (different code paths, separate fixes), but together they form a user-facing chain:
overwritesentity.created_atwith the reindex timestamp for notes without a frontmattercreated:` — irreversible #1772 — so users hit that bug while trying to repair the symptoms of this one →andbm doctor` don't surface index inconsistencies (duplicate rows, orphans, inverted timestamps) #1773 explains why neither problem is visible through bm status / bm doctor, so both go unnoticed until the duplicated search hits become obvious.Noting the chain here since it affects triage: on its own, this issue can look like a minor display-level annoyance.
dolly-saas-studio commentedon Oct 20, 2026
Reproduced SQLite search-index duplication and positive atomic-refresh comparison
Using normal v2 API publications, we reproduced duplicate FTS rows for identical
(project_id, type, id)with Basic Memory 0.23.2 on SQLite. No duplicate rows were inserted by the test and no runtime source was modified.The test uses only two synthetic notes. The target has one observation and seven outgoing relations: one resolvable reference and six unresolved references. The neighbor links back to the target. The real API application lifespan, watcher and relation follow-ups run normally, in
devmode.Semantic search stays enabled with local FastEmbed
sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2, 384 dimensions, two embedding threads and existing model weights used offline. Both runs use Python 3.14.7, SQLAlchemy 2.1.5, aiosqlite 0.22.1 and the same publication workload.Workload and observations
Per runtime: two initial POSTs, two sequential mutations, three simultaneous PATCH/PUT pairs and one projects GET. SQL statement/commit events and independent read-only snapshots are recorded. Instrumentation is passive; there are no timing barriers or injected SQL failures.
Both runs finish with the latest successfully accepted API response matching
note_contentand the materialized file exactly, including version and SHA-256. Entity, observation and relation identities match the final FTS projection. The seven target relations, including six pending references, remain present. Real finite, nonzero 384-dimensional embeddings are persisted in both runs. Embedding layouts/counts differ between snapshots, so this is not evidence of semantic-result equivalence.The released runtime also shows duplication during a sequential PUT with internally scheduled relation refreshes. Multiple external clients are therefore not required. A final-only count would miss the reproduced transient duplicates.
Candidate provenance and limits
The tested official snapshot is commit
9027203e4feb39f0b003af1d438ba6e15868fc02, the merge commit of #1623.[3] Its archive SHA-256 is5f16a20dcf8a903c8d1524471755cee9f57ffbc8a771c836a8604c0ced6870fc. All 448 installed Python source files that correspond to the extracted official package source match it byte-for-byte.The source module still reports
__version__ == 0.23.2; the archive build's distribution metadata reports0.0.0. We identify the tested code by commit and source hashes, not those version strings. This snapshot requires FastMCP/FastMCP-slim 4.0.3 instead of 4.0.0b1. No dependencies were forced with--no-deps.This is a bounded, positive comparative result for the full official snapshot. It is not an isolated cherry-pick experiment, universal concurrency proof, migration qualification, MCP-client qualification or production deployment. The candidate had one explicit HTTP 409, and only successfully accepted generations were treated as committed content.
Request
Could you provide or recommend a maintained backport of the atomic search-row replacement for the 0.23.x line, or an officially supported artifact that avoids the broader schema/index migration?
The tested snapshot's migration path includes
d2e3f4a5b6c7, which rebuilds the entire SQLite FTS table usingINSERT ... SELECT ... FROM search_index.[4] A full-index migration has a wider operational scope than the targeted refresh fix. No such migration has been run against production.We can supply the synthetic HTTP workload and transaction/snapshot evidence for a regression test. No private corpus or real-note content is needed.
Sources
[3] #1623 — fix(core): replace an entity's search rows in one transaction
[4] https://raw.githubusercontent.com/basicmachines-co/basic-memory/9027203e4feb39f0b003af1d438ba6e15868fc02/src/basic_memory/alembic/versions/d2e3f4a5b6c7_add_script_ngrams_to_full_text_search.py — Official SQLite FTS migration at the tested immutable commit