couple-weekly-report

name	couple-weekly-report
description	Generate weekly relationship health report showing affect ratios and repair rates for both partners across 3 weeks (current + 2 prior) with message quotes.

Couple Weekly Report Skill

Generate a concise weekly relationship health report with metrics and message context.

---

🎯 When to Use This Skill

Use this skill when:

• "Generate weekly report for [couple]"
• "Show me this week's relationship metrics for [couple]"
• "Create a weekly health summary for [couple]"
• "What happened this week with [couple]?"

Output: Markdown file with 3-week metrics table + message quotes for context.

---

📋 Report Structure

The report includes:

1. Metrics Table: 3 weeks of data (current week + 2 prior weeks) showing:

   • Week dates
   • Each partner's affect ratio (positive:negative)
   • Each partner's repair rate (%)
   • Trend indicators (↑↓→)

2. Message Context: 2-4 sentences quoting actual messages that:

   • Illustrate significant changes in metrics
   • Provide emotional color for the week's dynamics
   • Interpret through the product framework (see below)

---

📖 Product Framework Reference

IMPORTANT: Before generating any report, read the "Product Principles" section at the top of README.md (parent repo).

When interpreting metrics:

1. Affect Ratios → Reference "What we do" section for the two core skills

   • High positive affect = practicing the first skill (expressing needs vulnerably)
   • High negative affect = protest behavior (see "Conflict is a plea for connection")

2. Repair Rates → Reference "What we do" section for engagement patterns

   • High repair rate = practicing the second skill (staying engaged)
   • Low repair rate = withdrawing or defending

3. Attachment Patterns → Reference "Understanding Your Role in the Dance" section

   • Identify Pursuer/Withdrawer dynamics from metrics
   • Map to the three common patterns described
   • Note which skill each partner needs to focus on

4. Message Context → Reference "Why these skills work" section

   • Quote examples showing vulnerability vs protest behavior
   • Show de-escalation attempts
   • Use EFT lens: conflict as plea for connection

Health Thresholds: Use Gottman research thresholds from "The cost of destructive conflict patterns" section.

---

🔄 Workflow

Step 1: Identify the Couple and Week

Ask user if needed:

• Which couple? (Get persons table IDs for both partners)
• Which week? (Default: current week, Mon-Sun)

Key dates:

• Current week: Most recent Monday-Sunday
• Week -1: Previous Monday-Sunday
• Week -2: Two weeks ago Monday-Sunday

Step 2: Calculate Weekly Metrics (3 weeks)

For each week and each partner, calculate:

Affect Ratio Query:

WITH affect_counts AS (
  SELECT
    p.id as person_id,
    p.name,
    DATE_TRUNC('week', m.provider_timestamp) as week_start,
    COUNT(*) FILTER (WHERE me.valence = 'Positive') as positive_count,
    COUNT(*) FILTER (WHERE me.valence = 'Negative') as negative_count
  FROM message m
  JOIN person_contacts pc ON m.sender_person_contact_id = pc.id
  JOIN persons p ON pc.person_id = p.id
  LEFT JOIN message_enrichment me ON me.message_id = m.id
  WHERE m.provider_timestamp >= CURRENT_DATE - INTERVAL '21 days'  -- 3 weeks
    AND pc.person_id IN (:person1_id, :person2_id)
    AND me.affect IS NOT NULL
  GROUP BY p.id, p.name, week_start
)
SELECT
  person_id,
  name,
  week_start,
  positive_count,
  negative_count,
  CASE
    WHEN negative_count = 0  positive_count    
     negative_count    
     ROUND((positive_count::  negative_count), )
    affect_ratio
 affect_counts
  week_start , name;

Repair Rate Query:

WITH conflict_repairs AS (
  SELECT
    p.id as person_id,
    p.name,
    DATE_TRUNC('week', m.provider_timestamp) as week_start,
    COUNT(*) FILTER (
      WHERE me.conflict_state IN ('De-escalation', 'De-Escalation', 'Resolution')
    ) as repairs,
    COUNT(*) FILTER (
      WHERE me.conflict_state IN ('New Conflict', 'New conflict', 'Escalation')
    ) as conflicts
  FROM message m
  JOIN person_contacts pc ON m.sender_person_contact_id = pc.id
  JOIN persons p ON pc.person_id = p.id
  LEFT JOIN message_enrichment me ON me.message_id = m.id
  WHERE m.provider_timestamp >= CURRENT_DATE - INTERVAL '21 days'
    AND pc.person_id IN (:person1_id, :person2_id)
    AND me.conflict_state IS NOT NULL
  GROUP BY p.id, p.name, week_start
)
SELECT
  person_id,
  name,
  week_start,
  repairs,
  conflicts,
  
     conflicts    
     ROUND((repairs::  conflicts  ), )
    repair_rate_pct
 conflict_repairs
  week_start , name;

Step 3: Fetch Representative Messages

Get 3-5 meaningful messages from the current week that show:

• High-affect moments (positive or negative)
• Conflict patterns or repairs
• Notable communication shifts

SELECT
  p.name as sender,
  m.message_text,
  me.affect,
  me.valence,
  me.conflict_state,
  m.provider_timestamp
