Summary

Eight kanban.db SQLite hardening fixes consolidated into one PR for batch review. Each commit is atomic, originally filed as a separate draft PR (kept open as fallback), and now validated together against a 607-test kanban + state suite (all green).

Pattern follows the precedent in #23791.

Closes #31158. Refs #29610, #30846, #30896, #30973, #31014, #31130.

Why batch this

Over the last two weeks I filed nine separate draft PRs (#30727, #31208, #31294, #31310, #31891, #31932, #32300, #32301, #32489) covering the same broad surface: kanban.db corruption events I hit running a five-profile kanban fleet under sustained writer load. Eleven distinct corruption events surfaced over that window, spanning several bug classes (torn-write on freed pages, single-row mid-write, b-tree split race during concurrent writers, EIO during WAL init, post-WAL-init (deleted) sidecar accumulation, zombie worker accumulation). The investigation pointed at a single upstream root cause for the cluster (commit 8 below) plus seven defense-in-depth contributors.

Shipping them as one PR makes the story reviewable in one pass instead of nine. Each commit is still cherry-pickable if you'd rather land them surgically; the original drafts remain open and pinned to their respective branches.

#31891 (merge complete_task + recompute_ready into one txn) is not in this bundle. I ran a week of production load with all of 1-8 applied and that PR not applied, and saw no idx_tasks_status drift. I'd rather not ship it on speculation.

What's in here, in commit order

The order is defensive primitives first, then EIO handling, then dispatcher hygiene, then write-side detection, then process hygiene, then the headline root cause last. Reading top-to-bottom traces the same story the investigation went through.

1. fix(kanban): harden SQLite against torn-write corruption (was #31208)

Sets secure_delete=ON, cell_size_check=ON, synchronous=FULL on every kanban connect(). After a torn write, freed pages contain zeros instead of stale cell text, so corruption is detectable and stale rows can't resurface as live data.

2. fix(state): never silently downgrade WAL to DELETE on transient EIO (was #31294)

apply_wal_with_fallback previously caught any OperationalError containing one of the NFS/SMB markers and silently dropped to DELETE. A transient EIO (kernel I/O backoff under memory pressure) was matching one of those markers and downgrading a healthy WAL DB to DELETE in place. Tightens the marker list and adds an on-disk header check before downgrading: if the file is already WAL on disk, re-raise instead of rewriting it.

3. fix(kanban): preserve original exception when write_txn rollback fails (was #31310)

On an EIO or auto-rollback inside write_txn, the explicit ROLLBACK statement raises a secondary OperationalError that shadows the original. Caller gets "no transaction is active" instead of "disk I/O error". The wrapper now catches the rollback error and re-raises the original.

4. fix(kanban): add grace period to detect_crashed_workers (was #30727)

When two dispatchers race during gateway restart, the new one's detect_crashed_workers sweep can reclaim a task that the old dispatcher is mid-spawning, causing a respawn storm. Adds a CRASH_GRACE_SECONDS window so a task whose claimed event landed within the last N seconds is not reclaimed.

5. fix(gateway): replace permanent corrupt-board latch with exponential backoff (was #31932)

The disabled_corrupt_boards dict permanently latched boards on any OperationalError matching "file is not a database". A transient EIO during connect() would short-read the SQLite header, raise that exact message, and strand the board until manual gateway restart. Replaces with exponential backoff (30s → 30min cap) plus a header-magic-byte recheck: if the first 16 bytes are SQLite format 3\0, the file is real and the error was transient.

6. fix(kanban): add post-commit page_count invariant check to write_txn (was #32300)

Reads bytes 28-31 of the DB header after every COMMIT and compares against os.path.getsize() / page_size. Raises sqlite3.DatabaseError on torn-extend (header claims more pages than the file has). Also sets PRAGMA wal_autocheckpoint=100 so the WAL doesn't grow unbounded under contention. Surfaces Bug F damage at commit time instead of on the next read.

7. fix(kanban): hoist zombie reaper out of dispatch_once (was #32301)

dispatch_once aborted on any per-board connect() failure, but the only os.waitpid(-1, WNOHANG) reaper call lived inside that same function past the connect. One transient EIO → reaper skipped → zombies accumulated → task_runs rows left with worker_pid pointing at defunct PIDs. Moves the reaper into _kanban_dispatcher_watcher so it runs at the top of every tick regardless of per-board outcomes. Also fixes a test-indentation bug from an earlier salvage. (Squashed from three sibling commits in #32301 for review.)

8. fix(kanban): skip redundant WAL pragma on already-WAL connections (was #32489)

The root cause. Every kanban connect() was running PRAGMA journal_mode=WAL even when the DB header already reported WAL mode. SQLite's WAL-init path treats that as "set up WAL from scratch": checkpoint, unlink the existing -shm/-wal sidecars, recreate them under the current connection's lock state. If any other process or thread had those sidecar inodes open at that instant (notifier-watcher polling every 5s, dashboard-watcher polling every 10s, dispatcher tick every 60s, any active worker), the unlinked inodes became (deleted) fds in those processes. Their in-process mmap regions kept pointing at vanished disk pages. The next connect() from any of those processes opened fresh inodes for the sidecars, but the long-lived watcher-thread connections still had the old (deleted) mappings pinned. Subsequent PRAGMA journal_mode=WAL on those stale-state connections raised sqlite3.OperationalError("disk I/O error"). Under enough write pressure the unlink-and-recreate dance raced with a concurrent writer's b-tree split and the next commit landed structurally damaged on disk (Class-C corruption).

The fix is a cheap read-only PRAGMA journal_mode probe before the set-pragma. If the result is already 'wal', return early. Mode is durable in the file header (byte 18 = 2 for WAL), so the first connection in the process primes WAL once and every subsequent connection is observation-only, with no unlink and no race. The set-pragma path still runs on first-connection-per-process, which is where the residual rate comes from; see "What this does not fix" below.

Approaches I tried locally and reverted

Worth surfacing so reviewers don't redo this work:

• Shared per-board _kb_conn cache (fix(gateway): use shared per-board kanban connection to prevent WAL inode-rotation race #32226). Tested locally and reverted after production corruption was confirmed within hours. Concurrent thread access to one SQLite connection violates SQLite's threading model under any meaningful load. fix(gateway): use shared per-board kanban connection to prevent WAL inode-rotation race #32226 is closed.
• TLS per-thread connection cache (fix(gateway): cache kanban DB connections per OS thread in GatewayRunner #32322). Tested locally and reverted: reduced connection count but did not eliminate the unlink itself, any code path that opened a fresh connection still triggered it. Same wedge symptom at lower frequency. fix(gateway): cache kanban DB connections per OS thread in GatewayRunner #32322 closed.
• Held-BEGIN + sqlite_master pinner (fix(gateway): add WAL pinner to hold shared lock and prevent sidecar unlink (Bug I.2) #32531). Tested locally and reverted. The "correct pinner pattern" from the prior-art comments. Pinner only helps for already-WAL connections, so it needs the redundant-WAL-pragma skip (fix(kanban): skip redundant WAL pragma on already-WAL connections #32489) to actually prevent the unlink it's trying to block. On its own it doesn't help. An earlier naive variant (bare SELECT 1) was also tested locally and reverted. fix(gateway): add WAL pinner to hold shared lock and prevent sidecar unlink (Bug I.2) #32531 closed.

The pattern across all three: they tried to make the unlink "less likely" by changing the connection topology. Commit 8 in this PR removes the unlink at the source instead.

What this PR does not fix

First-connection-per-process still hits the WAL-init path, because WAL must be primed once. Any fresh process spawning under write pressure can still produce one unlink event. Three of the commits above (the on-disk header check in #2, the header-magic peek in #5, the post-commit page_count check in #6) catch and either prevent or surface the resulting damage, but they do not eliminate it.

Empirically, with this entire stack live, my fleet has seen one to two Class-C events per week on the highest-write-volume board, down from one to two events per day before any of this landed. A future fix that serializes first-connection WAL init across processes (a flock around PRAGMA journal_mode=WAL, which I'm running locally as a separate patch) would close the residual gap. Not in this PR.

Coordination with PR #32759

@MoonRay305 filed #32759 (fix(kanban): harden SQLite connection concurrency) covering related territory: first-connect flock, busy_timeout, and refusing auto-init on malformed DBs. There's no file overlap with this PR (#32759 touches hermes_cli/kanban_db.py only, mostly different functions; the AUTHOR_MAP addition won't conflict). Suggested merge order: this PR first as the root-cause fix, then #32759 as defense-in-depth on top. If you prefer the opposite, the only rebase work needed on my side would be on commit 1.

Testing

.venv/bin/python -m pytest tests/hermes_cli/test_kanban_db.py tests/test_hermes_state.py tests/test_hermes_state_wal_fallback.py tests/hermes_cli/test_kanban_dispatcher_resilience.py tests/hermes_cli/test_kanban_core_functionality.py

Result: 607 passed.

The new tests added by these commits cover each fix individually. Most useful new fixtures: tests/hermes_cli/test_kanban_dispatcher_resilience.py (corrupt-board latch behavior under transient EIO) and the WAL-skip probe tests in tests/test_hermes_state.py.

Production validation

These commits have been running on local/runtime of my fork against five gateway processes (default + writing + admin + coder + researcher) on a Linux LXC for one to seven days each, depending on the commit. The writing board sustains roughly 200-400 commits per hour under typical workloads, mostly from dispatcher tick + worker heartbeat + event log writes. EIO rate dropped from hundreds per day to single digits.

PR scope checklist

• Conventional commits
• Eight atomic, cherry-pickable commits, each from a previously-filed draft
• No new features, no API changes, no schema migrations
• All changes are kanban / SQLite hardening; no unrelated drive-by edits
• AUTHOR_MAP entry for steveonjava (folded into commit 1)
• Closes kanban dispatcher wedges under multi-thread + subprocess concurrency due to WAL/SHM cache poisoning #31158 in PR body. Refs Bug: embedded Kanban dispatcher still leaks sqlite/WAL file descriptors after #28301 #29610, fix: BTRFS COW + SQLite WAL incompatibility — disk I/O errors on BTRFS #30846, [Bug]: Kanban: rapid worker spawn-crash loop (sub-2s/crash) corrupts board SQLite B-tree before failure_limit trips #30896, feat(kanban): opt-in HERMES_KANBAN_SYNCHRONOUS_MODE for synchronous=FULL durability hardening #30973, fix: remove disk i/o error from WAL incompatibility markers #31014, fix(kanban-db): WAL file descriptor leak on connect/close cycles (fixes #30799) #31130.
• Local pytest run green (607 passed)
• I've tested on my platform: Linux 6.17.13 (LXC on Proxmox), Python 3.13, ext4 on NVMe.