Session Storage

VibeOS uses a SQLite database (~/.vibeos/state.db) to persist session metadata, full message history, and model configuration across CLI and gateway sessions. This replaces the earlier per-session JSONL file approach.

Source file: vibeos_state.py

Architecture Overview

~/.vibeos/state.db (SQLite, WAL mode)
├── sessions              — Session metadata, token counts, billing
├── messages              — Full message history per session
├── messages_fts          — FTS5 virtual table (content + tool_name + tool_calls)
├── messages_fts_trigram  — FTS5 virtual table with trigram tokenizer (CJK / substring search)
├── state_meta            — Key/value metadata table
├── compression_locks      — per-session leases that serialize compression
└── schema_version        — Single-row table tracking migration state

Key design decisions:

• WAL mode for concurrent readers + one writer (gateway multi-platform)
• FTS5 virtual table for fast text search across all session messages
• Session lineage via parent_session_id chains for legacy continuations, /branch, and explicit context-handoff fallback
• Source tagging (cli, telegram, discord, etc.) for platform filtering
• Batch runner and RL trajectories are NOT stored here (separate systems)

SQLite Schema

Sessions Table

CREATE TABLE IF NOT EXISTS sessions (
    id TEXT PRIMARY KEY,
    source TEXT NOT NULL,
    user_id TEXT,
    model TEXT,
    model_config TEXT,
    system_prompt TEXT,
    parent_session_id TEXT,
    started_at REAL NOT NULL,
    ended_at REAL,
    end_reason TEXT,
    message_count INTEGER DEFAULT 0,
    tool_call_count INTEGER DEFAULT 0,
    input_tokens INTEGER DEFAULT 0,
    output_tokens INTEGER DEFAULT 0,
    cache_read_tokens INTEGER DEFAULT 0,
    cache_write_tokens INTEGER DEFAULT 0,
    reasoning_tokens INTEGER DEFAULT 0,
    billing_provider TEXT,
    billing_base_url TEXT,
    billing_mode TEXT,
    estimated_cost_usd REAL,
    actual_cost_usd REAL,
    cost_status TEXT,
    cost_source TEXT,
    pricing_version TEXT,
    title TEXT,
    api_call_count INTEGER DEFAULT 0,
    handoff_state TEXT,
    handoff_platform TEXT,
    handoff_error TEXT,
    rewind_count INTEGER NOT NULL DEFAULT 0,
    archived INTEGER NOT NULL DEFAULT 0,
    project_id TEXT,
    department_id TEXT,
    FOREIGN KEY (parent_session_id) REFERENCES sessions(id)
);

CREATE INDEX IF NOT EXISTS idx_sessions_source ON sessions(source);
CREATE INDEX IF NOT EXISTS idx_sessions_parent ON sessions(parent_session_id);
CREATE INDEX IF NOT EXISTS idx_sessions_started ON sessions(started_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_sessions_title_unique
    ON sessions(title) WHERE title IS NOT NULL;

Messages Table

CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id TEXT NOT NULL REFERENCES sessions(id),
    role TEXT NOT NULL,
    content TEXT,
    tool_call_id TEXT,
    tool_calls TEXT,
    tool_name TEXT,
    timestamp REAL NOT NULL,
    token_count INTEGER,
    finish_reason TEXT,
    reasoning TEXT,
    reasoning_content TEXT,
    reasoning_details TEXT,
    codex_reasoning_items TEXT,
    codex_message_items TEXT,
    platform_message_id TEXT,
    observed INTEGER DEFAULT 0,
    active INTEGER NOT NULL DEFAULT 1,
    compacted INTEGER NOT NULL DEFAULT 0,
    sync_id TEXT
);

CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id, timestamp);

Notes:

• tool_calls is stored as a JSON string (serialized list of tool call objects)
• reasoning_details, codex_reasoning_items, and codex_message_items are stored as JSON strings
• reasoning stores the raw reasoning text for providers that expose it
• sync_id is a stable UUID used for idempotent multi-device history sync; the integer id stays local because it is used by FTS rowids
• Timestamps are Unix epoch floats (time.time())

FTS5 Full-Text Search

CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(content);

The FTS5 table is kept in sync via three triggers that fire on INSERT, UPDATE, and DELETE of the messages table:

CREATE TRIGGER IF NOT EXISTS messages_fts_insert AFTER INSERT ON messages BEGIN
    INSERT INTO messages_fts(rowid, content) VALUES (
        new.id,
        COALESCE(new.content, '') || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
    );
END;

CREATE TRIGGER IF NOT EXISTS messages_fts_delete AFTER DELETE ON messages BEGIN
    DELETE FROM messages_fts WHERE rowid = old.id;
END;

CREATE TRIGGER IF NOT EXISTS messages_fts_update AFTER UPDATE ON messages BEGIN
    DELETE FROM messages_fts WHERE rowid = old.id;
    INSERT INTO messages_fts(rowid, content) VALUES (
        new.id,
        COALESCE(new.content, '') || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
    );
