1  -- Migration 110: unique index for scan_optin messages
2  -- Prevents duplicate inbound nurture records when poll-free-scans runs repeatedly.
3  -- Only applies to scan_optin message_type — other types are unaffected.
4  
5  CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_scan_optin_dedup
6      ON messages (site_id, contact_uri, message_type)
7      WHERE message_type = 'scan_optin';