# Email Sequences - Implementation Plan

> Priority #1 Weakness Fix | Est: 15-20 hours | Ongoing: $15-50/mo

---

## Executive Summary

**Goal:** Add multi-step email sequences to compete with Outreach/Salesloft
**Reuse:** 70% of SMS infrastructure directly transfers
**Build:** Email service, templates, sequence builder, tracking

---

## What Exists (Reusable)

| Component | Status | Reuse Level |
|-----------|--------|-------------|
| Template rendering (Mustache) | Production | 100% |
| Scheduled message processing | Production | 90% |
| Batch sending pattern | Production | 90% |
| Communications table | Production | 100% |
| Sequences schema (unused) | Defined | 80% |
| Lead variables builder | Production | 100% |
| Job scheduler (60s intervals) | Production | 100% |
| Rate limiting middleware | Production | 90% |

---

## Architecture Decision

### Email Provider: **SendGrid**

| Factor | SendGrid | Postmark | AWS SES |
|--------|----------|----------|---------|
| Price (40K/mo) | $15-20 | $10-15 | $4 |
| Webhooks | Excellent | Excellent | Manual |
| Node SDK | Yes | Yes | Yes |
| Sequences | Built-in | No | No |
| Open/Click tracking | Yes | Yes | Manual |
| Australian sending | Yes | Yes | Yes |
| **Winner** | ✅ | - | - |

**Why SendGrid:** Best balance of features, price, and ease of integration. Event webhooks for opens/clicks/bounces out of the box.

---

## Implementation Steps

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

#### Step 1.1: Install SendGrid + Create Service
**File:** `src/services/email.js`
**Time:** 1 hour

```javascript
// Pattern from twilio.js
const sgMail = require('@sendgrid/mail');

sgMail.setApiKey(process.env.SENDGRID_API_KEY);

async function sendEmail({ to, subject, html, text, from, replyTo, metadata }) {
  // Validate
  // Send via SendGrid
  // Return { success, messageId, error }
}

async function sendBatchEmails(emails) {
  // Loop with delay (like SMS batch)
  // Return { sent, failed, errors }
}
```

#### Step 1.2: Database Migration - Email Templates
**File:** `supabase/migrations/YYYYMMDD_email_templates.sql`
**Time:** 30 min

```sql
CREATE TABLE IF NOT EXISTS email_templates (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  preview_text VARCHAR(150),
  html_content TEXT NOT NULL,
  text_content TEXT,
  variables JSONB DEFAULT '[]',
  category VARCHAR(50) DEFAULT 'general',
  is_active BOOLEAN DEFAULT TRUE,
  usage_count INTEGER DEFAULT 0,
  open_rate DECIMAL(5,2),
  click_rate DECIMAL(5,2),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_email_templates_slug ON email_templates(slug);
CREATE INDEX idx_email_templates_category ON email_templates(category);
```

#### Step 1.3: Email Routes - Basic CRUD
**File:** `src/routes/email.js`
**Time:** 1.5 hours

```
GET    /api/email/templates          - List templates
GET    /api/email/templates/:slug    - Get template
POST   /api/email/templates          - Create template
PUT    /api/email/templates/:id      - Update template
DELETE /api/email/templates/:id      - Delete template
POST   /api/email/send               - Send single email
POST   /api/email/send-batch         - Send batch emails
```

#### Step 1.4: Register Routes + Env Vars
**File:** `src/index.js`
**Time:** 30 min

```javascript
const emailRoutes = require('./routes/email');
app.use('/api/email', requireAuth, emailRoutes);
```

Add to Railway:
- `SENDGRID_API_KEY`
- `SENDGRID_FROM_EMAIL` (verified sender)

---

### Phase 2: Sequences Engine (6-8 hours)

#### Step 2.1: Activate Sequences Tables
**File:** `supabase/migrations/YYYYMMDD_email_sequences.sql`
**Time:** 1 hour

```sql
-- sequences table already exists in schema.sql
-- Add email-specific fields

ALTER TABLE sequences ADD COLUMN IF NOT EXISTS
  channel VARCHAR(20) DEFAULT 'email'; -- 'email', 'sms', 'mixed'

ALTER TABLE sequence_steps ADD COLUMN IF NOT EXISTS
  email_template_id UUID REFERENCES email_templates(id);

-- Add sequence stats tracking
CREATE TABLE IF NOT EXISTS sequence_stats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sequence_id UUID REFERENCES sequences(id),
  period_start DATE NOT NULL,
  enrolled INTEGER DEFAULT 0,
  completed INTEGER DEFAULT 0,
  replied INTEGER DEFAULT 0,
  converted INTEGER DEFAULT 0,
  bounced INTEGER DEFAULT 0,
  unsubscribed INTEGER DEFAULT 0,
  UNIQUE(sequence_id, period_start)
);
```

