# 🚨 URGENT: Database Migration Required
## Job Locking System - Prevents Duplicate SMS

**Created:** February 2, 2026  
**Priority:** P0 (Critical)  
**Status:** ⚠️ **AWAITING MIGRATION**

---

## 📋 Problem Summary

The job duplication fix has been implemented in code but requires a database migration to complete.

### What's Been Done:
- ✅ **Code implemented:** `src/services/jobLocking.js`
- ✅ **Integration complete:** `src/jobs/index.js` uses `runJobWithLock`
- ✅ **Redundant job disabled:** `scheduledSms.js` removed from execution
- ✅ **Migration file created:** `supabase/migrations/20260202130000_create_job_locks_table.sql`
- ❌ **Database table not created:** `job_locks` table missing

### Current Risk:
Without the `job_locks` table, the job locking system will fail silently. Jobs will still run (fallback behavior) but without proper locking coordination.

---

## 🔧 Technical Details

### The Fix:
Two jobs were sending duplicate SMS messages:
1. `sequenceProcessor.js` - Every minute (processes sequences)
2. `scheduledSms.js` - Every minute (redundant - now disabled)

### Solution Implemented:
1. **Job Locking Service:** Prevents duplicate execution of the same job
2. **Single Source:** Only `sequenceProcessor.js` runs (handles both sequences and scheduled messages)
3. **Database Coordination:** `job_locks` table tracks running jobs

### Migration SQL Required:
```sql
-- Create job_locks table to prevent duplicate job execution
CREATE TABLE IF NOT EXISTS job_locks (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  job_name TEXT NOT NULL UNIQUE,
  lock_id TEXT NOT NULL,
  acquired_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL,
  status TEXT NOT NULL DEFAULT 'acquired',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_job_locks_job_name ON job_locks(job_name);
CREATE INDEX IF NOT EXISTS idx_job_locks_expires_at ON job_locks(expires_at);

-- Add RLS policies
ALTER TABLE job_locks ENABLE ROW LEVEL SECURITY;

-- Only service role can manage job locks
CREATE POLICY "Service role only" ON job_locks
  USING (auth.role() = 'service_role');
```

---

## 🚀 Action Required

### Step 1: Run Migration
1. Login to [Supabase Dashboard](https://supabase.com/dashboard)
2. Navigate to: **Project → SQL Editor**
3. Paste the SQL above
4. Click **Run**

### Step 2: Verify
1. Check **Tables** list for `job_locks`
2. Verify table structure matches migration
3. Check application logs for successful lock acquisition

### Step 3: Monitor
1. Watch for `[JobLocking] Acquired lock for "sequence_processor"` in logs
2. Ensure no duplicate SMS are sent
3. Verify job coordination is working

---

## 📊 Impact Assessment

### Without Migration:
- **Risk Level:** Medium
- **Immediate Impact:** Job locking fails silently, jobs run without coordination
- **Long-term Risk:** Potential duplicate SMS if code changes reintroduce redundancy
- **Fallback:** Jobs still execute, just without locking

### With Migration:
- **Risk Level:** Low
- **Benefit:** Guaranteed no duplicate job execution
- **System Integrity:** Proper job coordination
- **Future-proof:** Prevents similar issues in other jobs

---

## 🔍 Verification Steps

After migration, check:

### In Logs:
```bash
# Should see:
[JobLocking] Acquired lock for "sequence_processor" (lockId: job_lock_sequence_processor_...)
[JobLocking] Released lock for "sequence_processor"

# Should NOT see:
error: relation "job_locks" does not exist
```

### In Database:
```sql
-- Verify table exists
SELECT * FROM job_locks LIMIT 5;

-- Check lock activity
SELECT job_name, acquired_at, expires_at, status 
FROM job_locks 
ORDER BY acquired_at DESC 
LIMIT 10;
```

### In Application:
- No duplicate SMS messages
- Sequence processor runs every minute
- No errors in job execution

---

## 📝 Related Files

### Code Changes:
- `src/services/jobLocking.js` - New job locking service
- `src/jobs/index.js` - Updated to use job locking
- `src/jobs/sequenceProcessor.js` - Now handles both sequences and scheduled messages

### Migration File:
- `supabase/migrations/20260202130000_create_job_locks_table.sql`

### Documentation:
- `docs/JOB-CONSOLIDATION-PLAN.md` - Full consolidation plan
- `rivet/knowledge/urgent-job-fix.md` - Rivet knowledge base

---

## ⏰ Timeline

- **Issue Identified:** February 2, 2026
- **Code Fix Deployed:** February 2, 2026 (13:20 UTC)
- **Migration Required:** **URGENT** - As soon as possible
- **Verification:** Immediately after migration

---

## 🆘 Support

If migration fails or issues arise:

1. **Check Supabase logs** for SQL errors
2. **Verify service role key** has proper permissions
3. **Test manually** with simple INSERT query
4. **Contact:** System will auto-alert via Telegram if job locking fails repeatedly

---

**Status:** ⚠️ **AWAITING MIGRATION** - Critical system integrity depends on this migration.

**Next Review:** After migration completion