sqlite-wal-fortress-skilllisted

# SQLite WAL Fortress Skill Standard configuration and operational procedures for running SQLite on workshop servers and bench workstations, engineered to remain invulnerable to sudden electrical power loss, high concurrency lock contention, and non-blocking online hot-backups. --- ## 1. Risks of Default Rollback Journaling Under default journaling (`PRAGMA journal_mode = DELETE`): - Active write operations lock the entire database exclusively, blocking all concurrent readers. - Sudden electrical blackouts occurring mid-write risk leaving partial journal locks or corrupting database page headers. --- ## 2. Mandatory Circuit PRAGMAs for Power Resilience & Lock Contention Execute these pragmas immediately whenever a database connection opens: ```python import sqlite3 def get_db_connection(db_path: str) -> sqlite3.Connection: # Set generous connection timeout to handle lock contention gracefully conn = sqlite3.connect(db_path, timeout=30.0) conn.row_factory = sqlite3.Row # 1. Enable Write-Ahead Logging (readers never block writers) conn.execute("PRAGMA journal_mode = WAL;") # 2. Synchronous NORMAL (resilient to sudden power cutoffs & 10x faster) conn.execute("PRAGMA synchronous = NORMAL;") # 3. Busy Timeout (wait up to 5000ms before raising OperationalError: database is locked) conn.execute("PRAGMA busy_timeout = 5000;") # 4. Allocate in-memory page cache (-64000 = 64 MB RAM) conn.execute("PRAGMA cache_size