opencode-db-querying

Files (1)

• SKILL.md

SKILL.md (first lines)

# Query the OpenCode 2 database

Use direct SQL for exact lookups, ordered transcripts, structured inspection, and aggregates. Use `recall_search` for fuzzy or semantic discovery across sessions, then switch to SQL when the session ID or required structure is known.

## Open the database safely

OpenCode 2 stores its SQLite database at `~/.local/share/opencode/opencode.db`. The V1 `opencode db` command no longer exists. Query through `sqlite3` in explicit read-only mode:

```bash
DB="file:$HOME/.local/share/opencode/opencode.db?mode=ro"
sqlite3 -header -column "$DB" "SELECT id, title FROM session_v2 ORDER BY time_updated DESC LIMIT 5;"
sqlite3 -json "$DB" "SELECT id, title FROM session_v2 ORDER BY time_updated DESC LIMIT 5;"
```

Keep every agent query read-only. OpenCode owns this database and may have it open in WAL mode. Never alter rows, schema, write-affecting pragmas, or migration state.

## V2 data model

The current conversation projection is:

```text
project 1--N session_v2 1--N session_message
                    |
                    +--N session_pending
                    +--N session_inbox
                    +--1 instruction_state
```

`session_v2` is the session summary. `session_message` is the ordered transcript projection. Do not use the V1 `session`, `message`, or `part` tables for OpenCode 2 conversations.

### `session_v2`

Important columns:

- Identity and location: `id`, `project_id`, `workspace_id`, `parent_id`, `fork_session_id`, `fork_boundary`, `slug`, `directory`, `path`, `title`, `version`.
- Aggregate usage: `cost`, `tokens_input`, `tokens_output`, `tokens_reasoning`, `tokens_cache_read`, `tokens_cache_write`.
- Current execution defaults: `agent`, `model` (JSON containing `providerID`, `id`, and optionally `variant`).
- State: `metadata`, `revert`, `permission`, `share_url`, summary columns, `resume_attempts`, `idle_outcome`.
- Millisecond timestamps: `time_created`, `time_updated`, `time_idle`, `time_viewed`, `time_compacting`, `time_archived`, `time_suspended`.

`parent_id` identifies child/subagent sessions. `fork_session_id` records a fork separately; do not treat every fork as a child.

### `session_message`

Columns: `id`, `session_id`, `type`, `seq`, `time_created`, `time_updated`, `data`.

`seq` is the canonical per-session order. Always order transcripts by `seq`, not timestamps or IDs.

Current `type` values are `user`, `assistant`, `synthetic`, `system`, `skill`, `shell`, `compaction`, `model-switched`, `agent-switched`, and `location-switched`. Inspect the live database before assuming the list is unchanged:

```sql
SELECT type, count(*) AS rows
FROM session_message
GROUP BY type
ORDER BY rows DESC;
```

Message data differs by type:

- `user`: visible prompt in `data.text`, plus optional `files`, `agents`, and `skills`; timestamp in `data.time.created`.
- `assistant`: `data.agent`, `data.model`, `data.content`, `data.finish`, `data.cost`, `data.tokens`, `data.error`, `data.retry`, and `data.time`.
- `assistant.data.content`: an array whose elements commonly have `type` equal to `text`, `reasoning`, or `tool`.
- Assistant `text` and `reasoning` elements store their content in `text`.
- Assistant `tool` elements store `name`, `id`, `state.status`, `state.input`, `state.content`, `state.metadata`, and timing data. Error states use `state.error`. Shapes vary by status and tool.
- `synthetic`: generated user-like text in `data.text`.
- `system`: model-facing update in `data.text` and optional human-facing `data.description`.
- `skill`: injected skill name, ID, and text.
- `shell`: shell command lifecycle and output, separate from assistant tool content.
- `compaction`: `status`, `reason`, summary fields, error, and timing; the exact shape depends on status.

Use `json_each(data, '$.content')` to expand assistant content. SQLite's JSON operators are useful for ad hoc work, but `json_extract` is clearest in reusable queries.

### Queue and instruction tables

- `session_pending`: admitted work not yet fully projected; ordered by `admitted_seq`. Columns include `type`, JSON `data`, nullable `delivery`, and `time_created`.
- `session_inbox`: queued input; ordered by `enqueued_seq`. The JSON column is named `payload`, not `data`.
- `instruction_state`: projected instruction state with `epoch_start`, `through_seq`, and JSON `initial_values`/`current_values`.
- `instruction_entry`: per-session instruction changes keyed by `(session_id, key)`.
- `instruction_blob`: content-addressed instruction values.

These are internal execution tables. Use them for diagnosis, not as the normal transcript source.

## Timestamps and JSON

All `time_*` columns and JSON times are Unix epoch milliseconds:

```sql
datetime(time_created / 1000, 'unixepoch', 'localtime')
```

JSON columns are stored as text. Select only needed paths because assistant content and tool output can be large:

```sql
json_extract(data, '$.model.providerID')
json_extract(data, '$.tokens.input')
json_extract(content.value, '$.state.status')
```

## Recipes

### Recent sessions

```sql
SELECT id, title, directory,
       datetime(time_created / 1000, 'unixepoch', 'localtime') AS created,
       datetime(time_updated / 1000, 'unixepoch', 'localtime') AS updated
FROM session_v2
WHERE time_archived IS NULL
ORDER BY time_updated DESC
LIMIT 20;
```

Add `AND directory LIKE '%/repo-name%'` to scope by working directory. `directory` is usually enough; joining `project` is unnecessary.

### Session by ID or slug

```sql
SELECT id, title, directory, parent_id, fork_session_id, agent,
       json_extract(model, '$.providerID') AS provider,

From MaxAnderson95/my-opencode-setup, MIT.

More from MaxAnderson95/my-opencode-setup