Memory Search

Memory Search: SQLite Trajectory Database

Direct SQL access to mimocode's trajectory database for structured analysis that the memory (BM25 over curated markdown) and history (FTS over raw messages) tools cannot perform — aggregation, filtering by tool/status/time, cross-session pattern detection, and execution chain inspection.

When to use

• You need to aggregate or count across sessions (e.g. "which tool fails most often?", "how many sessions touched file X?").
• You need to filter by structure — tool name, status, agent_id, time range — not just text content.
• You need to view a complete execution chain for a session (every tool call in order).
• You need to verify a memory claim against what actually happened (the DB is the source of truth).
• The memory and history tools returned nothing useful despite multiple query attempts.

Locating the database

# Typically at this path. MIMOCODE_DB env var overrides if set.
sqlite3 -readonly ~/.local/share/mimocode/mimocode.db ".tables"

Always use -readonly or only SELECT queries — never modify the database.

Schema

Part types in part.data

• {"type":"text","text":"..."} — agent text output
• {"type":"tool","tool":"<name>","callID":"...","state":{"status":"completed","input":{...},"output":"..."}} — completed tool call
• {"type":"tool","tool":"<name>","callID":"...","state":{"status":"error","input":{...},"error":"..."}} — failed tool call (no output field; error message in $.state.error)
• {"type":"step-start"} / {"type":"step-finish","tokens":...} — step boundaries
• {"type":"compaction","auto":true/false} — compaction boundary
• {"type":"checkpoint",...} — checkpoint/rebuild boundary

Key conventions

• agent_id = 'main' = main agent; other values = subagent (e.g. "explore-1", "general-1").
• $.state.output only exists when $.state.status = "completed". Failures store the message in $.state.error.
• time_created is Unix milliseconds.

Query templates

List recent sessions for this project:

SELECT id, title, time_created,
       datetime(time_created/1000, 'unixepoch', 'localtime') as created
FROM session
WHERE project_id = '<PROJECT_ID>'
  AND parent_id IS NULL
ORDER BY time_created DESC
LIMIT 20;

Find user messages containing a keyword:

SELECT m.session_id, m.id,
       substr(json_extract(p.data, '$.text'), 1, 200) as preview
FROM message m
JOIN part p ON p.message_id = m.id AND p.session_id = m.session_id
WHERE json_extract(m.data, '$.role') = 'user'
  AND json_extract(p.data, '$.type') = 'text'
  AND json_extract(p.data, '$.text') LIKE '%keyword%'
ORDER BY m.time_created DESC
LIMIT 10;

Find tool calls by tool name:

SELECT m.session_id, m.id, m.agent_id,
       json_extract(p.data, '$.tool') as tool,
       json_extract(p.data, '$.state.status') as status,
       substr(COALESCE(json_extract(p.data, '$.state.output'), json_extract(p.data, '$.state.error')), 1, 300) as result_preview
FROM message m
JOIN part p ON p.message_id = m.id AND p.session_id = m.session_id
WHERE json_extract(m.data, '$.role') = 'assistant'
  AND json_extract(p.data, '$.type') = 'tool'
  AND json_extract(p.data, '$.tool') = '<TOOL_NAME>'
  AND m.session_id = '<SESSION_ID>'
ORDER BY m.time_created DESC
LIMIT 20;

View a session's full execution chain:

SELECT m.id, m.agent_id,
       json_extract(p.data, '$.type') as part_type,
       json_extract(p.data, '$.tool') as tool,
       substr(p.data, 1, 800) as preview
FROM message m
JOIN part p ON p.message_id = m.id AND p.session_id = m.session_id
WHERE m.session_id = '<SESSION_ID>'
  AND json_extract(m.data, '$.role') = 'assistant'
ORDER BY m.time_created, p.time_created;

Find repeated stdout errors (completed bash calls, last 7 days):

SELECT substr(json_extract(p.data, '$.state.output'), 1, 200) as error_output,
       COUNT(*) as occurrences,
       GROUP_CONCAT(DISTINCT m.session_id) as sessions
FROM part p
JOIN message m ON m.id = p.message_id AND m.session_id = p.session_id
WHERE json_extract(p.data, '$.type') = 'tool'
  AND json_extract(p.data, '$.tool') = 'bash'
  AND json_extract(p.data, '$.state.status') = 'completed'
  AND json_extract(p.data, '$.state.output') LIKE '%error%'
  AND m.time_created > (strftime('%s', 'now') - 7*86400) * 1000
GROUP BY substr(json_extract(p.data, '$.state.output'), 1, 200)
HAVING occurrences > 1
ORDER BY occurrences DESC
LIMIT 10;

Find actual tool failures (any tool, last 7 days):

SELECT json_extract(p.data, '$.tool') as tool,
       substr(json_extract(p.data, '$.state.error'), 1, 200) as error_msg,
       COUNT(*) as occurrences,
       GROUP_CONCAT(DISTINCT m.session_id) as sessions
FROM part p
JOIN message m ON m.id = p.message_id AND m.session_id = p.session_id
WHERE json_extract(p.data, '$.type') = 'tool'
  AND json_extract(p.data, '$.state.status') = 'error'
  AND m.time_created > (strftime('%s', 'now') - 7*86400) * 1000
GROUP BY json_extract(p.data, '$.tool'), substr(json_extract(p.data, '$.state.error'), 1, 200)
HAVING occurrences > 1
ORDER BY occurrences DESC
LIMIT 10;

Search strategies

Constraints

• Read-only: Never modify the database. Always sqlite3 -readonly or SELECT only.
• Performance: The DB can be multi-GB. Always use LIMIT and filter by session_id or time_created range.
• Privacy: Raw trajectory contains everything the user typed. Treat it with care.
• JSON access: Part data is JSON-in-a-column. Always use json_extract() for structured field access.