← back to Lawyer Directory Builder

migrations/013_user_tiers.sql

25 lines

-- Four-tier auth: guest / client / lawyer / admin
-- Adds Google OAuth fields, bar-number claim flow, Stripe subscription fields,
-- and per-user comment-toggle.

ALTER TABLE app_users
  ADD COLUMN IF NOT EXISTS tier TEXT NOT NULL DEFAULT 'client'
    CHECK (tier IN ('guest','client','lawyer','admin')),
  ADD COLUMN IF NOT EXISTS google_sub TEXT,
  ADD COLUMN IF NOT EXISTS google_email TEXT,
  ADD COLUMN IF NOT EXISTS google_picture TEXT,
  ADD COLUMN IF NOT EXISTS claimed_professional_id BIGINT REFERENCES professionals(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS bar_number TEXT,
  ADD COLUMN IF NOT EXISTS bar_verified_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS paid_until TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS stripe_customer_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_subscription_id TEXT,
  ADD COLUMN IF NOT EXISTS comments_enabled BOOLEAN NOT NULL DEFAULT true;

-- Unique only when set (allow multiple rows with NULL google_sub)
CREATE UNIQUE INDEX IF NOT EXISTS uq_app_users_google_sub
  ON app_users (google_sub) WHERE google_sub IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_app_users_tier ON app_users (tier);
CREATE INDEX IF NOT EXISTS idx_app_users_claimed_pro ON app_users (claimed_professional_id);