I’m developing a Next.js application with Supabase as the backend. I need to implement Row Level Security (RLS) policies that allow a company owner to view and manage data for all employees in their company, while restricting regular employees to only their own data. My setup includes separate authentication and employee tables.

Database structure:

• auth.users table (managed by Supabase):

  • id (PK, uuid)
  • email

• company table:

  • company_id (PK)
  • company_name

• employee table:

  • id (PK)
  • user_id (FK to auth.users.id)
  • company_id (FK to company table)
  • employee_name
  • employee_permission (e.g., ‘owner’, ’employee’)

• work_time table:

  • id (PK)
  • employee_id (FK to employee.id)
  • user_id (FK to auth.users.id)
  • start_time
  • end_time

I’ve tried implementing the following policy for the work_time table, but it’s not working as expected:

CREATE POLICY "Owners can view all work time in their company" ON work_time
FOR SELECT USING (
  EXISTS (
    SELECT 1 FROM employee AS e
    WHERE e.user_id = auth.uid() 
    AND e.employee_permission = 'owner'
    AND e.company_id = (SELECT company_id FROM employee WHERE id = work_time.employee_id)
  )
);

Any guidance on structuring these policies correctly would be greatly appreciated. Thank you!