🏠 Overview › ✅ Implementation Tasks ›✅ epic-geographic-peer-mentor-map-consent-privacy-task-005

Configure Supabase RLS policies for consent table

epic-geographic-peer-mentor-map-consent-privacy-task-005 — Write and apply Row Level Security policies on consent_grants and consent_audit_log tables so that a peer mentor can only read and modify their own consent record, coordinators can read consent status for mentors in their chapter, and no role can delete audit log entries. Policies must be tested against all three organisation roles.

critical priority medium complexity infrastructure pending database specialist Tier 1

Acceptance Criteria

RLS is enabled on both consent_grants and consent_audit_log tables with FORCE ROW LEVEL SECURITY
A peer mentor can SELECT their own consent_grants row (mentor_id = auth.uid()) and no rows belonging to other mentors
A peer mentor can INSERT a new consent_grants row only when mentor_id = auth.uid() and org_id matches their JWT org claim
A peer mentor can UPDATE their own consent_grants row (status field only) but cannot UPDATE any other mentor's row
A coordinator can SELECT all consent_grants rows where org_id matches their JWT org claim and the mentor belongs to their chapter — verified via chapter membership join
A coordinator cannot INSERT, UPDATE, or DELETE any consent_grants row directly
No role (mentor, coordinator, org admin) can DELETE any row from consent_audit_log
No role can UPDATE any row in consent_audit_log (append-only enforced via policy)
Service role (used by Edge Functions) bypasses RLS as expected — confirm this is intentional and documented
All six policies (mentor SELECT/INSERT/UPDATE on consent_grants, coordinator SELECT on consent_grants, audit log INSERT-only) are applied via a versioned SQL migration file committed to the repository
Policy tests pass for all three roles using Supabase's built-in RLS test utilities or a dedicated test script

Technical Requirements

frameworks
Supabase PostgreSQL 15
apis
Supabase Auth JWT claims (auth.uid(), auth.jwt()->>'org_id', auth.jwt()->>'role')
data models
consent_grants
consent_audit_log
contact_chapter
performance requirements
Coordinator SELECT policy must use an indexed join path — confirm index on consent_grants(org_id) and contact_chapter(org_unit_id, contact_id)
Policy expressions must not trigger full table scans — EXPLAIN ANALYZE policies before deployment
security requirements
FORCE ROW LEVEL SECURITY applied so table owner (postgres) is also subject to policies in non-service-role contexts
JWT role claim used to differentiate mentor vs coordinator vs admin — ensure claim is populated correctly by Supabase Auth custom claims hook
Service role key restricted to server-side Edge Functions only; Flutter client uses anon/user key subject to RLS
Audit log append-only policy enforced at database level — application-level checks are not sufficient for GDPR compliance
No SECURITY DEFINER functions that bypass RLS without explicit intent and documentation

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Write policies as a numbered SQL migration file (e.g., 0012_consent_rls_policies.sql) using Supabase migration conventions. Define separate named policies per operation (SELECT, INSERT, UPDATE) rather than combined USING + WITH CHECK in one policy — this makes auditing clearer. For the coordinator chapter-scoped SELECT, use a subquery: EXISTS (SELECT 1 FROM contact_chapter cc WHERE cc.contact_id = consent_grants.mentor_id AND cc.org_unit_id = (auth.jwt()->>'chapter_id')::uuid). Ensure the JWT custom claims hook sets chapter_id and role for every session token.

Document the service role bypass explicitly in a comment in the migration file so future developers understand it is intentional. After applying, verify with \d+ consent_grants in psql to confirm RLS is enabled.

Testing Requirements

Write a dedicated SQL test script (or use pgTAP / Supabase test helpers) that: (1) creates three test users with roles mentor, coordinator, admin and assigns them to the same org/chapter, (2) seeds consent_grants with one row per mentor, (3) asserts each SELECT/INSERT/UPDATE/DELETE operation returns expected rows or raises insufficient_privilege for each role. Include negative tests: coordinator attempting DELETE on consent_grants must fail, mentor attempting SELECT on another mentor's row must return empty. Run tests in CI against a local Supabase stack (supabase start). Coverage: all 6 policy branches exercised.

Document expected results in a policy-test-results.md file.

Component
Location Consent Service
service medium
Dependencies (1)
Design and implement the database schema for storing location consent records, including consent_grants table with fields for mentor_id, organisation_id, granted_at, revoked_at, consent_version, ip_hash, and audit_log table for all consent lifecycle events. Ensure schema supports GDPR Article 17 right-to-erasure by allowing full deletion of location data on revocation. epic-geographic-peer-mentor-map-consent-privacy-task-001
Epic Risks (2)
medium impact medium prob scope

If the privacy policy text or consent terms change after mentors have already opted in, existing consent records may become legally insufficient, requiring re-consent from all opted-in mentors which could temporarily reduce map coverage.

Mitigation & Contingency

Mitigation: Store a consent_version field on every consent record. Implement a consent version check in location-consent-service that compares the stored version against the current policy version from location-privacy-config and flags stale consents for re-consent prompting.

Contingency: If a policy update invalidates existing consents, suppress affected mentors from the map, queue them for re-consent notification via the existing in-app notification system, and restore map visibility only after new consent is recorded.

medium impact medium prob scope

A poorly designed consent dialog may lead to low opt-in rates, reducing map utility for coordinators to the point where the feature delivers insufficient value to justify maintenance cost.

Mitigation & Contingency

Mitigation: Follow plain-language writing guidelines from the cognitive accessibility feature. User-test the dialog with 2-3 peer mentors from Blindeforbundet before implementation is finalised. Ensure the dialog explains the benefit to the mentor, not just the data collection facts.

Contingency: If opt-in rate after launch is below 40%, conduct a targeted usability study and iterate on dialog copy and layout. The coordinator can also send a bulk opt-in invitation notification (per the user story) to non-consenting mentors.

Quick Links
All Tasks Execution Plan