SessionDB Storage & SQLite High-Concurrency Lock Self-Healing

💾 1. SQLite state.db Core Table Relationships & Foreign Key Design

Session state (such as Turn history messages, variable snapshots, and Kanban sub-task lifecycle) is uniformly persisted in SQLite (typically named state.db). The underlying data table topology is shown in the following ER diagram:

📊 Figure 4-2: SQLite state.db Core Data Table Foreign Key Relationship ER Diagram

Core Table Responsibilities:

• sessions table: Main table. Stores session global state (e.g., session_id PK, status, metadata JSON).
• messages table: Stores the conversation tree. Its session_id serves as an FK cascading to the main table. Each row stores the Turn role, content (BLOB), and unique sequence number.
• variables table: Stores environment variables and snapshots bound during the session, providing context self-healing for background tasks and retries.
• compression_locks table: High-concurrency conversation trajectory compression mutex table, ensuring that no two background threads compress the same session simultaneously.

☁️ 2. Network Filesystem WAL Compatibility Degradation (apply_wal_with_fallback)

If the user's project is deployed on NFS, SMB, WSL host virtual mount points, these environments often lack support for SQLite WAL mode's required shared memory mapping (.shm) and byte-range locks, which can throw:

sqlite3.OperationalError: locking protocol (or: not authorized)

# apply_wal_with_fallback implementation details
def apply_wal_with_fallback(conn: sqlite3.Connection, db_label: str) -> str:
    try:
        conn.execute("PRAGMA journal_mode=WAL")
        return "wal"
    except sqlite3.OperationalError as exc:
        msg = str(exc).lower()
        if any(marker in msg for marker in ("locking protocol", "not authorized")):
            # Automatically fall back to DELETE mode, disable shared memory mapping
            conn.execute("PRAGMA journal_mode=DELETE")
            _log_wal_fallback_once(db_label, exc)
            return "delete"
        raise

⏳ 3. Application-Layer Jitter Retry & Lock Convoy Avoidance

Regular SQLite retry mechanisms cause the "Convoy Pattern" — multiple blocked threads wake simultaneously to contend for the same lock, causing sustained database deadlock. Hermes uses application-layer random jitter in SessionDB to break the queue convoy:

1. BEGIN IMMEDIATE Explicit Preemption: Before executing any write SQL, the connection immediately declares BEGIN IMMEDIATE. This intercepts SQLite write lock conflicts at the start of the transaction, preventing mid-transaction rollbacks.
2. Application-Layer Jitter (Random Backoff): Once a locked error occurs, the code automatically releases the current Python thread lock and initiates a random sleep:

   # Jitter avoidance core logic
   _WRITE_RETRY_MIN_S = 0.020   # 20ms
   _WRITE_RETRY_MAX_S = 0.150   # 150ms
   
   # On lock conflict, release Python lock and sleep random time between 20ms-150ms to break convoy queuing
   jitter = random.uniform(_WRITE_RETRY_MIN_S, _WRITE_RETRY_MAX_S)
   time.sleep(jitter)

🔧 4. sqlite_master Schema Corruption In-Flight Direct Surgery

Under extreme circumstances, the sqlite_master metadata table can develop anomalous primary key conflicts, leaving ordinary developers helpless. Hermes encapsulates an in-flight repair surgery inside SessionDB:

• Exclusive Self-Heal Lock (_repair_attempt_lock): Ensures no other concurrent background thread can read/write the DB block during repair.
• Writable Schema PRAGMA Write:

  # PRAGMA force repair schema
  conn.execute("PRAGMA writable_schema=ON")
  # 1. Attempt Strategy 1: Execute dedup surgery, keep only the lowest rowid original definition
  conn.execute("""
      DELETE FROM sqlite_master 
      WHERE rowid NOT IN (
          SELECT MIN(rowid) FROM sqlite_master GROUP BY type, name
      )
  """)
  # 2. If corruption persists, execute Strategy 2: Directly force-clear FTS virtual table metadata
  conn.execute("DELETE FROM sqlite_master WHERE name LIKE 'messages_fts%'")
  conn.execute("PRAGMA writable_schema=OFF")

🔗 Sub-topic Deep Dive Articles

For master-level understanding of SessionDB, explore these sub-pages:

• SessionDB Concurrent Lock Conflict & Jitter Retry Mechanism (OperationalError capture & collision avoidance)
• SessionDB Foreign Key Design & Hot Schema Migration (table-level relationship design & online field upgrade)