🏠 Overview › ✅ Implementation Tasks ›✅ epic-admin-portal-foundation-task-013

Validate HLF certification-gated RLS visibility

epic-admin-portal-foundation-task-013 — Implement and test the HLF-specific RLS policy extension that suppresses peer mentor visibility when certification has expired. Write a Supabase policy condition that checks the certifications table expiry date and returns a boolean used as an RLS filter predicate. Verify against the existing certification-status-repository.

high priority medium complexity database pending database specialist Tier 4

Acceptance Criteria

A Supabase RLS SELECT policy exists on the peer_mentors (or equivalent) table that evaluates certification expiry for HLF-scoped rows
Peer mentor rows with an expired certification (expiry_date < now()) are excluded from SELECT results for all roles except super_admin
Peer mentor rows with a valid certification (expiry_date >= now()) remain visible to coordinators and org_admins within HLF's org scope
The policy uses a subquery or join against the certifications table — no denormalised expiry field on the peer_mentors table
Policy is wrapped in a migration file with a descriptive name (e.g., 20260001000000_hlf_certification_rls.sql)
A companion test (SQL or integration) seeds an expired and a valid certification and asserts correct visibility for each
The existing certification-status-repository Dart class reads the same certifications table without modification — RLS is the enforcement layer
Policy does not degrade query performance beyond 50ms p95 for a 1,000-row peer_mentors table (measured with EXPLAIN ANALYZE in local Supabase)
Policy applies only to the HLF organisation scope — NHF and Blindeforbundet peer mentors are unaffected

Technical Requirements

frameworks
Supabase (PostgreSQL RLS)
Flutter
flutter_test
apis
Supabase PostgREST (RLS-filtered queries)
Supabase set_claim RPC
data models
PeerMentor
Certification
Organisation
performance requirements
RLS policy subquery must use an indexed column (user_id + organisation_id) on certifications table
Query plan must show Index Scan, not Seq Scan, for certification lookup at scale
security requirements
Policy must be USING clause only (not WITH CHECK) — this is a read-visibility gate, not a write gate
Super_admin bypass must be explicit: `(auth.jwt() ->> 'role') = 'super_admin' OR <certification check>`
Expired mentor data must not be accessible via direct table query, REST API, or PostgREST filter bypass

Execution Context

Execution Tier
Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

Implementation Notes

The RLS USING expression should be structured as: `(organisation_id != hlf_org_id()) OR EXISTS (SELECT 1 FROM certifications c WHERE c.user_id = peer_mentors.user_id AND c.organisation_id = peer_mentors.organisation_id AND c.expiry_date >= now() AND c.is_active = true)`. Create a stable SQL function `hlf_org_id()` that returns HLF's UUID from an organisations config table — avoids hardcoding UUIDs in the policy. The certifications table must have a composite index on `(user_id, organisation_id, expiry_date)` for efficient policy evaluation. Note: HLF's workshop requirement states that expired peer mentors must also disappear from HLF's public website — coordinate with the Dynamics portal team so the same expiry logic is respected in that integration.

The Flutter certification-status-repository should remain read-only; enforcement lives entirely in PostgreSQL.

Testing Requirements

Write a SQL test script (or Dart integration test) that: (1) inserts a peer mentor with an expired certification (expiry_date = now() - interval '1 day') and asserts they are invisible to a coordinator-role query; (2) inserts a peer mentor with a valid certification (expiry_date = now() + interval '30 days') and asserts they are visible; (3) updates the expired certification to a future date and asserts the mentor becomes visible without any app restart; (4) asserts super_admin can see both records regardless of certification state. Run `EXPLAIN ANALYZE` on the policy-filtered query and assert no sequential scans on tables with >100 rows. Test with the existing certification-status-repository integration to confirm no Dart-layer changes break the contract.

Component
Supabase RLS Policy Configuration
infrastructure high
Dependencies (2)
Implement Supabase row-level security policies scoped to the org_admin role across users, activities, reimbursements, and organisations tables. Policies must restrict data access to the authenticated admin's organisation subtree using the JWT org_id claim. Include recursive CTE helper function for subtree resolution. epic-admin-portal-foundation-task-003 Create a comprehensive test suite that exercises all RLS policies defined in tasks 003 and 004. Tests must verify: org_admin cannot read data outside their subtree, super_admin can read cross-org data, unauthenticated requests are blocked, and recursive CTE subtree scoping returns correct child orgs for NHF's hierarchy depth. epic-admin-portal-foundation-task-010
Epic Risks (3)
high impact medium prob security

Missing RLS policies on one or more tables (e.g., a newly added join table or a Supabase view) could expose cross-org data to org_admin queries, creating a GDPR-reportable data breach.

Mitigation & Contingency

Mitigation: Enumerate all tables and views accessed by admin queries before writing any policy. Create an automated test that attempts a cross-org query for each table from an org_admin JWT and asserts an empty result set.

Contingency: If a gap is discovered post-deployment, immediately disable the affected query surface and deploy a hotfix policy before re-enabling. Log the incident and notify DPO if any cross-org data was returned.

high impact medium prob technical

The recursive CTE for NHF's deeply nested org tree (up to 5 levels, 1,400 local chapters) may exceed the 2-second dashboard load target when resolving large subtrees on every request.

Mitigation & Contingency

Mitigation: Benchmark the recursive CTE against a synthetic NHF-scale dataset during development. Introduce a short-TTL server-side cache for subtree resolution results. Index the parent_id column on the organisations table.

Contingency: If CTE performance remains insufficient, materialise the org subtree as a precomputed closure table updated on org structure changes, and switch the RLS guard to query the closure table instead.

high impact low prob security

Incorrect JWT claim injection in AdminRlsGuard (e.g., wrong claim key name or missing refresh on org switch) could silently apply the wrong org scope, causing org_admin to see a different organisation's data without an explicit error.

Mitigation & Contingency

Mitigation: Write unit tests for the guard that verify the injected claim value against the authenticated user's org_id for every admin route. Add a server-side assertion that the claim matches the user's database record before executing any query.

Contingency: Roll back the guard to a deny-all fallback, invalidate active admin sessions, and re-issue corrected JWTs. Audit query logs to identify any sessions that received incorrect scope.

Quick Links
All Tasks Execution Plan