What does this PR do?

Switches periodic WAL checkpoint from TRUNCATE to PASSIVE mode to prevent B-tree corruption on large databases. Also adds error logging for checkpoint failures that were previously silently swallowed.

Related Issue

Fixes #45383

Type of Change

• 🐛 Bug fix (non-breaking change that fixes an issue)

Changes Made

• hermes_state.py: Changed _try_wal_checkpoint() from PRAGMA wal_checkpoint(TRUNCATE) to PRAGMA wal_checkpoint(PASSIVE) for periodic checkpoints. PASSIVE does not require an exclusive lock and cannot corrupt B-tree pages under I/O pressure. TRUNCATE is retained in close() and pre-VACUUM paths where it runs infrequently under controlled conditions. Replaced silent except Exception: pass with logger.warning() / logger.debug() for checkpoint failures.
• tests/test_wal_checkpoint_strategy.py: Added 6 tests verifying PASSIVE mode for periodic checkpoints, TRUNCATE for close(), error logging behavior, and checkpoint frequency.

How to Test

1. pytest tests/test_wal_checkpoint_strategy.py -v — all 6 new tests pass
2. pytest tests/test_hermes_state.py tests/hermes_state/ tests/test_hermes_state_wal_fallback.py tests/test_hermes_state_compression_locks.py -q — all 327 existing tests pass (no regression)
3. Verify the periodic checkpoint now uses PASSIVE: the test test_checkpoint_uses_passive_mode confirms PRAGMA wal_checkpoint(PASSIVE) is called and no TRUNCATE call is made
4. Verify close() still uses TRUNCATE: the test test_close_uses_truncate_mode confirms PRAGMA wal_checkpoint(TRUNCATE) is called at shutdown

Checklist

Code

• I've read the Contributing Guide
• My commit messages follow Conventional Commits (fix(scope):, feat(scope):, etc.)
• I searched for existing PRs to make sure this isn't a duplicate
• My PR contains only changes related to this fix/feature (no unrelated commits)
• I've run pytest tests/ -q and all tests pass
• I've added tests for my changes (required for bug fixes, strongly encouraged for features)
• I've tested on my platform: macOS

Documentation & Housekeeping

• I've updated relevant documentation (README, docs/, docstrings) — or N/A
• I've updated cli-config.yaml.example if I added/changed config keys — or N/A
• I've updated CONTRIBUTING.md or AGENTS.md if I changed architecture or workflows — or N/A
• I've considered cross-platform impact (Windows, macOS) per the compatibility guide — or N/A
• I've updated tool descriptions/schemas if I changed tool behavior — or N/A

Code Intelligence

• Analyzed: _try_wal_checkpoint (callers: 1 — _execute_write periodic path), close (callers: many — all shutdown paths), optimize_fts pre-VACUUM (callers: maybe_auto_prune_and_vacuum)
• Blast radius: LOW — changes checkpoint mode from TRUNCATE to PASSIVE in one code path; PASSIVE is strictly safer (no exclusive lock, no corruption risk). TRUNCATE retained in infrequent shutdown/maintenance paths.
• Related patterns: doctor.py already uses PASSIVE for its checkpoint. This PR aligns the periodic path with that precedent.