---
name: carta-co-investors
description: >
  Interactive co-investor report from Carta SPA data with clickable portfolio
  drill-downs. Use for co-investor analysis or asking who invested in a
  specific portfolio company.
  Trigger phrases: "co-investors", "coinvestors", "who are my co-investors",
  "who else invested", "co-investors by stage/round", "co-investors on Aumni".
  Use instead: carta-explore-data for general fund/investment/portfolio data —
  this skill is specifically for co-investor ("who else invested alongside us")
  analysis.
version: 0.6.2
model: sonnet
allowed-tools:
  - mcp__carta__call_tool
  - mcp__carta__list_contexts
  - mcp__carta__set_context
  - mcp__Claude_Preview__preview_start
  - mcp__Claude_Preview__preview_list
  - mcp__Claude_Preview__preview_eval
  - AskUserQuestion
  - Read
  - Write
  - Bash(carta workspace cache *)
  - Bash(jq *)
  - Bash(uv run *)
  - Bash(test -f *)
  - Bash(test -d *)
  - Bash(find *)
  - Bash(date *)
  - Bash(mkdir -p *)
  - Bash(rm -f *)
  - Bash(open *)
---

<!-- Part of the official Carta AI Agent Plugin -->

# Co-investor analysis

Analyze who co-invests alongside you across your portfolio using Stock Purchase
Agreement (SPA) data uploaded to Carta. Supports both interactive visual reports
and direct analytical questions.

---

## UX patterns [PATTERN base v0.1.0]

### Typography and text formatting

Follow these rules every time except for machine-readable output (JSON, XML):

- **Casing:** Sentence case always — headings, titles, table column heads. No title case.
- **Punctuation:** No period at the end of headings or titles.
- **Bullets:** Always use `•` — never `-` or `*` for user-facing bullets. Numbered lists use `1.` `2.` `3.`
- **Dates:** `Mmm D, yyyy` format (e.g. `Jan 5, 2024`).
- **Currency (standard):** `$123,456`. Negative: `($445,443)`.
- **Null values:** Use `—` (em-dash), never `N/A`, blank, or prose like "not recorded".

### Tables

Always use Markdown tables for list output with more than one column.

- Numeric columns: right-aligned (header too).
- Text columns: left-aligned (header too).
- Add a single blank line after every table.
- Never use tables for lists of user actions — use `AskUserQuestion` instead.

### Writing style [PATTERN carta-writing-style v0.0.2]

Direct, calm, short sentences. Professional. No "please". Not sycophantic.

- Be clear — plain language, specific actions, understood on first read.
- Match mental models — use fund manager / investor vocabulary; favor domain terms.
- Be concise — say only what the user needs to move forward. No filler.
- Match tone to moment — neutral/direct for tasks; supportive for high-risk actions.
- Action-oriented language — buttons/links describe the action or outcome.
- **Never use "OK", "Submit", "Yes", or "No" as action labels.** Use specific verb + object.
- Never use humor for errors.

### Etiquette [PATTERN etiquette v0.0.6]

1. Always show the user a short (1–4 sentences max) summary of this skill's purpose, plus 2–3 brief bullets describing how it works, on first use.
2. After processing a request that changed data or made non-read tool calls, summarize what changed. Then, if appropriate, suggest 1–3 things the user might do next.

### Step transparency (required during execution)

After every major step, print a one-line status in plain language: what
completed and what comes next.
Example: `SPA data loaded across 23 companies. Building your report…`

Never go silent for more than one step. Never present results without a prior status line.

> **User-facing language — no internals, ever.** The user is a fund manager,
> not an engineer. Status lines, summaries, and error messages must use plain
> investor vocabulary only. **Never** expose any of the following to the user —
> not in a status line, a summary, an error, or an aside: query names
> (“Query S”, “Query R”), SQL, pagination/pages/offsets, `total_rows`, row or
> byte counts, blob/file paths, “Snowflake”/“DWH”/“ndjson”, latency or timing
> breakdowns, retries, UUIDs, or exit codes. Talk about *companies*,
> *co-investors*, *rounds*, and *SPA coverage* — never the machinery that
> produces them. (Example of what NOT to say: “Query S returned 2,837 rows
> across 3 pages.” Say: “SPA data loaded.”)

### Carta watermark [PATTERN carta-watermark v0.0.10]

Every time you respond in natural language to a human user using this skill, show
this Carta ASCII logo at the start of the response:

```
┌───────┐
│ carta │
└───────┘
```

---

## Prerequisites

This skill assumes:

- **Carta MCP connection** — `list_contexts` and `fetch` tools available; user has an active session for at least one investment firm.
- **SPA documents uploaded** — the firm has Stock Purchase Agreements in Carta's
  Document Intelligence; without them, Mode A will stop with a "no SPAs found"
  message.
- **Local execution environment** — `Bash` + `uv` are available to run
  `process.py` and `generate_artifact.py`. Hosted runtimes that block subprocess
  execution (e.g. some claude.ai surfaces) will hit the Step A3 fallback and
  degrade to Mode B text analysis.

## Accessibility

The interactive artifact generated by `generate_artifact.py` has **not yet been
formally audited for WCAG 2.1 AA compliance.** Known accessibility considerations:

- Color-only encoding is avoided (entity-type tags use both color and text labels)
- Tooltips are keyboard-focusable (`tabindex="0"`) with `aria-describedby`
- The drawer close button is a real `<button>` with an SVG icon
- Tables use proper `<thead>` / `<tbody>` semantics

Users who need a WCAG-compliant text view should request Mode B output explicitly
("just tell me", "no file", "quick summary").

---

## When to use

- "Who are my most frequent co-investors?"
- "Show me an interactive co-investor report"
- "Who co-invested with me most often?"
- "Who are my most frequent co-investors with more than 5% of a round?"
- "Who co-invested in [Company Name]?"

---

## Step 0: Announce

Open every invocation with:

> "I'll analyze co-investors across your portfolio using SPA data uploaded to
> Carta — pulling data across all your funds. I'll normalize fund vehicles so
> each firm counts once, and build the interactive report. Larger portfolios
> take a moment."

Then proceed immediately to Step 1.

---

## Step 1: Establish firm context

1. Call `list_contexts` to get the firm UUID and display name.
2. If nothing returned → stop with: "I couldn't find any Carta data associated
   with your account. Try reconnecting to the Carta MCP server. If you believe
   you're already connected, contact your Carta representative."
3. Call `call_tool({"name": "fa__list__entities", "arguments": {}})` and extract:
   - `firm_carta_id` from any entity in the response — the integer PK used in
     Carta web URLs.
   - `firm_vehicle_names` — collect the `name` of every entity returned. These
     are the firm's own fund vehicles. Use them as additional firm-exclusion
     patterns in Step A1 — without this, vehicles named differently from the
     parent firm slip into the co-investor results.
4. Store:
   - `<firm_id>` — the UUID (36-char string)
   - `<firm_name>` — display name from `list_contexts`
   - `<firm_carta_id>` — integer PK from `fa:list:entities`
   - `<firm_vehicle_names>` — array of fund vehicle name strings
