CLAUDE.md@plugins/observability/skills/session git:20261020.fdf4cddA

Immutable. This exact content never changes and is served at /api/v1/blob/1208213208bd268e.

# Session Skill

## DuckDB Architecture

JSONL files in `~/.claude/projects/` are the canonical data. The DuckDB database is a derived cache at `${XDG_DATA_HOME:-~/.local/share}/claude-sessions/`, outside `~/.claude` because the desktop app refuses to grant a cloud session any folder under it. `CLAUDE_SESSION_INDEX_DIR` overrides the path, and tests always set it. A dev run without the override writes the live index. The first read-write open at the default path moves an index left in the old plugin data dir (`~/.claude/plugins/data/observability-*`), and moves nothing when both exist. Other machines' corpora, copied under `~/.claude/session-imports/<label>/projects/`, are indexed under a `host` label alongside `local` (see SKILL.md "Cross-Machine History").

### Schema

`resources/schema/` runs on every startup:

- `00_pinned.sql`: `pinned_columns(line)`, the projection from one JSONL line to `raw`'s derived columns, returned as a STRUCT. It is the single source of truth for their names, order and types, so it loads before the tables that are built from it.
- `01_tables.sql`: `indexed_files(host, path, mtime, size)` as the per-file change catalog, `meta(host, last_import)` as display metadata for `hosts.ts`, and `index_meta(version, views_hash, import_hash)` holding the ingestion schema version and a fingerprint per derivation stage.
- `03_macros.sql`: filter macros for date ranges, project paths, and host, `project_id(host, path)`, and the `model_input_rate`/`model_output_rate` cost-rate table the usage queries share. Macros must live here (the schema load path) to persist in the DB file for the read-only query CLI.

`raw` is created after the schema files by `rawDdl` in `db.ts`, which takes its shape from `DESCRIBE` over `UNNEST(pinned_columns(...))` rather than restating it, and declares `data JSON USING COMPRESSION zstd`. DuckDB's own pick skips ZSTD for strings averaging under `zstd_min_string_length` (4096), and a transcript line averages about 3.7 KB, so `data` stayed uncompressed. Only `data` is forced: ZSTD on every string column cut the bench index to a third but slowed a third of the replayed queries by 24-97% at VM limits, and `data` alone halves it with latency flat. A `CREATE TABLE AS` cannot carry a column's compression, so nothing that rebuilds `raw` may use one.

### Host Enumeration

`db.ts` enumerates one entry per host: `local` (honoring `CLAUDE_PROJECTS_DIR` / the `projectsDir` option) plus one per `~/.claude/session-imports/*/manifest.json` (override the root with `CLAUDE_SESSION_IMPORTS_DIR` / the `importsDir` option). The filesystem is the registry; there is no host table. `ensureIndex` loops the hosts, scanning each host's root in TypeScript and importing per file. A host whose root directory is missing is skipped, never treated as all-files-deleted, so a typo'd `CLAUDE_PROJECTS_DIR` or unmounted dir cannot wipe a host's rows. Any other scan failure (e.g. an unreadable subdirectory) aborts the refresh, because a partial listing is indistinguishable from mass deletion.

### Tables

