# SMS Sequence Automation - Implementation Plan

> Complete the last 5% of playbook support | Est: 12-15 hours

**Created:** Jan 19, 2026
**Status:** COMPLETE

---

## Executive Summary

**Goal:** Automate the playbook's multi-step follow-up sequences (SMS only)
**What exists:** Schema (sequences, sequence_steps, sequence_enrollments), scheduled SMS job
**What's missing:** Sequence processor, enrollment logic, auto-pause on reply, UI

---

## The Playbook Attempt Schedule

| Attempt | Day | Channel | Action | Automation |
|---------|-----|---------|--------|------------|
| 1 | 0 | Call | Intro call | Manual |
| 2 | 0 | SMS | If no answer | **Auto** |
| 3 | 1 | Call | Follow-up reminder | Manual (callback) |
| 4 | 3 | SMS | Value prop | **Auto** |
| 5 | 7 | SMS | Last chance | **Auto** |

**Focus:** Automate the 3 SMS steps. Calls remain manual but system shows reminders.

---

## What Already Exists

### Database Schema ✅

```sql
-- sequences table
CREATE TABLE sequences (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  trigger_type VARCHAR(50),  -- 'manual', 'new_lead', 'no_answer'
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ
);

-- sequence_steps table
CREATE TABLE sequence_steps (
  id UUID PRIMARY KEY,
  sequence_id UUID REFERENCES sequences(id),
  step_order INTEGER NOT NULL,
  action_type VARCHAR(50) NOT NULL,  -- 'sms', 'call_reminder', 'wait'
  delay_hours INTEGER DEFAULT 0,
  template_id UUID REFERENCES sms_templates(id),
  config JSONB DEFAULT '{}'
);

-- sequence_enrollments table
CREATE TABLE sequence_enrollments (
  id UUID PRIMARY KEY,
  sequence_id UUID REFERENCES sequences(id),
  lead_id UUID REFERENCES leads(id),
  current_step INTEGER DEFAULT 0,
  status VARCHAR(20) DEFAULT 'active',  -- 'active', 'paused', 'completed', 'replied'
  enrolled_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  paused_at TIMESTAMPTZ
);
```

### Scheduled SMS Job ✅

`src/jobs/scheduledSms.js` - Runs every 60 seconds, sends messages with `status: 'scheduled'`

### SMS Templates ✅

`sms_templates` table with 30+ templates including follow-up sequences

---

## What's Missing

### 1. Schema Enhancements (Migration)

```sql
-- Add next_step_at for scheduling
ALTER TABLE sequence_enrollments
ADD COLUMN IF NOT EXISTS next_step_at TIMESTAMPTZ;

-- Add pause_reason for tracking why paused
ALTER TABLE sequence_enrollments
ADD COLUMN IF NOT EXISTS pause_reason VARCHAR(50);

-- Index for processor queries
CREATE INDEX IF NOT EXISTS idx_sequence_enrollments_due
ON sequence_enrollments(next_step_at)
WHERE status = 'active';

-- Add channel to sequences for filtering
ALTER TABLE sequences
ADD COLUMN IF NOT EXISTS channel VARCHAR(20) DEFAULT 'sms';

-- Add step name for UI display
ALTER TABLE sequence_steps
ADD COLUMN IF NOT EXISTS name VARCHAR(100);
```

### 2. Sequence Processor Job

**File:** `src/jobs/sequenceProcessor.js`

```javascript
/**
 * Runs every 60 seconds
 * 1. Find enrollments where next_step_at <= NOW and status = 'active'
 * 2. For each enrollment:
 *    a. Get current step
 *    b. If SMS step: schedule message via communications table
 *    c. If call_reminder: create callback
 *    d. Increment current_step
 *    e. Calculate next_step_at based on next step's delay_hours
 *    f. If no more steps: mark completed
 */
```

### 3. Auto-Pause on Reply