5. Resolve `<base_url>` from the current Carta MCP server URL — never hardcode
   an environment URL. For the production MCP server (`mcp.app.carta.com`),
   `<base_url>` is `https://app.carta.com`. For any other MCP server URL,
   default to `https://app.carta.com` as well.

**Pre-flight check:** before any DWH query, confirm that `<firm_id>` is a
non-empty UUID string (matches pattern `[a-f0-9-]{36}`). If not, stop with:
"Could not determine your firm ID. Try reconnecting to the Carta MCP server.
If you believe you're already connected, contact your Carta representative."

Tell the user: `Firm context loaded: <firm_name>. Fetching SPA data…`

---

## Step 2: Route

**The default output of this skill is the interactive artifact. Proceed directly to Step A1.**

Only route to **text-only analysis (Mode B)** if the user explicitly signals they want text:
- Says "text only", "no file", "just tell me", or "quick summary" → Q1
- Mentions a specific company by name → Q4
- Mentions ">5%", "over 5%", "lead", or "leads" → Q2
- Mentions "<5%", "less than 5%", "under 5%", or "follow-on" → Q3

**Everything else — including any general request about co-investors — goes to Step A1.**

---

## Mode A — Interactive artifact

Generate a self-contained interactive HTML file showing the firm's most frequent
co-investors. Each portfolio company in the table is clickable — clicking it opens
a right-side drawer with the full investor breakdown for that company (investors,
% of round, amount paid).

### Step A1: Fetch SPA data

Resolve a stable, cross-platform working directory once before fetching.
The intermediate response files and the final HTML artifact all live under
`$WORKSPACE`. **Both the Claude process AND the preview-panel host must be
able to read this path** — on Cowork demo VMs running macOS 26.5+ the host
can no longer see `~/.cache/...` or `/tmp/...`. The probe below picks the
right path automatically: Cowork sandboxes get `$HOME/mnt/outputs/` (the
bind-mounted session outputs dir, visible from both VM and host), regular
Claude Code CLI laptops get `carta workspace cache`, and anything else
falls back to `$TMPDIR`.

```bash
# --- Workspace probe -------------------------------------------------
if [ -d "${HOME}/mnt/outputs" ] && [ -w "${HOME}/mnt/outputs" ]; then
  # Cowork sandbox: $HOME is the session root (/sessions/<name>) and
  # mnt/outputs/ is the bind mount the macOS host sees as
  # ~/Library/Application Support/Claude/.../outputs/. Writes here are
  # readable by both the sandboxed Claude process and the host
  # preview-panel process.
  WORKSPACE="${HOME}/mnt/outputs/carta-co-investors"
elif command -v carta >/dev/null 2>&1; then
  # Regular Claude Code CLI on a developer laptop.
  WORKSPACE=$(carta workspace cache carta-co-investors | jq -r .)
else
  # Last-resort fallback (e.g. CI / hosted runtimes without Carta CLI).
  WORKSPACE="${TMPDIR:-/tmp}/carta-co-investors"
fi
mkdir -p "$WORKSPACE"

# --- Plugin install probe (SKILL_DIR) --------------------------------
# Claude Code CLI exports CLAUDE_PLUGIN_ROOT and substitutes it inline
# in skill content. Cowork's harness DOES NOT export this variable and
# does not substitute ${CLAUDE_PLUGIN_ROOT}, so any literal reference
# would resolve to an empty string or a host-side macOS path the
# sandbox can't read. This probe resolves the install path in both
# environments without depending on harness substitution.
if [ -n "${CLAUDE_PLUGIN_ROOT:-}" ] && [ -d "$CLAUDE_PLUGIN_ROOT/skills/carta-co-investors" ]; then
  SKILL_DIR="$CLAUDE_PLUGIN_ROOT/skills/carta-co-investors"
else
  # Cowork: the plugin is bind-mounted at $HOME/mnt/.remote-plugins/plugin_*/skills/carta-co-investors
  SKILL_DIR=$(find "${HOME}/mnt/.remote-plugins" -maxdepth 3 -type d -name "carta-co-investors" 2>/dev/null | head -1)
fi
if [ -z "${SKILL_DIR:-}" ] || [ ! -d "$SKILL_DIR/scripts" ]; then
  echo "ERROR: could not locate carta-co-investors skill install dir" >&2
  exit 1
fi

# --- Blob path resolver ----------------------------------------------
# dwh:execute:query with format="ndjson" returns the body as a blob the
# MCP client auto-persists. The tool result surfaces the host path in a
# "saved to <PATH>" line. resolve_blob translates that to a path THIS
# shell can read:
#   * Claude Code CLI — bash runs on the host, so the saved path is
#     directly readable; use it as-is.
#   * Cowork sandbox — bash can't see the macOS host path (/var/folders/…),
#     but the SAME blob is exposed read-only at a bindfs mount under
#     $HOME/mnt/.claude/projects/. The blob filename is unique
#     (mcp-…-blob-<ts>-<rand>.bin), so locate it by basename.
# Prints the readable path and returns 0, or prints nothing and returns 1.
resolve_blob() {
  saved="$1"
  if [ -r "$saved" ]; then echo "$saved"; return 0; fi
  hit=$(find "${HOME}/mnt/.claude/projects" -name "$(basename "$saved")" 2>/dev/null | head -1)
  if [ -n "$hit" ] && [ -r "$hit" ]; then echo "$hit"; return 0; fi
  return 1
}
```

All file paths below assume this `$WORKSPACE`. Do not hardcode `/tmp` — it
breaks on Windows and is invisible to the Cowork host on macOS 26.5+.

Run **two** server-aggregated DWH queries, both with `"format": "ndjson"`.
**Fire them in parallel — issue both `fetch` calls in the SAME turn** (two tool
calls in one assistant message) so they run concurrently. They are fully
independent (Query S = co-investors, Query R = per-company rounds) and each is a
single fetch, so there is nothing to chain. Do **not** wait for Query S's blob
to return before issuing Query R — firing them sequentially just doubles the
wait (the fetch phase should take `max(S, R)`, not `S + R`).

**How the ndjson response is delivered (important — read before fetching):**
With `format: "ndjson"`, carta-mcp returns the body as a blob the MCP client
auto-persists to disk. The tool result is a small ack plus a line of the form:

> `Binary content (application/x-ndjson, N bytes) saved to <ABSOLUTE_PATH>`

**The row data never enters your context** — only that path does. Capture the
`<ABSOLUTE_PATH>` from each query's `saved to …` line, then resolve it to a
readable path with the `resolve_blob` function defined in the Step A1 probe
block above (it handles Claude Code CLI, where the path is directly readable,
and Cowork, where the same blob is exposed at a bind-mounted sandbox path):

```bash
QUERY_S_BLOB=$(resolve_blob "<query_s_saved_path>")
QUERY_R_BLOB=$(resolve_blob "<query_r_saved_path>")
```

Pass the resolved paths straight to `process.py` in Step A2. **Do not `Write`
the bodies** — they're already on disk, and re-emitting them through the
model defeats the whole point of ndjson (keeping the payload out of context).

**If `resolve_blob` returns non-zero** (the saved file couldn't be located —
rare), re-run that one query once and resolve again. This is an internal
retry — do **not** narrate paths, "blob", "sandbox", or any other
mechanics to the user, and do **not** ask the user anything. If it still
fails after the retry, stop with this plain message and nothing else:

