OpenCode Session Toolkit
Read the local OpenCode SQLite database and query or export sessions, messages, parts, and projects across directories.
All commands below assume the workdir is this skill directory. For Markdown export, run the bundled script directly:
./scripts/export_opencode_sessions.py --help
When to use
- List recent sessions, filter by directory, or search by title
- Read message JSON for a specific session
- Export matched sessions into one-Markdown-per-session archives
- Inspect database schema and indexes (load
references/schema.md only when needed)
Workflow
- Resolve the database path with
opencode db path.
- Run all queries in read-only mode.
- Load
references/schema.md only when field-level details are required.
1. Resolve the database path
if ! command -v opencode >/dev/null 2>&1; then
echo "opencode command not found in PATH" >&2
exit 1
fi
if ! DB_PATH="$(opencode db path 2>/dev/null)"; then
echo "Failed to resolve OpenCode DB path via: opencode db path" >&2
exit 1
fi
if [ -z "${DB_PATH:-}" ] || [ ! -f "$DB_PATH" ]; then
echo "OpenCode DB not found: $DB_PATH" >&2
exit 1
fi
echo "Using DB: $DB_PATH"
List existing DB files (no error when there is no match):
find "${XDG_DATA_HOME:-$HOME/.local/share}/opencode" -maxdepth 1 -name '*.db' -print 2>/dev/null
2. Time conversion and output formatting
Time conversion: all time fields are Unix timestamps in milliseconds. Convert them directly in SQL with datetime().
# Convert in SQL (recommended, no external command needed)
datetime(time_updated/1000, 'unixepoch', 'localtime')
# Shell helpers for time windows
NOW_MS=$(date +%s000)
LAST_7D=$((NOW_MS - 7*86400*1000))
LAST_30D=$((NOW_MS - 30*86400*1000))
Table alignment: for normal fields, pipe SQLite output to column -t -s '|' (| is SQLite's default delimiter). For long JSON fields such as message.data, prefer -json output.
sqlite3 -readonly "$DB_PATH" "SELECT id, title, time_updated FROM session LIMIT 5;" | column -t -s '|'
3. Common read-only queries
Tip: For queries without large JSON fields, append | column -t -s '|' for aligned table output.
List the latest 20 sessions (most recently updated first)
sqlite3 -readonly "$DB_PATH" \
"SELECT id, title, directory,
datetime(time_updated/1000,'unixepoch','localtime') as updated
FROM session
ORDER BY time_updated DESC
LIMIT 20;" | column -t -s '|'
Filter sessions by directory
sqlite3 -readonly "$DB_PATH" \
"SELECT id, title, datetime(time_updated/1000,'unixepoch','localtime') as updated
FROM session
WHERE directory LIKE '/path/to/project%'
ORDER BY time_updated DESC
LIMIT 20;" | column -t -s '|'
Filter sessions by project_id (most precise project linkage)
sqlite3 -readonly "$DB_PATH" \
"SELECT s.id, s.title, s.directory,
datetime(s.time_updated/1000,'unixepoch','localtime') as updated
FROM session s
WHERE s.project_id = 'your-project-id'
ORDER BY s.time_updated DESC
LIMIT 20;" | column -t -s '|'
project_id maps to project.id. List projects with:
sqlite3 -readonly "$DB_PATH" "SELECT id, worktree, name FROM project;" | column -t -s '|'
List sessions across all directories (with project info)
sqlite3 -readonly "$DB_PATH" \
"SELECT s.id, s.title, s.directory, p.worktree,
datetime(s.time_updated/1000,'unixepoch','localtime') as updated
FROM session s
LEFT JOIN project p ON s.project_id = p.id
ORDER BY s.time_updated DESC
LIMIT 50;" | column -t -s '|'
Filter by time range
# Sessions active in the last 7 days
sqlite3 -readonly "$DB_PATH" \
"SELECT id, title, datetime(time_updated/1000,'unixepoch','localtime') as updated
FROM session
WHERE time_updated > $(( $(date +%s000) - 7*86400*1000 ))
ORDER BY time_updated DESC
LIMIT 20;" | column -t -s '|'
# Sessions created today (local time)
sqlite3 -readonly "$DB_PATH" \
"SELECT id, title, datetime(time_created/1000,'unixepoch','localtime') as created
FROM session
WHERE date(time_created/1000,'unixepoch','localtime') = date('now','localtime')
ORDER BY time_created DESC
LIMIT 20;" | column -t -s '|'
Read message content for one session
sqlite3 -readonly -json "$DB_PATH" \
"SELECT m.id, datetime(m.time_created/1000,'unixepoch','localtime') as created, m.data
FROM message m
WHERE m.session_id = 'your-session-id'
ORDER BY m.time_created ASC;"
Extract fields from message.data JSON
# Extract key fields such as role and modelID
sqlite3 -readonly "$DB_PATH" \
"SELECT id,
json_extract(data, '$.role') as role,
json_extract(data, '$.modelID') as model,
datetime(time_created/1000,'unixepoch','localtime') as created
FROM message
WHERE session_id = 'your-session-id'
ORDER BY time_created ASC;" | column -t -s '|'
# Search message payload text with LIKE
sqlite3 -readonly "$DB_PATH" \
"SELECT id, json_extract(data, '$.role') as role, time_created
FROM message
WHERE data LIKE '%keyword%'
ORDER BY time_created DESC
LIMIT 20;" | column -t -s '|'
Search session titles
sqlite3 -readonly "$DB_PATH" \
"SELECT id, title, directory, datetime(time_updated/1000,'unixepoch','localtime') as updated
FROM session
WHERE title LIKE '%keyword%'
ORDER BY time_updated DESC
LIMIT 20;" | column -t -s '|'
View session summary stats
sqlite3 -readonly "$DB_PATH" \
"SELECT title, summary_additions, summary_deletions, summary_files,
datetime(time_created/1000,'unixepoch','localtime') as created
FROM session
ORDER BY time_updated DESC
LIMIT 20;" | column -t -s '|'
4. Export sessions to Markdown
The export script writes one session per Markdown file. By default:
- filename =
session title + created time
- time filtering uses
time_updated unless --time-field created is passed
step-start / step-finish parts are skipped to reduce noise- when
project.name is empty, project folder names fall back to the worktree basename, or global
Export sessions for one project
./scripts/export_opencode_sessions.py \
--project opencode-session-toolkit \
--output-dir ./exports/opencode-session-toolkit
--project matches by substring against project_id, project.name, project.worktree, and session.directory.
Export sessions in a time range
./scripts/export_opencode_sessions.py \
--start 2026-03-01 \
--end 2026-03-24T23:59:59 \
--time-field updated \
--output-dir ./exports/march
Accepted time formats:
- ISO date:
2026-03-24
- ISO datetime:
2026-03-24T22:35:37
- Unix seconds / milliseconds
Full export grouped by project
./scripts/export_opencode_sessions.py \
--all \
--group-by-project \
--output-dir ./exports/all
Output example:
exports/all/
OrchAI/
Migration work planning with subagent discussion_2026-03-23_23-48-07.md
global/
opencode-session-toolkit 命令验证与优化_2026-03-24_22-35-37.md
Useful extra filters
--session-id ses_xxx: exact session export--title-contains keyword: match session titles--directory-contains keyword: match session directories--archived include|exclude|only: filter archived sessions--filename-time-field created|updated: choose which session time goes into the filename--include-part-type text --include-part-type tool: export only certain part types--exclude-part-type reasoning: drop noisy part types--overwrite: overwrite existing files instead of appending the session id to avoid collisions
If no filters are provided, the script requires --all to avoid accidental full-database exports.
5. Inspect schema
sqlite3 -readonly "$DB_PATH" ".schema session"
sqlite3 -readonly "$DB_PATH" ".schema message"
sqlite3 -readonly "$DB_PATH" ".schema part"
sqlite3 -readonly "$DB_PATH" ".schema project"
For complete field and index notes, see references/schema.md.
6. List all tables
sqlite3 -readonly "$DB_PATH" ".tables"
7. Example output
id title directory updated
---------- ----------------------- -------------------------- -------------------
ses_abc123 My Session - 2026-03-24 /home/user/project 2026-03-24 10:00:00
ses_def456 Another Session /home/user/other 2026-03-23 15:30:00
(Aligned with | column -t -s '|'.)
8. Notes
- OpenCode uses SQLite WAL mode, so
.db-wal and .db-shm files are expected.
- Time fields are Unix timestamps in milliseconds. Convert with
datetime(ts/1000,'unixepoch','localtime').
data fields are JSON. Use json_extract(data, '$.field') for structured extraction, and prefer sqlite3 -json for raw message inspection.- Session isolation is anchored by
project_id; for cross-directory queries, joining project.worktree is recommended.
- Direct writes can corrupt data. Back up before any non-read-only operation.
account and control_account tables may contain sensitive credentials. Redact outputs when sharing.
