Skip to main content

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.

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

ModuleHas Org AccessIs Org AdminIs Module Admin
PFpf_has_org_access(org_id, user_id)pf_is_org_admin(user_id, org_id)pf_is_platform_admin(user_id)
HRhr_has_org_access(org_id, user_id)hr_is_org_admin(org_id, user_id)-
FAfa_has_org_access(org_id, user_id)fa_is_org_admin(org_id, user_id)fa_is_finance_admin(user_id, org_id)
CEce_has_org_access(org_id, user_id)ce_is_org_admin(org_id, user_id)Permission-based admin via ce.%.admin grant
FWfw_has_org_access(org_id, user_id)fw_is_org_admin(org_id, user_id)-
FMfm_has_org_access(org_id, user_id)fm_is_org_admin(org_id, user_id)-
GRgr_has_org_access(org_id, user_id)gr_is_org_admin(org_id, user_id)gr_is_compliance_officer(...)
RHrh_has_org_access(org_id, user_id)--
LOlo_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/

​
See Also