dev-session-debug

# Session Database Debugging Every Capsem VM session produces a ledger in its session directory: `~/.capsem/run/sessions/<id>/` for an ephemeral VM, `~/.capsem/run/persistent/<name>/` for a named one. The ledger is two files that only make sense together: - `session.db` -- SQLite, every telemetry table plus the index of captured bodies. - `session.bodies` -- the append-only body archive: HTTP, model, tool, exec and security payloads, deflated in blocks of about 1 MiB. SQLite never holds body bytes. Copy, fork or delete them as a pair; either one alone is useless. A global `~/.capsem/sessions/main.db` aggregates stats across sessions. The session ledger has no migrations. A `session.db` written by an older build fails to open, loudly and by name, rather than being upgraded in place. ## Session identity invariant `<id>` is the opaque VM/session id used for route paths, DB-handle keys, session-directory names, `CAPSEM_VM_ID`, and UI tab routing. Human names such as `co-work1` or `code-vm1` are display aliases only and must live in `name` fields. User surfaces may accept a name (`capsem resume co-work1`), but they must translate it to the VM id before calling `/vms/{id}/...`. Never use a persistent registry name as `SandboxInfo.id`, never look up telemetry routes with `registry.get(&id)` unless the id has first been resolved to the registry key, and never key `session_db_handles` by display name. If a UI shows telemetry for the wrong provider/model, check this boundary first: `/vms/list` and `/vms/{id}/info` must expose `id = session id` and `name = display name`, and `/vms/{id}/stats/detail` must open the DB under the resolved session directory for that id. A session named `co-work1` showing another session's `ollama` rows while its own DB has AGY/Google rows is a route identity bug until proven otherwise. ## Quick inspection ### Listing sessions Python here means the build-system environment: `uv run --project build_system --frozen python ...` (a bare `python3` may be a system interpreter without the packages these need). ```bash python3 build_system/scripts/doctor/list_sessions.py # Recent sessions python3 build_system/scripts/doctor/list_sessions.py -n 20 # Show more python3 build_system/scripts/doctor/list_sessions.py --with-model # Only sessions with AI model calls python3 build_system/scripts/doctor/list_sessions.py --with-db # Only sessions whose ledger is still on disk python3 build_system/scripts/doctor/list_sessions.py --with-net # Only sessions with network events python3 build_system/scripts/doctor/list_sessions.py --with-tools # Only sessions with tool calls python3 build_system/scripts/doctor/list_sessions.py --min-cost 0.01 # Only sessions that cost money python3 build_system/scripts/doctor/list_sessions.py --with-db --with-model # Combine filters ``` Output columns: ID, Created (MM-DD HH:MM:SS), Duration, Cost, net events, tokens (in+out), tool calls, fs events. Sessions with `*` after the ID still have a `session.db` on disk (queryable). Stats come from the main.db rollup, so they're available after the session directory is gone. ### Deep inspection ```bash python3 build_system/scripts/doctor/check_session.py # Full integrity check on latest session python3 build_system/scripts/doctor/check_session.py <id> # Specific session or named VM python3 build_system/scripts/doctor/check_session.py --db path/session.db # A ledger file directly python3 build_system/scripts/doctor/check_session.py --verify-bodies # Also read every body back through its hash python3 build_system/scripts/doctor/check_session.py -n 10 # Show 10 preview rows per table ``` Checks: SQLite page integrity (`PRAGMA quick_check`, first -- a damaged file ends the report), table existence, row counts, body archive integrity (below), file-monitor overflow windows, tool lifecycle integrity (orphaned tool_calls/tool_responses), AI provider correlation (net_events vs model_calls), and NULL detection in critical fields. It exits 1 when the ledger is damaged. Route readiness (`DbHandle::ready`) checks the schema's shape only, once per `schema_version`, and never scans pages: that scan cost 1.8 s per failed poll on a million-row ledger (google/capsem#230). Page damage surfaces as a `malformed` error on the query that reads it, at the ledger copy's `quick_check`, and here. The body archive check compares the index with the file without trusting either: every `event_body_blobs.block_offset` exists in `body_blocks`; every `body_offset + body_len` fits in its block's `raw_len`; `session.bodies` is at least as long as the largest `block_offset + disk_len` (a block's committed extent, headers included) and starts with the archive's file header. `--verify-bodies` then reads every body, inflating each segment once, and checks it against `body_hash`. ## Session database tables (session.db) The model/tool contract is intentionally one ledger: - `model_calls` is one row per model exchange: request sent to the provider and response received from it. - `model_items` is the ordered item ledger for request, reasoning/thinking, response, tool_call, and tool_response content inside those exchanges. - `tool_calls` is the canonical user/security tool-call ledger for all origins (`native`, `mcp`, `builtin`, `local`). User-facing tool counts and CEL tool evidence come from this table. - `tool_responses` records tool result content sent back to a model. A response row must match a `tool_calls.call_id` in the same trace. - MCP protocol facts are typed security events. MCP-origin `tools/call` activity must appear in `tool_calls` with `origin = 'mcp'`. - One `model_calls.id` can emit many `tool_calls.call_id` values. The tool response must reuse the same `call_id`; MCP can enrich that same logical call, but it does not create a second product ledger. ### Identity Graph Use this graph when correlating model, tool, and security rows: ```mermaid flowchart TD Session["session_id<br/>one Capsem session database"] Trace["trace_id<br/>causal runtime chain"] Turn["turn_id<br/>one user-visible agent turn<br/>user input + all resulting work"] ModelA["model_call_id A = model_calls.id<br/>one provider exchange<br/>one request + one response"] ModelB["model_call_id B = model_calls.id<br/>later provider exchange<br/>same user-visible turn"] ModelAItems["model_items for model_call_id A<br/>request, reasoning, response, tool_call"] ModelBItems["model_items for model_call_id B<br/>tool_response input, request, reasoning, response"] ToolA1["tool_call_id A1 = tool_calls.call_id<br/>logical tool invocation"] ToolA2["tool_call_id A2 = tool_calls.call_id<br/>logical tool invocation"] ToolA1Response["tool_responses.call_id = A1<br/>same tool_call_id"] ToolA2Response["tool_responses.call_id = A2<br/>same tool_call_id"] McpFacts["MCP transport facts<br/>origin/type enrichment<br/>same tool_call_id, no duplicate ledger"] EventRows["event_id rows<br/>http, dns, model, tool, file, process, credential, security"] BodyBlobs["event_body_blobs<br/>index by event_id into session.bodies"] SecurityRows["security_rule_events<br/>rule matches by event_id"] ProviderIds["provider response_id / message_id / transport ids<br/>metadata only"] Session --> Trace Trace --> Turn Turn -->|"contains 1..N"| ModelA Turn -->|"contains 1..N"| ModelB ModelA -->|"owns ordered rows"| ModelAItems ModelB -->|"owns ordered rows"| ModelBItems ModelA -->|"emits 0..N"| ToolA1 ModelA -->|"emits 0..N"| ToolA2 ToolA1 -->|"response reuses id"| ToolA1Response ToolA2 -->|"response reuses id"| ToolA2Response McpFacts -.->|"enriches"| ToolA1 McpFacts -.->|"enriches"| ToolA2 ToolA1Response -->|"can feed later exchange"| ModelB ToolA2Response -->|"can feed later exchange"| ModelB Trace --> EventRows Turn --> EventRows ModelA --> EventRows ModelB --> EventRows ToolA1 --> EventRows ToolA2 --> EventRows EventRows --> BodyBlobs EventRows --> SecurityRows ModelA -.-> ProviderIds ModelB -.-> ProviderIds ``` Definitions: - `event_id` identifies one emitted ledger event row. Security rows, body blobs, and event detail routes join back through this id. - `trace_id` groups runtime work caused by one causal operation across tables: HTTP, DNS, model, tool, file, process, credentials, and security. - `turn_id` groups all work caused by one user-visible agent turn: the user's input, every provider exchange needed to answer it, every tool request and response, and every emitted HTTP/DNS/file/process/security row caused by it. - `model_call_id` is the `model_calls.id` value for exactly one provider request/response exchange inside a turn. It owns that exchange's request, reasoning/thinking, response, model-emitted tool-call items, token counts, and provider metadata. It is not the whole user turn; a single `turn_id` can contain multiple `model_call_id` values. - `tool_call_id` identifies one logical tool invocation across model-native tools, MCP transport, Capsem built-ins, and local tools. In SQLite it is stored as `tool_calls.call_id` and `tool_responses.call_id`. Provider response ids, message ids, and transport request ids are provider or transport metadata. They are not Capsem's join contract. One `turn_id` is the user-input scope. It can contain multiple `model_call_id` values when an agent calls the model, executes tools, then calls the model again with tool results. One `model_call_id` is one provider-exchange scope and carries that exchange's request, reasoning/thinking, response, token counts, and ordered `model_items`. It can emit zero or more `tool_call_id` values; this is the canonical one-to-many relationship for model-visible tools. Stated as the debugging invariant: one `model_call_id` can emit N `tool_call_id` values, and each emitted tool response must reuse that `tool_call_id`. A tool response must carry the same `tool_call_id` as the tool request. MCP is not a second user-facing tool ledger. MCP-origin `tools/call` activity must resolve to a `tool_calls` row with `origin = 'mcp'` or enrich an existing logical `tool_call_id`. An MCP call observed without a corresponding logical tool call is an integrity/security finding, not a separate product counter. Key cardinalities: - One session has many `trace_id` values. - One `trace_id` has one or more `turn_id` values. - One `turn_id` has one or more `model_call_id` values. - One `model_call_id` has one provider request and one provider response. - One `model_call_id` has many `model_items` rows: request, reasoning, response, tool_call, and tool_response items in observed order. - One `model_call_id` can emit many `tool_call_id` values. - One `tool_call_id` has one tool request and zero or more observed response rows, all with the same `tool_call_id`. - One `event_id` identifies one emitted row and joins its security, body, and display details. ### net_events -- one row per HTTP request through MITM proxy ```sql CREATE TABLE net_events ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL, -- RFC 3339 domain TEXT NOT NULL, -- "api.anthropic.com" port INTEGER DEFAULT 443, decision TEXT NOT NULL, -- "allowed" or "denied" process_name TEXT, -- "claude", "node", "python3" pid INTEGER, method TEXT, -- "POST", "GET" path TEXT, -- "/v1/messages" query TEXT, -- URL query string status_code INTEGER, -- 200, 403, etc. bytes_sent INTEGER DEFAULT 0, bytes_received INTEGER DEFAULT 0, duration_ms INTEGER DEFAULT 0, matched_rule TEXT, -- which policy rule matched request_headers TEXT, -- JSON (allowlisted verbatim, others hashed) response_headers TEXT, request_body_preview TEXT, -- compact display field only response_body_preview TEXT, conn_type TEXT DEFAULT 'https' ); ``` ### model_calls -- one row per AI API request+response cycle ```sql CREATE TABLE model_calls ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT NOT NULL, provider TEXT NOT NULL, -- "anthropic", "openai", "google" model TEXT, -- "claude-sonnet-4-20250514", "gpt-4o" process_name TEXT, pid INTEGER, method TEXT NOT NULL, -- "POST" path TEXT NOT NULL, -- "/v1/messages" stream INTEGER DEFAULT 0, -- 1 if SSE streaming system_prompt_preview TEXT, messages_count INTEGER DEFAULT 0, tools_count INTEGER DEFAULT 0, request_bytes INTEGER DEFAULT 0, request_body_preview TEXT, -- compact display field only message_id TEXT, -- "msg_..." (Anthropic), "chatcmpl-..." (OpenAI) status_code INTEGER, text_content TEXT, -- full response text thinking_content TEXT, -- thinking/reasoning text stop_reason TEXT, -- "end_turn", "tool_use", "stop", "STOP" input_tokens INTEGER, output_tokens INTEGER, duration_ms INTEGER DEFAULT 0, response_bytes INTEGER DEFAULT 0, estimated_cost_usd REAL DEFAULT 0, trace_id TEXT, -- groups tool call chains across turns usage_details TEXT -- JSON: {"cache_read": N, "thinking": N} ); ``` Only emitted for actual LLM API paths (`/v1/messages`, `/v1/chat/completions`, `/v1beta/models/*/`). Health checks, auth endpoints don't create rows. ### tool_calls -- canonical tool invocation ledger ```sql CREATE TABLE tool_calls ( id INTEGER PRIMARY KEY AUTOINCREMENT, event_id TEXT NOT NULL, -- 12 hex chars timestamp TEXT NOT NULL, model_call_id INTEGER, -- model_calls.id that emitted the tool call when model-visible provider TEXT NOT NULL, status TEXT NOT NULL, -- "requested", "observed", "responded", "error" call_index INTEGER NOT NULL, -- position in response call_id TEXT NOT NULL, -- "toolu_..." (Anthropic), "call_..." (OpenAI) tool_name TEXT NOT NULL, arguments TEXT, -- JSON string response_preview TEXT, origin TEXT NOT NULL DEFAULT 'native', -- "native", "mcp", "builtin", or "local" server_name TEXT, method TEXT, request_id TEXT, decision TEXT NOT NULL, duration_ms INTEGER DEFAULT 0, error_message TEXT, process_name TEXT, bytes_sent INTEGER DEFAULT 0, bytes_received INTEGER DEFAULT 0, policy_mode TEXT, policy_action TEXT, policy_rule TEXT, policy_reason TEXT, trace_id TEXT, credential_ref TEXT ); ``` For model-emitted tool calls, `model_call_id` points to the model exchange whose response emitted that tool call. It is not a trace-level guess. ### tool_responses -- results sent back for tool calls ```sql CREATE TABLE tool_responses ( id INTEGER PRIMARY KEY AUTOINCREMENT, model_call_id INTEGER NOT NULL, -- model_calls.id whose request consumed the tool result call_id TEXT NOT NULL, -- matches tool_calls.call_id content_preview TEXT, is_error INTEGER DEFAULT 0, trace_id TEXT, credential_ref TEXT ); ``` `tool_responses.model_call_id` points to the later model exchange that carried the tool result back to the model. The same `call_id` must match a `tool_calls.call_id` in the same trace. MCP initialize/list/resource protocol evidence is in the forensic payload of the matching `security_rule_events` row. That payload is archive-backed, not a column: read it with `DbHandle::read_body(event_id, "security_rule_events", BodyDirection::Payload)`, or join `event_body_blobs` on `source_table = 'security_rule_events'` and `direction = 'payload'` to see what is stored. Decisions and asks archive the same way under `security_decision_events` and `security_ask_events`, and all three name the same event, so a payload read always names its table. Use `tool_calls` for product/user/security tool activity.