- **`raw`**: the only durable table. `data JSON` holds the full original JSONL line, and `host`, `source_file` and `source_line` identify where it came from; the columns between them (`session_id`, `type`, `project_path`, `git_branch`, `is_meta`, `is_sidechain`, `duration_ms`, `timestamp`, the token counts, `uuid`, `message_id`, `model`, `entrypoint`, attribution, `title`, `content_text`) are projections of `data` declared once in `00_pinned.sql`, so changing one never requires re-reading a file. Imports use `read_json_objects(...)` (no auto-detected types beyond `json` itself), so column types never drift between imports. Ingestion does **not** filter by record type: every line lands in `raw` (chat, attachments, system events, permission modes, snapshots, etc.). The blindness this avoids was real: a prior `WHERE type IN ('user','assistant','summary')` silently dropped the entire structured layer (hook executions, compactions, permission modes). Pinned numerics use `TRY_CAST` so a divergent value type degrades to NULL instead of failing the whole import.
- **`records`**: universal view over `raw`, one row per line, projecting a normalized `kind` label and the cross-cutting dimensions every record may carry, plus `data`. This is the discoverable union: lossless storage (`raw.data`) plus a labeled surface so no record type is invisible. Query-time extraction over `data` (the `fields` registry lists the paths) is preferred over pinning more typed columns, which would reintroduce drift risk against divergent fields.
- **`messages`**: view over `raw` filtered to `type IN ('user', 'assistant')`, adding `content_text` (when message content is a string) and the turn-entry fields `prompt_source` and `interrupted_message_id`. A session's display name comes from `session_labels`, a separate view over the `custom-title`/`ai-title`/`agent-name` records, which `sessions` joins.
- **`content_items`**: a table filled from `raw` by the `content_item_rows(scoped)` table macro in `views.sql`, via `unnest(json_extract(data, '$.message.content[*]'))`. Pinned columns plus the content item's full `data JSON` plus the parent's `tool_use_result JSON`, `role`, `model` and `is_meta`. A typed prompt, a string rather than a block array, becomes one `type = 'prompt'` row, so `text_content` reads `content_items` alone. Session rewind/resume replays JSONL lines verbatim (same record uuid, usually later in the same file), so the macro dedupes twice: replayed source lines by `(host, session_id, uuid)`, then residual duplicate tool ids by `(host, session_id, type, id/tool_use_id)` (e.g. an Agent tool_use echoed into its subagent transcript under a fresh uuid). Both passes keep the main-thread copy, then the latest. `attachments` and `hook_events` apply the uuid-level dedupe. Because both passes stay within a session, a session's rows depend only on that session's lines, which is what lets a refresh rebuild just the sessions it touched (see Import Pipeline).
- **`search_items`**: the lowercased prose the `search` query ranks, one row per typed prompt or main-thread assistant text block plus each session's label at weight 3, filled by `search_item_rows(scoped)` from `content_items` and `session_labels`. It is per session, so a refresh rebuilds it with `content_items`. BM25's document frequencies come from it at query time, so no stats table needs a refresh of its own.
- **`file_fields`**: the field registry, one row per `(host, source_file, type, path)` with `first_ts`, `last_ts`, `first_version`, `last_version` and a line count `n`, filled by `field_rows(scoped)` from the keys of `$`, of each top-level object, and of `$.message.usage`, without recursing further. The `fields` view sums it to `(host, type, path)`, which `field_drift`, `field-drift` and `index-health` read. Rows are per file because a removed or rewritten file must take its counts with it: a refresh deletes the file's rows and re-walks only that file, so `n`, `first_ts` and `last_ts` stay exact without a recompute from `raw`, and a path whose last file is gone drops out of `fields`, as the transcript index mirrors the disk.

Chat views (`tool_calls`, `tool_errors`, `permission_requests`, `sandbox_bypasses`, `skill_calls`, `sessions`, `file_operations`) read from `messages` / `content_items`, which carry attribution (`attribution_skill`/`plugin`/`agent`) and token columns merged from the parent message. Every one of them inherits `session_id` from the transcript, and a subagent's transcript carries its parent's, so a per-session count over these views credits a whole fan-out to the orchestrator. The `subagent_id` macro (see `03_macros.sql` for why the path beats `is_sidechain`) derives the real origin, and `tool_calls`, `tool_errors`, `file_operations`, `sandbox_bypasses`, `hook_denies`, and `hook_blocks` expose it as an `agent_id` column rather than filtering rows, so callers choose. `message_usage` dedupes assistant usage to one row per message id (`MAX` per token column), because every content-block row repeats the parent message's cumulative usage; token-summing queries read from it instead of summing `raw`/`messages` rows. Structured views (`attachments`, `system_events`, `stop_hook_runs`, `session_labels`, `hook_events`, `hook_blocks`, `diagnostics`, `pr_links`) read from `raw` directly, extracting defensively with `->>` and `TRY_CAST` so type divergence never errors. `stop_hook_runs` unnests `system_events.hook_infos`, the harness's roster of every Stop hook that fired, which is the only surface a silently-succeeding hook reaches. `diagnostics` double-unnests the `files[].diagnostics[]` arrays of `diagnostics` attachments. `hook_events` parses a PreToolUse permission decision out of the stdout JSON of a `hook_success` record (where `hookSpecificOutput.permissionDecision` lives) and unwraps `hook_blocking_error`'s `{"blockingError": ...}` payload.

### Import Pipeline

`ensureIndex` scans each host's root for `*.jsonl` in TypeScript (`scanJsonlFiles`) and diffs it against `indexed_files`: a file is changed when its path is new or its (mtime, size) differs, and a vanished path drops its rows. This per-file catalog replaced a per-host mtime watermark, which had two holes: a write landing between scan and stamp was skipped forever, and `rsync -a` delivering a new file with an old preserved mtime was never indexed.

