087-omnichannel-messages.sql
1 -- Migration 087: Omnichannel Messages 2 -- Merge outreaches + conversations into a unified messages table. 3 -- Split outreaches.status into messages.approval_status + messages.delivery_status. 4 -- Add sites.conversation_status for business lifecycle tracking. 5 -- Add sites.resulted_in_sale + sites.sale_amount (moved from outreaches). 6 7 -- ────────────────────────────────────────────────────────────────────────────── 8 -- Step 1: Add new columns to sites table 9 -- ────────────────────────────────────────────────────────────────────────────── 10 11 ALTER TABLE sites ADD COLUMN conversation_status TEXT DEFAULT NULL CHECK(conversation_status IN ( 12 'draft', 13 'active', 14 'qualified', 15 'payment_requested', 16 'paid', 17 'report_delivered', 18 'not_interested', 19 'closed', 20 'unsubscribed' 21 )); 22 ALTER TABLE sites ADD COLUMN resulted_in_sale INTEGER DEFAULT 0; 23 ALTER TABLE sites ADD COLUMN sale_amount REAL DEFAULT 0; 24 25 CREATE INDEX IF NOT EXISTS idx_sites_conversation_status ON sites(conversation_status) WHERE conversation_status IS NOT NULL; 26 27 -- ────────────────────────────────────────────────────────────────────────────── 28 -- Step 2: Create messages table 29 -- ────────────────────────────────────────────────────────────────────────────── 30 31 CREATE TABLE IF NOT EXISTS messages ( 32 id INTEGER PRIMARY KEY AUTOINCREMENT, 33 site_id INTEGER NOT NULL REFERENCES sites(id) ON DELETE CASCADE, 34 direction TEXT NOT NULL CHECK(direction IN ('inbound', 'outbound')), 35 36 -- Per-message channel info (omnichannel — can vary per message) 37 contact_method TEXT NOT NULL CHECK(contact_method IN ('sms', 'email', 'form', 'x', 'linkedin')), 38 contact_uri TEXT NOT NULL, 39 40 message_body TEXT, 41 subject_line TEXT, 42 43 -- Outbound: approval workflow (same for proposals AND follow-up replies) 44 approval_status TEXT CHECK(approval_status IN ( 45 'pending', 'approved', 'rework', 'rejected', 'gdpr_blocked' 46 )), 47 rework_instructions TEXT, 48 exported_at TEXT, 49 50 -- Outbound: delivery tracking 51 delivery_status TEXT CHECK(delivery_status IN ( 52 'queued', 'sent', 'delivered', 'failed', 'bounced', 'retry_later' 53 )), 54 error_message TEXT, 55 retry_at TEXT, 56 sent_at TEXT, 57 delivered_at TEXT, 58 email_id TEXT, 59 60 -- Engagement tracking (outbound) 61 opened_at TEXT, 62 tracking_clicked_at TEXT, 63 64 -- Inbound: classification 65 sentiment TEXT CHECK(sentiment IN ('positive', 'neutral', 'negative', 'objection')), 66 intent TEXT CHECK(intent IN ( 67 'inquiry', 'opt-out', 'interested', 'not-interested', 68 'pricing', 'schedule', 'unknown', 'autoresponder' 69 )), 70 71 -- Inbound: metadata 72 raw_payload TEXT, 73 is_read INTEGER DEFAULT 0, 74 read_at TEXT, 75 processed_at TEXT, 76 77 -- Payment (on specific messages that request/confirm payment) 78 payment_link TEXT, 79 payment_id TEXT, 80 payment_amount REAL, 81 payment_currency TEXT, 82 payment_amount_local INTEGER, 83 payment_amount_usd INTEGER, 84 exchange_rate REAL, 85 pricing_variant TEXT, 86 report_url TEXT, 87 88 -- Carried over from outreaches 89 our_account TEXT, 90 template_id TEXT, 91 zb_status TEXT, 92 93 created_at TEXT NOT NULL DEFAULT (datetime('now')), 94 updated_at TEXT NOT NULL DEFAULT (datetime('now')) 95 ); 96 97 -- Indexes 98 CREATE INDEX idx_messages_site_id ON messages(site_id); 99 CREATE INDEX idx_messages_direction ON messages(direction); 100 CREATE INDEX idx_messages_approval_status ON messages(approval_status) WHERE approval_status IS NOT NULL; 101 CREATE INDEX idx_messages_delivery_status ON messages(delivery_status) WHERE delivery_status IS NOT NULL; 102 CREATE INDEX idx_messages_email_id ON messages(email_id) WHERE email_id IS NOT NULL; 103 CREATE INDEX idx_messages_created_at ON messages(created_at); 104 CREATE INDEX idx_messages_intent ON messages(intent) WHERE intent IS NOT NULL; 105 CREATE INDEX idx_messages_contact_uri ON messages(contact_uri); 106 CREATE INDEX idx_messages_contact_method ON messages(contact_method); 107 CREATE INDEX idx_messages_sent_at ON messages(sent_at) WHERE sent_at IS NOT NULL; 108 CREATE INDEX idx_messages_updated_at ON messages(updated_at); 109 CREATE INDEX idx_messages_template_id ON messages(template_id) WHERE template_id IS NOT NULL; 110 CREATE INDEX idx_messages_exported_at ON messages(exported_at) WHERE exported_at IS NULL; 111 CREATE INDEX idx_messages_retry_at ON messages(retry_at) WHERE retry_at IS NOT NULL; 112 CREATE INDEX idx_messages_payment_id ON messages(payment_id) WHERE payment_id IS NOT NULL; 113 CREATE INDEX idx_messages_zb_status ON messages(zb_status) WHERE zb_status IS NOT NULL; 114 115 -- Dedup: prevent duplicate outbound proposals to same contact for same site 116 CREATE UNIQUE INDEX idx_messages_dedup 117 ON messages(site_id, contact_method, contact_uri) 118 WHERE direction = 'outbound' AND approval_status IN ('pending', 'approved'); 119 120 -- Auto-update updated_at on approval/delivery status change 121 CREATE TRIGGER messages_updated_at 122 AFTER UPDATE ON messages 123 FOR EACH ROW WHEN ( 124 COALESCE(NEW.approval_status, '') != COALESCE(OLD.approval_status, '') OR 125 COALESCE(NEW.delivery_status, '') != COALESCE(OLD.delivery_status, '') 126 ) 127 BEGIN 128 UPDATE messages SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id; 129 END; 130 131 -- ────────────────────────────────────────────────────────────────────────────── 132 -- Step 3: Migrate outreaches → messages (outbound) 133 -- ────────────────────────────────────────────────────────────────────────────── 134 135 INSERT INTO messages ( 136 site_id, direction, contact_method, contact_uri, message_body, subject_line, 137 approval_status, delivery_status, error_message, rework_instructions, exported_at, 138 retry_at, sent_at, delivered_at, email_id, opened_at, tracking_clicked_at, 139 our_account, template_id, zb_status, created_at 140 ) 141 SELECT 142 site_id, 143 'outbound', 144 contact_method, 145 contact_uri, 146 proposal_text, 147 subject_line, 148 -- Map old status -> approval_status 149 CASE 150 WHEN status = 'pending' THEN 'pending' 151 WHEN status IN ('approved', 'sent', 'delivered', 'opened', 'clicked', 'replied', 'scheduled') THEN 'approved' 152 WHEN status = 'rework' THEN 'rework' 153 WHEN status = 'gdpr_blocked' THEN 'gdpr_blocked' 154 WHEN status = 'gov_blocked' THEN 'rejected' 155 WHEN status = 'no_message_button' THEN NULL 156 ELSE 'approved' -- failed/bounced were approved before sending 157 END, 158 -- Map old status -> delivery_status 159 CASE 160 WHEN status IN ('sent', 'opened', 'clicked', 'replied') THEN 'sent' 161 WHEN status = 'delivered' THEN 'delivered' 162 WHEN status = 'failed' THEN 'failed' 163 WHEN status = 'bounced' THEN 'bounced' 164 WHEN status = 'no_message_button' THEN 'failed' 165 WHEN status = 'retry_later' THEN 'retry_later' 166 ELSE NULL 167 END, 168 -- error_message: add 'No DM button' for no_message_button 169 CASE WHEN status = 'no_message_button' THEN 'No DM button' ELSE error_message END, 170 rework_instructions, 171 exported_at, 172 retry_at, 173 -- sent_at: only set for actually-sent statuses (the column was confusingly used as creation time) 174 CASE WHEN status IN ('sent','delivered','failed','bounced','opened','clicked','replied') THEN sent_at ELSE NULL END, 175 delivered_at, 176 email_id, 177 opened_at, 178 tracking_clicked_at, 179 our_account, 180 template_id, 181 zb_status, 182 COALESCE(created_at, sent_at, datetime('now')) -- created_at with fallback 183 FROM outreaches; 184 185 -- ────────────────────────────────────────────────────────────────────────────── 186 -- Step 4: Migrate conversations → messages (inbound + outbound replies) 187 -- ────────────────────────────────────────────────────────────────────────────── 188 189 INSERT INTO messages ( 190 site_id, direction, contact_method, contact_uri, message_body, subject_line, 191 sentiment, intent, raw_payload, is_read, read_at, processed_at, 192 payment_link, payment_id, payment_amount, payment_currency, 193 payment_amount_local, payment_amount_usd, exchange_rate, pricing_variant, 194 report_url, created_at 195 ) 196 SELECT 197 COALESCE(c.site_id, o.site_id), 198 c.direction, 199 c.channel, 200 COALESCE(c.sender_identifier, ''), 201 c.message_body, 202 c.subject_line, 203 c.sentiment, 204 c.intent, 205 c.raw_payload, 206 c.is_read, 207 c.read_at, 208 c.processed_at, 209 c.payment_link, 210 c.payment_id, 211 c.payment_amount, 212 c.payment_currency, 213 c.payment_amount_local, 214 c.payment_amount_usd, 215 c.exchange_rate, 216 c.pricing_variant, 217 c.report_url, 218 COALESCE(c.received_at, c.created_at, datetime('now')) 219 FROM conversations c 220 LEFT JOIN outreaches o ON c.outreach_id = o.id 221 WHERE COALESCE(c.site_id, o.site_id) IS NOT NULL; 222 223 -- ────────────────────────────────────────────────────────────────────────────── 224 -- Step 5: Set sites.conversation_status from existing data 225 -- ────────────────────────────────────────────────────────────────────────────── 226 227 -- Sites with sent outreaches → 'active' 228 UPDATE sites SET conversation_status = 'active' 229 WHERE id IN ( 230 SELECT site_id FROM outreaches WHERE status IN ( 231 'sent', 'delivered', 'opened', 'clicked', 'replied', 232 'failed', 'bounced' 233 ) 234 ); 235 236 -- Sites with only pending/approved outreaches → 'draft' 237 UPDATE sites SET conversation_status = 'draft' 238 WHERE conversation_status IS NULL 239 AND id IN (SELECT site_id FROM outreaches WHERE status IN ('pending', 'approved', 'rework')); 240 241 -- Override with conversation lifecycle where applicable 242 UPDATE sites SET conversation_status = ( 243 SELECT CASE c.status 244 WHEN 'qualified' THEN 'qualified' 245 WHEN 'payment_requested' THEN 'payment_requested' 246 WHEN 'paid' THEN 'paid' 247 WHEN 'report_delivered' THEN 'report_delivered' 248 WHEN 'not_interested' THEN 'not_interested' 249 WHEN 'closed' THEN 'closed' 250 ELSE sites.conversation_status 251 END 252 FROM conversations c 253 JOIN outreaches o ON c.outreach_id = o.id 254 WHERE o.site_id = sites.id 255 ORDER BY c.id DESC LIMIT 1 256 ) 257 WHERE id IN (SELECT o.site_id FROM conversations c JOIN outreaches o ON c.outreach_id = o.id); 258 259 -- Migrate sale data from outreaches to sites 260 UPDATE sites SET 261 resulted_in_sale = (SELECT MAX(resulted_in_sale) FROM outreaches WHERE site_id = sites.id), 262 sale_amount = (SELECT SUM(sale_amount) FROM outreaches WHERE site_id = sites.id AND sale_amount > 0) 263 WHERE id IN (SELECT site_id FROM outreaches WHERE resulted_in_sale = 1); 264 265 -- ────────────────────────────────────────────────────────────────────────────── 266 -- Step 6: Update prompt_feedback FK (outreach_id → message_id) 267 -- ────────────────────────────────────────────────────────────────────────────── 268 269 -- SQLite doesn't support RENAME COLUMN in older versions, but better-sqlite3 does. 270 -- Historical feedback keeps outreach_id as-is (IDs won't match new message IDs). 271 -- New feedback will use site_id which already exists on prompt_feedback. 272 273 -- ────────────────────────────────────────────────────────────────────────────── 274 -- Step 7: Update purchases FK (conversation_id → message_id) 275 -- ────────────────────────────────────────────────────────────────────────────── 276 277 -- purchases.conversation_id references conversations(id) — these IDs won't map to messages. 278 -- We keep the column but it becomes stale. New purchases will use site_id directly. 279 280 -- ────────────────────────────────────────────────────────────────────────────── 281 -- Step 8: Rename old tables (keep as backup, drop in follow-up migration) 282 -- ────────────────────────────────────────────────────────────────────────────── 283 284 ALTER TABLE outreaches RENAME TO outreaches_old; 285 ALTER TABLE conversations RENAME TO conversations_old;