Carta Spa Audit

SPA audit

Audit SPA (Stock Purchase Agreement) coverage across your portfolio. Every portfolio company lands in exactly one of four buckets — missing, unexecuted, executed, or not needed — ranked by cost basis within each bucket.

UX patterns [PATTERN base v0.1.0]

Typography and text formatting [PATTERN text v0.0.8]

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). Exception: first invested dates are shown as Mmm yyyy (month-level — day precision is not meaningful for portfolio-entry dates).
• Currency (standard): $123,456. Negative: ($445,443).
• Null values: Use — (em-dash), never N/A, blank, or prose like "not recorded".

Tables [PATTERN tables v0.0.12]

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: Investment records loaded across 47 companies. Looking up SPA documents…

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, 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, SPAs, portfolios, and coverage — never the machinery that produces them.

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

• 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, all equity companies land in bucket 1 (missing SPA).
• Mode A needs Cowork. Live Artifacts only render there. Claude Desktop and the Claude Code CLI get Mode B, which is a full text answer, not a degraded one.

Accessibility

The Mode B text path is fully accessible in any text environment — all output is plain Markdown tables.

The Mode A interactive HTML artifact has not yet been formally audited for WCAG 2.1 AA compliance. Known considerations:

• Color-only encoding is avoided: every bucket badge and tile pairs its accent color with a text label.
• The drawer close button is a real <button> with an SVG icon and aria-label.
• The Escape key closes the drawer.
• Tables use proper <thead> / <tbody> semantics; sort headers expose aria-sort state.
• Search input has an aria-label.

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

When to use

• "Run SPA audit"
• "Show me SPA coverage across my portfolio"
• "Which companies are missing SPAs?"
• "Which portfolio companies have executed SPAs?"
• "What's our SPA status?"
• "Do we have SPAs on file for all our equity investments?"

SPA data source

Every SPA query below reads one surface: FUND_ADMIN.DOCUMENT_AI_RECORD, the generic, document-type-agnostic view. Extracted fields live in the ATTRIBUTES JSON column and need explicit casts (ATTRIBUTES:name::STRING).

Two rules when writing or editing any of these queries:

1. Filter on both DOCUMENT_TYPE = 'stock_purchase_agreement' and the relevant RECORD_TYPE. The RECORD_TYPE labels (company, investor, security, stock_purchase) are generic and reused by other document types — an LPA also has company and investor records. RECORD_TYPE alone silently pulls unrelated documents into the audit.
2. Relate record types within a document by DOCUMENT_ID or EXTRACTION_ID — the view holds exactly one extraction per document, so either key works and no deduplication is needed.

A SPA extracted before this pipeline became the source of record will not appear. Those documents live only in the older per-type views (DOCUMENT_AI_SPA_ISSUER and friends), which this skill no longer reads, and the company will read as "Missing SPA" until it is re-extracted. Do not reintroduce a read of those views to patch an individual gap — the source of record is one surface, and a fix belongs upstream in the extraction backfill.

Step 0: Announce

Open every invocation with:

"I'll audit SPA coverage across your portfolio — pulling every investment record and SPA document from Carta, then categorizing each company by execution status. Larger portfolios take a moment."

Then proceed immediately to Step 1.

Step 1: Establish firm context

1. Call list_contexts. If no context is 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."
2. Note the firm id — this is your <firm_id> for every query below.
3. Note the firm display name — this is your <firm_name>.
4. Call list_accounts searching for <firm_name>. Find the entry with type: "investment firm". Extract the numeric portion of its id (e.g. "organization_pk:2645" → 2645) — this is <org_pk> for the document library link in Step 3.
5. Resolve <base_url> from the current Carta MCP server context — never hardcode an environment URL. For the production MCP server (mcp.app.carta.com), <base_url> is the Carta production web app URL. For any other server, default to the same production URL.

Pre-flight check: 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 investment records and SPA documents…

Step 2: Route

The default output of this skill is the interactive artifact (Mode A). Proceed directly to Mode A Step A1.

Only route to text-only (Mode B) when the user explicitly signals it:

