1  -- Migration 070: Auto-maintain outreaches.updated_at via trigger
 2  --
 3  -- Bug: outreaches.updated_at is NEVER set by any UPDATE statement.
 4  -- All delta queries in buildOutreachTree that use updated_at return 0 for every
 5  -- status (approved, rework, failed, gdpr_blocked, gov_blocked, no_message_button).
 6  --
 7  -- Fix: SQLite AFTER UPDATE trigger sets updated_at on every row change.
 8  -- This covers all 12+ callsites (sms.js, email.js, form.js, x.js, linkedin.js,
 9  -- proposal-generator-v2.js, sheets-export.js) without touching each file.
10  --
11  -- Also backfills existing records using the best available timestamp:
12  --   - sent/bounced/delivered/opened/clicked/replied → delivered_at (most accurate)
13  --   - failed → created_at (best available; sent_at = created_at due to DEFAULT)
14  --   - all others (approved, rework, pending, gdpr_blocked etc.) → created_at
15  
16  -- Trigger fires only when status changes (not on every update).
17  -- This keeps updated_at semantically clean: it means "when this outreach
18  -- last changed status", not "when any column was last touched".
19  -- WHEN clause prevents self-trigger loops and noise from exported_at/our_account updates.
20  CREATE TRIGGER IF NOT EXISTS outreaches_updated_at
21  AFTER UPDATE OF status ON outreaches
22  FOR EACH ROW
23  WHEN NEW.status != OLD.status
24  BEGIN
25    UPDATE outreaches SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
26  END;
27  
28  -- Backfill: set updated_at on existing records that have none
29  UPDATE outreaches
30  SET updated_at = CASE
31    WHEN status IN ('sent', 'delivered', 'bounced', 'opened', 'clicked', 'replied')
32         AND delivered_at IS NOT NULL
33      THEN delivered_at
34    ELSE created_at
35  END
36  WHERE updated_at IS NULL;