/fix traceability

API_CONTRACT_BE_PYTHON.md

@@ -467,10 +467,13 @@ Field rules:
 
 - `planning`: present only when the planner ran (`structured_flow`); otherwise `null`.
 - `thinking`: **always `null` in v1** — our agents are plain chat completions with no native reasoning output, and synthesizing it post-hoc would be unfaithful. The field stays in the payload so it can be populated later without a contract change.
-- `tool_calls`: every invoked tool with `input`, `output`, `status`, `task_id` (nullable), and `error` (nullable); empty for chat / help / greeting / refusal paths.
-- `sources`: required for retrieval flows; empty for chat / help / refusal paths and for `check`.
+- `tool_calls`: every invoked tool with `summary` (plain-English one-liner), `input`, `output`, `status`, `task_id` (nullable), and `error` (nullable); empty for chat / help / greeting / refusal paths. `input`/`output` are the raw tool I/O (opaque ids) — render them in a collapsible "technical details" section, not the headline; use `summary` for the headline.
+- `data_used`: one entry per `retrieve_data` call, resolved to **real names** for display (present only when a structured pull ran; empty otherwise). Split into `columns_read` (columns read straight from the user's data, each tagged with its `roles`) and `output_columns` (`kind: "column"` = read from data, `kind: "computed"` = calculated, carrying a `formula` and **no id**). Also carries `tables` (all touched, incl. join targets), `joins`, `filters` (with a plain-language `description`), `group_by`, `order_by`, `limit`, `rows_returned`, and the executed `query`. Built by deterministic catalog lookup — no LLM.
+- **`id` fields are machine-only.** Every `id` in `data_used` (`source.id`, `tables[].id`, `columns_read[].id`) is for linking/reconciliation/audit — **the frontend must never render it.** Show `name` (qualified as `table.name`). A `computed` output column has no id by design.
+- `sources`: required for retrieval flows; empty for chat / help / refusal paths and for `check`. Database sources also carry `source_name` (the DB's real name) and `tables` (every table touched).
+- `thinking`, `filters[].description`, `tool_calls[].summary` are built from fixed templates, never an LLM — traceability adds no latency or token cost and cannot hallucinate.
 - The payload also carries an internal `user_id` (ownership); the frontend may ignore it.
-- Truncation: `preview` ≤ 5 rows; any string inside `input`/`output`/`preview`/`snippet` ≤ 300 chars; rows beyond the preview are dropped (`row_count` is preserved).
+- Truncation: `preview` ≤ 5 rows; any string inside `input`/`output`/`preview`/`snippet` ≤ 300 chars (executed `query` ≤ 2000); rows beyond the preview are dropped (`row_count` is preserved).
 
 Response `200` for `structured_flow`:
 
@@ -507,6 +510,7 @@ Response `200` for `structured_flow`:
       "order": 1,
       "task_id": null,
       "name": "check_data",
+      "summary": "Inspected your data source structure",
       "input": { "source_hint": "structured" },
       "output": {
         "kind": "table",
@@ -521,6 +525,7 @@ Response `200` for `structured_flow`:
       "order": 2,
       "task_id": null,
       "name": "retrieve_data",
+      "summary": "Retrieved 4 rows across 2 columns from orders",
       "input": { "ir": { "source_id": "src_sales_db", "table_id": "orders", "select": ["region", "amount"], "group_by": ["region"] } },
       "output": {
         "kind": "table",
@@ -532,11 +537,34 @@ Response `200` for `structured_flow`:
       "error": null
     }
   ],
+  "data_used": [
+    {
+      "source": { "id": "src_sales_db", "name": "sales db", "type": "schema" },
+      "tables": [ { "id": "orders", "name": "orders", "role": "base" } ],
+      "joins": [],
+      "columns_read": [
+        { "id": "c_region", "name": "region", "table": "orders", "data_type": "string",  "pii": false, "roles": ["selected", "grouped"] },
+        { "id": "c_amount", "name": "amount", "table": "orders", "data_type": "decimal", "pii": false, "roles": ["aggregated"] }
+      ],
+      "output_columns": [
+        { "name": "region", "kind": "column",   "from": "orders.region" },
+        { "name": "total",  "kind": "computed", "from": "orders.amount", "formula": "SUM(orders.amount)" }
+      ],
+      "filters": [],
+      "group_by": ["orders.region"],
+      "order_by": [],
+      "limit": null,
+      "rows_returned": 4,
+      "query": "SELECT region, SUM(amount) AS total FROM orders GROUP BY region"
+    }
+  ],
   "sources": [
     {
       "type": "database",
       "source_id": "src_sales_db",
+      "source_name": "sales db",
       "name": "orders",
+      "tables": ["orders"],
       "query": "SELECT region, SUM(amount) AS total FROM orders GROUP BY region",
       "detail": {
         "table": "orders",
@@ -547,7 +575,7 @@ Response `200` for `structured_flow`:
 }
 ```
 
-> Note: `retrieve_data`'s real `input` is the compiled query IR under an `ir` key (the planner builds an IR, never raw SQL). The executed SQL/rendered query appears on the corresponding `sources[].query`.
+> Note: `tool_calls[].input` is the raw compiled query IR (opaque `column_id`/`table_id`) — the technical layer. **`data_used` is the user-facing layer**: the same pull resolved to real names, with computed columns (e.g. `total`) flagged `kind: "computed"` so they are never shown as if they were columns in the user's database. Every `id` there is machine-only.
 
 Response `200` for `unstructured_flow`:
 

DEV_PLAN.md

@@ -65,6 +65,8 @@ base64-mangled from Go. Fix tasks (same status legend as §0):
 | Q7 | `analyze_merge` two-table combine tool (unblocks "worst A + biggest B" questions) | tool owner | ✅ | tool shipped by Sofia (8abf635, KM-703); planner slice done 2026-07-09: `_validate_data_source` guards `data_right`, two-retrieve→merge few-shot (Example I), planner.md "Two measures per entity" bullet |
 | Q8 | Report v2: business-question answer section, unresolved/excluded sections, evidence tables from `results_snapshot`, caveat dedupe, single language | Rifqi/Sofhia | ✅ | done 2026-07-09: still exactly ONE LLM call (extended to also draft `bq_answers`, index-based record refs, deterministic fallback = v1 behavior); evidence tables from table-kind outputs (≤3/record, ≤10 rows, ≤8 cols, `check_*` skipped); reply language via `detect_reply_language` on objective+BQs; verified in-process against live analysis 935a091e |
 | Q9 | Record-curation endpoint (`GET …/records` + `exclude_record_ids`) + readiness GET for the FE delta guard | Rifqi ↔ FE | ✅ | done 2026-07-09: `GET /tools/report/{analysis_id}/records` + `/readiness` (registered before `/{version}` — int-coercion route-order trap), `exclude_record_ids` on POST; contract updated same change; FE wiring pending (Rifqi → FE) |
+| Q10 | Traceability `data_used` layer — resolve IR ids → real names for the FE (users couldn't map `c_…`/`t_…` ids back to their data; aggregate aliases like `total_revenue` looked like real columns) | Rifqi ↔ FE | ✅ | done 2026-07-13 (pr/15): new `src/traceability/resolve.py` builds `data_used[]` (real source/table/column names; joins; plain-language filters; `columns_read` vs `output_columns` with `computed`+`formula`), `tool_calls[].summary`, `sources[]` gains `source_name`+all tables; **ids kept but machine-only (FE must not render)**; deterministic no-LLM, never-throw; catalog threaded to the scratchpad at the composition root. Contract + `TRACEABILITY_FE_HANDOFF.md` updated; FE wiring pending (Rifqi → FE). Additive/non-breaking |
+| Q11 | IR wart: `OrderByClause.column_id` may hold a SELECT **alias** (a computed output), not a catalog column_id | Rifqi | 🔎 | surfaced by Q10 — the resolver tolerates it (`kind: "computed"` fallback), but the IR field name is misleading. Consider an explicit `by_alias` field or renaming. Low priority; no functional bug |
 
 ## 1. The direction change (locked decisions from 2026-06-24)
 

DS_TOOLS_PLAN.md

@@ -0,0 +1,328 @@
+# 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)

REPO_STATUS.md

@@ -2,7 +2,7 @@
 
 **Audience:** teammates onboarding onto the Python repo (`Agentic-Service-Data-Eyond-Catalog`).
 **Scope:** what the code does **right now** (branch `pr/4`, ticket KM-652). Describes current state only — no roadmap or to-dos.
-**Snapshot date:** 2026-06-25. **Data-layer reconcile 2026-07-01:** §8/§12 updated — dedorch cutover done, `data_catalog` model reconciled. **Query-path fix 2026-07-02:** §8/§13 — dedorch catalogs ship no FKs → Python infers them (`fk_inference.py`); shared-Fernet-key gotcha documented. **Agent-quality fixes 2026-07-08 (pr/13):** from the scoped live-test review — the planner gains an explicit **infeasible** outcome (`TaskList.infeasible_reason` → deterministic EN/ID data-gap reply via `refusals.data_gap_message`; no more force-mapping absent measures like `pa` AS "revenue"), the IR validator rejects bare selects under `group_by` (self-corrects via the planner retry), `analyze_trend` handles integer year/month columns (was collapsing every row into one 1970-01 bucket), planner few-shots add top-N (Example G) + infeasible (Example H), numeric catalog `sample_values` are base64-decoded at read (`catalog/sample_decode.py` — stopgap for Go's byte-marshaling; primary fix is Go-side), traceability no longer emits null source rows for failed retrievals, and `check_data` hides `-1` row counts. **Report v2 + analyze_merge planner support 2026-07-09 (pr/13):** Sofia's `analyze_merge` tool (8abf635, KM-703) is now planner-supported (`_validate_data_source` guards `data_right`, two-retrieve→merge few-shot Example I, planner.md "Two measures per entity" rule); the report gains per-business-question answers (`bq_answers` — drafted by the SAME single LLM call, index-based record refs, deterministic fallback unchanged), "Attempted, Unresolved" + "Excluded Analyses" sections (failed runs are no longer silently dropped), evidence tables copied from `results_snapshot` (table-kind outputs, ≤3/record ≤10 rows ≤8 cols, `check_*` skipped), normalized caveat dedupe with caps (12/10), and single-language output via `detect_reply_language`; the report surface adds `GET /tools/report/{analysis_id}/records` (curation list), `GET …/readiness` (FE delta guard), and `exclude_record_ids` on POST — see API_CONTRACT_BE_PYTHON.md. **Report compaction 2026-07-09 (pr/13):** the rendered markdown drops the "Notes & Limitations", "Attempted, Unresolved", and "How This Was Analyzed" sections (team decision — compact report; render blocks commented out in `report/generator.py`, not deleted). The JSON body keeps `caveats`/`open_questions`/`unresolved`/`method_steps` and the curation/records endpoints are unchanged. **Cross-repo update 2026-06-29:** §2/§8/§11/§12 re-verified against
+**Snapshot date:** 2026-06-25. **Data-layer reconcile 2026-07-01:** §8/§12 updated — dedorch cutover done, `data_catalog` model reconciled. **Query-path fix 2026-07-02:** §8/§13 — dedorch catalogs ship no FKs → Python infers them (`fk_inference.py`); shared-Fernet-key gotcha documented. **Agent-quality fixes 2026-07-08 (pr/13):** from the scoped live-test review — the planner gains an explicit **infeasible** outcome (`TaskList.infeasible_reason` → deterministic EN/ID data-gap reply via `refusals.data_gap_message`; no more force-mapping absent measures like `pa` AS "revenue"), the IR validator rejects bare selects under `group_by` (self-corrects via the planner retry), `analyze_trend` handles integer year/month columns (was collapsing every row into one 1970-01 bucket), planner few-shots add top-N (Example G) + infeasible (Example H), numeric catalog `sample_values` are base64-decoded at read (`catalog/sample_decode.py` — stopgap for Go's byte-marshaling; primary fix is Go-side), traceability no longer emits null source rows for failed retrievals, and `check_data` hides `-1` row counts. **Report v2 + analyze_merge planner support 2026-07-09 (pr/13):** Sofia's `analyze_merge` tool (8abf635, KM-703) is now planner-supported (`_validate_data_source` guards `data_right`, two-retrieve→merge few-shot Example I, planner.md "Two measures per entity" rule); the report gains per-business-question answers (`bq_answers` — drafted by the SAME single LLM call, index-based record refs, deterministic fallback unchanged), "Attempted, Unresolved" + "Excluded Analyses" sections (failed runs are no longer silently dropped), evidence tables copied from `results_snapshot` (table-kind outputs, ≤3/record ≤10 rows ≤8 cols, `check_*` skipped), normalized caveat dedupe with caps (12/10), and single-language output via `detect_reply_language`; the report surface adds `GET /tools/report/{analysis_id}/records` (curation list), `GET …/readiness` (FE delta guard), and `exclude_record_ids` on POST — see API_CONTRACT_BE_PYTHON.md. **Report compaction 2026-07-09 (pr/13):** the rendered markdown drops the "Notes & Limitations", "Attempted, Unresolved", and "How This Was Analyzed" sections (team decision — compact report; render blocks commented out in `report/generator.py`, not deleted). The JSON body keeps `caveats`/`open_questions`/`unresolved`/`method_steps` and the curation/records endpoints are unchanged. **Traceability `data_used` layer 2026-07-13 (pr/15):** `GET /api/v1/traceability` gains a resolved, user-facing `data_used[]` block (one per `retrieve_data` call) — real source/table/column names, joins, plain-language filters, and result columns split into read-from-data vs `computed` (with `formula`, e.g. `total_revenue` = `SUM(line_total)`, so an alias is never shown as a real column). Ids are kept but **machine-only (FE must not render)**. Also adds `tool_calls[].summary`, and `sources[]` now carry `source_name` + every table touched. Deterministic catalog resolution (new `src/traceability/resolve.py`), no LLM, never-throw; catalog threaded to the scratchpad at the slow-path composition root. Additive/non-breaking; contract + `TRACEABILITY_FE_HANDOFF.md` updated. **Cross-repo update 2026-06-29:** §2/§8/§11/§12 re-verified against
 the **Go source** (`Orchestrator-Agent-Service`), not its docs. The Go service has moved well past its
 own (uncommitted, stale) design docs: it now hosts the **dedorch SQL migrations** in-repo and a full
 **`/api/v1/analyses` + `/api/v1/skills`** REST surface. Go does **not** call Python yet — those skills

src/agents/chat_handler.py

@@ -418,6 +418,9 @@ class ChatHandler:
                 )
                 reader = MemoizingCatalogReader(scoped)
                 catalog = await reader.read(user_id, "structured")
+                # Give traceability the same catalog snapshot so it can resolve the
+                # retrieve_data IR ids to real names (the data_used layer). KM-691.
+                pad.set_catalog(catalog)
                 # structured_flow always runs the slow analytical path (the
                 # ENABLE_SLOW_PATH flag was removed 2026-07-02).
                 # Detect reply language from the ORIGINAL message (not `rewritten` — the

src/agents/handlers/check.py

@@ -550,13 +550,18 @@ def _render_schema_source(out: ToolOutput, reply_language: str) -> str:
         tname, tcols = next(iter(tables.items()))
         rc = tcols[0]["table_rows"]
         head = f"**{name}**" + (f" ({rc} {sc['rows_word']})" if rc else "")
-        parts.append(f"{head}\n{_columns_table(tcols, reply_language)}")
+        # Blank line between the bold header and the table: GFM only parses a
+        # pipe table as a table when a blank line precedes it. Without it the FE
+        # renderer folds header + table into one paragraph and the pipes render
+        # literally (the "flattened one-line table" bug).
+        parts.append(f"{head}\n\n{_columns_table(tcols, reply_language)}")
     else:
         parts.append(f"**{name}**")
         for tname, tcols in tables.items():
             rc = tcols[0]["table_rows"]
             sub = f"{sc['table_word']} {tname}" + (f" ({rc} {sc['rows_word']})" if rc else "") + ":"
-            parts.append(f"{sub}\n{_columns_table(tcols, reply_language)}")
+            # Blank line before the table so GFM renders it as a table (see above).
+            parts.append(f"{sub}\n\n{_columns_table(tcols, reply_language)}")
 
     summary = _render_summary(rows, reply_language)
     if summary:

src/catalog/reader.py

@@ -7,9 +7,13 @@ Catalog-level search is added later if catalog grows past the limit.
 from datetime import UTC, datetime
 from typing import Literal
 
+from src.middlewares.logging import get_logger
+
 from .models import Catalog, Source
 from .store import CatalogStore
 
+logger = get_logger("catalog_reader")
+
 SourceHint = Literal["chat", "unstructured", "structured"]
 
 
@@ -81,9 +85,19 @@ class AnalysisScopedCatalogReader(CatalogReader):
     names. A database shows as "xl test" (analysis-scope) instead of the
     auto-generated `postgres_<hash>` placeholder, and documents show at all
     (the user-scope catalog holds no `unstructured` sources, so reading them from
-    user-scope always came back empty). When the analysis has no catalog row
-    (legacy / not yet bound) or the read fails, it degrades to the wrapped
-    user-scope reader, so unbound rooms behave exactly as before.
+    user-scope always came back empty).
+
+    Fallback rule (tightened 2026-07-13): the user-scope reader is used ONLY when
+    there is no `analysis_id` at all (a legacy room with no analysis concept).
+    When an `analysis_id` IS present but its catalog row is missing (legacy
+    analysis created before catalog materialization, or Go hasn't rebuilt the
+    binding yet) or the read fails, this returns an EMPTY catalog — NOT the
+    user-scope catalog. Previously it degraded to user-scope, which silently
+    surfaced sources that are NOT bound to this analysis as if they were (e.g. a
+    room bound to a CSV answered "check" with the account's unrelated XLSX). An
+    empty result reads correctly as "nothing bound yet — re-save / rebuild the
+    binding to materialize the analysis catalog". Every outcome is logged so a
+    miss is diagnosable instead of silent.
     """
 
     def __init__(self, inner: CatalogReader, analysis_id: str | None) -> None:
@@ -94,19 +108,37 @@ class AnalysisScopedCatalogReader(CatalogReader):
         self._analysis_id = analysis_id
 
     async def read(self, user_id: str, source_hint: SourceHint) -> Catalog:
-        # Read analysis-scope for BOTH structured and unstructured. Verified via
-        # the dedorch `data_catalog` table: the analysis-scope rows carry the real
-        # DB names AND the room's documents (`source_type='unstructured'`), whereas
-        # the user-scope rows hold only structured sources with `postgres_<hash>`
-        # placeholder names and NO documents at all — so reading documents from
-        # user-scope always returned empty ("not listed"). Fall back to the
-        # user-scope reader only when the analysis has no catalog row (legacy /
-        # unbound room).
-        if self._analysis_id:
-            try:
-                catalog = await self._store.get_by_analysis(self._analysis_id)
-            except Exception:  # noqa: BLE001 — never block check on the analysis read
-                catalog = None
-            if catalog is not None:
-                return _filter_sources(catalog, source_hint)
-        return await self._inner.read(user_id, source_hint)
+        # No analysis_id at all → legacy room with no analysis-scope concept; fall
+        # back to the user-scope reader (unchanged behavior).
+        if not self._analysis_id:
+            return await self._inner.read(user_id, source_hint)
+
+        # Analysis-scoped room: read its OWN catalog. Analysis-scope rows carry the
+        # real DB names AND the room's documents (`source_type='unstructured'`),
+        # unlike the user-scope rows (`postgres_<hash>` names, no documents).
+        try:
+            catalog = await self._store.get_by_analysis(self._analysis_id)
+        except Exception as e:  # noqa: BLE001 — never block check on the analysis read
+            logger.warning(
+                "analysis catalog read failed — returning empty",
+                analysis_id=self._analysis_id,
+                error=str(e),
+            )
+            catalog = None
+
+        if catalog is not None:
+            logger.info(
+                "analysis catalog hit",
+                analysis_id=self._analysis_id,
+                sources=len(catalog.sources),
+            )
+            return _filter_sources(catalog, source_hint)
+
+        # analysis_id present but no catalog row (or read failed): return EMPTY,
+        # NOT the user-scope catalog. Surfacing user-scope sources here reads as
+        # "these are your bound sources" when they are not (the misleading-XLSX bug).
+        logger.info(
+            "analysis catalog miss — returning empty (no user-scope fallback)",
+            analysis_id=self._analysis_id,
+        )
+        return Catalog(user_id=user_id, generated_at=datetime.now(UTC))

src/traceability/resolve.py

@@ -0,0 +1,172 @@
+"""Resolve a query IR into the user-facing `DataUsed` record (KM-691).
+
+Pure, deterministic id->name resolution against the catalog — NO LLM. Turns the
+opaque IR the tools ran (column_id / table_id / source_id) into real names the
+user can check against their own database, and splits the result set into columns
+read straight from the data vs values the analysis computed.
+
+Never raises on the caller's path: an unresolved id degrades to showing the raw id
+(marked table='?'), never a crash — a trace slip must not break the user's answer.
+"""
+
+from __future__ import annotations
+
+from typing import Any
+
+from ..catalog.models import Catalog
+from ..query.ir.models import AggSelect, ColumnSelect, QueryIR
+from .schemas import (
+    ColumnRef,
+    DataUsed,
+    FilterRef,
+    JoinRef,
+    OrderByRef,
+    OutputColumn,
+    SourceRef,
+    TableRef,
+)
+
+
+class _CatalogIndex:
+    """Flat id->name lookups built once from a Catalog (ids are globally unique)."""
+
+    def __init__(self, catalog: Catalog) -> None:
+        self.source: dict[str, tuple[str, str | None]] = {}
+        self.table: dict[str, str] = {}
+        # column_id -> (table_name, column_name, data_type, pii_flag)
+        self.col: dict[str, tuple[str, str, str | None, bool]] = {}
+        for s in catalog.sources:
+            self.source[s.source_id] = (s.name, s.source_type)
+            for t in s.tables:
+                self.table[t.table_id] = t.name
+                for c in t.columns:
+                    self.col[c.column_id] = (t.name, c.name, c.data_type, c.pii_flag)
+
+    def col_name(self, column_id: str) -> str:
+        hit = self.col.get(column_id)
+        return hit[1] if hit else column_id
+
+    def col_qual(self, column_id: str) -> str:
+        """`table.column` when resolvable, else the raw id (honest fallback)."""
+        hit = self.col.get(column_id)
+        return f"{hit[0]}.{hit[1]}" if hit else column_id
+
+
+def _describe_filter(col: str, op: str, value: Any) -> str:
+    """Plain-language rendering of one filter (fixed templates — no LLM)."""
+    if op == "between" and isinstance(value, list | tuple) and len(value) == 2:
+        return f"{col} is between {value[0]} and {value[1]}"
+    if op == "in":
+        return f"{col} is one of {value}"
+    if op == "not_in":
+        return f"{col} is not one of {value}"
+    if op == "is_null":
+        return f"{col} is empty"
+    if op == "is_not_null":
+        return f"{col} is not empty"
+    if op == "like":
+        return f"{col} matches {value}"
+    return f"{col} {op} {value}"
+
+
+def resolve_data_used(
+    ir: QueryIR,
+    catalog: Catalog,
+    query: str | None = None,
+    rows_returned: int | None = None,
+) -> DataUsed:
+    """Resolve one `retrieve_data` IR into a `DataUsed`. Deterministic; no LLM."""
+    idx = _CatalogIndex(catalog)
+    src_name, src_type = idx.source.get(ir.source_id, (ir.source_id, None))
+
+    tables = [TableRef(id=ir.table_id, name=idx.table.get(ir.table_id, ir.table_id), role="base")]
+    joins: list[JoinRef] = []
+    for j in ir.joins:
+        ttid = j.target_table_id
+        tables.append(TableRef(id=ttid, name=idx.table.get(ttid, ttid), role="joined"))
+        left, right = idx.col_qual(j.left_column_id), idx.col_qual(j.right_column_id)
+        joins.append(JoinRef(type=j.type, condition=f"{left} = {right}"))
+
+    # roles[column_id] -> set of why-used tags, accumulated across every clause.
+    roles: dict[str, set[str]] = {}
+
+    def _tag(cid: str, role: str) -> None:
+        roles.setdefault(cid, set()).add(role)
+
+    output_columns: list[OutputColumn] = []
+    for s in ir.select:
+        if isinstance(s, ColumnSelect):
+            _tag(s.column_id, "selected")
+            output_columns.append(
+                OutputColumn(
+                    name=s.alias or idx.col_name(s.column_id),
+                    kind="column",
+                    from_=idx.col_qual(s.column_id),
+                )
+            )
+        elif isinstance(s, AggSelect):
+            if s.column_id:
+                _tag(s.column_id, "aggregated")
+                formula = f"{s.fn.upper()}({idx.col_qual(s.column_id)})"
+                frm = idx.col_qual(s.column_id)
+            else:
+                formula = f"{s.fn.upper()}(*)"  # count(*) carries no column
+                frm = None
+            output_columns.append(
+                OutputColumn(name=s.alias or formula, kind="computed", formula=formula, from_=frm)
+            )
+
+    filters: list[FilterRef] = []
+    for f in ir.filters:
+        _tag(f.column_id, "filtered")
+        col = idx.col_qual(f.column_id)
+        desc = _describe_filter(col, f.op, f.value)
+        filters.append(FilterRef(column=col, op=f.op, value=f.value, description=desc))
+
+    group_by: list[str] = []
+    for gid in ir.group_by:
+        _tag(gid, "grouped")
+        group_by.append(idx.col_qual(gid))
+
+    for j in ir.joins:
+        _tag(j.left_column_id, "joined")
+        _tag(j.right_column_id, "joined")
+
+    order_by: list[OrderByRef] = []
+    for o in ir.order_by:
+        # IR wart: order_by.column_id may hold a SELECT alias (a computed output),
+        # not a catalog column_id. Resolve as a real column when known, else treat
+        # it as a computed-output reference rather than inventing a name.
+        if o.column_id in idx.col:
+            _tag(o.column_id, "ordered")
+            order_by.append(OrderByRef(target=idx.col_qual(o.column_id), kind="column", dir=o.dir))
+        else:
+            order_by.append(OrderByRef(target=o.column_id, kind="computed", dir=o.dir))
+
+    columns_read: list[ColumnRef] = []
+    for cid, rs in roles.items():
+        hit = idx.col.get(cid)
+        if hit:
+            tname, cname, dtype, pii = hit
+            columns_read.append(
+                ColumnRef(
+                    id=cid, name=cname, table=tname, data_type=dtype, pii=pii, roles=sorted(rs)
+                )
+            )
+        else:
+            # Unresolved id — keep it, mark it, never guess a name.
+            columns_read.append(ColumnRef(id=cid, name=cid, table="?", roles=sorted(rs)))
+
+    return DataUsed(
+        source=SourceRef(id=ir.source_id, name=src_name, type=src_type),
+        tables=tables,
+        joins=joins,
+        columns_read=columns_read,
+        output_columns=output_columns,
+        filters=filters,
+        group_by=group_by,
+        order_by=order_by,
+        limit=ir.limit,
+        rows_returned=rows_returned,
+        query=query,
+    )

src/traceability/schemas.py

@@ -47,6 +47,10 @@ class ToolCallInfo(BaseModel):
     The field is spelled `input` in the wire contract (the FE reads it), which
     shadows the `input` builtin — hence the alias + `populate_by_name` so callers
     can pass `input=` on construction and `model_dump(by_alias=True)` emits `input`.
+
+    `summary` is a plain-English one-liner built from a fixed per-tool template
+    (never an LLM call) — the FE headline for this step; `input`/`output` stay raw
+    for the collapsible "technical details" layer.
     """
 
     model_config = ConfigDict(populate_by_name=True)
@@ -54,12 +58,113 @@ class ToolCallInfo(BaseModel):
     order: int
     task_id: str | None = None
     name: str
+    summary: str | None = None
     input_: dict[str, Any] = Field(default_factory=dict, alias="input")
     output: dict[str, Any] = Field(default_factory=dict)
     status: Literal["success", "error"] = "success"
     error: str | None = None
 
 
+# ---------------------------------------------------------------------------
+# `data_used` — the user-facing "what data did this analysis touch" layer.
+# All names are resolved from the catalog at build time (deterministic lookup,
+# no LLM). Every `id` field is MACHINE-ONLY — the FE must never render it; it
+# exists for click-through/linking, reconciliation, and audit. One DataUsed
+# entry per `retrieve_data` call.
+# ---------------------------------------------------------------------------
+
+
+class SourceRef(BaseModel):
+    """The data source a pull read from. `id` is machine-only (FE must not render)."""
+
+    id: str
+    name: str
+    type: str | None = None
+
+
+class TableRef(BaseModel):
+    """A table the query touched. `id` is machine-only. `role`: base | joined."""
+
+    id: str
+    name: str
+    role: str
+
+
+class JoinRef(BaseModel):
+    """A join, rendered in real names, e.g. condition='order_items.order_id = orders.id'."""
+
+    type: str
+    condition: str
+
+
+class ColumnRef(BaseModel):
+    """A real catalog column the query read. `id` is machine-only (FE must not render).
+
+    `roles` records why it was used: selected | aggregated | filtered | grouped |
+    joined | ordered. `table` is the real table name for qualification.
+    """
+
+    id: str
+    name: str
+    table: str
+    data_type: str | None = None
+    pii: bool = False
+    roles: list[str] = Field(default_factory=list)
+
+
+class OutputColumn(BaseModel):
+    """A column in the result set.
+
+    `kind='column'` — read straight from the data (has a real `from`).
+    `kind='computed'` — calculated; carries a `formula`, and has NO catalog id
+    because it is not a stored column (e.g. total_revenue = SUM(line_total)).
+    `from` is spelled with an alias (Python keyword) — populate_by_name lets
+    callers pass `from_=`.
+    """
+
+    model_config = ConfigDict(populate_by_name=True)
+
+    name: str
+    kind: Literal["column", "computed"]
+    from_: str | None = Field(default=None, alias="from")
+    formula: str | None = None
+
+
+class FilterRef(BaseModel):
+    """A filter, resolved to a real column plus a plain-language `description`."""
+
+    column: str
+    op: str
+    value: Any = None
+    description: str
+
+
+class OrderByRef(BaseModel):
+    """A sort. `target` is a real column name (kind='column') OR a computed
+    output alias (kind='computed' — the IR stores the alias in `column_id`)."""
+
+    target: str
+    kind: Literal["column", "computed"]
+    dir: str = "asc"
+
+
+class DataUsed(BaseModel):
+    """One `retrieve_data` pull, fully resolved for the user. Names for display,
+    ids for machine linkage only (FE must not render any `id`)."""
+
+    source: SourceRef
+    tables: list[TableRef] = Field(default_factory=list)
+    joins: list[JoinRef] = Field(default_factory=list)
+    columns_read: list[ColumnRef] = Field(default_factory=list)
+    output_columns: list[OutputColumn] = Field(default_factory=list)
+    filters: list[FilterRef] = Field(default_factory=list)
+    group_by: list[str] = Field(default_factory=list)
+    order_by: list[OrderByRef] = Field(default_factory=list)
+    limit: int | None = None
+    rows_returned: int | None = None
+    query: str | None = None
+
+
 class TraceabilityPayload(BaseModel):
     """The full provenance record for one assistant `message_id`."""
 
@@ -73,4 +178,5 @@ class TraceabilityPayload(BaseModel):
     planning: PlanningInfo | None = None
     thinking: str | None = None
     tool_calls: list[ToolCallInfo] = Field(default_factory=list)
+    data_used: list[DataUsed] = Field(default_factory=list)
     sources: list[dict[str, Any]] = Field(default_factory=list)

src/traceability/scratchpad.py

@@ -87,6 +87,31 @@ def _meta_of(output: Any) -> dict[str, Any]:
     return meta if isinstance(meta, dict) else {}
 
 
+def _summarize(name: str, out_dict: dict[str, Any], meta: dict[str, Any]) -> str:
+    """One plain-English line per tool step (fixed templates — never an LLM call)."""
+    rc = out_dict.get("row_count")
+    cols = out_dict.get("columns")
+    ncol = len(cols) if isinstance(cols, list) else None
+    if name == "check_data":
+        return "Inspected your data source structure"
+    if name == "retrieve_data":
+        parts = "Retrieved"
+        if rc is not None:
+            parts += f" {rc} rows"
+        if ncol:
+            parts += f" across {ncol} columns"
+        table = meta.get("table_name")
+        if table:
+            parts += f" from {table}"
+        return parts
+    if name == "retrieve_knowledge":
+        return f"Searched your documents ({rc if rc is not None else 0} passages found)"
+    if name.startswith("analyze_"):
+        pretty = name.removeprefix("analyze_").replace("_", " ")
+        return f"Ran {pretty} analysis on {ncol} columns" if ncol else f"Ran {pretty} analysis"
+    return name.replace("_", " ")
+
+
 class TraceabilityScratchpad:
     """Mutable per-request accumulator; `build()` freezes it into a payload."""
 
@@ -98,10 +123,18 @@ class TraceabilityScratchpad:
         self._db_sources: list[dict[str, Any]] = []
         self._doc_sources: list[dict[str, Any]] = []
         self._doc_seen: set[tuple[Any, Any]] = set()
+        self._catalog: Any = None  # set by set_catalog: enables id->name resolution
+        self._retrieve_calls: list[dict[str, Any]] = []  # raw retrieve_data IRs + meta
 
     def set_intent(self, intent: str) -> None:
         self.intent = intent
 
+    def set_catalog(self, catalog: Any) -> None:
+        """Provide the catalog used this turn so `build()` can resolve the IR ids in
+        each retrieve_data call to real names (the `data_used` layer). No-op-safe:
+        without it, `data_used` stays empty and the raw tool_calls still carry the IR."""
+        self._catalog = catalog
+
     def record_tool_call(
         self,
         name: str,
@@ -118,6 +151,7 @@ class TraceabilityScratchpad:
                 order=len(self._tool_calls) + 1,
                 task_id=task_id,
                 name=name,
+                summary=_summarize(name, out_dict, _meta_of(output)),
                 input=_truncate(dict(args)),
                 output=out_dict,
                 status=status,
@@ -126,6 +160,26 @@ class TraceabilityScratchpad:
         )
         if name == "retrieve_data" and status == "success":
             self._record_db_source(output)
+            self._capture_retrieve(args, output)
+
+    def _capture_retrieve(self, args: Any, output: Any) -> None:
+        """Stash the raw retrieve_data IR + provenance meta so `build()` can resolve a
+        `DataUsed`. Gated on the SAME `meta.source_id` check as `_record_db_source`, so
+        the two stay index-aligned (the db source was just appended)."""
+        meta = _meta_of(output)
+        if not meta.get("source_id"):
+            return
+        ir = args.get("ir") if isinstance(args, dict) else None
+        if not isinstance(ir, dict):
+            return
+        query = meta.get("query")
+        self._retrieve_calls.append({
+            "ir": ir,
+            "query": query[:CAP_QUERY] if isinstance(query, str) else None,
+            "source_name": meta.get("source_name"),
+            "row_count": meta.get("row_count"),
+            "db_source_index": len(self._db_sources) - 1,
+        })
 
     def _record_db_source(self, output: Any) -> None:
         # retrieve_data's args are {"ir": ...}; the reliable source_id/table/query
@@ -196,10 +250,34 @@ class TraceabilityScratchpad:
                 source["score"] = score
             self._doc_sources.append(source)
 
+    def _build_data_used(self) -> list[Any]:
+        """Resolve each captured retrieve_data IR into a `DataUsed` (real names) and
+        enrich the matching db source with source_name + all tables touched. Never
+        raises — a resolution slip drops that entry, never breaks the answer."""
+        if self._catalog is None or not self._retrieve_calls:
+            return []
+        from ..query.ir.models import QueryIR
+        from .resolve import resolve_data_used
+
+        out: list[Any] = []
+        for rc in self._retrieve_calls:
+            try:
+                ir = QueryIR.model_validate(rc["ir"])
+                du = resolve_data_used(ir, self._catalog, rc.get("query"), rc.get("row_count"))
+                out.append(du)
+                idx = rc.get("db_source_index")
+                if isinstance(idx, int) and 0 <= idx < len(self._db_sources):
+                    self._db_sources[idx]["source_name"] = rc.get("source_name")
+                    self._db_sources[idx]["tables"] = [t.name for t in du.tables]
+            except Exception as exc:  # never break the answer on a resolve slip
+                logger.warning("data_used resolve failed", error=str(exc))
+        return out
+
     def build(self, analysis_id: str, user_id: str, message_id: str) -> TraceabilityPayload:
         """Freeze the accumulated state into a `TraceabilityPayload`."""
         from datetime import UTC, datetime
 
+        data_used = self._build_data_used()  # also enriches self._db_sources in place
         return TraceabilityPayload(
             analysis_id=analysis_id,
             message_id=message_id,
@@ -209,6 +287,7 @@ class TraceabilityScratchpad:
             planning=self._planning,
             thinking=None,
             tool_calls=list(self._tool_calls),
+            data_used=data_used,
             sources=self._doc_sources + self._db_sources,
         )