`db/migrations/004_business_users_and_claims.sql`

31 lines
-- Admin/claim infrastructure for businesses to claim their directory listing,
-- log into a dashboard, see leads, edit profile, and manage subscription.

CREATE TABLE IF NOT EXISTS business_users (
  id              BIGSERIAL PRIMARY KEY,
  email           TEXT UNIQUE NOT NULL,
  password_hash   TEXT,                                            -- nullable until first password set; magic-link login still works
  business_id     BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  display_name    TEXT,
  email_verified  BOOLEAN NOT NULL DEFAULT false,
  last_login_at   TIMESTAMPTZ,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (business_id, email)
);
CREATE INDEX IF NOT EXISTS business_users_business_idx ON business_users (business_id);

-- One-shot claim tokens. The email→business_id mapping is in the row, so a
-- compromised token only unlocks that one specific listing.
CREATE TABLE IF NOT EXISTS claim_tokens (
  id              BIGSERIAL PRIMARY KEY,
  token           TEXT UNIQUE NOT NULL,                            -- HMAC-derived, 32 chars
  business_id     BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  email           TEXT NOT NULL,                                   -- the recipient who'll claim
  issued_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NOT NULL,
  consumed_at     TIMESTAMPTZ,
  ip_hash         TEXT
);
CREATE INDEX IF NOT EXISTS claim_tokens_business_idx ON claim_tokens (business_id);
CREATE INDEX IF NOT EXISTS claim_tokens_active_idx ON claim_tokens (token) WHERE consumed_at IS NULL;