> "I couldn't load your SPA data just now. Try running the report again in
> a moment. If it keeps happening, contact your Carta representative."

**Why two queries, not four:** Snowflake does all aggregation (canonical grouping
happens in Python, but per-round/per-company nesting and coverage counts happen
server-side). This collapses ~700 raw investor-round rows into ~30–40 nested rows,
removing the batched-alphabetic split the prior version relied on.

**Build these substitutions first:**
- `<firm_name_esc>` — escape single quotes in `<firm_name>` (replace `'` with `''`)
- `<firm_name_spaced>` — insert a space before each uppercase letter that follows
  a lowercase letter (e.g. `"AcmeVentures"` → `"Acme Ventures"`). Only add the
  corresponding ILIKE clause if the result differs from `<firm_name_esc>`.
- For each name in `<firm_vehicle_names>`, escape single quotes to get `<vehicle_N_esc>`.

Items in `[...]` below are conditional — include only when the substituted
value is non-empty and differs from the `<firm_name_esc>` clause.

---

**Query S** — all purchasers (ranked by number of shared companies), with SPA
coverage and total-company counts embedded in every row. Returns one row per
purchaser; large firms can have thousands of rows.

**Fetch it in ONE call — do NOT paginate.** Pass `"format": "ndjson"` **and a
high `"limit"` (use `50000`)** so the entire result set comes back in a single
fetch. `limit: 50000` comfortably covers even the largest firms in a single
call (no `next_offset`), and the ndjson body lands on disk regardless of size,
so there is no payload-cap reason to split it. Capture the `saved to …` path
from the result and resolve it to `$QUERY_S_BLOB` via the helper (above). Do
not `Write` the body.

> **CRITICAL — fetch ONCE; never re-issue this query with a different
> `offset`.** A single high-`limit` fetch returns everything, so there is no
> page 2. Do **not** re-run the query for "later pages." The reason this rule
> exists: when the model re-types this ~1,200-char SQL for a second call it
> reliably corrupts a token — the embedded firm UUID (`…8af6…` → `…8ad6…`) or
> even a JOIN key (`s.EXTRACTION_ID` → `s.CLOSING_DATE`, which errors with
> *"Date '<uuid>' is not recognized"*) — silently dropping rows or failing the
> call. One fetch means the SQL is authored exactly once and this whole class
> of error cannot happen.

> **If the row count ever exceeds the limit** (it won't at `50000` for any real
> firm — flag it as a data anomaly rather than paginating): the safe response
> is to raise the `limit` further in the same single call, never to add
> `offset` pages.

> **Why the ORDER BY has a tiebreaker:** Query S ends with
> `ORDER BY COUNT(DISTINCT cc.CANONICAL_NAME) DESC, p.PURCHASER_NAME, p.ENTITY_TYPE`.
> The leading count is not unique (many purchasers tie); the trailing
> `p.PURCHASER_NAME, p.ENTITY_TYPE` (the GROUP BY key) makes the order total
> and deterministic. Keep it.

> **Why the `canonical_company` CTE:** SPA issuer names aren't consistently
> cased — the same portfolio company can appear as "PIE Group Holdings, Inc.",
> "PIE GROUP HOLDINGS, INC.", and "Pie Group Holdings, Inc." across three SPAs.
> The `canonical_company` CTE collapses these variants by `LOWER(TRIM(name))`
> and picks one canonical spelling per group: the matching name from the SOI
> (`AGGREGATE_INVESTMENTS`) when it exists, else the alphabetically-largest
> raw spelling (which, in ASCII, prefers Title Case over ALL CAPS). The chip
> column, per-co-investor company count, and Query R's per-company rounds all
> use the canonical name — so one company stays one row regardless of how many
> casings show up in the SPAs.

```
call_tool({"name": "dwh__execute__query", "arguments": {
  "format": "ndjson",
  "limit": 50000,
  "sql": "WITH soi_norm AS (SELECT DISTINCT ai.ISSUER_NAME AS SOI_NAME, LOWER(TRIM(ai.ISSUER_NAME)) AS NORM_KEY FROM FUND_ADMIN.AGGREGATE_INVESTMENTS ai WHERE ai.FIRM_ID = '<firm_id>' AND (ai.ASSET_NAME ILIKE '%Series %' OR ai.ASSET_NAME ILIKE '%Preferred%' OR ai.ASSET_NAME ILIKE '%Common%')), spa_issuer_norm AS (SELECT DISTINCT i.ISSUER_NAME AS RAW_NAME, LOWER(TRIM(i.ISSUER_NAME)) AS NORM_KEY FROM FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i JOIN FUND_ADMIN.DOCUMENT_AI_DOCUMENT d ON i.EXTRACTION_ID = d.EXTRACTION_ID WHERE d.FIRM_ID = '<firm_id>' AND i.ISSUER_NAME IS NOT NULL AND TRIM(i.ISSUER_NAME) <> ''), canonical_company AS (SELECT sin.NORM_KEY, COALESCE(MIN(soi.SOI_NAME), MAX(sin.RAW_NAME)) AS CANONICAL_NAME FROM spa_issuer_norm sin LEFT JOIN soi_norm soi ON sin.NORM_KEY = soi.NORM_KEY GROUP BY sin.NORM_KEY), doc_metadata AS (SELECT d.EXTRACTION_ID, cc.CANONICAL_NAME AS ISSUER_NAME, s.CLOSING_DATE, MIN(p.SHARE_CLASS_NAME) AS SHARE_CLASS_NAME FROM FUND_ADMIN.DOCUMENT_AI_DOCUMENT d JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON d.EXTRACTION_ID = i.EXTRACTION_ID JOIN canonical_company cc ON LOWER(TRIM(i.ISSUER_NAME)) = cc.NORM_KEY LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON d.EXTRACTION_ID = s.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON d.EXTRACTION_ID = p.EXTRACTION_ID WHERE d.FIRM_ID = '<firm_id>' AND i.ISSUER_NAME IS NOT NULL AND TRIM(i.ISSUER_NAME) <> '' GROUP BY d.EXTRACTION_ID, cc.CANONICAL_NAME, s.CLOSING_DATE), dedup_docs AS (SELECT MAX(EXTRACTION_ID) AS EXTRACTION_ID FROM doc_metadata GROUP BY ISSUER_NAME, COALESCE(CAST(CLOSING_DATE AS VARCHAR), SHARE_CLASS_NAME, 'undated')), coverage AS (SELECT (SELECT COUNT(*) FROM soi_norm soi WHERE EXISTS (SELECT 1 FROM spa_issuer_norm sin WHERE sin.NORM_KEY = soi.NORM_KEY)) AS SPA_COMPANIES, (SELECT COUNT(*) FROM soi_norm) AS TOTAL_COMPANIES) SELECT p.PURCHASER_NAME, p.ENTITY_TYPE, ARRAY_AGG(DISTINCT cc.CANONICAL_NAME) WITHIN GROUP (ORDER BY cc.CANONICAL_NAME) AS COMPANIES, (SELECT SPA_COMPANIES FROM coverage) AS SPA_COMPANIES, (SELECT TOTAL_COMPANIES FROM coverage) AS TOTAL_COMPANIES FROM dedup_docs dd JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON dd.EXTRACTION_ID = i.EXTRACTION_ID JOIN canonical_company cc ON LOWER(TRIM(i.ISSUER_NAME)) = cc.NORM_KEY JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON dd.EXTRACTION_ID = p.EXTRACTION_ID WHERE p.ENTITY_TYPE NOT ILIKE '%notice%' AND p.ENTITY_TYPE NOT ILIKE '%law firm%' AND p.PURCHASER_NAME NOT ILIKE '%<firm_name_esc>%' [AND p.PURCHASER_NAME NOT ILIKE '%<firm_name_spaced>%'] [AND p.PURCHASER_NAME NOT ILIKE '%<vehicle_N_esc>%' ...] GROUP BY p.PURCHASER_NAME, p.ENTITY_TYPE ORDER BY COUNT(DISTINCT cc.CANONICAL_NAME) DESC, p.PURCHASER_NAME, p.ENTITY_TYPE"
}})
```