• Says "text only", "no file", "quick summary", "just tell me", or "list missing only" → Mode B
• Asks to drill into a specific named company (any phrasing) → run Mode B Step B0 cache check + drill-down only

Everything else — including any general "audit my SPAs" or "show coverage" request — goes to Mode A.

Route to Mode B when Step A1's gate finds no Artifact tool — that means this session is not Cowork.

Mode A — Live Artifact

The artifact shows the firm's four-bucket SPA audit — missing, unexecuted, executed, no SPA needed — ranked by cost basis within each bucket, with every company that has an SPA on file clickable to open a drawer holding the full SPA and purchaser breakdown for that company.

The page fetches its own data. You issue no warehouse queries in Mode A. The rendered HTML calls Carta at runtime through claude.use("mcp"), pinning firm context and running the audit, drill-down, enrichment, status, and coverage queries itself. This is why Mode A costs the same for a 400-company firm as for a 12-company one — no rows ever pass through your context.

Consequences worth holding onto:

• There is no NDJSON blob to resolve, no process.py, and no data file to assemble.
• Do not hand-write or edit the artifact HTML. Every render goes through render-artifact.py.
• Do not pre-fetch SPA data "to check it worked". The artifact reports its own errors to the viewer.

Step A1: Checks before building

Run both checks, and stay quiet about them when they pass:

1. ${CLAUDE_PLUGIN_ROOT}/references/gate-has-artifact-tool.md — can this session publish at all?
2. ${CLAUDE_PLUGIN_ROOT}/references/gate-carta-connector-name.md — the connector name the page will call.

Both sit in the plugin's references/ directory — ${CLAUDE_PLUGIN_ROOT}/references/, alongside the other plugin-wide references. They are not under this skill's own references/. Read them by that exact path; don't search for them.

If the Artifact tool is missing, this session is not Cowork: go to Mode B and tell the user plainly "I'll give you the audit as text." Say nothing about artifacts, Cowork, or sandboxes.

Store the name the connector gate resolves as <CARTA_MCP_SERVER>. Step A2 passes it to the render script and Step A3 puts it in the capabilities.mcp grant. It is one string; there is nothing to derive and nothing to keep in sync.

Step A2: Render

Locate the script:

find /sessions "$HOME/mnt" -type f -path '*/carta-spa-audit/scripts/render-artifact.py' 2>/dev/null | head -1

If it prints nothing, use ${CLAUDE_PLUGIN_ROOT}/skills/carta-spa-audit/scripts/render-artifact.py.

Keep the find scoped to those two roots — the remote plugin mounts, the only place bash can reach this script, since it cannot reach the path ${CLAUDE_PLUGIN_ROOT} expands to there. Locally neither exists, the find is empty, and the plugin-root path is the correct one. Do not broaden to $HOME or /: it takes tens of seconds and can resolve a stale cached copy.

Render, substituting the path found above literally. allowed-tools matches the command text, so a shell variable in place of the path fails the allowlist and the call has to be approved by hand each time:

uv run "<SCRIPT_PATH>" \
    "<CWD>/<firm-slug>-spa-audit.html" \
    "<firm-slug>-spa-audit" \
    "<CARTA_MCP_SERVER>" \
    "<firm_id>" \
    "<firm_name>" \
    "<org_pk>" \
    "<base_url>"

Positional arguments:

1. Output path — must be absolute, under the session's current working directory (<CWD>), and not under /tmp. Use pwd to resolve <CWD> if needed.
2. Artifact ID — the kebab-case slug naming this artifact. Must equal <firm-slug>-spa-audit.
3. Carta connector display name — <CARTA_MCP_SERVER> from Step A1.
4. Firm UUID — from Step 1. The artifact calls set_context with this on every load, so the queries succeed even if the user switched contexts elsewhere.
5. Firm name — the human-readable name, shown in the page header.
6. Firm Carta ID — <org_pk> from Step 1, the numeric organization pk, for the documents deep link.
7. Base URL — <base_url> from Step 1.

On success the script prints one stdout line: the absolute output path. It exits non-zero on any validation failure (bad UUID, non-numeric Carta ID, base URL with a path or a non-https scheme, unusable connector name, output outside CWD, missing template or placeholders). If it fails, surface the error and stop — do not fall back to hand-writing HTML.

