# Sales Playbook System - Complete Guide

**Created:** Jan 21, 2026
**Purpose:** Comprehensive documentation of the Sales Playbook and Sequence automation system

---

## Quick Reference

| Component | Location | Purpose |
|-----------|----------|---------|
| Playbook API | `src/routes/playbook.js` | AI-generated messages, bulk send |
| Sequences API | `src/routes/sequences.js` | Automated follow-up sequences |
| Sequence Processor | `src/jobs/sequenceProcessor.js` | Background job (every 60s) |
| Frontend Page | `admin/src/pages/SalesPlaybook.jsx` | Scripts, compliance, guidelines |
| Today's Plays | `admin/src/components/playbook/TodaysPlays.jsx` | Top 3 leads with AI messages |
| Bulk Send | `admin/src/components/playbook/BulkSendModal.jsx` | Multi-lead outreach |

---

## 1. Database Tables

### playbook_messages
Stores AI-generated messages for the "Today's Plays" feature.

```sql
CREATE TABLE playbook_messages (
  id UUID PRIMARY KEY,
  user_id UUID,
  lead_id UUID REFERENCES leads(id),
  message_text TEXT NOT NULL,
  message_angle VARCHAR(50),    -- 'follow_up', 'value_prop', 'urgency'
  was_sent BOOLEAN DEFAULT FALSE,
  sent_at TIMESTAMPTZ,
  generated_at TIMESTAMPTZ DEFAULT NOW(),
  batch_id UUID,                -- Groups bulk sends
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### sequences
Defines automated SMS/email/call sequences.

```sql
CREATE TABLE sequences (
  id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  trigger_type VARCHAR(50),     -- 'no_answer', 'manual', etc.
  is_active BOOLEAN DEFAULT true,
  channel VARCHAR(20) DEFAULT 'sms',
  user_id UUID REFERENCES auth.users(id),
  stage VARCHAR(50),            -- For stage-based matching
  lead_type VARCHAR(20),        -- 'worker', 'contractor'
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### sequence_steps
Individual steps within a sequence.

```sql
CREATE TABLE sequence_steps (
  id UUID PRIMARY KEY,
  sequence_id UUID REFERENCES sequences(id),
  step_order INTEGER NOT NULL,  -- 1, 2, 3...
  name VARCHAR(100),            -- 'Initial SMS', 'Follow-up Call'
  action_type VARCHAR(50),      -- 'sms', 'call_reminder', 'wait'
  delay_hours INTEGER DEFAULT 0,
  delay_days_min INTEGER DEFAULT 0,
  delay_days_max INTEGER DEFAULT 0,
  send_window VARCHAR(20) DEFAULT 'any',  -- 'morning', 'lunch', 'knockoff'
  message_template TEXT,        -- SMS content with {placeholders}
  template_id UUID REFERENCES sms_templates(id),
  config JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

### sequence_enrollments
Tracks lead enrollment in sequences.

```sql
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'
  enrolled_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  paused_at TIMESTAMPTZ,
  pause_reason VARCHAR(50),
  enrollment_source VARCHAR(50) DEFAULT 'manual',
  next_step_at TIMESTAMPTZ      -- When processor picks it up
);
```

### sms_templates
Template library for reusable SMS messages.

```sql
CREATE TABLE sms_templates (
  id UUID PRIMARY KEY,
  slug VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  description TEXT,
  variables JSONB DEFAULT '[]',
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
```

---

## 2. How Sequences Are Triggered

### Trigger Types

| Trigger | When | How |
|---------|------|-----|
| `manual` | User clicks enroll | API call to `/api/sequences/:id/enroll` |
| `no_answer` | Call ends with no answer | Backend auto-enrolls in `calls.js` |
| `stage_change` | Lead stage changes | Can be automated via triggers |

### Manual Enrollment
```javascript
// Frontend
await sequencesApi.enroll(sequenceId, [leadId]);

// Backend processes
POST /api/sequences/:id/enroll
Body: { lead_ids: ['uuid1', 'uuid2'] }
```

### Auto-Enrollment (No Answer)
In `src/routes/calls.js`, when a call outcome is `no_answer`:
```javascript
if (outcome === 'no_answer' && shouldAutoEnroll) {
  // Find appropriate sequence
  // Create enrollment with next_step_at = NOW + step delay
}
```

### Sequence Processor (Background Job)
Runs every 60 seconds via `src/jobs/sequenceProcessor.js`:

1. Checks business hours (8am-6pm AEST, weekdays only)
2. Queries: `WHERE status = 'active' AND next_step_at <= NOW()`
3. For each enrollment:
   - Gets current step
   - Executes action (SMS, callback, wait)
   - Advances to next step
   - Calculates next_step_at with randomization
4. Marks complete when no more steps

---

## 3. Template Storage & Personalization

### Template Sources (Priority Order)

1. **Inline in step**: `sequence_steps.message_template`
2. **Referenced template**: `sequence_steps.template_id` → `sms_templates`
3. **AI-generated**: `playbook_messages` for Today's Plays

### Supported Variables

| Variable | Replaced With | Fallback |
|----------|---------------|----------|
| `{first_name}` | `lead.first_name` | "there" |
| `{name}` | Full name | "there" |
| `{trade}` | `lead.trade` or `metadata.trade` | "trade" |
| `{company}` | `lead.company` | "" |
| `{sender_name}` | Configured sender | "Rocky" |

### Personalization Code
```javascript
// src/jobs/sequenceProcessor.js
function personalizeMessage(template, lead) {
  let message = template;
  const replacements = {
    '{first_name}': lead?.first_name || 'there',
    '{name}': fullName,
    '{trade}': lead?.trade || lead?.metadata?.trade || 'trade',
    '{company}': lead?.company || '',
    '{sender_name}': 'Rocky',
  };
  for (const [key, value] of Object.entries(replacements)) {
    message = message.replace(new RegExp(key, 'g'), value);
  }
  return message;
}
```

### Example Templates
```
Initial: "Hey {first_name}, Rocky from RateRight here. We connect tradies
with work - 9.9% only when you get paid. Worth a quick chat?"

Follow-up: "Hey {first_name}, just checking - still looking for work?
We've got {trade} jobs coming through."

Last chance: "Last one from me {first_name} - let me know if you want
to chat about getting more {trade} work. No pressure!"
```

---

## 4. How to Update the Playbook

### Adding a New Sequence

**Via SQL (recommended for production):**
```sql
-- 1. Create sequence
INSERT INTO sequences (name, description, trigger_type, channel, lead_type)
VALUES ('New Follow-up', 'Custom follow-up sequence', 'manual', 'sms', 'worker');

-- 2. Add steps
INSERT INTO sequence_steps (sequence_id, step_order, name, action_type, delay_days_min, delay_days_max, send_window, message_template)
VALUES
  ('seq-uuid', 1, 'Initial SMS', 'sms', 0, 0, 'morning', 'Hey {first_name}, ...'),
  ('seq-uuid', 2, 'Follow-up', 'sms', 2, 3, 'knockoff', 'Checking in {first_name}...'),
  ('seq-uuid', 3, 'Call Reminder', 'call_reminder', 5, 7, 'any', NULL);
```

### Modifying Step Timing

```sql
-- Change delay range
UPDATE sequence_steps
SET delay_days_min = 1, delay_days_max = 2, send_window = 'lunch'
WHERE sequence_id = 'uuid' AND step_order = 2;
```

### Updating Templates

```sql
-- Update inline template
UPDATE sequence_steps
SET message_template = 'New message text with {first_name}'
WHERE id = 'step-uuid';

-- Or use shared template
UPDATE sequence_steps
SET template_id = 'template-uuid', message_template = NULL
WHERE id = 'step-uuid';
```

### Adding SMS Templates

```sql
INSERT INTO sms_templates (slug, name, content, variables)
VALUES (
  'worker-intro',
  'Worker Introduction',
  'Hey {first_name}, Rocky here from RateRight...',
  '["first_name", "trade"]'
);
```

### Disabling a Sequence

```sql
-- Soft disable (keeps enrollments but stops new ones)
UPDATE sequences SET is_active = false WHERE id = 'uuid';

-- Pause all active enrollments
UPDATE sequence_enrollments
SET status = 'paused', pause_reason = 'sequence_disabled'
WHERE sequence_id = 'uuid' AND status = 'active';
```

---

## 5. Smart Timing System

### Send Windows (AEST)

| Window | Hours | Best For |
|--------|-------|----------|
| `morning` | 6:00-7:30am | Before site starts |
| `lunch` | 12:00-1:00pm | Lunch break |
| `knockoff` | 3:00-6:00pm | After work |
| `any` | 8:00am-6:00pm | General business |

### Randomization (v2)

Steps can specify a range:
```sql
delay_days_min = 2,  -- Minimum 2 days
delay_days_max = 4,  -- Maximum 4 days
send_window = 'knockoff'
```

The processor picks a random day in range, then a random time within the window.

### Business Hour Enforcement

- Only sends during 8am-6pm AEST
- Automatically skips weekends
- Reschedules overdue messages to next business day

---

## 6. API Reference

### Playbook API

| Endpoint | Method | Purpose |
|----------|--------|---------|
| `/api/playbook/today` | GET | Top 3 leads with AI messages |
| `/api/playbook/send` | POST | Send single message |
| `/api/playbook/bulk-preview` | GET | Preview bulk outreach |
| `/api/playbook/bulk-send` | POST | Send bulk messages |

### Sequences API

| Endpoint | Method | Purpose |
|----------|--------|---------|
| `/api/sequences` | GET | List active sequences |
| `/api/sequences/:id` | GET | Get sequence with steps |
| `/api/sequences/:id/enroll` | POST | Enroll lead(s) |
| `/api/sequences/:id/unenroll` | POST | Unenroll lead |
| `/api/sequences/enrollments/active` | GET | Active enrollment queue |
| `/api/sequences/enrollments/:id/pause` | POST | Pause enrollment |
| `/api/sequences/enrollments/:id/resume` | POST | Resume enrollment |
| `/api/sequences/enrollments/:id/skip` | POST | Skip to next step |
| `/api/sequences/enrollments/:id/send-now` | POST | Trigger immediately |
| `/api/sequences/lead/:leadId/opt-out` | GET/POST | Opt-out status |

---

## 7. Frontend API Client

```javascript
// admin/src/api/client.js

export const playbookApi = {
  getToday: () => request('/api/playbook/today'),
  send: (leadId, messageId, messageText) =>
    request('/api/playbook/send', { method: 'POST', body: JSON.stringify({...}) }),
  getBulkPreview: (params) => request(`/api/playbook/bulk-preview?${query}`),
  bulkSend: (batchId, leadIds, edits) =>
    request('/api/playbook/bulk-send', { method: 'POST', body: JSON.stringify({...}) }),
};

export const sequencesApi = {
  list: (channel) => request(`/api/sequences?channel=${channel}`),
  get: (id) => request(`/api/sequences/${id}`),
  enroll: (sequenceId, leadIds) => request(`/api/sequences/${sequenceId}/enroll`, {...}),
  unenroll: (sequenceId, leadId) => request(`/api/sequences/${sequenceId}/unenroll`, {...}),
  getActiveEnrollments: (params) => request('/api/sequences/enrollments/active'),
  pauseEnrollment: (id, reason) => request(`/api/sequences/enrollments/${id}/pause`, {...}),
  resumeEnrollment: (id) => request(`/api/sequences/enrollments/${id}/resume`, {...}),
  skipStep: (id) => request(`/api/sequences/enrollments/${id}/skip`, {...}),
  sendNow: (id) => request(`/api/sequences/enrollments/${id}/send-now`, {...}),
};
```

---

## 8. Default Sequences

### New Worker Outreach
- **Trigger:** no_answer
- **Channel:** SMS
- **Steps:**
  1. Day 0 (morning): Initial intro SMS
  2. Day 1 (any): Follow-up call reminder
  3. Day 3 (knockoff): Value prop SMS
  4. Day 7 (any): Last chance SMS

### New Contractor Outreach
- **Trigger:** no_answer
- **Channel:** SMS
- **Steps:**
  1. Day 0: Contractor-focused intro
  2. Day 1: Call reminder
  3. Day 3: Hiring needs follow-up
  4. Day 7: Final check-in

---

## 9. Safety & Collision Prevention

| Protection | Implementation |
|------------|----------------|
| 1-hour contact window | Skips leads contacted by anyone in last hour |
| 30-day re-enrollment | Prevents duplicate enrollments |
| Max 20 per bulk | Rate limit on bulk sends |
| Business hours | 8am-6pm AEST only |
| Weekend skip | Auto-reschedules to Monday |
| Status checks | Skips converted/opted-out/archived |
| Skip on failure | Bad phone doesn't block sequence |

---

## 10. Troubleshooting

### Sequences Not Processing

1. **Check RLS:** Jobs must use `supabaseAdmin || supabase`
2. **Check business hours:** Only runs 8am-6pm AEST weekdays
3. **Check enrollment status:** Must be `active`
4. **Check next_step_at:** Must be <= NOW

### Messages Not Personalized

1. **Check lead data:** `first_name`, `trade` must exist
2. **Check template syntax:** Use `{first_name}` not `{{first_name}}`
3. **Check fallbacks:** Empty values use defaults

### Enrollment Not Created

1. **Check 30-day window:** May already be enrolled recently
2. **Check opt-out:** Lead may have `sequence_opted_out = true`
3. **Check sequence active:** `is_active` must be true

---

## 11. Key Files Reference

| File | Purpose |
|------|---------|
| `src/routes/playbook.js` | Playbook API endpoints |
| `src/routes/sequences.js` | Sequences API endpoints |
| `src/jobs/sequenceProcessor.js` | Background processor |
| `src/jobs/index.js` | Job scheduler |
| `admin/src/pages/SalesPlaybook.jsx` | Main playbook page |
| `admin/src/components/playbook/TodaysPlays.jsx` | Top leads widget |
| `admin/src/components/playbook/BulkSendModal.jsx` | Bulk outreach |
| `admin/src/components/playbook/SequenceQueue.jsx` | Enrollment queue |
| `admin/src/api/client.js` | API client functions |
| `supabase/consolidated-migration.sql` | Table definitions |

---

## 12. Adding New Features

### Add New Variable

1. Update `personalizeMessage()` in `sequenceProcessor.js`
2. Document in this guide
3. Update existing templates if needed

### Add New Action Type

1. Add case in `processEnrollmentStep()`
2. Update `action_type` enum in schema
3. Add UI handling in SequenceQueue

### Add New Send Window

1. Add to `SEND_WINDOWS` object in `sequenceProcessor.js`
2. Update UI dropdown options
3. Document timing in this guide