2026-09-04 PM — state.db Structural Corruption Recurrence
On September 4, 2026, the full SQLite integrity check detected a third structural-corruption manifestation in the Hermes state.db family. The damaged object was the canonical system_prompts table, not the FTS index: a full row scan failed with database disk image is malformed, while a shallow count still passed. The source was preserved, an initial recovery output was rejected, and the live database was subsequently repaired offline, restarted, and verified.
This record extends the September 1-2 state.db postmortem and the September 3 deleted-inode postmortem. The generic procedure is state.db Recovery — Procedure; the operator log for the recovery work is retained separately when available.
Incident summary
Section titled “Incident summary”| Aspect | Detail |
|---|---|
| Incident ID | INC-2026-09-state.db-recurrence-02 (same family as INC-2026-09-state.db) |
| Date | September 4, 2026 (UTC) |
| First clean reference | Full integrity verification at 2026-09-03 12:18 (recorded during the previous incident work) |
| Detection | Daily snapshot at 02:00 aborted; full integrity check later identified Tree 3, system_prompts root page 3 |
| Impact | Structural corruption in one canonical table; services were kept stopped during diagnosis and repair |
| Recovery | Offline targeted salvage; the first generated recovery output was rejected, then the live database was repaired and verified clean |
| Data status | The initial probe read 194 of 195 system_prompts rows; the unreadable row was later shown to be referenced by a session. After the repair, live-state verification recorded integrity_check = ok; see the operator session record for the repair outcome |
| Root cause | Exact trigger unproven. The recurrence confirms that the current safeguards detect damage but do not yet identify the write that introduced it |
Timeline (evidence)
Section titled “Timeline (evidence)”| When (UTC) | Event | Evidence |
|---|---|---|
| Sep 3 12:18 | Last full integrity check recorded as clean before the recurrence window | Incident-session verification record |
| Sep 4 02:00 | Hermes state.db Daily Snapshot aborts because the full integrity check reports Tree 3 page 8185 cell 0: invalid page number 218103808 and Page ...: never used entries |
Cron result and state_db_backup.sh output |
| Sep 4 18:04 | Gateway and dashboard are stopped for controlled maintenance; a precautionary state.db.pre-optimize-20260904_180414 copy is created |
systemd journal and file metadata |
| Sep 4 18:04 | hermes sessions optimize-storage --yes reports Search index is already on the compact layout — nothing to do |
CLI output; the database already has fts_storage_version = 1 and external-content FTS tables |
| Sep 4 18:05 | Full PRAGMA integrity_check fails on the live file and the precautionary copy; quick_check had not exposed the defect |
SQLite probe on both files |
| Sep 4 18:05 | hermes sessions repair --check-only reports database disk image is malformed; no replacement is installed |
CLI output |
| Sep 4 18:05 | hermes sessions recover --source ... --inspect-only reports recoverable: true, but a recovery output fails verification because system_prompts cannot be copied cleanly and foreign-key violations remain |
Recovery inspection and JSON report |
| Sep 4 18:08 | Offline row-level probe reads 194 rows; rowid 194, hash prefix b2b6e163a9ebc5af, fails. The source contains 195 session references, including that hash |
SQLite probe and FK query |
| Sep 4 onward | Services are reopened only after the controlled repair and post-restart checks | systemd journal and subsequent watchdog verification |
What was confirmed
Section titled “What was confirmed”Full checks catch defects that shallow checks miss
Section titled “Full checks catch defects that shallow checks miss”The daily snapshot script uses a full PRAGMA integrity_check on its staged SQLite backup. That check aborted before publishing a new snapshot, preserving the previous verified snapshot instead of replacing it with a damaged artifact. A smaller query such as COUNT(*) was not sufficient: the count could use an index path without traversing the damaged table pages, whereas SELECT * FROM system_prompts failed.
The final recovery also exposed a second limitation: the source database was marked recoverable: true by the inspection phase, but the generated output was still rejected because its report contained verified: false, complete: false, a failed system_prompts copy, and remaining foreign-key violations. recoverable therefore means “the tool can attempt a reconstruction”, not “the result is safe to install”.
The operational rule is therefore explicit:
quick_checkor a successful count is not proof thatstate.dbis healthy;- use the full
integrity_checkandforeign_key_checkbefore accepting a snapshot or recovered database; - never install a recovery output solely because
recover --inspect-onlysaysrecoverable: true.
The damage was not an optimize-storage migration failure
Section titled “The damage was not an optimize-storage migration failure”The maintenance command did not migrate this database: the live schema already used the compact FTS layout, with state_meta.fts_storage_version = 1, messages_fts backed by messages, and matching canonical/FTS row counts. The damaged object was system_prompts, whose table root page was 3. The corruption was already present in the pre-maintenance copy.
Recovery preserved the safety boundary
Section titled “Recovery preserved the safety boundary”The first recovery output was rejected because its report had verified: false, complete: false, a failed system_prompts copy, and remaining foreign-key violations. It was not installed. The operator instead used the offline repair path with a preserved source copy and an explicit row-level inventory. The unreadable row could not be declared harmless: the first probe incorrectly called it orphaned, but the subsequent reference query showed that all 195 hashes were referenced by sessions. That correction is part of the incident record and is why the runbook requires checking references before deleting any row.
Root-cause analysis
Section titled “Root-cause analysis”Proven
Section titled “Proven”- Structural corruption existed in
state.dbbefore the Sep 4 maintenance command: the same failure appeared in the pre-maintenance copy. - The damaged tree corresponded to
system_prompts, root page 3; a full scan failed while a count could pass. - The daily snapshot job detected the damage and did not publish a replacement snapshot.
optimize-storagewas a no-op on this already-migrated compact FTS layout.- The standard recovery tool correctly refused to produce an installable result when the
system_promptstable copy and foreign-key verification failed.
Unproven
Section titled “Unproven”The exact write, process, or filesystem event that damaged the system_prompts table remains unknown. The available evidence does not prove whether the original corruption and the Sep 4 recurrence share one trigger. The prior incident established live FTS surgery and sidecar handling as proven aggravators for the Sep 1-2 event, but that evidence must not be reused as proof for this canonical-table recurrence.
Detection and response
Section titled “Detection and response”| Phase | Assessment |
|---|---|
| Detection | Improved: the full check in the daily snapshot cron found the defect before a user-facing write failure. The freshness watchdog alone would not have detected it. |
| Diagnosis | Correct after escalation: full integrity check, source preservation, repair --check-only, and recover --inspect-only were used before any write. |
| Recovery | Safe but not lossless: the verified recovery output was rejected; targeted offline salvage was then used. One referenced prompt row remained unreadable in the source. |
| Maintenance classification | Corrected during the incident: optimize-storage was not the repair and did not cause the defect. |
What went right / what went wrong
Section titled “What went right / what went wrong”Went right
Section titled “Went right”- The daily snapshot was validated before publication and kept the prior verified snapshot when the new check failed.
- The full check caught a defect that quick probes could miss.
- Services were stopped before any write-side repair.
- The failed recovery output was not installed.
- The database source and pre-maintenance copies were retained for comparison.
Went wrong
Section titled “Went wrong”- The previous monitoring set did not distinguish full structural integrity from snapshot freshness; a fresh-but-invalid database can exist until the full snapshot check runs.
- The first row-level interpretation was wrong: readability alone was treated as evidence that the row was unreferenced. The reference query corrected this before deletion.
- The exact corruption trigger remains uninstrumented; current safeguards are detectors and recovery controls, not causal telemetry.
- The initial maintenance plan incorrectly assumed an FTS v22→v23 migration was pending. The live schema inspection corrected that assumption.
Action plan
Section titled “Action plan”| # | Action | Status | Evidence / next step |
|---|---|---|---|
| 1 | Keep the daily WAL-safe snapshot atomic: stage with SQLite backup, run full integrity_check + foreign_key_check, gzip only after validation, publish with atomic rename, preserve the previous snapshot on failure |
Done | ~/.hermes/scripts/state_db_backup.sh; verified during this incident |
| 2 | Keep the full read-only health watchdog for the main DB and profile DBs, including deleted state.db/WAL/SHM descriptors |
Done | ~/.hermes/scripts/state_db_health_watchdog.py; hourly cron, local-only output |
| 3 | Keep the active-log watchdog for FTS corruption and write-failure signatures | Done | cron state.db Corruption & Write-Failure Watchdog, every 5 minutes; script ~/.hermes/scripts/fts_repair_watchdog.sh; deduplicated Slack alerts |
| 4 | Keep the snapshot-freshness watchdog separate from snapshot validity | Done | ~/.hermes/scripts/state_db_snapshot_freshness.sh; every 6h, Slack only on stale/missing snapshot |
| 5 | Treat recoverable: true as an inspection result, never as permission to install; require verified: true, complete: true, no integrity/FK errors, and canonical-table coverage |
Done in runbook | Recovery procedure |
| 6 | Require reference analysis before dropping an unreadable canonical row; record the hash, rowid, referencing sessions, and data-loss decision | Done in procedure | Recovery procedure |
| 7 | Determine the causal write through upstream instrumentation: identify the writer, operation, SQLite error, file identity, and sidecar state around each integrity transition | Open — upstream | Documented in the Proposed Upstream state.db Report; do not post automatically |
| 8 | Reassess whether local snapshots need an off-host encrypted destination; same-disk snapshots do not protect against VPS/disk loss | Open — estate decision | Cost and privacy review required before implementation |
| 9 | Add an upstream documentation issue/PR for sessions recover, repair, and optimize-storage, including flags and a damaged-state.db walkthrough |
Open — upstream | Follow the official contributing guide and duplicate search first |
The upstream follow-up is documented separately in Proposed Upstream state.db Report, including the sanitized message draft and the contribution checklist. It is not posted.