sqlite-log-query

# SQLite Log Query LingTai keeps durable runtime traces and token ledgers in JSONL files. The SQLite file at `logs/log.sqlite` is an **additive, rebuildable query index** over those JSONL sources of truth. Use it to answer questions that are painful with `grep`: which event types are hottest, what happened inside daemon runs, what chat-history turn surrounded a failure, whether notification/daemon/context events are storming, or how token usage is distributed across main/soul/daemon sources. ## Start here for log.sqlite (quick start) First-use, copy-pasteable snippets. `log.sqlite` lives at `logs/log.sqlite` under the agent directory (`AGENT_DIR=/path/to/project/.lingtai/agent-name`). Open it read-only and run the three first checks: ```bash sqlite3 -readonly logs/log.sqlite '.tables' sqlite3 -readonly logs/log.sqlite 'pragma table_info(events);' sqlite3 -readonly logs/log.sqlite \ "select type, count(*) as n from events group by type order by n desc limit 20;" ``` Plain `sqlite3 logs/log.sqlite` opens the file read-write; for inspection prefer `-readonly` (or the Python URI form below) so you never accidentally write the sidecar. Equivalent read-only Python: ```python import sqlite3 db_path = "/path/to/.lingtai/<agent>/logs/log.sqlite" conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True) for row in conn.execute( "select type, count(*) from events group by type order by count(*) desc limit 20;" ): print(row) ``` The five recipes in **Query recipes** below (event type counts, per-tool result length, percentiles, `tool_call_id` lifecycle, large/spilled results) are directly copy-pasteable. The **Safety contract** and **Gotchas** sections apply to every query. ## Safety contract - **JSONL is authoritative.** `logs/log.sqlite` is derived; deleting it should not delete facts. - **Prefer the CLI.** Use `lingtai-agent log ...` instead of opening the DB for writes yourself. - **Queries are read-only with respect to SQLite database contents.** `log query` accepts read-only `SELECT`, CTE (`WITH ... SELECT`), and `EXPLAIN` statements and opens the sidecar through the kernel read-only inspection path: they never write the main database file (opened `mode=ro` with `PRAGMA query_only=ON`), though SQLite read-support `-wal`/`-shm` sidecar files may still be created or updated. - **Rebuild is offline.** `log rebuild` requires the agent working-directory lock; if the agent is running, stop/sleep/lull/suspend it first as appropriate. - **Runtime SQLite is best effort.** New top-level `logs/events.jsonl` and standard `logs/token_ledger.jsonl` rows are indexed live after the JSONL write succeeds. Chat history, archive, and daemon JSONL sources are indexed into a target agent sidecar by explicit offline rebuild so normal turns and daemon runs do not pay recursive scan or live-rewrite costs. - **Live queries are snapshots.** Runtime writes use SQLite WAL mode. For a complete historical snapshot, stop the agent and run `log rebuild` before querying. - **Never paste secrets.** Logs and chat history can contain URLs, tokens, prompts, and user data — including raw `fields_json`/`entry_json`. Apply the redaction rules below before sharing anything. ## Gotchas - The event-kind column is **`type`**, not `event_type`. - The structured event payload lives in **`fields_json`** (chat rows in `entry_json`); reach into it with `json_extract(fields_json, '$.key')`. - `log.sqlite` is **derived and rebuildable**; JSONL remains authoritative. A missing sidecar is a rebuildable index gap, never lost facts. - Open the sidecar **read-only** for inspection: `sqlite3 -readonly`, the `file:...?mode=ro` URI, or `lingtai-agent log query`. Never write to it directly. - For exact forensic payloads, follow `source_file` / `source_offset` back to the JSONL source. - `fields_json`/`entry_json` can carry URLs, tokens, prompts, and user data — redact before sharing anything. ## Commands Set a variable for the target agent directory: ```bash AGENT_DIR=/path/to/project/.lingtai/agent-name ``` Check whether the sidecar exists and is readable: ```bash lingtai-agent log doctor "$AGENT_DIR" ``` If `doctor` reports `{"status":"missing"...}` or a failing `integrity_check`, rebuild **only while the target agent is stopped/offline**. `doctor` does not detect staleness — a stale but intact sidecar still reports `status: ok`; compare `import_cursors` against source-file mtimes, or just rebuild: ```bash lingtai-agent log rebuild "$AGENT_DIR" ``` `log rebuild` scans the known JSONL trace surfaces under the target agent: - `logs/events.jsonl` → `events` (`source_kind='agent_events'`) - `logs/token_ledger.jsonl` → `token_entries` (`source_kind='agent_token_ledger'`) - `history/chat_history.jsonl` → `chat_entries` (`source_kind='agent_chat'`) - `history/chat_history_archive.jsonl` → `chat_entries` (`source_kind='agent_chat_archive'`) - `daemons/*/logs/events.jsonl` → `events` (`source_kind='daemon_events'`, `run_id=<daemon folder>`) - `daemons/*/logs/token_ledger.jsonl` → `token_entries` (`source_kind='daemon_token_ledger'`, `run_id=<daemon folder>`) - `daemons/*/history/chat_history.jsonl` → `chat_entries` (`source_kind='daemon_chat'`, `run_id=<daemon folder>`) Run a read-only query. The CLI always prints JSON; pipe to `jq .` to pretty-print when it is available: ```bash lingtai-agent log query "$AGENT_DIR" \ 'SELECT id, ts, type, agent_address, substr(fields_json, 1, 240) AS fields FROM events ORDER BY ts DESC LIMIT 20' | jq . ``` ## Schema quick reference `events` indexes top-level agent runtime events and daemon run events: | Column | Meaning | |---|---| | `id` | SQLite row id, not a stable cross-rebuild event identifier | | `ts` | event timestamp as a numeric epoch-like value; ISO strings are parsed when possible | | `type` | event `type` field, or daemon `event` field | | `agent_address` | event `address` field when present | | `agent_name_snapshot` | event `agent_name` field when present | | `fields_json` | the remaining event fields as JSON text | | `source_file` | JSONL file imported from | | `source_offset` | byte offset in the JSONL source; unique with `source_file` | | `source_line` | 1-based JSONL line number | | `source_kind` | `agent_events`, `daemon_events`, or fallback kind | | `scope` | `agent`, `daemon`, or `unknown` | | `run_id` | daemon run folder name for daemon rows | | `inserted_at` | sidecar insertion time | `chat_entries` indexes agent and daemon chat-history JSONL rows: | Column | Meaning | |---|---| | `id` | SQLite row id, not stable across rebuilds | | `ts` | parsed numeric timestamp when a row has `ts`/`timestamp`, else `0` | | `ts_text` | original timestamp text/value as stored in JSONL | | `role` | chat role (`user`, `assistant`, etc.) when present | | `kind` | LingTai daemon user-entry kind (`task`, `tool_results`, `followup`) when present | | `turn` | daemon turn number when present | | `content_text` | best-effort extracted plain text from `text` or content blocks | | `entry_json` | full source chat row as JSON text | | `source_file`, `source_offset`, `source_line` | source JSONL identity | | `source_kind` | `agent_chat`, `agent_chat_archive`, `daemon_chat`, or fallback kind | | `scope` | `agent`, `daemon`, or `unknown` | | `run_id` | daemon run folder name for daemon rows | | `inserted_at` | sidecar insertion time | `token_entries` indexes agent and daemon token-ledger JSONL rows: | Column | Meaning | |---|---| | `id` | SQLite row id, not stable across rebuilds | | `ts` | parsed numeric timestamp when possible | | `ts_text` | original `ts` value from JSONL | | `input_tokens`, `output_tokens`, `thinking_tokens`, `cached_tokens` | token counters from the JSONL ledger row | | `model`, `endpoint` | model/provider endpoint metadata when present | | `source` | ledger source tag such as `main`, `soul`, `daemon`, `tc_wake`, or legacy/null | | `em_id`, `run_id`, `api_call_id` | daemon/run/API attribution when present | | `entry_json` | full source token-ledger row as JSON text | | `source_file`, `source_offset`, `source_line` | source JSONL identity | | `source_kind` | `agent_token_ledger`, `daemon_token_ledger`, or fallback kind | | `scope` | `agent`, `daemon`, or `unknown` | | `inserted_at` | sidecar insertion time | Parent ledgers intentionally include daemon spend rows. If you query both `agent_token_ledger` and `daemon_token_ledger` rows together, avoid double-counting daemon calls that were mirrored into the parent ledger and the daemon-local ledger. Filter by `source_kind`, `source`, `em_id`, or `run_id` according to the report you need. Maintenance tables: - `schema_migrations(version, name, applied_at)` records sidecar schema version. - `import_cursors(source_file, byte_offset, line_no, updated_at)` records the last rebuild/import cursor for each JSONL source. ## Query recipes Recent events: ```sql SELECT id, ts, type, source_kind, run_id, substr(fields_json, 1, 300) AS fields FROM events ORDER BY ts DESC LIMIT 50; ``` Event type counts across agent + daemon events: ```sql SELECT source_kind, type, COUNT(*) AS n, MIN(ts) AS first_ts, MAX(ts) AS last_ts FROM events GROUP BY source_kind, type ORDER BY n DESC LIMIT 50; ``` Per-tool result length aggregation (`$.result` holds the tool output text): ```sql SELECT json_extract(fields_json, '$.tool_name') AS tool, COUNT(*) AS n, CAST(AVG(length(json_extract(fields_json, '$.result'))) AS INT) AS avg_result_len, MAX(length(json_extract(fields_json, '$.result'))) AS max_result_len, SUM(CASE WHEN length(json_extract(fields_json, '$.result')) > 5000 THEN 1 ELSE 0 END) AS over_5000 FROM events WHERE type = 'tool_result' AND json_extract(fields_json, '$.result') IS NOT NULL GROUP BY tool ORDER BY n DESC LIMIT 20; ``` Nearest-rank percentiles of tool result lengths (SQLite window functions): ```sql WITH ranked AS ( SELECT length(json_extract(fields_json, '$.result')) AS result_len, ROW_NUMBER() OVER (ORDER BY length(json_extract(fields_json, '$.result'))) AS rn, COUNT(*) OVER () AS n FROM events WHERE type = 'tool_result' AND json_extract(fields_json, '$.result') IS NOT NULL ) SELECT MAX(CASE WHEN rn <= CAST(n * 0.50 + 0.5 AS INTEGER) THEN result_len END) AS p50, MAX(CASE WHEN rn <= CAST(n * 0.90 + 0.5 AS INTEGER) THEN result_len END) AS p90, MAX(CASE WHEN rn <= CAST(n * 0.95 + 0.5 AS INTEGER) THEN result_len END) AS p95, MAX(CASE WHEN rn <= CAST(n * 0.99 + 0.5 AS INTEGER) THEN result_len END) AS p99, MAX(result_len) AS max_len FROM ranked; ``` Trace one `tool_call_id` lifecycle. A full lifecycle typically spans `tool_call_received` -> `tool_reasoning` -> `tool_call_normalized` -> `tool_call_approved` -> `tool_call` -> `tool_call_dispatch_start` -> `tool_call_dispatch_done` -> `tool_result` -> `tool_result_durable_log_visible` -> `tool_result_model_visible` (daemon runs prefix some of these with `daemon_` and carry `run_id`): ```sql SELECT ts, type, source_kind, run_id, json_extract(fields_json, '$.tool_name') AS tool, substr(fields_json, 1, 160) AS fields FROM events WHERE json_extract(fields_json, '$.tool_call_id') = 'call_00_XXXX' OR json_extract(fields_json, '$.tool_trace_id') = 'call_00_XXXX' ORDER BY ts; ``` Find large/spilled tool results. `tool_result_spilled` rows keep the real size in `$.original_char_count` and point to `$.spill_path`: ```sql SELECT id, ts, type, json_extract(fields_json, '$.tool_name') AS tool, COALESCE(json_extract(fields_json, '$.original_char_count'), length(json_extract(fields_json, '$.result'))) AS result_len, json_extract(fields_json, '$.spill_path') AS spill_path FROM events WHERE type IN ('tool_result', 'tool_result_spilled') AND json_extract(fields_json, '$.result') IS NOT NULL ORDER BY result_len DESC LIMIT 20; ``` Recent chat-history entries: ```sql SELECT id, source_kind, run_id, role, kind, turn, substr(content_text, 1, 400) AS text FROM chat_entries ORDER BY id DESC LIMIT 50; ``` Join daemon tool events with daemon chat rows by `run_id`: ```sql