SessionDB & Schema

SessionDB Foreign Key Relationships & Runtime Hot Migration

Decrypting SessionDB table constraints and try-except dynamic ALTER TABLE auto-upgrade details
Database Schema
📊 Figure 4-2-1: SessionDB core data tables, foreign key constraints, and index distribution ER diagram

📂 1. SessionDB Core Table Foreign Key Relationships

The state.db database uses a high-cohesion three-table star schema design for fast queries:

  • sessions: Main session table, storing session state, active model configuration, and system_prompt prefix cache.
  • messages: Message history table, storing role, content, and reasoning_content for each turn, with a foreign key linked to the main session.
  • checkpoints: State snapshot table, storing variable rollback snapshots from each tool call step of the agent.
Write Lock Contention
📊 Figure 4-2-2: Multi-agent concurrent write contention and hot migration lock mechanism diagram

🛠️ 2. Runtime ALTER TABLE Dynamic Online Hot Migration

During version upgrades, if new code adds a column (e.g., finish_reason), old databases would throw an undefined column exception when reading/writing. The system in SessionDB catches this at runtime:

# Online hot-patch triggered by catching unknown column exception
try:
    conn.execute("ALTER TABLE messages ADD COLUMN finish_reason TEXT;")
except sqlite3.OperationalError:
    pass # Column already exists, silently ignore