freshie-inventory

What this skill does

# Freshie Inventory Manager

Interactive command center for the freshie ecosystem inventory database.

## Current DB Status

!`sqlite3 freshie/inventory.sqlite "SELECT 'Run #' || id || ' — ' || run_date || ' | Plugins: ' || total_plugins || ' | Skills: ' || total_skills || ' | Packs: ' || COALESCE(total_packs, 0) FROM discovery_runs ORDER BY id DESC LIMIT 3;" 2>/dev/null || echo "DB not found at freshie/inventory.sqlite"`

!`sqlite3 freshie/inventory.sqlite "SELECT grade || ': ' || COUNT(*) FROM skill_compliance GROUP BY grade ORDER BY grade;" 2>/dev/null`

## Overview

The freshie database is the single source of truth for ecosystem-wide metrics — plugin counts,
skill compliance grades, pack coverage, anomaly detection, and historical trends across versioned
discovery runs. This skill is an **interactive wizard** — it always asks what you want to do,
then delegates heavy operations to specialized subagents.

**Database location:** `freshie/inventory.sqlite` (50 tables, versioned by `run_id`)

**Key scripts:**

- `freshie/scripts/rebuild-inventory.py` — full repo scan, creates new discovery run
- `freshie/scripts/batch-remediate.py` — auto-fix compliance issues
- `scripts/validate-skills-schema.py` — enterprise validation with DB population

## Prerequisites

- `sqlite3` CLI available on PATH
- `python3` with `pyyaml` installed
- Working directory is the repo root (`claude-code-plugins/`)
- Database exists at `freshie/inventory.sqlite`
- `/email` skill installed (for PDF report emailing)

## Instructions

### Step 1: Present Main Menu

When invoked, ALWAYS start by presenting this menu using AskUserQuestion:

```
FRESHIE INVENTORY COMMAND CENTER
================================================================

What would you like to do?

 1. Dashboard        — Current status, grades, staleness
 2. Discovery Scan   — Full repo scan, create new run
 3. Compliance Check — Enterprise validation + DB population
 4. Remediation      — Batch fix compliance issues
 5. Query            — Ad-hoc SQLite queries
 6. Compare Runs     — Delta analysis between runs
 7. Export Data      — CSV exports to freshie/exports/
 8. Anomaly Scan     — Data quality + outlier detection
 9. Pack Coverage    — SaaS pack completeness metrics
10. Full Audit       — Scan + validate + report (end-to-end)
11. Report Only      — Generate summary from existing data
```

Use AskUserQuestion with these options. If the user's initial prompt already contains
a clear intent (e.g., "freshie status"), skip the menu and route directly.

### Step 2: Execute Chosen Workflow

Based on selection, follow the matching workflow below. Every workflow ends with
Step 3 (Email Report).

---

## Workflow A: Dashboard

Run these queries and present as a formatted dashboard:

```bash
sqlite3 freshie/inventory.sqlite "SELECT id, run_date, total_plugins, total_skills, COALESCE(total_packs,0) FROM discovery_runs ORDER BY id DESC LIMIT 1;"
sqlite3 freshie/inventory.sqlite "SELECT grade, COUNT(*) FROM skill_compliance WHERE run_id=(SELECT MAX(id) FROM discovery_runs) GROUP BY grade ORDER BY grade;"
sqlite3 freshie/inventory.sqlite "SELECT CAST(julianday('now') - julianday(run_date) AS INTEGER) FROM discovery_runs ORDER BY id DESC LIMIT 1;"
sqlite3 freshie/inventory.sqlite "SELECT 'plugins', COUNT(*) FROM plugins WHERE run_id=(SELECT MAX(id) FROM discovery_runs) UNION ALL SELECT 'skills', COUNT(*) FROM skills WHERE run_id=(SELECT MAX(id) FROM discovery_runs) UNION ALL SELECT 'packs', COUNT(*) FROM packs WHERE run_id=(SELECT MAX(id) FROM discovery_runs) UNION ALL SELECT 'anomalies', COUNT(*) FROM anomalies WHERE run_id=(SELECT MAX(id) FROM discovery_runs);"
# Core vs SaaS pack breakdown
sqlite3 freshie/inventory.sqlite "SELECT CASE WHEN path LIKE '%saas-packs%' THEN 'saas-pack-skills' ELSE 'core-skills' END as type, COUNT(*) FROM skills WHERE run_id=(SELECT MAX(id) FROM discovery_runs) GROUP BY type;"
```

