docs/PLAN_data_adjust_agent_tools.md

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.

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

CREATE TABLE _PREADJ_<TABLE>_<n> CLONE <TABLE>;              -- snapshot
CREATE OR REPLACE TABLE <TABLE> CLONE _PREADJ_<TABLE>_<n>;   -- 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

• Close the viz-name -> columns gap — search_query brackets + model column_id (TABLE::COLUMN). Deterministic, no fuzzy matching.
• inspect_viz() — viz_inspector.py. 131/131 vizzes resolved across 6 real boards, 0 unresolved, 0 SQL errors.
• 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

• 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."