# SMS Duplicate Fix Plan

**Priority:** URGENT
**Estimated Effort:** 2-3 hours
**Risk:** Low (additive changes, no breaking changes)

---

## Problem Summary

Leads can be enrolled in multiple sequences simultaneously, causing duplicate SMS messages when the sequence processor fires.

---

## Solution: Three-Layer Protection

### Layer 1: Enrollment Prevention (Primary Fix)
### Layer 2: Send-Time Throttle (Safety Net)
### Layer 3: UI Warning (User Feedback)

---

## Layer 1: Fix Enrollment Logic

**File:** `src/routes/sequences.js`
**Location:** `/api/sequences/:id/enroll` endpoint (lines ~148-162)

### Current Code (Buggy):
```javascript
const { data: existingEnrollments } = await db
  .from('sequence_enrollments')
  .select('lead_id')
  .eq('sequence_id', sequenceId)  // Only checks same sequence
  .in('lead_id', leadsToEnroll)
  .or(`status.eq.active,enrolled_at.gte.${thirtyDaysAgo.toISOString()}`);
```

### Fixed Code:
```javascript
// Check for ANY active enrollment or recent enrollment (any sequence)
const { data: existingEnrollments } = await db
  .from('sequence_enrollments')
  .select('lead_id, sequence_id, status')
  .in('lead_id', leadsToEnroll)
  .or(`status.eq.active,enrolled_at.gte.${thirtyDaysAgo.toISOString()}`);

const alreadyEnrolled = new Set((existingEnrollments || []).map(e => e.lead_id));
const newLeadIds = leadsToEnroll.filter(id => !alreadyEnrolled.has(id));

// Build detailed skip reasons for feedback
const skipReasons = {};
for (const enrollment of existingEnrollments || []) {
  if (!skipReasons[enrollment.lead_id]) {
    skipReasons[enrollment.lead_id] = {
      reason: enrollment.status === 'active' ? 'already_in_active_sequence' : 'recently_enrolled',
      sequence_id: enrollment.sequence_id
    };
  }
}
```

### Response Change:
```javascript
res.json({
  success: true,
  enrolled: created.length,
  skipped: leadsToEnroll.length - newLeadIds.length,
  skipReasons: Object.keys(skipReasons).length > 0 ? skipReasons : undefined,
  enrollments: created
});
```

---

## Layer 2: Send-Time Throttle

**File:** `src/jobs/sequenceProcessor.js`
**Location:** `processEnrollmentStep()` function (around line 135)

### Add Before Sending SMS:
```javascript
// DUPLICATE PROTECTION: Check if we sent SMS to this lead in last 30 minutes
const thirtyMinutesAgo = new Date(Date.now() - 30 * 60 * 1000).toISOString();
const { data: recentSms } = await db
  .from('communications')
  .select('id, created_at')
  .eq('lead_id', lead.id)
  .eq('type', 'sms_outbound')
  .gte('created_at', thirtyMinutesAgo)
  .limit(1);

if (recentSms && recentSms.length > 0) {
  console.log(`[SequenceProcessor] SKIPPED: Lead ${lead.id} received SMS ${Math.round((Date.now() - new Date(recentSms[0].created_at).getTime()) / 60000)} minutes ago`);
  
  // Reschedule this step for 1 hour later instead of failing
  const rescheduleAt = new Date(Date.now() + 60 * 60 * 1000);
  await db
    .from('sequence_enrollments')
    .update({ next_step_at: rescheduleAt.toISOString() })
    .eq('id', enrollment.id);
    
  return {
    success: false,
    action: 'throttled',
    error: 'Lead received SMS within 30 minutes, rescheduled'
  };
}
```

---

## Layer 3: UI Warning

**File:** `admin/src/components/SequenceEnrollModal.jsx` (or equivalent)

