web/lib/supabase/schema.sql

-- ClauseGuard — Full Database Schema v3.1
-- FIX v4.1: Removed hardcoded admin email (was committed to public repo)
-- Tables ordered by dependency (no forward references)

-- ─── 1. Teams (no dependencies) ───
CREATE TABLE IF NOT EXISTS public.teams (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  owner_id UUID REFERENCES auth.users NOT NULL,
  plan TEXT DEFAULT 'team',
  max_seats INT DEFAULT 5,
  razorpay_subscription_id TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ─── 2. Profiles (depends on teams) ───
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
  email TEXT,
  full_name TEXT,
  avatar_url TEXT,
  razorpay_subscription_id TEXT,
  plan TEXT DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'team')),
  role TEXT DEFAULT 'user' CHECK (role IN ('user', 'admin')),
  is_banned BOOLEAN DEFAULT false,
  team_id UUID REFERENCES public.teams(id) ON DELETE SET NULL,
  analyses_this_month INT DEFAULT 0,
  monthly_reset_at TIMESTAMPTZ DEFAULT date_trunc('month', NOW()),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ─── 3. Team Invites (depends on teams) ───
CREATE TABLE IF NOT EXISTS public.team_invites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  team_id UUID REFERENCES public.teams ON DELETE CASCADE NOT NULL,
  email TEXT NOT NULL,
  role TEXT DEFAULT 'member' CHECK (role IN ('admin', 'member')),
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'expired')),
  invited_by UUID REFERENCES auth.users NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  expires_at TIMESTAMPTZ DEFAULT NOW() + INTERVAL '7 days',
  UNIQUE(team_id, email)
);

-- ─── 4. Analyses (depends on profiles, teams) ───
CREATE TABLE IF NOT EXISTS public.analyses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.profiles ON DELETE CASCADE NOT NULL,
  team_id UUID REFERENCES public.teams ON DELETE SET NULL,
  source_url TEXT,
  source_type TEXT DEFAULT 'tos' CHECK (source_type IN ('tos', 'contract', 'rental', 'other')),
  total_clauses INT NOT NULL,
  flagged_count INT NOT NULL,
  risk_score INT NOT NULL CHECK (risk_score >= 0 AND risk_score <= 100),
  grade CHAR(1) NOT NULL CHECK (grade IN ('A', 'B', 'C', 'D', 'F')),
  clauses JSONB NOT NULL DEFAULT '[]',
  entities JSONB DEFAULT '[]',
  contradictions JSONB DEFAULT '[]',
  obligations JSONB DEFAULT '[]',
  compliance JSONB DEFAULT '{}',
  raw_text TEXT,
  model TEXT DEFAULT 'regex',
  latency_ms INT DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ─── 5. API Keys (depends on profiles, teams) ───
