Enable SQLite WAL journal mode

[careck]

Security Review Finding — HIGH Priority

Source: Krillnotes Security Review v1.0.1 (April 2026)
Location: krillnotes-core/src/core/storage.rs

Description

Default SQLite journal mode (DELETE) is currently used. Write-heavy workloads (sync, bulk import) may have worse performance and higher corruption risk on crash compared to WAL mode.

Impact

• Higher risk of database corruption on unexpected shutdown during write-heavy operations
• Reduced write performance during sync and bulk import operations
• DELETE mode holds exclusive locks during writes, blocking concurrent reads

Recommendation

Add PRAGMA journal_mode = WAL after opening connections in storage.rs. WAL mode is the standard for production SQLite usage and provides:

• Better concurrent read/write performance
• Reduced corruption risk on crash
• Improved performance for write-heavy workloads

WAL mode is persistent per-database — it only needs to be set once, but setting it on every open is harmless and ensures consistency.

Acceptance Criteria

• PRAGMA journal_mode = WAL added after connection open in storage.rs
• Verified WAL mode works correctly with SQLCipher encryption
• Tested with sync and bulk import workloads