mcp / docs /PLAN_data_adjust_agent_tools.md
mikeboone's picture
feat(mcp): inspect and repair flat category data from AgentSpot
6a8d963
|
Raw History Blame Contribute Delete
12.6 kB
# 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_<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**
- [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."