pond_sql
Run read-only SQL (SELECT/WITH) over session, message, and part tables for aggregation, exact-string matching, tool-call analytics, and bulk export (parquet/NDJSON).
Instructions
Advanced escape hatch: run ONE read-only SQL statement (SELECT/WITH, DataFusion / PostgreSQL-compatible) over the sessions / messages / parts tables. NOT for finding or reading conversations - pond_search and pond_get_session / pond_get_message cover almost all recall. Reach for SQL only for: aggregation (counts, group-by, joins, time buckets), exact strings or identifiers in conversational text (contains_tokens / fts), tool-call analytics and tool bodies, subagent sessions, bulk export (format=parquet|ndjson). Read resource schema://pond-sql FIRST - exact columns, indexed predicates, JSON access rules, worked examples; do not guess column names or JSON paths. Inline text output is row-capped and long cells clip with a +N chars marker (full values via format=parquet|ndjson); queries are wall-clock-capped (raise via timeout_seconds).
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| query | Yes | One read-only SQL statement (SELECT/WITH only; writes rejected). Exact columns - messages(session_id, message_id, timestamp, role, source_agent, project, content [system-role only], search_text [the conversational text], embedding_model, options) | sessions(session_id, parent_session_id, parent_message_id, source_agent, created_at, project, options) | parts(session_id, message_id, id, ordinal, type, provenance, tool_name, call_id, is_failure, variant_data, options). parts.type enums use underscores: 'tool_call', 'tool_result', 'text', 'reasoning', 'file'. Tool bodies live in JSONB variant_data - tool_call is {call_id, name, params} (a Bash command is json_extract(variant_data, '$.params.command')), tool_result is {call_id, name, is_failure, result}; never CAST JSON columns. No substring index covers tool bodies: an unscoped LIKE over variant_data full-scans and times out on a remote store - scope-then-scan instead (collect session_ids WHERE contains_tokens(search_text, '...'), then match variant_data fields only within them; worked example in schema://pond-sql). Tool analytics: prefer the narrow native columns (tool_name, call_id, is_failure). Text search: WHERE contains_tokens(search_text, 'words'), or FROM fts('messages', '{...}') for BM25 ranking. Joins, indexed columns, JSON functions, pagination, worked examples: resource schema://pond-sql. | |
| format | No | Output format: "text" (default; rendered ASCII table with metrics footer, row-capped), "parquet", or "ndjson". For parquet/ndjson the full result set is written to a file and a `pond-sql-export://` resource link is returned (no truncation) - ndjson is the path for machine-readable JSON output. | |
| timeout_seconds | No | Per-query timeout in seconds (default 30, max 600). Raise it for a genuinely long-running query (e.g. a large remote-store scan); prefer narrower predicates and the indexed/native columns first. |