FTS5 session_search indexing for tool fields

Fixes on record

Merged to main. A stamp counts the issues a fix is linked to.

• #16914 fix(state): index tool_name and tool_calls in FTS5, with repair + migration (salvages #16866)

  merged to main

  files: hermes_state.py, scripts/release.py, tests/test_hermes_state.py

  Show the diff

  • hermes_state.py

    @@ -33,7 +33,7 @@
     
     DEFAULT_DB_PATH = get_hermes_home() / "state.db"
     
    -SCHEMA_VERSION = 10
    +SCHEMA_VERSION = 11
     
     SCHEMA_SQL = """
     CREATE TABLE IF NOT EXISTS schema_version (
    @@ -102,22 +102,26 @@
     
     FTS_SQL = """
     CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
    -    content,
    -    content=messages,
    -    content_rowid=id
    +    content
     );
     
     CREATE TRIGGER IF NOT EXISTS messages_fts_insert AFTER INSERT ON messages BEGIN
    -    INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);
    +    INSERT INTO messages_fts(rowid, content) VALUES (
    +        new.id,
    +        COALESCE(new.content, '') || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
    +    );
     END;
     
     CREATE TRIGGER IF NOT EXISTS messages_fts_delete AFTER DELETE ON messages BEGIN
    -    INSERT INTO messages_fts(messages_fts, rowid, content) VALUES('delete', old.id, old.content);
    +    DELETE FROM messages_fts WHERE rowid = old.id;
     END;
     
     CREATE TRIGGER IF NOT EXISTS messages_fts_update AFTER UPDATE ON messages BEGIN
    -    INSERT INTO messages_fts(messages_fts, rowid, content) VALUES('delete', old.id, old.content);
    -    INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);
    +    DELETE FROM messages_fts WHERE rowid = old.id;
    +    INSERT INTO messages_fts(rowid, content) VALUES (
    +        new.id,
    +        COALESCE(new.content, '') || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
    +    );
     END;
     """
     
    @@ -128,22 +132,26 @@
     FTS_TRIGRAM_SQL = """
     CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts_trigram USING fts5(
         content,
    -    content=messages,
    -    content_rowid=id,
         tokenize='[redacted:secret-assignment]'
     );
     
     CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_insert AFTER INSERT ON messages BEGIN
    -    INSERT INTO messages_fts_trigram(rowid, content) VALUES (new.id, new.content);
    +    INSERT INTO messages_fts_trigram(rowid, content) VALUES (
    +        new.id,
    +        COALESCE(new.content, '') || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
    +    );
     END;
     
     CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_delete AFTER DELETE ON messages BEGIN
    -    INSERT INTO messages_fts_trigram(messages_fts_trigram, rowid, content) VALUES('delete', old.id, old.content);
    +    DELETE FROM messages_fts_trigram WHERE rowid = old.id;
     END;
     
     CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_update AFTER UPDATE ON messages BEGIN
    -    INSERT INTO messages_fts_trigram(messages_fts_trigram, rowid, content) VALUES('delete', old.id, old.content);
    -    INSERT INTO messages_fts_trigram(rowid, content) VALUES (new.id, new.content);
    +    DELETE FROM messages_fts_trigram WHERE rowid = old.id;
    +    INSERT INTO messages_fts_trigram(rowid, content) VALUES (
    +        new.id,
    +        COALESCE(new.content, '') || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
    +    );
     END;
     """
     
    @@ -428,6 +436,51 @@ def _init_schema(self):
                             "INSERT INTO messages_fts_trigram(rowid, content) "
                             "SELECT id, content FROM messages WHERE content IS NOT NULL"
                         )
    +            if current_version < 11:
    +                # v11: re-index FTS5 tables to cover tool_name + tool_calls and
    +                # switch from external-content to inline mode. Existing DBs have
    +                # old-schema FTS tables and triggers that IF NOT EXISTS won't
    +                # overwrite, so we drop them explicitly and let the post-migration
    +                # existence checks (below) recreate them from FTS_SQL /
    +                # FTS_TRIGRAM_SQL, then backfill every message row. Fixes #16751.
    +                for _trig in (
    +                    "messages_fts_insert",
    +                    "messages_fts_delete",
    +                    "messages_fts_update",
    +                    "messages_fts_trigram_insert",
    +                    "messages_fts_trigram_delete",
    +                    "messages_fts_trigram_update",
    +                ):
    +                    try:
    +                        cursor.execute(f"DROP TRIGGER IF EXISTS {_trig}")
    +                    except sqlite3.OperationalError:
    +                        pass
    +                for _tbl in ("messages_fts", "messages_fts_trigram"):
    +                    try:
    +                        cursor.execute(f"DROP TABLE IF EXISTS {_tbl}")
    +                    except sqlite3.OperationalError:
    +                        pass
    +                # Recreate virtual tables + triggers with the new inline-mode
    +                # schema that indexes content || tool_name || tool_calls.
    +                cursor.executescript(FTS_SQL)
    +                cursor.executescript(FTS_TRIGRAM_SQL)
    +                # Backfill both indexes from every existing messages row.
    +                cursor.execute(
    +                    "INSERT INTO messages_fts(rowid, content) "
    +                    "SELECT id, "
    +                    "COALESCE(content, '') || ' ' || "
    +                    "COALESCE(tool_name, '') || ' ' || "
    +                    "COALESCE(tool_calls, '') "
    +                    "FROM messages"
    +                )
    +                cursor.execute(
    +                    "INSERT INTO messages_fts_trigram(rowid, content) "
    +                    "SELECT id, "
    +                    "COALESCE(content, '') || ' ' || "
    +                    "COALESCE(tool_name, '') || ' ' || "
    +                    "COALESCE(tool_calls, '') "
    +                    "FROM messages"
    +                )
                 if current_version < SCHEMA_VERSION:
                     cursor.execute(
                         "UPDATE schema_version SET version = ?",
    @@ -1534,8 +1587,8 @@ def search_messages(
                     # Short CJK query (1-2 chars) — trigram needs ≥3 CJK chars.
                     # Fall back to LIKE substring search.
                     escaped = raw_query.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
    -                like_where = ["m.content LIKE ? ESCAPE '\\'"]
    -                like_params: list = [f"%{escaped}%"]
    +                like_where = ["(m.content LIKE ? ESCAPE '\\' OR m.tool_name LIKE ? ESCAPE '\\' OR m.tool_calls LIKE ? ESCAPE '\\')"]
    +                like_params: list = [f"%{escaped}%", f"%{escaped}%", f"%{escaped}%"]
                     if source_filter is not None:
                         like_where.append(f"s.source IN ({','.join('?' for _ in source_filter)})")
                         like_params.extend(source_filter)

  • scripts/release.py

    @@ -54,6 +54,7 @@
         "[redacted:email]": "dhabibi",
         "[redacted:email]": "johnncenae",
         "[redacted:email]": "yes999zc",
    +    "[redacted:email]": "yes999zc",
         "[redacted:email]": "DrStrangerUJN",
         "[redacted:email]": "dlkakbs",
         "[redacted:email]": "Mind-Dragon",

  • tests/test_hermes_state.py

    @@ -1292,7 +1292,7 @@ def test_tables_exist(self, db):
         def test_schema_version(self, db):
             cursor = db._conn.execute("SELECT version FROM schema_version")
             version = cursor.fetchone()[0]
    -        assert version == 10
    +        assert version == 11
     
         def test_title_column_exists(self, db):
             """Verify the title column was created in the sessions table."""
    @@ -1353,7 +1353,7 @@ def test_migration_from_v2(self, tmp_path):
     
             # Verify migration
             cursor = migrated_db._conn.execute("SELECT version FROM schema_version")
    -        assert cursor.fetchone()[0] == 10
    +        assert cursor.fetchone()[0] == 11
     
             # Verify title column exists and is NULL for existing sessions
             session = migrated_db.get_session("existing")
    @@ -2266,3 +2266,198 @@ def test_prune_sessions_deletes_files_for_pruned_only(self, db, tmp_path):
             assert not (sessions_dir / "old.jsonl").exists()
             assert (sessions_dir / "active.jsonl").exists()
     
    +
    +# =========================================================================
    +# FTS5 indexing of tool_calls / tool_name (#16751)
    +# =========================================================================
    +
    +class TestFTS5ToolCallIndexing:
    +    """Regression tests: search_messages must see tool_name and tool_calls.
    +
    +    Before #16751's fix, `messages_fts` only indexed `messages.content`, so
    +    tokens that only appeared in `tool_name` or the serialized `tool_calls`
    +    JSON were invisible to session_search even though the row was in the DB.
    +    """
    +
    +    def test_tool_name_is_searchable(self, db):
    +        db.create_session(session_id="s1", source="cli")
    +        db.append_message(
    +            "s1", role="assistant", content="",
    +            tool_name="UNIQUETOOLNAME",
    +        )
    +        results = db.search_messages("UNIQUETOOLNAME")
    +        assert len(results) == 1
    +
    +    def test_tool_calls_args_are_searchable(self, db):
    +        db.create_session(session_id="s1", source="cli")
    +        db.append_message(
    +            "s1", role="assistant", content="",
    +            tool_calls=[{
    +                "id": "c1",
    +                "type": "function",
    +                "function": {
    +                    "name": "web_search",
    +                    "arguments": '{"query": "UNIQUESEARCHTOKEN"}',
    +                },
    +            }],
    +        )
    +        results = db.search_messages("UNIQUESEARCHTOKEN")
    +        assert len(results) == 1
    +
    +    def test_tool_function_name_in_tool_calls_is_searchable(self, db):
    +        db.create_session(session_id="s1", source="cli")
    +        db.append_message(
    +            "s1", role="assistant", content="",
    +            tool_calls=[{
    +                "id": "c1",
    +                "type": "function",
    +                "function": {"name": "UNIQUEFUNCNAME", "arguments": "{}"},
    +            }],
    +        )
    +        results = db.search_messages("UNIQUEFUNCNAME")
    +        assert len(results) == 1
    +
    +    def test_delete_message_row_does_not_crash(self, db):
    +        """DELETE on messages must not raise when FTS rows reference tool fields.
    +
    +        Previously the messages_fts_delete trigger passed old.content to the
    +        FTS5 delete-command but the inserted row was the concatenation of
    +        content || tool_name || tool_calls, so FTS5 rejected the delete with
    +        'SQL logic error' and every session delete path broke.
    +        """
    +        db.create_session(session_id="s1", source="cli")
    +        db.append_message(
    +            "s1", role="assistant", content="hello",
    +            tool_name="web_search",
    +            tool_calls=[{
    +                "id": "c1",
    +                "type": "function",
    +                "function": {"name": "web_search", "arguments": '{"q": "x"}'},
    +            }],
    +        )
    +        # end_session + end-time prune path would exercise DELETE; hit the
    +        # row directly through the write helper to keep the regression focused.
    +        def _delete(conn):
    +            conn.execute("DELETE FROM messages WHERE session_id = ?", ("s1",))
    +        db._execute_write(_delete)  # must not raise
    +
    +        assert db.search_messages("hello") == []
    +        assert db.search_messages("web_search") == []
    +
    +    def test_update_message_reindexes_tool_fields(self, db):
    +        """UPDATE must refresh the FTS row so old tokens drop out and new tokens appear."""
    +        db.create_session(session_id="s1", source="cli")
    +        db.append_message(
    +            "s1", role="assistant", content="",
    +            tool_name="ORIGINALTOOL",
    +        )
    +        assert len(db.search_messages("ORIGINALTOOL")) == 1
    +
    +        def _update(conn):
    +            conn.execute(
    +                "UPDATE messages SET tool_name = ? WHERE session_id = ?",
    +                ("RENAMEDTOOL", "s1"),
    +            )
    +        db._execute_write(_update)
    +
    +        assert db.search_messages("ORIGINALTOOL") == []
    +        assert len(db.search_messages("RENAMEDTOOL")) == 1
    +
    +
    +class TestFTS5ToolCallMigration:
    +    """v11 migration: pre-existing state.db with old external-content FTS tables
    +    must be re-indexed so tool_name / tool_calls become searchable after upgrade."""
    +
    +    def test_v10_to_v11_upgrade_backfills_tool_fields(self, tmp_path):
    +        """Simulate an existing user: build a v10-shaped DB by hand, insert a
    +        row with tool_calls, then open via SessionDB (which runs migrations).
    +        After upgrade, the tool_calls token must be searchable."""
    +        import sqlite3
    +
    +        db_path = tmp_path / "legacy.db"
    +
    +        # Build the pre-v11 schema by hand: external-content FTS tables +
    +        # old triggers that only reference new.content.
    +        conn = sqlite3.connect(str(db_path))
    +        conn.executescript("""
    +            CREATE TABLE schema_version (version INTEGER NOT NULL);
    +            INSERT INTO schema_version (version) VALUES (10);
    +
    +            CREATE TABLE sessions (
    +                id TEXT PRIMARY KEY,
    +                source TEXT,
    +                started_at REAL,
    +                ended_at REAL,
    +                title TEXT,
    +                parent_session_id TEXT,
    +                message_count INTEGER DEFAULT 0,
    +                tool_call_count INTEGER DEFAULT 0,
    +                api_call_count INTEGER DEFAULT 0
    +            );
    +            CREATE TABLE messages (
    +                id INTEGER PRIMARY KEY,
    +                session_id TEXT NOT NULL,
    +                timestamp REAL NOT NULL,
    +                role TEXT NOT NULL,
    +                content TEXT,
    +                tool_name TEXT,
    +                tool_calls TEXT,
    +                tool_call_id TEXT,
    +                token_count INTEGER,
    +                finish_reason TEXT,
    +                reasoning TEXT,
    +                reasoning_content TEXT,
    +                reasoning_details TEXT,
    +                codex_reasoning_items TEXT,
    +                codex_message_items TEXT
    +            );
    +
    +            CREATE VIRTUAL TABLE messages_fts USING fts5(
    +                content, content=messages, content_rowid=id
    +            );
    +            CREATE TRIGGER messages_fts_insert AFTER INSERT ON messages BEGIN
    +                INSERT INTO messages_fts(rowid, content) VALUES (new.id, new.content);
    +            END;
    +
    +            CREATE VIRTUAL TABLE messages_fts_trigram USING fts5(
    +                content, content=messages, content_rowid=id, tokenize='[redacted:secret-assignment]'
    +            );
    +            CREATE TRIGGER messages_fts_trigram_insert AFTER INSERT ON messages BEGIN
    +                INSERT INTO messages_fts_trigram(rowid, content) VALUES (new.id, new.content);
    +            END;
    +        """)
    +        conn.execute(
    +            "INSERT INTO sessions (id, source, started_at) VALUES (?, ?, ?)",
    +            ("s1", "cli", time.time()),
    +        )
    +        conn.execute(
    +            "INSERT INTO messages (session_id, timestamp, role, content, tool_name, tool_calls) "
    +            "VALUES (?, ?, ?, ?, ?, ?)",
    +            ("s1", time.time(), "assistant", "", "LEGACYTOOL",
    +             '{"function":{"name":"web_search","arguments":"{\\"q\\":\\"LEGACYARG\\"}"}}'),
    +        )
    +        conn.commit()
    +
    +        # Verify the legacy FTS rows don't contain the tool tokens yet.
    +        legacy_hits = conn.execute(
    +            "SELECT rowid FROM messages_fts WHERE messages_fts MATCH 'LEGACYTOOL'"
    +        ).fetchall()
    +        assert legacy_hits == [], "sanity: legacy FTS must NOT contain tool_name"
    +        conn.close()
    +
    +        # Now open via SessionDB — migration runs.
    +        session_db = SessionDB(db_path=db_path)
    +        try:
    +            assert len(session_db.search_messages("LEGACYTOOL")) == 1, \
    +                "v11 migration must backfill tool_name into FTS"
    +            assert len(session_db.search_messages("LEGACYARG")) == 1, \
    +                "v11 migration must backfill tool_calls JSON into FTS"
    +            # schema_version bumped
    +            row = session_db._conn.execute(
    +                "SELECT version FROM schema_version LIMIT 1"
    +            ).fetchone()
    +            version = row["version"] if hasattr(row, "keys") else row[0]
    +            assert version == 11
    +        finally:
    +            session_db.close()
    +