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:

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:

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:

# 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