Query a session database with SQL
smart_compact_sqlRun read-only SQLite SELECT queries against a session’s stored messages to compute counts, aggregates, and joins that full-text search cannot express.
Instructions
Run one read-only SQLite SELECT (or WITH … SELECT) against a session's database, for counts, aggregates and joins that smart_compact_search cannot express. The database holds every message of the session, in or out of your context. Schema: messages(seq INTEGER PRIMARY KEY, fingerprint, thread, role 'user'|'assistant'|'tool', kind 'text'|'tool_use'|'tool_result'|'thinking', name (tool name or NULL), text, blob_id, in_context 0|1, trimmed_at_seq, trimmed_at, created_at); lines(id, message_seq, line_no (1-based), role, text) with one row per line of each message; messages_fts(text) and lines_fts(text): FTS5 indexes whose rowid is messages.seq / lines.id; vectors(message_seq, model, dim, blob); events(id, at, kind 'trim'|'fail_open'|'usage', version, location, error, tokens_before, tokens_after, dropped, model, thread_root, trimmed 0|1|2, input_tokens, cache_read_tokens, cache_creation_tokens, output_tokens, breakpoints, breakpoint_at), one row per trim, fail-open or model call; trim token figures estimated, usage counts provider-reported (NULL when not reported). Full-text: lines_fts MATCH 'error AND payments' (FTS5 syntax; join lines ON lines.id = lines_fts.rowid); exact substrings: text LIKE '%level=error%' (case-insensitive for ASCII). Examples: (1) lines naming a level and a service: SELECT count() AS n FROM lines WHERE text LIKE '%level=error%' AND text LIKE '%service=payments%'; (2) tool calls per tool: SELECT name, count() AS calls FROM messages WHERE kind = 'tool_use' GROUP BY name ORDER BY calls DESC; (3) error lines per tool result: SELECT m.seq, m.name, count(*) AS errors FROM lines_fts JOIN lines l ON l.id = lines_fts.rowid JOIN messages m ON m.seq = l.message_seq WHERE lines_fts MATCH 'error' AND m.kind = 'tool_result' GROUP BY m.seq ORDER BY errors DESC LIMIT 10. Writes, ATTACH and PRAGMA are rejected; the query stops after 2 s; at most 200 rows return (truncated: true when there were more; long text cells are cut at 2000 characters).
Tool call template
Replace each placeholder with a value matching the input schema. Only required parameters are included; add optional parameters as needed.
{
"id": 1,
"jsonrpc": "2.0",
"method": "tools/call",
"params": {
"arguments": {
"session_id": "<session_id>",
"sql": "<sql>"
},
"name": "smart_compact_sql"
}
}Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| sql | Yes | One SELECT or WITH statement, at most 10000 characters | |
| session_id | Yes | Session id from the [openmem: continuing session …] pointer |