opencode-db

name

opencode-db

description

Query and explore the opencode session database using `opencode db` CLI. Use when: (1) Searching session history by title, directory, or date, (2) Inspecting conversation messages and parts within sessions, (3) Analyzing project or workspace data, (4) Reviewing todo lists across sessions, (5) Any task requiring structured queries against opencode's persisted state — sessions, messages, parts, projects, workspaces, todos, events, permissions, accounts. Trigger keywords: "session db", "opencode db", "session history", "conversation data", "past sessions", "session search".

opencode-db

Query the opencode SQLite database via opencode db CLI.

CLI Reference

opencode db "SQL"              # Run a SQL query
opencode db "SQL" --format json  # JSON output (default is TSV)
opencode db                     # Interactive sqlite3 shell
opencode db path                # Print database file path
opencode db migrate             # Migrate JSON data to SQLite

Schema Overview

See references/schema.md for full table definitions. Key tables:

Table	Purpose	Key Columns
session	Conversation sessions	id, project_id, slug, directory, title, time_created, time_updated
message	Top-level messages in a session	id, session_id, data (JSON: role, modelID, providerID, mode, agent, summary)
part	Message parts (reasoning, tool calls, text)	id, message_id, session_id, data (JSON: type, text)
project	Projects (worktree groupings)	id, worktree, name, time_created
workspace	Workspaces within projects	id, name, branch, directory, type, project_id
todo	Todo items per session	session_id, content, status, priority, position
permission	Project permissions	project_id, data (JSON)
session_share	Shared session URLs	session_id, id, url, secret
event / event_sequence	Event sourcing log	aggregate_id, seq, type, data
account / account_state	User accounts	id, email, url, access_token
session_entry	Session entries (may be empty)	id, session_id, type, data

Common Queries

Find sessions

-- Recent sessions
SELECT id, title, slug, directory, datetime(time_updated/1000, 'unixepoch') AS updated
FROM session ORDER BY time_updated DESC LIMIT 20;

-- Search by title
SELECT id, title, directory FROM session WHERE title LIKE '%keyword%';

-- Sessions in a specific directory
SELECT id, title, slug FROM session WHERE directory = '/path/to/project';

-- Session with its messages
SELECT m.id, json_extract(m.data, '$.role') AS role,
       json_extract(m.data, '$.modelID') AS model,
       datetime(json_extract(m.data, '$.time.created')/1000, 'unixepoch') AS created
FROM message m WHERE m.session_id = 'SESSION_ID' ORDER BY m.time_created;

Inspect message parts

-- Parts of a message (reasoning, tool calls, text output)
SELECT p.id, json_extract(p.data, '$.type') AS part_type,
       substr(json_extract(p.data, '$.text'), 1, 200) AS preview
FROM part p WHERE p.message_id = 'MESSAGE_ID' ORDER BY p.time_created;

Todos

-- Todos for a session
SELECT content, status, priority, position FROM todo
WHERE session_id = 'SESSION_ID' ORDER BY position;

-- All incomplete todos across sessions
SELECT s.title, t.content, t.status, t.priority
FROM todo t JOIN session s ON t.session_id = s.id
WHERE t.status != 'completed' ORDER BY t.priority;

Projects & workspaces

-- All projects
SELECT id, name, worktree FROM project;

-- Workspaces for a project
SELECT w.id, w.name, w.branch, w.directory, w.type
FROM workspace w WHERE w.project_id = 'PROJECT_ID';

Export Transcripts

The scripts/export-transcripts.py script exports full session transcripts as formatted text files.

# Export specific sessions by ID
uv run scripts/export-transcripts.py ses_abc123 ses_def456

# Search by title keyword and export all matches
uv run scripts/export-transcripts.py --search-title "CRSF"

# Search by exact directory
uv run scripts/export-transcripts.py --search-dir "/home/user/project"

# Search by directory substring
uv run scripts/export-transcripts.py --search-dir-like "decktx"

# Pipe IDs from a SQL query
opencode db "SELECT id FROM session WHERE title LIKE '%CRSF%'" --format json \
  | jq -r '.[].id' \
  | uv run scripts/export-transcripts.py --from-stdin

# Custom output directory
uv run scripts/export-transcripts.py --output ./my_transcripts ses_abc123

Each transcript includes:

• Session header (title, slug, ID, directory, timestamps)
• All messages with timestamps, roles, model info
• Reasoning blocks (truncated at 500 chars)
• Tool calls with formatted JSON input
• Tool results (truncated at 500 chars)
• Full text responses

Notes

• data columns are JSON — use json_extract() to query fields.
• Timestamps are millisecond Unix epochs — divide by 1000 for datetime().
• Use --format json for structured output; default is TSV.
• Always quote SQL in the shell to avoid glob/pipe interpretation.