Conversation Recall

Query the OpenCode SQLite database to find and display past conversations.

Database Access

Run queries via the opencode db CLI:

# Run a query (tsv is default and most token-efficient)
opencode db "SELECT ..."

# Get the database file path
opencode db path

Schema

session table (conversations)

Column	Type	Description
id	text PK	Session ID (e.g. ses_...)
project_id	text	Project this session belongs to
directory	text	Working directory
title	text	Conversation title
time_created	integer	Creation timestamp (ms epoch)
time_updated	integer	Last update timestamp (ms epoch)

message table

Column	Type	Description
id	text PK	Message ID
session_id	text FK	References session.id
data	text	JSON: {role, time: {created, completed}, ...}

part table (message content)

Column	Type	Description
id	text PK	Part ID
message_id	text FK	References message.id
session_id	text	Session ID
data	text	JSON: {type, text, ...}
time_created	integer	Timestamp (ms epoch)

Only parts with json_extract(data, '$.type') = 'text' contain conversation text. The text content is in json_extract(data, '$.text').

Workflow

1. Search for conversations

When the user asks to find a conversation, search the part table for matching text and group by session:

SELECT
  p.session_id,
  s.title,
  s.directory,
  datetime(s.time_created / 1000, 'unixepoch', 'localtime') as created,
  COUNT(DISTINCT p.message_id) as matching_messages
FROM part p
JOIN session s ON s.id = p.session_id
WHERE json_extract(p.data, '$.type') = 'text'
  AND json_extract(p.data, '$.text') LIKE '%SEARCH_TERM%'
GROUP BY p.session_id
ORDER BY s.time_updated DESC
LIMIT 10

2. List recent conversations

SELECT
  s.id,
  s.title,
  s.directory,
  datetime(s.time_created / 1000, 'unixepoch', 'localtime') as created,
  datetime(s.time_updated / 1000, 'unixepoch', 'localtime') as updated
FROM session s
ORDER BY s.time_updated DESC
LIMIT 20

3. Read a specific conversation

Once a session is identified, retrieve the full conversation text in order:

SELECT
  json_extract(m.data, '$.role') as role,
  json_extract(p.data, '$.text') as text
FROM part p
JOIN message m ON m.id = p.message_id
WHERE p.session_id = 'SESSION_ID'
  AND json_extract(p.data, '$.type') = 'text'
ORDER BY json_extract(m.data, '$.time.created') ASC

Guidelines

• Default to --format tsv (the default) to minimize token usage. Use --format json only when structured parsing is strictly required.
• When searching, use LIKE '%term%' with COLLATE NOCASE for case-insensitive matching.
• Summarize conversations rather than dumping raw text unless the user asks for full content.
• If multiple sessions match, present a numbered list with title, date, and directory so the user can pick one.
• Timestamps are millisecond epoch; convert with datetime(ts / 1000, 'unixepoch', 'localtime').