🏠 Overview › ✅ Implementation Tasks ›✅ epic-external-system-integration-configuration-foundation-task-003

Implement RLS policies for organization_integrations

epic-external-system-integration-configuration-foundation-task-003 — Write and apply Row Level Security policies on the organization_integrations table ensuring coordinators and admins can only read/write their own organization's integration records. Define separate policies for SELECT, INSERT, UPDATE, DELETE. Ensure service-role bypass for edge function access.

critical priority medium complexity database pending database specialist Tier 2

Acceptance Criteria

RLS is enabled on organization_integrations: ALTER TABLE organization_integrations ENABLE ROW LEVEL SECURITY
SELECT policy named 'org_members_can_read_own_integrations' allows users with role 'coordinator' or 'admin' to read rows where organization_id matches their organization from the auth JWT claims
INSERT policy named 'admins_can_create_integrations' restricts inserts to users with role 'admin' only, where organization_id matches the user's organization JWT claim
UPDATE policy named 'admins_can_update_integrations' restricts updates to users with role 'admin', scoped to their own organization
DELETE policy named 'admins_can_delete_integrations' restricts deletes to users with role 'admin', scoped to their own organization
Service role (used by Supabase Edge Functions) bypasses all RLS policies — verified by confirming service_role key can read/write any organization's rows
A user from organization A cannot read, insert, update, or delete rows belonging to organization B — verified by test with two distinct organization JWTs
Coordinator role can read integration records for their own organization but cannot insert, update, or delete them
Policies are applied in a migration file in supabase/migrations/ following the table creation migration

Technical Requirements

frameworks
Supabase
PostgreSQL
apis
Supabase Auth JWT
data models
OrganizationIntegration
UserRole
Organization
performance requirements
RLS policy expressions must use indexed columns only (organization_id) to avoid sequential scans
JWT claim extraction function must be stable and inlineable by the query planner
security requirements
Organization ID must be extracted from the verified JWT (auth.jwt() -> 'user_metadata' -> 'organization_id'), never from user-supplied request body
Role check must also read from JWT claims, not from a user-editable profile column
Ensure no policy accidentally uses PERMISSIVE mode where RESTRICTIVE is needed
Test with a freshly created user who has no role claim to confirm they receive zero rows

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Integration Task

Handles integration between different epics or system components. Requires coordination across multiple development streams.

Implementation Notes

Supabase stores JWT metadata in auth.jwt(). The organization_id is typically stored under raw_user_meta_data.organization_id or app_metadata.organization_id depending on how the auth flow sets claims. Confirm the exact JWT path with the existing auth implementation before writing policies. Use a helper function get_my_org_id() RETURNS UUID that extracts the org claim once and is referenced in all four policies — this avoids duplicating the extraction logic and makes future path changes a one-line fix.

For role checking, use (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin' style expressions. Avoid using the public.users table in RLS policies as it creates a recursive dependency risk. Document the exact JWT structure expected by the policies in the migration file header comment.

Testing Requirements

RLS tests using Supabase local environment with supabase test db or a dedicated test migration that inserts fixture data and impersonates users via SET LOCAL role and SET LOCAL request.jwt.claims. Test matrix: (1) admin of org A can SELECT own org rows. (2) admin of org A cannot SELECT org B rows. (3) coordinator of org A can SELECT own org rows.

(4) coordinator of org A cannot INSERT a new integration. (5) admin of org A can INSERT into own org. (6) admin of org A cannot INSERT into org B. (7) service_role can SELECT all rows regardless of organization.

(8) unauthenticated request returns zero rows. All 8 test cases must pass before this task is closed.

Component
Organization Integration Repository
data medium
Dependencies (1)
Design and write the Supabase migration for the organization_integrations table including all columns: id, organization_id (FK), integration_type (enum referencing registry), status, field_mappings (JSONB), sync_schedule, excluded_features (JSONB array), created_at, updated_at. Include all necessary indexes for org-scoped queries. epic-external-system-integration-configuration-foundation-task-002
Epic Risks (3)
high impact medium prob technical

Supabase Vault API has limited documentation for Dart/Flutter clients; wrapping it correctly for credential rotation and secret reference management may require significant trial and error, delaying the vault component and blocking all downstream credential-dependent work.

Mitigation & Contingency

Mitigation: Spike the Vault integration in the first sprint using a minimal proof-of-concept (store, retrieve, rotate one secret). Document the API surface before building the full vault client. Identify any missing Dart SDK bindings early.

Contingency: If Supabase Vault is too complex, fall back to Supabase's encrypted column approach (pgcrypto) for credential storage as a temporary measure, with a planned migration path to Vault once the API is understood.

high impact low prob security

Incorrect RLS policy configuration on organization_integrations could allow org admins of one organization to read or modify another organization's integration credentials, creating a serious data breach and compliance violation.

Mitigation & Contingency

Mitigation: Write integration tests that explicitly attempt cross-org data access using different JWT tokens and assert 0 rows returned. Include RLS policy review in PR checklist. Use Supabase's local development stack for policy validation before deployment.

Contingency: If a breach is discovered post-deployment, immediately revoke all integration credentials, rotate vault secrets, notify affected organizations, and apply emergency RLS patches.

medium impact medium prob technical

JSONB columns for field_mappings and sync_schedule lack database-level schema enforcement; AI-generated or malformed JSON could silently corrupt integration configurations, causing export failures that are hard to diagnose.

Mitigation & Contingency

Mitigation: Define TypeScript/Dart model classes with strict deserialization and validation. Add database check constraints or triggers that validate JSONB structure at write time. Version the JSONB schema to enable forward-compatible migrations.

Contingency: Build a repair script that scans organization_integrations for invalid JSONB and resets corrupted records to a safe default state, alerting the admin of the affected organization.

Quick Links
All Tasks Execution Plan