Slugification rules (apply to the firm name, not any UUID):

1. Lowercase
2. Replace whitespace with hyphens
3. Strip non-alphanumeric characters except hyphens
4. Collapse consecutive hyphens
5. Trim leading and trailing hyphens

Example: "Acme Capital Partners, L.P." → slug "acme-capital-partners-lp" → output acme-capital-partners-lp-spa-audit.html, artifact id acme-capital-partners-lp-spa-audit.

Re-running the skill for the same firm produces the same artifact id and filename, so Step A3 updates the artifact in place.

Step A3: Publish

The render script only writes the HTML file. Nothing picks up file changes on its own — you MUST publish after every render, or the reader keeps seeing the prior version.

First look for an existing one:

Artifact({action: "list", scope: "mine"})

If a — SPA coverage audit artifact is already published, keep its url. Then publish — one call either way, url being the only difference:

Artifact({
  file_path: "<absolute path printed by the render script>",
  url: "<url from the list — omit entirely on a first publish>",
  title: "<Firm Name> — SPA coverage audit",
  description: "<Firm Name> — SPA coverage across your portfolio, missing/unexecuted/executed/not-needed",
  favicon: "📋",
  capabilities: {
    mcp: {
      servers: [
        { server: "<CARTA_MCP_SERVER>", tools: ["call_tool", "set_context", "welcome"] }
      ]
    }
  }
})

All three tools must be in the grant, or the page loads and the matching call rejects with not_in_manifest. The artifact only calls welcome itself when the MCP reports a "session not initialized" error, then retries the original call once.

Keep title and favicon stable across redeploys, and restate the whole capabilities object every time: a non-empty object replaces the stored grant, so a tool left out is revoked.

Step A4: Confirm

Your SPA coverage audit for is loading in the Cowork sidebar.

Then a 3–5 bullet summary of what it shows — the four-bucket audit (missing, unexecuted, executed, no SPA needed) ranked by cost basis, a coverage stat contrasting count vs. cost-basis coverage, click any company with an SPA on file to see the document and purchaser breakdown in a drawer, fund and geography filters, live/exited tabs. Keep it brief; the customer can see the artifact. Skip the bullets on re-invocation if you've already shown them.

Cross-skill follow-up. Always append a one-line suggestion to run carta-co-investors: "Want to see who co-invests alongside you in these portfolio companies? Run the carta-co-investors skill — it builds an interactive co-investor report from the same SPA data this audit just collected."

Numbers live in the artifact, not in your reply. Do not restate coverage percentages, bucket counts, or company names in chat — you have not seen them. The page queries Carta after your turn ends, so any figure you quote is invented.

Mode B — Audit (text)

Render the four-bucket audit as Markdown tables. Single fetch, single render.

Step B0: Resolve workspace and check cache

Before fetching anything, resolve a stable, cross-platform working directory. The audit cache and (in v0.4.0) the HTML artifact both 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.

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.
  WORKSPACE="${HOME}/mnt/outputs/carta-spa-audit"
elif command -v carta >/dev/null 2>&1; then
  # Regular Claude Code CLI on a developer laptop.
  WORKSPACE=$(carta workspace cache carta-spa-audit | jq -r .)
else
  # Last-resort fallback (e.g. CI / hosted runtimes without Carta CLI).
  WORKSPACE="${TMPDIR:-/tmp}/carta-spa-audit"
fi
mkdir -p "$WORKSPACE"

Do not hardcode /tmp — it breaks on Windows and is invisible to the Cowork host on macOS 26.5+.

Cache check. If $WORKSPACE/carta-spa-audit-data.json exists AND is less than 60 minutes old AND the cached firmId matches the current <firm_id>, read it and skip Step B1 entirely:

test -f "$WORKSPACE/carta-spa-audit-data.json" && \
  find "$WORKSPACE/carta-spa-audit-data.json" -mmin -60 -print

If the file is fresh:

1. Read it.
2. Confirm data.meta.firmId == <firm_id> (cache is per-firm; a stale cache from another firm must be ignored).
3. Tell the user: Using cached SPA data from $(date -r "$WORKSPACE/carta-spa-audit-data.json" "+%H:%M"). Preparing results…
4. Skip Step B1 and proceed directly to Step B2.

Fall through to Step B1 (live fetch) when:

• The cache file does not exist
• The cache file is older than 60 minutes
• The cached firmId does not match
• The user explicitly asks to refresh (e.g. "rerun", "fresh data")

Drill-down queries always run live — per-company SPA documents are too noisy to cache and the user expects current data when they ask for it.

Step B1: Fetch audit data

Run the main audit query and the two coverage queries in parallel.

Main audit query

Reads the unified SPA source (see below), groups by issuer name to deduplicate multi-upload cases, then fuzzy-matches investment names to SPA issuer names at Jaro-Winkler ≥ 90 to catch variants like "AcmeCorp, Inc." vs. "AcmeCorp Inc." or "AcmeCo International Inc. (fka. OldName, Inc.)" vs. "AcmeCo International Inc."

Important — regex escaping: Snowflake processes backslashes in string literals (\s → s), so patterns that need a literal backslash for the regex engine must use \\ in the SQL string. The patterns below use [(], [)], and [ ] (space) instead of \(, \), and \s to avoid this entirely.

fuzzy_matched needs a total ordering — do not drop a trailing ORDER BY key. Once norm_investments emits an alias candidate alongside the primary name, one investment can score 100 against two different SPAs, and has_executed_spa DESC alone does not separate them. With a tie, ROW_NUMBER() picks whichever row the plan happens to emit first, so the SPA named against a company changes between runs of the same audit. The rank ends ..., s.has_executed_spa DESC, i.is_alias, s.spa_name: the executed flag keeps its precedence so bucket assignment is untouched, is_alias prefers a match on the company's own name over its former name, and spa_name makes the order total.

NULLS LAST on that same rank is load-bearing — do not drop it. fuzzy_matched ranks candidates through a LEFT JOIN, and Snowflake sorts NULLs first on a DESC ordering. Without NULLS LAST, a company whose primary name finds no SPA but whose alias scores 100 has its NULL row win rn = 1, carries a NULL spa_name into labeled, and gets bucketed "1. Missing SPA" — silently discarding exactly the matches the alias candidates exist to recover. At one firm that was the difference between 66 and 133 companies in Executed SPA. Like the total ordering above, it only became load-bearing once norm_investments emitted more than one row per investment. The co-investors skill carries the same clause; keep the two in step.