---

**Query R** — one row per portfolio company. The `ROUNDS_JSON` column is a
compact JSON string produced by `TO_JSON(ARRAY_AGG(OBJECT_CONSTRUCT(...)))`,
nesting up to 15 investors per round, ordered by % of round descending. Short
keys (`n`, `t`, `p`, `a`, `f`, `sc`, `cd`, `inv`) keep the payload small.

Same as Query S — **one fetch** with `"format": "ndjson"` and `"limit": 50000`
(never paginate; the fetch-once rule above applies here too). Capture the
`saved to …` path, resolve it to `$QUERY_R_BLOB`. Do not `Write` the body.

```
call_tool({"name": "dwh__execute__query", "arguments": {
  "format": "ndjson",
  "limit": 50000,
  "sql": "WITH soi_norm AS (SELECT DISTINCT ai.ISSUER_NAME AS SOI_NAME, LOWER(TRIM(ai.ISSUER_NAME)) AS NORM_KEY FROM FUND_ADMIN.AGGREGATE_INVESTMENTS ai WHERE ai.FIRM_ID = '<firm_id>' AND (ai.ASSET_NAME ILIKE '%Series %' OR ai.ASSET_NAME ILIKE '%Preferred%' OR ai.ASSET_NAME ILIKE '%Common%')), spa_issuer_norm AS (SELECT DISTINCT i.ISSUER_NAME AS RAW_NAME, LOWER(TRIM(i.ISSUER_NAME)) AS NORM_KEY FROM FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i JOIN FUND_ADMIN.DOCUMENT_AI_DOCUMENT d ON i.EXTRACTION_ID = d.EXTRACTION_ID WHERE d.FIRM_ID = '<firm_id>' AND i.ISSUER_NAME IS NOT NULL AND TRIM(i.ISSUER_NAME) <> ''), canonical_company AS (SELECT sin.NORM_KEY, COALESCE(MIN(soi.SOI_NAME), MAX(sin.RAW_NAME)) AS CANONICAL_NAME FROM spa_issuer_norm sin LEFT JOIN soi_norm soi ON sin.NORM_KEY = soi.NORM_KEY GROUP BY sin.NORM_KEY), doc_metadata AS (SELECT d.EXTRACTION_ID, cc.CANONICAL_NAME AS ISSUER_NAME, s.CLOSING_DATE, MIN(p.SHARE_CLASS_NAME) AS SHARE_CLASS_NAME FROM FUND_ADMIN.DOCUMENT_AI_DOCUMENT d JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON d.EXTRACTION_ID = i.EXTRACTION_ID JOIN canonical_company cc ON LOWER(TRIM(i.ISSUER_NAME)) = cc.NORM_KEY LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON d.EXTRACTION_ID = s.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON d.EXTRACTION_ID = p.EXTRACTION_ID WHERE d.FIRM_ID = '<firm_id>' GROUP BY d.EXTRACTION_ID, cc.CANONICAL_NAME, s.CLOSING_DATE), dedup_docs AS (SELECT MAX(EXTRACTION_ID) AS EXTRACTION_ID FROM doc_metadata GROUP BY ISSUER_NAME, COALESCE(CAST(CLOSING_DATE AS VARCHAR), SHARE_CLASS_NAME, 'undated')), investor_rows AS (SELECT cc.CANONICAL_NAME AS ISSUER_NAME, p.SHARE_CLASS_NAME, s.CLOSING_DATE, dd.EXTRACTION_ID, p.PURCHASER_NAME, p.ENTITY_TYPE, p.SHARES_PURCHASED, p.TOTAL_AMOUNT_PAID, p.SHARES_PURCHASED / NULLIF(SUM(p.SHARES_PURCHASED) OVER (PARTITION BY dd.EXTRACTION_ID), 0) AS PCT_OF_ROUND, CASE WHEN p.PURCHASER_NAME ILIKE '%<firm_name_esc>%' [OR p.PURCHASER_NAME ILIKE '%<firm_name_spaced>%'] [OR p.PURCHASER_NAME ILIKE '%<vehicle_N_esc>%' ...] THEN TRUE ELSE FALSE END AS IS_FIRM, ROW_NUMBER() OVER (PARTITION BY dd.EXTRACTION_ID ORDER BY p.SHARES_PURCHASED DESC NULLS LAST) AS RN FROM dedup_docs dd JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON dd.EXTRACTION_ID = i.EXTRACTION_ID JOIN canonical_company cc ON LOWER(TRIM(i.ISSUER_NAME)) = cc.NORM_KEY JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON dd.EXTRACTION_ID = p.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON dd.EXTRACTION_ID = s.EXTRACTION_ID WHERE p.ENTITY_TYPE NOT ILIKE '%notice%' AND p.ENTITY_TYPE NOT ILIKE '%law firm%'), per_round AS (SELECT ISSUER_NAME, SHARE_CLASS_NAME, CLOSING_DATE, EXTRACTION_ID, ARRAY_AGG(OBJECT_CONSTRUCT('n', PURCHASER_NAME, 't', ENTITY_TYPE, 'p', ROUND(PCT_OF_ROUND, 4), 'a', TOTAL_AMOUNT_PAID, 'f', IS_FIRM)) WITHIN GROUP (ORDER BY PCT_OF_ROUND DESC NULLS LAST) AS INVESTORS FROM investor_rows WHERE RN <= 15 GROUP BY ISSUER_NAME, SHARE_CLASS_NAME, CLOSING_DATE, EXTRACTION_ID) SELECT ISSUER_NAME, TO_JSON(ARRAY_AGG(OBJECT_CONSTRUCT('sc', SHARE_CLASS_NAME, 'cd', CLOSING_DATE, 'inv', INVESTORS)) WITHIN GROUP (ORDER BY CLOSING_DATE DESC NULLS LAST)) AS ROUNDS_JSON FROM per_round GROUP BY ISSUER_NAME ORDER BY ISSUER_NAME"
}})
```

> The `RN <= 15` cap keeps the per-round investor list bounded. The long
> tail past the 15th investor has near-zero percentage of round and is
> not rendered prominently in the drill-down view.

