Feature: Persist mapping fire usage data for LLM-powered usage analytics

[amiable-dev]

Summary

Persist mapping fire counts to SQLite so the LLM can analyze usage patterns and provide insights like: "Your 'Copy' shortcut fires 50×/day but 'Paste' only 2×", "Mode 'Transport' hasn't been used in 2 weeks", "Your usage peaks between 2-4pm", or "These 5 mappings have never fired — consider removing or remapping them."

Currently, fire counts live in a frontend in-memory store (mappingFireCount writable in events.js) that resets on page reload. No historical usage data survives across sessions.

Architecture Decision: Daemon-Side Persistence

Persist from the daemon, not the frontend. Rationale:

Approach	Pros	Cons
Frontend flush (periodic invoke)	Simple to implement	Lost on crash, GUI-dependent, flush timing gaps
Daemon-side capture	Works without GUI, crash-resilient, captures all usage, MCP tools have direct DB access	Slightly more complex initial setup

The daemon already:

• Processes every fired event in engine_manager.rs:3567-3626
• Has SQLite infrastructure (audit.db with WAL mode, retention policies)
• Hosts MCP tools that can query the data directly

Proposed Schema

Time-Bucketed Usage Table

Hourly buckets balance granularity with storage efficiency. One row per mapping per hour, with an atomic upsert (INSERT...ON CONFLICT UPDATE).

CREATE TABLE mapping_usage (
    mapping_key TEXT NOT NULL,        -- fireStateKey: "ModeName:Label" or "ModeName:type:device:num"
    mode_name TEXT NOT NULL,
    mapping_label TEXT,               -- human-readable description (nullable)
    trigger_type TEXT NOT NULL,       -- "note", "cc", "encoder", "gamepad_button", etc.
    trigger_number INTEGER,           -- note/CC/button number (nullable for pitchbend/aftertouch)
    device_id TEXT,                   -- device alias or port name (nullable)
    action_type TEXT NOT NULL,        -- "keystroke", "volume_control", "launch", etc.
    action_summary TEXT,              -- human-readable: "Cmd+C", "Launch Spotify"
    fire_count INTEGER NOT NULL DEFAULT 0,
    error_count INTEGER NOT NULL DEFAULT 0,
    total_latency_us INTEGER NOT NULL DEFAULT 0,  -- sum of latency_us for avg calculation
    bucket_hour TEXT NOT NULL,        -- ISO 8601 hour: "2026-03-12T14:00:00Z"
    updated_at INTEGER NOT NULL,      -- epoch ms of last update
    PRIMARY KEY (mapping_key, bucket_hour)
);

CREATE INDEX idx_usage_mode ON mapping_usage(mode_name);
CREATE INDEX idx_usage_bucket ON mapping_usage(bucket_hour);
CREATE INDEX idx_usage_trigger ON mapping_usage(trigger_type, trigger_number);

Upsert on Fire

INSERT INTO mapping_usage (
    mapping_key, mode_name, mapping_label, trigger_type, trigger_number,
    device_id, action_type, action_summary, fire_count, error_count,
    total_latency_us, bucket_hour, updated_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 1, ?, ?, ?, ?)
ON CONFLICT(mapping_key, bucket_hour) DO UPDATE SET
    fire_count = fire_count + 1,
    error_count = error_count + excluded.error_count,
    total_latency_us = total_latency_us + excluded.total_latency_us,
    updated_at = excluded.updated_at;

Storage Estimate

• ~200 bytes per row
• 10 mappings × 24 hours × 30 days = 7,200 rows ≈ 1.4 MB
• 100 mappings × 24 × 365 = 876,000 rows ≈ 175 MB (extreme case)
• 90-day retention default, configurable — keeps DB small

Implementation Plan

Phase 1: Daemon Persistence

Files to modify:

1. conductor-daemon/src/daemon/audit/ — Add usage.rs module (or new usage/ directory)

   • UsageTracker struct with Arc<Mutex<Connection>> (follows AuditLogger pattern)
   • UsageTracker::new(db_path) — creates table, enables WAL
   • UsageTracker::record_fire(payload: &MappingFiredPayload) — upsert into current hour bucket
   • UsageTracker::query(UsageQuery) — flexible querying (by mode, time range, trigger type)
   • UsageTracker::prune(max_age_days) — retention cleanup
   • UsageTracker::get_summary() — aggregate stats for MCP tool

