| name | opencode-introspect |
| description | Read and search opencode session logs from the SQLite DB at ~/.local/share/opencode/opencode.db. Use when you need to review what a session did, assess whether AI work was safe/correct, diagnose a "session wipe", find a specific tool call or output from a past session, or recover the DB after a migration reset.
|
OpenCode Session Introspection
Architecture (read this first)
OpenCode stores data in two parallel stores:
| Store | Path | Role |
|---|
| SQLite DB | ~/.local/share/opencode/opencode.db | Query cache — can be wiped and rebuilt |
| Flat JSON files | ~/.local/share/opencode/storage/ | Ground truth — never wiped by opencode |
The DB schema has these key tables: project, session, message, part.
session.directory — working directory the session ran inmessage.session_id → FK to sessionpart.message_id → FK to message; part.data is a JSON blob (not columns)
part.data JSON structure
type: "text" → d['text'] (assistant/user prose)
type: "tool" → d['tool'], d['state']['input'], d['state']['output']
type: "step-start" → d['snapshot'] (context snapshot hash)
type: "step-finish"→ d['cost'], d['tokens'] (billing info)
type: "patch" → file diff applied by agent
For bash tool calls: d['state']['input']['command'], d['state']['output']
For read/write/edit: d['state']['input']['path'] or d['state']['input']['filePath']
NEVER use json_extract(p.data, '$.input') at the SQLite level — it's nested under state. Use Python to parse.
1. List recent sessions
import sqlite3, json
db = sqlite3.connect('/Users/tdyar/.local/share/opencode/opencode.db')
rows = db.execute("""
SELECT s.id, s.title, s.directory,
s.parent_id,
datetime(s.time_created/1000,'unixepoch','localtime') as created,
datetime(s.time_updated/1000,'unixepoch','localtime') as updated
FROM session s
ORDER BY s.time_updated DESC
LIMIT 20
""").fetchall()
for r in rows:
parent = f" [child of {r[3][:20]}]" if r[3] else ""
print(f"{r[4]} – {r[5]}")
print(f" {r[0][:30]}{parent}")
print(f" {r[1][:80]}")
print(f" dir: {r[2]}")
print()
2. List sessions by project directory
rows = db.execute("""
SELECT s.id, s.title, s.parent_id,
datetime(s.time_created/1000,'unixepoch','localtime') as created,
datetime(s.time_updated/1000,'unixepoch','localtime') as updated
FROM session s
WHERE s.directory LIKE '%/arno%' -- change to your project
ORDER BY s.time_updated DESC
LIMIT 20
""").fetchall()
3. Read a session's full activity log
import sqlite3, json
def read_session(session_id):
db = sqlite3.connect('/Users/tdyar/.local/share/opencode/opencode.db')
rows = db.execute("""
SELECT p.data
FROM part p JOIN message m ON p.message_id = m.id
WHERE m.session_id = ?
ORDER BY p.rowid
""", (session_id,)).fetchall()
for (data_str,) in rows:
try:
d = json.loads(data_str)
except:
continue
t = d.get('type', '')
if t == 'text' and d.get('text', '').strip():
print(f"[TEXT] {d['text'][:300]}")
elif t == 'tool':
tool = d.get('tool', '')
state = d.get('state', {})
inp = state.get('input', {})
cmd = inp.get('command','') or inp.get('path','') or inp.get('filePath','') or str(inp)[:80] if isinstance(inp, dict) else str(inp)[:80]
out = (state.get('output', '') or '')[:150]
print(f"[{tool}] {cmd[:150]}")
if out:
print(f" → {out}")
elif t == 'step-finish':
cost = d.get('cost')
if cost:
print(f"[cost ${cost:.4f}]")
4. Search across sessions for a keyword
import sqlite3, json, re
def search_sessions(keyword, project_dir=None):
db = sqlite3.connect('/Users/tdyar/.local/share/opencode/opencode.db')
where = "WHERE s.directory LIKE ?" if project_dir else "WHERE 1=1"
params = (f'%{project_dir}%',) if project_dir else ()
rows = db.execute(f"""
SELECT p.data, s.id, s.title, s.directory
FROM part p
JOIN message m ON p.message_id = m.id
JOIN session s ON m.session_id = s.id
{where}
ORDER BY p.rowid
""", params).fetchall()
for (data_str, sess_id, title, directory) in rows:
try:
d = json.loads(data_str)
except:
continue
txt = ''
if d.get('type') == 'text':
txt = d.get('text', '')
elif d.get('type') == 'tool':
state = d.get('state', {})
inp = state.get('input', {})
txt = inp.get('command','') if isinstance(inp, dict) else str(inp)
txt += '\n' + (state.get('output','') or '')
if re.search(keyword, txt, re.IGNORECASE):
print(f"\n[{sess_id[:25]}] {title[:60]}")
print(f" dir: {directory}")
print(f" match: {txt[:300]}")
5. Count sessions and check DB health
sqlite3 ~/.local/share/opencode/opencode.db \
"SELECT COUNT(*) as sessions FROM session;
SELECT COUNT(*) as messages FROM message;
SELECT COUNT(*) as parts FROM part;"
If any count is 0 → DB was wiped. Sessions are still on disk (see recovery below).
DB Wipe Recovery
The DB is a cache — flat files are the ground truth.
When does a wipe happen?
Running opencode debug config (or any command that spawns a child opencode process) against a DB from an older schema version triggers Drizzle migration, which drops and recreates tables. The flat files at ~/.local/share/opencode/storage/ are never touched.
Recovery procedure
import json, sqlite3, os, glob
db_path = os.path.expanduser("~/.local/share/opencode/opencode.db")
conn = sqlite3.connect(db_path)
storage_project = os.path.expanduser("~/.local/share/opencode/storage/project")
inserted_projects = 0
for f in glob.glob(f"{storage_project}/*.json"):
try:
d = json.load(open(f))
conn.execute("""
INSERT OR IGNORE INTO project (id, worktree, vcs, name, time_created, time_updated, sandboxes)
VALUES (?, ?, ?, ?, ?, ?, ?)
""", (d['id'], d.get('worktree',''), d.get('vcs'), d.get('name'),
d.get('time',{}).get('created',0), d.get('time',{}).get('updated',0),
json.dumps(d.get('sandboxes',[]))))
inserted_projects += 1
except Exception as e:
pass
conn.commit()
print(f"Projects: {inserted_projects}")
storage_session = os.path.expanduser("~/.local/share/opencode/storage/session")
inserted_sessions = skipped = 0
for project_hash in os.listdir(storage_session):
project_dir = os.path.join(storage_session, project_hash)
if not os.path.isdir(project_dir):
continue
for f in glob.glob(f"{project_dir}/*.json"):
try:
d = json.load(open(f))
conn.execute("""
INSERT OR IGNORE INTO session
(id, project_id, parent_id, slug, directory, title, version, time_created, time_updated, permission)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (d['id'], project_hash, d.get('parentID'), d.get('slug',''),
d.get('path', d.get('directory','')), d.get('title',''),
d.get('version',''), d.get('time',{}).get('created',0),
d.get('time',{}).get('updated',0), d.get('permission')))
inserted_sessions += 1
except Exception as e:
skipped += 1
conn.commit()
print(f"Sessions: {inserted_sessions} inserted, {skipped} skipped")
storage_message = os.path.expanduser("~/.local/share/opencode/storage/message")
inserted_msgs = 0
for session_dir in os.listdir(storage_message):
msg_dir = os.path.join(storage_message, session_dir)
if not os.path.isdir(msg_dir):
continue
for f in glob.glob(f"{msg_dir}/*.json"):
try:
d = json.load(open(f))
conn.execute("""
INSERT OR IGNORE INTO message (id, session_id, time_created, time_updated, data)
VALUES (?, ?, ?, ?, ?)
""", (d['id'], d.get('sessionID', session_dir),
d.get('time',{}).get('created',0), d.get('time',{}).get('updated',0),
json.dumps(d)))
inserted_msgs += 1
if inserted_msgs % 50000 == 0:
conn.commit()
print(f" {inserted_msgs} messages...")
except Exception:
pass
conn.commit()
print(f"Messages: {inserted_msgs}")
After recovery: reopen opencode normally — it will use the restored DB.
Key facts
~/.local/share/opencode/storage/session/<project-hash>/ — one JSON file per session- Project hash = SHA1 of the project's git worktree path (deterministic)
- The DB's
part table is populated from storage/message/ JSON files
opencode debug config is SAFE to run — it prints config, does NOT wipe sessions
- The wipe was caused by a schema migration triggered by running a child opencode process against a DB with an older schema version
- The WAL files (
opencode.db-wal, opencode.db-shm) are part of the active DB transaction journal — back up all three together