FROM message m
JOIN person_contacts pc ON m.sender_person_contact_id = pc.id
JOIN persons p ON pc.person_id = p.id
LEFT JOIN message_enrichment me ON me.message_id = m.id
WHERE m.provider_timestamp >= :current_week_start
  AND m.provider_timestamp < :current_week_end
  AND pc.person_id IN (:person1_id, :person2_id)
  AND (
    me.affect IN ('Joy', 'Love', 'Contempt', 'Partner-Criticism', 'Stonewalling')
    OR me.conflict_state IN ('New Conflict', 'Escalation', 'De-escalation', 'Resolution')
  )
ORDER BY
  CASE
    WHEN me.valence = 'Negative' THEN 1
    WHEN me.conflict_state IS NOT NULL THEN 2
    ELSE 3
  END,
  m.provider_timestamp DESC
LIMIT 5;

Step 4: Generate Markdown Report

Create markdown file: reports/couple_weekly_report_{couple_name}_{week_date}.md

Template:

# Weekly Relationship Report: {Partner1} & {Partner2}
**Week of {Current Week Start} - {Current Week End}**

---

## 📊 Metrics Overview (3 Weeks)

| Week | {Partner1} Affect Ratio | {Partner1} Repair % | {Partner2} Affect Ratio | {Partner2} Repair % |
|------|-------------------------|---------------------|-------------------------|---------------------|
| Current ({dates}) | {ratio} {trend} | {pct}% {trend} | {ratio} {trend} | {pct}% {trend} |
| Week -1 ({dates}) | {ratio} | {pct}% | {ratio} | {pct}% |
| Week -2 ({dates}) | {ratio} | {pct}% | {ratio} | {pct}% |

**Legend:**
- Affect Ratio: Positive:Negative emotions (Healthy: 5:1 to 30:1)
- Repair Rate: % of conflicts successfully de-escalated (Healthy: >75%)
- Trends: ↑ improving, ↓ declining, → stable

---

## 💬 This Week's Context

{2-4 sentences summarizing the week using the two core skills framework from README.md, with direct message quotes}

**Instructions:**
- Reference "What we do" section to interpret affect patterns (vulnerability vs protest behavior)
- Quote messages that show skill practice or challenges
- Use language from "Conflict is a plea for connection" to reframe criticism

**Example:**
"{Partner1}'s affect ratio improved significantly this week (8.2:1 ↑ from 3.1:1), with messages like '{positive quote}' showing vulnerability. However, {Partner2} showed increased Partner-Criticism ('{negative quote}'), suggesting protest behavior. Their repair rate remained strong at 80%, with effective de-escalation in conversations about {topic}."

---

## 📈 Trend Insights

{1-2 sentences noting significant trends using attachment patterns from README.md}

**Instructions:**
- Reference "Understanding Your Role in the Dance" section to identify pattern (Pursuer/Withdrawer, Attack-Attack, or Avoidant-Avoidant)
- Note which of the two core skills each partner needs most
- Use research language from "Why these skills work" section

**Example:**
"Metrics suggest a [pattern name] dynamic, with {Partner1} showing [pursuer/withdrawer behaviors]. {Partner2}'s [improving/declining] repair rate indicates [progress/challenge] with the second core skill described in README.md."

---

*Report generated: {timestamp}*

Step 5: Save and Display

1. Save markdown file to reports/ directory
2. Display file path to user
3. Show preview of key metrics

---

🎯 Health Thresholds (Quick Reference)

Affect Ratio (Positive:Negative)

• Healthy: 5:1 to 30:1
• At-Risk: <5:1
• Context-Dependent: >30:1 (may indicate conflict avoidance)

Repair Rate (De-escalation Success %)

• Healthy: >75%
• At-Risk: 50-75%
• Concerning: <50%

---

🚀 Usage Example

User: "Generate weekly report for Sam and Alex"

Claude (using sql-reader skill):
1. Identifies person_ids for Sam and Alex from persons table
2. Determines current week dates (Mon-Sun)
3. Runs affect ratio query for 3 weeks
4. Runs repair rate query for 3 weeks
5. Fetches 3-5 representative messages from current week
6. Generates markdown report with:
   - 3-week metrics table with trends
   - 2-4 sentences with message quotes
   - Trend insights
7. Saves to: reports/couple_weekly_report_sam_alex_2025-01-13.md
8. Shows user the file path and key findings

---

💡 Tips

Choosing Quotes:

• Pick messages that illustrate the two core skills from README.md "What we do" section
• Include both partners' voices
• Show emotional color:
  • Vulnerability examples (first skill)
  • Staying engaged examples (second skill)
  • Protest behavior (see "Conflict is a plea for connection" in README.md)
• Keep quotes concise (1 sentence each)

Interpreting Metrics (reference README.md sections):

• High positive affect = First skill (expressing needs vulnerably)
• High negative affect = Protest behavior (see "Why we do it" section)
• High repair rate = Second skill (staying engaged)
• Low repair rate = Withdrawing or defending (see "Pursuer & Withdrawer Dynamics")
• >30:1 affect ratio = May indicate conflict avoidance (see "Avoidant-Avoidant" pattern)

Calculating Trends:

• ↑ if current week > previous week by >20%
• ↓ if current week < previous week by >20%
• → if within 20% range

Handling Missing Data:

• If no affect data: Show "N/A" and note in context
• If no conflicts: Repair rate shows "No conflicts this week"
• If <3 weeks of data: Show available weeks only

---

🔗 Related Resources

• README.md (Product Principles): Source of truth for interpreting metrics through the product framework
• sql-reader: Use this skill to query the production database
• product-data-analyst: For broader relationship health analysis
• _archived/assess-couple-health: More comprehensive clinical assessment (8-week trailing averages)