`migrations/017_lost_pet_alerts.sql`

45 lines
-- Project: Animals — lost-pet alert opt-in + dispatch dedupe.
--
-- Wires src/lib/lost_pet_alert.js → fires when a marketplace listing of
-- listing_type='lost_pet' is posted. Recipients are app_users who:
--   1. Set alert_lost_pets = TRUE (must opt in — default FALSE)
--   2. Have a home_zip matching listing.zip OR same ZIP-3 prefix
--      (sectional center facility — typically dozens of cities)
--   3. Have a verified email and have not unsubscribed entirely
--
-- SMS — alert_lost_pets_sms exists for future TCPA-compliant double opt-in
-- flow, but no sender code references it. Per Steve's standing rule: no SMS
-- without an explicit opt-in event we can prove (timestamp + source + IP).
--
-- Dedupe: lost_pet_alerts_sent is a (listing_id, app_user_id, channel) record
-- so re-bumping a listing does not re-blast the neighborhood.

BEGIN;

ALTER TABLE app_users
  ADD COLUMN IF NOT EXISTS alert_lost_pets      BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS alert_lost_pets_sms  BOOLEAN NOT NULL DEFAULT FALSE;

-- Same ZIP: idx_app_users_zip (from migration 003) already covers home_zip = $1.
-- ZIP-3 prefix lookup wants a partial expression index so we don't full-scan
-- app_users every time someone posts a lost pet.
CREATE INDEX IF NOT EXISTS idx_app_users_zip3_alerts
  ON app_users (LEFT(home_zip, 3))
  WHERE alert_lost_pets = TRUE AND home_zip IS NOT NULL;

-- Per-recipient send ledger. Channel column lets us add 'sms' later without
-- a schema change. UNIQUE prevents duplicate sends if a worker retries.
CREATE TABLE IF NOT EXISTS lost_pet_alerts_sent (
  listing_id    BIGINT NOT NULL REFERENCES marketplace_listings(id) ON DELETE CASCADE,
  app_user_id   BIGINT NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
  channel       TEXT   NOT NULL CHECK (channel IN ('email','sms')),
  sent_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  status        TEXT   NOT NULL DEFAULT 'sent' CHECK (status IN ('sent','failed','skipped')),
  error         TEXT,
  PRIMARY KEY (listing_id, app_user_id, channel)
);
CREATE INDEX IF NOT EXISTS idx_lost_pet_alerts_listing ON lost_pet_alerts_sent(listing_id);
CREATE INDEX IF NOT EXISTS idx_lost_pet_alerts_user    ON lost_pet_alerts_sent(app_user_id);

COMMIT;