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

#database #mcp

Your agent's memory is an archive until you can update it. Ours was an archive for two weeks, and nothing in the logs said so.

The bug: a trigger that killed every update

We store facts in SQLite with an FTS5 index. The tokenizer is trigram, because it handles Russian morphology without a stemmer: "подпись" and "подписан" share trigrams, and we get recall without shipping a dictionary.

The index is kept in sync by triggers:

CREATE TRIGGER facts_au AFTER UPDATE ON facts BEGIN
  INSERT INTO facts_fts(facts_fts, rowid, text, tags) VALUES('delete', old.id, old.text, old.tags);
  INSERT INTO facts_fts(rowid, text, tags) VALUES (new.id, new.text, new.tags);
END;

That is the canonical FTS5 recipe from the docs, and it is wrong for trigram. Any UPDATE on facts failed with a bare:

sqlite3.OperationalError: SQL logic error

No column, no hint. It took a deliberately written test to find it: we asked the memory to supersede an old price with a new one and the call raised. Which means the feature we advertised — "facts are updated, not duplicated" — had never once run.

The fix

DROP TRIGGER IF EXISTS facts_au;
CREATE TRIGGER facts_au AFTER UPDATE ON facts BEGIN
  INSERT OR REPLACE INTO facts_fts(rowid, text, tags) VALUES (new.id, new.text, new.tags);
END;

INSERT OR REPLACE works with trigram. The DROP matters as much as the new body: databases already on disk carry the old broken trigger, and CREATE TRIGGER IF NOT EXISTS would happily leave it in place. Init now heals existing files.

The lesson is not "use REPLACE". The lesson is that a single ranking metric hid a dead write path for two weeks.

Five abilities, not one score

We used to measure memory with one number: recall@3 over ten bilingual questions. Ten green checks, and a feature that crashed on first use.

LongMemEval-V2 (MIT) separates memory into abilities. We copied the shape, not the dataset, and wrote one test group per ability:

Ability	The question it answers	What breaks without it
Extraction	is the fact found at all?	memory is a write-only log
Cross-session	does a fact from last week surface today?	each session starts blind
Temporal	does a time window exclude the old value?	stale facts look current
Supersede	does the new fact replace the old one?	contradictions pile up
Abstention	does it return nothing when it knows nothing?	confident invention

The last one is the one nobody tests. A memory that always returns its top-3 will always look useful — and will feed the model a plausible neighbour of the question instead of an honest "I don't know". Our test asserts that a question about an office in Tokyo returns no fact containing "Tokyo", not merely a low score.

What the tests look like

def test_superseded_fact_is_not_recalled(tmp_path):
    m = Memory(path=str(tmp_path / "m.db"))
    old = m.remember("Soma costs 19 USD per month.", tags="pricing")
    m.supersede(old, "Soma costs 49 USD per month.", tags="pricing")
    hits = [r["text"] for r in m.recall("how much does soma cost", k=3)]
    assert any("49" in t for t in hits)
    assert all("19" not in t for t in hits)      # the dead path shows up here

Run against the broken trigger, this fails with SQL logic error. Run against the fix, it passes. That is the whole difference between a benchmark and a decoration.

Why trigram at all

Because our users write in Russian and English in the same memory. A word-based tokenizer splits "подписан" and "подпись" into different tokens and recall silently drops. Trigram costs index size and gives morphology for free. The trade-off is real: it also made an obscure FTS5 incompatibility reachable, which a word tokenizer would have hidden.

If you run FTS5 with trigram, check your update trigger today. The failure is silent until something tries to change a row.

We build Soma: envelopes with budget, mandate and signature, plus memory that keeps its word — facts, obligations and signed receipts behind one MCP endpoint. If your agent memory passed a single recall test and you have never updated a fact in it, you may have the same two weeks we had.

DEV Community