SQLite store corrupts under concurrent writes — no WAL, no backup, no repair path

[VVeb1250]

Title: SQLite store corrupts under concurrent writes — no WAL, no backup, no repair path

Summary

memories.db corrupted into database disk image is malformed during normal multi-process use. All writes failed (failed to open database) while read-only commands partially worked. There is no built-in detection, backup, or recovery path — recovery required external sqlite3 .recover + manual FTS rebuild + manual file swap, with partial data loss.

• Version: icm 0.10.53 (also: not addressed in release notes through latest v0.10.57; no existing issue for WAL/durability/corruption/backup).
• OS: Windows 11.

Impact

This is load-bearing: ICM is positioned as a durable cross-host brain shared by multiple agents (Claude Code + Codex etc.) calling icm.exe concurrently. A store that can silently corrupt under that exact intended usage — with no backup or repair — is a critical durability gap. In our case ~half the memories were unrecoverable.

Repro / trigger (observed)

Heterogeneous agents writing concurrently to the same memories.db: a session-start mesh/presence write coincided with store/hook activity. Shortly after, every write failed:

$ icm store -t lessons -c "..." -i high -k "..."
Error: failed to open database
Caused by: database error: database disk image is malformed

PRAGMA integrity_check on a copy showed btree/index/FTS damage (Tree 2/22/42 invalid page number, 2nd reference to page, btreeInitPage() returns error code 11, plus wrong # of entries in index ... across idx_memories_*, idx_facts_*, and memories_fts). Base tables (memories, facts) were largely intact; the damage was concentrated in indexes + FTS shadow tables — consistent with an interrupted/concurrent write without WAL.

The live DB was not in WAL mode (rollback journal), which makes concurrent writers far more corruption-prone.

Recovery that worked (for reference / to codify)

1. cp memories.db memories.corrupt.bak (preserve)
2. sqlite3 corrupt.bak ".recover" | sqlite3 recovered.db (salvaged more than a plain SELECT btree walk could)
3. INSERT INTO memories_fts(memories_fts) VALUES('rebuild'); (+ concepts/feedback/messages FTS) → PRAGMA integrity_check = ok
4. atomic swap recovered.db into place; corrupt archived.

Proposed fix

1. Open the DB in WAL mode + set busy_timeout by default. WAL allows multiple readers + one writer safely across processes on local disk and is the single biggest mitigation for this corruption class. (Caveat to document: WAL is unsafe on network filesystems — relevant if the brain is synced cross-machine.)
2. icm doctor (or icm check) runs PRAGMA quick_check/integrity_check and reports DB health honestly (degraded vs ok), instead of only checking hook paths.
3. icm repair command wrapping the recovery runbook above (backup → .recover → FTS/vector rebuild → integrity verify → atomic swap). Turns a multi-step manual rescue into one safe command.
4. Automatic timestamped backup + rotation (e.g. on SessionStart hook or a write/age threshold) so corruption never means total loss. A consistent online backup via the SQLite backup API (not raw copy) is correct under WAL.
5. Export/import (JSONL) for portability + a recovery floor independent of SQLite internals.

Related

While recovering, the #185 finding "Hook-prompt fallback dumps top-weight memories on irrelevant queries" (extract.rs:219-239) reproduced for us as well: conceptual/abstract recall queries returned generic top-weight rows instead of relevant matches. Mentioning in case durability + recall-fallback get batched.

[Donach]

I've had this exact problem, and I "solved" it by not using sqlite at all, but instead libsql/Turso, which is like "successor" to sqlite, see my PR #262

I wanted to keep my fork work minimal, so the changes should be fairly straightforward and it would fix your problem as well.

[VVeb1250]

@Donach yo that's fire 🔥 going straight to libsql/Turso instead of band-aiding SQLite is such a goat move, that's actually solving the real issue. respect