Row-Level Security (RLS) Policies

Consolidated reference for all RLS policies across the system.

SQL is illustrative

SQL fragments in this document are examples meant to convey shape and intent — they're not authoritative reproductions of the production schema. The real migrations live in services/api/migrations/core/.

Overview

Every table (except migrations) has Row-Level Security enabled. Policies enforce tenant isolation and role-based access at the database level, not application level.

Key principles:

Two-pool architecture: The Core API uses two PostgreSQL connection pools. Superadmins use the AdminPool (restartix — table owner, bypasses RLS entirely). All other principals and public endpoints use the AppPool (restartix_app — restricted role, RLS enforced).
Org-scoped: Most tables filter by organization_id = current_app_org_id()
Principal-scoped: Some tables filter by principal_id = current_app_principal_id() (or, for human-only columns, human_id = current_app_principal_id()) or by current_human_patient_profile_ids() for portable patient profiles.
Permission-based, never role-string: Capability gates use current_app_has_permission(resource, action). Don't write current_app_role() = 'admin' or current_app_role() IN (...) — see rbac-permissions.md. Roles are human-facing labels; permissions are the authorization primitive. Custom per-org roles can hold any subset of permissions, so a role-string compare locks the policy to today's seeded roles.
Superadmin bypass: Superadmins bypass RLS via the owner pool (AdminPool), not via is_superadmin() in policies. No RLS policy references is_superadmin().
Public access: Public endpoints (e.g., org resolve) run on AdminPool with handler-side column allow-listing. There are no public RLS carve-out policies — AppPool with no session vars sees zero rows on every authenticated table.

Session Variables

RLS policies read from these PostgreSQL session variables (set by middleware on every request):

Variable	Type	Set By	Used By
`app.current_principal_id`	UUID	OrganizationContext middleware	Principal-scoped policies
`app.current_actor_type`	TEXT	OrganizationContext middleware	Actor-type-aware policies (`'human' \| 'agent' \| 'service_account' \| 'system'`)
`app.current_org_id`	UUID	OrganizationContext middleware	Org-scoped policies
`app.current_role`	TEXT	OrganizationContext middleware	Set by the wrappers for diagnostic logging; no longer read by any RLS policy

Helper Functions

sql

CREATE OR REPLACE FUNCTION current_app_principal_id() RETURNS UUID AS $$
    SELECT NULLIF(current_setting('app.current_principal_id', true), '')::UUID;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION current_app_principal_type() RETURNS TEXT AS $$
    SELECT NULLIF(current_setting('app.current_actor_type', true), '');
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION current_app_org_id() RETURNS UUID AS $$
    SELECT NULLIF(current_setting('app.current_org_id', true), '')::UUID;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION current_app_role() RETURNS TEXT AS $$
    SELECT NULLIF(current_setting('app.current_role', true), '');
$$ LANGUAGE SQL STABLE;

STABLE keyword: Tells PostgreSQL the function won't change during the transaction, enabling index usage.

Note: There is no is_superadmin() function. Superadmins bypass RLS entirely by using the AdminPool (owner role), not via policy checks.

Policy Patterns

Pattern 1: Org-Scoped (Most Common)

Example: appointments, patients, forms, etc.

sql

ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

CREATE POLICY appointments_org_isolation ON appointments
    USING (organization_id = current_app_org_id());

Access:

Patient/Specialist/Admin (AppPool): See only their organization's data
Superadmin (AdminPool): Bypasses RLS entirely — sees all data

Applies to: appointments, patients, specialists, forms, form_templates, segments, custom_fields, webhooks, documents

Note: No is_superadmin() in policies. Superadmins use the AdminPool (table owner) which bypasses all RLS. This applies to every policy pattern below.

Pattern 2: Principal-Scoped

Example: humans table (multi-org membership)

sql

ALTER TABLE humans ENABLE ROW LEVEL SECURITY;

CREATE POLICY humans_own_record ON humans
    USING (
        principal_id = current_app_principal_id()
        OR EXISTS (
            SELECT 1 FROM organization_memberships
            WHERE principal_id = humans.principal_id
              AND organization_id = current_app_org_id()
        )
    );

Access:

Human (AppPool): See own record
Admin (AppPool): See humans in their organization (via organization_memberships join)
Superadmin (AdminPool): Bypasses RLS — sees all humans

Pattern 3: Permission-gated + Org-Scoped + Ownership