Query R is one row per portfolio company (10s–100s of rows even for large
firms), so the single `"limit": 50000` fetch always covers it in one call —
the same fetch-once rule as Query S. Never re-issue it with an `offset`.

If Query S returns 0 rows: stop with "No SPA documents were found for your account.
Contact your Carta representative if you believe this is an error."

Tell the user: `SPA data loaded. Assembling report…`

### Step A2: Assemble JSON

```bash
uv run "$SKILL_DIR/scripts/process.py" \
  --summary "$QUERY_S_BLOB" \
  --rounds  "$QUERY_R_BLOB" \
  --firm-name "<firm_name>" \
  --firm-carta-id "<firm_carta_id>" \
  --canonical "$SKILL_DIR/canonical-investors.json" \
  --out "$WORKSPACE/carta-co-investors-data.json"
```

The `--summary` / `--rounds` inputs are the **resolved blob paths** from
Step A1 (`$QUERY_S_BLOB`, `$QUERY_R_BLOB`), not files the skill wrote. Each
query is a single fetch, so pass **one** `--summary` and **one** `--rounds`.
(Both flags still accept multiple values — `process.py` concatenates them — but
with single-fetch queries there is only ever one blob per query, so do not
synthesize extra paths.) SPA-coverage counts ride on the Query S rows, and the
output (`carta-co-investors-data.json`) is written to `$WORKSPACE`.

- **Exit 0** — data written to `$WORKSPACE/carta-co-investors-data.json`. Proceed.
- **Non-zero exit** — show the script's stderr output to the user and stop.

### Step A3: Generate HTML artifact

> **You MUST run `generate_artifact.py` to produce the HTML.** Do **not** write,
> compose, inline, or "improvise" HTML for the artifact under any
> circumstances. The template (`artifact-template.html`) is the single source
> of truth for the artifact's structure, styling, tile labels, tabs (none),
> and interactive drawer behavior. Hand-written or model-generated HTML
> diverges from the design system, omits required tooltips and Ink tags, and
> has produced silent "Could not load data" failures in past sessions.

```bash
uv run "$SKILL_DIR/scripts/generate_artifact.py" \
  --data "$WORKSPACE/carta-co-investors-data.json" \
  --title "<firm_name> — Co-investor analysis" \
  --out "$WORKSPACE/carta-co-investors.html"
```

**If you cannot execute Bash / `uv` in your current environment** (e.g. some
hosted runtimes block subprocess execution): stop and tell the user
verbatim:

> "I can't generate the interactive artifact here because this environment
> doesn't allow running the artifact script. Try the same prompt in Claude
> Code, or ask me for a text summary instead."

Then offer Mode B (text analysis) as an alternative. Do **not** fabricate
an HTML file to fill the gap.

Tell the user: `Artifact generated. Opening preview panel…`

### Step A4: Open in preview panel

1. Read `.claude/launch.json` if it exists. Start with
   `{"version":"0.0.1","configurations":[]}` if it doesn't.
2. Upsert the co-investor config — add or replace any entry whose `name` starts
   with `carta-co-investors-`:

```json
{
  "name": "carta-co-investors-<firm_carta_id>",
  "runtimeExecutable": "uv",
  "runtimeArgs": [
    "run", "python",
    "${CLAUDE_PLUGIN_ROOT}/skills/carta-co-investors/scripts/preview_server.py",
    "--serve-dir", "<workspace_path>"
  ],
  "autoPort": true
}
```

> **Why `uv run python` and not a bare `python3` path?** Claude Desktop spawns
> `launch.json` processes outside a normal shell. Calling `python3` directly
> can trigger pyenv's shim and fail silently in some setups; an absolute Unix
> path like `/usr/bin/python3` doesn't exist on Windows. `uv` is installed on
> PATH by the Carta plugin installer on every platform and `uv run python`
> guarantees the right interpreter without environment leakage. Same pattern
> as the published `carta-form-adv` skill.

3. Write the merged config back to `.claude/launch.json`.
4. Call `preview_start`.
5. Call `preview_list` — find the entry matching `carta-co-investors-<firm_carta_id>`. Extract `port` and `serverId`.
6. Call `preview_eval` passing `serverId` as the target server parameter and this JavaScript:

```javascript
window.location.href = 'http://localhost:<port>/carta-co-investors.html';
```

> Substitute `<workspace_path>` with the value of `$WORKSPACE` resolved in
> Step A1. `launch.json` does not expand environment variables, so the path
> must be a literal string.

**Fallback** — if `preview_start` is unavailable, run `open "$WORKSPACE/carta-co-investors.html"`
and tell the user to look in their browser.

Tell the user:

> "Report ready: <N> co-investors across <M> portfolio companies (SPA coverage:
> <spa_companies> of <total_companies>). Click any company to drill into the full
> investor breakdown."
>
> Data as of <generatedAt>.
> [View SPA source documents in Carta](<base_url>/investors/firm/<firm_carta_id>/portfolio/documents/)

### Step A5: Clean up

Nothing to clean. The skill no longer writes intermediate response files —
the ndjson query bodies are blobs the MCP client persists into its own
session-scoped `tool-results/` directory (read-only from the sandbox, and
garbage-collected when the session ends). The only thing the skill writes to
`$WORKSPACE` is the assembled `carta-co-investors-data.json` and the HTML
artifact, both of which are intended to persist.

---

## Mode B — Text analysis

Answer specific analytical questions about co-investors using aggregation queries.

### Step B0: Reuse cached data if available

Before fetching anything, resolve the workspace and check for the assembled
artifact data from a prior Mode A run. Use the same Cowork-aware probe as
Step A1 so a cached file from a Mode A run on the same machine is actually
discoverable here.

```bash
if [ -d "${HOME}/mnt/outputs" ] && [ -w "${HOME}/mnt/outputs" ]; then
  WORKSPACE="${HOME}/mnt/outputs/carta-co-investors"
elif command -v carta >/dev/null 2>&1; then
  WORKSPACE=$(carta workspace cache carta-co-investors | jq -r .)
else
  WORKSPACE="${TMPDIR:-/tmp}/carta-co-investors"
fi
mkdir -p "$WORKSPACE"
test -f "$WORKSPACE/carta-co-investors-data.json" && \
  find "$WORKSPACE/carta-co-investors-data.json" -mmin -60 -print
```

If the file exists **and** is less than 60 minutes old, it contains everything
Q1 and Q4 need — `coInvestors` (canonical-grouped, ranked by company count)
and `companyRounds[<company>]` (per-round investor breakdowns with `name`,
`entityType`, `pctOfRound`, `amountPaid`, `isFirm`, `shareClass`, `closingDate`).

**Cache-served question types:**
- **Q1** — read `data.coInvestors` directly; map to the Q1 output table.
- **Q4** — read `data.companyRounds[<matched_company>]` directly; render each
  round as a separate section. Use `data.meta.firmName` for the title and
  `data.meta.firmCartaId` for the source-documents link.

**For Q1 and Q4, tell the user:** `Using cached SPA data from $(date -r "$WORKSPACE/carta-co-investors-data.json" "+%H:%M"). Preparing results…` then skip Step B2 entirely and proceed to Step B3.