END;

Schema Version and Migrations

Current schema version: 22

The schema_version table stores a single integer. Simple column additions are handled declaratively by _reconcile_columns() (which diffs live columns against SCHEMA_SQL and ADDs any missing ones). The version-gated chain is reserved for data migrations and index/FTS changes that can't be expressed declaratively:

Version	Change
1	Initial schema (sessions, messages, FTS5)
2	Add finish_reason column to messages
3	Add title column to sessions
4	Add unique index on title (NULLs allowed, non-NULL must be unique)
5	Add billing columns: cache_read_tokens, cache_write_tokens, reasoning_tokens, billing_provider, billing_base_url, billing_mode, estimated_cost_usd, actual_cost_usd, cost_status, cost_source, pricing_version
6	Add reasoning columns to messages: reasoning, reasoning_details, codex_reasoning_items
7	Add reasoning_content column to messages
8	Add api_call_count column to sessions
9	Add codex_message_items column to messages for Codex Responses message id/phase replay
10	Add messages_fts_trigram virtual table (trigram tokenizer for CJK / substring search) and backfill existing rows
11	Re-index messages_fts and messages_fts_trigram to cover tool_name + tool_calls and switch from external-content to inline mode; drop old triggers and backfill every message row
12	Add messages.active for non-destructive rewind/undo
16	Backfill delegation markers used to keep ephemeral subagent sessions out of normal pickers
17	Add session project/department bindings and stable messages.sync_id for logical history sync
22	Keep trigram FTS only for CJK/Japanese/Korean text; base FTS remains complete for all messages

Declarative column adds use ALTER TABLE ADD COLUMN wrapped in try/except to handle the column-already-exists case (idempotent). The version number is bumped after each successful migration block.

Schema versions are monotonic. A profile whose schema_version is newer than the checkout is not downgraded, but its newer features are outside this checkout's compatibility contract. Run vibeos sessions doctor before opening such a profile for writes and use the VibeOS release that created the newer schema whenever possible.

Write Contention Handling

Multiple vibeos processes (gateway + CLI sessions + worktree agents) share one state.db. The SessionDB class handles write contention with:

• Short SQLite timeout (1 second) instead of the default 30s
• Application-level retry with random jitter (20-150ms, up to 15 retries)
• BEGIN IMMEDIATE transactions to surface lock contention at transaction start
• Best-effort TRUNCATE WAL checkpoints every 50 successful writes, and on clean close, to prevent the WAL from retaining its high-water size

This avoids the "convoy effect" where SQLite's deterministic internal backoff causes all competing writers to retry at the same intervals.

_WRITE_MAX_RETRIES = 15
_WRITE_RETRY_MIN_S = 0.020   # 20ms
_WRITE_RETRY_MAX_S = 0.150   # 150ms
_CHECKPOINT_EVERY_N_WRITES = 50

Common Operations

Initialize

from vibeos_state import SessionDB

db = SessionDB()                           # Default: ~/.vibeos/state.db
db = SessionDB(db_path=Path("/tmp/test.db"))  # Custom path

Create and Manage Sessions

# Create a new session
db.create_session(
    session_id="sess_abc123",
    source="cli",
    model="anthropic/claude-sonnet-4.6",
    user_id="user_1",
    parent_session_id=None,  # previous session ID only for branch/explicit continuation
)

# End a session
db.end_session("sess_abc123", end_reason="user_exit")

# Reopen a session (clear ended_at/end_reason)
db.reopen_session("sess_abc123")

Store Messages

msg_id = db.append_message(
    session_id="sess_abc123",
    role="assistant",
    content="Here's the answer...",
    tool_calls=[{"id": "call_1", "function": {"name": "terminal", "arguments": "{}"}}],
    token_count=150,
    finish_reason="stop",
    reasoning="Let me think about this...",
)

Retrieve Messages

# Raw messages with all metadata
messages = db.get_messages("sess_abc123")

# OpenAI conversation format (for API replay)
conversation = db.get_messages_as_conversation("sess_abc123")
# Returns: [{"role": "user", "content": "..."}, {"role": "assistant", ...}]

Session Titles

# Set a title (must be unique among non-NULL titles)
db.set_session_title("sess_abc123", "Fix Docker Build")

# Resolve by title (returns most recent in lineage)
session_id = db.resolve_session_by_title("Fix Docker Build")

# Auto-generate next title in lineage
next_title = db.get_next_title_in_lineage("Fix Docker Build")
# Returns: "Fix Docker Build #2"

Full-Text Search

The search_messages() method supports FTS5 query syntax with automatic sanitization of user input.

Basic Search

results = db.search_messages("docker deployment")

FTS5 Query Syntax

