OpenCode Session History & Recovery

Every OpenCode session you've ever run — every prompt, response, tool call, and file change — is stored in a single SQLite database on your machine. Git history tells you what changed. The session database is the only record of why: the reasoning, the dead ends, the exact order of operations that produced your code.

That record is valuable in ways you only notice when you need it. This guide covers where it lives, how it's structured, and how to mine it with SQL for real situations.

Where the Database Lives

Session history is stored in:

~/.local/share/opencode/opencode.db

The file contains the full conversation history, file snapshots, and token/cost statistics for every project and session. You can find the exact path at any time with:

opencode db path

The Database at a Glance

The schema is straightforward, and understanding it unlocks every query below:

project (1)───(N) session (1)───(N) message (1)───(N) part

Three things to internalize before writing queries:

1. Session IDs look like ses_.... For example ses_312c8fd8bffe5DMWmNRPKKLvt3. You'll use these to scope queries and to continue sessions with opencode -s <id>.

2. Timestamps are milliseconds since the Unix epoch. Divide by 1000 and convert to read them:

SELECT id, title,
       datetime(time_created/1000, 'unixepoch', 'localtime') as created
FROM session
ORDER BY time_created DESC;

3. message vs part. The message table stores metadata — which role, which model, which agent:

{
  "role": "assistant",
  "agent": "build",
  "model": { "providerID": "opencode", "modelID": "big-pickle" }
}

The part table stores the actual content, in three shapes:

{ "type": "text", "text": "review the codebase" }

{ "type": "tool_use", "name": "Read", "input": { "filePath": "/path/to/file.ts" } }

{ "type": "tool_result", "tool_use_id": "toolu_01...", "content": "file contents..." }

Sessions can also have a parent_id, which means they were forked from another session — a useful trail when work branches.

"I've Solved This Before — Where?"

The most common reason to dig into the database: you know you solved a problem weeks ago, but you can't remember which session it was in. Search the session titles:

SELECT id, title, directory,
       datetime(time_created/1000, 'unixepoch', 'localtime') as created
FROM session
WHERE title LIKE '%oauth%'
ORDER BY time_created DESC;

Titles are auto-generated, so a fuzzy keyword usually finds it. When the title doesn't contain the keyword, scan the actual conversation text in part:

SELECT DISTINCT s.id, s.title,
       datetime(s.time_created/1000, 'unixepoch', 'localtime') as created
FROM session s
JOIN part p ON p.session_id = s.id
WHERE json_extract(p.data, '$.type') = 'text'
  AND json_extract(p.data, '$.text') LIKE '%stripe webhook%';

Once you have the session ID, continue it directly instead of re-deriving the solution:

opencode -s ses_312c8fd8bffe5DMWmNRPKKLvt3

"What Was I Doing in This Project Months Ago?"

Coming back to a project after a long break, the fastest way to re-orient is to read your last conversation. Each session row records its working directory, so you can scope to the project and pull the most recent sessions:

SELECT id, title,
       summary_additions, summary_deletions, summary_files,
       datetime(time_created/1000, 'unixepoch', 'localtime') as created
FROM session
WHERE directory LIKE '%promptgenius%'
ORDER BY time_created DESC
LIMIT 10;

Then dump the full text of the most recent one — this is the closest thing to "loading your brain back in":

SELECT json_extract(data, '$.text') as text
FROM part
WHERE session_id = 'ses_XXX'
  AND json_extract(data, '$.type') = 'text'
ORDER BY time_created;

"Which Change Broke the Build?"

Every session row stores git-like summary stats: summary_additions, summary_deletions, and summary_files. That makes it easy to find the session that landed a lot of change around a specific date — useful when a regression appears and you need to isolate what to blame:

SELECT id, title, summary_additions, summary_deletions, summary_files,
       datetime(time_created/1000, 'unixepoch', 'localtime') as created
FROM session
WHERE directory LIKE '%api%'
  AND summary_additions > 100
ORDER BY summary_additions DESC
LIMIT 10;

Big changes are usually the ones that need review first. When you've narrowed it down, replay that session's tool calls to see the exact sequence of file writes and commands that caused the problem (see next section).

"Why Did the Agent Do That?"

A session did something wrong — wrote to the wrong file, deleted something, made a change you didn't expect. The final message doesn't tell you how it got there, but part preserves the full tool_use → tool_result chain in order. Reconstruct the exact sequence of operations:

SELECT time_created,
       json_extract(data, '$.type') as type,
       json_extract(data, '$.name') as tool,
       json_extract(data, '$.input') as input
FROM part
WHERE session_id = 'ses_XXX'
  AND json_extract(data, '$.type') IN ('tool_use', 'tool_result')
ORDER BY time_created;

Now you can see, step by step, which tool call wrote what — the root cause is usually visible in the ordering, even when the final text reads fine.

"Where Is the Budget Actually Going?"

opencode stats gives you aggregate numbers, but the raw message metadata lets you slice by exactly what you care about: which provider, which model, which agent, per project or per week. For example, total messages per model across all your sessions:

SELECT json_extract(data, '$.model.modelID') as model,
       count(*) as messages
FROM message
GROUP BY model
ORDER BY messages DESC;

Or per agent, to see how much work happens in plan mode vs build mode:

SELECT json_extract(data, '$.agent') as agent,
       count(*) as messages
FROM message
GROUP BY agent
ORDER BY messages DESC;

This is the data behind the hunch that "I'm burning tokens on the expensive model for boilerplate." Now it's a number.

"Turn My History Into Searchable Memory"

The database is your personal knowledge base — you just can't search it yet. Two ways to change that:

Export conversations to JSON and index them anywhere (grep, SQLite FTS, a vector store):

SELECT json_group_array(
    json_object(
        'role', json_extract(m.data, '$.role'),
        'text', json_extract(p.data, '$.text'),
        'time', p.time_created
    )
) as conversation
FROM message m
JOIN part p ON m.id = p.message_id
WHERE m.session_id = 'ses_XXX'
  AND json_extract(p.data, '$.type') = 'text'
ORDER BY p.time_created;

Back up the file itself. It's a single SQLite file — copy it to another machine or a backup location to keep history across reinstalls:

cp ~/.local/share/opencode/opencode.db ~/backups/opencode-$(date +%Y%m%d).db

The Supported Way Out

Raw SQL is the power path, but for routine tasks the built-in commands are maintained and safer:

Use the CLI when you just need a list or an export. Use SQL when you need to search, slice, or correlate — that's what it's there for.

Related Pages

• OpenCode Getting Started — session commands, plan mode, and the first session walkthrough
• OpenCode Configuration Reference — opencode.json, MCP, skills
• Prompt Engineering in OpenCode — AGENTS.md conventions and slash commands