Spaces:
Runtime error
Runtime error
File size: 5,781 Bytes
5463318 | 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 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 | -- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- ==========================================
-- 1. Profiles Table
-- ==========================================
CREATE TABLE public.profiles (
id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
full_name TEXT,
work_email TEXT,
institution_name TEXT,
role TEXT,
password TEXT,
avatar_url TEXT,
plan_type TEXT DEFAULT 'free',
available_credits INTEGER DEFAULT 10,
created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);
-- Enable Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
-- RLS Policies for Profiles
CREATE POLICY "Users can view their own profile."
ON public.profiles FOR SELECT
USING (auth.uid() = id);
CREATE POLICY "Users can update their own profile."
ON public.profiles FOR UPDATE
USING (auth.uid() = id);
-- Trigger to automatically create a profile when a new user signs up in Auth
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
INSERT INTO public.profiles (id, full_name, work_email, institution_name, role, avatar_url)
VALUES (
new.id,
new.raw_user_meta_data->>'full_name',
new.raw_user_meta_data->>'work_email',
new.raw_user_meta_data->>'institution_name',
new.raw_user_meta_data->>'role',
new.raw_user_meta_data->>'avatar_url'
);
RETURN new;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE OR REPLACE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE PROCEDURE public.handle_new_user();
-- ==========================================
-- 2. Materials Table
-- ==========================================
CREATE TABLE public.materials (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
file_name TEXT NOT NULL,
file_size INTEGER,
storage_path TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);
-- Enable Row Level Security
ALTER TABLE public.materials ENABLE ROW LEVEL SECURITY;
-- RLS Policies for Materials
CREATE POLICY "Users can view their own materials."
ON public.materials FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own materials."
ON public.materials FOR INSERT
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own materials."
ON public.materials FOR UPDATE
USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own materials."
ON public.materials FOR DELETE
USING (auth.uid() = user_id);
-- ==========================================
-- 3. Exam Papers Table
-- ==========================================
CREATE TABLE public.exam_papers (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE NOT NULL,
title TEXT NOT NULL,
subject TEXT,
total_marks INTEGER,
total_questions INTEGER,
paper_config JSONB,
content JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()) NOT NULL
);
-- Enable Row Level Security
ALTER TABLE public.exam_papers ENABLE ROW LEVEL SECURITY;
-- RLS Policies for Exam Papers
CREATE POLICY "Users can view their own exam papers."
ON public.exam_papers FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can insert their own exam papers."
ON public.exam_papers FOR INSERT
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own exam papers."
ON public.exam_papers FOR UPDATE
USING (auth.uid() = user_id);
CREATE POLICY "Users can delete their own exam papers."
ON public.exam_papers FOR DELETE
USING (auth.uid() = user_id);
-- ==========================================
-- 4. Questions Table
-- ==========================================
CREATE TABLE public.questions (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
paper_id UUID REFERENCES public.exam_papers(id) ON DELETE CASCADE NOT NULL,
question_type TEXT,
question_text TEXT,
options JSONB,
correct_answer TEXT,
marks INTEGER,
explanation TEXT
);
-- Enable Row Level Security
ALTER TABLE public.questions ENABLE ROW LEVEL SECURITY;
-- RLS Policies for Questions (Based on exam_papers ownership)
CREATE POLICY "Users can view questions of their own exam papers."
ON public.questions FOR SELECT
USING (EXISTS (
SELECT 1 FROM public.exam_papers
WHERE id = public.questions.paper_id AND user_id = auth.uid()
));
CREATE POLICY "Users can insert questions to their own exam papers."
ON public.questions FOR INSERT
WITH CHECK (EXISTS (
SELECT 1 FROM public.exam_papers
WHERE id = public.questions.paper_id AND user_id = auth.uid()
));
CREATE POLICY "Users can update questions of their own exam papers."
ON public.questions FOR UPDATE
USING (EXISTS (
SELECT 1 FROM public.exam_papers
WHERE id = public.questions.paper_id AND user_id = auth.uid()
));
CREATE POLICY "Users can delete questions of their own exam papers."
ON public.questions FOR DELETE
USING (EXISTS (
SELECT 1 FROM public.exam_papers
WHERE id = public.questions.paper_id AND user_id = auth.uid()
));
-- ==========================================
-- Optional: Storage Bucket Policies
-- (Assuming you create a bucket named 'materials' in the Supabase Dashboard)
-- ==========================================
-- CREATE POLICY "Users can upload materials to their own folder."
-- ON storage.objects FOR INSERT
-- WITH CHECK (bucket_id = 'materials' AND auth.uid()::text = (storage.foldername(name))[1]);
-- CREATE POLICY "Users can read their own materials."
-- ON storage.objects FOR SELECT
-- USING (bucket_id = 'materials' AND auth.uid()::text = (storage.foldername(name))[1]);
|