1  -- Migration: Create unsubscribed_emails table
 2  -- Purpose: Track globally unsubscribed email addresses
 3  -- Date: 2026-01-26
 4  
 5  -- Global unsubscribe list (blocks all future emails to these addresses)
 6  CREATE TABLE IF NOT EXISTS unsubscribed_emails (
 7      id INTEGER PRIMARY KEY AUTOINCREMENT,
 8      email TEXT NOT NULL UNIQUE COLLATE NOCASE,
 9      outreach_id INTEGER REFERENCES outreaches(id),
10      unsubscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
11      source TEXT DEFAULT 'web' CHECK(source IN ('web', 'manual', 'bounce', 'complaint')),
12      created_at DATETIME DEFAULT CURRENT_TIMESTAMP
13  );
14  
15  -- Index for fast email lookups
16  CREATE INDEX IF NOT EXISTS idx_unsubscribed_emails_email ON unsubscribed_emails(email COLLATE NOCASE);
17  CREATE INDEX IF NOT EXISTS idx_unsubscribed_emails_date ON unsubscribed_emails(unsubscribed_at);
18  
19  -- Insert any existing unsubscribed outreaches into the global table
20  INSERT OR IGNORE INTO unsubscribed_emails (email, outreach_id, source, unsubscribed_at)
21  SELECT
22      contact_uri,
23      id,
24      'manual',
25      CURRENT_TIMESTAMP
26  FROM outreaches
27  WHERE unsubscribed = 1
28    AND contact_method = 'email'
29    AND contact_uri != 'PENDING_CONTACT_EXTRACTION';