Write PostgreSQL Migration for RLS Policies

epic-bufdir-data-aggregation-foundation-task-002 — Implement the PostgreSQL migration file that creates and enables Row Level Security policies on all relevant tables (activities, user_stories, components, etc.). Include USING and WITH CHECK clauses tied to app.current_org_id, grant policies per role (peer_mentor, coordinator, admin), and include a rollback strategy. Validate that no SELECT, INSERT, UPDATE, or DELETE can cross organization boundaries.

critical priority high complexity database pending database specialist Tier 1

Acceptance Criteria

Migration file is a single .sql file following the project's existing migration naming convention (e.g., YYYYMMDDHHMMSS_rls_policies.sql)

Migration enables RLS on every data-bearing table: ALTER TABLE <table> ENABLE ROW LEVEL SECURITY and ALTER TABLE <table> FORCE ROW LEVEL SECURITY

Each table has separate CREATE POLICY statements for SELECT, INSERT, UPDATE, and DELETE operations

All USING clauses reference org_id using the JWT claim approach: (auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid = org_id

WITH CHECK clauses on INSERT and UPDATE match the USING clause to prevent writing to a different org's rows

Role-specific policies: peer_mentor role has SELECT-only on contacts and assignments; coordinator role has SELECT + INSERT + UPDATE; admin role has full CRUD on all tables

Service role is granted BYPASSRLS implicitly (PostgreSQL default) — this is documented in a comment, not as an explicit grant

Rollback strategy: a corresponding _rollback.sql file drops all created policies and disables RLS on all affected tables

Migration runs to completion without errors on a clean PostgreSQL 15 staging database

Migration is idempotent: running it twice does not produce duplicate policies (use CREATE POLICY IF NOT EXISTS or DROP POLICY IF EXISTS before CREATE POLICY)

After migration applied, a cross-org data leakage test (run as a non-service-role user with org_A JWT) confirms zero rows returned from org_B tables

Technical Requirements

frameworks

Dart

Flutter

apis

Supabase PostgreSQL 15

Supabase Auth (JWT claims)

Supabase Edge Functions (Deno)

data models

activity

contact

assignment

activity_type

bufdir_export_audit_log

bufdir_column_schema

annual_summary

certification

badge_definition

claim_event

confidentiality_declaration

contact_chapter

declaration_acknowledgement

device_token

accessibility_preferences

performance requirements

USING clause must use the org_id column which must be indexed — add CREATE INDEX IF NOT EXISTS on org_id for any table missing it

JWT claim extraction `auth.jwt()` is called once per statement by PostgreSQL planner — verify no per-row function call overhead

After migration, run EXPLAIN ANALYZE on a representative SELECT with org_id filter and confirm Index Scan (not Seq Scan) is used

security requirements

FORCE ROW LEVEL SECURITY applied to all tables so table owner cannot bypass policies

No policy grants access based on user_id alone — org_id must always be part of the USING clause

cross_org_test user (created in staging for testing) must be denied access to all rows outside its org after migration

Migration must not contain any hardcoded org UUIDs — all isolation is based on JWT claims dynamically

Migration SQL reviewed for SQL injection risk in policy expressions before deployment

The migration must not drop existing data or alter column types — it adds security policies only

Rollback file must be tested: apply migration, then rollback, then confirm RLS is fully disabled

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Structure the migration in sections: (1) Index creation for org_id columns, (2) Enable RLS per table, (3) Create policies per table per operation per role. Use a DO $$ BEGIN ... END $$ block with IF NOT EXISTS checks for idempotency. Prefer explicit policy names following a convention: {table}_{operation}_{role}_policy (e.g., activities_select_peer_mentor_policy).

For tables that legitimately need cross-org reads (e.g., a global configuration table), explicitly document the exception with a comment explaining why RLS is not applied or uses a permissive policy. Do not attempt to add RLS to Supabase internal tables (auth.users, storage.objects) — only to public schema tables. The rollback file should be a DROP POLICY IF EXISTS for each policy plus DISABLE ROW LEVEL SECURITY per table. Coordinate with the Edge Functions team to ensure SET LOCAL app.current_org_id is called before any service-role queries that touch these tables.

Testing Requirements

SQL-level verification tests (run via psql against staging): (1) Apply migration, log in as peer_mentor JWT for org_A, SELECT from activities — confirm only org_A rows returned, (2) Attempt INSERT into activities with org_id = org_B UUID as peer_mentor of org_A — confirm permission denied, (3) Attempt UPDATE on a contact row belonging to org_B as coordinator of org_A — confirm 0 rows affected (RLS silently filters), (4) Log in as admin of org_A — confirm full CRUD access to org_A rows and zero access to org_B rows, (5) Apply rollback, confirm RLS disabled (SELECT across all orgs succeeds), (6) Re-apply migration, confirm policies re-created correctly. All tests documented in a test script file alongside the migration.

Component

Multi-Organization Data Isolator

data medium

Dependencies (1)

Design and document the Row Level Security policy schema for strict per-organization data isolation. Define the org_id propagation strategy, session variable approach (app.current_org_id), and policy templates for the activities, user_roles, and related tables. Produce a migration scaffold that enforces zero cross-tenant data leakage for NHF, Blindeforbundet, HLF, and Barnekreftforeningen. epic-bufdir-data-aggregation-foundation-task-001

Epic Risks (3)

high impact medium prob security

Supabase RLS policies may not propagate correctly into RPC function execution context, causing org-scoping predicates to be silently ignored when the function is invoked with service_role key. This could lead to cross-org data exposure in production without any obvious error.

Mitigation & Contingency

Mitigation: Invoke all RPCs using the anon/authenticated key rather than service_role, write explicit WHERE org_id = auth.uid()::org_id predicates inside the RPC body as a secondary control, and include automated cross-org leakage tests in the CI pipeline from day one.

Contingency: If RLS bypass is discovered post-deployment, immediately revoke service_role usage in all aggregation paths and hotfix with explicit org_id parameters passed as function arguments validated server-side.

high impact medium prob dependency

Bufdir may update its official reporting category taxonomy between the mapping configuration being defined and the annual submission deadline. If the ActivityCategoryMappingConfig is compiled as a static Dart constant, it cannot be updated without an app release, potentially causing mapping failures that block submission.

Mitigation & Contingency

Mitigation: Store the mapping as a remote-configurable table (bufdir_category_mappings) in Supabase with a version field rather than as a hardcoded Dart constant. Fetch the current mapping at aggregation time so updates can be pushed without a new app release.

Contingency: If a mapping mismatch is detected during an active reporting cycle, coordinators can be temporarily directed to the manual Excel fallback while an emergency mapping update is pushed to the Supabase table.

high impact low prob technical

For large organisations like NHF with 1,400 local chapters and potentially tens of thousands of activity records per reporting period, the Supabase RPC aggregation query may exceed the default PostgREST statement timeout, causing the aggregation to fail with a 503 error.

Mitigation & Contingency

Mitigation: Add partial indexes on (organization_id, created_at) and (organization_id, activity_type_id) to the activities table before writing the RPC. Profile the query plan against a realistic fixture of 50,000 records during development and increase the statement_timeout setting for the RPC role if needed.

Contingency: Implement chunked aggregation fallback: split the period into monthly sub-ranges and aggregate each chunk client-side, merging results with UNION-style Dart logic before assembling the final payload.

Quick Links

All Tasks Execution Plan