supabase-setup.sql

-- Enable Row Level Security
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Case" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Document" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Conversation" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Message" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "LinkedImage" ENABLE ROW LEVEL SECURITY;

-- Create a function to handle new user signups
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, display_name)
  VALUES (NEW.id, NEW.email, COALESCE(NEW.raw_user_meta_data->>'full_name', NEW.email));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger to automatically create profile on user signup
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- Create RLS policies for profiles table
CREATE POLICY "Users can view own profile" ON profiles
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id);

-- Create RLS policies for Case table
CREATE POLICY "Users can view own cases" ON "Case"
  FOR SELECT USING (auth.uid() = "profilesId");

CREATE POLICY "Users can create own cases" ON "Case"
  FOR INSERT WITH CHECK (auth.uid() = "profilesId");

CREATE POLICY "Users can update own cases" ON "Case"
  FOR UPDATE USING (auth.uid() = "profilesId");

CREATE POLICY "Users can delete own cases" ON "Case"
  FOR DELETE USING (auth.uid() = "profilesId");

-- Create RLS policies for Document table
CREATE POLICY "Users can view own documents" ON "Document"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "Case" 
      WHERE "Case".id = "Document"."caseId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

CREATE POLICY "Users can create documents for own cases" ON "Document"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "Case" 
      WHERE "Case".id = "Document"."caseId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

CREATE POLICY "Users can update own documents" ON "Document"
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM "Case" 
      WHERE "Case".id = "Document"."caseId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

CREATE POLICY "Users can delete own documents" ON "Document"
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM "Case" 
      WHERE "Case".id = "Document"."caseId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

-- Create RLS policies for Conversation table
CREATE POLICY "Users can view own conversations" ON "Conversation"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "Case" 
      WHERE "Case".id = "Conversation"."caseId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

CREATE POLICY "Users can create conversations for own cases" ON "Conversation"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "Case" 
      WHERE "Case".id = "Conversation"."caseId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

CREATE POLICY "Users can update own conversations" ON "Conversation"
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM "Case" 
      WHERE "Case".id = "Conversation"."caseId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

CREATE POLICY "Users can delete own conversations" ON "Conversation"
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM "Case" 
      WHERE "Case".id = "Conversation"."caseId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

-- Create RLS policies for Message table
CREATE POLICY "Users can view messages in own conversations" ON "Message"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "Conversation" 
      JOIN "Case" ON "Case".id = "Conversation"."caseId" 
      WHERE "Conversation".id = "Message"."conversationId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

CREATE POLICY "Users can create messages in own conversations" ON "Message"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "Conversation" 
      JOIN "Case" ON "Case".id = "Conversation"."caseId" 
      WHERE "Conversation".id = "Message"."conversationId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

-- Create RLS policies for LinkedImage table
CREATE POLICY "Users can view own linked images" ON "LinkedImage"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "Document" 
      JOIN "Case" ON "Case".id = "Document"."caseId" 
      WHERE "Document".id = "LinkedImage"."documentId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

CREATE POLICY "Users can create linked images for own documents" ON "LinkedImage"
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM "Document" 
      JOIN "Case" ON "Case".id = "Document"."caseId" 
      WHERE "Document".id = "LinkedImage"."documentId" 
      AND "Case"."profilesId" = auth.uid()
    )
  );

-- Disable email confirmation requirement
-- This needs to be done in the Supabase dashboard:
-- Go to Authentication > Settings > Email Templates
-- Set "Confirm signup" to "No email confirmation required"