Example: patients (org-patient link — staff see their org's patients, patients see their own)

sql

ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

-- Staff with patients.view_org see every patient in the org. Patients
-- (and managers) see their own record via the patient_profile ownership
-- helper. The two paths OR together — staff don't need ownership;
-- patients don't need the permission.
CREATE POLICY patients_access ON patients
    USING (
        organization_id = current_app_org_id()
        AND (
            current_app_has_permission('patients', 'view_org')
            OR patient_profile_id IN (SELECT current_human_patient_profile_ids())
        )
    );

Access:

Patient / manager (AppPool): See their own record and managed dependents' records (via current_human_patient_profile_ids())
Caller with patients.view_org (AppPool): Sees all patients in their organization (granted to specialist / customer_support / admin by default)
Superadmin (AdminPool): Bypasses RLS — sees all patients across all organizations

Pattern 4: Human-scoped without org (patient_profiles)

Example: patient_profiles (portable profile — no organization_id)

sql

ALTER TABLE patient_profiles ENABLE ROW LEVEL SECURITY;

-- The person themselves, their managers, or org staff (via patients JOIN)
CREATE POLICY patient_profiles_select ON patient_profiles FOR SELECT USING (
    id IN (SELECT current_human_patient_profile_ids())
    OR id IN (SELECT patient_profile_id FROM patients WHERE organization_id = current_app_org_id())
);

-- Only the person or their managers can update the portable profile
CREATE POLICY patient_profiles_update ON patient_profiles FOR UPDATE USING (
    id IN (SELECT current_human_patient_profile_ids())
);

RLS helper function (defined in services/api/migrations/core/000006_patient_identity.up.sql):

sql

-- Returns all patient_profile IDs the current human can act on behalf of
CREATE OR REPLACE FUNCTION current_human_patient_profile_ids() RETURNS SETOF UUID AS $$
    SELECT id FROM patient_profiles WHERE human_id = current_app_principal_id()
    UNION
    SELECT patient_profile_id FROM patient_caregivers WHERE caregiver_human_id = current_app_principal_id()
$$ LANGUAGE SQL STABLE;

Pattern 4: Private Field Filtering

Example: forms with private fields

Forms have private fields that are only visible to specialists/admins. This is handled in the application layer (Go service), not RLS.

Why? RLS operates at the row level, not column level. We can't filter JSONB fields at the database level without expensive queries.

Implementation: Field filtering is permission-driven, not role-string-driven, so a future custom role can hold the same capability without code changes.

// internal/domain/form/repository.go

func (r *Repository) Get(ctx context.Context, id uuid.UUID) (*Form, error) {
    principal := auth.PrincipalFromContext(ctx)
    form, err := r.fetchForm(ctx, id)
    if err != nil {
        return nil, err
    }

    // Org-staff path: caller can read any form in the org (including
    // private fields). Owner path: caller without forms.view_org can
    // read their own form, but private fields are stripped.
    if !principal.HasPermission("forms.view_org") {
        if form.OwnerPrincipalID != principal.PrincipalID {
            return nil, ErrForbidden
        }
        form.Values = filterPrivateFields(form.Fields, form.Values)
    }

    return form, nil
}

func filterPrivateFields(fields []FieldDef, values map[string]any) map[string]any {
    filtered := make(map[string]any)
    for _, field := range fields {
        if !field.Private {
            filtered[field.Key] = values[field.Key]
        }
    }
    return filtered
}

Policy Reference

Architecture note: All policies below are written for the AppPool (restartix_app role). Superadmins use the AdminPool (restartix owner role) which bypasses all RLS entirely. Therefore, no policy includes is_superadmin(). Policies for future features (not yet implemented) follow this same pattern — when those migrations are written, they will not include is_superadmin().

Core Features

humans

sql

CREATE POLICY humans_own_record ON humans
    USING (
        principal_id = current_app_principal_id()
        OR EXISTS (
            SELECT 1 FROM organization_memberships
            WHERE principal_id = humans.principal_id
              AND organization_id = current_app_org_id()
        )
    );

organization_memberships

sql

CREATE POLICY organization_memberships_access ON organization_memberships
    USING (
        organization_id = current_app_org_id()
    );

organizations

sql

CREATE POLICY organizations_access ON organizations
    USING (
        id = current_app_org_id()
    );

organization_integrations

sql

CREATE POLICY organization_integrations_access ON organization_integrations
    USING (
        organization_id = current_app_org_id()
    );

Appointments & Scheduling

appointments

sql

CREATE POLICY appointments_org_isolation ON appointments
    USING (organization_id = current_app_org_id());

appointment_files

sql

CREATE POLICY appointment_files_org_isolation ON appointment_files
    USING (
        organization_id = current_app_org_id()
    );

appointment_templates

sql

CREATE POLICY appointment_templates_org_isolation ON appointment_templates
    USING (organization_id = current_app_org_id());

appointment_template_forms

sql

CREATE POLICY appointment_template_forms_org_isolation ON appointment_template_forms
    USING (organization_id = current_app_org_id());

appointment_template_specialists

sql

CREATE POLICY appointment_template_specialists_org_isolation ON appointment_template_specialists
    USING (organization_id = current_app_org_id());

appointment_template_attachments

sql

CREATE POLICY appointment_template_attachments_org_isolation ON appointment_template_attachments
    USING (organization_id = current_app_org_id());

specialist_hours

sql

CREATE POLICY specialist_hours_org_isolation ON specialist_hours
    USING (organization_id = current_app_org_id());

specialist_date_overrides

sql

CREATE POLICY specialist_date_overrides_org_isolation ON specialist_date_overrides
    USING (organization_id = current_app_org_id());

Profiles

patient_profiles

sql

-- SELECT: the person themselves, their managers, or any org staff with the person as a patient
CREATE POLICY patient_profiles_select ON patient_profiles FOR SELECT USING (
    id IN (SELECT current_human_patient_profile_ids())
    OR id IN (SELECT patient_profile_id FROM patients WHERE organization_id = current_app_org_id())
);
-- UPDATE: only the person or their managers
CREATE POLICY patient_profiles_update ON patient_profiles FOR UPDATE USING (
    id IN (SELECT current_human_patient_profile_ids())
);
-- INSERT: any authenticated principal
CREATE POLICY patient_profiles_insert ON patient_profiles FOR INSERT WITH CHECK (
    current_app_principal_id() IS NOT NULL
);

patient_caregivers

sql

CREATE POLICY caregivers_select ON patient_caregivers FOR SELECT USING (
    caregiver_human_id = current_app_principal_id()
    OR patient_profile_id IN (SELECT id FROM patient_profiles WHERE human_id = current_app_principal_id())
);
CREATE POLICY caregivers_modify ON patient_caregivers FOR ALL USING (
    caregiver_human_id = current_app_principal_id()
);

patients

sql

-- Read: org staff with patients.view_org, plus the patient/manager themselves.
CREATE POLICY patients_select ON patients FOR SELECT USING (
    organization_id = current_app_org_id()
    AND (
        current_app_has_permission('patients', 'view_org')
        OR patient_profile_id IN (SELECT current_human_patient_profile_ids())
    )
);

-- Write: org staff with patients.update_org, plus the patient/manager
-- updating their own row. Patient self-update of the portable profile
-- happens on patient_profiles, not here.
CREATE POLICY patients_modify ON patients FOR ALL USING (
    organization_id = current_app_org_id()
    AND (
        current_app_has_permission('patients', 'update_org')
        OR patient_profile_id IN (SELECT current_human_patient_profile_ids())
    )
);

specialists

sql

CREATE POLICY specialists_org_isolation ON specialists
    USING (organization_id = current_app_org_id());

specialist_specialties

sql

CREATE POLICY specialist_specialties_org_isolation ON specialist_specialties
    USING (organization_id = current_app_org_id());

specialties

sql

CREATE POLICY specialties_org_isolation ON specialties
    USING (organization_id = current_app_org_id());

Forms System

forms

sql

CREATE POLICY forms_org_isolation ON forms
    USING (organization_id = current_app_org_id());

form_templates (part of forms feature)

sql

CREATE POLICY form_templates_org_isolation ON form_templates
    USING (organization_id = current_app_org_id());

form_template_versions

sql

CREATE POLICY form_template_versions_org_isolation ON form_template_versions
    USING (organization_id = current_app_org_id());

custom_fields

sql

CREATE POLICY custom_fields_org_isolation ON custom_fields
    USING (organization_id = current_app_org_id());

custom_field_values

sql

CREATE POLICY custom_field_values_org_isolation ON custom_field_values
    USING (organization_id = current_app_org_id());

segments

sql

CREATE POLICY segments_org_isolation ON segments
    USING (organization_id = current_app_org_id());

segment_members

sql

CREATE POLICY segment_members_org_isolation ON segment_members
    USING (organization_id = current_app_org_id());

segment_versions

sql

CREATE POLICY segment_versions_org_isolation ON segment_versions
    USING (organization_id = current_app_org_id());

Telerehabilitation

exercises (dual-scope: global + org)

sql

-- SELECT: Global exercises (org_id IS NULL) are visible to anyone with
-- exercises.view_published. Org exercises require both view_published
-- and an org match. Drafts (status != 'published') are restricted to
-- callers with exercises.manage_org.
CREATE POLICY exercises_select ON exercises FOR SELECT USING (
    (
        organization_id IS NULL
        OR organization_id = current_app_org_id()
    )
    AND (
        (status = 'published' AND deleted_at IS NULL
         AND current_app_has_permission('exercises', 'view_published'))
        OR current_app_has_permission('exercises', 'manage_org')
    )
);

-- INSERT/UPDATE/DELETE: org-scoped manage. Global exercises (org_id IS
-- NULL) can only be modified by superadmins (AdminPool bypass) — RLS
-- excludes them here.
CREATE POLICY exercises_insert ON exercises FOR INSERT WITH CHECK (
    organization_id = current_app_org_id()
    AND current_app_has_permission('exercises', 'manage_org')
);
CREATE POLICY exercises_update ON exercises FOR UPDATE USING (
    organization_id IS NOT NULL
    AND organization_id = current_app_org_id()
    AND current_app_has_permission('exercises', 'manage_org')
);

Dual-scope pattern: organization_id IS NULL in SELECT enables global visibility for anyone with exercises.view_published. UPDATE/DELETE explicitly exclude IS NULL for non-superadmins, so global exercises can only be edited via the AdminPool.

exercise_categories, exercise_body_regions, exercise_equipment

sql

-- Same dual-scope pattern as exercises. Read is open to any authenticated
-- caller (taxonomy is not sensitive); write requires exercises.manage_org
-- on an org-scoped row.
CREATE POLICY exercise_categories_select ON exercise_categories FOR SELECT USING (
    organization_id IS NULL OR organization_id = current_app_org_id()
);
CREATE POLICY exercise_categories_modify ON exercise_categories FOR ALL USING (
    organization_id IS NOT NULL
    AND organization_id = current_app_org_id()
    AND current_app_has_permission('exercises', 'manage_org')
);

exercise_tags, exercise_instructions, exercise_contraindications

sql

-- Sub-query through parent exercise (no organization_id column)
CREATE POLICY exercise_instructions_select ON exercise_instructions FOR SELECT USING (
    EXISTS (
        SELECT 1 FROM exercises e WHERE e.id = exercise_instructions.exercise_id
        AND (e.organization_id IS NULL
             OR e.organization_id = current_app_org_id())
    )
);

treatment_plans (dual-scope: global + org)

sql

-- SELECT: callers with treatment_plans.view_org see every plan in scope
-- (global + their org). Callers without it see only plans they can
-- execute (treatment_plans.execute_own_session) and only when published
-- + library-visible — that path covers the patient case without naming
-- the role.
CREATE POLICY treatment_plans_select ON treatment_plans FOR SELECT USING (
    (organization_id IS NULL OR organization_id = current_app_org_id())
    AND (
        current_app_has_permission('treatment_plans', 'view_org')
        OR (
            status = 'published'
            AND visibility = 'library'
            AND current_app_has_permission('treatment_plans', 'execute_own_session')
        )
    )
);

-- INSERT/UPDATE/DELETE: org-scoped manage. Global plans (org_id IS NULL)
-- can only be modified by superadmins (AdminPool bypass) — RLS excludes
-- them here.
CREATE POLICY treatment_plans_insert ON treatment_plans FOR INSERT WITH CHECK (
    organization_id = current_app_org_id()
    AND current_app_has_permission('treatment_plans', 'manage')
);
CREATE POLICY treatment_plans_update ON treatment_plans FOR UPDATE USING (
    organization_id IS NOT NULL
    AND organization_id = current_app_org_id()
    AND current_app_has_permission('treatment_plans', 'manage')
);
CREATE POLICY treatment_plans_delete ON treatment_plans FOR DELETE USING (
    organization_id IS NOT NULL
    AND organization_id = current_app_org_id()
    AND current_app_has_permission('treatment_plans', 'delete')
);

Dual-scope pattern: Same as exercises — organization_id IS NULL in SELECT enables global visibility for any caller with the relevant permission. UPDATE/DELETE explicitly require organization_id IS NOT NULL, so global plans can only be modified via the AdminPool.

treatment_plan_versions, treatment_plan_sessions, treatment_plan_session_exercises

sql

-- SELECT: org-scoped + global plan children (org_id IS NULL). Children
-- inherit the parent's read gate — `treatment_plans.view_org`.
CREATE POLICY tpv_select ON treatment_plan_versions FOR SELECT USING (
    (organization_id IS NULL OR organization_id = current_app_org_id())
    AND current_app_has_permission('treatment_plans', 'view_org')
);

-- INSERT/UPDATE/DELETE: org-scoped manage. Global plan children require
-- the AdminPool bypass (mirrors the parent table).
CREATE POLICY tpv_modify ON treatment_plan_versions FOR ALL USING (
    organization_id IS NOT NULL
    AND organization_id = current_app_org_id()
    AND current_app_has_permission('treatment_plans', 'manage')
);

Note: Child tables of treatment_plans mirror the parent's gate: callers with treatment_plans.view_org read global + org-scoped children; only callers with treatment_plans.manage (and an org match) can modify org children; global children require the AdminPool. The same policies apply to treatment_plan_sessions and treatment_plan_session_exercises.

patient_treatment_plans

sql

-- Read: callers with treatment_plans.view_org see all org assignments.
-- The patient/manager sees their own assignments via the patient_profile
-- ownership helper.
CREATE POLICY patient_treatment_plans_select ON patient_treatment_plans FOR SELECT USING (
    organization_id = current_app_org_id()
    AND (
        current_app_has_permission('treatment_plans', 'view_org')
        OR patient_id IN (
            SELECT id FROM patients
            WHERE organization_id = current_app_org_id()
              AND patient_profile_id IN (SELECT current_human_patient_profile_ids())
        )
    )
);

-- Write: callers with treatment_plans.assign can assign any patient.
-- A caller with treatment_plans.execute_own_session can self-assign a
-- published library plan to their own patient row (the patient case),
-- gated by self_assigned = TRUE.
CREATE POLICY patient_treatment_plans_insert ON patient_treatment_plans FOR INSERT WITH CHECK (
    organization_id = current_app_org_id()
    AND (
        current_app_has_permission('treatment_plans', 'assign')
        OR (
            self_assigned = TRUE
            AND current_app_has_permission('treatment_plans', 'execute_own_session')
            AND patient_id IN (
                SELECT id FROM patients
                WHERE organization_id = current_app_org_id()
                  AND patient_profile_id IN (SELECT current_human_patient_profile_ids())
            )
        )
    )
);

Patient self-assignment: Callers with treatment_plans.execute_own_session (granted to the patient template by default) can INSERT their own patient_treatment_plans rows when self_assigned = TRUE and patient_id matches their own patient record (resolved via current_human_patient_profile_ids()). This enables patients to add published library plans to their own treatment schedule without requiring specialist intervention.

patient_session_completions

sql

-- Org staff with treatment_plans.view_org see all session completions
-- in the org. The patient/manager sees their own via the assignment chain.
CREATE POLICY psc_select ON patient_session_completions FOR SELECT USING (
    organization_id = current_app_org_id()
    AND (
        current_app_has_permission('treatment_plans', 'view_org')
        OR EXISTS (
            SELECT 1 FROM patient_treatment_plans ptp
            JOIN patients p ON p.id = ptp.patient_id
            WHERE ptp.id = patient_session_completions.patient_treatment_plan_id
              AND p.organization_id = current_app_org_id()
              AND p.patient_profile_id IN (SELECT current_human_patient_profile_ids())
        )
    )
);

patient_exercise_logs

sql

-- Same shape as patient_session_completions, one join deeper.
CREATE POLICY pel_select ON patient_exercise_logs FOR SELECT USING (
    organization_id = current_app_org_id()
    AND (
        current_app_has_permission('treatment_plans', 'view_org')
        OR EXISTS (
            SELECT 1 FROM patient_session_completions psc
            JOIN patient_treatment_plans ptp ON ptp.id = psc.patient_treatment_plan_id
            JOIN patients p ON p.id = ptp.patient_id
            WHERE psc.id = patient_exercise_logs.session_completion_id
              AND p.organization_id = current_app_org_id()
              AND p.patient_profile_id IN (SELECT current_human_patient_profile_ids())
        )
    )
);

Services & Products

service_plan_products

sql

ALTER TABLE service_plan_products ENABLE ROW LEVEL SECURITY;

-- SELECT: org match (superadmins bypass RLS via AdminPool)
CREATE POLICY service_plan_products_select ON service_plan_products FOR SELECT USING (
    organization_id = current_app_org_id()
);

-- INSERT/UPDATE/DELETE: org match AND services.manage permission
CREATE POLICY service_plan_products_modify ON service_plan_products FOR ALL USING (
    organization_id = current_app_org_id()
    AND current_app_has_permission('services', 'manage')
);

Supporting Features

appointment_documents

sql

CREATE POLICY appointment_documents_org_isolation ON appointment_documents
    USING (organization_id = current_app_org_id());

appointment_document_files

sql

CREATE POLICY appointment_document_files_org_isolation ON appointment_document_files
    USING (organization_id = current_app_org_id());

webhook_subscriptions

sql

CREATE POLICY webhook_subscriptions_org_isolation ON webhook_subscriptions
    USING (organization_id = current_app_org_id());

webhook_events

sql

CREATE POLICY webhook_events_org_isolation ON webhook_events
    USING (organization_id = current_app_org_id());

audit_log

Append-only by design. The only policy is audit_select (gated on the audit_log.view_org permission). There is no INSERT / UPDATE / DELETE policy — with RLS enabled and no policy for an operation, Postgres denies it by default. Combined with the REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON audit_log FROM restartix_app block in 000001_init.up.sql, AppPool is locked out at both the grant layer and the RLS layer (double-deny). Production writers (audit.Recorder and the trigger-side audit_log_insert) run via AdminPool / SECURITY DEFINER, neither of which needs the AppPool grant.

sql

-- Read: the caller must be in the org and hold the `audit_log.view_org`
-- permission. Granted to the admin system role template by default; can be
-- granted to custom roles for a compliance-officer pattern.
CREATE POLICY audit_select ON audit_log FOR SELECT USING (
    organization_id = current_app_org_id()
    AND current_app_has_permission('audit_log', 'view_org')
);

-- No INSERT / UPDATE / DELETE policy. RLS denies by default; the REVOKE
-- below is the second layer.
REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON audit_log FROM restartix_app;

Testing RLS

Integration Tests

Set session variables manually:

func TestRLS(t *testing.T) {
    org1 := createOrg(t, "Org 1")
    org2 := createOrg(t, "Org 2")

    human1 := createHuman(t, org1.ID, "patient")
    human2 := createHuman(t, org2.ID, "patient")

    // Set RLS context for org 1
    conn := testDB.Acquire(ctx)
    defer conn.Release()

    conn.Exec(ctx, `
        SELECT set_config('app.current_principal_id', $1::text, true),
               set_config('app.current_actor_type', 'human', true),
               set_config('app.current_org_id', $2::text, true),
               set_config('app.current_role', $3, true)
    `, human1.PrincipalID, org1.ID, "patient")

    // Query appointments
    var count int
    conn.QueryRow(ctx, "SELECT COUNT(*) FROM appointments").Scan(&count)

    // Verify: Only org 1's appointments visible
    assert.Equal(t, org1AppointmentCount, count)
}

Manual Verification (psql)

sql

-- Set session vars
SELECT set_config('app.current_org_id', '1', true);
SELECT set_config('app.current_role', 'patient', true);

-- Query (RLS applies)
SELECT * FROM appointments;
-- Should only return org 1's appointments

-- Switch org
SELECT set_config('app.current_org_id', '2', true);
SELECT * FROM appointments;
-- Should only return org 2's appointments

Performance Considerations

Indexes Are Critical

Every table with organization_id must have an index:

sql

CREATE INDEX idx_{table}_org ON {table}(organization_id);

Without these, RLS queries become sequential scans (extremely slow).

Query Plan Caching

PostgreSQL caches query plans. RLS policies are baked into plans, so there's no per-query overhead for RLS filtering after the first execution.

Benchmark: RLS adds < 1ms to query time when indexes exist.

Security Guarantees

Tenant Isolation

Guaranteed by PostgreSQL, not application code:

Middleware sets app.current_org_id = 1
RLS policy: WHERE organization_id = current_app_org_id()
Query: SELECT * FROM appointments
PostgreSQL rewrites to: SELECT * FROM appointments WHERE organization_id = 1
Even if query has WHERE id = 999, RLS adds the org filter

Impossible scenarios:

❌ Org 1 sees Org 2's data (RLS blocks at DB level)
❌ Request without org context sees data (no current_app_org_id() → NULL → no matches)
❌ Superadmin org context pollutes patient request (session vars are transaction-scoped)

Transaction Scope

Session variables are set with set_config(..., true):

Third parameter = true: Transaction-scoped
Automatic cleanup: Variables reset when transaction ends
No cross-request pollution: Each request gets a clean connection

Session Management - Middleware stack, connection lifecycle
Database Overview - All tables
RBAC Permissions - Role-based access control
Features Index - Feature-by-feature documentation

Row-Level Security (RLS) Policies ​

Overview ​

Session Variables ​

Helper Functions ​

Policy Patterns ​

Pattern 1: Org-Scoped (Most Common) ​

Pattern 2: Principal-Scoped ​

Pattern 3: Permission-gated + Org-Scoped + Ownership ​

Pattern 4: Human-scoped without org (patient_profiles) ​

Pattern 4: Private Field Filtering ​

Policy Reference ​

Core Features ​

humans ​

organization_memberships ​

organizations ​

organization_integrations ​

Appointments & Scheduling ​

appointments ​

appointment_files ​

appointment_templates ​

appointment_template_forms ​

appointment_template_specialists ​

appointment_template_attachments ​

specialist_hours ​

specialist_date_overrides ​

Profiles ​

patient_profiles ​

patient_caregivers ​

patients ​

specialists ​

specialist_specialties ​

specialties ​

Forms System ​

forms ​

form_templates (part of forms feature) ​

form_template_versions ​

custom_fields ​

custom_field_values ​

segments ​

segment_members ​

segment_versions ​

Telerehabilitation ​

exercises (dual-scope: global + org) ​

exercise_categories, exercise_body_regions, exercise_equipment ​

exercise_tags, exercise_instructions, exercise_contraindications ​

treatment_plans (dual-scope: global + org) ​

treatment_plan_versions, treatment_plan_sessions, treatment_plan_session_exercises ​

patient_treatment_plans ​

patient_session_completions ​

patient_exercise_logs ​

Services & Products ​

service_plan_products ​

Supporting Features ​

appointment_documents ​

appointment_document_files ​

webhook_subscriptions ​

webhook_events ​

audit_log ​

Testing RLS ​

Integration Tests ​

Manual Verification (psql) ​

Performance Considerations ​

Indexes Are Critical ​

Query Plan Caching ​

Security Guarantees ​

Tenant Isolation ​

Transaction Scope ​

Related Documentation ​

Row-Level Security (RLS) Policies

Overview

Session Variables

Helper Functions

Policy Patterns

Pattern 1: Org-Scoped (Most Common)

Pattern 2: Principal-Scoped

Pattern 3: Permission-gated + Org-Scoped + Ownership

Pattern 4: Human-scoped without org (patient_profiles)

Pattern 4: Private Field Filtering

Policy Reference

Core Features

humans

organization_memberships

organizations

organization_integrations

Appointments & Scheduling

appointments

appointment_files

appointment_templates

appointment_template_forms

appointment_template_specialists

appointment_template_attachments

specialist_hours

specialist_date_overrides

Profiles

patient_profiles

patient_caregivers

patients

specialists

specialist_specialties

specialties

Forms System

forms

form_templates (part of forms feature)

form_template_versions

custom_fields

custom_field_values

segments

segment_members

segment_versions

Telerehabilitation

exercises (dual-scope: global + org)

exercise_categories, exercise_body_regions, exercise_equipment

exercise_tags, exercise_instructions, exercise_contraindications

treatment_plans (dual-scope: global + org)

treatment_plan_versions, treatment_plan_sessions, treatment_plan_session_exercises

patient_treatment_plans

patient_session_completions

patient_exercise_logs

Services & Products

service_plan_products

Supporting Features

appointment_documents

appointment_document_files

webhook_subscriptions

webhook_events

audit_log

Testing RLS

Integration Tests

Manual Verification (psql)

Performance Considerations

Indexes Are Critical

Query Plan Caching

Security Guarantees

Tenant Isolation

Transaction Scope

Related Documentation