Spaces:
Sleeping
Sleeping
Download Plans/schema.sql from smit9604/ExamCraftAI: direct link, hf CLI and curl.
- Browser
- Download file 5.78 kB
-
https://huggingface.co/spaces/smit9604/ExamCraftAI/resolve/main/Plans/schema.sql
- Command line
-
hf download hf://spaces/smit9604/ExamCraftAI/Plans/schema.sql
-
curl -L -o schema.sql https://huggingface.co/spaces/smit9604/ExamCraftAI/resolve/main/Plans/schema.sql
5.78 kB
| -- 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]); | |