Created
March 25, 2026 19:28
-
-
Save etherops/df78300a0ce6eeb08e056533bdfa30d9 to your computer and use it in GitHub Desktop.
deep recovery opencode sqllite db
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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