ishaq101's picture
/fix traceability (#15)
32abc41
|
Raw
History Blame Contribute Delete
25.2 kB
# DS Tools Expansion — Visualization & Modeling (Proposal)
**Status:** PROPOSAL — for team/mentor review; §6 items need Harry (Go/dedorch) + FE coordination.
**Date:** 2026-07-07 · **Branch context:** `pr/12`.
**Companions:** [REPO_STATUS.md](REPO_STATUS.md) (current built state) · [DEV_PLAN.md](DEV_PLAN.md)
(§4 #26/#27 deferred charts/images — this doc un-defers #26 with a concrete design) ·
[API_CONTRACT_BE_PYTHON.md](API_CONTRACT_BE_PYTHON.md) (contract that §6 extends).
**Research basis:** industry/lit review 2026-07-07 — sources in §9.
---
## 0. Executive summary
**The ask.** Data Eyond answers analytical questions with text + markdown tables. The product story
("junior data scientist that hands back a decision-ready deliverable", CRISP-DM) requires two
capabilities the chat surface can't express today: **charts** and **models** (forecast / clusters /
anomalies / drivers). This doc proposes what to add, on what stack, and in what order.
**The one architectural decision that matters.** DS agents in the market split into two families:
1. **Sandboxed code generation** — the LLM writes Python, an isolated sandbox executes it, charts
come back as PNG files (ChatGPT Advanced Data Analysis, Julius, LangChain deep-agents; infra =
E2B/Modal/Firecracker microVMs). Maximum flexibility; weakest governance — per Gartner's 2026
agentic-analytics taxonomy this is "Level 1: output varies per run, no governance."
2. **Declarative specs over governed tools** — the LLM (or deterministic code) emits a validated
JSON *specification*; deterministic code executes it against governed data (Databricks Genie,
Snowflake Cortex Analyst, ThoughtSpot Spotter; research systems LIDA and chat2plot). Bounded
flexibility; repeatable, auditable, no arbitrary code execution.
**Recommendation: family 2.** It is literally our existing architecture — the query engine already
does *constrained spec (QueryIR) → validator → deterministic compiler → guarded executor*.
Visualization and modeling should extend that spine, not bolt a code sandbox onto it. Sandboxed
codegen is re-evaluated only when curated tools hit an expressiveness ceiling (§7, M2).
**Feature set (phased):**
| Phase | Feature | User sees | New LLM calls |
|---|---|---|---|
| **V1** | Deterministic charts from existing `analyze_*` results (trend→line, aggregate→bar, correlation→heatmap, …) | Interactive chart under the answer, chart in traceability, chart in report | 0 |
| **V2** | Chart-aware planning: `render_chart` tool + LLM-picked `ChartSpec` for "plot X vs Y" asks and chart-edit turns | Charts on demand, editable ("make it a pie") | 0–1 (structured output) |
| **M1** | Modeling tools: `analyze_forecast`, `analyze_cluster`, `analyze_anomaly`, `analyze_driver` | Predictions with confidence bands, segments, outliers, ranked drivers — each with metrics, caveats, and a chart | 0 (planner already budgeted) |
| **M2** | *(deferred)* Hosted code sandbox for the long tail | Arbitrary analyses | n/a — decision gate in §7 |
**Stack (and the one-line why — full rationale §4):**
| Concern | Pick | Why |
|---|---|---|
| Chart artifact format | **Plotly Figure JSON**, compiled from a small pydantic `ChartSpec` | Team already decided Plotly-JSON (DEV_PLAN #26); `plotly==5.24.1` + `kaleido==0.2.1` already pinned in `pyproject.toml`; `react-plotly.js` fits the React/Vite FE; kaleido gives server-side PNG for the deferred PPT/PDF report export |
| Chart storage/delivery | Python-owned **`message_charts`** JSONB table + **`GET /api/v1/charts`** — the `message_traceability` pattern reused verbatim | SSE stays text-only (house rule); FE fetches artifacts on `done`, exactly like traceability today |
| Forecasting | **statsmodels** (ETS / SARIMAX, `seasonal_decompose`) | The standard agent-tool library for TS; interpretable, CPU-cheap, no new heavy deps beyond itself |
| Clustering / anomaly / drivers | **scikit-learn** (KMeans+silhouette, IsolationForest, regularized linear/logistic + permutation importance) | Already in the venv transitively (sentence-transformers) — pin it explicitly; interpretable models only |
| Explicitly NOT now | code sandbox (E2B/Modal), AutoGluon/FLAML AutoML, prophet, deep-learning TS, Vega-Lite | §4.3 rejected-alternatives table |
**Infrastructure delta (the "other than the chatbot interface" part):** one new Python-owned dedorch
table (DDL handoff to Harry), one new GET endpoint (contract addition), an FE chart renderer
(react-plotly.js) + artifact fetch on `done`, 2 new Python deps (`statsmodels`, explicit
`scikit-learn`), and a `chart` output kind in the tool contract (coordinate: `src/tools/contracts.py`
is tool-team-owned). **No sandbox service, no GPU, no new datastore, no change to the SSE stream.**
**Effort (rough):** V1 ≈ 4–5 dev-days Python + 2–3 FE · V2 ≈ 3 · M1 ≈ 6–8. V1 is demo-visible fastest.
---
## 1. How the field does it (what the research says)
### 1.1 Three architecture families
**A. Sandboxed code interpreters** (ChatGPT ADA, Claude analysis tool, Julius, LangChain
deep-agents reference). LLM writes pandas/matplotlib code; a sandbox (E2B, Modal, Daytona,
LangSmith Sandbox — Firecracker microVM isolation) executes it; PNGs/files come back.
*Strengths:* unbounded expressiveness — any analysis pandas can do.
*Weaknesses:* non-repeatable ("output varies per run"), un-auditable code paths, prompt-injection →
code-execution risk, real infra (microVMs, warm pools, credential isolation — LangChain's own docs:
"avoid adding credentials to the sandbox"), and results that bypass any governance layer. The 2026
Gartner-derived maturity taxonomy places these at **Level 1** precisely because of governance.
**B. Declarative specs over governed data** (Databricks Genie, Snowflake Cortex Analyst,
ThoughtSpot Spotter, Amazon Q in QuickSight — **Level 2/3**). The LLM's only job is to emit a
constrained artifact (SQL against a semantic layer, or a chart/analysis spec); execution is
deterministic platform code. Research systems converge here for viz: **LIDA** (Microsoft) runs a
staged pipeline — data *Summarizer**Goal Explorer**VisGenerator* with generate-validate-repair;
**chat2plot** generates *"declarative visualization specs in JSON rather than Python code"* for
*"more secure execution, as the LLM does not directly generate code"*, validated by
structured-output/function-calling, rendered by plotly or altair.
*Strengths:* repeatable, auditable, cheap, safe; specs are storable/editable/versionable artifacts.
*Weaknesses:* bounded expressiveness — you can only draw/fit what the spec grammar covers.
**C. Investigative/agentic analytics** (Tellius, Qlik Predict — **Level 3/4**): multi-step
decomposition of metric changes with quantified attribution ("why did revenue dip?"), ML under the
hood (segment comparison, variance decomposition), narrative output. Architecturally these are
family B + a planner + statistical tooling — *not* codegen.
### 1.2 Where Data Eyond already sits
The repo is a family-B system with a family-C planner:
`data_catalog` = the semantic/governance layer · QueryIR + `IRValidator` + SqlCompiler + read-only
executor = the constrained-spec pipeline · Planner→TaskRunner→Assembler = the investigative loop ·
`report_inputs` → versioned reports = the audit trail. The 2026 platform comparison's core critique —
Level-1 tools "sacrifice audit trail, role-controlled, repeatable execution" — is exactly the
trade-off we already refused when we built IR validation instead of LLM-SQL. Viz and ML should
follow the same refusal.
### 1.3 ML in analyst agents specifically
Published agent systems for business TS/ML (sktime LLM workflows, TimeCopilot, DCATS) wrap
**statsmodels / scikit-learn estimators as tools** with the LLM planning which tool to call —
not writing model code. Interpretability drives library choice: ETS/ARIMA with confidence
intervals, KMeans with silhouette, permutation importance — things an executive-facing narrative
can explain and a report can defend. Heavy AutoML (AutoGluon, FLAML) appears in Kaggle-style
agents (MLE-bench, AIDE), not analyst products.
---
## 2. What we add and why (feature detail)
Product gaps these close, mapped to CRISP-DM (the report's own structure):
1. **Charts (V1/V2)***Data Understanding + Evaluation.* Trend, composition, correlation and
distribution questions are answered today with tables the user must mentally plot. Every
comparable product renders charts; our reports' "EDA" section is tables-only. V1 needs **zero
new LLM calls**: each registered `analyze_*` already returns typed, structured output
(`ToolOutput.kind ∈ table|stats|series`) that maps rule-deterministically to a chart type.
2. **Forecast (M1, `analyze_forecast`)** — *Modeling.* "What will sales look like next quarter?"
is currently answered by `analyze_trend` (descriptive slope only). ETS/SARIMAX with a holdout
backtest (MAPE/sMAPE reported) + CI bands is the minimum credible answer.
3. **Clustering (M1, `analyze_segment` upgrade → `analyze_cluster`)** — *Modeling.* "What kinds of
customers do we have?" KMeans on scaled numerics, k by silhouette, cluster profile table +
PCA-2D scatter. Note the taxonomy already reserved `analyze_segment` (built, unregistered) —
this either upgrades it or registers a sibling; decide with the tool owner.
4. **Anomaly detection (M1, `analyze_anomaly`)** — *Evaluation.* "Anything unusual last month?"
IsolationForest (tabular) / STL-residual z-score (time series); flagged-rows table + marked
chart. Also the seed of the Level-4 "proactive monitoring" story later.
5. **Driver analysis (M1, `analyze_driver`)** — the Level-3 differentiator: "what's driving
churn/the dip?" Regularized linear/logistic fit + permutation importance → ranked-driver table.
Complements `analyze_contribution` (arithmetic decomposition) with statistical attribution.
6. **Model artifacts in reports** — every M1 tool writes metrics + caveats into its
`AnalysisRecord`, so reports gain honest Modeling/Evaluation sections for free (charts embed as
kaleido PNG when the deferred PPT/PDF export lands — same artifact, two renderings).
Non-goals now: dashboards, scheduled/proactive monitoring, model persistence/registry (each run
fits in-request on ≤10k retrieved rows), deep-learning anything, cross-source joins.
---
## 3. Architecture (how)
### 3.1 Charts — V1 data flow (no LLM)
```
structured_flow turn (unchanged):
Planner → TaskRunner → results_snapshot {task_id → ToolOutput}
▼ NEW, deterministic, never-throw
ChartBuilder.build(results_snapshot, plan)
rule table: analyze_trend→line · analyze_aggregate→bar/grouped
analyze_correlation→heatmap · analyze_descriptive→histogram
analyze_comparison→grouped bar · analyze_contribution→pareto
→ ChartSpec (pydantic, ≤1 per substantive task)
SpecCompiler → plotly.graph_objects.Figure (schema-validated
by construction) → fig.to_json(), downsample >2k pts/trace,
payload cap ~1 MB
ChartStore.save → dedorch `message_charts` (Python-owned JSONB, one row per chart,
keyed analysis_id+message_id+order) — flushed alongside the traceability flush,
before `done` (same 8-site discipline; error turns write nothing)
SSE stream: UNCHANGED (text-only) ──▶ done{message_id}
FE on done ──▶ GET /api/v1/charts?analysis_id&message_id → [{chart_id, spec, figure_json,
title, source_task_id}] ──▶ react-plotly.js render under the answer
```
Design rules carried over from the house style: **never-throw** (a chart failure degrades to
no-chart, never kills the turn) · charts derive **only from executed tool results** (never from
LLM text — grounded by construction, LIDA's "data-faithful" property) · traceability's `tool_calls`
entries gain a `chart_id` ref so provenance and artifact stay correlated.
### 3.2 Charts — V2 (`render_chart` tool + ChartSpec-by-LLM)
- Register `render_chart` in the planner registry (Pattern A: takes `data` = `${t<id>}` + spec
params) so "plot revenue by region as a pie" becomes a plannable step. `ToolOutput` gains
`kind="chart"`**one-line Literal change in tool-team-owned `contracts.py` + an Assembler
branch; coordinate with the tool owner before building.**
- Where the rule table is ambiguous or the user asked for a specific viz, ONE structured-output
LLM call emits `ChartSpec` (chat2plot's exact trick: constrained pydantic schema via function
calling — the LLM never writes Plotly JSON, so invalid output is a validation error with one
repair retry, mirroring the Planner's re-prompt loop).
- Chart-edit turns ("make it horizontal") load the stored spec, apply the delta, re-compile,
save a new chart row — spec-as-artifact is what makes edits cheap (family-B dividend).
### 3.3 Modeling tools — M1
All four are composite tools in the existing taxonomy — Pattern A inputs, `ToolOutput` outputs,
registered in `analytics_registry()`, planner-visible with prompt-grade descriptions:
| Tool | Method (all CPU, interpretable) | Output (`kind`) | Auto-caveats |
|---|---|---|---|
| `analyze_forecast` | statsmodels ETS; SARIMAX when seasonality detected; naive-seasonal fallback | `series` (history + forecast + CI) + chart | holdout MAPE/sMAPE; "≥2 seasons or fallback"; missing-period warning |
| `analyze_cluster` | sklearn scale→KMeans, k∈2..8 by silhouette | `table` (profiles) + PCA scatter chart | silhouette score; "clusters are descriptive, not causal" |
| `analyze_anomaly` | IsolationForest (tabular) / STL residual z (TS) | `table` (flagged rows) + marked chart | contamination assumption; top-N only |
| `analyze_driver` | standardized ridge/logistic + permutation importance | `stats` (ranked drivers) + bar chart | R²/AUC on holdout; "association ≠ causation" |
Safety/robustness rails (same philosophy as `DbExecutor`): row cap (inherits the 10k retrieve
cap) + feature cap (≤20 numeric) · fixed `random_state` (repeatable runs — the family-B promise) ·
`asyncio.to_thread` + 30s wall-clock timeout · never-throw (failure → `kind="error"`, TaskRunner
degrade-and-continue does the rest) · metrics/caveats copied verbatim into the `AnalysisRecord`
(Assembler narrates them; it never invents numbers — existing rule).
### 3.4 What explicitly does NOT change
Router intents (structured_flow already covers "plot/forecast/segment" asks — verify with new
`eval/intent` cases, not new intents) · SSE event shape (charts are fetched, not streamed) ·
QueryIR/compiler/executor (ML runs on already-retrieved DataFrames) · report floor · guardrail
layers.
---
## 4. Stack rationale (why these picks)
### 4.1 Plotly JSON over the alternatives
| Option | Verdict | Reasoning |
|---|---|---|
| **Plotly Figure JSON** (pick) | ✅ | Already a pinned dep (`plotly==5.24.1`, `kaleido==0.2.1`) and already the team lean (DEV_PLAN #26 "Plotly→JSON, not matplotlib PNG"). `graph_objects` construction = schema validation for free. `react-plotly.js` is mature for the React/Vite FE. **kaleido closes the report loop**: same figure → interactive JSON in chat, PNG in PPT/PDF export (DEV_PLAN deferred "PPT preferred"). |
| Vega-Lite | ❌ for now | The research favorite (LIDA/chat2plot support it; tighter grammar, smaller specs) — *when the LLM writes the spec*. In our design the LLM writes a tiny `ChartSpec` and a deterministic compiler emits the figure, so Vega-Lite's LLM-ergonomics advantage mostly evaporates, and it would add an FE renderer + Python compiler we don't have. `ChartSpec` is renderer-agnostic (chat2plot precedent) — a Vega compiler can be added later without touching tools. |
| matplotlib/seaborn PNG | ❌ | Static, non-interactive, heavier payloads, no client theming, and the team already rejected it (#26). Keep matplotlib only as kaleido's export path. |
| Mermaid/ASCII in markdown | ❌ | Not data-viz grade. |
### 4.2 statsmodels + scikit-learn over the alternatives
- **statsmodels**: the default in every surveyed agent-tools stack for forecasting; ETS/SARIMAX are
explainable to executives and run in milliseconds on our row caps. *(New dep — needs sign-off.)*
- **scikit-learn**: KMeans/IsolationForest/linear models cover cluster/anomaly/driver with
interpretable outputs. Already in the venv **transitively** via sentence-transformers — pin it
explicitly the moment we import it (transitive deps are not a contract).
- **Rejected:** prophet (heavy dep, maintenance-mode, marginal gain over ETS at our scale) ·
AutoGluon/FLAML (GPU-hungry, opaque ensembles — wrong for decision-ready narratives; that's
Kaggle-agent gear) · sktime/TimeCopilot (nice unified API, but another abstraction layer over the
two libs we'd still be running; revisit if tool count grows) · LLM-as-forecaster (research shows
it underperforms classical baselines on numeric TS; we use the LLM to *plan and narrate*, never
to produce numbers — existing Assembler rule).
### 4.3 No code sandbox (the biggest "why not")
Codegen would give the long tail (custom feature engineering, exotic plots) but costs exactly what
our architecture is sold on: arbitrary LLM code vs our five-layer read-only defense; per-run
variance vs `report_inputs` repeatability; PNG blobs vs auditable specs; and real infra we don't
have — HF Spaces is one Docker container (no Firecracker microVMs; in-process `exec()` of LLM code
is a non-starter against our own guardrail posture). Every hosted option (E2B, Modal, Daytona)
means a new external service + credential-isolation design. **Decision gate for M2 (§7):** revisit
only when a logged backlog of user asks provably doesn't fit the curated tools.
---
## 5. Implementation plan (task-table-ready)
Statuses: ⬜ not started (all — proposal). Owners are suggestions.
| # | Phase | Task | Owner | Note |
|---|---|---|---|---|
| 1 | V1 | `ChartSpec` pydantic + rule table + `SpecCompiler` (plotly), downsampling + size caps | Rifqi | pure Python, no seams touched |
| 2 | V1 | `ChartBuilder` hook in `chat_handler._run_slow_path` after TaskRunner; never-throw | Rifqi | mirrors traceability accumulation |
| 3 | V1 | `message_charts` ORM + `ChartStore` (save/list); flush wired at the traceability flush sites | Rifqi | Python-owned, like `message_traceability` |
| 4 | V1 | **DDL handoff to Harry**: `message_charts` (uuid id, analysis_id FK, message_id, user_id, `chart` jsonb {spec, figure, title, source_task_id, order}, created_at) | Rifqi → Harry | plural name, uuid ids — house rules |
| 5 | V1 | `GET /api/v1/charts?analysis_id&message_id` + contract § in API_CONTRACT_BE_PYTHON.md | Rifqi | mirror traceability endpoint; 404 semantics same |
| 6 | V1 | FE: react-plotly.js renderer + fetch-on-done; Go passthrough if FE→Go→Python | FE + Harry | coordination, not Python work |
| 7 | V1 | traceability `tool_calls[].chart_id` ref; local tests (spec compile goldens, store, endpoint) | Rifqi | tests stay local |
| 8 | V2 | `render_chart` ToolSpec + `kind="chart"` in contracts.py | **tool owner** + Rifqi | contracts are tool-team-owned — coordinate first |
| 9 | V2 | ChartSpec-by-LLM (structured output + 1 repair retry) for explicit viz asks; chart-edit turns | Rifqi | chat2plot pattern |
| 10 | V2 | `eval/intent` cases for plot/forecast phrasing (EN+ID); chart-rule goldens in eval or tests | Rifqi + Sofhia | protocol: eval before router-adjacent claims |
| 11 | M1 | `uv add statsmodels` + pin scikit-learn (sign-off needed) | Rifqi | manual §6.4 |
| 12 | M1 | `analyze_forecast` (+ backtest + caveats) → registry + planner prompt row + few-shot | Rifqi + tool owner | taxonomy fit review |
| 13 | M1 | `analyze_cluster` / `analyze_anomaly` / `analyze_driver` (same template) | split | one PR each, stacked |
| 14 | M1 | Report generator: Modeling/Evaluation sections consume new record fields; kaleido PNG path stub for PPT export | Sofhia (report) / Rifqi | ties into deferred report-formats work |
| 15 | M2 | ⏸️ sandbox decision gate: collect can't-answer asks in traceability meta; revisit with evidence | — | deferred by design |
Sequencing: 1–7 ship V1 end-to-end (demo-visible; FE renderer is the only external dependency —
until it lands, Swagger shows the JSON). 8–10 next. 11–14 after taxonomy review with the tool
owner. Estimates: V1 ≈ 4–5 Python dev-days + 2–3 FE; V2 ≈ 3; M1 ≈ 6–8.
## 6. Coordination & infra deltas (the handoff list)
1. **Harry / dedorch:** `message_charts` migration (task #4 DDL). Optional later: `charts_count`
hint in the `done` payload — contract change, batch it with the next contract rev.
2. **FE:** react-plotly.js (+ theme config), fetch-on-done, render under answer bubble; chart
panel in the report preview later.
3. **Tool team:** `kind="chart"` Literal + `render_chart` spec review; `analyze_segment`
upgrade-vs-sibling decision (§2.3).
4. **Deps (Rifqi sign-off):** `statsmodels` new; `scikit-learn` explicit pin. Both CPU wheels,
no image-size drama on HF.
5. **No change requested from:** router intents, SSE shape, Redis, Langfuse, guardrails.
## 7. Risks & mitigations
| Risk | Mitigation |
|---|---|
| Chart payloads bloat the DB / FE | downsample >2k pts/trace; ~1 MB cap; store spec always, figure optionally recompilable |
| Wrong chart type annoys users | V1 rules only for unambiguous mappings; else no chart (no-chart beats bad-chart); V2 adds the LLM picker |
| Forecast on garbage data → confident nonsense | hard preconditions (min periods, regular frequency) → refuse-with-reason into the record; backtest metric always shown; naive fallback labeled |
| Never-throw hides chart/model failures | same answer as traceability: failure reason lands in the tool span + record caveats, visible in `/traceability` |
| PII in chart labels/tooltips | charts render only executed result data (same exposure class as today's tables); `pii_flag` columns excluded from V2 LLM spec-picking context (prompt-plane rule holds) |
| Scope creep toward AutoML/sandbox | M2 gate requires logged evidence of unmet asks, not vibes |
## 8. Open questions
1. `message_charts` vs folding chart JSON into `message_traceability` — separate table recommended
(different consumer, different lifecycle, report reuse by `chart_id`), but Harry may prefer one
migration. **Decide at DDL handoff.**
2. Does the FE want figure JSON (render-ready, bigger) or spec+data (smaller, FE compiles)?
Recommend figure JSON first — dumbest possible FE integration.
3. `analyze_segment` (existing, unregistered) vs new `analyze_cluster` — tool-owner call.
4. Report PNG embedding: inline base64 in markdown vs chart_id placeholder resolved at export —
recommend placeholder; decide with report-formats work.
5. Do forecast/cluster asks change `structured_flow` routing confidence? Add eval cases first
(task #10) — data before prompt edits.
## 9. Sources (reviewed 2026-07-07)
- Tellius — [Best AI Data Analysis Agents in 2026: 12 platforms compared](https://www.tellius.com/resources/blog/best-ai-data-analysis-agents-in-2026-12-platforms-compared-for-nl-to-sql-autonomous-investigation-and-governance) (maturity levels, governance trade-offs, Gartner 2026 agentic-analytics framing)
- Microsoft Research — [LIDA: grammar-agnostic visualization generation with LLMs](https://microsoft.github.io/lida/) · [paper](https://aclanthology.org/2023.acl-demo.11/) (staged pipeline, generate-validate-repair, data-faithfulness)
- [chat2plot](https://github.com/nyanp/chat2plot) (declarative JSON specs over codegen; structured-output validation; "more secure execution, as LLM does not directly generate code")
- LangChain — [deep-agents data analysis reference](https://docs.langchain.com/oss/python/deepagents/data-analysis) (the codegen-family architecture: sandbox backends E2B/Modal/Daytona, PNG artifacts, credential isolation)
- Modal — [Best code execution sandboxes for AI agents 2026](https://modal.com/resources/best-code-execution-sandboxes-ai-agents) (sandbox infra requirements: microVM isolation, warm pools)
- [Vega-Lite](https://vega.github.io/vega-lite/) · [Plotly vs Vega comparison thread](https://github.com/plotly/documentation/issues/1330) (template-based vs grammar-based spec trade-off)
- [sktime LLM workflows](https://medium.com/@benedikt_heidrich/can-we-do-time-series-analysis-with-llm-powered-workflows-using-sktime-12b19cf39376) · [TimeCopilot / agentic forecasting survey](https://arxiv.org/html/2508.04231v1) (statsmodels/sklearn-as-tools pattern; LLM plans, classical models compute)