Design RLS Policy Schema for Multi-Tenant Isolation

epic-bufdir-data-aggregation-foundation-task-001 — 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.

critical priority high complexity database pending database specialist Tier 0

Acceptance Criteria

Produced artifact is a design document (RLS_POLICY_DESIGN.md) covering: org_id propagation strategy, session variable approach, policy templates per table, and role grant matrix

Session variable strategy is documented: how app.current_org_id is set at the start of each request (e.g., via Supabase Edge Function using SET LOCAL or via custom JWT claims extracted in a policy)

Policy templates are defined for all data-bearing tables: activities, user_roles, contacts, assignments, components, and any others identified during design

Each policy template specifies: USING clause, WITH CHECK clause, applicable roles (peer_mentor, coordinator, admin, service_role), and whether the policy applies to SELECT / INSERT / UPDATE / DELETE

The design document includes a threat model section listing at least 5 cross-tenant leakage scenarios and how each is prevented by the proposed policies

Role grant matrix table shows which PostgreSQL roles can perform which operations on which tables — no ambiguous grants

The design explicitly addresses the four organizations (NHF, Blindeforbundet, HLF, Barnekreftforeningen) and confirms the schema is organization-agnostic (org_id UUID as the isolation key, not hard-coded org names)

Migration scaffold (SQL file with TODO comments) is produced — actual policy SQL can be placeholder but table names and USING clause structure must be correct

Design is reviewed for GDPR compliance: personal data in each table is identified and the RLS policy prevents unauthorized access to that data

The design document is committed to the repository under docs/architecture/rls-policy-design.md

Technical Requirements

frameworks

Dart

Flutter

apis

Supabase PostgreSQL 15

Supabase Edge Functions (Deno)

Supabase Auth

data models

activity

contact

assignment

activity_type

bufdir_export_audit_log

bufdir_column_schema

annual_summary

certification

performance requirements

RLS policies must use indexed columns (org_id) in USING clauses to avoid full table scans

current_setting() calls in USING clauses must not be repeated per-row — PostgreSQL evaluates them once per statement for non-volatile functions

Policy design must not require per-row subqueries unless absolutely necessary

security requirements

Zero cross-tenant data leakage: a JWT for org_A must never return rows belonging to org_B under any query

Service role key bypasses RLS by default — document which Edge Functions use service role and ensure they set app.current_org_id explicitly before any query

BYPASSRLS must not be granted to any role accessible from the Flutter mobile client

The design must specify that RLS is ENABLED (not just defined) on every table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY

FORCE ROW LEVEL SECURITY must be applied to prevent table owners from bypassing policies

Design must address the scenario where a user belongs to multiple organizations and how the session variable is scoped to a single org per request

Execution Context

Execution Tier

Tier 0

Tier 0 - 440 tasks

View Full Execution Plan

Implementation Notes

Start by listing all Supabase tables and grouping them by sensitivity tier: (1) PII tables (contacts, assignments), (2) financial tables (activities with expenses), (3) audit tables (bufdir_export_audit_log), (4) configuration tables (activity_type, bufdir_column_schema). Apply stricter policies to higher-sensitivity tiers. For the session variable approach, prefer JWT claim extraction (`(auth.jwt() -> 'app_metadata' ->> 'org_id')::uuid`) over `current_setting('app.current_org_id')` where possible — JWT claims are cryptographically verified and cannot be spoofed by SET commands from untrusted clients. Document the trade-off.

For service role Edge Functions, use `SET LOCAL app.current_org_id = $1` within a transaction to scope org context. Reference Supabase's official RLS documentation and the existing codebase's `database.ts` for any already-defined policies.

Testing Requirements

This task produces a design document and migration scaffold, not runnable code. Validation criteria: (1) Design document reviewed by at least one other team member before task-002 starts, (2) Migration scaffold SQL is parseable by PostgreSQL without syntax errors (run psql --set ON_ERROR_STOP=1 on the scaffold file to verify syntax), (3) All tables identified in the data models section of CLAUDE.md appear in the policy template list — flag any missing tables. The threat model section is reviewed for completeness against OWASP's multi-tenancy threat list.

Component

Multi-Organization Data Isolator

data medium

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