Present as:

```
FRESHIE INVENTORY DASHBOARD
============================
Last Scan:     Run #{id} — {date} ({days} days ago)
Plugins:       {n}
Skills:        {n} total
  Core:        {n} (hand-crafted plugin skills)
  SaaS Packs:  {n} (auto-generated pack skills)
Packs:         {n}

Grade Distribution:
  A: {n}  B: {n}  C: {n}  D: {n}  F: {n}

Staleness: {Fresh (<3d) | Stale (3-7d) | CRITICAL (>7d)}
```

If Critical (>7 days), recommend a discovery scan.

---

## Workflow B: Discovery Scan

**Delegate to the discovery-scanner subagent** via the Agent tool:

```
Launch Agent: discovery-scanner
Prompt: "Run a full freshie discovery scan. Show current state first, execute
rebuild-inventory.py, then report the delta (plugin/skill count changes)
compared to the previous run."
```

The subagent handles the long-running scan in isolation and returns the delta report.

---

## Workflow C: Compliance Check

**Delegate to the compliance-validator subagent** via the Agent tool:

```
Launch Agent: compliance-validator
Prompt: "Run enterprise compliance validation against the freshie DB.
Execute: python3 scripts/validate-skills-schema.py --enterprise --populate-db freshie/inventory.sqlite --verbose
Then summarize: grade distribution with percentages, and list all D/F grade skills."
```

The subagent runs the full validation pipeline and returns a structured summary.

---

## Workflow D: Remediation

**CRITICAL: Always dry-run first, then confirm before executing.**

1. Run dry-run:

```bash
python3 freshie/scripts/batch-remediate.py --dry-run
```

1. Present the changes that would be made.

2. Use AskUserQuestion:

```
REMEDIATION PREVIEW
================================================================
{summary of proposed changes}

Proceed?
  - Execute — Apply all fixes
  - Cancel  — Abort, no changes made
```

1. Only if user selects "Execute":

```bash
python3 freshie/scripts/batch-remediate.py --all --execute
```

1. After execution, run Workflow C (Compliance Check) to measure improvement.

---

## Workflow E: Query

For ad-hoc queries, load the pre-built query library from [common-queries.md](references/common-queries.md).

Match the user's question to the closest pre-built query. If no match, construct a custom
query against the freshie schema using these key tables:

| Table | Contents |
|-------|----------|
| `plugins` | name, category, version, path |
| `skills` | name, plugin_path, has_references, has_scripts |
| `packs` | name, skill_count, category |
| `skill_compliance` | score, grade, error_count, warning_count, is_stub |
| `plugin_compliance` | plugin-level roll-up scores |
| `content_signals` | word_count, code_block_count |
| `anomalies` | detected data quality issues |
| `discovery_runs` | run history with timestamps |

Always filter to latest run: `WHERE run_id = (SELECT MAX(id) FROM discovery_runs)`

After showing results, use AskUserQuestion to offer follow-up:

```
Results shown. What next?
  - Refine query  — Modify or drill deeper
  - Export to CSV — Save results to file
  - Back to menu  — Return to main menu
```

---

## Workflow F: Compare Runs

```bash
sqlite3 freshie/inventory.sqlite "SELECT id, run_date, total_plugins, total_skills, COALESCE(total_packs,0) FROM discovery_runs ORDER BY id;"
```

If more than 2 runs exist, use AskUserQuestion to let user pick which two to compare.
Default to the two most recent.

Use the "Historical Trends" queries from [common-queries.md](references/common-queries.md) for:

- Grade distribution comparison between runs
- Skills that changed grade (upgrades/downgrades with score delta)
- New skills added since previous run
- Skills removed since previous run

---

## Workflow G: Export Data

```bash
mkdir -p freshie/exports
```

Use AskUserQuestion to let user pick what to export:

```
EXPORT OPTIONS
================================================================
What should I export?

  - Skill Grades    — All skill compliance scores + grades
  - Plugin Inventory — All plugins with category and version
  - Pack Coverage   — Pack names, skill counts, categori