Environment:
brainctl v2.8.0
Windows 11, Python 3.12.10
SQLite WAL mode
Fresh database initialized with brainctl init --path
Description:
After a fresh brainctl init, memory search returns empty results even after memory add calls succeed and the memories are confirmed present in the database. The FTS5 content rows exist and are readable by rowid, but the inverted index is not populated, so MATCH queries return nothing.
Steps to reproduce:
Initialize a fresh database: brainctl init --path /path/to/brain.db
Register an agent: brainctl agent register ...
Add a memory: brainctl memory add "some content" --category integration
Confirm the memory exists: brainctl stats shows memories: 1
Search for the memory: brainctl memory search "some content" --output json
Expected: Returns the memory.
Actual: Returns []
Investigation:
Direct SQLite inspection confirms:
The memory row exists in the memories table with indexed=1
The FTS content row exists in memories_fts and is readable by rowid
SELECT count(*) FROM memories_fts returns the correct count
SELECT content FROM memories_fts WHERE rowid=N returns the correct content
SELECT rowid FROM memories_fts WHERE memories_fts MATCH 'keyword' returns nothing
This means the inverted index (token → rowid mapping) is empty. The content was stored but never tokenized into the index.
Workaround:
Running a manual FTS rebuild fixes it durably:
INSERT INTO memories_fts(memories_fts) VALUES('rebuild');
After the rebuild, MATCH queries work correctly and the insert trigger (memories_fts_insert) maintains the index correctly for all subsequent memory add calls. The fix is durable -- new writes do not re-introduce the problem.
Root cause hypothesis:
The brainctl init migration sequence creates the memories_fts table and the associated insert/update/delete triggers, but does not run an initial FTS rebuild. If any rows are inserted before the inverted index is properly seeded (or if the index is created in a state where the B-tree structure is present but empty), subsequent inserts via trigger add content rows but the MATCH index remains unusable until an explicit rebuild is triggered.
Suggested fix:
Add an FTS rebuild step to the init sequence after all migrations complete:
INSERT INTO memories_fts(memories_fts) VALUES('rebuild');
Alternatively, ensure the FTS table creation migration explicitly initializes the index state so triggers can maintain it from the first insert.
Environment:
brainctl v2.8.0
Windows 11, Python 3.12.10
SQLite WAL mode
Fresh database initialized with brainctl init --path
Description:
After a fresh brainctl init, memory search returns empty results even after memory add calls succeed and the memories are confirmed present in the database. The FTS5 content rows exist and are readable by rowid, but the inverted index is not populated, so MATCH queries return nothing.
Steps to reproduce:
Initialize a fresh database: brainctl init --path /path/to/brain.db
Register an agent: brainctl agent register ...
Add a memory: brainctl memory add "some content" --category integration
Confirm the memory exists: brainctl stats shows memories: 1
Search for the memory: brainctl memory search "some content" --output json
Expected: Returns the memory.
Actual: Returns []
Investigation:
Direct SQLite inspection confirms:
The memory row exists in the memories table with indexed=1
The FTS content row exists in memories_fts and is readable by rowid
SELECT count(*) FROM memories_fts returns the correct count
SELECT content FROM memories_fts WHERE rowid=N returns the correct content
SELECT rowid FROM memories_fts WHERE memories_fts MATCH 'keyword' returns nothing
This means the inverted index (token → rowid mapping) is empty. The content was stored but never tokenized into the index.
Workaround:
Running a manual FTS rebuild fixes it durably:
INSERT INTO memories_fts(memories_fts) VALUES('rebuild');
After the rebuild, MATCH queries work correctly and the insert trigger (memories_fts_insert) maintains the index correctly for all subsequent memory add calls. The fix is durable -- new writes do not re-introduce the problem.
Root cause hypothesis:
The brainctl init migration sequence creates the memories_fts table and the associated insert/update/delete triggers, but does not run an initial FTS rebuild. If any rows are inserted before the inverted index is properly seeded (or if the index is created in a state where the B-tree structure is present but empty), subsequent inserts via trigger add content rows but the MATCH index remains unusable until an explicit rebuild is triggered.
Suggested fix:
Add an FTS rebuild step to the init sequence after all migrations complete:
INSERT INTO memories_fts(memories_fts) VALUES('rebuild');
Alternatively, ensure the FTS table creation migration explicitly initializes the index state so triggers can maintain it from the first insert.