#### Step 2.2: Sequence Builder API
**File:** `src/routes/sequences.js`
**Time:** 2 hours

```
GET    /api/sequences                    - List sequences
GET    /api/sequences/:id                - Get sequence with steps
POST   /api/sequences                    - Create sequence
PUT    /api/sequences/:id                - Update sequence
DELETE /api/sequences/:id                - Delete sequence
POST   /api/sequences/:id/steps          - Add step
PUT    /api/sequences/:id/steps/:stepId  - Update step
DELETE /api/sequences/:id/steps/:stepId  - Delete step
POST   /api/sequences/:id/enroll         - Enroll leads
POST   /api/sequences/:id/pause          - Pause sequence
POST   /api/sequences/:id/resume         - Resume sequence
GET    /api/sequences/:id/stats          - Get stats
```

#### Step 2.3: Sequence Processor Job
**File:** `src/jobs/sequenceProcessor.js`
**Time:** 2-3 hours

```javascript
// Runs every 60 seconds (like scheduledSms.js)
async function processSequenceSteps() {
  // 1. Get active enrollments where next_step_at <= NOW
  // 2. For each enrollment:
  //    a. Get current step
  //    b. Execute action (send email)
  //    c. Update enrollment (increment step, set next_step_at)
  //    d. If last step, mark completed
  // 3. Log results
}
```

#### Step 2.4: Enrollment Logic
**Time:** 1 hour

```javascript
async function enrollLead(sequenceId, leadId) {
  // Check not already enrolled
  // Check lead has email
  // Check lead not opted out
  // Create enrollment
  // Schedule first step
}

async function unenrollLead(enrollmentId, reason) {
  // Mark enrollment as stopped
  // Cancel pending steps
  // Log reason
}
```

---

### Phase 3: Tracking & Webhooks (3-4 hours)

#### Step 3.1: SendGrid Webhook Handler
**File:** `src/routes/webhooks.js` (add to existing)
**Time:** 1.5 hours

```javascript
// POST /api/webhooks/sendgrid
router.post('/sendgrid', async (req, res) => {
  // Verify SendGrid signature
  // Process events:
  //   - delivered: Update communication status
  //   - open: Log open, update stats
  //   - click: Log click, track URL
  //   - bounce: Mark lead, pause sequence
  //   - unsubscribe: Opt out lead
  //   - spam_report: Opt out lead
});
```

#### Step 3.2: Update Communications for Email Events
**Time:** 1 hour

```sql
-- Add email tracking fields to communications metadata
-- metadata: {
--   email_provider: 'sendgrid',
--   message_id: 'xxx',
--   opened_at: timestamp,
--   open_count: number,
--   clicked_at: timestamp,
--   clicked_links: ['url1', 'url2'],
--   bounced: boolean,
--   bounce_type: 'hard'|'soft'
-- }
```

#### Step 3.3: Reply Detection
**Time:** 1 hour

```javascript
// When email reply received (via SendGrid inbound parse):
// 1. Match to lead by email
// 2. Check if in sequence
// 3. If in sequence, pause/complete based on config
// 4. Create inbound communication
// 5. Notify via Slack
```

---

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

#### Step 4.1: Email Templates Page
**File:** `admin/src/pages/EmailTemplates.jsx`
**Time:** 1.5 hours

- List templates with stats (open rate, click rate)
- Create/edit template modal
- Preview with sample lead data
- Variable picker
- HTML + plain text editor

#### Step 4.2: Sequence Builder Page
**File:** `admin/src/pages/SequenceBuilder.jsx`
**Time:** 2 hours

- Drag-drop step ordering
- Add email step (select template)
- Set delay (hours/days)
- Add conditions (optional, future)
- Preview full sequence
- Enrollment stats

#### Step 4.3: Lead Profile - Email Tab
**File:** `admin/src/pages/LeadProfile.jsx` (modify)
**Time:** 1 hour

- Show email history
- Current sequence enrollment
- Manual email send
- Opt-out toggle

#### Step 4.4: Dashboard Integration
**Time:** 30 min

- Add "Emails Sent Today" to dashboard
- Add sequence stats widget

---

## Database Schema Summary

```
email_templates
├── id, slug, name, subject, preview_text
├── html_content, text_content, variables
├── category, is_active, usage_count
└── open_rate, click_rate, timestamps

sequences (existing, extend)
├── id, name, description, trigger_type
├── channel ('email', 'sms', 'mixed')
└── is_active, timestamps

sequence_steps (existing, extend)
├── id, sequence_id, step_order
├── action_type ('send_email', 'wait', 'condition')
├── email_template_id, sms_template_id
├── delay_hours, config
└── timestamps

sequence_enrollments (existing)
├── id, sequence_id, lead_id
├── current_step, status, next_step_at
└── enrolled_at, completed_at, paused_at

sequence_stats
├── sequence_id, period_start
├── enrolled, completed, replied
└── converted, bounced, unsubscribed

communications (existing, use type='email')
├── metadata.email_provider, message_id
├── metadata.opened_at, clicked_at
└── metadata.bounce_type
```

