1  -- Migration 104: Phone exclusion list
 2  -- Date: 2026-03-15
 3  -- Purpose: Track phone numbers that appear across too many sites (scraped noise)
 4  --          so they can be stripped from contacts_json and blocked from outreach.
 5  
 6  CREATE TABLE IF NOT EXISTS phone_exclusion_list (
 7    phone TEXT PRIMARY KEY,
 8    reason TEXT NOT NULL,
 9    site_count INTEGER NOT NULL,  -- how many sites had this number when added
10    added_at TEXT NOT NULL DEFAULT (datetime('now')),
11    added_by TEXT NOT NULL DEFAULT 'cron'
12  );
13  
14  -- Weekly phone 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.2 Phone Exclusion Audit',
26      'phoneExclusionAudit',
27      'Detect phone numbers appearing across 10+ sites (scraped noise), add to exclusion list, strip from contacts_json, and mark queued SMS messages as failed',
28      'command',
29      'node src/cron/phone-exclusion-audit.js',
30      7,
31      'days',
32      1
33  );