Row Level Security (RLS)

All PIP AI tables have RLS enabled. Policies enforce data isolation and access control.

Helper Functions

pip_ai_check_is_admin()

CREATE FUNCTION pip_ai_check_is_admin()
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER  -- Bypasses RLS to avoid recursion
SET search_path = 'public'
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM pip_ai_user_profiles
    WHERE id = auth.uid() AND role = 'admin'
  );
END;
$$;

Why SECURITY DEFINER?

RLS policies on pip_ai_user_profiles that query the same table cause infinite recursion. Using SECURITY DEFINER functions that bypass RLS avoids this problem.

pip_ai_is_project_member(project_id)

Returns true if the current user is a member of the specified project.

pip_ai_can_edit_project(project_id)

Returns true if the current user has edit access (owner, editor, or admin).

Policy Summary

Table	SELECT	INSERT	UPDATE	DELETE
projects	Member or Admin	Authenticated	Editor/Owner/Admin	Owner/Admin
project_members	Member or Admin	Editor/Owner/Admin	Owner or Self	Owner or Self
documents	Member or Admin	Editor/Owner/Admin	Editor/Owner/Admin	Editor/Owner/Admin
spec_uploads	Admin only	Admin only	Admin only	Admin only
spec_sections	Admin or brand match	Admin only	Admin only	Admin only
pip_items	Member or Admin	Editor/Owner/Admin	Editor/Owner/Admin	Editor/Owner/Admin
matches	Via PIP membership	Via PIP edit	Via PIP edit	Via PIP edit
floor_plans	Member or Admin	Editor/Owner/Admin	Editor/Owner/Admin	Editor/Owner/Admin
floor_plan_elements	Via floor plan membership	Via floor plan edit	Via floor plan edit	Via floor plan edit
user_profiles	Own or Admin	System trigger	Own or Admin	—
upload_jobs	Admin only	Admin only	Admin only	Admin only

Brand Isolation Policy

The critical security policy that prevents cross-brand data leakage:

CREATE POLICY "spec_sections_select_scoped"
ON pip_ai_spec_sections FOR SELECT
USING (
  pip_ai_check_is_admin()
  OR brand_name IN (
    SELECT DISTINCT brand_name
    FROM pip_ai_projects
    WHERE created_by = auth.uid()
  )
);

Users can only see specs for brands they have projects in.