[EPIC] ClawMetry Dives — AI SQL→chart over local DuckDB

[vivekchand]

TL;DR

Plain-English question → SQL → chart, all against the local DuckDB store. No SaaS dep, no row values leave the machine.

Full design + trade-off analysis in PRD-clawmetry-dives.md (private repo).

Why now

• PR feat(local-store): multi-agent schema — sessions/memory/heartbeats (PR 1 of 3) #995 / feat(sync): daemon write-through for sessions/memory/heartbeat (PR 2 of 3) #997 / feat(sessions): /api/sessions opt-in local-store fast path (PR 3 of 3) #998 just landed: local DuckDB has sessions, memory_blobs, heartbeats, system_snapshots, crons, subagents tables — all multi-agent keyed by agent_type.
• Aisha chat widget + /claude-api already routes prompts through user's BYO key.
• Chart.js is already loaded for the Tokens tab.

What's missing is the glue: schema-aware prompting + SQL safety validator + chart-spec rendering.

Architecture (high-level)

1. User types in /dives tab input box
2. Backend prompt: schema descriptor (table names, column types, no row values) + question → LLM returns {sql, chart_type, x, y, title}
3. SQL safety validator rejects anything that's not a SELECT against the allowlisted 9 tables
4. Run query against local DuckDB
5. Render with Chart.js
6. Optional save → JSON spec at ~/.clawmetry/dives/<slug>.json

Privacy promise

On each Dive run, the LLM call sends:

• The user's natural-language question
• A schema descriptor (table/column names, types)
• NEVER row values

For users wanting zero LLM contact, the existing Ollama integration is the path.

Sub-issues

• #DIVES-1 — SQL safety validator (allowlist, regex rejection, 30+ malicious-input tests)
• #DIVES-2 — Prompt template + schema descriptor builder
• #DIVES-3 — /api/dives/* endpoints (query, save, list, get, delete)
• #DIVES-4 — /dives tab UI (input box, chart area, saved Dives list, "show SQL" toggle)
• #DIVES-5 — Suggested-questions gallery (regression-tested fixed list)
• #DIVES-6 — Telemetry: log dive_run events into the local store for prompt improvement

Out of scope (Phase 2+)

• Evidence.dev /reports sidecar (Phase 2 — power users authoring markdown+SQL by hand)
• Sharing Dives across team via cloud (Phase 3)
• Scheduled Dive re-runs + threshold notifications (Phase 4)
• Self-Evolve feedback loop (Phase 5)

Effort

~3-4 days for Phase 1 MVP. Phase 2 (Evidence.dev) ~2-3 days, opt-in via pip install clawmetry[reports].

Acceptance

• User types "Show me total cost per agent today" → bar chart in <5s, labeled openclaw/claude_code/etc.
• All 9 multi-agent schema tables queryable
• SQL injection ('; DROP TABLE events;--) rejected with clear error
• Saved Dive re-runs on tab open with current data
• No-key state shows clear banner, no silent failures

[vivekchand]

Hi @vivekchand! Took a careful look at what's remaining in this epic. Here's the plan:

The problem
DIVES-1 through DIVES-5 are fully implemented and tested, but DIVES-6 (telemetry: log dive_run events into the local store for prompt improvement) is the only missing piece.

Approach

1. clawmetry/local_store.py — Add ingest_dive_run(self, run: dict) -> None method that writes a row to the events table with event_type='dive_run', storing {question, sql, chart_type, row_count, latency_ms, had_error} in the data blob. Uses INSERT OR IGNORE on a generated run-id so re-delivery is safe. Follows the ingest_guardrail_event pattern at line 5726.
2. routes/local_query.py — Add "ingest_dive_run" to _DAEMON_METHODS frozenset (line ~628) so the dashboard process can call it via the daemon proxy without fighting the writer lock.
3. routes/dives.py — In api_dives_query() (line 183), after rows, sql_error = _execute(sql, store), add a fire-and-forget call: local_store_via_daemon("ingest_dive_run", question=..., sql=..., chart_type=..., row_count=..., latency_ms=..., had_error=...) wrapped in try/except so telemetry never fails the user-facing response.
4. tests/test_dives_telemetry.py — New test: instantiate a fresh in-memory LocalStore, call ingest_dive_run(...), assert one row exists in events with event_type='dive_run' and the expected fields in the data blob. Also confirm a duplicate call is a no-op.

Test plan

• New test file covers the DuckDB write contract directly
• python3 -m pytest tests/test_dives_telemetry.py -q passes
• python3 scripts/lint_daemon_allowlist.py confirms ingest_dive_run is registered

Estimated change
~65 lines across 4 files (clawmetry/local_store.py, routes/local_query.py, routes/dives.py, tests/test_dives_telemetry.py).

Drafting a PR now — will link here when ready. If you'd rather drive this yourself, drop a comment and I'll back off.

---

Generated by Claude Code

[vivekchand]

Draft PR opened: #2423. Please review when you have a moment.

---

Generated by Claude Code

[vivekchand]

Hi @vivekchand! Took a careful look at the remaining open sub-issues in this epic. Here's the plan:

The problem
DIVES-1 (SQL validator), DIVES-2 (prompt builder), and DIVES-3 (endpoints) are all shipped, but DIVES-5 (suggested-questions gallery) is still missing — there's no GET /api/dives/questions endpoint and no curated list for the UI to offer as starter prompts.

Approach

1. routes/dives.py — append a SUGGESTED_QUESTIONS list constant (~15 diverse questions covering all 9 allowlisted tables) and a new GET /api/dives/questions endpoint that returns {questions: [...]}. Each entry carries question, chart_type, and category so the UI can group them. ~40 lines.
2. tests/test_dives_questions.py — a regression test suite (~30 tests) that:
   • Asserts the endpoint returns 200 and a non-empty list
   • Asserts every question is a non-empty string
   • Asserts every chart_type is one of the known Chart.js types (bar, line, doughnut, scatter, bubble)
   • Asserts no duplicate question text (the list can't silently grow stale with copies)
   • Asserts every question would pass validate_sql if the LLM answered it correctly (smoke-tests the corpus against the validator)
   • Pins the exact count (one-way ratchet: new questions must be intentional)

Test plan

• python3 -m pytest tests/test_dives_questions.py tests/test_dives_sql_safety.py -q
• Verify GET /api/dives/questions returns the list on a running dashboard

Estimated change
~70 lines across 2 files (routes/dives.py, tests/test_dives_questions.py).

Drafting a PR now — will link here when ready. If you'd rather drive this yourself, drop a comment and I'll back off.

---

Generated by Claude Code