Closely related to #31158 (which I just opened), but the failure modes are not identical:

• This PR's symptom: WAL FDs accumulate over time → eventual "too many open files" / "unable to open database file"
• My kanban dispatcher wedges under multi-thread + subprocess concurrency due to WAL/SHM cache poisoning #31158 symptom: WAL/SHM FDs in the long-running gateway end up referencing deleted inodes (verifier subprocess's connection close triggers SQLite's "last connection cleanup" while gateway threads are momentarily between connect()s, unlinking -wal/-shm; gateway's existing mmaps then point at the deleted-inode). Surfaces as sqlite3.OperationalError: disk I/O error on SELECTs in release_stale_claims.

Same underlying class (gateway-side WAL FD lifecycle in a long-running multi-threaded process with concurrent multi-process writers), different specific manifestation.

The _WalSafeConnection approach in this PR — PRAGMA wal_checkpoint(TRUNCATE) before each close() — might also resolve my wedge: the checkpoint should force WAL consolidation so a closing subprocess's "last connection cleanup" finds nothing to unlink, leaving the gateway's other connections with valid (not deleted) mmap targets. But I haven't validated that, and it's an inference from SQLite's documented per-process WAL state semantics — not a measurement.

I have a deterministic reproduction of the #31158 wedge (4 verifier-subprocess completions on a single board → wedge; restart resolves; reproduces on next run). Happy to apply this PR's patch locally and run my repro against it to either confirm or rule out that #31130 also addresses the deleted-inode-FD class, if that's useful data for moving this PR forward.

Currently I have a separate local workaround on #31158 (switched the kanban DB to journal_mode=DELETE) — pragmatic, removes the failure class by construction, but trades away WAL's concurrent-reader benefits (acceptable for the small, low-frequency kanban DB in my case, but your approach is more general).

Thanks for the detailed write-up @valhir1. The inode-deletion wedge is an interesting failure mode -- I hadn't considered the 'last connection cleanup' unlinking -wal/-shm while gateway threads are between connect() calls.

Yes, please do apply the patch and run your repro against it. The PR branch is fix/30799-kanban-fd-leak-wal-checkpoint -- you can grab it with:

git fetch origin fix/30799-kanban-fd-leak-wal-checkpoint
git checkout fix/30799-kanban-fd-leak-wal-checkpoint

The key change is in tools/kanban_db.py: the _WalSafeConnection wrapper calls PRAGMA wal_checkpoint(TRUNCATE) before each close(). If it resolves your wedge, that's useful signal that the WAL consolidation theory holds across both failure modes. If not, your journal_mode=DELETE workaround is pragmatic and you should ship it -- WAL's concurrent-reader benefit is marginal for the low-frequency kanban DB.

Either way, posting your findings on #31158 would help the maintainers understand the full scope of the WAL lifecycle issue.