**Modify:** `src/routes/webhooks.js` (Twilio inbound SMS handler)

```javascript
// When inbound SMS received:
// 1. Check if lead is enrolled in active sequence
// 2. If yes, pause enrollment with reason 'replied'
// 3. Notify rep via Slack
```

### 4. Enrollment API

**File:** `src/routes/sequences.js`

```
GET    /api/sequences                    - List active sequences
GET    /api/sequences/:id                - Get sequence with steps
POST   /api/sequences/:id/enroll         - Enroll lead(s)
POST   /api/sequences/:id/unenroll       - Unenroll lead
GET    /api/sequences/enrollments        - Get active enrollments (for queue view)
POST   /api/sequences/enrollments/:id/pause  - Pause enrollment
POST   /api/sequences/enrollments/:id/resume - Resume enrollment
```

### 5. UI Components

**Quick Enroll from Call Outcome:**
- Add "Start follow-up sequence" toggle to CallOutcomeSheet
- When call outcome is "no_answer", auto-suggest enrollment

**Today's Sequence Queue:**
- Show leads with pending sequence steps for today
- Filter: due today, by sequence, by status
- One-click send now / skip / pause

---

## Implementation Steps

### Phase 1: Backend Foundation (5-6 hours)

| Step | Task | Hours |
|------|------|-------|
| 1.1 | Schema migration (add columns, indexes) | 0.5 |
| 1.2 | Sequence processor job | 2 |
| 1.3 | Enrollment API routes | 1.5 |
| 1.4 | Auto-pause on reply (webhook mod) | 1 |
| 1.5 | Register routes, add to job scheduler | 0.5 |

### Phase 2: Seed Data (1-2 hours)

| Step | Task | Hours |
|------|------|-------|
| 2.1 | Create "New Worker" sequence | 0.5 |
| 2.2 | Create "New Contractor" sequence | 0.5 |
| 2.3 | Create SMS templates for sequence steps | 1 |

### Phase 3: Frontend (4-5 hours)

| Step | Task | Hours |
|------|------|-------|
| 3.1 | Add enroll toggle to CallOutcomeSheet | 1 |
| 3.2 | Create SequenceQueue component | 2 |
| 3.3 | Add to Playbook page (new tab) | 1 |
| 3.4 | Lead profile - show sequence status | 1 |

### Phase 4: Testing & Polish (2-3 hours)

| Step | Task | Hours |
|------|------|-------|
| 4.1 | End-to-end test: enroll → auto-send → reply → pause | 1 |
| 4.2 | Test edge cases (double enroll, completed lead, etc.) | 1 |
| 4.3 | Analytics: sequence completion rates | 1 |

---

## Total Estimate

| Phase | Hours |
|-------|-------|
| Phase 1: Backend | 5.5 |
| Phase 2: Seed Data | 2 |
| Phase 3: Frontend | 5 |
| Phase 4: Testing | 3 |
| **Total** | **15.5 hours** |

---

## Default Sequences to Create

### Sequence 1: New Worker Outreach

| Step | Day | Type | Template |
|------|-----|------|----------|
| 1 | 0 | sms | "Hey {first_name}, Rocky from RateRight. We connect tradies with work - 9.9% only when you get paid. Worth a quick chat?" |
| 2 | 1 | call_reminder | (Creates callback) |
| 3 | 3 | sms | "Hey {first_name}, just checking - still looking for work? We've got {trade} jobs coming through." |
| 4 | 7 | sms | "Last one from me {first_name}. If you're sorted for work, no worries. If things change, we're here." |

### Sequence 2: New Contractor Outreach

| Step | Day | Type | Template |
|------|-----|------|----------|
| 1 | 0 | sms | "Hey {first_name}, Rocky from RateRight. We supply quality tradies to builders - FREE to use. Worth a chat about your crew needs?" |
| 2 | 1 | call_reminder | (Creates callback) |
| 3 | 3 | sms | "Hey {first_name}, just following up. Need any {trade}s for upcoming jobs? We've got good blokes ready." |
| 4 | 7 | sms | "Final check-in {first_name}. If you're all staffed up, no dramas. We're here when you need crew." |