### Add Warning When Lead Has Active Enrollment:
Before showing enroll button, check:
```javascript
// Fetch active enrollments for selected leads
const checkActiveEnrollments = async (leadIds) => {
  const { data } = await api.get('/api/sequences/check-enrollments', {
    params: { lead_ids: leadIds.join(',') }
  });
  return data.activeEnrollments || [];
};

// Show warning if any leads have active enrollments
{activeEnrollments.length > 0 && (
  <Alert variant="warning">
    {activeEnrollments.length} lead(s) are already in active sequences and will be skipped.
  </Alert>
)}
```

### New API Endpoint Needed:
**File:** `src/routes/sequences.js`
```javascript
router.get('/check-enrollments', async (req, res) => {
  const { lead_ids } = req.query;
  const leadIdArray = lead_ids.split(',').filter(Boolean);
  
  const { data } = await db
    .from('sequence_enrollments')
    .select('lead_id, sequence_id, sequences(name)')
    .in('lead_id', leadIdArray)
    .eq('status', 'active');
    
  res.json({ activeEnrollments: data || [] });
});
```

---

## Immediate Hotfix (Can Deploy Now)

If you need the fastest fix, Layer 2 alone will stop duplicates:

1. Edit `src/jobs/sequenceProcessor.js`
2. Add the 30-minute throttle check before `sendSMS()` call
3. Deploy

This is safe because:
- It doesn't break existing enrollments
- It just reschedules instead of failing
- Legitimate messages will still be sent (just delayed if duplicate)

---

## Database Cleanup Script

Run AFTER deploying the fix to clean up existing duplicate enrollments:

```sql
-- Find leads with multiple active enrollments
WITH duplicate_enrollments AS (
  SELECT 
    lead_id,
    COUNT(*) as enrollment_count,
    array_agg(id ORDER BY enrolled_at) as enrollment_ids
  FROM sequence_enrollments
  WHERE status = 'active'
  GROUP BY lead_id
  HAVING COUNT(*) > 1
)
SELECT 
  l.first_name,
  l.last_name,
  l.phone,
  de.enrollment_count,
  de.enrollment_ids
FROM duplicate_enrollments de
JOIN leads l ON l.id = de.lead_id;

-- Pause all but the first enrollment for each lead (PREVIEW FIRST!)
-- UPDATE sequence_enrollments
-- SET status = 'paused', pause_reason = 'duplicate_enrollment_cleanup'
-- WHERE id IN (
--   SELECT unnest(enrollment_ids[2:]) 
--   FROM duplicate_enrollments
-- );
```

---

## Testing Checklist

### Unit Tests:
- [ ] Enroll lead in Sequence A, then try Sequence B → Should fail/skip
- [ ] Enroll lead, complete sequence, enroll in new sequence after 30 days → Should work
- [ ] Bulk enroll leads, some already enrolled → Should skip enrolled, enroll new

### Integration Tests:
- [ ] Sequence processor respects 30-minute throttle
- [ ] Throttled messages get rescheduled, not lost
- [ ] Logs clearly indicate when throttle kicks in

### Manual Tests:
- [ ] Send SMS to lead via sequence
- [ ] Within 30 minutes, try manual send via /api/sms/send → Should work (different path)
- [ ] Within 30 minutes, try sequence send → Should be rescheduled

---

## Rollout Plan

1. **Phase 1 (Immediate):** Deploy Layer 2 throttle as hotfix
2. **Phase 2 (Same Day):** Deploy Layer 1 enrollment fix
3. **Phase 3 (Next Day):** Run database cleanup
4. **Phase 4 (Optional):** Add Layer 3 UI warning

---

## Success Metrics

- Zero duplicate SMS to same lead within 30 minutes
- Enrollment attempts for already-enrolled leads return clear error
- Sequence processor logs show throttle activations (should decrease over time)

---

## Risk Assessment

| Change | Risk | Mitigation |
|--------|------|------------|
| Layer 1: Enrollment check | Low | Only prevents new enrollments, doesn't affect existing |
| Layer 2: Send throttle | Low | Reschedules instead of failing, messages not lost |
| Layer 3: UI warning | Very Low | Informational only |
| DB Cleanup | Medium | Preview queries first, backup before UPDATE |