Summary

• serialize Kanban DB writes across processes with a bounded sidecar write lock
• run first-connect/schema/WAL initialization under the same DB write lock
• avoid WAL-mode false positives from the post-commit main-file length invariant
• add regression coverage for timeout behavior, schema-init locking, write_txn lock scope, symlinked DB lock canonicalization, Windows byte-range lock setup, and WAL invariant gating

Why

Kanban can have multiple worker processes opening the same board database during dispatcher bursts. SQLite WAL and busy_timeout handle normal lock waiting, but they do not give Hermes a single process-wide critical section around schema initialization and multi-statement write_txn() calls. This leaves DDL/DML and concurrent write bursts vulnerable to corrupt-board outcomes.

This patch adds a bounded DB-scoped sidecar lock that is shared by connect() initialization and write_txn(). It also keeps the existing main-file torn-extend check for non-WAL modes while skipping that check in WAL mode, where valid frames can live in the WAL while the main DB file lags.

Scope

Changed files:

• hermes_cli/kanban_db.py
• tests/hermes_cli/test_kanban_db.py

Out of scope:

• dispatcher recovery UX
• corrupt-backup cleanup or quarantine policy
• gateway supervision/orphan-process behavior
• notification or messaging adapters
• dashboard/UI changes
• unrelated CI/web-server flake fixes; see test(web-server): deflake pub event broadcast coverage #37359

Related work

Related: #37344, #35770, #35787, #37292, #33482, #33696, #37359

This PR keeps the write-lock fix Kanban-only and includes tests for the lock boundaries and WAL invariant behavior. The unrelated web-server broadcast flake remains split into #37359.

Fix boundary / coverage

This PR intentionally covers the full Kanban DB write boundary rather than only one call site:

• connect() initialization: header/integrity checks, WAL activation, and schema migration run under the DB write lock so first-connect DDL cannot race ordinary writes.
• write_txn(): the same DB-scoped lock is held from BEGIN IMMEDIATE through COMMIT and the post-commit invariant check.
• bounded contention: sidecar lock acquisition times out with a clear SQLite operational error instead of waiting indefinitely.
• path identity: sidecar lock paths are derived from the resolved DB path, with regression coverage for symlinked DB access.
• Windows behavior: both .init.lock and .lock sidecars are pre-seeded before byte-range locking, with tests covering the non-empty lock-file requirement.
• WAL correctness: the main-file length invariant is skipped in WAL mode, where valid committed frames may still live in the WAL while the main DB file lags.

The related PRs and historical fixes are useful context; this patch keeps the scope limited to the Kanban SQLite lock/invariant boundary and its regression tests.

Verification

• python -m pytest tests/hermes_cli/test_kanban_db.py -q -o 'addopts='
  • 211 passed
• targeted lock/WAL regression subset
  • 7 passed
• python -m py_compile hermes_cli/kanban_db.py tests/hermes_cli/test_kanban_db.py
• git diff --check origin/main...HEAD
• multi-process smoke: 8 worker processes x 40 create_task calls against one DB
  • quick_check=ok
  • integrity_check=ok
  • tasks=320
  • journal=wal
• independent review: passed after fixing the Windows zero-byte sidecar issue for both .init.lock and .lock