CREATE TABLE IF NOT EXISTS public.api_keys (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.profiles ON DELETE CASCADE NOT NULL,
  team_id UUID REFERENCES public.teams ON DELETE CASCADE,
  name TEXT NOT NULL,
  key_hash TEXT NOT NULL UNIQUE,
  key_prefix TEXT NOT NULL,
  calls_this_month INT DEFAULT 0,
  calls_limit INT DEFAULT 1000,
  last_used_at TIMESTAMPTZ,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ─── 6. Custom Clause Rules (depends on profiles, teams) ───
CREATE TABLE IF NOT EXISTS public.custom_rules (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.profiles ON DELETE CASCADE,
  team_id UUID REFERENCES public.teams ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  pattern TEXT NOT NULL,
  severity TEXT DEFAULT 'MEDIUM' CHECK (severity IN ('HIGH', 'MEDIUM', 'LOW')),
  category TEXT NOT NULL,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- ─── 7. Admin Logs ───
CREATE TABLE IF NOT EXISTS public.admin_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  admin_id UUID REFERENCES auth.users NOT NULL,
  action TEXT NOT NULL,
  target_type TEXT,
  target_id TEXT,
  details JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ─── Indexes ───
CREATE INDEX IF NOT EXISTS idx_analyses_user_id ON public.analyses(user_id);
CREATE INDEX IF NOT EXISTS idx_analyses_team_id ON public.analyses(team_id);
CREATE INDEX IF NOT EXISTS idx_analyses_created_at ON public.analyses(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_api_keys_key_hash ON public.api_keys(key_hash);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON public.api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_team_invites_email ON public.team_invites(email);
CREATE INDEX IF NOT EXISTS idx_custom_rules_user_id ON public.custom_rules(user_id);
CREATE INDEX IF NOT EXISTS idx_custom_rules_team_id ON public.custom_rules(team_id);
CREATE INDEX IF NOT EXISTS idx_admin_logs_created ON public.admin_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_profiles_role ON public.profiles(role);
CREATE INDEX IF NOT EXISTS idx_profiles_email ON public.profiles(email);

-- ─── Row Level Security ───
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.analyses ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.team_invites ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.api_keys ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.custom_rules ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.admin_logs ENABLE ROW LEVEL SECURITY;

-- Profiles
CREATE POLICY "Users see own profile" ON public.profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users update own profile" ON public.profiles FOR UPDATE USING (auth.uid() = id);
CREATE POLICY "Admins read all profiles" ON public.profiles FOR SELECT USING (auth.uid() IN (SELECT id FROM public.profiles WHERE role = 'admin'));
CREATE POLICY "Admins update all profiles" ON public.profiles FOR UPDATE USING (auth.uid() IN (SELECT id FROM public.profiles WHERE role = 'admin'));

-- Analyses
CREATE POLICY "Users see own analyses" ON public.analyses FOR SELECT
  USING (auth.uid() = user_id OR team_id IN (SELECT team_id FROM public.profiles WHERE id = auth.uid()));
CREATE POLICY "Users insert analyses" ON public.analyses FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users delete own analyses" ON public.analyses FOR DELETE USING (auth.uid() = user_id);
CREATE POLICY "Admins read all analyses" ON public.analyses FOR SELECT USING (auth.uid() IN (SELECT id FROM public.profiles WHERE role = 'admin'));

-- Teams
CREATE POLICY "Team members can view" ON public.teams FOR SELECT
  USING (id IN (SELECT team_id FROM public.profiles WHERE id = auth.uid()) OR owner_id = auth.uid());
CREATE POLICY "Owner can update team" ON public.teams FOR UPDATE USING (owner_id = auth.uid());
CREATE POLICY "Admins read all teams" ON public.teams FOR SELECT USING (auth.uid() IN (SELECT id FROM public.profiles WHERE role = 'admin'));

-- Team invites
CREATE POLICY "Members see team invites" ON public.team_invites FOR SELECT
  USING (team_id IN (SELECT team_id FROM public.profiles WHERE id = auth.uid()));
CREATE POLICY "Users can invite" ON public.team_invites FOR INSERT WITH CHECK (invited_by = auth.uid());

-- API Keys
CREATE POLICY "Users see own API keys" ON public.api_keys FOR SELECT
  USING (user_id = auth.uid() OR team_id IN (SELECT team_id FROM public.profiles WHERE id = auth.uid()));
CREATE POLICY "Users manage own API keys" ON public.api_keys FOR ALL USING (user_id = auth.uid());
CREATE POLICY "Admins read all api_keys" ON public.api_keys FOR SELECT USING (auth.uid() IN (SELECT id FROM public.profiles WHERE role = 'admin'));

-- Custom Rules
CREATE POLICY "Users see own rules" ON public.custom_rules FOR SELECT
  USING (user_id = auth.uid() OR team_id IN (SELECT team_id FROM public.profiles WHERE id = auth.uid()));
CREATE POLICY "Users manage own rules" ON public.custom_rules FOR ALL USING (user_id = auth.uid());
CREATE POLICY "Admins read all rules" ON public.custom_rules FOR SELECT USING (auth.uid() IN (SELECT id FROM public.profiles WHERE role = 'admin'));

-- Admin Logs
CREATE POLICY "Admins manage logs" ON public.admin_logs FOR ALL
  USING (auth.uid() IN (SELECT id FROM public.profiles WHERE role = 'admin'));

-- ─── Auto-create profile on signup ───
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name, avatar_url)
  VALUES (
    NEW.id, NEW.email,
    COALESCE(NEW.raw_user_meta_data ->> 'full_name', NEW.raw_user_meta_data ->> 'name', ''),
    COALESCE(NEW.raw_user_meta_data ->> 'avatar_url', NEW.raw_user_meta_data ->> 'picture', '')
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- ─── FIX v4.1: Admin setup via environment variable ───
-- DO NOT hardcode admin emails in source code committed to public repos.
-- Instead, run this manually after your first signup:
--
--   UPDATE public.profiles
--   SET role = 'admin', plan = 'pro'
--   WHERE email = '<YOUR_EMAIL>';
--
-- Or set ADMIN_EMAIL env var and run:
--   DO $$ BEGIN
--     UPDATE public.profiles SET role = 'admin', plan = 'pro'
--     WHERE email = current_setting('app.admin_email', true);
--   END $$;

-- ─── Monthly reset function ───
CREATE OR REPLACE FUNCTION public.reset_monthly_usage()
RETURNS void AS $$
BEGIN
  UPDATE public.profiles SET analyses_this_month = 0, monthly_reset_at = date_trunc('month', NOW())
  WHERE monthly_reset_at < date_trunc('month', NOW());
  UPDATE public.api_keys SET calls_this_month = 0;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;