**Fall through to Step B2 (DWH fetch) when:**
- The cache file does not exist
- The cache file is older than 60 minutes
- The question type is Q2 or Q3 (those need per-round percentage math the cache doesn't precompute)
- The user explicitly asks to refresh (e.g. "rerun", "fresh data")

### Step B1: Infer scope and question type

Default to **all funds** — do not ask the user to confirm scope unless they
specifically request a single fund.

Infer the **question type** from `$ARGUMENTS`:
- Company name mentioned → Q4 (company-specific)
- ">5%" or "lead" mentioned → Q2 (frequent leads)
- "<5%" or "less than" mentioned → Q3 (frequent below-threshold)
- Otherwise → **Q1 (most frequent overall)**

Only ask a clarifying question if the request is genuinely ambiguous (e.g. a
company name that could match multiple issuers).

### Step B2: Fetch data

Use `call_tool({"name": "dwh__execute__query", "arguments": {...}})` with the appropriate query below.

Run the main query **in parallel** with the coverage queries (B and C from Mode A).

> **SPA deduplication:** all queries open with `doc_metadata` + `dedup_docs` CTEs
> that select `MAX(EXTRACTION_ID)` per `(ISSUER_NAME, COALESCE(CLOSING_DATE, SHARE_CLASS_NAME, 'undated'))`.
> This deduplicates duplicate uploads while preserving genuine multiple rounds.

**Standard exclusion filters (add to every WHERE clause):**

```sql
AND p.PURCHASER_NAME NOT ILIKE '%<firm_name>%'
AND p.PURCHASER_NAME NOT ILIKE '%<firm_name_spaced>%'
AND p.ENTITY_TYPE NOT ILIKE '%notice%'
AND p.ENTITY_TYPE NOT ILIKE '%law firm%'
```

For `<firm_name_spaced>`: insert a space before any digit sequence that follows
a letter (e.g. "Capital99" → "Capital 99").

> If a query fails with a table-not-found error: call `call_tool({"name": "dwh__list__tables", "arguments": {}})`
> to confirm available table names, then retry with the correct names.

#### Q1 — Most frequent overall

> **Cache-first:** if Step B0 found a fresh `carta-co-investors-data.json`,
> read `coInvestors` from it (already canonical-grouped and ranked) instead
> of running this query. Only fall back to the DWH query when the cache is
> absent or stale.

Identical to Mode A's Query A. **Run Step A1.0 first** to assemble
`<CANONICAL_CASE>` from `canonical-investors.json` — the model needs that even
when Mode A wasn't invoked. The result schema (`CANONICAL_NAME`, `ENTITY_TYPE`,
`COMPANY_COUNT`, `COMPANIES`, `RAW_NAMES`) maps directly to the Q1 output
table below.

```
call_tool({"name": "dwh__execute__query", "arguments": {
  "sql": "WITH doc_metadata AS (SELECT d.EXTRACTION_ID, i.ISSUER_NAME, s.CLOSING_DATE, MIN(p.SHARE_CLASS_NAME) AS SHARE_CLASS_NAME FROM FUND_ADMIN.DOCUMENT_AI_DOCUMENT d JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON d.EXTRACTION_ID = i.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON d.EXTRACTION_ID = s.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON d.EXTRACTION_ID = p.EXTRACTION_ID WHERE d.FIRM_ID = '<firm_id>' AND i.ISSUER_NAME IS NOT NULL AND TRIM(i.ISSUER_NAME) <> '' GROUP BY d.EXTRACTION_ID, i.ISSUER_NAME, s.CLOSING_DATE), dedup_docs AS (SELECT MAX(EXTRACTION_ID) AS EXTRACTION_ID FROM doc_metadata GROUP BY ISSUER_NAME, COALESCE(CAST(CLOSING_DATE AS VARCHAR), SHARE_CLASS_NAME, 'undated')), purchaser_canonical AS (SELECT i.ISSUER_NAME, p.PURCHASER_NAME, p.ENTITY_TYPE, <CANONICAL_CASE> FROM dedup_docs dd JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON dd.EXTRACTION_ID = i.EXTRACTION_ID JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON dd.EXTRACTION_ID = p.EXTRACTION_ID WHERE p.PURCHASER_NAME NOT ILIKE '%<firm_name>%' AND p.PURCHASER_NAME NOT ILIKE '%<firm_name_spaced>%' AND p.ENTITY_TYPE NOT ILIKE '%notice%' AND p.ENTITY_TYPE NOT ILIKE '%law firm%') SELECT CANONICAL_NAME, ANY_VALUE(ENTITY_TYPE) AS ENTITY_TYPE, COUNT(DISTINCT ISSUER_NAME) AS COMPANY_COUNT, ARRAY_AGG(DISTINCT ISSUER_NAME) WITHIN GROUP (ORDER BY ISSUER_NAME) AS COMPANIES, ARRAY_AGG(DISTINCT PURCHASER_NAME) WITHIN GROUP (ORDER BY PURCHASER_NAME) AS RAW_NAMES FROM purchaser_canonical GROUP BY CANONICAL_NAME ORDER BY COMPANY_COUNT DESC LIMIT 50"
}})
```

> Append the same `firm_vehicle_names` exclusion clauses to the WHERE that
> Mode A Step A1 describes, so off-brand firm vehicles don't leak into Q1.

#### Q2 — Most frequent with >5% of a round

> **Why "% of round" and not "ownership":** this number reflects the investor's
> share of a single SPA round at purchase time. It is **not** current fully
> diluted ownership — that would require dilution math (subsequent rounds,
> option pool refreshes, secondaries) which SPA data alone cannot provide.
> Never use the word "ownership" in user-facing output for this skill.

Use Mode A Query A's `CANONICAL_NAME` CASE expression so multi-vehicle investors
are aggregated at the canonical level. % of round is recomputed as
`SUM(canonical shares) / SUM(round shares)` so a firm investing through
multiple vehicles in the same round is credited with the combined stake.

