Encore OS — Documentation

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

Overview

This guide explains the automated RLS (Row Level Security) validation integrated into our CI/CD pipeline. These checks prevent security regressions and ensure all database changes follow multi-tenant isolation best practices.

Quick Reference

Local Commands

# Lint migration files (static analysis)
npx tsx scripts/lint-rls-migration.ts

# Lint specific file
npx tsx scripts/lint-rls-migration.ts supabase/migrations/20260111_my_migration.sql

# Lint staged files only
npx tsx scripts/lint-rls-migration.ts --staged

# Full RLS validation (requires database connection)
npx tsx scripts/validate-rls-policies.ts

# CI mode (JSON output, strict exit codes)
npx tsx scripts/validate-rls-policies.ts --ci --fail-on-error

What Gets Checked

Check	Type	Blocks CI	Description
Missing RLS	Error	✅ Yes	Tables without `ENABLE ROW LEVEL SECURITY`
Missing WITH CHECK	Error	✅ Yes	UPDATE policies without `WITH CHECK` clause
Recursion Risk	Error	✅ Yes	Policies querying `pf_user_roles` directly
Missing search_path	Error	✅ Yes	SECURITY DEFINER functions without `SET search_path`
Incomplete Policies	Warning	❌ No	Tables with < 4 policies (excludes audit tables)
Missing org_id Filter	Warning	❌ No	Policies not filtering by `organization_id`
FOR ALL Policy	Warning	❌ No	Using `FOR ALL` instead of specific CRUD policies

CI/CD Pipeline

Pipeline Stages

┌─────────────────┐     ┌─────────────────┐     ┌─────────────────┐
│  Migration Lint │ --> │  Unit Tests     │ --> │  RLS Validation │
│  (Static)       │     │  (Mocked)       │     │  (Live DB)      │
└─────────────────┘     └─────────────────┘     └─────────────────┘

Workflow Jobs

