`scripts/migrate_013_wa_auto_reply_cooldown.sql`

23 lines
-- migrate_013_wa_auto_reply_cooldown.sql — WhatsApp auto-reply cost guard.
--
-- BUG (cold audit, cycle 30): the inbound WhatsApp auto-reply (routes/webhooks.js)
-- fires one Meta-billed sendButtons per inbound keyword message with NO rate limit —
-- anyone who can WhatsApp the business number can drive unbounded, real-money outbound
-- sends. This column backs a per-contact cooldown: the route's auto-reply loop does an
-- atomic conditional UPDATE (SET last_auto_reply_at=NOW() WHERE it's null or older than
-- the window) that both checks AND claims the slot — no TOCTOU, durable across restarts
-- (an in-process Map would reset on deploy + not hold in cluster mode).
--
-- SECURITY NOTE (documented per the cycle-30 audit, no code change): whatsapp_contacts
-- .profile_name and whatsapp_messages.body are persisted RAW from attacker-controlled
-- inbound payloads (a WhatsApp display name / message text is settable to anything,
-- incl. `<img src=x onerror=...>`). No admin surface renders them today, but the FIRST
-- one that does MUST escape (textContent / esc()), or it recreates the cycle-24 stored
-- XSS. Likewise whatsapp_contacts.user_id is an UNUSED FK — wiring "link my WhatsApp to
-- my account" by bare wa_id/phone equality (no OTP/consent) would create real
-- impersonation; require a verification step.
--
-- PROD-APPLY: additive nullable column, safe online (brief lock on a small table); no
-- backfill needed (NULL = never auto-replied = eligible).
ALTER TABLE whatsapp_contacts ADD COLUMN IF NOT EXISTS last_auto_reply_at TIMESTAMPTZ;