Document security contract and RLS policy coverage
epic-admin-portal-foundation-task-014 — Write the security contract document that specifies which tables have RLS coverage, what claims each role injects, the recursive subtree resolution strategy, and the GDPR data isolation guarantees. This document becomes QA's reference for admin portal security testing and feeds the Bufdir grant reporting audit trail requirements.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 6 - 158 tasks
Can start after Tier 5 completes
Implementation Notes
Structure the document in these sections: (1) Overview and scope; (2) Role taxonomy and JWT claims map (table: role → claim name → claim value → injection point); (3) Table RLS inventory (table: table name → RLS enabled → policy names → roles covered → personal data: yes/no); (4) Recursive organisation subtree resolution (explain the WITH RECURSIVE CTE or equivalent used to resolve org membership for multi-level hierarchies); (5) HLF-specific extensions (certification-gated visibility); (6) GDPR data isolation guarantees; (7) Bufdir audit trail requirements; (8) Known limitations and future work. Extract the table inventory programmatically: `SELECT tablename, policyname, roles FROM pg_policies WHERE schemaname = 'public'` — paste the output and annotate it. This document is the foundation for Bufdir grant reporting and must survive project handovers.
Testing Requirements
This is a documentation task; testing is a review process. Acceptance testing: (1) a QA engineer not involved in implementation reads the document and can independently construct a security test matrix covering all RLS policies; (2) a compliance reviewer confirms the GDPR isolation guarantees section is legally accurate for Norwegian data protection requirements; (3) a developer cross-checks the table inventory against `supabase/migrations/` and confirms no table with personal data is omitted; (4) Bufdir audit trail section is reviewed against the grant agreement requirements for Bufdir-funded organisations (NHF, Blindeforbundet, HLF).
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.
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.
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.