```
call_tool({"name": "dwh__execute__query", "arguments": {
  "sql": "WITH doc_metadata AS (SELECT d.EXTRACTION_ID, i.ISSUER_NAME, s.CLOSING_DATE, MIN(p.SHARE_CLASS_NAME) AS SHARE_CLASS_NAME FROM FUND_ADMIN.DOCUMENT_AI_DOCUMENT d JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON d.EXTRACTION_ID = i.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON d.EXTRACTION_ID = s.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON d.EXTRACTION_ID = p.EXTRACTION_ID WHERE d.FIRM_ID = '<firm_id>' AND i.ISSUER_NAME IS NOT NULL AND TRIM(i.ISSUER_NAME) <> '' GROUP BY d.EXTRACTION_ID, i.ISSUER_NAME, s.CLOSING_DATE), dedup_docs AS (SELECT MAX(EXTRACTION_ID) AS EXTRACTION_ID FROM doc_metadata GROUP BY ISSUER_NAME, COALESCE(CAST(CLOSING_DATE AS VARCHAR), SHARE_CLASS_NAME, 'undated')), spa_canonical AS (SELECT i.ISSUER_NAME, <CANONICAL_CASE> AS CANONICAL_NAME, p.ENTITY_TYPE, p.SHARES_PURCHASED, s.CLOSING_DATE, dd.EXTRACTION_ID FROM dedup_docs dd JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON dd.EXTRACTION_ID = i.EXTRACTION_ID JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON dd.EXTRACTION_ID = p.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON dd.EXTRACTION_ID = s.EXTRACTION_ID WHERE p.PURCHASER_NAME NOT ILIKE '%<firm_name>%' AND p.PURCHASER_NAME NOT ILIKE '%<firm_name_spaced>%' AND p.ENTITY_TYPE NOT ILIKE '%notice%' AND p.ENTITY_TYPE NOT ILIKE '%law firm%'), per_round AS (SELECT ISSUER_NAME, CANONICAL_NAME, ANY_VALUE(ENTITY_TYPE) AS ENTITY_TYPE, EXTRACTION_ID, CLOSING_DATE, SUM(SHARES_PURCHASED) AS CANONICAL_SHARES FROM spa_canonical GROUP BY ISSUER_NAME, CANONICAL_NAME, EXTRACTION_ID, CLOSING_DATE), pct_per_round AS (SELECT ISSUER_NAME, CANONICAL_NAME, ENTITY_TYPE, EXTRACTION_ID, CLOSING_DATE, CANONICAL_SHARES / NULLIF(SUM(CANONICAL_SHARES) OVER (PARTITION BY EXTRACTION_ID), 0) AS PCT_OF_ROUND FROM per_round), latest_round AS (SELECT ISSUER_NAME, CANONICAL_NAME, ENTITY_TYPE, PCT_OF_ROUND, ROW_NUMBER() OVER (PARTITION BY ISSUER_NAME, CANONICAL_NAME ORDER BY CLOSING_DATE DESC NULLS LAST, EXTRACTION_ID DESC) AS rn FROM pct_per_round), filtered AS (SELECT ISSUER_NAME, CANONICAL_NAME, ENTITY_TYPE, PCT_OF_ROUND FROM latest_round WHERE rn = 1 AND PCT_OF_ROUND > 0.05) SELECT CANONICAL_NAME, ANY_VALUE(ENTITY_TYPE) AS ENTITY_TYPE, COUNT(DISTINCT ISSUER_NAME) AS COMPANIES_ABOVE_5PCT, ROUND(AVG(PCT_OF_ROUND) * 100, 1) AS AVG_PCT_OF_ROUND, ARRAY_AGG(DISTINCT ISSUER_NAME) WITHIN GROUP (ORDER BY ISSUER_NAME) AS COMPANIES FROM filtered GROUP BY CANONICAL_NAME ORDER BY COMPANIES_ABOVE_5PCT DESC, AVG_PCT_OF_ROUND DESC LIMIT 50"
}})
```

> Substitute `<CANONICAL_CASE>` with the same assembled CASE block built in
> Mode A Step A1.0 (read from `canonical-investors.json`).

#### Q3 — Most frequent with <5% of a round

Same shape as Q2 with three changes: filter is `PCT_OF_ROUND < 0.05 AND
PCT_OF_ROUND > 0 AND PCT_OF_ROUND < 1.0` (the `< 1.0` clause excludes
single-purchaser SPAs where the investor was the only buyer); aggregate column
is renamed `COMPANIES_BELOW_5PCT`; rounding goes to 2 decimals to match the
small percentage values.

```
call_tool({"name": "dwh__execute__query", "arguments": {
  "sql": "WITH doc_metadata AS (SELECT d.EXTRACTION_ID, i.ISSUER_NAME, s.CLOSING_DATE, MIN(p.SHARE_CLASS_NAME) AS SHARE_CLASS_NAME FROM FUND_ADMIN.DOCUMENT_AI_DOCUMENT d JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON d.EXTRACTION_ID = i.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON d.EXTRACTION_ID = s.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON d.EXTRACTION_ID = p.EXTRACTION_ID WHERE d.FIRM_ID = '<firm_id>' AND i.ISSUER_NAME IS NOT NULL AND TRIM(i.ISSUER_NAME) <> '' GROUP BY d.EXTRACTION_ID, i.ISSUER_NAME, s.CLOSING_DATE), dedup_docs AS (SELECT MAX(EXTRACTION_ID) AS EXTRACTION_ID FROM doc_metadata GROUP BY ISSUER_NAME, COALESCE(CAST(CLOSING_DATE AS VARCHAR), SHARE_CLASS_NAME, 'undated')), spa_canonical AS (SELECT i.ISSUER_NAME, <CANONICAL_CASE> AS CANONICAL_NAME, p.ENTITY_TYPE, p.SHARES_PURCHASED, s.CLOSING_DATE, dd.EXTRACTION_ID FROM dedup_docs dd JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON dd.EXTRACTION_ID = i.EXTRACTION_ID JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON dd.EXTRACTION_ID = p.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON dd.EXTRACTION_ID = s.EXTRACTION_ID WHERE p.PURCHASER_NAME NOT ILIKE '%<firm_name>%' AND p.PURCHASER_NAME NOT ILIKE '%<firm_name_spaced>%' AND p.ENTITY_TYPE NOT ILIKE '%notice%' AND p.ENTITY_TYPE NOT ILIKE '%law firm%'), per_round AS (SELECT ISSUER_NAME, CANONICAL_NAME, ANY_VALUE(ENTITY_TYPE) AS ENTITY_TYPE, EXTRACTION_ID, CLOSING_DATE, SUM(SHARES_PURCHASED) AS CANONICAL_SHARES FROM spa_canonical GROUP BY ISSUER_NAME, CANONICAL_NAME, EXTRACTION_ID, CLOSING_DATE), pct_per_round AS (SELECT ISSUER_NAME, CANONICAL_NAME, ENTITY_TYPE, EXTRACTION_ID, CLOSING_DATE, CANONICAL_SHARES / NULLIF(SUM(CANONICAL_SHARES) OVER (PARTITION BY EXTRACTION_ID), 0) AS PCT_OF_ROUND FROM per_round), latest_round AS (SELECT ISSUER_NAME, CANONICAL_NAME, ENTITY_TYPE, PCT_OF_ROUND, ROW_NUMBER() OVER (PARTITION BY ISSUER_NAME, CANONICAL_NAME ORDER BY CLOSING_DATE DESC NULLS LAST, EXTRACTION_ID DESC) AS rn FROM pct_per_round), filtered AS (SELECT ISSUER_NAME, CANONICAL_NAME, ENTITY_TYPE, PCT_OF_ROUND FROM latest_round WHERE rn = 1 AND PCT_OF_ROUND < 0.05 AND PCT_OF_ROUND > 0 AND PCT_OF_ROUND < 1.0) SELECT CANONICAL_NAME, ANY_VALUE(ENTITY_TYPE) AS ENTITY_TYPE, COUNT(DISTINCT ISSUER_NAME) AS COMPANIES_BELOW_5PCT, ROUND(AVG(PCT_OF_ROUND) * 100, 2) AS AVG_PCT_OF_ROUND, ARRAY_AGG(DISTINCT ISSUER_NAME) WITHIN GROUP (ORDER BY ISSUER_NAME) AS COMPANIES FROM filtered GROUP BY CANONICAL_NAME ORDER BY COMPANIES_BELOW_5PCT DESC, AVG_PCT_OF_ROUND DESC LIMIT 50"
}})
```

