Configure RLS Policy Manager for unit assignments

epic-organizational-hierarchy-management-assignment-aggregation-task-006 — Implement and configure Supabase Row Level Security policies governing access to unit_assignments and organization_units tables. Coordinators see only units within their scope, peer mentors see only their own assignments, national admins see the full tree. Integrate with the assignment repository so all queries automatically apply tenant-scoped filters.

critical priority high complexity infrastructure pending infrastructure specialist Tier 2

Acceptance Criteria

RLS is enabled on both unit_assignments and organization_units tables with no bypass for service role except in explicitly audited migration scripts

A peer mentor user can SELECT only rows in unit_assignments where user_id = auth.uid() — attempting to query another user's assignments returns an empty result set, not an error

A coordinator can SELECT all unit_assignments and organization_units within their assigned subtree (determined by a recursive CTE or ltree path match); nodes outside their subtree return no rows

A national admin (role = 'national_admin') can SELECT all rows in both tables with no filtering

INSERT and UPDATE on unit_assignments are restricted to coordinators acting within their scope and national admins; peer mentors cannot write to this table

DELETE on unit_assignments is restricted to national admins only

All RLS policies are implemented as Supabase SQL migration files versioned in the repository under supabase/migrations/

The Dart assignment repository does not add manual WHERE clauses for scoping — it relies entirely on RLS; removing an RLS policy must be the only way to widen access

Integration test: log in as each of the three roles using separate test accounts and assert the correct row visibility and write permissions for each

Technical Requirements

frameworks

Supabase (PostgreSQL RLS)

Dart

Flutter

apis

Supabase Auth (auth.uid(), auth.jwt() claims)

Supabase PostgREST

data models

UnitAssignment

OrganizationUnit

UserRole (enum: peer_mentor, coordinator, national_admin)

performance requirements

RLS policy evaluation for coordinator subtree query must not exceed 50ms for a tree of 1,400 nodes; use ltree extension or materialised path if recursive CTEs are too slow

Policies must not trigger N+1 queries — use set-based subtree membership checks

Add a GIN index on the path column if using ltree for subtree scoping

security requirements

No anon role access to unit_assignments or organization_units — all access requires authenticated JWT

Role claim used in RLS policies must be read from the JWT (app_metadata.role), not from a user-writable table, to prevent privilege escalation

Policies must be tested against a user attempting to forge a role claim — Supabase auth.jwt() is signed and cannot be tampered with client-side

Enable audit logging (pg_audit or Supabase audit table) for all DELETE operations on unit_assignments

Service role key must never be shipped in the Flutter app binary

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Use PostgreSQL ltree extension for efficient subtree membership checks in coordinator policies — store the materialized path (e.g., 'national.region_oslo.county_akershus.local_baerum') on each organization_units row. The coordinator RLS policy SELECT check becomes: path <@ (SELECT path FROM organization_units WHERE id = coordinator_root_unit_id). Store the coordinator's root unit ID in auth.jwt() under app_metadata.root_unit_id, set during user provisioning. For the national_admin SELECT policy use: (auth.jwt() ->> 'role') = 'national_admin'.

Define separate policies for SELECT, INSERT, UPDATE, DELETE rather than combining them — separate policies are easier to audit and modify independently. All policies go in a single migration file named YYYYMMDDHHMMSS_rls_unit_assignments.sql. Document each policy with a SQL comment explaining the business rule it enforces.

Testing Requirements

RLS policies must be tested with three distinct Supabase test user accounts (one per role) using the supabase_test helper or direct psql sessions with SET ROLE. Test matrix: for each role × operation (SELECT, INSERT, UPDATE, DELETE) × scope (own data, in-scope other, out-of-scope) assert the exact expected result (rows returned or permission denied). Write these as integration tests in the repository's test/integration/ directory, runnable against a local Supabase instance via supabase start. Also add a Dart integration test using flutter_test that boots a real Supabase client with each test user's credentials and asserts the repository methods return only permitted data.

Component

Unit Assignment Service

service medium

Dependencies (1)

Implement the Supabase-backed repository for the junction table linking users to organizational units. Provide create, read, update, delete operations for user-unit assignments, including queries for fetching all units a user belongs to and all users in a given unit. Enforce uniqueness constraints at the repository level. epic-organizational-hierarchy-management-assignment-aggregation-task-002

Epic Risks (3)

high impact medium prob technical

Recursive aggregation queries across four hierarchy levels (national → region → local) with 1,400 leaf nodes may be too slow for real-time dashboard requests, exceeding the 200ms target and causing spinner timeouts.

Mitigation & Contingency

Mitigation: Implement aggregation as a Supabase RPC using a single recursive CTE rather than multiple round-trip queries. Pre-compute aggregations nightly via a scheduled Edge Function and cache results. For real-time needs, aggregate only the immediate subtree on demand.

Contingency: Surface a 'Refreshing...' indicator and serve stale cached aggregations immediately. Queue an async recalculation and push updated data via Supabase Realtime when ready, avoiding blocking the admin dashboard.

medium impact medium prob scope

The 5-chapter limit and primary-assignment constraint are NHF-specific. Applying these rules globally may break HLF and Blindeforbundet configurations where different limits apply, requiring per-organization configuration that was not initially scoped.

Mitigation & Contingency

Mitigation: Make the maximum assignment count a configurable value stored in the organization's feature-flag or settings table rather than a hardcoded constant. Design the assignment service to read this limit at runtime per organization.

Contingency: Default the limit to a high value (e.g., 100) for organizations other than NHF, effectively making it non-restrictive, while keeping the enforcement logic intact for when per-org configuration is fully implemented.

medium impact low prob technical

The searchable parent dropdown in HierarchyNodeEditor must search across up to 1,400 units efficiently. Client-side filtering of the full hierarchy may be slow; server-side search adds complexity and latency.

Mitigation & Contingency

Mitigation: Use the in-memory hierarchy cache as the search corpus — since the cache already holds the flat unit list, client-side filtering with a debounced input is sufficient and avoids extra Supabase calls. Pre-build a search index on cache load.

Contingency: Cap the dropdown to showing the 50 most recently accessed units by default, with a 'search all' option that triggers a server-side full-text query. This keeps the common case fast while supporting edge cases.

Quick Links

All Tasks Execution Plan