carta-co-investors

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.

# --- 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):

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

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.

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

{
  "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:

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: co-investors across portfolio companies (SPA coverage: <spa_companies> of <total_companies>). Click any company to drill into the full investor breakdown."

Data as of . [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.

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):

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

— 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

— 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:

— , (Closing: <date or —>) 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."