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

If unsure, one query can cover both:

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:

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:

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

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:

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