---

## API Endpoints Summary

| Endpoint | Method | Purpose |
|----------|--------|---------|
| `/api/email/templates` | GET | List templates |
| `/api/email/templates` | POST | Create template |
| `/api/email/templates/:id` | PUT | Update template |
| `/api/email/templates/:id` | DELETE | Delete template |
| `/api/email/send` | POST | Send single email |
| `/api/email/send-batch` | POST | Send batch emails |
| `/api/sequences` | GET | List sequences |
| `/api/sequences` | POST | Create sequence |
| `/api/sequences/:id` | GET | Get sequence |
| `/api/sequences/:id` | PUT | Update sequence |
| `/api/sequences/:id/steps` | POST | Add step |
| `/api/sequences/:id/enroll` | POST | Enroll leads |
| `/api/sequences/:id/stats` | GET | Get stats |
| `/api/webhooks/sendgrid` | POST | Webhook handler |

---

## Environment Variables

```
SENDGRID_API_KEY=SG.xxx
SENDGRID_FROM_EMAIL=team@rateright.com.au
SENDGRID_FROM_NAME=RateRight Team
SENDGRID_WEBHOOK_KEY=xxx (for signature verification)
```

---

## Seed Data

### Default Email Templates

1. **Initial Outreach**
   - Subject: "Quick question about {{company}}"
   - Body: Intro, value prop, soft CTA

2. **Follow-up #1**
   - Subject: "Re: Quick question about {{company}}"
   - Body: Bump, add value, ask for call

3. **Follow-up #2**
   - Subject: "{{first_name}}, one more thing"
   - Body: Different angle, social proof

4. **Break-up Email**
   - Subject: "Should I close your file?"
   - Body: Last attempt, fear of loss

### Default Sequence

**"New Lead Outreach"**
1. Day 0: Initial Outreach
2. Day 3: Follow-up #1
3. Day 7: Follow-up #2
4. Day 14: Break-up Email

---

## Testing Checklist

- [ ] Send single email to test address
- [ ] Send batch emails (5 leads)
- [ ] Create template with variables
- [ ] Template preview renders correctly
- [ ] Create sequence with 3 steps
- [ ] Enroll lead in sequence
- [ ] Sequence processor sends step 1
- [ ] Delay works correctly (step 2 after X hours)
- [ ] Open tracking works (webhook received)
- [ ] Click tracking works
- [ ] Bounce handling (lead marked, sequence paused)
- [ ] Reply detection (sequence paused)
- [ ] Manual unenroll works
- [ ] Stats update correctly

---

## Risks & Mitigations

| Risk | Impact | Mitigation |
|------|--------|------------|
| SendGrid deliverability | High | Warm up domain, monitor reputation |
| Webhook failures | Medium | Retry logic, dead letter queue |
| Spam complaints | High | Easy unsubscribe, honor opt-outs |
| Rate limiting | Medium | Batch with delays, respect limits |
| Email content quality | Medium | Compliance checking (reuse brand guidelines) |

---

## Success Metrics

| Metric | Target |
|--------|--------|
| Sequence completion rate | >60% |
| Open rate | >25% |
| Reply rate | >5% |
| Bounce rate | <2% |
| Unsubscribe rate | <1% |

---

## Implementation Order

1. [x] Investigation complete
2. [ ] Step 1.1: Email service (SendGrid)
3. [ ] Step 1.2: Email templates migration
4. [ ] Step 1.3: Email routes (CRUD + send)
5. [ ] Step 1.4: Register routes
6. [ ] Step 2.1: Sequences migration
7. [ ] Step 2.2: Sequences API
8. [ ] Step 2.3: Sequence processor job
9. [ ] Step 2.4: Enrollment logic
10. [ ] Step 3.1: SendGrid webhooks
11. [ ] Step 3.2: Email tracking
12. [ ] Step 3.3: Reply detection
13. [ ] Step 4.1: Email templates UI
14. [ ] Step 4.2: Sequence builder UI
15. [ ] Step 4.3: Lead profile email tab
16. [ ] Step 4.4: Dashboard integration
17. [ ] Seed templates + sequence
18. [ ] End-to-end testing

---

## Estimated Time

| Phase | Hours |
|-------|-------|
| Phase 1: Foundation | 5-6 |
| Phase 2: Sequences | 6-8 |
| Phase 3: Tracking | 3-4 |
| Phase 4: Frontend | 4-5 |
| **Total** | **18-23 hours** |

---

*Plan created: Jan 18, 2026*