Spaces:
Sleeping
Sleeping
File size: 2,823 Bytes
fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 fbf3514 9548e93 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | -- ClauseGuard — Supabase Database Schema (Razorpay)
-- Profiles
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')),
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()
);
-- Analyses
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,
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 '[]',
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_created_at ON public.analyses(created_at DESC);
-- Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.analyses ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Users can view own profile" ON public.profiles FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own profile" ON public.profiles FOR UPDATE USING (auth.uid() = id);
CREATE POLICY "Users can view own analyses" ON public.analyses FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own analyses" ON public.analyses FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete own analyses" ON public.analyses FOR DELETE USING (auth.uid() = user_id);
-- 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;
CREATE OR REPLACE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
-- Monthly usage reset
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());
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
|