`db/migrations/007_business_invitations.sql`

20 lines
-- Multi-user-per-business via invite-teammate flow.
-- The existing schema (003_business_users_and_claims.sql) already has
-- UNIQUE (business_id, email) on business_users so multiple users per
-- business is allowed at the table level — we just lacked a flow to
-- create the second+ user. This migration adds that flow.

CREATE TABLE IF NOT EXISTS business_invitations (
  id              BIGSERIAL PRIMARY KEY,
  token           TEXT UNIQUE NOT NULL,                   -- HMAC, 32 chars
  business_id     BIGINT NOT NULL REFERENCES businesses(id) ON DELETE CASCADE,
  email           TEXT NOT NULL,                          -- recipient
  invited_by_user_id BIGINT NOT NULL REFERENCES business_users(id) ON DELETE SET NULL,
  issued_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at      TIMESTAMPTZ NOT NULL,
  consumed_at     TIMESTAMPTZ,
  ip_hash         TEXT
);
CREATE INDEX IF NOT EXISTS business_invitations_business_idx ON business_invitations (business_id);
CREATE INDEX IF NOT EXISTS business_invitations_active_idx ON business_invitations (token) WHERE consumed_at IS NULL;