> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# RLS Policy Patterns Reference

> > Version: 1.0.0 > Last Updated: 2026-01-11 > Status: Active

> **Version:** 1.0.0\
> **Last Updated:** 2026-01-11\
> **Status:** Active

## Overview

This document defines the standard Row Level Security (RLS) patterns used across Encore Health OS. All business tables MUST have RLS enabled with appropriate policies.

## Core Principles

### 1. SECURITY DEFINER Helper Functions

**Never query RLS-protected tables directly in policies** - this causes infinite recursion.

```sql theme={null}
-- ❌ WRONG - Causes infinite recursion
CREATE POLICY "bad_policy" ON hr_employees
  FOR SELECT USING (
    organization_id IN (SELECT organization_id FROM pf_user_roles WHERE user_id = auth.uid())
  );

-- ✅ CORRECT - Use SECURITY DEFINER helper
CREATE POLICY "good_policy" ON hr_employees
  FOR SELECT USING (hr_has_org_access(organization_id, auth.uid()));
```

### 2. WITH CHECK on UPDATE Policies

**Always include WITH CHECK clause** to prevent tenant data movement.

```sql theme={null}
-- ❌ WRONG - Missing WITH CHECK allows changing organization_id
CREATE POLICY "update_policy" ON hr_employees
  FOR UPDATE USING (hr_has_org_access(organization_id, auth.uid()));

-- ✅ CORRECT - WITH CHECK prevents tenant switching
CREATE POLICY "update_policy" ON hr_employees
  FOR UPDATE USING (hr_has_org_access(organization_id, auth.uid()))
  WITH CHECK (hr_has_org_access(organization_id, auth.uid()));
```

### 3. Multi-Tenant Isolation

All policies MUST filter by `organization_id` (and `site_id` where applicable).

### 4. Defense in Depth

RLS is one layer - also include application-level checks in mutations.

***

## Standard Patterns

### Pattern A: Full CRUD (Business Entities)

For tables where users with org access can perform all operations.

```sql theme={null}
-- SELECT: All org users can view
CREATE POLICY "{table}_select" ON {table}
  FOR SELECT USING ({core}_has_org_access(organization_id, auth.uid()));

-- INSERT: All org users can create
CREATE POLICY "{table}_insert" ON {table}
  FOR INSERT WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));

-- UPDATE: All org users can modify
CREATE POLICY "{table}_update" ON {table}
  FOR UPDATE USING ({core}_has_org_access(organization_id, auth.uid()))
  WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));

-- DELETE: Only org admins can delete
CREATE POLICY "{table}_delete" ON {table}
  FOR DELETE USING ({core}_is_org_admin(organization_id, auth.uid()));
```

### Pattern B: Admin-Managed (Settings/Config)

For tables where only admins can write but all org users can read.

```sql theme={null}
-- SELECT: All org users can view
CREATE POLICY "{table}_select" ON {table}
  FOR SELECT USING ({core}_has_org_access(organization_id, auth.uid()));

-- INSERT/UPDATE/DELETE: Only org admins
CREATE POLICY "{table}_insert" ON {table}
  FOR INSERT WITH CHECK ({core}_is_org_admin(organization_id, auth.uid()));

CREATE POLICY "{table}_update" ON {table}
  FOR UPDATE USING ({core}_is_org_admin(organization_id, auth.uid()))
  WITH CHECK ({core}_is_org_admin(organization_id, auth.uid()));

CREATE POLICY "{table}_delete" ON {table}
  FOR DELETE USING ({core}_is_org_admin(organization_id, auth.uid()));
```

### Pattern C: Audit/Log (Append-Only)

For tables that should never be modified after creation.

```sql theme={null}
-- SELECT: All org users can view logs
CREATE POLICY "{table}_select" ON {table}
  FOR SELECT USING ({core}_has_org_access(organization_id, auth.uid()));

-- INSERT: All org users can create logs
CREATE POLICY "{table}_insert" ON {table}
  FOR INSERT WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));

-- NO UPDATE or DELETE policies - logs are immutable
```

**Tables using this pattern:**

* `pf_audit_logs`, `pf_index_cleanup_audit`
* `hr_document_access_logs`, `hr_payroll_audit_log`, `hr_ssn_access_log`
* `fw_approval_history`, `fw_domain_events`, `fw_signature_audit_log`
* `fm_asset_maintenance_history`, `fm_work_order_history`

### Pattern D: User-Owned (Personal Data)

For tables where users own their own records.

```sql theme={null}
-- SELECT: Users can view their own records
CREATE POLICY "{table}_select" ON {table}
  FOR SELECT USING (auth.uid() = user_id);

-- INSERT: Users can create their own records
CREATE POLICY "{table}_insert" ON {table}
  FOR INSERT WITH CHECK (auth.uid() = user_id);

-- UPDATE: Users can modify their own records
CREATE POLICY "{table}_update" ON {table}
  FOR UPDATE USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- DELETE: Users can delete their own records
CREATE POLICY "{table}_delete" ON {table}
  FOR DELETE USING (auth.uid() = user_id);
```

