ts-object-model-coach

name

ts-object-model-coach

description

Prepare a ThoughtSpot Model to work well with Spotter. Use when you want to improve how a Model responds to natural language queries — reviewing and writing AI context, synonyms, reference questions, business terms, and data model instructions. Mines real language from existing Liveboards, Answers, and optionally Snowflake query history to ground the coaching in how users actually talk about the data.

ThoughtSpot: Coach a Model (Comprehensive Spotter Preparation)

Spotter accuracy depends on five distinct coaching surfaces working together. Most teams curate them ad-hoc, in isolation, and let them drift over time. This skill produces all five from the same evidence base — Model schema, dependent Liveboards/Answers, analyst prose, and (optionally) Snowflake query history — and reviews any existing content critically rather than blindly adding more.

The five Spotter coaching surfaces:

#	Surface	Where it lives	What it does
1	Column AI Context	model.columns[].properties.ai_context (structured YAML — closed enums + refs only; ≤ 400 chars)	Declares the constraints downstream LLMs need to write correct SQL: additivity, time_basis, source, grain_keys. See ai-context-schema.md. Prose context lives in column.description.
2	Column Synonyms	model.columns[].synonyms[] (array, per column)	Schema-level alternative names — used by the parser
3	Reference Questions	nls_feedback.feedback[type=REFERENCE_QUESTION]	Per-question NL → tokenised search mappings
4	Business Terms	nls_feedback.feedback[type=BUSINESS_TERM]	Coaching-layer phrase → column/formula mappings
5	Data Model Instructions	Model-level free text — TML location TBD (see open-items.md #4)	Global rules ("when I say last month, use last 30 days")

Research-backed defaults:

• Reference Question target: ~15 to start (Snowflake's "10–20 covering common questions").
• Mix simple aggregations with complex joins/ratios — "Simple queries may not have as much useful information" (Cortex optimization).
• Existing AI assets are critiqued + improved, never silently overwritten.
• The user picks which surfaces to generate via a single up-front menu.

Ask one question at a time. Wait for each answer before proceeding.

---

References

File	Purpose
references/open-items.md	Unverified API behaviours; Data Model Instructions TML location
references/question-taxonomy.md	Deterministic candidate-question patterns (T1–T4) and ranking
references/token-mapping-rules.md	NL → search_tokens translation; paraphrase variants; chart_type/display_mode inference
references/prose-mining-rules.md	How to extract business phrases from Model description, Answer/Liveboard prose, tile names
references/ai-asset-review-rules.md	Critique heuristics for existing ai_context / synonyms / description
references/synonym-strategy-explainer.md	Inline explainer for column synonyms vs BUSINESS_TERM coaching (shown to the user)
references/review-explainers.md	3-section explainer blocks (purpose / signals checked / outcome rules) prepended to every Step 7 review file
references/cross-model-consistency.md	Cross-Model column collision detection — purpose, signals, decision tree, output format (powers Step 4.5)
references/feedback-tml-verified-patterns.md	Verified nls_feedback syntax patterns (search_tokens shapes, chart_type/display_mode values, axis_config notation) mined from real coached Models — authoritative reference for Step 6 + Step 8c generation
../ts-profile-thoughtspot/SKILL.md	ThoughtSpot auth, profile config
Cortex Code connection (configured via cortex connections set)	Snowflake auth (optional)
../../shared/schemas/thoughtspot-feedback-tml.md	Coaching TML structure (output for surfaces 3 + 4)
../../shared/schemas/thoughtspot-model-tml.md	Model TML structure — ai_context, synonyms, description field locations (output for surfaces 1 + 2)
../../shared/schemas/thoughtspot-answer-tml.md	Answer TML — name, description, search_query mining input
../../shared/schemas/thoughtspot-liveboard-tml.md	Liveboard TML — visualization names + tile descriptions for prose mining
../../shared/schemas/thoughtspot-formula-patterns.md	Formula syntax for answer-level formula generation
../../shared/mappings/ts-snowflake/ts-snowflake-formula-translation.md	SQL → TS formula translation (mandatory read for SQL-derived candidates)

---

Prerequisites

• ThoughtSpot profile configured — run /ts-profile-thoughtspot if not
• Snowflake profile configured (optional, only for query-history mining) — /ts-profile-snowflake
• ts CLI installed: pip install -e tools/ts-cli
• Python: pip install pyyaml
• ThoughtSpot user must have MODIFY or FULL access on the target Model

---

Step 0 — Overview

On skill invocation, display:

---

ts-object-model-coach — comprehensively prepare a Model for Spotter: review existing AI context/synonyms/description, mine dependent objects + (optionally) Snowflake history, then generate your chosen mix of column AI Context, Synonyms, Reference Questions, Business Terms, and a Data Model Instructions draft.

Steps:

1. Authenticate and pick the Model ........................ you choose
2. Export Model TML; extract schema and existing AI assets . auto
3. Mine candidate sources (Liveboards/Answers, prose, Snowflake) . auto
4. Review existing AI assets — produce critique + deltas .. auto 4.5 Cross-Model consistency scan — flag column-name collisions across other Models . auto
5. Show critique; pick which surfaces to generate ......... you confirm
6. Generate proposals per selected surface ................. auto
7. Per-surface review with explainer blocks (purpose / signals / outcome rules) . you confirm
8. Build merged TML, backup, final import gate ............. you confirm
9. Import + smoke test ..................................... auto

Confirmation required: Steps 1, 5, 7, 8 Auto-executed: Steps 2, 3, 4, 4.5, 6, 9

Ready to start? [Y / N]

Do not begin Step 1 until the user confirms.

---

Step 1 — Authenticate and Pick the Model

Read ~/.claude/thoughtspot-profiles.json. Prompt for profile if multiple exist; confirm the single profile if exactly one.

source ~/.zshenv && ts auth whoami --profile "{profile_name}"

Save {base_url} (strip trailing slash) and {profile_name}.

Pick the Model — accept --guid or prompt to search:

source ~/.zshenv && ts metadata search \
  --subtype WORKSHEET --name "%{search_term}%" --profile "{profile_name}"

Mark each result [MODEL] or [WORKSHEET] using metadata_header.contentUpgradeId / worksheetVersion (same logic as ts-object-answer-promote Step 5). This skill targets Models only — if a Worksheet is selected, tell the user it must be upgraded to a Model first (no ThoughtSpot skill for this exists yet — a ts-object-model-builder skill is planned but not shipped) and stop.

Display format. Show results as a markdown table with columns # | Name | Owner | GUID | Modified. The Owner column is the metadata_header.authorDisplayName (fall back to authorName if the display name is absent). On shared instances, name collisions across authors are common; without the Owner column the user often picks the wrong object.

Save {model_guid} and {model_name}.

Optional Snowflake profile. Ask:

Mine the underlying Snowflake query history for real-world question patterns? (Y / N)
(requires a Snowflake profile with ACCOUNT_USAGE access; defaults to N)

If Y, prompt for Snowflake profile name and save as {sf_profile_name}.

Create the run directory:

import time, pathlib
run_dir = pathlib.Path.home() / "Dev" / "coaching-runs" / f"{slug(model_name)}-{int(time.time())}"
run_dir.mkdir(parents=True, exist_ok=True)

---

Step 2 — Export Model TML; Extract Schema and Existing AI Assets

Export the Model bundle:

source ~/.zshenv && ts tml export {model_guid} \
  --profile "{profile_name}" --fqn --associated --parse > {run_dir}/model_bundle.json

Parse and extract two structured outputs:

2a. Schema (drives candidate generation in Steps 3 + 6)

import json
data = json.loads(open(run_dir/"model_bundle.json").read())

model = next(i["tml"]["model"] for i in data if i["type"] == "model")
columns = model.get("columns", [])
formulas = model.get("formulas", [])

# Classify columns. The Model TML may not populate properties.data_type for every
# column — fall back to NAME PATTERN for date detection (e.g. "Transaction Date",
# "Order Date" → date dim even when data_type is None).
import re
DATE_NAME_RE = re.compile(r'\b(date|datetime|timestamp|time|day|month|quarter|year)\b', re.I)
measures   = [c for c in columns if c.get("properties",{}).get("column_type") == "MEASURE"]
attributes = [c for c in columns if c.get("properties",{}).get("column_type") == "ATTRIBUTE"]
date_dims  = [c for c in attributes if DATE_NAME_RE.search(c["name"]) or c.get("properties",{}).get("data_type") in ("DATE","DATE_TIME","TIMESTAMP")]
non_date_attrs = [c for c in attributes if c not in date_dims]

Joins live at the physical-table level (table_tml.table.joins_with), not on model.model_tables[].joins_with (which is empty in most exports). Aggregate them:

table_items = [i for i in data if i["type"] == "table"]
joins = []
for t in table_items:
    for j in t["tml"]["table"].get("joins_with", []):
        joins.append({
            "from_table": t["tml"]["table"]["name"],
            "to_table":   j.get("destination", {}).get("name"),
            "type":       j.get("type"),
        })
# A dim is a "join key" (D_join in scoring) when its table is a join target for ≥2 tables.
join_target_counts = {}
for j in joins:
    join_target_counts[j["to_table"]] = join_target_counts.get(j["to_table"], 0) + 1

2b. Existing AI assets (drives the critique in Step 4)

Pull two categories of existing assets:

1. Model TML assets (model.description, columns[].properties.ai_context, columns[].properties.synonyms[]) — read directly from the bundle parsed in 2a.
2. Existing feedback entries — NOT in the bundle. ts tml export --associated does not surface nls_feedback (verified). ts tml export --type FEEDBACK is also not supported — the API returns HTTP 400 when a model GUID is passed with type=FEEDBACK (verified 2026-05-11). The correct approach is to locate the feedback object's own GUID via ts metadata dependents, then export it directly:

import json, subprocess

# Step 1: find the feedback object GUID via dependents
dep_result = subprocess.run(
    ["bash", "-c",
     f"source ~/.zshenv && ts metadata dependents {model_guid} --raw --profile '{profile_name}'"],
    capture_output=True, text=True,
)
dep_body = json.loads(dep_result.stdout) if dep_result.stdout.strip() else []
deps_node = (dep_body[0].get("dependent_objects", {})
                        .get("dependents", {})
                        .get(model_guid, {})) if dep_body else {}
feedback_deps = deps_node.get("FEEDBACK", []) or []
feedback_guids = [d.get("id") or d.get("metadata_id") for d in feedback_deps
                  if d.get("id") or d.get("metadata_id")]

# Step 2: export each feedback GUID (no --type flag needed)
all_fb_entries = []
for fb_guid in feedback_guids:
    fb_result = subprocess.run(
        ["bash", "-c",
         f"source ~/.zshenv && ts tml export {fb_guid} --parse --profile '{profile_name}'"],
        capture_output=True, text=True,
    )
    fb_body = json.loads(fb_result.stdout) if fb_result.stdout.strip() else []
    if fb_body:
        fb_payload = fb_body[0].get("tml", {})
        entries = fb_payload.get("nls_feedback", {}).get("feedback", []) or []
        all_fb_entries.extend(entries)

# Full content available: search_tokens, formula_info, access, chart_type,
# display_mode, parent_question, rating, axis_config, etc.
fb_global = [e for e in all_fb_entries if e.get("access") == "GLOBAL"]
fb_user   = [e for e in all_fb_entries if e.get("access") != "GLOBAL"]

If no FEEDBACK dependents are found, all_fb_entries will be empty — proceed normally with zero feedback entries (the Step 5 critique will note this and suggest generating new content).

existing = {
    "model_description": model.get("description", "").strip(),
    "spotter_enabled":   model.get("properties",{}).get("spotter_config",{}).get("is_spotter_enabled", False),
    "columns_with_ai_context": [(c["name"], c["properties"]["ai_context"])
                                 for c in columns if c.get("properties",{}).get("ai_context")],
    "columns_with_synonyms":   [(c["name"], c.get("properties",{}).get("synonyms", []),
                                 c.get("properties",{}).get("synonym_type", ""))
                                 for c in columns if c.get("properties",{}).get("synonyms")],
    "existing_feedback_global": fb_global,
    "existing_feedback_user":   fb_user,
}
existing_entries = fb_global + fb_user   # flat list — used by Steps 8a, 8c, 9c

Persist both to {run_dir}/schema.json and {run_dir}/existing_assets.json.

---

Step 3 — Mine Candidate Sources

Three sub-steps — run all that apply.

3a. Dependent Liveboards/Answers via verified v2 API

Dependent Liveboards/Answers are not a flag on ts metadata search — use ts metadata dependents, the verified API contract documented in this skill's open-items.md #1 (independently verified for the ts-dependency-manager skill on the wip/ts-dependency-manager branch) — fast (~2s), VERIFIED on Cloud:

import json, subprocess

result = subprocess.run(
    ["bash", "-c",
     f"source ~/.zshenv && ts metadata dependents {model_guid} --raw --profile '{profile_name}'"],
    capture_output=True, text=True,
)
body = json.loads(result.stdout)
deps_node  = body[0].get("dependent_objects",{}).get("dependents",{}).get(model_guid, {})
answers    = deps_node.get("QUESTION_ANSWER_BOOK", []) or []
liveboards = deps_node.get("PINBOARD_ANSWER_BOOK", []) or []

Then export each dependent's TML and harvest:

• answer.search_query (tokenised — feeds taxonomy ranking)
• answer.name, answer.description, answer.dynamic_name, answer.dynamic_description
• For Liveboards: liveboard.name, liveboard.description, each liveboard.visualizations[].answer.name and per-tile description (these are the highest signal — analysts label tiles in business language)

Save all mined prose to {run_dir}/mined_prose.json and the search_queries to {run_dir}/mined_searches.json. The two have separate downstream consumers.

3b. Snowflake query history (optional)

If {sf_profile_name} is set AND the Model is Snowflake-backed (check table_items[].tml.table.connection.type == "SNOWFLAKE"):

Use QUERY_PARAMETERIZED_HASH to group queries by structure (different literals collapse to the same hash). Filter out internal ThoughtSpot tasks (SAGE_INDEXING, SAGE_SAMPLING, A3*) by filtering on the comment block:

WITH ranked AS (
  SELECT QUERY_PARAMETERIZED_HASH,
         ANY_VALUE(QUERY_TEXT) AS sample_query,
         COUNT(*) AS run_count
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE QUERY_TEXT ILIKE '%{db}.{schema}.DM_%'
    AND START_TIME >= DATEADD('day', -90, CURRENT_TIMESTAMP())
    AND EXECUTION_STATUS = 'SUCCESS' AND QUERY_TYPE = 'SELECT'
    AND USER_NAME NOT LIKE 'ETL_%'
    AND CONTAINS(UPPER(QUERY_TEXT), 'GROUP BY')
    AND NOT QUERY_TEXT ILIKE '%task: SAGE_%'
    AND NOT QUERY_TEXT ILIKE '%task: A3%'
  GROUP BY QUERY_PARAMETERIZED_HASH
)
SELECT * FROM ranked WHERE run_count >= 2 ORDER BY run_count DESC LIMIT 30;

Real-world finding: demo / TS-fronted Snowflake accounts often have zero useful patterns — the workload is dominated by ThoughtSpot's own indexing. If the result is empty, log it and proceed with mined prose + schema only. Don't error.

3c. Prose mining

Per prose-mining-rules.md: extract noun phrases from all mined prose (model description, Answer/Liveboard names + descriptions, tile names), match them against Model column display names with stem overlap, and emit two outputs:

• Synonym candidates per column — phrases that look like alternative names ("inventory levels" ↔ [Inventory Balance])
• Question seeds — full sentences that read like business questions ("growth in sales amounts" → seed a YoY question with that phrasing)

Save to {run_dir}/mined_prose_extract.json.

3d. Existing GLOBAL feedback as input signal

Already-curated access: GLOBAL Reference Questions and Business Terms on the Model are the highest-quality input available — an analyst has explicitly promoted them for shared use. They feed:

• Paraphrase variant generation (Step 6.3) — existing feedback_phrase strings are pre-validated NL phrasings; reuse them as variants for any new question with matching search_tokens.
• Synonym proposal validation (Step 6.2) — existing BUSINESS_TERM entries show which phrase→column mappings the team has already accepted; don't propose competitors.
• Ranking (per question-taxonomy.md) — patterns that share search_tokens shape with an existing GLOBAL entry get +4 (signals an important measure×dim combination); identical feedback_phrase matches drop the candidate and mark the existing entry as KEEP.

access: USER entries are excluded from input signal by default — they're private and may be unreviewed. The user opts them in via the Step 5 scope menu prompt (see ai-asset-review-rules.md §4a).

# Already extracted in Step 2b — just compile the per-pattern lookup
import re
def tokens_of(s):
    return set(re.findall(r'\[([^\]]+)\]', s.lower()))

global_token_shapes = [tokens_of(e["search_tokens"]) for e in existing_feedback_global
                        if e.get("type") == "REFERENCE_QUESTION"]
global_phrase_to_col = {e["feedback_phrase"].lower(): tokens_of(e["search_tokens"])
                        for e in existing_feedback_global
                        if e.get("type") == "BUSINESS_TERM"}

Stash both into {run_dir}/feedback_signal.json for Step 4 (review) and Step 6 (generation).

---

Step 4 — Review Existing AI Assets — Produce Critique + Deltas

For each existing asset, score it against mined evidence and produce a delta proposal. Full heuristics in ai-asset-review-rules.md. Summary:

Asset	Critique signals	Delta types
model.description	Length < 100 chars; missing key entities/measures present in mined prose	KEEP / EXPAND / REWRITE
column.ai_context	Empty; shorter than 30 chars; doesn't mention column purpose; contradicts mined prose	ADD / REFINE / KEEP
column.synonyms	Empty; missing high-frequency phrases from mined prose; redundant with display name	ADD_PHRASES / REMOVE_REDUNDANT / KEEP
nls_feedback GLOBAL entries	Stale references (tokens reference columns/formulas no longer on Model); downvoted entries; matches new candidate	KEEP / FLAG_FOR_HUMAN
nls_feedback USER entries	Out of scope by default; surface count only	KEEP_OUT_OF_SCOPE (default)

Per ai-asset-review-rules.md §4:

• GLOBAL feedback is treated as authoritative — preserved, used as input signal for Step 6, never silently overwritten
• USER feedback is gated behind the Step 5 opt-in. Default: skip entirely; not used as signal, not modified
• Stale references (entries pointing to columns/formulas no longer on the Model) get FLAG_FOR_HUMAN so the user can decide whether to keep, edit, or remove via UI

Existing values are never silently overwritten — the user must explicitly accept each REFINE or REWRITE in Step 7. Save the critique to {run_dir}/existing_review.json.

---

Step 4.5 — Cross-Model Consistency Scan

Spotter doesn't disambiguate between same-named columns across Models the user can reach. The same query may return different numbers depending on which Model the user happens to hit — the central failure mode in enterprise text-to-SQL (Axius, "The 7-Table Fallacy", 2026). Full implementation rules in cross-model-consistency.md. Summary:

For each column in this Model, search all Models the user can read in the org and compare on:

1. db_column_name — different warehouse source ⇒ almost always different meaning
2. column_type — measure vs attribute mismatch
3. aggregation — sum vs avg = different semantics
4. Formula expression — for formula columns, does the math agree?
5. ai_context text — substring conflict heuristic

Pre-scan gate

The scan is the only step that scales with tenant size, not target size. Show the user the cost estimate and let them choose the scope before any work starts:

Step 4.5 — Cross-Model Consistency Scan

Found {N_models} readable Models on this profile.

First-run scan exports TML for each Model in parallel (4-way concurrent),
cached locally on (guid, modified_time). Subsequent runs only re-export
Models that have been modified since the last run.

  Estimated time:  ~{est_seconds // 60} minute(s) first run, seconds thereafter
  Cache location:  ~/.cache/ts-object-model-coach/tml-corpus/

Proceed?
  [Y]              run full scan (default)
  [filter <name>]  scope-by-name LIKE pattern (e.g., "filter Dunder")
  [N]              skip Step 4.5 entirely

Choose:

Time estimate: assume ~1.5s per uncached Model export at 4-way parallel (N / 4 * 1.5s). A scan of 343 Models lands at ~2 min wall time first run.

If the user picks filter <name>, re-run the metadata search with --name "%<name>%" and recompute the count. Skip option (N) writes an empty cross_model_consistency.md and proceeds to Step 5 normally.

Implementation

import json, pathlib, subprocess, time
from concurrent.futures import ThreadPoolExecutor, as_completed

# 1. Enumerate readable Models (--all auto-paginates; default page size is 50).
res = subprocess.check_output([
    "ts", "metadata", "search",
    "--subtype", "WORKSHEET",
    "--all",
    "--profile", profile_name,
])
all_models = [r for r in json.loads(res)
              if r["metadata_header"].get("contentUpgradeId") != "WORKSHEET_TO_MODEL_UPGRADE"
              and r["metadata_header"].get("worksheetVersion") != "V1"
              and r["metadata_id"] != model_guid]

# 2. Resolve cache dirs and load FORBIDDEN cache (24h TTL).
cache_dir = pathlib.Path.home() / ".cache" / "ts-object-model-coach" / "tml-corpus"
cache_dir.mkdir(parents=True, exist_ok=True)
forbidden_cache_path = cache_dir.parent / "forbidden.json"
forbidden_cache = {}
if forbidden_cache_path.exists():
    raw = json.loads(forbidden_cache_path.read_text())
    cutoff_ms = (time.time() - 24 * 3600) * 1000
    forbidden_cache = {g: e for g, e in raw.items() if e.get("ts_ms", 0) > cutoff_ms}

# 3. Split into "hit cache", "skip — known-FORBIDDEN", and "needs export".
to_export, corpus = [], []
for m in all_models:
    if m["metadata_id"] in forbidden_cache:
        continue  # silently skip — user can clear ~/.cache/ts-object-model-coach/forbidden.json to retry
    cache_key = f"{m['metadata_id']}-{m['metadata_header']['modified']}.json"
    cache_path = cache_dir / cache_key
    if cache_path.exists():
        corpus.append(json.loads(cache_path.read_text()))
    else:
        # Evict stale entries for this guid before re-exporting
        for old in cache_dir.glob(f"{m['metadata_id']}-*.json"):
            old.unlink()
        to_export.append((m, cache_path))

# 4. Parallel export with progress reporting.
def _export_one(m, cache_path):
    try:
        out = subprocess.check_output(
            ["ts", "tml", "export", m["metadata_id"],
             "--profile", profile_name, "--fqn", "--parse"],
            stderr=subprocess.PIPE, timeout=60,
        )
        cache_path.write_bytes(out)
        return ("ok", m, json.loads(out))
    except subprocess.CalledProcessError as e:
        err = e.stderr.decode("utf-8", "replace")
        if "FORBIDDEN" in err or "UNAUTHORIZED" in err:
            return ("forbidden", m, err[:200])
        return ("error", m, err[:200])
    except Exception as e:
        return ("error", m, str(e)[:200])

if to_export:
    print(f"  Exporting {len(to_export)} Model(s) — {len(corpus)} cached, "
          f"{len(forbidden_cache)} skipped (known FORBIDDEN).")
    completed = 0
    with ThreadPoolExecutor(max_workers=4) as executor:
        futures = {executor.submit(_export_one, m, p): m for m, p in to_export}
        for future in as_completed(futures):
            completed += 1
            status, m, payload = future.result()
            if status == "ok":
                corpus.append(payload)
            elif status == "forbidden":
                forbidden_cache[m["metadata_id"]] = {
                    "ts_ms": int(time.time() * 1000),
                    "name": m["metadata_header"]["name"],
                    "error": payload,
                }
            # ("error", ...) — log and skip; will retry next run
            if completed % 25 == 0 or completed == len(to_export):
                print(f"  Exporting {completed}/{len(to_export)}...")

# 5. Persist FORBIDDEN cache for the next run.
forbidden_cache_path.write_text(json.dumps(forbidden_cache, indent=2))

Build the column-name → collisions index, run the divergence checks per cross-model-consistency.md, and write {run_dir}/cross_model_consistency.md with the explainer block from review-explainers.md Block 6 prepended.

Until the heuristic is calibrated against a live tenant (open-items.md #15), default the proposed RouteAction to NEEDS_REVIEW for every collision — let the user pick.

The scan output is referenced from the Step 5 critique summary; users can defer review by picking 0 from the surface menu or skip it via the cross-Model checkbox.

Performance notes.

• max_workers=4 is chosen to be polite to the API while delivering ~4x speedup. The endpoint tolerates higher concurrency, but 4 keeps the run well under any sensible rate limit even on smaller TS instances.
• FORBIDDEN cache TTL is 24 h to handle daily permission changes without re-paying the discovery cost on every run. Clear ~/.cache/ts-object-model-coach/forbidden.json to force a re-check.
• Successful TML caches do not expire — they're keyed on modified_time, so a Model edit naturally invalidates its entry. Stale entries for the same guid are evicted on miss.

---

Step 5 — Show Critique; Pick Which Surfaces to Generate

Display the critique summary, ask about USER feedback inclusion, then show the scope menu. Example output:

=== Existing AI assets on "Dunder Mifflin Sales & Inventory" ===

Model description:               Present (430 chars, AI-generated)
                                 Critique: KEEP — coverage is good
Column AI Context:               0 / 19 columns populated
Column Synonyms:                 0 / 19 columns populated
Existing feedback (GLOBAL):      0 entries        — used as input signal
Existing feedback (USER):        0 entries        — private to creator
Spotter enabled:                 ✅

Cross-Model consistency scan:
  Scanned 47 Models you can read.
  Of this Model's 19 columns, 5 have name collisions in other Models.
  Of those, 2 look genuinely divergent (different db_column_name or formula),
  3 are duplicates with identical definitions.
  → cross_model_consistency.md generated; review in Step 7.

If existing_feedback_user is non-empty, ask:

This Model has {N_user} access:USER feedback entries (private to their creators).
By default these are excluded from this run — not used as signal, not modified.

Include them as input signal? (y/N, default N)
   - Y: existing USER entries seed paraphrase variants and influence ranking
        (treated like GLOBAL for the duration of this run only)
   - N: ignore them entirely; they remain untouched on the Model

Choose:

Save the answer as include_user_feedback: bool in {run_dir}/scope.json.

Then show the surface menu:

What would you like to generate or improve? (tick all that apply)

  [ ] 1. Column AI Context           — propose a 2-3 sentence business description per column
  [ ] 2. Column Synonyms             — propose alternative names per column (parser-level)
  [ ] 3. Reference Questions         — full NL question → tokenised search mappings (target ~15)
  [ ] 4. Business Terms              — phrase → column mappings (coaching-layer alternative)
  [ ] 5. Data Model Instructions     — global rules draft (manual paste — TML location TBD)
  [ ] 6. Improve Model description   — only shown when Step 4 critique is REWRITE/EXPAND
  [ ] 7. Cross-Model consistency     — review same-named-column collisions in other Models
                                        (only shown when Step 4.5 found ≥ 1 collision)
  [ ] 8. Column metadata + hierarchies — cardinality, sample values, drill paths
                                          (requires Snowflake profile for samples)

Enter numbers (e.g. "1,3,5"), "all", or "0" to skip generation but still review #7:

If the user selects any of #2 (Column Synonyms), #3 (Reference Questions), or #4 (Business Terms) — i.e. any phrase-coaching surface — display the plain-English explainer from synonym-strategy-explainer.md verbatim, with all variables substituted from the user's data.

The explainer is informational, not a strategy choice — the skill auto-selects the right Method per phrase using the decision tree:

Phrase target	Method assigned	Surface
Maps to a single existing Model column	A — Synonym	model.columns[].synonyms[]
Maps to a calculation (formula)	B — Business Term	nls_feedback BUSINESS_TERM
Whole-sentence conversational phrasing	C — Reference Question	nls_feedback REFERENCE_QUESTION

The user does NOT pick a global strategy ("A only" / "B only" / "Both"). Instead, during Step 7 review they can override per row — change KEEP to MOVE_TO_A, MOVE_TO_B, or MOVE_TO_C to route a phrase to a different Method. This per-row control is the right level of granularity; global strategy was wrong.

After displaying the explainer, ask:

Continue with Method A/B/C auto-selection (you can override per row in Step 7)?
(Y / N):

Save the surface selection set and the user's confirmation as {run_dir}/scope.json.

---

Step 6 — Generate Proposals Per Selected Surface

Per surface (only those selected in Step 5):

6.1 — Column AI Context (structured) + Column Description (prose)

ai_context is structured-only — closed enums and refs, never prose. The full spec is in ai-context-schema.md; concrete worked examples are in ai-context-examples.md. Step 6.1 generates two surfaces in parallel:

Surface	Form	What goes here
properties.ai_context	Structured YAML — closed enums + refs only	Constraints the LLM needs to write correct SQL. Allowed keys: additivity, non_additive_dimension, time_basis, source, grain_keys, unit, null_semantics, role
column.description	Prose, 1–2 sentences (≤ 200 chars)	Business meaning, gotchas, edge cases, grain-in-words — the human-readable context

Bootstrapping measures

For each measure column, bootstrap the mandatory tier (additivity, time_basis, grain_keys) deterministically:

1. additivity — first-pass guess from aggregation:
   • SUM ⇒ additive unless mined evidence flags "snapshot" / "balance" / "closing" / "filled" → semi_additive candidate
   • MAX / MIN over a date column ⇒ semi_additive candidate
   • COUNT_DISTINCT and ratios/divisions ⇒ non_additive
2. For additivity: semi_additive — populate non_additive_dimension (the time-grain column the snapshot is anchored on). additive_dimensions is NOT an axis any more (removed 2026-04-29 — redundant with non_additive_dimension).
3. time_basis — for formula columns, infer from formulas[] query_groups({DATE_DIM.DATE})-style references. Never copy formula text into ai_context. Prefer the conformed shared date dim when one exists in the Model join graph. May be legitimately absent for time-agnostic measures.
4. grain_keys — list of column refs that uniquely identify one fact row. Derive from the table's primary key + the time_basis column.
5. source — conditional override only. Omit when column_id: TABLE::COL resolves cleanly via the table's fqn. Required when the column_id doesn't match the physical path (renamed/aliased columns, view-backed columns).

For optional axes on measures:

• unit — closed enum: currency / count / ratio / percentage / duration. Inferred from column name patterns and the underlying type.
• null_semantics — closed enum: zero / unknown / no_snapshot. For snapshot/balance measures, default to no_snapshot.

Bootstrapping dimensions

Dimensions get at most three axes in ai_context: source (conditional), null_semantics (when NULL has business meaning), and role (the primary dimensional axis).

Dimension shape	What to populate
Has an id/code/label/key sibling on the same table (e.g. Product Category + Product Category ID)	role: on each — closed enum: label / id / code / key
Stand-alone label where NULL has business meaning	role: label + null_semantics: unknown
Renamed/aliased — column_id doesn't resolve	source: override (+ optionally role:)
Sole, unambiguous, resolves cleanly, no NULL semantics	Empty. column.description carries any prose.
Surrogate key, never user-facing	Empty.

The role axis prevents the Test 3 Q-010 failure mode (LLM picked category_id when the user asked for "category"). Combined with the system-prompt rule below, it also reinforces the prevention of phantom dimension tables (DM_CATEGORY etc.) when paired with the description prose.

Bootstrapping column.description (prose)

In parallel, generate column.description (prose, ≤ 200 chars) from:

• mined_prose_extract.ai_context_evidence_per_column (Step 3c)
• Existing column.description if present (preserve as primary signal)
• Mined Liveboard / Answer prose near the column reference

column.description is where business meaning, gotchas, and grain-in-words live. Do not duplicate this content into ai_context.

Generator system-prompt rule

Include this clause verbatim in any LLM prompt that consumes TML + ai_context (Spotter coaching prompts, third-party agent prompts, downstream SQL agents):

"This TML mixes ThoughtSpot DSL with metadata. Distinguish them when emitting SQL:

1. The formulas[] block contains ThoughtSpot formula DSL, not SQL. Functions like last_value(...), query_groups(...), growth_rate(...), cumulative_sum(...) and any other TS-specific formula functions are not SQL functions. Re-implement the formula's intent in target SQL from scratch — never copy or partially translate the DSL.
2. Square-bracket column refs ([Amount], [Stock Quantity]) and curly-brace dim refs ({DM_DATE_DIM.DATE}) are TS logical references, not SQL identifiers. Resolve each to its physical path via column_id: TABLE::COL plus the table's fqn (or via an explicit source: axis when present, which overrides column_id).
3. Column and table display names (e.g. Total Sales, Inventory Balance, Product Category) are TS logical names. They are never valid SQL identifiers — resolve them to physical paths the same way as bracket refs.
4. The ai_context block declares constraints on the column's result (additivity, time_basis, grain_keys, role). Respect them when writing SQL; do not infer constraints that are not declared. All prose context for a column lives in column.description, not ai_context."

Output and review

Propose each surface with a confidence score; flag low-confidence cases for explicit review in Step 7. Low-confidence drivers: mined evidence is sparse, additivity guess conflicts with mined evidence, no shared date dim detected for time_basis, or an id/label/code/key sibling pair lacks a clear role assignment.

6.2 — Column Synonyms

For each column, compile a candidate synonym list from:

• Mined prose phrases matching the column name (stem overlap ≥ 0.6)
• Common business shorthands inferred from the column's apparent role
• User-defined existing synonyms (preserved)

Reject phrases that are exact substrings/supersets of the display name (e.g. don't add "inventory" as a synonym of Inventory Balance).

Before writing proposals, run two cross-column validation checks across ALL columns (existing synonyms + proposed additions combined):

1. No synonym matches another column's display name. A synonym equal to another column's name causes Spotter to resolve the phrase to two different things depending on context. Flag every such entry as REMOVE in the review file with the note "synonym conflicts with column name [{other_col}]". This applies to existing synonyms too — surface them for removal even if they weren't proposed in this run.

2. No synonym appears on more than one column. Duplicate synonyms cause non-deterministic resolution. Flag every duplicate as REMOVE on the lower-priority column (keep it on the column whose display name is closest in meaning).

all_col_names_lower = {c["name"].lower(): c["name"] for c in m.get("columns", [])}

# Build merged map: synonym_lower → [col_name, ...] across existing + proposed
syn_to_cols = {}
for col in m.get("columns", []):
    all_syns = list(col.get("properties", {}).get("synonyms", []))
    proposed = synonym_deltas.get(col["name"], {}).get("proposed_additions", [])
    for syn in all_syns + proposed:
        syn_to_cols.setdefault(syn.lower(), []).append(col["name"])

synonym_errors = []
for col in m.get("columns", []):
    all_syns = list(col.get("properties", {}).get("synonyms", []))
    proposed = synonym_deltas.get(col["name"], {}).get("proposed_additions", [])
    for syn in all_syns + proposed:
        # Rule 1: synonym matches another column's display name
        match = all_col_names_lower.get(syn.lower())
        if match and match != col["name"]:
            synonym_errors.append(
                f"[{col['name']}] synonym '{syn}' conflicts with column name [{match}] — flag REMOVE"
            )
        # Rule 2: synonym appears on multiple columns
        owners = syn_to_cols.get(syn.lower(), [])
        if len(owners) > 1 and owners[0] != col["name"]:
            synonym_errors.append(
                f"[{col['name']}] synonym '{syn}' duplicated on {owners} — flag REMOVE on lower-priority column"
            )

if synonym_errors:
    print("Synonym validation issues (flag in review file):")
    for e in synonym_errors:
        print(f"  {e}")

Surface all violations in synonyms.md as REMOVE rows at the top of the file, clearly separated from new proposals, so the user sees them before approving anything.

6.3 — Reference Questions

Apply the taxonomy in question-taxonomy.md: generate ~40 candidates across T1–T4, score by signals (mined search match +5, mined SQL match +3, prose mention +2, join key +2, tier T1/T2 +1, formula required +1, duplicate of existing entry → drop).

For each kept question, also generate 2–3 paraphrase variants per token-mapping-rules.md §5 — e.g. canonical "Inventory Balance this month" + variants "how much stock do we have right now", "current inventory". All variants share the same search_tokens and formula_info, differ only in feedback_phrase.

6.4 — Business Terms

Phrase → existing Model column or formula mappings, drawn from prose mining where:

• The phrase targets an existing Model formula (column synonyms can't reach formulas)
• OR the phrase needs chart_type / display_mode hints that a column synonym can't carry

BUSINESS_TERMs cannot create new formulas inline (verified open-items.md #12). If a phrase needs a calculation that doesn't exist on the Model:

1. The skill emits a MOVE_TO_NEW_FORMULA proposal in business_terms.md, NOT a default-KEEP BT entry
2. The user is directed to add the formula via /ts-object-answer-promote (or manually) first
3. On a subsequent run of ts-object-model-coach, the BT can target the new formula directly

Required fields on every BT entry:

- type: BUSINESS_TERM
  access: GLOBAL
  feedback_phrase: "stock"
  parent_question: "stock"
  search_tokens: "[Inventory Balance]"   # MUST reference existing column or formula
  rating: UPVOTE
  display_mode: UNDEFINED                 # REQUIRED
  chart_type: KPI                         # REQUIRED (KPI is universally safe; see open-items.md #11)

Do NOT include formula_info on BT entries — verified rejected by the API.

See token-mapping-rules.md §4 for the full Method B specification.

6.5 — Data Model Instructions (structured)

model_instructions is structured-only — closed enums and refs, never prose. The full spec is in model-instructions-schema.md. Follows the same declarative-only discipline as per-column ai_context, just at Model scope.

Boundary — only untriggered global rules belong here. Phrase-triggered behavior ("when users say X, do Y") goes to nls_feedback (Surfaces 3 and 4), not here. See model-instructions-schema.md § Boundary.

Bootstrapping the 7 categories

Category	Bootstrap source
exclusion_rules	Mined prose mentioning "exclude", "ignore", "filter out"; existing CASE WHEN patterns in mined Snowflake SQL; analyst input on business semantics
aggregation_defaults	Columns with column_type: ATTRIBUTE whose name suggests an entity (Customer Name, Order ID) — bootstrap count_distinct. Mined query history showing repeated aggregation patterns.
time_defaults	Mined query window heuristics (the modal time range across mined queries); fiscal year metadata if present in connection settings
output_formatting	One rule per ai_context.unit value present in the Model — bootstrap conservative defaults (currency: no decimals, percentage: one decimal)
schema_assumptions	denormalized_attributes: every dimension column whose column_id lives on a parent table (rather than a dedicated dim table) is added — this is the meta-level reinforcement of the per-column phantom-table prevention. shared_conformed_date_dim: detect from joins_with graph — if multiple facts join through one date dim, it's the conformed anchor. surrogate_keys_only: tables whose PK columns have column_id like *_KEY and no business meaning. chasm_attribution: detect from joins_with graph — for each pair of fact tables (tables with MEASURE columns), compute shared dims (dims both join to) and unique dims; if shared ≥ 1 AND (A-only ≥ 1 OR B-only ≥ 1) emit a proposal. Default RouteAction KEEP; flag pairs with only one shared dim as NEEDS_REVIEW (single-shared-dim chasms have higher false-positive risk — analyst should confirm intent). See model-instructions-schema.md § How chasm_attribution enables (and clarifies) cross-fact queries.
column_metadata	Requires Snowflake profile. For each ATTRIBUTE column: query APPROX_COUNT_DISTINCT → classify cardinality tier; PII-gate flagged columns; for low/medium non-PII columns query SELECT DISTINCT ... LIMIT 5 for samples; infer usage from mined query history (WHERE = filter, GROUP BY = group_by) or from cardinality heuristic; infer value_format from sample value patterns. See § Column metadata generation below.
hierarchies	Detect from name-prefix grouping + cardinality ordering + functional dependency validation via Snowflake. See § Hierarchy detection below.

Output

Generate the structured form to {run_dir}/model_instructions.yaml AND emit the prose instructions.md for manual paste until open-items.md #4 verifies the TML location for Model-level instructions. Once the location is verified, Step 9 will write directly via tml/import and the manual-paste step becomes obsolete.

Validation

Same deploy-time validation as ai_context (Step 8b), applied at Model scope: closed-key check, ref resolution, enum check, ≤ 80 char note: and reason: fields, total payload ≤ 3000 chars (verified hard limit on the Settings → Coach Spotter → Instructions field). Validation failures block import. See model-instructions-schema.md § Safeguards #3 for the budget-trim order when a generated payload exceeds 3000 chars.

Worked example

For Dunder Mifflin, a fully populated model_instructions is ~70 lines and covers the Model-scope failure clusters from Test 4, the chasm_attribution rule for the Inventory ↔ Order Detail pair (which has shared Product + Date but no shared Customer/Region), column metadata for 8 dimensions, and 3 drill-path hierarchies — see model-instructions-schema.md § Worked example.

Chasm-attribution detection algorithm (sketch)

# Run after Step 2 (TML export) — operates on the parsed model graph.
fact_tables = [t for t in model["tables"] if any(
    c.get("properties", {}).get("column_type") == "MEASURE" for c in t["columns"])]

dim_joins_by_table = {}  # table_name -> set of dim col_refs it joins to
for jw in model.get("joins_with", []):
    a, b = parse_join_endpoints(jw["on"])  # e.g. "DM_INVENTORY::BALANCE_DATE = DM_DATE_DIM::DATE"
    dim_joins_by_table.setdefault(a.table, set()).add(b)
    dim_joins_by_table.setdefault(b.table, set()).add(a)

proposals = []
for fact_a, fact_b in itertools.combinations(fact_tables, 2):
    shared = dim_joins_by_table[fact_a.name] & dim_joins_by_table[fact_b.name]
    a_only = dim_joins_by_table[fact_a.name] - dim_joins_by_table[fact_b.name]
    b_only = dim_joins_by_table[fact_b.name] - dim_joins_by_table[fact_a.name]
    if len(shared) >= 1 and (a_only or b_only):
        confidence = "high" if len(shared) >= 2 else "low"
        proposals.append({
            "assumption": "chasm_attribution",
            "facts": [fact_a.name, fact_b.name],
            "shared_dims": sorted(shared),
            "note": f"non-shared values repeat across the other fact's unique dims",
            "_route_action": "KEEP" if confidence == "high" else "NEEDS_REVIEW",
        })

Pairs with 0 shared dims are NOT chasm attributions (they have no bridging dim) — never emit a proposal. Pairs with 1 shared dim are flagged NEEDS_REVIEW because single-shared-dim attributions have higher false-positive risk (the analyst should confirm the cross-fact attribution is intended).

Column metadata generation (requires Snowflake profile)

Generates the column_metadata category. Skipped entirely if no Snowflake profile is available (can't query the data for cardinality or samples).

import re, subprocess, json

# PII detection patterns
PII_PATTERNS = re.compile(
    r'\b(name|email|address|phone|ssn|social_sec|dob|birth|password|token|'
    r'credit_card|account_num)\b', re.I)

# Step 1: Identify ATTRIBUTE columns and flag PII candidates
attr_columns = [c for c in columns if c.get("properties", {}).get("column_type") == "ATTRIBUTE"]
pii_flagged = [c for c in attr_columns if PII_PATTERNS.search(c["name"])]
non_pii_attrs = [c for c in attr_columns if c not in pii_flagged]

Present the PII-flagged list for user confirmation:

The following columns were flagged as potentially containing PII:
  {list of pii_flagged column names}

Confirm exclusion from sample values? (Y to confirm / N to include all):

If confirmed, those columns get cardinality only — no samples.

# Step 2: Query Snowflake for cardinality (all ATTRIBUTE columns)
# Build a single query with APPROX_COUNT_DISTINCT per column
# (resolve physical table + column from column_id + table fqn)

cardinality_sql = f"""
SELECT
  {", ".join([
    f"APPROX_COUNT_DISTINCT({resolve_physical_col(c)}) AS {sanitize(c['name'])}"
    for c in attr_columns
  ])}
FROM {physical_table_fqn}
"""

# Step 3: Classify cardinality tiers
row_count = ...  # from Step 2 schema extraction
for c in attr_columns:
    distinct = cardinality_results[c["name"]]
    if distinct < 20:
        c["_cardinality"] = "low"
    elif distinct < 1000:
        c["_cardinality"] = "medium"
    elif distinct > row_count * 0.9:
        c["_cardinality"] = "unique"
    else:
        c["_cardinality"] = "high"

# Step 4: Query sample values for low/medium non-PII columns
sample_columns = [c for c in non_pii_attrs if c["_cardinality"] in ("low", "medium")]
for c in sample_columns:
    sample_sql = f"""
    SELECT DISTINCT {resolve_physical_col(c)} AS val
    FROM {physical_table_fqn}
    WHERE {resolve_physical_col(c)} IS NOT NULL
    ORDER BY val
    LIMIT 5
    """
    c["_samples"] = [row["val"] for row in execute(sample_sql)]

# Step 5: Infer usage from mined query history (if available)
# Falls back to cardinality heuristic if no query history
for c in attr_columns:
    if c["name"] in where_clause_columns:
        c["_usage"] = "filter"
    elif c["name"] in group_by_columns:
        c["_usage"] = "group_by"
    elif c["name"] in both_columns:
        c["_usage"] = "both"
    else:
        # Heuristic fallback
        if c["_cardinality"] in ("high", "unique"):
            c["_usage"] = "filter"
        else:
            c["_usage"] = "group_by"

# Step 6: Infer value_format from sample patterns
VALUE_FORMAT_PATTERNS = [
    (re.compile(r'^[A-Z]{2}$'), "2-letter code"),
    (re.compile(r'^[A-Z]{3}$'), "3-letter code"),
    (re.compile(r'^\d{4}-\d{2}-\d{2}$'), "YYYY-MM-DD"),
    (re.compile(r'^\d+$'), "integer"),
]
for c in sample_columns:
    if c.get("_samples"):
        for pattern, fmt in VALUE_FORMAT_PATTERNS:
            if all(pattern.match(str(s)) for s in c["_samples"]):
                c["_value_format"] = fmt
                break

Hierarchy detection

Detects drill-path hierarchies from schema structure and validates via Snowflake.

import re
from collections import defaultdict

# Step 1: Group dimensions by table + name prefix
# e.g., "Ship Country", "Ship Region", "Ship City" → prefix "Ship"
prefix_groups = defaultdict(list)
for c in attr_columns:
    parts = c["name"].split()
    if len(parts) >= 2:
        prefix = parts[0]
        prefix_groups[(c.get("_source_table", ""), prefix)].append(c)

# Step 2: Order within each group by cardinality (lowest = coarsest)
hierarchy_candidates = []
for (table, prefix), cols in prefix_groups.items():
    if len(cols) < 2:
        continue
    ordered = sorted(cols, key=lambda c: {"low": 0, "medium": 1, "high": 2, "unique": 3}.get(c["_cardinality"], 99))
    hierarchy_candidates.append({
        "name": prefix.lower(),
        "levels": [c["name"] for c in ordered],
        "_confidence": "medium",
    })

# Step 3: Detect date dim hierarchies
# Look for date dimension tables with known temporal columns
date_dim_cols = [c for c in attr_columns
                 if c.get("_source_table", "").upper() in ("DM_DATE_DIM", "DM_DATE", "DATE_DIM")]
TEMPORAL_ORDER = {"year": 0, "quarter": 1, "month": 2, "week": 3, "day": 4, "date": 5}
temporal_cols = [(c, TEMPORAL_ORDER[k]) for c in date_dim_cols
                 for k in TEMPORAL_ORDER if k in c["name"].lower()]
if len(temporal_cols) >= 2:
    temporal_cols.sort(key=lambda x: x[1])
    hierarchy_candidates.append({
        "name": "time",
        "levels": [c["name"] for c, _ in temporal_cols],
        "_confidence": "high",
    })

# Step 4: Validate functional dependencies via Snowflake
# For each candidate hierarchy, check that child → parent is many-to-one
for h in hierarchy_candidates:
    for i in range(len(h["levels"]) - 1):
        parent_col = resolve_physical_col_by_name(h["levels"][i])
        child_col = resolve_physical_col_by_name(h["levels"][i + 1])
        validation_sql = f"""
        SELECT {child_col}, COUNT(DISTINCT {parent_col}) AS parent_count
        FROM {physical_table_fqn}
        WHERE {child_col} IS NOT NULL AND {parent_col} IS NOT NULL
        GROUP BY {child_col}
        HAVING parent_count > 1
        LIMIT 1
        """
        result = execute(validation_sql)
        if result:
            h["_confidence"] = "invalid"  # Not a true hierarchy
            break

# Remove invalid hierarchies
hierarchy_candidates = [h for h in hierarchy_candidates if h["_confidence"] != "invalid"]

Hierarchy candidates with _confidence: "medium" (name-prefix + cardinality only, no date-dim detection) are flagged as NEEDS_REVIEW in the review file. High-confidence candidates (date dim, validated functional dependency) default to KEEP.

6.6 — Improved Model Description

Generate a single proposed replacement description, mentioning all primary measures + dimensions present in the Model and any business context surfaced from mined prose.

6.7 — Cross-Model Consistency Report

Format the collisions detected in Step 4.5 into the review file {run_dir}/cross_model_consistency.md. Per cross-model-consistency.md:

• Prepend the explainer block (Block 6 in review-explainers.md)
• One row per column with ≥ 1 collision (skip columns with zero collisions — the file's purpose is to surface divergence, not enumerate non-issues)
• Default RouteAction = NEEDS_REVIEW until the heuristic is calibrated (open-items.md #15)
• Auto-generate the "Suggested rationale" column from the heuristic; the user edits it during review

If a column is marked DOCUMENT_DIFFERENCE, append a # CONFLICTS_WITH: annotation to that column's ai_context block during Step 8 build (uses the rationale text from the review file). Future runs detect this annotation and skip re-flagging the same collision unless underlying definitions change.

---

Step 7 — Per-Surface Review with Explainer Blocks

For each generated surface, write a markdown file the user can edit directly.

Prepend a 3-section explainer block to each review file — what this is for, what we checked, rules for the outcomes you can pick. The canonical text per surface lives in review-explainers.md. Display it verbatim with placeholder substitutions ({N_columns}, {N_filled}, {N_models_scanned}, etc.) so the user can pick decisions without re-reading SKILL.md.

The Method labels (A/B/C) live within the relevant surface's explainer rather than as separate headers — e.g. synonyms.md is Method A, mappings.md is Method C. The full Method reasoning (when to use each, decision tree) is in synonym-strategy-explainer.md and is shown once at Step 5 before the surface menu.

Per-surface format:

Surface	File	Explainer block	Notes
AI Context	ai_context.md	Block 1 in review-explainers.md	Table: column, current value, proposed YAML, action, slots filled (X / 9)
Synonyms	synonyms.md	Block 2	METHOD A. Table: column, current synonyms, proposed additions, action
Reference Questions, Stage 1	candidates.md	Block 3	METHOD C — confirm questions
Reference Questions, Stage 2	mappings.md	Block 4	METHOD C — confirm tokens, variants, formulas
Business Terms	business_terms.md	Block 5	METHOD B. Table: phrase, target formula, justification
Cross-Model Consistency	cross_model_consistency.md	Block 6	Always shown when Step 4.5 found ≥ 1 collision
Description	description.md	Block 7	Before/after diff
Data Model Instructions	instructions.md	Block 8	Free-form draft + manual-paste guidance
Column Metadata + Hierarchies	column_metadata.md	Block 9	Table: column, cardinality, samples, usage, format, action + hierarchies section

Each row has a RouteAction column with surface-specific allowed values. The per-surface allowed values, defaults, and what each one does are in the explainer block at the top of the file (Block 1–9 in review-explainers.md). The shared / common shape:

RouteAction	Available on	Meaning
KEEP	All surfaces (default)	Apply this row using the surface's Method
DROP	All surfaces	Don't apply this row
EDIT	All surfaces	The user has edited the proposed value; treat as the new authoritative value
MOVE_TO_A	Synonyms, Reference Questions, Business Terms	Re-route this phrase to be a column synonym
MOVE_TO_B	Synonyms, Reference Questions	Re-route to be a Business Term + formula
MOVE_TO_C	Synonyms, Business Terms	Re-route to be a Reference Question
MOVE_TO_NEW_FORMULA	Business Terms only	Target formula doesn't exist; user runs /ts-object-answer-promote first
KEEP_AS_IS / ALIGN / RENAME / DOCUMENT_DIFFERENCE / INTENTIONAL_DIFFERENCE / NEEDS_REVIEW	Cross-Model Consistency only	See Block 6
EXPAND / REWRITE	Description only	See Block 7
DEFER	AI Context only	Skip this column this run; flag again next run

This per-row override is the user's full control surface — there's no global strategy choice. A phrase the user disagrees with as a Synonym can be moved to a Business Term or Reference Question without leaving the review file.

Wait for the user to type done (or per-file done), re-read each file, parse the RouteAction column, apply the routing, and ask for final confirmation per surface:

ai_context.md                 — kept 12 ADDs, 2 REFINEs, 5 KEEPs. Proceed? (Y / N):
synonyms.md                    — kept 18 ADDs, 6 routed to Method C, 11 dropped. Proceed? (Y / N):
business_terms.md              — kept 4 ADDs, 1 routed to Method A, 0 dropped. Proceed? (Y / N):
mappings.md                    — kept 28 questions, 4 routed to Method A. Proceed? (Y / N):
cross_model_consistency.md     — 2 RENAMEs, 1 DOCUMENT_DIFFERENCE, 2 KEEP_AS_IS. Proceed? (Y / N):
instructions.md                — kept 7 rules. Save for manual paste? (Y / N):

---

Step 8 — Build Merged TML, Backup, Final Import Gate

8a. Backup current state

import yaml, copy
backup_path = run_dir / "before" / "model.tml"
backup_path.parent.mkdir(parents=True, exist_ok=True)
backup_path.write_text(yaml.dump(model_tml, sort_keys=False))
if existing_entries:
    (run_dir / "before" / "feedback.tml").write_text(
        yaml.dump({"guid": model_guid,
                   "nls_feedback": {"feedback": existing_entries}}, sort_keys=False)
    )

8b. Build the patched Model TML (surfaces 1, 2, 6)

Deep-copy the original Model TML and apply each accepted delta:

patched = copy.deepcopy(model_tml)
m = patched["model"]

# Surface 1 — column ai_context (structured-only) + column.description (prose)
for col in m.get("columns", []):
    delta = ai_context_deltas.get(col["name"])
    if delta and delta["action"] in ("ADD","REFINE"):
        col.setdefault("properties", {})["ai_context"] = delta["proposed_value"]
    desc_delta = description_deltas.get(col["name"])
    if desc_delta and desc_delta["action"] in ("ADD","REFINE"):
        col["description"] = desc_delta["proposed_value"]

# Validate ai_context per ai-context-schema.md § Safeguards. Block import on failure.
ALLOWED_KEYS = {"additivity","non_additive_dimension","time_basis","source",
                "grain_keys","unit","null_semantics","role"}
ENUM_ADDITIVITY = {"additive","semi_additive","non_additive"}
ENUM_UNIT = {"currency","count","ratio","percentage","duration"}
ENUM_NULL = {"zero","unknown","no_snapshot"}
ENUM_ROLE = {"label","id","code","key"}
errors = []
for col in m.get("columns", []):
    raw = col.get("properties", {}).get("ai_context")
    if not raw:
        continue
    if len(raw) > 400:
        errors.append(f"{col['name']}: ai_context exceeds 400 chars ({len(raw)})")
    parsed = yaml.safe_load(raw) if isinstance(raw, str) else raw
    if not isinstance(parsed, dict):
        errors.append(f"{col['name']}: ai_context must be a structured map, not prose")
        continue
    unknown = set(parsed.keys()) - ALLOWED_KEYS
    if unknown:
        errors.append(f"{col['name']}: unknown keys {unknown}; allowed: {ALLOWED_KEYS}")
    if "additivity" in parsed and parsed["additivity"] not in ENUM_ADDITIVITY:
        errors.append(f"{col['name']}: additivity must be one of {ENUM_ADDITIVITY}")
    if parsed.get("additivity") == "semi_additive" and "non_additive_dimension" not in parsed:
        errors.append(f"{col['name']}: semi_additive requires non_additive_dimension")
    if "unit" in parsed and parsed["unit"] not in ENUM_UNIT:
        errors.append(f"{col['name']}: unit must be one of {ENUM_UNIT}")
    if "null_semantics" in parsed and parsed["null_semantics"] not in ENUM_NULL:
        errors.append(f"{col['name']}: null_semantics must be one of {ENUM_NULL}")
    if "role" in parsed and parsed["role"] not in ENUM_ROLE:
        errors.append(f"{col['name']}: role must be one of {ENUM_ROLE}")
    # Reject prose: any string value containing whitespace + alpha words that don't match a ref shape
    enum_keys = ("additivity","unit","null_semantics","role")
    for k, v in parsed.items():
        if k in enum_keys:
            continue  # enums already validated
        values = v if isinstance(v, list) else [v]
        for item in values:
            if isinstance(item, str) and " " in item and "." not in item:
                errors.append(f"{col['name']}.{k}: free-text value rejected — use enums/refs only")
    # source / time_basis / non_additive_dimension resolution checked against schema
    src = parsed.get("source")
    if src and not _resolve_physical_column(src):
        errors.append(f"{col['name']}.source: '{src}' does not resolve to a physical column")
    tb = parsed.get("time_basis")
    if tb and not _resolve_model_column(m, tb):
        errors.append(f"{col['name']}.time_basis: '{tb}' is not a Model column")
    nad = parsed.get("non_additive_dimension")
    if nad and not _resolve_model_column(m, nad):
        errors.append(f"{col['name']}.non_additive_dimension: '{nad}' is not a Model column")
if errors:
    raise SystemExit("ai_context validation failed:\n  " + "\n  ".join(errors))

# Surface 2 — column synonyms
# Re-run cross-column validation before writing (catches anything added during Step 7 review)
all_col_names_lower = {c["name"].lower(): c["name"] for c in m.get("columns", [])}
syn_to_cols_final = {}
for col in m.get("columns", []):
    delta = synonym_deltas.get(col["name"])
    merged = set(col.get("properties", {}).get("synonyms", []))
    if delta and delta["action"] == "ADD_PHRASES":
        merged |= set(delta["proposed_additions"])
    if delta and delta["action"] == "REMOVE":
        merged -= set(delta.get("remove_phrases", []))
    for syn in merged:
        syn_to_cols_final.setdefault(syn.lower(), []).append(col["name"])

build_errors = []
for col in m.get("columns", []):
    delta = synonym_deltas.get(col["name"])
    merged = set(col.get("properties", {}).get("synonyms", []))
    if delta and delta["action"] == "ADD_PHRASES":
        merged |= set(delta["proposed_additions"])
    if delta and delta["action"] == "REMOVE":
        merged -= set(delta.get("remove_phrases", []))
    validated = set()
    for syn in merged:
        # Rule 1: must not match another column's display name
        match = all_col_names_lower.get(syn.lower())
        if match and match != col["name"]:
            build_errors.append(f"[{col['name']}] synonym '{syn}' = column name [{match}] — skipped")
            continue
        # Rule 2: must not be on more than one column
        owners = syn_to_cols_final.get(syn.lower(), [])
        if len(owners) > 1 and owners[0] != col["name"]:
            build_errors.append(f"[{col['name']}] synonym '{syn}' duplicated across {owners} — skipped on this column")
            continue
        validated.add(syn)
    col["synonyms"] = sorted(validated)
    col.setdefault("properties", {})["synonym_type"] = "USER_DEFINED"

if build_errors:
    print("Synonym build warnings (entries skipped to prevent conflicts):")
    for e in build_errors:
        print(f"  {e}")

# Surface 6 — model description
if description_delta and description_delta["action"] in ("EXPAND","REWRITE"):
    m["description"] = description_delta["proposed_value"]

Run the Model TML self-validation checklist from ../../shared/schemas/thoughtspot-model-tml.md before serialising.

8c. Build the merged feedback TML (surfaces 3, 4)

Verified API behaviour (2026-04-27): the nls_feedback TML import wholesale REPLACES the Model's feedback collection with the payload. There is no append/merge mode at the API. Per open-items.md #18 the skill simulates merge-with-preservation by fetching existing entries first, then including them in the import payload alongside the new ones.

Step 1 — Use feedback fetched in Step 2b

The full feedback content was already retrieved in Step 2b (all_fb_entries). Reconstruct the flat list for round-trip preservation:

existing_entries = existing["existing_feedback_global"] + existing["existing_feedback_user"]

existing_entries contains the full content (search_tokens, formula_info, chart_type, display_mode, parent_question, access, rating, axis_config, etc.) — ready to round-trip back into the import.

Step 2 — Build the merged payload

Re-id the new entries to avoid collision with existing IDs, then concatenate. The API will "replace" the collection, but the collection now contains everything we want to keep:

def next_id(used_ids):
    n = 1
    while str(n) in used_ids: n += 1
    return str(n)

merged = list(existing_entries)
used_ids = {str(e.get("id","")) for e in merged}
for e in new_reference_questions + new_business_terms:
    e["id"] = next_id(used_ids); used_ids.add(e["id"])
    merged.append(e)

feedback_tml = {"guid": model_guid, "nls_feedback": {"feedback": merged}}

Step 3 — Generated entries must use verified-only syntax

Every emitted search_tokens, chart_type, display_mode, and axis_config value must follow the verified-working forms in feedback-tml-verified-patterns.md. Forms not yet verified (untested keywords / positions) must NOT be emitted — either drop the question or route via MOVE_TO_NEW_FORMULA (per #17) / DEFER.

8d. Save instructions.md (surface 5 — manual paste)

(run_dir / "instructions.md").write_text(generated_instructions)

8e. Final confirmation gate

Ready to apply coaching to "{model_name}":

  Column AI Context updates:    {N_ai_add} ADDs, {N_ai_refine} REFINEs
                                 (structured-only — declarative validation +
                                  ≤ 400 chars per column; see ai-context-schema.md
                                  § Safeguards)
  Column Description updates:   {N_desc_add} ADDs, {N_desc_refine} REFINEs
                                 (prose surface; ai_context contains no prose)
  Column Synonyms updates:      {N_syn_add} ADDs, {N_syn_keep} KEEPs
  Reference Questions to add:   {N_ref}      (existing: {N_existing_ref})
                                 ⚠ {N_existing_ref} existing entries WILL BE
                                  REPLACED by the import — see open-items.md #18
                                 ⚠ Formula-bearing tiers (t2.cumulative,
                                  t3.avg_per, t3.ratio, t3.share_of_total, t4.*)
                                  DEFERRED until #17 verified
                                 ⚠ Keyword-bearing tiers (t1.top_n,
                                  t2.recent_period, t2.this_vs_last,
                                  t3.year_filter) DEFERRED until #16 verified
  Business Terms to add:        {N_bt}        (existing: {N_existing_bt})
  Model description:            {DESCRIPTION_ACTION}
  Data Model Instructions:      {N_instr} draft rule(s), {N_instr_bytes}/3000
                                  chars — for manual paste (verified hard
                                  limit on Settings → Coach Spotter field)
                                  Includes: {N_col_meta} column_metadata entries,
                                  {N_hierarchies} hierarchy declarations

  Backup saved to:   {run_dir}/before/
  Patched files at:  {run_dir}/after/

Proceed with import? (Y / N):

If N, exit gracefully — leave the run dir in place so the user can re-run or hand-edit.

---

Step 9 — Import + Smoke Test

9a. Import patched Model TML (if any of surfaces 1, 2, 6 are non-empty)

source ~/.zshenv && ts tml import --file {run_dir}/after/model.tml \
  --profile "{profile_name}" --policy ALL_OR_NONE --no-create-new

CLI format: --file reads the raw TML file directly (ts-cli ≥ v0.27.0) — no JSON wrapping needed.

9b. Import feedback TML (if any of surfaces 3, 4 are non-empty)

source ~/.zshenv && ts tml import --file {run_dir}/after/feedback.tml \
  --profile "{profile_name}" --policy ALL_OR_NONE --no-create-new

Open item: standalone nls_feedback import vs bundled-with-model — see open-items.md #2 for the verification test.

9c. Smoke-test

Verification is split between two endpoints — --associated does NOT surface feedback entries even when they exist (verified open-items.md #2).

For Model TML changes (surfaces 1, 2, 6) — use --associated:

source ~/.zshenv && ts tml export {model_guid} \
  --profile "{profile_name}" --fqn --associated --parse

Parse the response and confirm column-by-column:

• All properties.ai_context updates round-tripped (NB: ai_context lives in properties.ai_context)
• All properties.synonyms[] additions round-tripped (NB: synonyms live in properties.synonyms, NOT at column-level — see open-items.md #3)
• model.description matches if updated

For feedback entries (surfaces 3, 4) — use ts metadata dependents:

import json, subprocess

result = subprocess.run(
    ["bash", "-c",
     f"source ~/.zshenv && ts metadata dependents {model_guid} --profile '{profile_name}'"],
    capture_output=True, text=True,
)
deps = json.loads(result.stdout)
feedback = [d for d in deps if d["type"] == "FEEDBACK"]

# Each entry: {source_guid, guid, name (= feedback_phrase), type, raw_bucket, ...}
expected_count = len(existing_entries) + ref_qs_added + bt_added
assert len(feedback) == expected_count, f"Expected {expected_count}, found {len(feedback)}"

Important: feedback import response interpretation:

• Successful imports return {header, status: {status_code: OK}} with empty diff: {} and no object field — different shape from Model TML imports
• Don't interpret empty diff as failure for feedback imports
• "Duplicates will be replaced" warnings are accurate (referencing prior-run entries)

Surface any silent drops to the user (known TML import failure modes per feedback_ts_tml_import_constraints memory). Common causes:

• Formula expression syntax invalid (BUSINESS_TERM with bad formulas) — entries drop with EDOC_FEEDBACK_TML_INVALID error in the response
• Invalid chart_type (e.g. TABLE is rejected; see open-items.md #11 for the verified valid set)
• References to columns/formulas no longer in the Model

9d. Final report

Coaching import complete for "{model_name}":

  AI Context applied:           {N_ai} columns
  Synonyms applied:             {N_syn} columns ({N_syn_phrases} total phrases)
  Reference Questions added:    {N_ref}
  Business Terms added:         {N_bt}
  Model description updated:    {Y/N}

  Data Model Instructions saved to: {run_dir}/instructions.md
    → paste these rules into the Spotter UI under Settings → Coach Spotter → Instructions

  Run directory: {run_dir}
  Rollback:      ts tml import --file {run_dir}/before/model.tml \
                 --profile {profile_name} --policy ALL_OR_NONE --no-create-new
                 (and feedback.tml if applicable)

Spotter will use the applied coaching on the next index refresh.

---

Error Handling

Symptom	Action
ts auth whoami returns 401	Token expired — /ts-profile-thoughtspot to refresh
Selected data source is a Worksheet	Tell user it must be upgraded to a Model first (no skill for this yet); stop
No dependent Liveboards/Answers found in Step 3a	Reduce reference question target; rely on schema + prose only
Snowflake mining returns empty / SAGE-only	Log and proceed; demo accounts often have no analyst SQL
Snowflake ACCOUNT_USAGE returns 0 rows for any DM_ table	Verify role has IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE
User edits malform a markdown table	Re-show with structural pointer; do not silently drop rows
Import returns 403 / UNAUTHORIZED	User lacks edit access on the Model — surface clearly, stop
Import: column not found	A token references a column that doesn't exist — rebuild without that token
Import: formula expression invalid	Re-open mappings.md for user fix
Smoke-test shows missing entries	Likely silent drop on bad reference; diff and flag to user
pyyaml not installed	pip install pyyaml

---

Cleanup

The run directory at ~/Dev/coaching-runs/{slug}-{ts}/ is preserved deliberately — backups + patched files are needed for rollback. To remove old runs:

find ~/Dev/coaching-runs -maxdepth 1 -mtime +30 -type d -exec rm -rf {} \;

---

Changelog

Version	Date	Summary
2.3.2	2026-07-11	Migrate Step 9a/9b/rollback imports to ts tml import --file (removes a broken | source ~/.zshenv && pipe that discarded the piped TML) (audit 5.1).
2.3.1	2026-07-03	Audit fixes: soften phantom /ts-object-model-builder recommendations (Step 1 and Error Handling) to "no skill for this yet — planned"; remove stale "CLI does not yet expose --include-dependent-objects" framing in Step 3a (it never did — ts metadata dependents is the command used). Same phantom-skill fix applied to references/ai-asset-review-rules.md.
2.3.0	2026-05-19	column_metadata + hierarchies categories added to model_instructions. Two new structured categories for agent disambiguation: column_metadata (cardinality tier, sample values, usage hint, value format per dimension column — requires Snowflake profile) and hierarchies (ordered drill-path declarations from coarse to fine grain). Allowed-key list updated (5 → 7 categories). Budget-trim order updated: output_formatting → samples → value_format → note:/reason: → aggregation_defaults; mandatory tier now includes hierarchies. Step 5 adds scope menu option 8; Step 6.5 adds generation algorithms (PII-gated cardinality queries, functional dependency validation for hierarchies, date-dim auto-detection); Step 7 adds column_metadata.md review file (Block 9 in review-explainers); Step 8b adds deploy-time validation for new enums/refs. Smoke test updated with structural validation steps for both categories.
2.2.0	2026-05-11	Migrate all direct urllib API calls to ts CLI: Step 2b feedback fetch now uses ts tml export --type FEEDBACK --parse (requires ts-cli v0.5.0); Step 3a dependents now use ts metadata dependents --raw; Step 9c smoke-test count now uses ts metadata dependents (flat output). Introduce existing_entries variable in Step 2b for consistent use in Steps 8a, 8c, 9c (replaces existing_feedback_entries).
2.1.1	2026-04-29	Document the 3000-char hard limit on the Settings → Coach Spotter → Instructions field (verified during a Dunder Mifflin coaching run on se-thoughtspot). references/model-instructions-schema.md Safeguard #3 adds the validation rule plus the budget-trim order (drop output_formatting first, then trim note: / reason: text, then collapse aggregation_defaults; never drop the mandatory tier of schema_assumptions / exclusion_rules / time_defaults). Step 6.5 Validation block points at the safeguard; Step 8e gate now displays {N_instr_bytes}/3000 chars so the user sees their headroom before pasting. Cursor mirror v1.1.1 syncs the same.