Skill Analytics: Usage Mining, Context-Tax ROI & SQL Telemetry Engine #

The skill-analytics skill mines local agent conversation transcripts (with 0% sampling and automatic transcript.jsonl + transcript_full.jsonl truncation merging) alongside your installed skill inventory across all tiers (builtin, plugin, global, workspace, and catalog) into a local SQLite warehouse (skill_analytics.db). Pair it with skill-optimizer to prune zero-use “shelfware” skills, rewrite under-performing frontmatter descriptions, and reconcile ~/.config/kungfu/state.json telemetry.

Available scripts #

scripts/analyze.py: Zero-dependency Python 3 CLI for incremental transcript ingestion, full historical rebuilds, pre-packaged ROI/trigger/drift reports, ad-hoc SQL queries, and deterministic state.json backfills.
scripts/test_analyze.py: Automated unit and regression test suite validating schema DDL, views, parser accuracy, and markdown SQL code blocks.

⚡ Quick Start & Primary Actions #

All operations are driven via scripts/analyze.py (runnable via kungfu run skill-analytics analyze.py or uv run scripts/analyze.py):

bash

# 1. Incremental Sync (Only parses new or modified conversation transcripts)
uv run scripts/analyze.py sync --db skill_analytics.db

# 2. Full Historical Rebuild (Drops and re-mines all conversations from scratch)
uv run scripts/analyze.py sync --full --db skill_analytics.db

# 3. Run Pre-Packaged Analytical Reports
uv run scripts/analyze.py report overview --db skill_analytics.db
uv run scripts/analyze.py report roi --db skill_analytics.db
uv run scripts/analyze.py report triggers --db skill_analytics.db
uv run scripts/analyze.py report velocity --db skill_analytics.db
uv run scripts/analyze.py report gaps --db skill_analytics.db
uv run scripts/analyze.py report drift --db skill_analytics.db

# 4. Reconcile & Backfill ~/.config/kungfu/state.json (Dry-Run vs. Apply)
uv run scripts/analyze.py backfill-state --dry-run --db skill_analytics.db
uv run scripts/analyze.py backfill-state --apply --db skill_analytics.db

# 5. Run Ad-Hoc SQL Query
uv run scripts/analyze.py query "SELECT skill_name, source_tier, tier1_tokens_est, total_activations, roi_recommendation FROM v_skill_roi LIMIT 15" --db skill_analytics.db

If --db is omitted, the script defaults to skill_analytics.db in the current working directory.

🗄️ Database Schema & Relational Structure #

The database maintains 5 relational tables and 7 analytical views. Full column-level DDL and constraints are documented in references/schema.md.

Tables #

skills_inventory: Every discovered skill across builtin, plugin, global, workspace, and catalog tiers, including is_always_in_prompt, tier1_tokens_est (name + description routing cost), and tier2_tokens_est (SKILL.md body cost).
conversations: Metadata across the union of conversation_summaries.db and brain/ transcript directories (total_steps, user_input_steps, planner_steps, first_step_at, last_step_at).
ingestion_state: File size, mtime_ns, and SHA-256 head watermarks per conversation for sub-second incremental updates.
skill_events: Every extracted skill event across all sources:
- direct_skill_view: Direct view_file activations on any */<skill>/SKILL.md.
- skill_resource_view: Companion references/, examples/, assets/, or scripts/ reads.
- cli_invocation: kungfu CLI commands (load, learn, run, find, show, list, status, update, forget, catalog) and direct script executions (script_exec).
- user_notation: User prompt shortcuts (modern_::, classic_//, find_:?).
- Includes trigger_mode (autonomous, user_explicit, rule_driven) and is_self_test (0 for production, 1 for test/authoring).
state_telemetry_snapshot: Snapshot of ~/.config/kungfu/state.json history (recorded_count, page_load_count, subpath_load_count, run_count, last_loaded_at).

📊 Analytical SQL Views #

View Name	Description	Key Columns
`v_skill_roi`	Context-Tax vs. Activation ROI across all skill tiers	`skill_name`, `source_tier`, `is_always_in_prompt`, `tier1_tokens_est`, `total_activations`, `autonomous_activations`, `explicit_activations`, `resource_reads`, `script_runs`, `roi_recommendation`
`v_trigger_attribution`	Autonomous description matching vs. manual user overrides	`skill_name`, `source_tier`, `total_activations`, `autonomous_activations`, `explicit_activations`, `autonomous_share_pct`, `trigger_health`
`v_daily_invocations`	Daily time-series across event categories and actions	`day`, `invocation_kind`, `action`, `prod_count`, `test_count`, `distinct_conversations`
`v_subcommand_summary`	Aggregate counts and date bounds per command/notation	`invocation_kind`, `action`, `total_events`, `prod_events`, `self_test_events`, `conversations`
`v_search_gaps`	Search queries ranked by whether they led to a skill load	`search_query`, `invocation_kind`, `search_count`, `led_to_load_count`, `last_searched_at`
`v_skill_telemetry_drift`	Side-by-side audit of `state.json` vs. transcript ground truth	`skill_name`, `state_json_count`, `transcript_cli_load_primary`, `transcript_total_activations`, `drift_total_vs_state`, `drift_diagnosis`

🔍 Pairing with `skill-optimizer`: Core SQL Workflows #

Tested SQL recipes for skill portfolio optimization are in references/queries.md.

1. Detect Shelfware Skills Consuming System-Prompt Tokens #

Find skills with is_always_in_prompt = 1 that add token overhead on every turn but have 0 activations:

sql

SELECT
    skill_name,
    source_tier,
    source_origin,
    tier1_tokens_est,
    cumulative_prompt_tax_tokens,
    roi_recommendation
FROM v_skill_roi
WHERE is_always_in_prompt = 1
  AND total_activations = 0
  AND script_runs = 0
ORDER BY tier1_tokens_est DESC;

2. Find Skills Whose Descriptions Fail to Trigger Autonomously #

Identify skills that are only activated when the user manually types ::skill or //skill (NEEDS_DESCRIPTION_TUNING or WEAK_AUTONOMOUS_TRIGGER), then feed them to skill-optimizer:

sql

SELECT
    skill_name,
    source_tier,
    total_activations,
    autonomous_activations,
    explicit_activations,
    autonomous_share_pct,
    trigger_health
FROM v_trigger_attribution
WHERE trigger_health IN ('NEEDS_DESCRIPTION_TUNING', 'WEAK_AUTONOMOUS_TRIGGER')
ORDER BY explicit_activations DESC;

📚 Progressive Disclosure & References #

Full DDL Schema Reference: references/schema.md — Complete SQLite table definitions, indexes, and analytical view logic.
SQL Query Cookbook: references/queries.md — Ready-to-run queries for Context-Tax ROI, Trigger Attribution, Progressive Disclosure depth, Catalog Gap discovery, and KungFu state.json drift.