1  -- Migration 105: Email exclusion list
 2  -- Date: 2026-03-15
 3  -- Purpose: Track email addresses that appear across 10+ distinct sites (likely scraped noise)
 4  --          so they can be stripped from contacts_json and blocked from outreach.
 5  
 6  CREATE TABLE IF NOT EXISTS email_exclusion_list (
 7    email       TEXT PRIMARY KEY,
 8    reason      TEXT NOT NULL,
 9    site_count  INTEGER NOT NULL,  -- how many sites had this address when added
10    added_at    TEXT NOT NULL DEFAULT (datetime('now')),
11    added_by    TEXT NOT NULL DEFAULT 'cron'
12  );
13  
14  -- Weekly email exclusion audit cron job
15  INSERT OR IGNORE INTO cron_jobs (
16      name,
17      task_key,
18      description,
19      handler_type,
20      handler_value,
21      interval_value,
22      interval_unit,
23      enabled
24  ) VALUES (
25      '10.3 Email Exclusion Audit',
26      'emailExclusionAudit',
27      'Detect email addresses appearing across 10+ sites (scraped noise), add to exclusion list, strip from contacts_json, and mark queued email messages as failed',
28      'command',
29      'node src/cron/email-exclusion-audit.js',
30      7,
31      'days',
32      1
33  );