Close RLS Policy Gaps for All Operations

ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;
 
-- Only a SELECT policy — inserts, updates, and deletes silently fail
CREATE POLICY "Users can view own todos"
  ON public.todos FOR SELECT
  USING (auth.uid() = user_id);
 
-- ❌ This insert will silently return null with no error:
-- const { data, error } = await supabase.from('todos').insert({ title: 'Test', user_id: userId })
-- data = null, error = null — no indication of failure

-- ❌ UPDATE with only USING — user can see rows but the update may fail
-- if the updated values would change user_id
CREATE POLICY "Users can update own todos"
  ON public.todos FOR UPDATE
  USING (auth.uid() = user_id);
  -- Missing: WITH CHECK (auth.uid() = user_id)
  -- An attacker could potentially change user_id to another user's ID

ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;
 
-- SELECT: Users can read their own todos
CREATE POLICY "Users can view own todos"
  ON public.todos FOR SELECT
  USING (auth.uid() = user_id);
 
-- INSERT: Users can create todos assigned to themselves
CREATE POLICY "Users can create own todos"
  ON public.todos FOR INSERT
  WITH CHECK (auth.uid() = user_id);
 
-- UPDATE: Users can update their own todos, cannot reassign ownership
CREATE POLICY "Users can update own todos"
  ON public.todos FOR UPDATE
  USING (auth.uid() = user_id)       -- can only see own rows
  WITH CHECK (auth.uid() = user_id); -- cannot change user_id to someone else
 
-- DELETE: Users can delete their own todos
CREATE POLICY "Users can delete own todos"
  ON public.todos FOR DELETE
  USING (auth.uid() = user_id);

-- ✅ Shorthand when the ownership check is identical for all operations
CREATE POLICY "Users can manage own todos"
  ON public.todos FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Child table: comments belong to documents
-- Access rule: you can manage comments on documents you own
 
CREATE POLICY "Users can view comments on own documents"
  ON public.comments FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.documents
      WHERE documents.id = comments.document_id
      AND documents.user_id = auth.uid()
    )
  );
 
CREATE POLICY "Users can add comments on own documents"
  ON public.comments FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.documents
      WHERE documents.id = comments.document_id
      AND documents.user_id = auth.uid()
    )
  );
 
-- Repeat for UPDATE and DELETE with the same EXISTS check

-- Users can access data for any team they belong to
CREATE POLICY "Team members can view team documents"
  ON public.documents FOR SELECT
  USING (
    team_id IN (
      SELECT team_id FROM public.team_members
      WHERE user_id = auth.uid()
    )
  );

# Find tables with RLS enabled, then check which operations have policies
grep -rn "ENABLE ROW LEVEL SECURITY" supabase/migrations/ --include="*.sql"
# Check for INSERT/UPDATE/DELETE policies for each table
grep -rn "CREATE POLICY" supabase/migrations/ --include="*.sql"