2. conductor-daemon/src/daemon/engine_manager.rs — Hook persistence at fire point

   • Add usage_tracker: Option<UsageTracker> field
   • After line ~3620 (where MappingFiredPayload is constructed), call usage_tracker.record_fire(&payload)
   • Use spawn_blocking or batched writes to avoid blocking the hot path

3. Write strategy: In-memory batch + periodic flush

   • Accumulate fires in a HashMap<(String, String), UsageDelta> (mapping_key × bucket_hour)
   • Flush to SQLite every 30 seconds OR when batch reaches 100 entries
   • Flush on graceful shutdown (DaemonService::stop())
   • This avoids per-event SQLite writes on the hot path

Phase 2: MCP Tool for LLM Access

New tool: conductor_get_mapping_usage (ReadOnly tier)

{
  "name": "conductor_get_mapping_usage",
  "description": "Get mapping usage statistics. Shows how frequently each mapping fires, most/least used mappings, usage by time of day, and unused mappings.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "mode": { "type": "string", "description": "Filter by mode name (optional)" },
      "period": { "type": "string", "enum": ["today", "7d", "30d", "90d", "all"], "default": "7d" },
      "sort": { "type": "string", "enum": ["most_used", "least_used", "recent"], "default": "most_used" },
      "limit": { "type": "integer", "default": 20 },
      "include_hourly": { "type": "boolean", "default": false, "description": "Include hourly breakdown" }
    }
  }
}

Response format:

{
  "period": "7d",
  "total_fires": 1234,
  "unique_mappings_fired": 15,
  "total_mappings_configured": 22,
  "unused_mappings": ["Mode:Label1", "Mode:Label2"],
  "top_mappings": [
    {
      "mapping": "Mix:Copy shortcut",
      "mode": "Mix",
      "trigger": "Note 36",
      "action": "Cmd+C",
      "fires": 312,
      "errors": 0,
      "avg_latency_ms": 0.4,
      "last_fired": "2026-03-12T14:23:00Z"
    }
  ],
  "by_mode": {
    "Mix": { "fires": 800, "mapping_count": 10 },
    "Edit": { "fires": 400, "mapping_count": 8 },
    "Transport": { "fires": 34, "mapping_count": 4 }
  },
  "by_hour": {
    "09": 45, "10": 120, "11": 98, "14": 200, "15": 180
  }
}

Phase 3: System Prompt + LLM Integration

• Update system prompt to mention conductor_get_mapping_usage tool availability
• Add guidance: "When asked about usage, mapping optimization, or workflow analysis, use conductor_get_mapping_usage to provide data-driven insights"
• LLM can proactively suggest: "I notice your 'Transport' mode has only 34 fires this week vs 800 for 'Mix' — would you like to consolidate?"

Phase 4: GUI Usage View (Optional)

• New workspace view or Settings section showing usage heatmap
• Per-mapping sparklines or bar charts
• "Reset usage data" button
• This is lower priority — the LLM analysis is the primary consumer

Privacy & User Control

• All data is local-only (SQLite on disk, no telemetry)
• Add [usage_tracking] config section:

  [usage_tracking]
  enabled = true              # Master toggle
  retention_days = 90         # Auto-prune older data
  flush_interval_secs = 30    # Batch write frequency

• conductorctl clear-usage CLI command to wipe all usage data
• MCP tool conductor_clear_mapping_usage (ConfigChange tier) for LLM access

Database Location

Use ~/.local/share/conductor/conductor_usage.db (separate from audit.db):

• Separate lifecycle and retention from audit logs
• Can be deleted independently without affecting audit trail
• Follows existing pattern (audit.db, conductor_conversations.db are separate)

Performance Considerations

• Hot path impact: Zero — batched writes with in-memory accumulation
• Flush cost: Single SQLite transaction with N upserts every 30s (sub-millisecond for typical loads)
• Memory: HashMap of active-hour buckets ≈ 100-500 bytes per active mapping
• Disk: ~1-2 MB/month for moderate use (10 active mappings)
• Startup: No loading of historical data into memory (frontend session counter stays independent)

Relationship to Frontend Fire Count