migration-lint - Runs on every PR that modifies supabase/migrations/*.sql
rls-policy-validation - Runs after migrations are applied (push to main or internal PRs)

Environment Variables Required

VITE_SUPABASE_URL: ${{ secrets.VITE_SUPABASE_URL }}
SUPABASE_SERVICE_ROLE_KEY: ${{ secrets.SUPABASE_SERVICE_ROLE_KEY }}

Pre-Commit Hook

The pre-commit hook automatically lints staged migration files:

# .husky/pre-commit (excerpt)
STAGED_SQL_FILES=$(git diff --cached --name-only --diff-filter=ACM | grep 'supabase/migrations/.*\.sql$' || true)
if [ -n "$STAGED_SQL_FILES" ]; then
  npx tsx scripts/lint-rls-migration.ts $STAGED_SQL_FILES
fi

Bypassing (Emergency Only)

git commit --no-verify -m "Emergency fix"

⚠️ Warning: Bypassing pre-commit hooks will still fail CI. Only use for emergencies.

Common Failures & Fixes

Error: CREATE TABLE without RLS

Failure:

❌ Line 5: CREATE TABLE without ENABLE ROW LEVEL SECURITY: hr_new_table

Fix:

CREATE TABLE hr_new_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES pf_organizations(id),
  -- ... columns
);

-- ADD THIS:
ALTER TABLE hr_new_table ENABLE ROW LEVEL SECURITY;

-- Then add policies...

Error: UPDATE policy without WITH CHECK

Failure:

❌ Line 25: UPDATE policy without WITH CHECK clause: hr_new_table_update

Fix:

-- WRONG:
CREATE POLICY "hr_new_table_update" ON hr_new_table
  FOR UPDATE USING (hr_has_org_access(organization_id, auth.uid()));

-- CORRECT:
CREATE POLICY "hr_new_table_update" ON hr_new_table
  FOR UPDATE 
  USING (hr_has_org_access(organization_id, auth.uid()))
  WITH CHECK (hr_has_org_access(organization_id, auth.uid()));

Error: Direct pf_user_roles query (Recursion Risk)

Failure:

❌ Line 30: Policy directly queries pf_user_roles (recursion risk)

Fix:

-- WRONG: Direct query 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()));

Error: Missing search_path on SECURITY DEFINER

Failure:

❌ Line 10: SECURITY DEFINER function without SET search_path: my_helper

Fix:

-- WRONG:
CREATE FUNCTION my_helper(org_id uuid, user_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER
AS $$ ... $$;

-- CORRECT:
CREATE FUNCTION my_helper(org_id uuid, user_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER
SET search_path = public  -- ADD THIS
AS $$ ... $$;

Excluding Tables from Validation

Some tables intentionally have fewer than 4 policies:

Audit/Log Tables (Append-Only)

Only SELECT + INSERT policies (no UPDATE/DELETE)
Examples: pf_audit_logs, hr_payroll_audit_log, fw_approval_history

Version Tables (Immutable)

Only SELECT + INSERT policies
Examples: fw_form_versions, pf_document_versions

System Tables (Deny-All)

Only service_role access
Examples: pf_integration_credentials

To add a table to the exclusion list, update scripts/validate-rls-policies.ts:

const EXCLUDED_TABLES = [
  // ... existing tables
  'my_new_audit_table',
];

Troubleshooting

”Missing environment variables”

Ensure these are set:

export VITE_SUPABASE_URL="https://your-project.supabase.co"
export SUPABASE_SERVICE_ROLE_KEY="your-service-role-key"

“RPC function does not exist”

The validation script relies on helper functions. Run the migration that adds them:

-- Creates: get_update_policies_without_with_check()
-- Creates: get_policies_with_recursion_risk()
-- Creates: get_policies_without_org_filter()

CI passes but local fails

Local and CI may have different database states. Ensure:

You’ve applied all migrations locally
Your local Supabase matches the CI environment

Adding New Validation Rules

Add rule definition in scripts/lint-rls-migration.ts:

const RULES = {
  MY_NEW_RULE: {
    severity: 'error',
    message: 'Description of the issue',
    suggestion: 'How to fix it',
  },
};

Add detection logic in lintFile() function
Add documentation in this guide
Test with existing migrations to avoid false positives

Reports

CI generates a JSON report at reports/rls-validation.json:

{
  "timestamp": "2026-01-11T12:00:00.000Z",
  "passed": true,
  "results": [...],
  "summary": {
    "total_checks": 5,
    "passed_checks": 5,
    "errors": 0,
    "warnings": 0
  }
}

Reports are retained as CI artifacts for 30 days.

​Overview

​Quick Reference

​Local Commands

​What Gets Checked

​CI/CD Pipeline

​Pipeline Stages

​Workflow Jobs

​Environment Variables Required

​Pre-Commit Hook

​Bypassing (Emergency Only)

​Common Failures & Fixes

​Error: CREATE TABLE without RLS

​Error: UPDATE policy without WITH CHECK

​Error: Direct pf_user_roles query (Recursion Risk)

​Error: Missing search_path on SECURITY DEFINER

​Excluding Tables from Validation

​Audit/Log Tables (Append-Only)

​Version Tables (Immutable)

​System Tables (Deny-All)

​Troubleshooting

​”Missing environment variables”

​“RPC function does not exist”

​CI passes but local fails

​Adding New Validation Rules

​Reports

​See Also

Overview

Quick Reference

Local Commands

What Gets Checked

CI/CD Pipeline

Pipeline Stages

Workflow Jobs

Environment Variables Required

Pre-Commit Hook

Bypassing (Emergency Only)

Common Failures & Fixes

Error: CREATE TABLE without RLS

Error: UPDATE policy without WITH CHECK

Error: Direct pf_user_roles query (Recursion Risk)

Error: Missing search_path on SECURITY DEFINER

Excluding Tables from Validation

Audit/Log Tables (Append-Only)

Version Tables (Immutable)

System Tables (Deny-All)

Troubleshooting

”Missing environment variables”

“RPC function does not exist”

CI passes but local fails

Adding New Validation Rules

Reports

See Also