[Bug] FTS Index Corruption on memory_search

[crystalwizard]

Product: brainctl
Version observed: 2.8.0 (installed from editable source)
Platform: Windows 11, Python 3.12.10, SQLite (bundled with Python)
Filed by: Claude (Anthropic), on behalf of Kelly (crystalwizard)
Date: 2026-06-04

---

Summary

Every call to brainctl memory search destroys the FTS5 index entries for the memories it returns. After enough searches, memory_search returns empty results even though the memories exist in the database and indexed=1. The index must be manually rebuilt to restore search functionality.

---

Root Cause

cmd_memory_search (in _impl.py, around line 3519) calls _retrieval_practice_boost(db, r["id"]) for every result returned by a search, then calls db.commit(). This UPDATE to the memories table fires an FTS5 auxiliary trigger (memories_fts_update_delete) that is supposed to delete the old FTS entry and re-insert the updated one -- the standard FTS5 external content table maintenance pattern.

The trigger deletes the FTS entry for the updated row. The re-insertion does not follow. The net result is that every memory returned by a search has its FTS index entry deleted. The more searches run, the more index entries disappear.

The specific UPDATE issued by _retrieval_practice_boost (lines 2606-2620) touches confidence, alpha, beta, recalled_count, last_recalled_at, and labile_until. None of these columns are FTS-indexed content columns. The trigger fires anyway because it is keyed on AFTER UPDATE ON memories, not on specific columns.

Why it manifests as a runtime bug rather than a schema bug

The trigger logic itself may be correct in principle -- it deletes the old FTS entry and re-inserts the new one. However, under the compiled SQLite version shipped with Python 3.12 on Windows, the re-insertion step of the trigger does not execute or silently fails, leaving the delete in place. The same operation issued as a direct SQL statement from a separate connection works correctly. This points to a SQLite version or compilation flag difference between the Python-bundled SQLite and standalone SQLite on this platform.

---

Reproduction

import subprocess, sqlite3, os

DB = r"path\to\brain.db"
BRAINCTL = r"path\to\brainctl.exe"
env = {**os.environ, "BRAIN_DB": DB}

def docsize():
    c = sqlite3.connect(DB)
    n = c.execute("SELECT COUNT(*) FROM memories_fts_docsize").fetchone()[0]
    c.close()
    return n

print("Before:", docsize())  # e.g. 8

for _ in range(5):
    subprocess.run([BRAINCTL, "--agent", "gpt", "memory", "search", "brainctl",
                    "--output", "json"], env=env, capture_output=True)

print("After:", docsize())   # drops to 2 or 0

After five searches against a database with 7 memories, docsize drops from 8 to 2. Subsequent searches return empty results.

---

Impact

Any agent that calls memory_search repeatedly will progressively lose access to its own memories within a single session. The memories are not deleted from the memories table; they are only removed from the FTS index. A full rebuild restores them. The bug is silent -- memory_search returns an empty list with no error.

---

Suggested Fix (two options)

Option A -- Narrow the trigger to content columns only

Change the FTS update trigger from AFTER UPDATE ON memories to AFTER UPDATE OF content, category, tags ON memories (or whichever columns are actually FTS-indexed). _retrieval_practice_boost does not touch those columns, so the trigger would not fire during searches. This is the minimal surgical fix.

Option B -- Remove the FTS trigger and manage the index explicitly

Replace the trigger with explicit FTS maintenance calls in cmd_memory_add, cmd_memory_update, and cmd_memory_delete. This is more verbose but eliminates the SQLite version dependency entirely and gives deterministic behavior. An INSERT INTO memories_fts(memories_fts) VALUES('rebuild') called after any write operation is reliable across all tested SQLite versions on this platform.

---

Workaround Applied (connector-level)

While the underlying bug exists in brainctl, a connector-level workaround was applied for the GPT integration: the MCP connector issues INSERT INTO memories_fts(memories_fts) VALUES('rebuild') after every memory_search and memory_add call. This is a compensating control, not a fix. It adds latency and is unnecessary overhead once the trigger is corrected.

---

Additional Notes

• INSERT INTO memories_fts(memories_fts) VALUES('rebuild') issued via a standard Python sqlite3 connection always works correctly on this platform, regardless of the brainctl trigger state.
• The memories_fts virtual table uses content=memories (external content table) with porter unicode61 tokenizer.
• The bug was confirmed by inspecting memories_fts_docsize row counts before and after search calls. Docsize drops correspond exactly to the number of distinct memory IDs returned by searches.

[crystalwizard]

Related: #151 covers the companion init-time issue -- the FTS index is not populated when brainctl first initializes, so memory_search returns empty results even before any searches have run.
Related: #151 covers the companion init-time issue -- the FTS index is not populated when brainctl first initializes, so memory_search returns empty results even before any searches have run.

The two bugs have the same root structure (FTS index not maintained correctly) but different triggers: #151 is at startup, #152 is at search time via _retrieval_practice_boost. Both were encountered in the same test session. The workaround for both is the same: INSERT INTO memories_fts(memories_fts) VALUES('rebuild') issued from a direct sqlite3 connection.
The two bugs have the same root structure (FTS index not maintained correctly) but different triggers: #151 is at startup, #152 is at search time via _retrieval_practice_boost. Both were encountered in the same test session. The workaround for both is the same: INSERT INTO memories_fts(memories_fts) VALUES('rebuild') issued from a direct sqlite3 connection.