Encore OS — Documentation

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 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.

-- ❌ 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.

-- ❌ 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.

-- 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.

-- 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.

-- 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.

-- 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.

-- 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.

-- 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

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

SELECT tablename, COUNT(*) as policy_count
FROM pg_policies 
WHERE schemaname = 'public'
GROUP BY tablename
ORDER BY policy_count, tablename;

Check for Recursion Risks

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

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/

Start Here

Module Engineering Snapshots

Canonical AI Governance

Architecture

Integrations

Development

Decision Guides

Database

Module Reference

Testing

Migration

Operations

Governance & Security

Platform Internals

RLS Policy Patterns Reference

Overview

Core Principles

1. SECURITY DEFINER Helper Functions

2. WITH CHECK on UPDATE Policies

3. Multi-Tenant Isolation

4. Defense in Depth

Standard Patterns

Pattern A: Full CRUD (Business Entities)

Pattern B: Admin-Managed (Settings/Config)

Pattern C: Audit/Log (Append-Only)

Pattern D: User-Owned (Personal Data)

Pattern E: Version/History (Immutable)

Pattern F: Service-Only (System Tables)

Helper Functions by Module

Verification Queries

Check Tables Without RLS

Check Policy Coverage

Check for Recursion Risks

Check UPDATE Policies Without WITH CHECK

Migration Checklist

See Also

​Overview

​Core Principles

​1. SECURITY DEFINER Helper Functions

​2. WITH CHECK on UPDATE Policies

​3. Multi-Tenant Isolation

​4. Defense in Depth

​Standard Patterns

​Pattern A: Full CRUD (Business Entities)

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

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

​Pattern D: User-Owned (Personal Data)

​Pattern E: Version/History (Immutable)

​Pattern F: Service-Only (System Tables)

​Helper Functions by Module

​Verification Queries

​Check Tables Without RLS

​Check Policy Coverage

​Check for Recursion Risks

​Check UPDATE Policies Without WITH CHECK

​Migration Checklist

​See Also

Overview

Core Principles

1. SECURITY DEFINER Helper Functions

2. WITH CHECK on UPDATE Policies

3. Multi-Tenant Isolation

4. Defense in Depth

Standard Patterns

Pattern A: Full CRUD (Business Entities)

Pattern B: Admin-Managed (Settings/Config)

Pattern C: Audit/Log (Append-Only)

Pattern D: User-Owned (Personal Data)

Pattern E: Version/History (Immutable)

Pattern F: Service-Only (System Tables)

Helper Functions by Module

Verification Queries

Check Tables Without RLS

Check Policy Coverage

Check for Recursion Risks

Check UPDATE Policies Without WITH CHECK

Migration Checklist

See Also