`migrations/013_email_verification.sql`

32 lines
-- Project: Animals — email verification tokens.
--
-- Wires a "click the link in your inbox before you can post listings or send
-- friend requests" gate. Token is generated at signup, emailed via George,
-- consumed (cleared) when the user clicks /auth/verify?token=.
--
-- Grandfathering: every existing app_users row gets email_verified_at = created_at
-- and email_verified = TRUE so we don't lock out anyone who signed up before
-- the gate existed. The boolean stays in sync with the timestamp — boolean is
-- the cheap predicate, timestamp is the audit trail.

BEGIN;

ALTER TABLE app_users
  ADD COLUMN IF NOT EXISTS email_verification_token   TEXT,
  ADD COLUMN IF NOT EXISTS email_verification_sent_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS email_verified_at          TIMESTAMPTZ;

-- Tokens are random opaque strings — must be unique while live (NULL after consumption).
CREATE UNIQUE INDEX IF NOT EXISTS idx_app_users_verification_token
  ON app_users (email_verification_token)
  WHERE email_verification_token IS NOT NULL;

-- Grandfather every pre-existing user. After this migration, only NEW signups
-- (post-deploy) will have email_verified_at = NULL.
UPDATE app_users
   SET email_verified_at = created_at,
       email_verified    = TRUE
 WHERE email_verified_at IS NULL;

COMMIT;