rimshaaa2025f/AI-JobInterview

1-- Supabase Database Setup for AI Interview Coach
2-- Run these commands in your Supabase SQL Editor
3
4-- 1. Create profiles table to store user information
5CREATE TABLE IF NOT EXISTS public.profiles (
6    id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
7    full_name TEXT,
8    email TEXT UNIQUE NOT NULL,
9    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
10    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
11);
12
13-- 2. Enable Row Level Security (RLS)
14ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
15
16-- 3. Create policies for profiles table
17-- Users can view their own profile
18CREATE POLICY "Users can view own profile" ON public.profiles
19    FOR SELECT USING (auth.uid() = id);
20
21-- Users can update their own profile
22CREATE POLICY "Users can update own profile" ON public.profiles
23    FOR UPDATE USING (auth.uid() = id);
24
25-- Users can insert their own profile
26CREATE POLICY "Users can insert own profile" ON public.profiles
27    FOR INSERT WITH CHECK (auth.uid() = id);
28
29-- 4. Create function to handle new user signup
30CREATE OR REPLACE FUNCTION public.handle_new_user()
31RETURNS TRIGGER AS $$
32BEGIN
33    INSERT INTO public.profiles (id, full_name, email)
34    VALUES (
35        NEW.id,
36        COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.email),
37        NEW.email
38    );
39    RETURN NEW;
40END;
41$$ LANGUAGE plpgsql SECURITY DEFINER;
42
43-- 5. Create trigger for new user signup
44DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
45CREATE TRIGGER on_auth_user_created
46    AFTER INSERT ON auth.users
47    FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
48
49-- 6. Create function to update updated_at timestamp
50CREATE OR REPLACE FUNCTION public.handle_updated_at()
51RETURNS TRIGGER AS $$
52BEGIN
53    NEW.updated_at = NOW();
54    RETURN NEW;
55END;
56$$ LANGUAGE plpgsql;
57
58-- 7. Create trigger for updated_at
59DROP TRIGGER IF EXISTS on_profiles_updated ON public.profiles;
60CREATE TRIGGER on_profiles_updated
61    BEFORE UPDATE ON public.profiles
62    FOR EACH ROW EXECUTE FUNCTION public.handle_updated_at();
63
64-- 8. Grant necessary permissions
65GRANT USAGE ON SCHEMA public TO anon, authenticated;
66GRANT ALL ON public.profiles TO anon, authenticated;
67