For each changed file, `import.sql` runs once inside a transaction: delete the file's `raw` rows, re-read it with `read_json_objects` (all record types, no `WHERE` filter) projecting through `pinned_columns`, and upsert its `indexed_files` row. `source_line` comes from `ROW_NUMBER() OVER ()` on the single-file scan, which preserves file order in practice but is not formally guaranteed (documented caveat in SKILL.md "Source Lookup"). Dedup keys on file, not `session_id`, because subagent files carry the parent session's id and some record types carry none: a session-scoped delete would drop unrelated rows or leave duplicates.

The per-file delete-then-insert (instead of the previous whole-table `CREATE OR REPLACE ... UNION ALL` rewrite of `raw`) plus the `CHECKPOINT` at the end of every writing run is what bounds the file size: DuckDB only reuses freed blocks after a checkpoint and never returns space to the OS. `refresh.ts` adds a compaction guard on top: when the file exceeds 4x the corpus size (with a 64 MiB floor), it copies the database into a fresh file via `ATTACH` + `COPY FROM DATABASE` and atomically renames it into place.

`import.sql` and a removed file record their sessions, before and after the change, in `content_item_scope`, and the file itself in `field_scope`. After the host loop, `ensureIndex` replaces those sessions' `content_items` rows with `content_item_rows(true)` and those files' `file_fields` rows with `field_rows(true)`, which equal what a full rebuild yields for them. `views.sql`, which rebuilds every view and all of `content_items`, runs only when its SHA-256 differs from the `index_meta.views_hash` the run started with, or `raw` was re-derived, so editing a view definition takes effect on the next refresh even with no changed files. Cross-host joins key on `(host, session_id)`; the `content_items`/`messages` join keys on `(source_file, source_line)`, which is host-unique because imported files have distinct absolute paths.

Two local-only sources sit beside the projects directory and are read by `telemetry.ts` after the host loop: debug logs (`debug/*.txt`, parsed in TypeScript into `debug_events`) and mod events (`mod-events/<session>/*.jsonl`, written by the mod-events plugin, into `mod_events`). Their catalog is `telemetry_files`, keyed per file. Neither table derives from `raw`, so they never touch `views_hash`.

Debug logs and mod events carry a size cap (2 GB and 500 MB). After each sync, `prune` deletes the oldest files past the cap, choosing only among files whose catalog row matches the disk, so nothing is deleted before its rows land. Both sources keep their rows when a file disappears, so their tables archive what the disk no longer holds. The `classifier_verdicts` view in `views.sql` reads the auto-mode mod's `tool.verdict` events from `mod_events`.

### Refresh Entry Point

`refresh.ts` prints the DB path to stdout (everything else goes to stderr). A `last-refresh` stamp file makes runs within `--max-age` (default 300s) print the path and exit before opening the database, so the fast path never takes a lock. `--refresh` bypasses the stamp. `--compact` (run weekly by dotfiles) compacts the database by copying it into a fresh file at `STORAGE_VERSION`. A change to the `raw` projection rewrites `raw` inline on the next refresh, and a change to `views.sql` rebuilds `content_items` in full, each holding the write lock for minutes on a large index. Past the stamp, the work runs in a worker in its own process group, so an agent's Bash timeout, which kills the caller's group, leaves the rebuild to finish rather than handing it to the next caller. On a lock conflict it retries briefly, then defers to the concurrent refresher and exits 0 with the path.

Every exit appends a `session-index.refresh` event (`events.ts`) with its outcome, phase timings, and file counts (a worker killed by a signal cannot, so the caller records it as `killed`), plus `fields_new` and `fields_gone` when an incremental refresh added or dropped a `(host, type, path)` key, under `mod-events/<session>/` or `mod-events/cli/`. `index-health` reads them as `refresh-health`.

### Migration

Only `raw` is durable, and within it only `data` plus the `(host, source_file, source_line)` identity: everything else is a projection or a table built from one. Each derived stage carries its own fingerprint in `index_meta` and is brought up to date without re-reading a JSONL file.