**Tables using this pattern:**

* `pf_profiles`, `pf_object_favorites`, `pf_notification_batches`
* `hr_employee_preferences`

### Pattern E: Version/History (Immutable)

For tables storing historical versions - SELECT + INSERT only.

```sql theme={null}
-- SELECT: All org users can view versions
CREATE POLICY "{table}_select" ON {table}
  FOR SELECT USING ({core}_has_org_access(organization_id, auth.uid()));

-- INSERT: System/service can create versions
CREATE POLICY "{table}_insert" ON {table}
  FOR INSERT WITH CHECK ({core}_has_org_access(organization_id, auth.uid()));

-- NO UPDATE or DELETE - versions are immutable
```

**Tables using this pattern:**

* `pf_document_versions`, `fw_form_versions`, `fw_workflow_versions`
* `gr_policy_versions`, `lo_knowledge_article_versions`

### Pattern F: Service-Only (System Tables)

For tables only accessible via service\_role key.

```sql theme={null}
-- Deny all user access - only service_role can access
CREATE POLICY "{table}_deny_all" ON {table}
  FOR ALL USING (false);
```

**Tables using this pattern:**

* `pf_integration_credentials`

***

## Helper Functions by Module

| Module | Has Org Access                       | Is Org Admin                       | Is Module Admin                               |
| ------ | ------------------------------------ | ---------------------------------- | --------------------------------------------- |
| PF     | `pf_has_org_access(org_id, user_id)` | `pf_is_org_admin(user_id, org_id)` | `pf_is_platform_admin(user_id)`               |
| HR     | `hr_has_org_access(org_id, user_id)` | `hr_is_org_admin(org_id, user_id)` | -                                             |
| FA     | `fa_has_org_access(org_id, user_id)` | `fa_is_org_admin(org_id, user_id)` | `fa_is_finance_admin(user_id, org_id)`        |
| CE     | `ce_has_org_access(org_id, user_id)` | `ce_is_org_admin(org_id, user_id)` | Permission-based admin via `ce.%.admin` grant |
| FW     | `fw_has_org_access(org_id, user_id)` | `fw_is_org_admin(org_id, user_id)` | -                                             |
| FM     | `fm_has_org_access(org_id, user_id)` | `fm_is_org_admin(org_id, user_id)` | -                                             |
| GR     | `gr_has_org_access(org_id, user_id)` | `gr_is_org_admin(org_id, user_id)` | `gr_is_compliance_officer(...)`               |
| RH     | `rh_has_org_access(org_id, user_id)` | -                                  | -                                             |
| LO     | `lo_has_org_access(org_id, user_id)` | -                                  | -                                             |

**CE admin behavior:** `ce_is_org_admin` is `SECURITY DEFINER` and returns true when the caller is an org admin **or** has any granted permission key matching `ce.%.admin`. Use this permission-based admin check for CE workflows that delegate admin responsibilities via module permissions. Use role-only `*_is_org_admin` checks when delegated module-admin permissions are not part of the access model.

**Note:** Parameter order can vary by helper (for example `pf_is_org_admin(user_id, org_id)` vs most module helpers `(...org_id, ...user_id)`) - always check function signature.

***

## Verification Queries

### Check Tables Without RLS

```sql theme={null}
SELECT tablename 
FROM pg_tables 
WHERE schemaname = 'public' 
  AND tablename NOT IN (SELECT tablename FROM pg_policies WHERE schemaname = 'public')
ORDER BY tablename;
```

### Check Policy Coverage

```sql theme={null}
SELECT tablename, COUNT(*) as policy_count
FROM pg_policies 
WHERE schemaname = 'public'
GROUP BY tablename
ORDER BY policy_count, tablename;
```

### Check for Recursion Risks

```sql theme={null}
SELECT tablename, policyname
FROM pg_policies 
WHERE schemaname = 'public' 
  AND (qual::text LIKE '%pf_user_roles%' OR with_check::text LIKE '%pf_user_roles%');
```

### Check UPDATE Policies Without WITH CHECK

```sql theme={null}
SELECT tablename, policyname
FROM pg_policies
WHERE schemaname = 'public'
  AND cmd = 'UPDATE'
  AND with_check IS NULL;
```

***

## Migration Checklist

When adding RLS to a new table:

* [ ] Enable RLS: `ALTER TABLE {table} ENABLE ROW LEVEL SECURITY;`
* [ ] Add SELECT policy with org access check
* [ ] Add INSERT policy with org access check
* [ ] Add UPDATE policy with BOTH `USING` AND `WITH CHECK`
* [ ] Add DELETE policy (usually admin-only)
* [ ] Use SECURITY DEFINER helper functions (never direct queries)
* [ ] Add RLS tests in `tests/rls/`

***

## See Also

* [Database Development Guide](../development/DATABASE_DEVELOPMENT_GUIDE.md)
* [RLS Policy Audit](./RLS_POLICY_AUDIT.md)
* [Constitution §5.7](../../constitution.md) - RLS Requirements