```
Error: optimization failed: no such table: messages_fts_trigram
No data was lost. Re-run to resume.
```
on any install where the trigram FTS index is legitimately absent. The failure is
deterministic — re-running can never make progress, because the crash happens at the same
point every time — so the database is permanently stuck on the legacy high-footprint FTS
layout with no supported way forward.
Observed on a 5.4 GB production `state.db`. After the fix the same database optimized
successfully and shrank to 3.3 GB.
The trigram index is absent whenever the runtime cannot maintain it. On a SQLite build
without the `trigram` tokenizer, `_ensure_fts_schema()` returns `False`, so `__init__`
leaves `self._trigram_available = False` and no `messages_fts_trigram` table on disk. This
is a **supported degraded runtime**, not damage — CJK/substring search falls back to
`LIKE` and everything else works normally. `_is_fts5_unavailable_error()` and
`_warn_trigram_unavailable()` exist specifically to make this path graceful.
Two code paths write the boundary sweep for the deferred FTS rebuild, and only one of them
respects that flag:
| Function | Trigram `INSERT` guarded? |
|---|---|
| `fts_rebuild_step()` | ✅ `if include_trigram:` where `include_trigram = self._trigram_available` |
| `_fts_rebuild_finish()` | ❌ unconditional |
`_fts_rebuild_finish()` runs the boundary sweep at the *end* of the backfill. Its
unguarded `INSERT INTO messages_fts_trigram …` raises `OperationalError`, which propagates
out of `optimize_fts_storage()` and aborts the entire optimization — *after* the backfill
has already completed. Hence the characteristic output showing 100% progress immediately
before the error:
```
Rebuilding index: 100% (909,671/909,671)
Error: optimization failed: no such table: messages_fts_trigram
```
There is a second, quieter consequence. The teardown phase that reclaims the demoted
`fts_v22_trash_*` shadow tables runs *after* the backfill phase in
`optimize_fts_storage()`. Because the crash happens before teardown is ever reached, those
tables are never emptied or dropped — so the space the migration was supposed to reclaim
stays allocated indefinitely, and the leftover trash tables look (misleadingly) like
evidence of a half-finished migration.
Build a populated v23 database, set the deferred-rebuild markers, then reopen it on a
runtime where `_ensure_fts_schema('messages_fts_trigram', …)` returns `False` (exactly
what a SQLite build without the trigram tokenizer produces) and call
`optimize_fts_storage()`:
```
[precondition] trigram absent, _trigram_available=False, rebuild pending ✓
RED ✗ optimize_fts_storage raised OperationalError: no such table: messages_fts_trigram
```
With this patch applied, unchanged harness:
```
optimize_fts_storage returned {'ok': True, 'vacuumed': None}
GREEN ✓ optimize ok; markers cleared; base FTS 'zebra' -> 200 hits
```
Full harness and transcripts in `TEST-EVIDENCE.md`.
Gate the sweep on `self._trigram_available`, exactly as `fts_rebuild_step()` already does:
```python
include_trigram = self._trigram_available
def _do(conn):
...
if include_trigram:
conn.execute("INSERT INTO messages_fts_trigram(...) ...")
```
The base `messages_fts` sweep and the marker cleanup are untouched, so the rebuild still
finalizes correctly and the index remains complete for every row it is responsible for.
The fix does not disable or weaken search to dodge the error — the regression tests assert
that base FTS still returns results afterwards.
`TestFtsRebuildFinishWithoutTrigram` in `tests/test_hermes_state.py`:
- `test_rebuild_finish_skips_trigram_when_unavailable` — drives `_fts_rebuild_finish()`
directly on a trigram-less runtime; asserts it completes, clears both rebuild markers,
and leaves base FTS searchable.
- `test_optimize_fts_storage_succeeds_without_trigram` — end-to-end through the public
`optimize_fts_storage()` entry point; asserts `ok=True`, markers cleared, search intact.
Both use the existing `_NoTrigramConnection` helper already in the file. Both fail on
`main` with `no such table: messages_fts_trigram` and pass with this patch.
`tests/test_hermes_state.py` passes in full (463 tests → 465 with these two). `ruff` clean.
This PR is the crash only.
A companion PR narrows `_db_opens_cleanly()` so that
`hermes sessions repair --check-only` stops reporting a write-broken FTS schema as
healthy — the gap that makes this class of problem hard to diagnose in the first place.
The two are independent and can land in either order.
_reset_fts_index_to_empty used a no-WHERE DELETE, whose docstring
claimed FTS5 treats it as an efficient drop-all. That's true only for
ordinary rowid tables — on external-content FTS5 each deleted row's
tokens are regenerated from the content table, making it O(rows)
(measured ~12us/row: 0.22s @100K, 5.2s @400K, ~25s projected @2M) while
holding the write lock. It also corrupts the index when indexed rows
have diverged from messages — exactly the broken-bookkeeping shape this
repair path handles. The FTS5 'delete-all' special command is the
documented O(1) truncate for external-content tables (measured 1.6ms
@100K) and truncates unconditionally regardless of divergence.
_fts_external_index_empty_with_messages runs on every writable open via
the _init_schema fts_storage_version stamp condition. COUNT(*) is a full
b-tree scan on both messages and messages_fts_docsize (~100ms per open
on a 2M-row DB, measured); the function only ever compares against
zero, so EXISTS(SELECT 1 ...) gives the identical boolean in O(1).
Demote wrote the empty v23 schema via executescript inside BEGIN IMMEDIATE,
which commits early and can leave trash + empty indexes without rebuild
markers. Re-run then tore down trash and stamped fts_storage_version with
docsize=0, permanently losing historical session search.
Stage markers with the demote, create schema only after they are durable,
heal empty-index bookkeeping on resume, and refuse settle until the base
index is populated. Settle refusal returns ok=False instead of raising,
and resume fails fast if the base v23 table cannot be re-created.
Orphan-marker repair only resets a missing fts_rebuild_progress to 0 once
the index is known empty: the chunk worker replays its whole selected id
range without an anti-join, so a partially indexed DB that lost only its
progress key is first reset to a known-empty surface, then rebuilt.
Ported onto the SessionDB mixin split (hermes_state_search.py /
hermes_state_schema.py).
Kyzcreig
Jakub Wolniewicz
kshitijk4poor
Adolanium
teknium1