- `import_hash` fingerprints `00_pinned.sql`. On a mismatch `reconcilePinned` re-derives every row into a fresh table from `rawDdl` (`INSERT INTO raw_next SELECT host, UNNEST(pinned_columns(data)), source_file, source_line, data FROM raw`) and swaps it in, which adds columns the projection gained, drops ones it lost, and recomputes the rest. With a matching hash, a `raw` whose `data` holds non-ZSTD segments on a `STORAGE_VERSION` file is rewritten the same way once, without invalidating anything derived. A null hash is an index that predates the check. Its rows are trusted only when `raw`'s live column list matches the projection's output exactly, in which case the fingerprint is recorded without a rewrite. Any drift (the projection changed after the index was built but before a stamping run) rewrites through the same path. Adopting one still rebuilds what is derived from `raw`, because the code that built it could leave a `content_items` behind a committed import and the index reads as current either way. The rewrite materializes a second copy of the table, and DuckDB never returns freed space, so it checkpoints and leaves `refresh.ts`'s compaction guard to shrink the file.
- `views_hash` fingerprints `views.sql`, which rebuilds the views and the `content_items`, `search_items` and `file_fields` tables, so a new derived table such as the field registry backfills from `raw` on the first refresh after the upgrade that adds it. It doubles as the stale marker for everything derived from `raw`: a refresh clears it before its first write and restores it after rebuilding the touched sessions, so a run that dies in between leaves a null that makes the next run rebuild in full. A `content_items` built from a superseded `raw` is indistinguishable from a current one by inspection, which is why the marker goes down first rather than being inferred afterward.
- `version` (`INDEX_VERSION`) is reserved for a change to the scan or to line identity, the one kind that genuinely needs the files back. `migrateIfNeeded` drops `content_items` and `ensureSchema` invalidates `indexed_files`' stats, so every file re-imports through `import.sql`'s per-file transaction instead of the index being emptied up front. Leaving the catalog's rows in place keeps the ordinary removed-file reap working, so the index stays a mirror of what is on disk.

The one case that still drops everything is a `raw` predating the `host`/`data` columns: its rows carry nothing the projection could be applied to.

The transcript index mirrors the disk rather than archiving it. Rows for a JSONL file deleted from disk are dropped on the next refresh, so `index-health`'s `indexed-not-on-disk` counts files awaiting that reap, not a surviving copy of anything.

### JSON Path Gotcha

DuckDB parses `data->>'$.x' = 'y'` as `data->>('$.x' = 'y')` because `=` binds tighter than `->>`. Wrap `data->>'$.path'` in parens before any comparison or function call where this matters. The views in `views.sql` apply this convention; downstream queries in `resources/queries/` should too.

### Concurrency

`getDb` opens read-write, which needs an exclusive DuckDB file lock: the writers (`refresh.ts` past its stamp, `import.ts`, `forget.ts`, `hosts.ts`) must not run concurrently with each other or with readers. Read-only opens take a shared lock: any number coexist, but none can open mid-refresh and a refresh cannot start while readers hold the file. Either collision fails with `Could not set lock`. The workflow fan-out pattern is therefore "refresh once, then have every agent query with `duckdb -readonly` over the shared file" (documented in SKILL.md "Parallel Queries"). This is why the agent-facing query path is the bare `duckdb` CLI rather than a wrapper: a read-only open at the stable path is all a caller needs, and a wrapper would force every parallel agent through it for no benefit. The plugin's own read-only scripts still share one, `query.ts` below, because they also share the binding and decoding an ad-hoc query does not.

Every open this skill makes or documents is resource-capped, because DuckDB's defaults (80% of RAM, every core, spill up to 90% of free disk) apply per process and a fan-out of readers multiplies them past the machine. Readers load `resources/limits.sql` through `-init` (60% of RAM up to 4 GiB, 4 threads, a per-process spill dir under `$TMPDIR`), and queries that need extensions add `-cmd ".read extensions.sql"`. The writer passes `WRITER_CONFIG` to `DuckDBInstance.create`. A query that outgrows its cap spills or fails with an out-of-memory error instead of swapping the machine.

The four writers carry the `claude:dangerouslyDisableSandbox` marker after their shebang. Each writes somewhere the sandbox denies: `refresh.ts` prunes `~/.claude/debug` and `~/.claude/mod-events`, `import.ts` and `forget.ts` change `~/.claude/session-imports`, `hosts.ts` opens the index read-write, and the first open moves the old index out of `~/.claude/plugins`. See [`plugins/mac/README.md`](../../../mac/README.md) for the marker.

### Callers

- `db.ts`: orchestrates migration, schema, scan, per-file import, view rebuild and versioning, checkpoint, compaction.
- `refresh.ts`: CLI entry point. Stamp fast-path, `ensureIndex`, `--compact`, compaction guard, prints DB path to stdout.
- `query.ts`: the one path from a named query (or literal SQL) plus params to decoded rows. Two adapters share the variable binding: `nodeAdapter` binds on the read-write connection `db.ts` already holds, and `cliAdapter` spawns `duckdb -readonly -init limits.sql` with a timeout, rendering the same params as `SET VARIABLE` lines through one escaping function. `usage.ts` and `schema.ts` take the read-only adapter so they never wait on a refresh's write lock. `runQuery` appends a `session-index.query` event per read. Every scripted reader goes through `cliAdapter`.

### Development

Set `CLAUDE_SESSION_INDEX_DIR` to a temp dir for any dev invocation that should not touch the live index. `bun test` covers this via per-test `mkdtemp` dirs.