Syntax	Example	Meaning
Keywords	docker deployment	Both terms (implicit AND)
Quoted phrase	"exact phrase"	Exact phrase match
Boolean OR	docker OR kubernetes	Either term
Boolean NOT	python NOT java	Exclude term
Prefix	deploy*	Prefix match

Filtered Search

# Search only CLI sessions
results = db.search_messages("error", source_filter=["cli"])

# Exclude gateway sessions
results = db.search_messages("bug", exclude_sources=["telegram", "discord"])

# Search only user messages
results = db.search_messages("help", role_filter=["user"])

Search Results Format

Each result includes:

• id, session_id, role, timestamp
• snippet — FTS5-generated snippet with >>>match<<< markers
• context — 1 message before and after the match (content truncated to 200 chars)
• source, model, session_started — from the parent session

The _sanitize_fts5_query() method handles edge cases:

• Strips unmatched quotes and special characters
• Wraps hyphenated terms in quotes (chat-send → "chat-send")
• Removes dangling boolean operators (hello AND → hello)

Session Lineage

Sessions can form chains via parent_session_id. Current compression rewrites the active transcript in place, so normal /compress does not create a child row. Lineage is still used for historical compressed sessions, /branch, and explicit continuation fallback (/new --continue) when the user deliberately chooses to continue from a prepared context handoff.

Query: Find Session Lineage

-- Find all ancestors of a session
WITH RECURSIVE lineage AS (
    SELECT * FROM sessions WHERE id = ?
    UNION ALL
    SELECT s.* FROM sessions s
    JOIN lineage l ON s.id = l.parent_session_id
)
SELECT id, title, started_at, parent_session_id FROM lineage;

-- Find all descendants of a session
WITH RECURSIVE descendants AS (
    SELECT * FROM sessions WHERE id = ?
    UNION ALL
    SELECT s.* FROM sessions s
    JOIN descendants d ON s.parent_session_id = d.id
)
SELECT id, title, started_at FROM descendants;

Query: Recent Sessions with Preview

SELECT s.*,
    COALESCE(
        (SELECT SUBSTR(m.content, 1, 63)
         FROM messages m
         WHERE m.session_id = s.id AND m.role = 'user' AND m.content IS NOT NULL
         ORDER BY m.timestamp, m.id LIMIT 1),
        ''
    ) AS preview,
    COALESCE(
        (SELECT MAX(m2.timestamp) FROM messages m2 WHERE m2.session_id = s.id),
        s.started_at
    ) AS last_active
FROM sessions s
ORDER BY s.started_at DESC
LIMIT 20;

Query: Token Usage Statistics

-- Total tokens by model
SELECT model,
       COUNT(*) as session_count,
       SUM(input_tokens) as total_input,
       SUM(output_tokens) as total_output,
       SUM(estimated_cost_usd) as total_cost
FROM sessions
WHERE model IS NOT NULL
GROUP BY model
ORDER BY total_cost DESC;

-- Sessions with highest token usage
SELECT id, title, model, input_tokens + output_tokens AS total_tokens,
       estimated_cost_usd
FROM sessions
ORDER BY total_tokens DESC
LIMIT 10;

Export and Cleanup

# Export a single session with messages
data = db.export_session("sess_abc123")

# Export all sessions (with messages) as list of dicts
all_data = db.export_all(source="cli")

# Include rewind/soft-deleted messages when producing a complete archive
archive_data = db.export_all(source="cli", include_inactive=True)

# Delete old sessions (only ended sessions)
deleted_count = db.prune_sessions(older_than_days=90)
deleted_count = db.prune_sessions(older_than_days=30, source="telegram")

# Clear messages but keep the session record
db.clear_messages("sess_abc123")

# Delete session and all messages
db.delete_session("sess_abc123")

For an operational database snapshot, use the CLI rather than copying state.db while a writer may have an active WAL file:

# Creates a new file only; it never replaces an existing backup.
# The command prints a SHA-256 checksum and runs SQLite quick_check on the copy.
vibeos sessions backup /safe/backups/state-2026-08-03.db

# JSONL exports are active-message-only by default; add this for a full archive.
vibeos sessions export /safe/backups/sessions.jsonl --include-inactive

Use vibeos backup when the recovery point must include configuration, credentials, skills, scheduler state, and every profile store — sessions backup is intentionally limited to one session database.

vibeos backup --quick creates a smaller local recovery point. It includes state.db plus the durable project, work-queue, kanban, memory, experience, settings/localization, account, billing, and supported plugin queue stores; every SQLite entry is copied through the same WAL-safe snapshot path.

Database Location

Default path: ~/.vibeos/state.db

This is derived from vibeos_constants.get_vibeos_home() which resolves to ~/.vibeos/ by default, or the value of VIBEOS_HOME environment variable.

The database file, WAL file (state.db-wal), and shared-memory file (state.db-shm) are all created in the same directory.