state.db FTS: messages_fts drifts and its rank=1 integrity-check is unsatisfiable (truncated projection + moving high-water boundary)

Summary

messages_fts cannot pass FTS5's strict rank=1 'integrity-check' in normal operation, and it also leaks index entries over time. Two independent defects in the FTS layer of state.db, both reproducible from the production schema:

1. Checker vs projection mismatch. hermes_state_common._fts_indexed_content_sql() indexes substr(content,1,8192) for role='tool' rows above fts_tool_full_content_high_water, but messages_fts is an external-content table over the raw messages table. FTS5's 'integrity-check' (rank 1) re-reads the content source and compares it with the index, so a truncated row is unsatisfiable by construction.
2. The truncation boundary moves. The DELETE and UPDATE triggers re-read fts_tool_full_content_high_water at delete time. Once the mark advances past a row (the chunked rebuild advances it), the 'delete' command sends full content for a row the index holds truncated; FTS5 cannot match the old tokens, so tokens leak. The leak survives even deleting the row.

Net effect: the index drifts continuously, and any health check that runs the strict probe sees database disk image is malformed / fts5: checksum mismatch for table "messages_fts" forever. On our deployment this tripped a nightly guard 4 times in a week and forced an in-place rebuild every night (a workaround, not a fix).

Environment

• Hermes Agent v0.21.3 (2026.9.14), upstream 03b0c794, git install
• SCHEMA_VERSION = 30, FTS_STORAGE_VERSION = 2, FTS_TOOL_CONTENT_PREFIX_CHARS = 8192
• Interpreter: /usr/local/lib/hermes-agent/venv/bin/python (SQLite 3.53.1)

⚠️ SQLite 3.45 (system python) reports the same probe as PASS on an index that 3.53 correctly fails — verify with the bundled venv interpreter.

Minimal reproduction

Build a throwaway DB from the production DDL and drive it through the real triggers:

import sqlite3, sys, tempfile, os
sys.path.insert(0, "/usr/local/lib/hermes-agent")
from hermes_state_common import SCHEMA_SQL, FTS_SQL   # FTS_SQL already has the triggers

db = os.path.join(tempfile.mkdtemp(), "mini.db")
c = sqlite3.connect(db, isolation_level=None)
c.executescript(SCHEMA_SQL); c.executescript(FTS_SQL)
c.execute("INSERT INTO sessions(id, source, started_at) VALUES('s1','cli',1.0)")
c.execute("INSERT INTO state_meta(key,value) VALUES('fts_tool_full_content_high_water','0')")

def probe(label):                      # the check in question
    c.execute("BEGIN IMMEDIATE")
    try:
        c.execute("INSERT INTO messages_fts(messages_fts, rank) VALUES('integrity-check', 1)")
        print(f"[{label}] PASS")
    except Exception as e:
        print(f"[{label}] FAIL -> {e}")
    finally:
        c.rollback()

BIG = "Z" * 20000
probe("baseline")                                              # PASS
c.execute("INSERT INTO messages(session_id,role,content,timestamp) VALUES('s1','tool',?,1.0)", (BIG,))
rid = c.execute("SELECT max(id) FROM messages").fetchone()[0]
probe("one truncated tool row indexed")                        # FAIL (defect 1)
c.execute("UPDATE state_meta SET value=? WHERE key='fts_tool_full_content_high_water'", (str(rid+10),))
c.execute("UPDATE messages SET content=? WHERE id=?", (BIG+"x", rid))
probe("after the mark moved + update")                         # FAIL (defect 2)
c.execute("DELETE FROM messages WHERE id=?", (rid,))
probe("after deleting the offending row")                      # FAIL (leak residue)

Observed:

[baseline] PASS
[one truncated tool row indexed] FAIL -> DatabaseError: fts5: checksum mismatch for table "messages_fts"
[after the mark moved + update] FAIL -> DatabaseError: fts5: checksum mismatch for table "messages_fts"
[after deleting the offending row] FAIL -> DatabaseError: fts5: checksum mismatch for table "messages_fts"

After the final DELETE the content table is empty (messages=0, messages_fts=0) and the probe still fails — only INSERT INTO messages_fts(messages_fts, rank) VALUES('rebuild', 1) clears it. In a variant where the row is left in place, the leak only appears once the high-water mark has been advanced past the row, i.e. it is the mark's motion, not the truncation alone, that corrupts the delete path.

Root cause detail

hermes_state_common.py:

def _fts_indexed_content_sql(alias: str) -> str:
    return f"""CASE WHEN {alias}.role = 'tool'
              AND {alias}.id > COALESCE((SELECT CAST(value AS INTEGER)
                                         FROM state_meta
                                         WHERE key = 'fts_tool_full_content_high_water'), -1)
         THEN substr(COALESCE({alias}.content, ''), 1, {FTS_TOOL_CONTENT_PREFIX_CHARS})
         ELSE {alias}.content END"""

• The INSERT path uses it to write truncated text into the index.
• The DELETE/UPDATE paths use the same expression with old, evaluated now — so the delete command's content no longer matches what the index holds for rows the mark has since passed. An external-content FTS5 delete must be given the exact indexed content.
• messages_fts declares content='messages', so the rank=1 check compares the (truncated) index against raw full content and can never agree.

Proposed fix

Make the indexed projection be the external-content source, with a rule that cannot move:

CREATE VIEW messages_fts_src AS
  SELECT id,
         CASE WHEN role='tool' THEN substr(COALESCE(content,''),1,8192)
              ELSE content END AS content,
         tool_name, tool_calls
  FROM messages;

CREATE VIRTUAL TABLE messages_fts USING fts5(
  content, tool_name, tool_calls,
  content='messages_fts_src', content_rowid='id');

…with insert/delete/update triggers using that same expression and no state_meta lookup. Bump fts_storage_version so the one-time realign rebuild happens on open. Afterwards the index content and the checker's content source are the same expression, so the probe passes by construction and no rebuild is ever needed again. Index size and search behaviour are unchanged from today's truncating behaviour (text past 8192 chars remains unsearchable for tool rows, as it already is).

The trigram index already follows this pattern (messages_fts_trigram_src), which is a useful precedent.

Verification of the fix (mini-DB, same churn)

With the aligned projection: baseline, long-row insert, update, short-row insert, delete — every probe PASSES, and MATCH on the inserted text returns the row (case-insensitive). A database built by the current (buggy) shape and then opened by the patched code realigns on open and passes. Happy to share the full patch + harness on request; I have not attached it because it touches hermes_state_common.py, hermes_state_schema.py and hermes_state_search.py and I did not want to presume the maintainers' preferred shape.

Impact seen in production

• A nightly integrity guard reported the index corrupt and rebuilt it every night (4 occurrences in 7 days), including a snapshot-validation failure path that, in a stricter configuration, would have quarantined a healthy database.
• Related: the same id > high_water gate is used by classifiers to decide "repairable vs genuine corruption", and the mark advancing made that count drift toward zero (measured: 31,725 long tool rows, only 4 above the mark), so the same root cause can flip a healthy database into "genuine corruption".