SQLite FTS5 missing: graceful degradation fixes

Fixes on record

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

• #35103 fix(session): survive, prevent, and explain missing SQLite FTS5

  merged to main

  files: hermes_state.py, scripts/install.sh, tests/test_hermes_state.py

  Show the diff

  • hermes_state.py

    @@ -380,6 +380,7 @@ def __init__(self, db_path: Path = None):
     
             self._lock = threading.Lock()
             self._write_count = 0
    +        self._fts_enabled = False
             try:
                 self._conn = sqlite3.connect(
                     str(self.db_path),
    @@ -388,7 +389,6 @@ def __init__(self, db_path: Path = None):
                     # handles contention instead of sitting in SQLite's internal
                     # busy handler for up to 30s.
                     timeout=1.0,
    -                # Autocommit mode: Python's default isolation_level=""
                     # auto-starts transactions on DML, which conflicts with our
                     # explicit BEGIN IMMEDIATE.  None = we manage transactions
                     # ourselves.
    @@ -724,14 +724,44 @@ def _init_schema(self):
             # FTS5 setup (separate because CREATE VIRTUAL TABLE can't be in executescript with IF NOT EXISTS reliably)
             try:
                 cursor.execute("SELECT * FROM messages_fts LIMIT 0")
    -        except sqlite3.OperationalError:
    -            cursor.executescript(FTS_SQL)
    +            self._fts_enabled = True
    +        except sqlite3.OperationalError as exc:
    +            if "no such table" not in str(exc).lower():
    +                raise
    +            try:
    +                cursor.executescript(FTS_SQL)
    +                self._fts_enabled = True
    +            except sqlite3.OperationalError as fts_exc:
    +                err = str(fts_exc).lower()
    +                if "fts5" not in err and "no such module" not in err:
    +                    raise
    +                logger.warning(
    +                    "SQLite FTS5 unavailable for %s; full-text session search "
    +                    "disabled. This usually means Hermes is running on an "
    +                    "unsupported install (e.g. a pip-installed or pip-managed "
    +                    "Python whose bundled SQLite lacks FTS5) rather than a "
    +                    "mainline install. Some features may be missing or behave "
    +                    "differently. Install the supported way: "
    +                    "https://hermes-agent.nousresearch.com (underlying error: %s)",
    +                    self.db_path,
    +                    fts_exc,
    +                )
     
             # Trigram FTS5 for CJK/substring search
             try:
                 cursor.execute("SELECT * FROM messages_fts_trigram LIMIT 0")
    -        except sqlite3.OperationalError:
    -            cursor.executescript(FTS_TRIGRAM_SQL)
    +        except sqlite3.OperationalError as exc:
    +            if "no such table" not in str(exc).lower():
    +                raise
    +            try:
    +                cursor.executescript(FTS_TRIGRAM_SQL)
    +            except sqlite3.OperationalError as fts_exc:
    +                err = str(fts_exc).lower()
    +                if "fts5" not in err and "no such module" not in err:
    +                    raise
    +                # Same FTS5-unavailable cause already warned about above for
    +                # messages_fts; the trigram table is an additional CJK index,
    +                # so just degrade silently here. CJK search falls back to LIKE.
     
             self._conn.commit()
     
    @@ -2317,6 +2347,9 @@ def search_messages(
             ignores ``sort``. The trigram CJK path honours ``sort`` like the main
             FTS5 path.
             """
    +        if not self._fts_enabled:
    +            return []
    +
             if not query or not query.strip():
                 return []
     

  • scripts/install.sh

    @@ -473,6 +473,7 @@ check_python() {
         if PYTHON_PATH="$("$UV_CMD" python find "$PYTHON_VERSION" 2>/dev/null)"; then
             PYTHON_FOUND_VERSION="$("$PYTHON_PATH" --version 2>/dev/null)"
             log_success "Python found: $PYTHON_FOUND_VERSION"
    +        ensure_fts5
             return 0
         fi
     
    @@ -482,6 +483,7 @@ check_python() {
             PYTHON_PATH="$("$UV_CMD" python find "$PYTHON_VERSION")"
             PYTHON_FOUND_VERSION="$("$PYTHON_PATH" --version 2>/dev/null)"
             log_success "Python installed: $PYTHON_FOUND_VERSION"
    +        ensure_fts5
         else
             log_error "Failed to install Python $PYTHON_VERSION"
             log_info "Install Python $PYTHON_VERSION manually, then re-run this script"
    @@ -489,6 +491,51 @@ check_python() {
         fi
     }
     
    +# Probe whether $1 (a python executable) links a SQLite with the FTS5
    +# module compiled in. Hermes' session store (hermes_state.py) creates FTS5
    +# virtual tables for full-text session search; a SQLite without FTS5 makes
    +# the bundled-python path unusable for that feature. Returns 0 if FTS5 works.
    +_python_has_fts5() {
    +    "$1" - <<'PY' 2>/dev/null
    +import sqlite3, sys
    +try:
    +    sqlite3.connect(":memory:").execute("CREATE VIRTUAL TABLE t USING fts5(x)")
    +except Exception:
    +    sys.exit(1)
    +PY
    +}
    +
    +# Guarantee the resolved uv-managed interpreter ships FTS5. uv's Python
    +# distributions only gained FTS5 in mid-2025 (python-build-standalone #694),
    +# so a stale interpreter already in uv's store — which `uv python find`
    +# happily reuses — can lack it. When that happens, force a reinstall of the
    +# latest patch for $PYTHON_VERSION (which has FTS5) and re-resolve. This keeps
    +# the supported install path's session search working without bundling a
    +# second SQLite or asking the user to do anything.
    +ensure_fts5() {
    +    [ -n "${PYTHON_PATH:-}" ] || return 0
    +    if _python_has_fts5 "$PYTHON_PATH"; then
    +        return 0
    +    fi
    +
    +    log_warn "Resolved Python's SQLite lacks the FTS5 module (session search needs it)."
    +    log_info "Reinstalling a current Python $PYTHON_VERSION with FTS5 via uv..."
    +    if "$UV_CMD" python install "$PYTHON_VERSION" --reinstall >/dev/null 2>&1; then
    +        PYTHON_PATH="$("$UV_CMD" python find "$PYTHON_VERSION" 2>/dev/null)"
    +        PYTHON_FOUND_VERSION="$("$PYTHON_PATH" --version 2>/dev/null)"
    +    fi
    +
    +    if [ -n "${PYTHON_PATH:-}" ] && _python_has_fts5 "$PYTHON_PATH"; then
    +        log_success "FTS5 available ($PYTHON_FOUND_VERSION)"
    +    else
    +        # Could not obtain an FTS5-capable interpreter (offline, pinned env,
    +        # etc.). Install proceeds — Hermes degrades gracefully and disables
    +        # only full-text session search — but warn so it isn't a silent gap.
    +        log_warn "Could not obtain an FTS5-capable Python. Hermes will run, but"
    +        log_warn "full-text session search will be disabled until FTS5 is present."
    +    fi
    +}
    +
     check_git() {
         log_info "Checking Git..."
     

  • tests/test_hermes_state.py

    @@ -1,11 +1,35 @@
     """Tests for hermes_state.py — SessionDB SQLite CRUD, FTS5 search, export."""
     
    +import sqlite3
     import time
     import pytest
     
     from hermes_state import SessionDB
     
     
    +class _NoFtsCursor(sqlite3.Cursor):
    +    """Simulate a SQLite build without the fts5 module."""
    +
    +    def execute(self, sql, parameters=()):
    +        probe = sql.strip()
    +        if probe in (
    +            "SELECT * FROM messages_fts LIMIT 0",
    +            "SELECT * FROM messages_fts_trigram LIMIT 0",
    +        ):
    +            raise sqlite3.OperationalError("no such table: " + probe.split()[-3])
    +        return super().execute(sql, parameters)
    +
    +    def executescript(self, sql_script):
    +        if "USING fts5" in sql_script:
    +            raise sqlite3.OperationalError("no such module: fts5")
    +        return super().executescript(sql_script)
    +
    +
    +class _NoFtsConnection(sqlite3.Connection):
    +    def cursor(self, factory=None):
    +        return super().cursor(factory or _NoFtsCursor)
    +
    +
     @pytest.fixture()
     def db(tmp_path):
         """Create a SessionDB with a temp database file."""
    @@ -135,6 +159,33 @@ def test_parent_session(self, db):
             child = db.get_session("child")
             assert child["parent_session_id"] == "parent"
     
    +    def test_db_initializes_without_fts5_module(self, tmp_path, monkeypatch):
    +        real_connect = sqlite3.connect
    +
    +        def connect_without_fts(*args, **kwargs):
    +            kwargs["factory"] = _NoFtsConnection
    +            return real_connect(*args, **kwargs)
    +
    +        monkeypatch.setattr("hermes_state.sqlite3.connect", connect_without_fts)
    +
    +        db = SessionDB(db_path=tmp_path / "state.db")
    +        try:
    +            assert db._fts_enabled is False
    +            # Neither FTS5 virtual table should have been created on a build
    +            # that lacks the fts5 module — both init paths must degrade.
    +            assert db._fts_table_exists("messages_fts") is False
    +            assert db._fts_table_exists("messages_fts_trigram") is False
    +
    +            db.create_session(session_id="s1", source="cli")
    +            db.append_message("s1", role="user", content="hello from sqlite without fts")
    +
    +            messages = db.get_messages("s1")
    +            assert len(messages) == 1
    +            assert messages[0]["content"] == "hello from sqlite without fts"
    +            assert db.search_messages("hello") == []
    +        finally:
    +            db.close()
    +
     
     # =========================================================================
     # Message storage