`migrations/016_weekly_newsletter.sql`

35 lines
-- Project: Animals — weekly newsletter unsubscribe + send tracking.
--
-- Wires the agents/animal-agent `weekly_newsletter` task:
--   1. Sunday cron picks up users with unsubscribed_at IS NULL.
--   2. Each user gets a digest (5 random breed photos + upcoming dog shows in
--      home_state + latest 3 marketplace listings near home_zip).
--   3. Email contains an "unsubscribe" link tied to a stable per-user
--      `unsubscribe_token` — clicking it sets unsubscribed_at without requiring
--      a login (per CAN-SPAM: unsubscribe must be one click, no auth).
--   4. last_newsletter_sent_at gates against double-sends if the cron tick
--      runs more than once on the same Sunday.
--
-- Token model: unlike email_verification_token (single-use, cleared after
-- click), unsubscribe_token is *stable* — the same link should keep working
-- forever, even after the user re-subscribes. Re-subscribe is a re-click
-- of the same link (or a checkbox in /me settings later).

BEGIN;

ALTER TABLE app_users
  ADD COLUMN IF NOT EXISTS unsubscribe_token        TEXT,
  ADD COLUMN IF NOT EXISTS unsubscribed_at          TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS last_newsletter_sent_at  TIMESTAMPTZ;

-- Tokens must be unique while populated (NULL allowed for grandfathered users
-- whose token gets backfilled lazily on first send).
CREATE UNIQUE INDEX IF NOT EXISTS idx_app_users_unsubscribe_token
  ON app_users (unsubscribe_token)
  WHERE unsubscribe_token IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_app_users_last_newsletter
  ON app_users (last_newsletter_sent_at);

COMMIT;