# Plan: Conversational Data Adjustment as MCP Tools **Status:** Phase 1 in progress — resolution spine and detector PROVEN on real boards **Date:** 2026-10-07 **Context:** AgentSpot (Agent Q) calls our MCP server to build demos. This adds a second capability: adjusting the *data* behind a built demo, conversationally, after the fact. --- ## Build this first: the spine Everything depends on one walk. The user names a liveboard and a chart; we have to get from there to a table and two columns. ``` liveboard URL or GUID -> liveboard name + viz list by name load_liveboard_context() HAVE -> viz name -> dimension + measure cols liveboard TML, export_fqn=True GAP -> model GUID -> db + schema model TML chain (CLAUDE.md) HAVE -> tables + columns _discover_schema() HAVE -> connection -> logical tables tools/ts_connection_tables.py HAVE ``` **Only one link is missing.** `load_liveboard_context()` reads `visualization_headers`, which carries viz id and name but nothing about what the chart plots. The liveboard TML export (`export_fqn=True`) has it, and the build pipeline already exports TML this way elsewhere — it just is not wired into the adjuster. ### Two tools, to start | Tool | Returns | |---|---| | `list_board_charts` / `inspect_board_data` **(built)** | the whole walk in one call: table, dimension column, measure column, current values per category, and whether the split looks synthetic | | `adjust_viz(...)` | changes those values, total preserved, snapshot taken first | That is the minimum that makes "the chart named XYZ is too flat, fix it" work end to end. `inspect_viz` is also what lets AgentSpot hold a conversation — it can list the charts, say what each one plots, and confirm before changing anything. ### Shapes are an internal detail `spread` / `variance` / `seasonal` / `spike` are *how* `adjust_viz` reshapes, not separate tools. Start with one behavior — make near-identical values look real — and add the rest underneath without changing the tool surface. --- ## Verified 2026-10-12 The resolution chain works and needs no intelligence to walk it: ``` viz.answer.search_query -> [Net Revenue Usd] [Product Line Name] ... model.columns[].column_id -> CUSTOMER_VALUE_DELIVERED::NET_REVENUE_USD properties.column_type: MEASURE | ATTRIBUTE properties.aggregation: SUM | AVERAGE | ... model.model_tables[].joins[].on -> the real join predicate table TML -> db + schema ``` `column_type` states outright whether a column is a measure or an attribute, and the joins are given explicitly — so `_find_fact_join()`'s heuristics and the LLM parse in `match_request_to_viz()` are both unnecessary on this path. **Gotcha:** TML `aggregation: AVERAGE` is not a Snowflake function. Map it (`AVERAGE`->`AVG`, `UNIQUE_COUNT`->`COUNT(DISTINCT ...)`). ### The flat charts are all rate measures Sweep of 6 boards, 66 category charts: | verdict | measure kind | count | |---|---|---| | PASS | additive (SUM/COUNT) | 48 | | PASS | rate/avg | 3 | | **FAIL (too even)** | **rate/avg** | **13** | | UNKNOWN | rate/avg | 2 | No additive measure failed. The generator spreads sums realistically and produces rates with almost no cross-category differentiation — e.g. `CTR by Channel` at 5.1 / 5.0 / 4.8 / 4.6 / 4.5% across seven channels. That makes the 33/33/33 complaint a **rate** problem. Worth deciding whether the durable fix belongs in the generator rather than only in a repair tool. --- ## The ask > "Look, the data is really flat here — adjust it a little to make that > visualization interesting." boone does this today by hand, going in behind a run with an agent and a database connection. The goal is to make it a first-class loop: AgentSpot talks to tools we host, and those tools reshape the data. ## The reframe The 2025-era `smart_chat.py` built its own chat loop because there was nothing to delegate to. **AgentSpot is the chat loop now.** We expose stateless tools with good descriptions; AgentSpot handles turn management, asks clarifying questions, and passes structured arguments. The LLM parsing inside `smart_data_adjuster.py` (`_call_llm`, `_parse_request_simple`) becomes dead code on this path. --- ## What already exists | Piece | Where | State | |---|---|---| | Flatness detection **over time** | `trend_shape()` — `tests/demo_checks.py:469` | **Works.** Thresholds tuned: `cv < 0.05` flat, `jitter > 0.35` chaotic, no outliers → warn | | Flatness **across categories** | — | **Missing.** Nothing detects a pie/bar split 33/33/33. See below | | Viz-name → table/column resolution | `match_request_to_viz()` — `smart_data_adjuster.py:302` | Works — lets a user name the chart by title | | GUID → db/schema resolution | `load_liveboard_context()` — `smart_data_adjuster.py:141` | Works (liveboard TML → model GUID → db/schema) | | Fact/dim join discovery | `_find_fact_join()` — `smart_data_adjuster.py:213` | Works | | Current value lookup | `get_current_value()` — `smart_data_adjuster.py:350` | Works | | SQL execution | `execute_sql()` — `smart_data_adjuster.py:503` | Works, commits immediately, no snapshot | | MCP tool scaffolding | `mcp_server.py` | Works — bearer gate, `@mcp.tool()`, background workers | | Terminal chat loop | `smart_chat.py` | **Discard.** Superseded by AgentSpot | ## The primary case: flat across categories > "On the graph XYZ on liveboard ABC the data is too flat — can we add some > variance?" > > If all three values are exactly 33%, that doesn't look real. This is the headline use case, and it is **not** what `trend_shape()` measures. `trend_shape()` groups by month and asks whether a line moves over time. A pie chart split 33/33/33 passes it untouched. Needs a second detector: measure grouped by dimension, shares compared against uniform. If the split is near-even across few categories, it reads as synthetic. The fix is a realistic share distribution — real business data is long-tailed, not uniform. Three categories should land around 48/31/21. Same total, so no other tile on the liveboard moves. ## What does not exist — the important gap `generate_strategy()` produces exactly one strategy: **"Scale All Transactions"**, a uniform multiplier. ```sql SET {metric} = {metric} * {multiplier} WHERE {fk} IN (SELECT ...) ``` **A uniform multiplier on a flat series leaves it flat.** It moves the level, not the shape. The core ask — "add some variance" — is a genuinely new operation, not a wrapper over the existing engine. --- ## Design ### Reshape operations One new primitive, four shapes. **All preserve the grand total.** | Shape | Behavior | |---|---| | `spread` | **(primary)** reshape shares across categories to a realistic long tail — kills 33/33/33 | | `variance` | redistribute over time to hit a target coefficient of variation | | `trend` | monotonic drift up or down across periods | | `seasonal` | repeating cycle | | `spike` | plant N outliers at chosen or auto-picked periods | `preserve_total=True` is the key safety property: if the sum is invariant, KPI tiles do not move — only trend tiles do. That is both the desired behavior (the total was fine, the line was boring) and a hard ceiling on blast radius. AgentSpot picks the shape conversationally. It cannot invent a shape we have not implemented, which is why the set is fixed here. ### Undo — Snowflake zero-copy clone ```sql CREATE TABLE _PREADJ__ CLONE
; -- snapshot CREATE OR REPLACE TABLE
CLONE _PREADJ_
_; -- undo ``` Clone is metadata-only — no storage cost until the tables diverge, and these demo schemas are small regardless. Because it is effectively free, snapshot before **every** adjustment and keep a stack, so "undo that last one" works and not just "put it all back." Preferred over Time Travel (`AT(OFFSET => -600)`) because Time Travel depends on `DATA_RETENTION_TIME_IN_DAYS` and the account edition, neither of which has been checked. The explicit clone does not. **Unverified:** whether ThoughtSpot serves cached data after an undo. The Snowflake side is clean (writes go to the fact table directly); the TS side needs a real test. ### Blast radius — "it accidentally adjusts other things" Two distinct causes, two distinct mitigations: 1. **Wrong rows matched.** `_fuzzy_match` hits something unintended. → Preview reports rows matched and the resolved entity name before writing. Half of this is already in `present_smart_confirmation()`. 2. **Derived measures drift.** If `revenue ≈ qty × price` is stored, scaling `revenue` alone breaks the relationship and some *other* tile that computes it differently now disagrees. Same family as the known zero-measure defect. → Preview samples ~200 rows, tests for stable ratios against other measures on the same row, and warns: "`unit_price` tracks this column — it will go out of sync." ### Tool surface | Tool | Writes? | Returns | |---|---|---| | `describe_dataset(liveboard_guid)` | no | fields, measures, and a `trend_shape` verdict per measure — AgentSpot sees "flat" without being told | | `preview_adjustment(...)` | **no** | rows matched, before/after, related-measure warnings, `preview_id` | | `apply_adjustment(preview_id)` | yes | snapshots, then executes | | `undo_last(liveboard_guid)` | yes | pops the clone stack | Preview/apply split is what makes "up this a little" safe: AgentSpot shows 40B → 46B and gets a yes before anything touches Snowflake. ### Concurrency **Do not put these on `_build_sem`.** A build is 15–20 minutes; an adjust is seconds. Sharing the semaphore would make adjustments consume build slots for no reason. Instead: a **per-schema lock**. One adjust at a time per demo schema, unlimited across schemas. Two concurrent adjusts on the same table race, and the clone stack becomes incoherent. This adds no load ceiling to the build path. --- ## Phases **Phase 1 — the spine** - [x] Close the viz-name -> columns gap — `search_query` brackets + model `column_id` (TABLE::COLUMN). Deterministic, no fuzzy matching. - [x] `inspect_viz()` — `viz_inspector.py`. **131/131 vizzes resolved across 6 real boards, 0 unresolved, 0 SQL errors.** - [x] Categorical flatness check — proven, flags 13 real charts - [ ] One reshape behavior: near-identical values -> realistic spread, total held **(this is the first thing that writes — snapshot lands with it)** - Snapshot/undo via clone - Reuse `trend_shape()` to verify: flat in, PASS out - Test against an existing demo schema **Phase 2 — safety and more shapes** - `variance` / `seasonal` / `spike` under the same tool surface - Related-measure ratio detection - Preview/apply split with `preview_id` handshake - Per-schema locking **Phase 3 — MCP tools** - [x] `list_board_charts` + `inspect_board_data` in `mcp_server.py` — read-only, no build slot taken. Tested against Beone Medicines: 19 charts checked, 3 flagged, GUID/URL input and all error paths verified. - [ ] `adjust_viz` wrapper (lands with the reshape) - Tool descriptions written for an agent audience, not a human one - Direct testing against our MCP server (not through AgentSpot — conversations cost DO credits) **Phase 4 — deploy** - `hf-mcp-test` first. Always. Health endpoint is not proof — call `ping`, then run a real adjust against test. - Prod only when boone raises it. --- ## Open items - Branch to work on — the documented three-branch model has drifted from reality (see CLAUDE.md reality check). Needs a deliberate call, not an assumption. - Does TS cache survive an undo? Test in Phase 1. - Does AgentSpot need org-memory instructions to use these tools well, and is that safe given org memory is org-wide on shared SE clusters? ## Settled - **AgentSpot connectivity is not a risk.** Agent Q has called our bearer-gated MCP server in production since late July 2026. New tools on the same server are reachable through the same connector with no auth work. - **User identity does not matter.** All writes run as the Snowflake service account. No per-user credential handling on this path. - **`trend_shape()` stays in `tests/`** and is imported by the adjust code. Slightly odd import direction; it can move if it ever becomes a problem. What matters is that the grader and the adjuster share one definition of "flat."