`migrations/015_business_claims.sql`

40 lines
-- Project: Animals — business-claim tokens.
--
-- Wires the /clinic/:id/claim flow:
--   1. Logged-in user POSTs /clinic/:id/claim
--   2. We generate a fresh token, persist it on businesses.claim_token,
--      and send the click-link to businesses.email (the address already on
--      file from the directory crawl).
--   3. Anyone with access to that mailbox clicks the link → /clinic/:id/verify-claim?token=…
--   4. Token is consumed (cleared) and businesses.claimed_by is set to the
--      user_id stored alongside the token.
--   5. From then on /clinic/:id/edit lets that user (and only that user)
--      edit hours/website/phone.
--
-- claimed_by already existed (BIGINT, see 001_initial_schema.sql) — we are
-- only adding the token-flow plumbing + claimed_at audit timestamp.
--
-- Single-token-at-a-time semantics: if a second user tries to claim while the
-- first token is still live, the second token overwrites the first. The
-- email-on-file owner is the arbiter — whoever's link they click, wins.

BEGIN;

ALTER TABLE businesses
  ADD COLUMN IF NOT EXISTS claim_token         TEXT,
  ADD COLUMN IF NOT EXISTS claim_token_user_id BIGINT,
  ADD COLUMN IF NOT EXISTS claim_token_sent_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS claimed_at          TIMESTAMPTZ;

-- Live tokens must be unique (NULL after consumption).
CREATE UNIQUE INDEX IF NOT EXISTS idx_businesses_claim_token
  ON businesses (claim_token)
  WHERE claim_token IS NOT NULL;

-- Helps the green-badge / "your listings" lookup.
CREATE INDEX IF NOT EXISTS idx_biz_claimed_by
  ON businesses (claimed_by)
  WHERE claimed_by IS NOT NULL;

COMMIT;