ExamCraftAI / Plans /schema.sql
Hkoradiya123
Initial commit
5463318
Raw History Blame Contribute Delete
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]);