# Avoca Vapi Call Resolve

> Resolves Avoca (internal) call IDs to VAPI call IDs using Supabase public.calls, then loads full call payloads via the VAPI API MCP get_call tool. Use when the user gives Avoca call IDs, Enterprise/API call_id values, or a CSV of Avoca calls and wants VAPI call details, transcripts, or provider metadata that only exist on VAPI. Supports saving results to the matching client's logs/ folder.

- Skill: `ivangit-avoca/avoca-vapi-call-resolve` (Agent Skill)
- Install (CLI): `npx skillmds@latest add ivangit-avoca/avoca-vapi-call-resolve`
- Raw SKILL.md: https://api.skillmd.com/api/skills/ivangit-avoca/avoca-vapi-call-resolve/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: ivangit-avoca (https://skillmd.com/u/ivangit-avoca)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/ivangit-avoca/avoca-vapi-call-resolve

---


# Avoca → VAPI call resolution

## When this applies

- User mentions **Avoca call ID**, **call_id** from the Enterprise/API export, or uploads a **CSV** whose call identifier is the Avoca row id — and needs **VAPI** data (`get_call`).
- The Avoca id (`calls.id`, UUID string) is **not** the same as VAPI's call id (`calls.vapi_call_id`); the database links them.

## MCP servers and tools

1. **Supabase** (`user-Supabase`): read the tool schema for `execute_sql` before calling. Requires `project_id` (discover via `list_projects` if unknown).
2. **VAPI API** (`user-vapi-api`): `get_call` with argument `callId` set to the **VAPI** id from Supabase — read that tool's schema before calling.

## Resolution rules

| Input | Meaning |
|--------|--------|
| UUID matching `calls.id` | Avoca call → look up `vapi_call_id`. |
| String matching `calls.vapi_call_id` | Already VAPI id → optional DB row for context; `get_call` uses this id. |

If unsure, one query can cover both:

```sql
SELECT id AS avoca_call_id, vapi_call_id, team_id, ai_or_human
FROM public.calls
WHERE id = '<candidate>'::uuid
   OR vapi_call_id = '<candidate>';
```

## Single Avoca ID

1. Run `execute_sql` with `project_id` and a query like above (or only `WHERE id = ...` if the user confirmed Avoca id).
2. If `vapi_call_id` is null (e.g. human-handled call), report that VAPI `get_call` does not apply; summarize what Supabase returned if useful.
3. If `vapi_call_id` is set, call `get_call` with that value.

## CSV / many IDs

The Avoca dashboard has **two CSV export formats:**

| Export type | ID column | Other useful columns |
|-------------|-----------|---------------------|
| **AI Calls Export** (has call IDs) | `Call ID` (UUID) | Date, Time, Duration, Status, Call Reason, Summary, Emergency, Caller ID, Job ID, Customer ID, Notes |
| **Basic Call Export** (no call IDs) | *none* | Call Result, Call Date, Call Duration, Customer Name, Customer Phone, Summary |

If the CSV has a `Call ID` column, use those UUIDs directly. If it doesn't, fall back to matching via Supabase using phone number + timestamp:

```sql
SELECT id AS avoca_call_id, vapi_call_id, team_id, ai_or_human, created_at
FROM public.calls
WHERE team_id = <team_id>
  AND customer_phone = '<phone>'
  AND created_at BETWEEN '<start>' AND '<end>';
```

**For CSVs with Call IDs:**

1. Parse the `Call ID` column — these are Avoca UUIDs (`calls.id`).
2. Normalize to a list of UUID strings; drop blanks and duplicates.
3. Batch-resolve in Supabase with one query (adjust batch size if the list is huge, e.g. 100–500 ids per round):

```sql
SELECT id AS avoca_call_id, vapi_call_id, team_id, ai_or_human
FROM public.calls
WHERE id = ANY (ARRAY[
  'uuid-1',
  'uuid-2'
]::uuid[]);
```

4. For each row with a non-null `vapi_call_id`, call `get_call` (parallelize when safe). For missing rows or null `vapi_call_id`, note them in a short summary table (Avoca id → status).

## Client folder routing

The `team_id` from Supabase maps to the client folder convention `clients/{Name}-{ID}/`. Use it to automatically route logs to the right place.

1. **Identify the client folder.** Match `team_id` from the SQL results to the numeric suffix in `clients/*/` folder names (e.g., team_id `2285` → `clients/Sykes Services-2285/`).
   - If the user already said which client this is for, use that folder directly.
   - If the CSV contains calls from multiple teams, group by `team_id` and file separately.
   - If no matching folder exists, ask the user before creating one.
2. **Read `context.md`** from the matched client folder before writing, so you have full context on the client.

## Saving logs

When the user wants logs saved (default for CSV uploads; ask if unclear for single IDs):

1. **File location:** `clients/{Name}-{ID}/logs/{date}-vapi-call-export.md` where `{date}` is today's date (`YYYY-MM-DD`). If a file with that name already exists, append a short disambiguator (e.g., `-2`).
2. **File format:** Markdown with a summary header and per-call sections. Structure:

```markdown
# {Client Name} — VAPI call logs ({date})

**Source:** Avoca dashboard CSV export ({N} calls)
**Resolved:** {resolved_count} VAPI calls | {no_vapi_count} human/no-VAPI | {missing_count} not in DB

---

## Summary

| # | Avoca ID | VAPI ID | Direction | Duration | Caller | Status |
|---|----------|---------|-----------|----------|--------|--------|
| 1 | `abc...` | `xyz...` | inbound | 3m 42s | +1... | ended-reason |
| ... |

---

## Call details

### Call 1 — `{vapi_call_id_short}`

- **Avoca ID:** `{avoca_call_id}`
- **VAPI ID:** `{vapi_call_id}`
- **Time:** {started_at} → {ended_at}
- **Direction:** {direction}
- **Duration:** {duration}
- **Caller:** {caller_number}
- **End reason:** {ended_reason}
- **Cost:** ${cost}

#### Transcript

> {role}: {message}
> {role}: {message}
> ...

#### Tool calls

| Tool | Arguments (summary) | Result (summary) |
|------|---------------------|-------------------|
| ... | ... | ... |

---
```

3. **What to include from `get_call`:** Extract and format these fields per call:
   - `id`, `type`, `status`, `endedReason`
   - `startedAt`, `endedAt`, duration (compute from timestamps)
   - `direction` (inbound/outbound)
   - Customer phone number (from `customer.number` or `phoneNumber`)
   - `transcript` — format as a readable dialogue with speaker labels
   - `messages` — tool-call entries (tool name, arguments summary, result summary)
   - `cost` and `costBreakdown` (if present)
   - `analysis.summary` and `analysis.successEvaluation` (if present)

4. **Large batches:** If more than ~20 calls, split into multiple files (`-part1`, `-part2`, etc.) to keep each file scannable.

5. **Tell the user** the file path after writing so they can find it.

## Safety

- Only interpolate IDs that match UUID format; do not pass arbitrary CSV text into SQL.
- If schema differs in another environment, use Supabase `list_tables` / inspect types before assuming column names.

## Output

For in-chat output, prefer a compact summary: how many calls resolved, how many saved, file path(s). Don't dump full transcripts in chat when saving to disk — just confirm and link to the file.