Overview

Add opencode support to toki by leveraging SQLite's WAL (Write-Ahead Logging) hook mechanism for real-time change detection and efficient data extraction.

Background

opencode is an AI coding agent that stores session data in SQLite databases (WAL mode). Unlike JSONL-based systems (e.g., Claude Code), opencode uses SQLite for persistent storage, which requires a different approach for change tracking.

Key Differences from JSONL-based Systems

Technical Approach

1. SQLite WAL Hook (sqlite3_wal_hook)

Why WAL Hook?

• FSEvents detects file system changes but not actual database changes
• sqlite3_update_hook requires the same DB connection (not suitable for cross-process monitoring)
• sqlite3_wal_hook works on independent connections and fires when data is committed to WAL

How it works:

void* sqlite3_wal_hook(
  sqlite3*,
  int(*)(void*, sqlite3*, const char*, int),
  void*
);

Advantages over FSEvents:

• Detects actual data commits (not just file changes)
• Fires at commit time (real-time)
• Works on independent DB connections

2. Sequence-based Change Extraction

opencode's database uses seq fields in tables like session_messages and events. This allows efficient extraction of only changed data:

-- Extract new messages after last known seq
SELECT * FROM session_messages 
WHERE seq > :last_known_seq 
ORDER BY seq;

-- Extract new events after last known seq
SELECT * FROM events 
WHERE seq > :last_known_seq 
ORDER BY seq;

3. Architecture

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│   opencode  │────▶│ SQLite DB   │◀────│    toki     │
│ (writer)    │     │ (WAL mode)  │     │ (reader)    │
└─────────────┘     └─────────────┘     └─────────────┘
                           │
                    ┌──────┴──────┐
                    │  WAL Hook   │
                    │ (callback)  │
                    └──────┬──────┘
                           │
                    ┌──────▼──────┐
                    │ Change      │
                    │ Detection   │
                    └──────┬──────┘
                           │
                    ┌──────▼──────┐
                    │ Seq-based   │
                    │ Extraction  │
                    └─────────────┘

4. Implementation Details

Step 1: Open Independent DB Connection

toki should open its own SQLite connection to opencode's database:

// Example in Rust (using rusqlite)
let conn = sqlite::open(db_path)?;

Step 2: Set Up WAL Hook

// Register WAL hook callback
unsafe {
    sqlite3_wal_hook(
        conn.as_ptr(),
        Some(wal_hook_callback),
        std::ptr::null_mut(),
    );
}

Step 3: WAL Hook Callback

The callback is called when data is committed to WAL:

extern "C" fn wal_hook_callback(
    _ctx: *mut std::ffi::c_void,
    _db: *mut sqlite3,
    _db_name: *const c_char,
    _num_pages: i32,
) -> i32 {
    // Notify toki to extract new data
    0 // SQLITE_OK
}

Step 4: Extract Changed Data

When WAL hook fires, extract only new data using seq-based queries:

-- Get the last known seq from toki's local state
SELECT MAX(seq) FROM session_messages WHERE session_id = :session_id;

-- Extract new messages
SELECT * FROM session_messages 
WHERE session_id = :session_id 
AND seq > :last_known_seq 
ORDER BY seq;

5. Database Schema

opencode's database structure (from source code analysis):

SessionTable:

• id (Session ID)
• project_id, directory, title
• agent, model
• cost, tokens (usage tracking)
• time (created/updated/archived)

SessionMessageTable:

• id (Message ID)
• session_id (Foreign key)
• seq (Sequence number)
• type, data (JSON format)
• Timestamps

EventTable:

• id (Event ID)
• type (Event type)
• seq (Sequence number)
• data (JSON format)
• aggregateID (Session ID)
• Timestamps

6. Usage Tracking

opencode tracks usage in the session table:

-- Token usage fields
tokens.input      -- Input tokens
tokens.output     -- Output tokens
tokens.cache.read -- Cache read tokens
tokens.cache.write -- Cache write tokens
cost              -- Cost in USD

ccusage reads this data directly from the SQLite database using these fields.

Implementation Plan

Phase 1: Core Support

1. Add opencode provider to toki
2. Implement SQLite WAL hook integration
3. Add seq-based change detection
4. Implement basic session/message extraction

Phase 2: Advanced Features

1. Real-time event streaming
2. Multi-session support
3. Cost/token tracking
4. Integration with existing toki query system

Phase 3: Optimization

1. Batch extraction for cold start
2. Incremental updates
3. Error handling and recovery
4. Performance optimization

Benefits

1. Real-time monitoring: WAL hook provides immediate notification of changes
2. Efficient extraction: Seq-based queries extract only new data
3. No polling needed: WAL hook eliminates the need for periodic polling
4. Concurrent access: SQLite's WAL mode allows simultaneous reads and writes
5. Reliable tracking: Database-level tracking is more reliable than file system monitoring

References

• SQLite WAL Hook Documentation
• SQLite Locking and Concurrency
• opencode Session Schema
• ccusage opencode adapter

Related Issues

• toki currently supports Claude Code and Codex
• opencode uses SQLite instead of JSONL files
• Need to implement SQLite-specific change detection
• Need to handle WAL mode concurrency correctly