---

## Key Decisions

### 1. Call Reminders vs Auto-Calls
**Decision:** Create callbacks for "call_reminder" steps, not auto-dial
**Reason:** Calls should be intentional, not automated

### 2. What Triggers Pause?
**Pauses sequence:**
- Lead replies (any inbound SMS)
- Lead converts (status change to converted)
- Manual pause by rep
- Lead opts out

**Does NOT pause:**
- Outbound call made (sequence continues)
- Manual SMS sent outside sequence

### 3. Re-enrollment Rules
**Decision:** Can't re-enroll in same sequence while active or within 30 days of completion
**Reason:** Prevent spam

### 4. Time of Day for Sending
**Decision:** Respect AEST business hours (8am-6pm) - delay sends outside this window
**Reason:** Don't text tradies at midnight

---

## Files to Create/Modify

### Create:
```
supabase/migrations/YYYYMMDD_sequence_automation.sql
src/jobs/sequenceProcessor.js
src/routes/sequences.js
admin/src/components/playbook/SequenceQueue.jsx
```

### Modify:
```
src/index.js (register routes, add job)
src/routes/webhooks.js (auto-pause on reply)
admin/src/components/CallOutcomeSheet.jsx (enroll toggle)
admin/src/pages/LeadProfile.jsx (sequence status)
admin/src/pages/Playbook.jsx (add Sequences tab)
```

---

## Success Metrics

| Metric | Target |
|--------|--------|
| Sequence enrollment rate | >50% of no-answer calls |
| Auto-pause rate (replies) | >15% |
| Sequence completion rate | <30% (want replies before completion) |
| Reply rate from sequences | >10% |

---

## Comparison: Email Sequences vs SMS Sequences

| Factor | Email Plan | SMS Plan (This) |
|--------|------------|-----------------|
| Provider | SendGrid ($15-50/mo) | Twilio (existing) |
| Setup complexity | High (webhooks, tracking) | Low (reuse scheduled SMS) |
| Hours estimate | 18-23 | 12-15 |
| Primary use case | Cold outreach at scale | Follow-up after calls |
| Tracking | Opens, clicks | Replies only |

**Recommendation:** Build SMS sequences first. Email can come later if needed.

---

## Implementation Order

1. [x] Schema migration
2. [x] Sequence processor job
3. [x] Enrollment API
4. [x] Auto-pause on reply
5. [x] Seed sequences + templates
6. [x] CallOutcomeSheet enroll toggle
7. [x] SequenceQueue component
8. [x] Lead profile sequence status
9. [x] End-to-end testing
10. [x] Deploy + verify

---

## Completion Notes

**Completed:** Jan 19, 2026

### Files Created:
- `supabase/migrations/20260119113451_sequence_automation.sql`
- `supabase/migrations/20260119113909_seed_sequences.sql`
- `src/jobs/sequenceProcessor.js`
- `src/routes/sequences.js`
- `admin/src/components/playbook/SequenceQueue.jsx`

### Files Modified:
- `src/index.js` (added sequences routes)
- `src/jobs/index.js` (added sequence processor to scheduler)
- `src/routes/webhooks.js` (auto-pause on reply)
- `admin/src/api/client.js` (added sequencesApi)
- `admin/src/components/CallOutcomeSheet.jsx` (enroll toggle)
- `admin/src/pages/SalesPlaybook.jsx` (added SequenceQueue)
- `admin/src/pages/LeadProfile.jsx` (added SequenceStatusCard)

### Seed Sequences:
1. **New Worker Outreach** (4 steps over 7 days)
2. **New Contractor Outreach** (4 steps over 7 days)

---

*Plan ready for approval*