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 the V2 schema is 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 debug paths db

OpenCode 2 writes to session_v2, session_message, and session_part. If you upgraded from V1, the old session, message, and part tables are still present for history created before the upgrade. Queries below target the V2 tables; swap in the old names only when you specifically want pre-upgrade sessions.

The Database at a Glance

project (1)───(N) session_v2 (1)───(N) session_message

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 run --session <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_v2
ORDER BY time_created DESC;

3. Messages are JSON. session_message.type is the role (user, assistant, system, and a few internal types). Inside data:

{
  "agent": "build",
  "model": { "providerID": "opencode-go", "id": "deepseek-v4.1-flash", "variant": "max" },
  "tokens": { "input": 720, "output": 342 },
  "cost": 0,
  "content": [
    { "type": "reasoning", "text": "..." },
    { "type": "text", "text": "review the codebase" },
    {
      "type": "tool",
      "id": "call_00_...",
      "name": "read",
      "state": { "status": "completed", "input": { "filePath": "/path/to/file.ts" }, "content": [] }
    }
  ]
}

The content array is where text, reasoning, and tool parts live. state.input is the tool's arguments; state.content is its result.

"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_v2
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 message text:

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

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

opencode run --session ses_312c8fd8bffe5DMWmNRPKKLvt3 "Continue from where that session stopped"

"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_v2 row records its working directory, so you can scope to the project:

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

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

SELECT json_extract(part.value, '$.text') as text
FROM session_message m
JOIN json_each(json_extract(m.data, '$.content')) part
WHERE m.session_id = 'ses_XXX'
  AND json_extract(part.value, '$.type') = 'text'
ORDER BY m.time_created, part.key;

"Which Change Broke the Build?"

Each session_v2 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:

SELECT id, title, summary_additions, summary_deletions, summary_files,
       datetime(time_created/1000, 'unixepoch', 'localtime') as created
FROM session_v2
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.

"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 the tool parts preserve the call chain in order. Reconstruct the exact sequence of operations:

SELECT m.time_created,
       json_extract(part.value, '$.type') as type,
       json_extract(part.value, '$.name') as tool,
       json_extract(part.value, '$.state.status') as status,
       json_extract(part.value, '$.state.input') as input
FROM session_message m
JOIN json_each(json_extract(m.data, '$.content')) part
WHERE m.session_id = 'ses_XXX'
  AND json_extract(part.value, '$.type') = 'tool'
ORDER BY m.time_created, part.key;

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 data lets you slice by exactly what you care about. session_v2 stores token and cost totals per session:

SELECT id, title,
       tokens_input, tokens_output,
       tokens_cache_read, tokens_cache_write,
       cost,
       datetime(time_created/1000, 'unixepoch', 'localtime') as created
FROM session_v2
ORDER BY cost DESC
LIMIT 20;

Or per model, counting assistant messages across all sessions:

SELECT json_extract(m.data, '$.model.id') as model,
       json_extract(m.data, '$.model.providerID') as provider,
       count(*) as messages,
       sum(json_extract(m.data, '$.tokens.input')) as input_tokens,
       sum(json_extract(m.data, '$.tokens.output')) as output_tokens
FROM session_message m
WHERE m.type = 'assistant'
GROUP BY provider, model
ORDER BY output_tokens DESC;

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

SELECT json_extract(m.data, '$.agent') as agent,
       count(*) as messages
FROM session_message m
WHERE m.type = 'assistant'
GROUP BY agent
ORDER BY messages DESC;

Subscription providers such as OpenCode Go report cost: 0 because usage counts against a plan, not per-token billing. Use the token columns for those; use cost when you bring your own API key.

"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(
        'type', m.type,
        'text', json_extract(part.value, '$.text'),
        'time', m.time_created
    )
) as conversation
FROM session_message m
JOIN json_each(json_extract(m.data, '$.content')) part
WHERE m.session_id = 'ses_XXX'
  AND json_extract(part.value, '$.type') = 'text'
ORDER BY m.time_created, part.key;

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

Stop OpenCode or use SQLite's .backup command first if you want a guaranteed consistent copy while a session is running.

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.jsonc, MCP, skills
• Prompt Engineering in OpenCode — AGENTS.md conventions and slash commands

Sources

• OpenCode V2 CLI
• OpenCode V2 commands