# TS Object Model Coach

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

- Skill: `thoughtspot/ts-object-model-coach` (Agent Skill, multi-file: 13 files)
- Install (CLI): `npx skillmds@latest add thoughtspot/ts-object-model-coach`
- Raw SKILL.md: https://api.skillmd.com/api/skills/thoughtspot/ts-object-model-coach/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: thoughtspot (https://skillmd.com/u/thoughtspot)
- Updated: 2026-09-21
- Page: https://skillmd.com/skills/thoughtspot/ts-object-model-coach

---


# 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](references/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](references/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](https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst/analyst-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 for **dependent** decisions. Batch **independent** questions
into a single prompt to cut round-trips.

---

## References

| File | Purpose |
|---|---|
| [references/open-items.md](references/open-items.md) | Unverified API behaviours; Data Model Instructions TML location |
| [references/question-taxonomy.md](references/question-taxonomy.md) | Deterministic candidate-question patterns (T1–T4) and ranking |
| [references/token-mapping-rules.md](references/token-mapping-rules.md) | NL → `search_tokens` translation; paraphrase variants; chart_type/display_mode inference |
| [references/prose-mining-rules.md](references/prose-mining-rules.md) | How to extract business phrases from Model description, Answer/Liveboard prose, tile names |
| [references/ai-asset-review-rules.md](references/ai-asset-review-rules.md) | Critique heuristics for existing ai_context / synonyms / description |
| [references/synonym-strategy-explainer.md](references/synonym-strategy-explainer.md) | Inline explainer for column synonyms vs BUSINESS_TERM coaching (shown to the user) |
| [references/review-explainers.md](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](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](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](../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](../../shared/schemas/thoughtspot-feedback-tml.md) | Coaching TML structure (output for surfaces 3 + 4) |
| [../../shared/schemas/thoughtspot-model-tml.md](../../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](../../shared/schemas/thoughtspot-answer-tml.md) | Answer TML — `name`, `description`, `search_query` mining input |
| [../../shared/schemas/thoughtspot-liveboard-tml.md](../../shared/schemas/thoughtspot-liveboard-tml.md) | Liveboard TML — visualization names + tile descriptions for prose mining |
| [../../shared/schemas/thoughtspot-formula-patterns.md](../../shared/schemas/thoughtspot-formula-patterns.md) | Formula syntax for answer-level formula generation |
| [../../shared/mappings/ts-snowflake/ts-snowflake-formula-translation.md](../../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.

```bash
ts auth whoami --profile "{profile_name}"
```

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

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

```bash
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](../ts-object-answer-promote/SKILL.md)).
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:

```python
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:

```bash
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)

```python
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:

```python
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:

```python
import json, subprocess

# Step 1: find the feedback object GUID via dependents
dep_result = subprocess.run(
    ["bash", "-c",
     f"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"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).

```python
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](references/open-items.md) #1 (independently verified for the
ts-dependency-manager skill on the `wip/ts-dependency-manager` branch) — fast (~2s),
VERIFIED on Cloud:

```python
import json, subprocess

result = subprocess.run(
    ["bash", "-c",
     f"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:

```sql
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](references/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](references/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](references/ai-asset-review-rules.md)).

```python
# 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](references/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](references/ai-asset-review-rules.md):
- **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](references/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

```python
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](references/cross-model-consistency.md), and
write `{run_dir}/cross_model_consistency.md` with the explainer block from
[review-explainers.md](references/review-explainers.md) Block 6 prepended.

Until the heuristic is calibrated against a live tenant
([open-items.md](references/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](references/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](references/ai-context-schema.md); concrete worked
examples are in [ai-context-examples.md](references/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).

```python
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}' dup

…(truncated)