1  -- Migration 037: Add Missing Outreach Statuses
 2  -- Created: 2026-02-10
 3  -- Purpose: Add 'rework' and 'gdpr_blocked' statuses to outreaches table CHECK constraint
 4  --
 5  -- Background:
 6  -- - proposal-generator-v2.js uses 'rework' status but it was never added to schema
 7  -- - Migration 028 added 'gdpr_blocked' but was never run
 8  -- - Need to consolidate all valid statuses in one migration
 9  
10  -- SQLite doesn't support ALTER TABLE ... ALTER COLUMN to modify CHECK constraints
11  -- We need to recreate the table with the new constraint
12  
13  -- Step 1: Temporarily disable foreign key constraints during migration
14  PRAGMA foreign_keys = OFF;
15  
16  -- Step 2: Create new table with updated CHECK constraint
17  CREATE TABLE outreaches_new (
18      id INTEGER PRIMARY KEY AUTOINCREMENT,
19      site_id INTEGER NOT NULL,
20      variant_number INTEGER,  -- No CHECK constraint - allows any variant number
21      contact_method TEXT NOT NULL CHECK(contact_method IN (
22          'sms', 'email', 'form', 'x', 'linkedin'
23      )),
24      contact_uri TEXT NOT NULL,
25      our_account TEXT,
26      proposal_text TEXT NOT NULL,
27      status TEXT DEFAULT 'pending' CHECK(status IN (
28          'pending', 'approved', 'rework', 'sent', 'delivered', 'failed',
29          'bounced', 'opened', 'clicked', 'replied', 'scheduled',
30          'gdpr_blocked', 'gov_blocked', 'no_message_button'
31      )),
32      error_message TEXT,
33      rework_instructions TEXT,
34      sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
35      delivered_at TIMESTAMP,
36      resulted_in_sale BOOLEAN DEFAULT 0,
37      sale_amount DECIMAL(10, 2),
38      tracking_clicked_at DATETIME,
39      opened_at DATETIME,
40      email_id TEXT,
41      unsubscribed BOOLEAN DEFAULT 0,
42      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
43      subject_line TEXT,
44      exported_at DATETIME DEFAULT NULL,
45      FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
46      UNIQUE(site_id, contact_method, contact_uri)
47  );
48  
49  -- Step 3: Copy data from old table
50  INSERT INTO outreaches_new (
51      id, site_id, variant_number, contact_method, contact_uri, our_account,
52      proposal_text, status, error_message, rework_instructions, sent_at,
53      delivered_at, resulted_in_sale, sale_amount, tracking_clicked_at,
54      opened_at, email_id, unsubscribed, created_at, subject_line, exported_at
55  )
56  SELECT
57      id, site_id, variant_number, contact_method, contact_uri, our_account,
58      proposal_text, status, error_message, rework_instructions, sent_at,
59      delivered_at, resulted_in_sale, sale_amount, tracking_clicked_at,
60      opened_at, email_id, unsubscribed, created_at, subject_line, exported_at
61  FROM outreaches;
62  
63  -- Step 4: Drop old table
64  DROP TABLE outreaches;
65  
66  -- Step 5: Rename new table
67  ALTER TABLE outreaches_new RENAME TO outreaches;
68  
69  -- Step 6: Re-enable foreign key constraints
70  PRAGMA foreign_keys = ON;
71  
72  -- Step 7: Recreate indexes
73  CREATE INDEX IF NOT EXISTS idx_outreaches_site_id ON outreaches(site_id);
74  CREATE INDEX IF NOT EXISTS idx_outreaches_status ON outreaches(status);
75  CREATE INDEX IF NOT EXISTS idx_outreaches_contact_method ON outreaches(contact_method);
76  
77  -- Step 8: Create indexes for specific statuses
78  CREATE INDEX IF NOT EXISTS idx_outreaches_gdpr_blocked ON outreaches(status) WHERE status = 'gdpr_blocked';
79  CREATE INDEX IF NOT EXISTS idx_outreaches_rework ON outreaches(status) WHERE status = 'rework';
80  CREATE INDEX IF NOT EXISTS idx_outreaches_pending ON outreaches(status) WHERE status = 'pending';
81  CREATE INDEX IF NOT EXISTS idx_outreaches_exported_at ON outreaches(exported_at) WHERE exported_at IS NULL;
82  
83  -- Documentation:
84  -- Valid statuses:
85  -- - pending: Awaiting operator approval
86  -- - approved: Approved but not yet sent
87  -- - rework: Marked for regeneration with feedback (used by proposal-generator-v2.js)
88  -- - sent: Successfully sent to recipient
89  -- - delivered: Confirmed delivery (email webhooks)
90  -- - failed: Delivery failed
91  -- - bounced: Email bounced
92  -- - opened: Email opened (tracking)
93  -- - clicked: Link clicked (tracking)
94  -- - replied: Recipient replied
95  -- - scheduled: Scheduled for future delivery
96  -- - gdpr_blocked: GDPR compliance blocked sending (unverified company email in GDPR country)
97  -- - no_message_button: X/LinkedIn profile has no DM button