# 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`: `raw`, whose shape is taken from `UNNEST(pinned_columns(...))` rather than restated, plus `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.

### 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` (via the `fields` query's `json_keys`/`json_type` inference) 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 rebuilt by `views.sql` from `raw` 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`. No temp file roundtrip. Session rewind/resume replays JSONL lines verbatim (same record uuid, usually later in the same file), so materialization dedupes twice: replayed source lines by `(host, session_id, uuid)` keeping the latest copy, then residual duplicate tool ids by `(host, type, id/tool_use_id)` (e.g. an Agent tool_use echoed into its subagent transcript under a fresh uuid). `attachments` and `hook_events` apply the same uuid-level dedupe. That second dedup pass is cross-file, which is why `content_items` stays a full rebuild rather than joining `raw`'s per-file incremental path: which copy of a duplicated tool id wins can depend on rows from a different file.

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.

`views.sql` (which also rebuilds the `content_items` table) runs after the host loop whenever `raw` changed, and additionally whenever its SHA-256 differs from `index_meta.views_hash`, 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, which holds the write lock for minutes on a large index. 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, 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` runs `CREATE OR REPLACE TABLE raw AS SELECT host, UNNEST(pinned_columns(data)), source_file, source_line, data FROM raw`, which adds columns the projection gained, drops ones it lost, and recomputes the rest in place. 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` table. It doubles as the stale marker for everything derived from `raw`: a refresh clears it before its first write and restores it after the rebuild, so a run that dies in between leaves a null the next run acts on. 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.