Skip to content

Instantly share code, notes, and snippets.

@etherops
Created March 25, 2026 19:28
Show Gist options
  • Select an option

  • Save etherops/df78300a0ce6eeb08e056533bdfa30d9 to your computer and use it in GitHub Desktop.

Select an option

Save etherops/df78300a0ce6eeb08e056533bdfa30d9 to your computer and use it in GitHub Desktop.
deep recovery opencode sqllite db
import sqlite3
import json
import shutil
SRC_PATH = "/Users/patrick.kennel/.local/share/opencode/opencode.db.notactuallyfixed"
OUT_PATH = "/Users/patrick.kennel/.local/share/opencode/opencode.db.recovered"
# Copy the notactuallyfixed db as the base (has sessions, parts intact)
shutil.copy2(SRC_PATH, OUT_PATH)
# Remove any stale WAL/SHM for the copy
import os
for ext in ["-shm", "-wal"]:
p = OUT_PATH + ext
if os.path.exists(p):
os.remove(p)
db = sqlite3.connect(OUT_PATH)
db.row_factory = sqlite3.Row
# Load all sessions keyed by id
sessions = {}
for row in db.execute("SELECT * FROM session"):
sessions[row['id']] = dict(row)
# Get all distinct messages from parts
msg_rows = db.execute("""
SELECT
message_id,
session_id,
MIN(time_created) as time_created,
MAX(time_updated) as time_updated
FROM part
GROUP BY message_id
ORDER BY MIN(time_created)
""").fetchall()
print(f"Distinct messages from parts: {len(msg_rows)}")
messages = []
skipped = 0
for row in msg_rows:
mid = row['message_id']
sid = row['session_id']
tc = row['time_created']
tu = row['time_updated']
sess = sessions.get(sid, {})
directory = sess.get('directory', '/')
# Determine if assistant: has a step-start part
has_step_start = db.execute(
"SELECT COUNT(*) FROM part WHERE message_id=? AND data LIKE '%\"type\":\"step-start\"%'",
(mid,)
).fetchone()[0]
is_assistant = has_step_start > 0
if is_assistant:
# Extract modelID/providerID from first tool part that has metadata.model
model_id = "claude-sonnet-4-6"
provider_id = "anthropic"
tool_parts = db.execute(
"SELECT data FROM part WHERE message_id=? AND data LIKE '%\"type\":\"tool\"%' AND data LIKE '%\"modelID\"%' LIMIT 1",
(mid,)
).fetchall()
if tool_parts:
try:
td = json.loads(tool_parts[0]['data'])
meta = td.get('state', {}).get('metadata') or td.get('metadata', {})
if meta and 'model' in meta:
model_id = meta['model'].get('modelID', model_id)
provider_id = meta['model'].get('providerID', provider_id)
except Exception:
pass
# Determine mode/agent from session permission field
# Subagent sessions restrict todowrite/task/todoread - they are sub-agents
permission = sess.get('permission') or '[]'
try:
perms = json.loads(permission) if isinstance(permission, str) else permission
except Exception:
perms = []
is_subagent = any(
p.get('permission') in ('todowrite', 'todoread', 'task')
for p in (perms if isinstance(perms, list) else [])
)
# Determine agent type from session slug/title or parent_id
parent_id = sess.get('parent_id')
if is_subagent:
# Try to infer agent type from session title
title = (sess.get('title') or '').lower()
if 'explore' in title:
agent = 'explore'
mode = 'explore'
elif 'research' in title:
agent = 'research'
mode = 'research'
else:
agent = 'build'
mode = 'build'
else:
agent = 'build'
mode = 'build'
# Aggregate tokens/cost from step-finish parts
finish = 'end-turn'
cost = 0.0
tokens = {"total": 0, "input": 0, "output": 0, "reasoning": 0, "cache": {"read": 0, "write": 0}}
finish_parts = db.execute(
"SELECT data FROM part WHERE message_id=? AND data LIKE '%\"type\":\"step-finish\"%'",
(mid,)
).fetchall()
for fp in finish_parts:
try:
fd = json.loads(fp['data'])
finish = fd.get('reason', finish)
cost += fd.get('cost', 0) or 0
ft = fd.get('tokens', {}) or {}
tokens['total'] += ft.get('total', 0) or 0
tokens['input'] += ft.get('input', 0) or 0
tokens['output'] += ft.get('output', 0) or 0
tokens['reasoning'] += ft.get('reasoning', 0) or 0
tokens['cache']['read'] += (ft.get('cache') or {}).get('read', 0) or 0
tokens['cache']['write']+= (ft.get('cache') or {}).get('write', 0) or 0
except Exception:
pass
data = json.dumps({
"role": "assistant",
"time": {"created": tc, "completed": tu},
"modelID": model_id,
"providerID": provider_id,
"mode": mode,
"agent": agent,
"path": {"cwd": directory, "root": "/"},
"cost": cost,
"tokens": tokens,
"finish": finish
})
else:
data = json.dumps({
"role": "user",
"time": {"created": tc},
"summary": {"diffs": []},
"agent": "build",
"model": {"providerID": "anthropic", "modelID": "claude-sonnet-4-6"}
})
messages.append((mid, sid, tc, tu, data))
print(f"Messages built: {len(messages)}")
# Wipe existing (wrong) messages and insert correct ones
db.execute("DELETE FROM message")
db.executemany(
"INSERT INTO message (id, session_id, time_created, time_updated, data) VALUES (?,?,?,?,?)",
messages
)
db.commit()
# Verify
count = db.execute("SELECT COUNT(*) FROM message").fetchone()[0]
print(f"Messages in recovered db: {count}")
# Spot-check a few assistant messages
rows = db.execute(
"SELECT id, json_extract(data,'$.role') as role, json_extract(data,'$.modelID') as model, json_extract(data,'$.mode') as mode FROM message LIMIT 6"
).fetchall()
for r in rows:
print(dict(r))
db.close()
print(f"\nRecovered database written to: {OUT_PATH}")
print("IMPORTANT: Do not replace the live opencode.db while opencode is running.")
print("Close opencode first, then: cp ~/.local/share/opencode/opencode.db.recovered ~/.local/share/opencode/opencode.db")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment