# Skill Analytics SQLite Schema Reference

The Skill Analytics database (`skill_analytics.db`) is provisioned and maintained automatically by `scripts/analyze.py`. All tables, indexes, and analytical views are created during `sync` operations.

---

## 1. Relational Tables

### `skills_inventory`
Stores every discovered skill across built-in, plugin, global, workspace, and catalog tiers along with its Tier-1 routing token footprint (`name + description`) and Tier-2 body metrics.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `skill_name` | `TEXT` | PRIMARY KEY | Canonical kebab-case skill identifier |
| `source_tier` | `TEXT` | NOT NULL | Installation tier (`builtin`, `plugin`, `global`, `workspace`, `catalog`) |
| `source_origin` | `TEXT` | NOT NULL | Plugin name, catalog repository, or scope origin |
| `category` | `TEXT` | | Skill category (`agents`, `analytics`, `coding`, `game-dev`, `media`, `writing`) |
| `version` | `TEXT` | | Semantic version from frontmatter (`0.5.0`, `unversioned`) |
| `description` | `TEXT` | | Full Tier-1 routing description string |
| `skill_path` | `TEXT` | | Local path or canonical remote URL |
| `is_always_in_prompt` | `INTEGER` | NOT NULL, DEFAULT 0 | `1` if injected into the system prompt `<skills>` block on every turn; `0` if JIT-only |
| `tier1_chars` | `INTEGER` | NOT NULL, DEFAULT 0 | Character count of `name + description` |
| `tier1_tokens_est` | `INTEGER` | NOT NULL, DEFAULT 0 | Estimated Tier-1 routing token tax per turn |
| `tier2_lines` | `INTEGER` | NOT NULL, DEFAULT 0 | Line count of `SKILL.md` body |
| `tier2_tokens_est` | `INTEGER` | NOT NULL, DEFAULT 0 | Estimated Tier-2 instruction token count when loaded |
| `synced_at` | `TEXT` | NOT NULL | ISO-8601 UTC timestamp of inventory scan |

---

### `conversations`
Stores conversation-level metadata across the union of `conversation_summaries.db` and `brain/` transcript directories.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `conversation_id` | `TEXT` | PRIMARY KEY | UUID of the agent conversation |
| `title` | `TEXT` | | Conversation summary title |
| `workspace_uris` | `TEXT` | | Active workspace URI(s) |
| `git_origin_urls` | `TEXT` | | Parent conversation ID or git origin metadata |
| `created_at` | `TEXT` | | ISO-8601 creation timestamp |
| `updated_at` | `TEXT` | | ISO-8601 last modified timestamp |
| `first_step_at` | `TEXT` | | Timestamp of earliest step in transcript |
| `last_step_at` | `TEXT` | | Timestamp of latest step in transcript |
| `total_steps` | `INTEGER` | DEFAULT 0 | Total steps parsed in transcript |
| `user_input_steps` | `INTEGER` | DEFAULT 0 | Count of `USER_INPUT` steps |
| `planner_steps` | `INTEGER` | DEFAULT 0 | Count of `PLANNER_RESPONSE` steps |
| `has_transcript` | `INTEGER` | DEFAULT 0 | `1` if `transcript.jsonl` or `transcript_full.jsonl` exists on disk |
| `has_summary_db` | `INTEGER` | DEFAULT 0 | `1` if indexed in `conversation_summaries.db` |
| `ingested_at` | `TEXT` | NOT NULL | ISO-8601 UTC ingestion timestamp |

---

### `ingestion_state`
Tracks file size, modification timestamp (`mtime_ns`), and SHA-256 head watermarks per conversation for fast incremental syncing.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `conversation_id` | `TEXT` | PRIMARY KEY | Conversation UUID |
| `transcript_path` | `TEXT` | NOT NULL | Path to transcript file |
| `file_size_bytes` | `INTEGER` | NOT NULL | Combined byte size of compact and full transcripts |
| `file_mtime_ns` | `INTEGER` | NOT NULL | Latest modification time in nanoseconds |
| `last_step_index` | `INTEGER` | NOT NULL | Highest `step_index` ingested |
| `sha256_head` | `TEXT` | NOT NULL | SHA-256 digest of leading 64 KB |
| `updated_at` | `TEXT` | NOT NULL | ISO-8601 UTC watermark update time |

---

### `skill_events`
Stores every extracted skill activation, companion resource view, CLI subcommand execution, script run, and user prompt notation (`::skill`, `//skill`, `:?`, `/skill`).

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `id` | `INTEGER` | PRIMARY KEY AUTOINCREMENT | Unique event ID |
| `conversation_id` | `TEXT` | NOT NULL | Conversation UUID |
| `step_index` | `INTEGER` | NOT NULL | Step index within the transcript |
| `step_created_at` | `TEXT` | | ISO-8601 UTC step timestamp |
| `step_type` | `TEXT` | NOT NULL | `USER_INPUT` or `PLANNER_RESPONSE` |
| `invocation_kind` | `TEXT` | NOT NULL | `direct_skill_view`, `skill_resource_view`, `cli_invocation`, or `user_notation` |
| `notation_type` | `TEXT` | | For `user_notation`: `modern_::`, `classic_//`, `find_:?` |
| `subcommand` | `TEXT` | | Subcommand or action (`load`, `find`, `learn`, `run`, `script_exec`, `show`, `list`, `status`, `update`, `forget`, `catalog`, `view_file`) |
| `target_skill` | `TEXT` | | Target skill name (or comma-joined list for rejected multi-skill batches) |
| `raw_args` | `TEXT` | | Parsed CLI arguments or file path |
| `subpath_or_script` | `TEXT` | | Loaded subpath (`references/...`), script name, `--load`, or search query |
| `page_number` | `INTEGER` | DEFAULT 1 | Page number for paginated `kungfu load -p N` calls |
| `trigger_mode` | `TEXT` | DEFAULT `'autonomous'` | `autonomous` (frontmatter description match), `user_explicit` (`::`, `//`, `/`), or `rule_driven` |
| `source_tier` | `TEXT` | | `builtin`, `plugin`, `global`, `workspace`, `kungfu_cli`, `direct_script`, `user_prompt`, `repo_authoring` |
| `raw_snippet` | `TEXT` | NOT NULL | Command line or prompt excerpt |
| `is_self_test` | `INTEGER` | DEFAULT 0 | `1` if development self-test or repo authoring inspection; `0` for production usage |

---

### `state_telemetry_snapshot`
Stores a snapshot of `~/.config/kungfu/state.json` `history` counters for drift comparison and deterministic backfilling.

| Column | Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `skill_name` | `TEXT` | PRIMARY KEY | Skill name in `state.json` |
| `recorded_count` | `INTEGER` | NOT NULL | `load_count` in `state.json` |
| `page_load_count` | `INTEGER` | NOT NULL, DEFAULT 0 | Continuation page loads (`-p 2+`) |
| `subpath_load_count` | `INTEGER` | NOT NULL, DEFAULT 0 | Companion subpath loads (`skill/references/...`) |
| `run_count` | `INTEGER` | NOT NULL, DEFAULT 0 | Bundled script executions (`kungfu run`) |
| `last_loaded_at` | `TEXT` | NOT NULL | `last_seen_at` ISO-8601 timestamp |
| `is_learned` | `INTEGER` | NOT NULL, DEFAULT 0 | `1` if currently installed in global or workspace scope |
| `snapshot_at` | `TEXT` | NOT NULL | ISO-8601 UTC snapshot timestamp |

---

## 2. Analytical SQL Views

| View Name | Description |
| :--- | :--- |
| `v_skill_roi` | Joins `skills_inventory` and `skill_events` to compute Context-Tax vs. Activation ROI (`SHELFWARE_CANDIDATE`, `LOW_ROI_GLOBAL`, `PROMOTE_TO_GLOBAL`, `ACTIVE_GLOBAL`, `HEALTHY_JIT`). |
| `v_trigger_attribution` | Breaks down activations per skill by `autonomous_activations`, `explicit_activations`, `rule_activations`, `autonomous_share_pct`, and `trigger_health` (`NEEDS_DESCRIPTION_TUNING`, `WEAK_AUTONOMOUS_TRIGGER`, `HEALTHY_TRIGGER`). |
| `v_daily_invocations` | Daily time-series of events grouped by `day`, `invocation_kind`, and `action`. |
| `v_subcommand_summary` | Aggregate counts, production vs. self-test split, conversation reach, and first/last seen timestamps by `invocation_kind` and `action`. |
| `v_search_gaps` | Ranks `kungfu find` and `:?` search queries by whether they led to a subsequent skill load (`led_to_load_count`). |
| `v_skill_telemetry_drift` | Compares `state.json` `load_count` against mined CLI primary loads, `learn --load`, and direct `SKILL.md` views with diagnostic classifications. |
| `kungfu_invocations` | Compatibility projection over `skill_events`. |