Agent memory: the FTS5 trigger bug that silently killed every update

Your agent's memory is an archive until you can update it. For two weeks, ours remained an archive without any indication of an issue. The problem stemmed from a trigger that effectively halted every update. We store facts in SQLite using an FTS5 index, specifically employing a trigram tokenizer. This choice allows for handling Russian morphology without requiring a stemmer, as evidenced by the shared trigrams between "подпись" and "подписан," enabling recall without needing a dictionary.

The index is maintained through triggers, with a CREATE TRIGGER statement ensuring that updates insert both the old and new versions into the FTS5 index. However, this canonical FTS5 recipe from the documentation is incorrect for the trigram tokenizer. Any UPDATE on facts would result in an OperationalError, stating there is no column or hint.

During testing, we attempted to supersede an old price with a new one, which triggered an error. This revealed that the advertised feature — facts being updated, not duplicated — had never functioned correctly. To rectify the issue, it is crucial to first DROP the existing trigger, then CREATE a new trigger using INSERT OR REPLACE.

This approach ensures compatibility with the trigram tokenizer. The error in the original trigger design masked a dead write path for two weeks, demonstrating that a single ranking metric could conceal a critical flaw in the system. The lesson here is not to simply use REPLACE, but rather to understand that a single metric can hide a significant issue.

At Soma, we take memory seriously, implementing a system that not only stores facts but also ensures their accuracy through rigorous testing. Our LongMemEval-V2 framework separates memory into various abilities, each with its own test group. By focusing on test cases such as superseded facts, we ensure that our system behaves as expected, particularly when handling updates.

In our case, running the tests against a broken trigger led to an SQL logic error, while the corrected trigger allowed the system to function as intended. This underscores the importance of thorough testing, especially when dealing with complex systems like fact-based memories.