# SMS Scheduler Investigation & Fix Plan

**Created:** Jan 21, 2026
**Priority:** Blocking (Monday callers need SMS sequences)
**Root Cause:** RLS blocking background job database reads

---

## Problem Statement

Scheduled SMS messages and automated sequences are not being sent. The background jobs run but find no messages to process.

---

## Investigation Summary

### Two SMS Scheduling Systems

| System | Table | Processor | Status |
|--------|-------|-----------|--------|
| User-scheduled SMS | `communications` | `scheduledSms.js` | Likely working (no RLS) |
| Sequence automation | `sequence_enrollments` | `sequenceProcessor.js` | **BROKEN (RLS blocks)** |

### Root Cause: RLS Blocking Anon Key

The `sequenceProcessor.js` job uses the anon key client:

```javascript
// src/jobs/sequenceProcessor.js:16
const { supabase } = require('../config/database');
```

But the sequence tables have RLS policies that only allow **authenticated** users:

```sql
-- supabase/migrations/20260119113451_sequence_automation.sql:48
CREATE POLICY "Authenticated users can manage sequences"
  ON sequences FOR ALL TO authenticated  -- Only authenticated, NOT anon!
  USING (true) WITH CHECK (true);
```

**Result:** When the job queries `sequence_enrollments`, RLS blocks the anon key and returns **empty results** (not an error). The job thinks there are no enrollments to process and exits silently.

### Tables With RLS (Blocking Jobs)

| Table | RLS Policy | Anon Access |
|-------|------------|-------------|
| `sequences` | authenticated only | **BLOCKED** |
| `sequence_steps` | authenticated only | **BLOCKED** |
| `sequence_enrollments` | authenticated only | **BLOCKED** |

### Tables Without RLS (Working)

| Table | RLS | Status |
|-------|-----|--------|
| `communications` | No RLS | Works |
| `leads` | No RLS | Works |
| `callbacks` | No RLS | Works |

---

## Why This Wasn't Caught

1. **No errors thrown** - RLS returns empty results, not errors
2. **Job logs "0 processed"** - Looks normal, not like a failure
3. **Routes use admin client** - `sequences.js` uses `supabaseAdmin || supabase`, so API works
4. **Jobs don't use admin** - Jobs were written before RLS was added

From LESSONS.md line 111:
> "RLS blocks backend reads AND writes... ALWAYS use `supabaseAdmin || supabase` pattern for any table with RLS."

---

## Solution

### Fix 1: Update sequenceProcessor.js to use admin client (Critical)

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

**Current:**
```javascript
const { supabase } = require('../config/database');
```

**Fixed:**
```javascript
const { supabase, supabaseAdmin } = require('../config/database');

// Use at top of processSequences():
const db = supabaseAdmin || supabase;
```

Then replace all `supabase.` calls with `db.` in the file.

### Fix 2: Update scheduledSms.js for consistency (Safety)

**File:** `src/jobs/scheduledSms.js:6`

Apply same pattern even though `communications` doesn't have RLS yet. Future-proofing.

### Fix 3: Add startup processing for sequences

**File:** `src/jobs/index.js` after line 391

**Add:**
```javascript
// Process any pending sequence steps at startup
processSequences().catch(console.error);
```

### Fix 4: Phone normalization in sequenceProcessor.js

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

**Current:**
```javascript
const smsResult = await sendSMS(lead.phone, message);
```

**Fixed:**
```javascript
const { normalizeAustralianPhone } = require('../utils/phone');
// ...
const normalizedPhone = normalizeAustralianPhone(lead.phone) || lead.phone;
const smsResult = await sendSMS(normalizedPhone, message);
```

---

## Implementation Steps

- [x] **Step 1:** Update `sequenceProcessor.js` to use `supabaseAdmin || supabase`
- [x] **Step 2:** Update `scheduledSms.js` to use `supabaseAdmin || supabase`
- [x] **Step 3:** Add phone normalization to `sequenceProcessor.js`
- [x] **Step 4:** Add sequence startup processing in `index.js`
- [ ] **Step 5:** Test locally by running jobs manually
- [ ] **Step 6:** Deploy and verify in production

---

## Verification Steps (QA)

1. **Check Railway logs** for `[SequenceProcessor]` messages
2. **Verify enrollments exist:**
   ```sql
   SELECT COUNT(*) FROM sequence_enrollments WHERE status = 'active';
   ```
3. **Verify job finds them** - should log "Processing X due enrollments"
4. **Check SMS sent** - verify in Twilio dashboard
5. **Check communications table** - new records with `metadata.sent_via: 'sequence_automation'`

---

## Rollback Plan

If issues occur:
1. Revert the commit
2. Jobs will continue running but finding 0 results (current state)
3. No destructive changes - safe to revert

---

## Notes for Builder

- `supabaseAdmin` may be undefined if `SUPABASE_SERVICE_ROLE_KEY` not set
- Always use `supabaseAdmin || supabase` pattern, never just `supabaseAdmin`
- The pattern is used in 23 other files already - follow existing examples

---

## Notes for QA

- Test during AEST business hours (8am-6pm) - sequence processor skips outside hours
- Test with a real enrollment - create one via API or UI
- Watch Railway logs for `[SequenceProcessor]` output
- Verify phone normalization works with `0412...` format phones

---

## Risk Assessment

| Risk | Probability | Impact | Mitigation |
|------|-------------|--------|------------|
| Admin key not configured | Low | High | Fallback to anon key |
| Job processes too many | Low | Medium | Limit of 20 per run |
| Phone normalization breaks | Low | Low | Fallback to original |

---

**Estimated effort:** 45 mins
**Ready for Builder:** Yes