`db/migrations/003_users_auth.sql`

105 lines
-- Phase 1 — Auth, users, doctor claims, subscriptions, claim_status
-- Apply with: psql -d doctor_professional_directory -f db/migrations/003_users_auth.sql

BEGIN;

-- ─── users ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id                bigserial PRIMARY KEY,
  email             citext NOT NULL UNIQUE,
  email_verified_at timestamptz,
  google_sub        text UNIQUE,
  display_name      text,
  avatar_url        text,
  role              text NOT NULL DEFAULT 'patient'
                       CHECK (role IN ('guest','patient','doctor','admin')),
  tier              text NOT NULL DEFAULT 'free'
                       CHECK (tier IN ('free','paid')),
  comments_disabled boolean NOT NULL DEFAULT false,
  claimed_professional_id bigint REFERENCES professionals(id) ON DELETE SET NULL,
  claimed_organization_id bigint REFERENCES organizations(id) ON DELETE SET NULL,
  stripe_customer_id text UNIQUE,
  last_login_at     timestamptz,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now(),
  deleted_at        timestamptz
);
CREATE INDEX IF NOT EXISTS idx_users_role_tier   ON users (role, tier);
CREATE INDEX IF NOT EXISTS idx_users_claimed_pro ON users (claimed_professional_id) WHERE claimed_professional_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_claimed_org ON users (claimed_organization_id) WHERE claimed_organization_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_deleted     ON users (deleted_at) WHERE deleted_at IS NOT NULL;

-- ─── doctor_claims ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS doctor_claims (
  id                bigserial PRIMARY KEY,
  user_id           bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  target_professional_id bigint REFERENCES professionals(id) ON DELETE CASCADE,
  target_organization_id bigint REFERENCES organizations(id) ON DELETE CASCADE,
  npi_provided      varchar(10),
  license_provided  text,
  email_verification_token text NOT NULL,
  email_verified_at timestamptz,
  status            text NOT NULL DEFAULT 'pending'
                       CHECK (status IN ('pending','approved','rejected','expired')),
  status_reason     text,
  decided_at        timestamptz,
  decided_by_user_id bigint REFERENCES users(id),
  created_at        timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT either_target CHECK (
    (target_professional_id IS NOT NULL AND target_organization_id IS NULL) OR
    (target_professional_id IS NULL AND target_organization_id IS NOT NULL)
  )
);
CREATE INDEX IF NOT EXISTS idx_claims_status ON doctor_claims (status);
CREATE INDEX IF NOT EXISTS idx_claims_user   ON doctor_claims (user_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_claims_one_pending_pro
  ON doctor_claims (target_professional_id) WHERE status = 'pending' AND target_professional_id IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_claims_one_pending_org
  ON doctor_claims (target_organization_id) WHERE status = 'pending' AND target_organization_id IS NOT NULL;

-- ─── subscriptions ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS subscriptions (
  id                  bigserial PRIMARY KEY,
  user_id             bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  stripe_subscription_id text NOT NULL UNIQUE,
  stripe_price_id     text NOT NULL,
  plan                text NOT NULL,        -- doctor_pro_monthly | patient_plus_monthly
  status              text NOT NULL,        -- active|past_due|canceled|unpaid|trialing
  current_period_end  timestamptz,
  cancel_at_period_end boolean NOT NULL DEFAULT false,
  started_at          timestamptz NOT NULL DEFAULT now(),
  ended_at            timestamptz,
  raw_json            jsonb
);
CREATE INDEX IF NOT EXISTS idx_subs_user   ON subscriptions (user_id);
CREATE INDEX IF NOT EXISTS idx_subs_status ON subscriptions (status);

-- ─── express-session table (connect-pg-simple expected schema) ────────────
CREATE TABLE IF NOT EXISTS user_sessions (
  sid    varchar NOT NULL COLLATE "default" PRIMARY KEY,
  sess   json    NOT NULL,
  expire timestamp(6) NOT NULL
) WITH (OIDS=FALSE);
CREATE INDEX IF NOT EXISTS IDX_user_sessions_expire ON user_sessions (expire);

-- ─── claim_status on existing tables ───────────────────────────────────────
ALTER TABLE professionals
  ADD COLUMN IF NOT EXISTS claim_status text NOT NULL DEFAULT 'unclaimed'
    CHECK (claim_status IN ('unclaimed','claimed','verified'));
ALTER TABLE organizations
  ADD COLUMN IF NOT EXISTS claim_status text NOT NULL DEFAULT 'unclaimed'
    CHECK (claim_status IN ('unclaimed','claimed','verified'));
CREATE INDEX IF NOT EXISTS idx_pros_claim ON professionals (claim_status);
CREATE INDEX IF NOT EXISTS idx_orgs_claim ON organizations (claim_status);

-- ─── updated_at trigger function (idempotent) ─────────────────────────────
CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS trigger AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS users_set_updated_at ON users;
CREATE TRIGGER users_set_updated_at BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION trigger_set_timestamp();

COMMIT;