---
name: voice-call-report
description: Generate voice call reports showing transcriptions, scores, and score deltas as markdown files
---

# voice-call-report Skill

## Purpose

Generate formatted markdown reports for voice calls showing:
- Full transcription with speaker labels
- Conflict health per segment
- Call score after each segment
- Score delta showing impact of each statement

**Use this skill when:**
- User asks to see a voice call report
- User wants to analyze call scoring trajectory
- User asks "show me the impact of each message"
- User requests a transcript with scores

---

## Step 1: Get the Call SID

You need a `call_sid` to generate the report. Use the `sql-reader` skill to find recent calls:

```bash
arsenal/dot-claude/skills/sql-reader/connect.sh "
SELECT DISTINCT
    m.provider_data->>'call_sid' as call_sid,
    MIN(m.created_at) as call_started,
    COUNT(*) as segments
FROM message m
WHERE m.provider_data->>'type' = 'voice_transcript'
  AND m.provider_data->>'call_sid' IS NOT NULL
GROUP BY m.provider_data->>'call_sid'
ORDER BY call_started DESC
LIMIT 10;
"
```

---

## Step 2: Query Call Data

Use `sql-reader` to get the call transcript with scores:

```bash
arsenal/dot-claude/skills/sql-reader/connect.sh "
SELECT
    (m.provider_data->>'segment_number')::int as seg,
    COALESCE(m.provider_data->>'speaker', 'Unknown') as speaker,
    COALESCE(me.enrichment_data->>'segment_conflict_health', '-') as conflict_health,
    COALESCE((me.enrichment_data->>'call_score')::text, '-') as score,
    m.content as transcript
FROM message m
LEFT JOIN message_enrichment me ON me.message_id = m.id
WHERE m.provider_data->>'call_sid' = 'YOUR_CALL_SID_HERE'
  AND m.provider_data->>'type' = 'voice_transcript'
ORDER BY (m.provider_data->>'segment_number')::int;
"
```

---

## Step 3: Format as Markdown Report

Create the report file at `tmp/reports/voice_call_CALL_SID.md`:

```markdown
# Voice Call Report

**Call SID:** CA1234...
**Generated:** YYYY-MM-DD HH:MM:SS

## Score Trajectory

| Seg | Speaker | Conflict Health | Score | Delta | Impact | Transcript |
|-----|---------|-----------------|-------|-------|--------|------------|
| 1   | Partner | Neutral         | 50    | --    | Start  | Hello...   |
| 2   | Partner | Positive        | 55    | +5    | Positive | I feel... |
| 3   | User    | Negative        | 48    | -7    | Negative | But you... |

## Summary

- **Starting Score:** 50
- **Final Score:** 72
- **Total Change:** +22
- **Most Positive Impact:** Segment 5 (+12)
- **Most Negative Impact:** Segment 3 (-7)

---
*Report generated by voice-call-report skill*
```

---

## Score Delta Calculation

For each segment with a score:
1. `delta = current_score - previous_score`
2. First segment shows `--` (no previous)
3. Segments without scores (pending/intervention) show `-`

**Impact categories:**
- `Very Positive`: delta > 10
- `Positive`: delta > 0
- `Neutral`: delta = 0
- `Negative`: delta > -10
- `Very Negative`: delta <= -10
- `N/A`: no score (pending message)

---

## Output Location

Save reports to: `tmp/reports/voice_call_{CALL_SID}.md`

The `tmp/` directory is gitignored.

---

## Common Violations

- **BANNED:** Generating reports without a valid call_sid
- **BANNED:** Outputting raw SQL results to the user (always format as markdown)
- **CORRECT:** Use `sql-reader` skill for database queries

---

## Troubleshooting

**Problem:** No data returned for call_sid
**Cause:** Call hasn't been processed or call_sid is incorrect
**Solution:** Check call exists with the query in Step 1

**Problem:** Score column shows NULL or dash
**Cause:** Message is pending/intervention type (no score computed)
**Solution:** This is expected - those messages don't affect the score

---

## Success Criteria

You've completed this skill when:
- [ ] Report is saved to `tmp/reports/`
- [ ] Report shows segment-by-segment scoring
- [ ] Score deltas are calculated correctly
- [ ] Report is readable markdown format