The existing in-memory mappingFireCount in events.js stays as-is for session-scoped UI display (the "×N" badge). The daemon-side persistence is a separate, long-term record. They serve different purposes:

	Frontend mappingFireCount	Daemon mapping_usage DB
Purpose	Session UI feedback (×N badge)	Long-term analytics
Lifetime	Page reload	Configurable retention (90 days default)
Consumer	MappingRow component	MCP tool / LLM / future GUI view
Granularity	Raw count per key	Hourly bucketed with metadata

Priority

P3 — Enhancement. High value for LLM-powered insights, but not blocking any current functionality.

Acceptance Criteria

• UsageTracker module with SQLite persistence (upsert into hourly buckets)
• Batched writes (30s flush interval) — no per-event SQLite writes on hot path
• Flush on graceful daemon shutdown
• conductor_get_mapping_usage MCP tool (ReadOnly tier) with period/mode/sort filters
• Response includes: top mappings, unused mappings, by-mode breakdown, by-hour breakdown
• 90-day default retention with configurable retention_days
• [usage_tracking] config section with enabled toggle
• System prompt updated with usage analysis guidance
• conductorctl clear-usage CLI command
• Test: fire 10 events → flush → query returns correct counts
• Test: hourly bucket boundaries roll correctly
• Test: retention prune removes data older than configured days
• Test: MCP tool returns correct aggregations for period filters
• Privacy: no external data transmission, local SQLite only

---

Pro Version & Hosted Platform Considerations

Label: pro-version — This feature is planned for Conductor Pro (paid tier).

Local vs Hosted Tiers

Capability	Free (Local)	Pro (Hosted)
Fire count persistence	Local SQLite	Synced to cloud
LLM usage analysis	Local LLM only	Cloud LLM with cross-device aggregation
Usage history retention	90 days local	Unlimited cloud archive
Usage dashboard	Basic CLI/MCP	Web dashboard with visualizations
Cross-device analytics	N/A	Aggregate usage across all devices
Team/org insights	N/A	Team usage patterns, shared mapping recommendations
Export	Local JSON/CSV	API access, webhook integrations

Relationship to #5 (Crowdsourced Pattern Platform)

Issue #5 defines the cloud infrastructure (conductor-cloud with Axum + PostgreSQL) and Pro tier ($9/month) with creator analytics. Mapping usage data is a natural extension:

• Phase 1 (this issue): Local persistence + MCP tool (free tier, no cloud dependency)
• Phase 2 (Pro): Opt-in cloud sync via conductor-cloud-sync (🚀 Crowdsourced Pattern Platform: Full Stack Implementation #5 Phase 3)
• Phase 3 (Pro): Hosted analytics dashboard, cross-device aggregation, team insights

Data Pipeline for Hosted Platform

Local SQLite (mapping_usage.db)
  → opt-in sync daemon (conductor-cloud-sync)
    → cloud API (conductor-cloud)
      → TimescaleDB (time-series optimized)
        → Web dashboard + API

Privacy Model

• Free tier: All data stays local. No telemetry. No opt-in required.
• Pro tier: Explicit opt-in per device. Data encrypted in transit (TLS) and at rest. GDPR-compliant deletion on request. User controls what data is shared (usage only, no mapping content).
• Schema compatibility: Local SQLite schema should be forward-compatible with cloud PostgreSQL/TimescaleDB schema to simplify sync.

[amiable-dev]

Update: Usage tracking toggle location

Per #559 (State Persistence Boundaries), the [usage_tracking] config section proposed in this issue should live in daemon.toml (daemon operational preferences), not config.toml (mapping behavior).

Rationale: Usage tracking controls how the daemon process operates (whether to persist fire statistics), not what happens when you press a button. config.toml stays reserved for mapping intent.

# daemon.toml (NOT config.toml)
[usage_tracking]
enabled = true
retention_days = 90
flush_interval_secs = 30

The daemon.toml schema and file location will be defined in #559.

[amiable-dev]

Note: This issue was listed under Epic #559 (ADR-017 Settings Persistence) but is a separate feature (SQLite usage analytics persistence). It is not part of ADR-017 scope — ADR-017 covers the two TOML settings files (preferences.toml, daemon.toml) and their GUI wiring.

Removing from Epic #559 scope. This remains open as an independent feature request.