CRITICAL — fetch ONCE; never re-issue this query with a different offset. A single fetch with limit: 500 covers any real portfolio (even Sequoia-scale firms top out around 300 portfolio companies). Do not re-run this query for "later pages." Reason: when the model re-types this ~1,500-char SQL for a second call it reliably corrupts a token — the embedded firm UUID drifts (e.g. …8af6… → …8ad6…), or a JOIN key changes (d.DOCUMENT_ID → s.DOCUMENT_ID), 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 500 (it won't for any real firm — flag it as a data anomaly rather than paginating): raise limit in the same single call. Never add offset pages.

Replace <firm_id> with the firm id from Step 1.

call_tool({"name": "dwh__execute__query", "arguments": {
  "sql": "WITH spa_issuers AS (SELECT ATTRIBUTES:name::STRING AS ISSUER_NAME, ATTRIBUTES:executed_by_issuer::BOOLEAN AS EXECUTED_BY_ISSUER FROM FUND_ADMIN.DOCUMENT_AI_RECORD WHERE FIRM_ID = '<firm_id>' AND DOCUMENT_TYPE = 'stock_purchase_agreement' AND RECORD_TYPE = 'company' AND ATTRIBUTES:name::STRING IS NOT NULL), norm_spa AS (SELECT ISSUER_NAME AS spa_name, TRIM(REGEXP_REPLACE(TRIM(REGEXP_REPLACE(TRIM(REGEXP_REPLACE(TRIM(REGEXP_REPLACE(UPPER(ISSUER_NAME), ' *[(][^)]*[)].*$', '')), ' +(D/?B/?A|F/?K/?A|AKA) +.*$', '')), '([ ,]+(INCORPORATED|INC|LLC|LTD|LIMITED|CORPORATION|CORP|L[.]P[.]|LLP|LP|PBC|PLC|CO[.]?|HOLDINGS|TECHNOLOGIES|TECHNOLOGY)[.]?)+ *$', '')), '[,.]', '')) AS name_norm, MAX(CASE WHEN EXECUTED_BY_ISSUER = TRUE THEN 1 ELSE 0 END) AS has_executed_spa FROM spa_issuers GROUP BY ISSUER_NAME), norm_investments_base AS (SELECT ISSUER_NAME, MAX(CASE WHEN ASSET_CLASS_TYPE = 'PREFERRED_EQUITY' THEN 1 ELSE 0 END) AS has_preferred, MAX(CASE WHEN ASSET_CLASS_TYPE = 'COMMON_EQUITY' THEN 1 ELSE 0 END) AS has_common, MIN(INVESTMENT_DATE) AS first_invested, SUM(CASE WHEN IS_FOREIGN_CURRENCY_INVESTMENT THEN BASE_CURRENCY_TOTAL_COST ELSE TOTAL_COST END) AS total_cost_basis, COUNT(DISTINCT CASE WHEN IS_FOREIGN_CURRENCY_INVESTMENT THEN BASE_CURRENCY_CODE ELSE CURRENCY_CODE END) AS currency_count, MAX(CASE WHEN IS_FOREIGN_CURRENCY_INVESTMENT THEN BASE_CURRENCY_CODE ELSE CURRENCY_CODE END) AS currency_code, TRIM(REGEXP_REPLACE(TRIM(REGEXP_REPLACE(TRIM(REGEXP_REPLACE(TRIM(REGEXP_REPLACE(UPPER(ISSUER_NAME), ' *[(][^)]*[)].*$', '')), ' +(D/?B/?A|F/?K/?A|AKA) +.*$', '')), '([ ,]+(INCORPORATED|INC|LLC|LTD|LIMITED|CORPORATION|CORP|L[.]P[.]|LLP|LP|PBC|PLC|CO[.]?|HOLDINGS|TECHNOLOGIES|TECHNOLOGY)[.]?)+ *$', '')), '[,.]', '')) AS name_norm, COALESCE(REGEXP_SUBSTR(ISSUER_NAME, '[(] *(D[.]?/?B[.]?/?A[.]?|F[.]?/?K[.]?/?A[.]?|N[.]?/?K[.]?/?A[.]?|A[.]?K[.]?A[.]?|FORMERLY( +KNOWN +AS)?)[ :]+([^)]+)[)]', 1, 1, 'ie', 3), REGEXP_SUBSTR(ISSUER_NAME, '[(]([^)]+)[)]', 1, 1, 'e', 1)) AS alias_raw FROM FUND_ADMIN.AGGREGATE_INVESTMENTS WHERE FIRM_ID = '<firm_id>' GROUP BY ISSUER_NAME), norm_investments AS (SELECT ISSUER_NAME, has_preferred, has_common, first_invested, total_cost_basis, currency_count, currency_code, name_norm, 0 AS is_alias FROM norm_investments_base UNION ALL SELECT ISSUER_NAME, has_preferred, has_common, first_invested, total_cost_basis, currency_count, currency_code, TRIM(REGEXP_REPLACE(TRIM(REGEXP_REPLACE(UPPER(alias_raw), '([ ,]+(INCORPORATED|INC|LLC|LTD|LIMITED|CORPORATION|CORP|L[.]P[.]|LLP|LP|PBC|PLC|CO[.]?|HOLDINGS|TECHNOLOGIES|TECHNOLOGY)[.]?)+ *$', '')), '[,.]', '')) AS name_norm, 1 AS is_alias FROM norm_investments_base WHERE alias_raw IS NOT NULL AND TRIM(alias_raw) <> ''), fuzzy_matched AS (SELECT i.ISSUER_NAME, i.has_preferred, i.has_common, i.first_invested, i.total_cost_basis, i.currency_count, i.currency_code, s.spa_name, s.has_executed_spa, ROW_NUMBER() OVER (PARTITION BY i.ISSUER_NAME ORDER BY JAROWINKLER_SIMILARITY(i.name_norm, s.name_norm) DESC NULLS LAST, s.has_executed_spa DESC, i.is_alias, s.spa_name) AS rn FROM norm_investments i LEFT JOIN norm_spa s ON JAROWINKLER_SIMILARITY(i.name_norm, s.name_norm) >= 90), best AS (SELECT * FROM fuzzy_matched WHERE rn = 1), labeled AS (SELECT CASE WHEN has_preferred = 0 AND has_common = 0 THEN 4 WHEN spa_name IS NULL THEN 1 WHEN has_executed_spa = 0 THEN 2 ELSE 3 END AS sort_key, CASE WHEN has_preferred = 0 AND has_common = 0 THEN '4. No SPA needed' WHEN spa_name IS NULL THEN '1. Missing SPA' WHEN has_executed_spa = 0 THEN '2. SPA not executed' ELSE '3. Executed SPA' END AS spa_bucket, ISSUER_NAME AS company, spa_name, first_invested, total_cost_basis, currency_count, currency_code FROM best) SELECT spa_bucket, company, spa_name, first_invested, total_cost_basis, currency_count, currency_code FROM labeled ORDER BY sort_key, total_cost_basis DESC NULLS LAST, company",
  "limit": 500
}})

Coverage queries (run in parallel with the main query)

call_tool({"name": "dwh__execute__query", "arguments": {
  "sql": "SELECT COUNT(DISTINCT ATTRIBUTES:name::STRING) AS spa_companies FROM FUND_ADMIN.DOCUMENT_AI_RECORD WHERE FIRM_ID = '<firm_id>' AND DOCUMENT_TYPE = 'stock_purchase_agreement' AND RECORD_TYPE = 'company' AND ATTRIBUTES:name::STRING IS NOT NULL"
}})

call_tool({"name": "dwh__execute__query", "arguments": {
  "sql": "SELECT COUNT(DISTINCT ISSUER_NAME) AS total_companies FROM FUND_ADMIN.AGGREGATE_INVESTMENTS WHERE FIRM_ID = '<firm_id>'"
}})

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

Bucket definitions:

• 1. Missing SPA — holds equity (preferred or common) but no SPA document found in Carta
• 2. SPA not executed — SPA uploaded but EXECUTED_BY_ISSUER = FALSE on all documents for that issuer
• 3. Executed SPA — at least one SPA with EXECUTED_BY_ISSUER = TRUE
• 4. No SPA needed — no preferred or common equity (SAFE-only, convertible debt, fund investment, warrants, tokens, etc.)

Tell the user: SPA data loaded. Building your coverage report…

Step B1.5: Write cache

After both queries return, assemble a cache payload and write it to $WORKSPACE/carta-spa-audit-data.json so a re-invocation within 60 minutes can skip the live fetch (Step B0).

Cache schema:

{
  "meta": {
    "firmId": "<firm_id>",
    "firmName": "<firm_name>",
    "firmCartaId": <org_pk>,
    "generatedAt": "<UTC ISO 8601 timestamp>"
  },
  "coverage": {
    "totalCompanies": <Y>,
    "spaCompanies": <X>
  },
  "buckets": {
    "missing":     [{"company": "...", "firstInvested": "YYYY-MM-DD", "costBasis": <number>}, ...],
    "unexecuted":  [...],
    "executed":    [...],
    "notNeeded":   [...]
  }
}

Use Write to write the JSON. Group the main audit query's rows by spa_bucket into the four bucket arrays. Drill-down data is NOT cached (always fetched live in Step B2 follow-ups).

Step B2: Present results

BLUF lead: One sentence with the coverage fraction and bucket breakdown before the tables.

"X of your Y priced-equity portfolio companies have an SPA on file. Of those equity investments: A are missing an SPA entirely, B have executed SPAs on file, and C have an SPA uploaded but not yet executed. Z additional investment(s) don't need an SPA (SAFEs, convertible notes, fund/LP positions, tokens, warrants)."

Y is priced-equity only. Count investments in the Executed / Unexecuted / Missing buckets — exclude "No SPA needed" from the denominator. That bucket surfaces in its own sub-table as an FYI. This keeps the co-investor skill and the SPA-audit skill reporting the same coverage % for the same firm.

Then present four separate sub-tables — one per bucket, in priority order, each with its own heading. Do not combine into one flat table.

Formatting rules (all tables):

• Currency: $X,XXX,XXX with commas, no cents
• Dates: Mmm yyyy (e.g. "Dec 2017") — month-level is intentional for first invested
• # column for row references — does not count toward the 6-column limit
• Never show raw UUIDs
• Sort each sub-table by total_cost_basis DESC when rendering — filter rows by their spa_bucket value and sort within that bucket, regardless of the order rows arrived from the query
• Within actionable buckets, insert a — Exited / written-off positions — divider row between the last $>0 entry and the first $0 cost-basis row
• Fuzzy-match footnote: if any company was matched to its SPA via Jaro-Winkler similarity (not an exact name match), append * to that company name and add a footnote below the table: * matched via name similarity

❌ Missing SPA — A companies

Show all rows individually — each represents a gap to remediate.

⚠ SPA not executed — C companies

SPA is on file but needs the issuer's signature.

✅ Executed SPA — B companies

— No SPA needed — W companies

After all four sections, add the document library link and flag the two highest-priority gaps:

[View all SPA documents in Carta](/investors/firm//portfolio/documents/)

• Largest cost-basis position in bucket 1 (missing SPA) — biggest blind spot, no document on file • Largest cost-basis position in bucket 2 (unexecuted SPA) — most urgent to chase for signature

Step B3: Offer next steps

AskUserQuestion(
    questions=[{
        "question": "What would you like to do next?",
        "header": "Next step",
        "multiSelect": False,
        "options": [
            {"label": "Drill into a company", "description": "See all SPA documents on file for a specific portfolio company."},
            {"label": "Show only gaps", "description": "List companies with missing or unexecuted SPAs only, sorted by cost basis."},
            {"label": "Run co-investor analysis", "description": "Hand off to the carta-co-investors skill — it uses the same SPA data to surface who else invested alongside you in your portfolio companies."},
            {"label": "Done — no further action", "description": "Return to chat."},
        ],
    }]
)

If the user picks "Drill into a company," continue with the Drill-down section below. If they pick "Show only gaps," re-render Step B2 with only buckets 1 and 2. If they pick "Run co-investor analysis," hand off to carta-co-investors (do not re-fetch — the SPA data is already cached at $WORKSPACE).

Suggested next step after presenting the audit. Always append, in plain text after Step B2 and before the AskUserQuestion: "To close any coverage gap, upload missing SPAs to your Carta portfolio documents page — uploaded SPAs unlock co-investor analysis and more downstream skills. View or upload SPA documents in Carta"

Drill-down: SPA documents by company

Triggered when the user selects "Drill into a company" or asks to see documents for a named company.

Step A

If the user hasn't named a company, ask: "Which company would you like to review?"

Step B

Run this query, replacing <company_name> with their input:

call_tool({"name": "dwh__execute__query", "arguments": {
  "sql": "WITH gen_rec AS (SELECT DOCUMENT_ID, RECORD_TYPE, ATTRIBUTES, CREATED_AT FROM FUND_ADMIN.DOCUMENT_AI_RECORD WHERE FIRM_ID = '<firm_id>' AND DOCUMENT_TYPE = 'stock_purchase_agreement'), spa_docs AS (SELECT c.DOCUMENT_ID, c.ATTRIBUTES:name::STRING AS ISSUER_NAME, c.ATTRIBUTES:executed_by_issuer::BOOLEAN AS EXECUTED_BY_ISSUER, TRY_TO_DATE(e.ATTRIBUTES:closing_dates[0]::STRING) AS CLOSING_DATE, IFF(REGEXP_LIKE(e.ATTRIBUTES:currency_code::STRING, '^[A-Z]{3}$'), e.ATTRIBUTES:currency_code::STRING, NULL) AS CURRENCY_CODE, c.CREATED_AT::DATE AS UPLOAD_DATE FROM gen_rec c LEFT JOIN gen_rec e ON e.DOCUMENT_ID = c.DOCUMENT_ID AND e.RECORD_TYPE = 'stock_purchase' WHERE c.RECORD_TYPE = 'company' AND c.ATTRIBUTES:name::STRING IS NOT NULL), gen_purch AS (SELECT DOCUMENT_ID, ATTRIBUTES:name::STRING AS PURCHASER_NAME, ATTRIBUTES:entity_type::STRING AS ENTITY_TYPE, ATTRIBUTES:share_class_name::STRING AS SHARE_CLASS_NAME, ATTRIBUTES:shares_purchased_by_cash::NUMBER AS SHARES_PURCHASED, ATTRIBUTES:price_per_share::NUMBER AS PRICE_PER_SHARE, ATTRIBUTES:total_amount_paid::NUMBER AS TOTAL_AMOUNT_PAID FROM gen_rec WHERE RECORD_TYPE = 'investor') SELECT DENSE_RANK() OVER (ORDER BY sd.DOCUMENT_ID) AS spa_num, sd.UPLOAD_DATE AS upload_date, sd.ISSUER_NAME, gp.PURCHASER_NAME, gp.SHARE_CLASS_NAME, gp.SHARES_PURCHASED, gp.PRICE_PER_SHARE, gp.TOTAL_AMOUNT_PAID, sd.CURRENCY_CODE, sd.CLOSING_DATE AS transaction_date, CASE WHEN sd.EXECUTED_BY_ISSUER = TRUE THEN 'Yes' ELSE 'No' END AS executed FROM spa_docs sd LEFT JOIN gen_purch gp ON gp.DOCUMENT_ID = sd.DOCUMENT_ID AND (gp.ENTITY_TYPE IS NULL OR (gp.ENTITY_TYPE NOT ILIKE '%notice%' AND gp.ENTITY_TYPE NOT ILIKE '%law firm%')) WHERE UPPER(sd.ISSUER_NAME) LIKE UPPER('%<company_name>%') ORDER BY sd.DOCUMENT_ID, gp.PURCHASER_NAME",
  "limit": 100
}})

Step C

Present results in two sub-tables. Both use SPA # as the join key so the user can correlate rows.

Document overview

Purchaser / transaction details

Every amount carries the CURRENCY_CODE of its SPA — never assume USD, and never total across SPAs with different codes. When CURRENCY_CODE is null the document did not state a currency: render the bare number with a * and add the footnote * currency not stated in the source document.

Field notes:

• Upload date — when Carta extracted the document. For SPAs that predate the current extraction pipeline this falls back to a data-refresh date and reads as approximate.
• Executed? — Yes/No from document extraction; a specific execution date is not available
• Currency — read from the document extraction, per SPA. Null when the document itself stated no currency.

Step D

AskUserQuestion(
    questions=[{
        "question": "What would you like to do next?",
        "header": "Next step",
        "multiSelect": False,
        "options": [
            {"label": "Back to full audit", "description": "Return to the four-bucket portfolio summary. ← recommended"},
            {"label": "Look up another company", "description": "Search SPA documents for a different portfolio company."},
        ],
    }]
)

Gates

AI computation: No — bucket assignments and cost basis totals come directly from Carta data via deterministic SQL. No AI-derived values are presented as facts.

Best effort

• Authoritative (from Carta): bucket assignments, cost basis, first invested date, SPA document counts, execution status
• Computed (by Claude): fuzzy match groupings — if an investment name and SPA issuer name are joined via Jaro-Winkler similarity score < 100, that match is Claude's inference, not a system-recorded link. These rows are labeled with * in results.
• Surfaced as FYI, not in a bucket: orphaned SPAs (SPA documents uploaded under an issuer name with no fuzzy-match to any equity investment record) are counted via Query O and shown as an FYI pill in the page-header subtitle. They do not land in any bucket — the audit pivots on investments, so an SPA without a matching investment has nowhere to go. Typical causes: parent vs. subsidiary entity mismatch, post-investment renames that diverge beyond Jaro-Winkler ≥ 90, or SPAs uploaded for investments not yet booked in Carta.

Error handling

…(truncated)