#### Q4 — Company-specific

> **Cache-first:** if Step B0 found a fresh `carta-co-investors-data.json`,
> read `companyRounds[<matched_company>]` from it instead of running this
> query. Match the company name case-insensitively against the JSON keys.
> Only fall back to the DWH query below when the cache is absent or stale.

```
call_tool({"name": "dwh__execute__query", "arguments": {
  "sql": "WITH doc_metadata AS (SELECT d.EXTRACTION_ID, i.ISSUER_NAME, s.CLOSING_DATE, MIN(p.SHARE_CLASS_NAME) AS SHARE_CLASS_NAME FROM FUND_ADMIN.DOCUMENT_AI_DOCUMENT d JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON d.EXTRACTION_ID = i.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON d.EXTRACTION_ID = s.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON d.EXTRACTION_ID = p.EXTRACTION_ID WHERE d.FIRM_ID = '<firm_id>' GROUP BY d.EXTRACTION_ID, i.ISSUER_NAME, s.CLOSING_DATE), dedup_docs AS (SELECT MAX(EXTRACTION_ID) AS EXTRACTION_ID FROM doc_metadata GROUP BY ISSUER_NAME, COALESCE(CAST(CLOSING_DATE AS VARCHAR), SHARE_CLASS_NAME, 'undated')) SELECT dd.EXTRACTION_ID, i.ISSUER_NAME, p.SHARE_CLASS_NAME, s.CLOSING_DATE, p.PURCHASER_NAME, p.ENTITY_TYPE, p.SHARES_PURCHASED, p.TOTAL_AMOUNT_PAID, p.SHARES_PURCHASED / NULLIF(SUM(p.SHARES_PURCHASED) OVER (PARTITION BY dd.EXTRACTION_ID), 0) AS PCT_OF_ROUND FROM dedup_docs dd JOIN FUND_ADMIN.DOCUMENT_AI_SPA_ISSUER i ON dd.EXTRACTION_ID = i.EXTRACTION_ID JOIN FUND_ADMIN.DOCUMENT_AI_SPA_PURCHASER p ON dd.EXTRACTION_ID = p.EXTRACTION_ID LEFT JOIN FUND_ADMIN.DOCUMENT_AI_SPA s ON dd.EXTRACTION_ID = s.EXTRACTION_ID WHERE i.ISSUER_NAME ILIKE '%<company_name>%' AND p.ENTITY_TYPE NOT ILIKE '%notice%' AND p.ENTITY_TYPE NOT ILIKE '%law firm%' ORDER BY s.CLOSING_DATE DESC, p.SHARES_PURCHASED DESC LIMIT 500"
}})
```

> Column note: use `p.SHARE_CLASS_NAME` from the purchaser table as the round label.
> The SPA table does not have a `SERIES_NAME` column. Q4 leaves purchaser names
> as raw values so the breakdown matches the SPA document line-for-line.

Tell the user: `SPA data loaded. Preparing results…`

### Step B3: Present results

**Coverage note — always include:**
"Results cover **X** of your **Y** priced-equity portfolio companies that have at least one SPA on file."

> **What X means:** the count of portfolio companies (from your SOI) with at
> least one matching SPA in Carta. Companies with multiple SPAs (e.g. one per
> round) count once. SPAs whose issuer name doesn't match any current portfolio
> company are excluded.

#### Q1 output

> **<Firm name> — Most frequent co-investors**
> (X of your Y priced-equity portfolio companies have at least one SPA on file)
> *Co-investment counts are per company, not per round. Multi-vehicle investors
> are aggregated to a single canonical entry per the groupings in
> `canonical-investors.json`.*

| Co-investor | Companies | Entity type | Portfolio companies |
|---|---|---|---|
| [Name] | [N] | [type] | Co. 1, Co. 2, Co. 3 |

*Name groupings applied: list rows where `RAW_NAMES` contains a `||` separator
— each becomes "Raw Name A" + "Raw Name B" → **Canonical Name**. Omit the
section if no rows had multi-vehicle groupings.*

[View SPA source documents in Carta](<base_url>/investors/firm/<firm_carta_id>/portfolio/documents/)

---

#### Q2 output

> **<Firm name> — Most frequent co-investors with >5% of a round**
> *% of round is calculated from shares at SPA closing — purchase-time only.
> This is not the investor's current cap table position; that would require
> dilution math (subsequent rounds, option pool refreshes, secondaries) not
> derivable from SPA data alone.*

| Co-investor | Companies >5% | Avg % of round | Entity type | Portfolio companies |
|---|---|---|---|---|

[View SPA source documents in Carta](<base_url>/investors/firm/<firm_carta_id>/portfolio/documents/)

---

#### Q3 output

Same as Q2 but heading reads "with <5% of a round".

---

#### Q4 output

For each round (grouped by `EXTRACTION_ID`), render a separate section:

> **<Firm name> — <Company name>, <Share class>** (Closing: <date or —>)
> <N> investors | Total raised: $X,XXX

| Investor | Entity type | Shares | Amount paid | % of round |
|---|---|---|---|---|
| [Your fund] **(You)** | [type] | [N] | $[X,XXX] | [X.X%] |

[View SPA source documents in Carta](<base_url>/investors/firm/<firm_carta_id>/portfolio/documents/)

If no match: "No SPA found for '[name]'. Did you mean one of these? [list closest matches from available issuers]"

### Step B4: Recommend next step

End with one concrete suggested next step:

- After Q1 → "Want to drill into a specific company to see the full investor breakdown?"
- After Q4 → "Want to see which of these investors took >5% of a round in your portfolio companies?"
- After Q2 → "Want to compare with investors who come in at <5% — the smaller check followers?"
- After Q3 → offer a summary insight and suggest generating the interactive artifact report

Do not repeat the full menu after every result. If the user asks "what else can you show me?", surface:
1. Drill into a specific co-investor — all companies you share with them
2. Switch question type — overall / >5% / <5% / by company
3. List portfolio companies with missing SPA data
4. Generate the interactive visual report

---

## Error handling

| Scenario | Response |
|---|---|
| `list_contexts` returns nothing | "I couldn't find any Carta data for your account. Try reconnecting to the Carta MCP server. If you believe you're already connected, contact your Carta representative." |
| `firm_id` fails pre-flight UUID check | "Could not determine your firm ID. Try reconnecting to the Carta MCP server. If you believe you're already connected, contact your Carta representative." |
| 401/403 from any DWH query | "Your Carta session has expired. Reconnect to the Carta MCP server and try again." |
| Query fails with table-not-found | Call `dwh:list:tables` to confirm available table names, then retry with correct names. |
| 0 SPA rows returned | "No SPA documents were found for your account. Contact your Carta representative if you believe this is an error." |
| Company name not found (Q4) | "No SPA found for '[name]'. Did you mean: [suggestions from available issuers]?" |
| Partial SPA coverage | Note in results: "X of your Y portfolio companies have at least one SPA on file." Offer to list missing companies. |
| `open` command fails | Tell the user the file path to open manually: `$WORKSPACE/carta-co-investors.html` (the resolved value, not the literal env var). |
| MCP query error | "Could not reach Carta data. Try again in a moment." |
