🏠 Overview › ✅ Implementation Tasks ›✅ epic-proxy-activity-registration-foundation-task-003

RLS policy for coordinator proxy inserts

epic-proxy-activity-registration-foundation-task-003 — Define a Supabase Row Level Security policy on the activities table that permits INSERT only when the caller holds the coordinator role AND `registered_by` equals `auth.uid()`. Deny updates to `registered_by` and `attributed_to` after creation. Verify the policy blocks peer mentor users from performing proxy inserts.

critical priority low complexity infrastructure pending infrastructure specialist Tier 1

Acceptance Criteria

INSERT on activities table succeeds when JWT role claim equals 'coordinator' and registered_by equals auth.uid()
INSERT is rejected with 403 when JWT role claim is 'peer_mentor', even if all other fields are valid
INSERT is rejected when registered_by does not match auth.uid(), regardless of role
UPDATE to registered_by column is denied for all roles after row creation
UPDATE to attributed_to column is denied for all roles after row creation
RLS policy is enabled on the activities table (row_security = on)
Policy is applied to the authenticated role, not to service_role or anon
Supabase migration file exists and is idempotent (can be re-run safely)
Manual test via Supabase SQL editor confirms policy blocks peer mentor JWT tokens
Policy does not interfere with existing non-proxy activity INSERT policies

Technical Requirements

frameworks
Supabase RLS
PostgreSQL policy DSL
apis
Supabase Auth (auth.uid(), auth.jwt())
PostgreSQL auth.role()
data models
activities
user_roles
ProxyActivityRecord
performance requirements
RLS policy must not add more than 1ms overhead per INSERT (use simple expression, avoid subqueries where possible)
Policy should use auth.jwt() ->> 'role' for role check to avoid extra DB lookups
security requirements
Policy must use USING and WITH CHECK clauses correctly — WITH CHECK for INSERT
Never rely solely on client-supplied registered_by; always enforce auth.uid() equality server-side
Deny UPDATE on registered_by and attributed_to via a separate RESTRICTIVE policy or column-level privilege revocation
Test with both coordinator and peer_mentor JWT tokens in Supabase dashboard

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Use `CREATE POLICY ... AS PERMISSIVE ... FOR INSERT ... WITH CHECK (auth.jwt() ->> 'role' = 'coordinator' AND registered_by = auth.uid())`.

For immutability of registered_by and attributed_to, either (a) use a RESTRICTIVE UPDATE policy that checks OLD.registered_by = NEW.registered_by AND OLD.attributed_to = NEW.attributed_to, or (b) revoke UPDATE privilege on those specific columns from the authenticated role and grant it only to service_role. Option (b) is cleaner and more foolproof. Always place the new policy in a numbered Supabase migration file (e.g., 20260330_proxy_rls.sql) so it is version-controlled and reproducible. Confirm that task-001 (which likely creates the activities table or adds the registered_by/attributed_to columns) has been applied before running this migration.

Testing Requirements

Write Supabase integration tests (via supabase-js or REST) using two test JWT tokens: one with role=coordinator and one with role=peer_mentor. Assert INSERT succeeds for coordinator with matching registered_by, fails for coordinator with mismatched registered_by, and fails entirely for peer_mentor. Assert UPDATE on registered_by and attributed_to returns error for all roles. Include these tests in the CI migration validation step.

No Flutter unit tests required for this task — policy lives entirely in the database layer.

Component
Coordinator Role Guard
infrastructure low
Dependencies (1)
Write and apply a Supabase migration that adds `registered_by` (uuid, NOT NULL, references auth.users) and `attributed_to` (uuid, NOT NULL, references auth.users) columns to the activities table. Both columns must be immutable after insert. Include appropriate indexes and update existing RLS policies to remain compatible. epic-proxy-activity-registration-foundation-task-001
Epic Risks (3)
high impact medium prob technical

The activities table migration adding registered_by and attributed_to columns may conflict with existing RLS policies or FK constraints if the user profile table structure differs from assumptions, blocking all subsequent epics.

Mitigation & Contingency

Mitigation: Review existing activities table schema and RLS policies before writing the migration. Run the migration against a staging database clone first. Write rollback scripts alongside the migration.

Contingency: If migration fails in staging, isolate the conflict with a targeted schema audit, adjust FK references or RLS policy scope, and re-run before touching production.

high impact medium prob security

The RLS policy must filter proxy inserts to the coordinator's chapter scope. If the chapter-scope resolver pattern differs between organisations (multi-chapter coordinators in NHF vs single-chapter in HLF), the policy may be too broad or too restrictive.

Mitigation & Contingency

Mitigation: Design the RLS policy to accept a coordinator's full set of assigned chapter IDs (array) rather than a single chapter_id. Validate the policy against NHF multi-chapter test fixtures during the integration test phase.

Contingency: If the policy is found to be incorrect after deployment, introduce a server-side validation edge function as a safety net while the RLS policy is corrected.

medium impact low prob technical

The bulk_register_activities RPC function may time out or cause lock contention when inserting large participant batches (e.g. 40+ peer mentors in a single group session), degrading the user experience.

Mitigation & Contingency

Mitigation: Benchmark the RPC function with 50-participant batches during development. Use unnest-based bulk insert rather than row-by-row PL/pgSQL loops. Set a reasonable statement_timeout.

Contingency: If performance is insufficient, split the client-side submission into chunks of 20 participants with progress feedback, rather than a single RPC call.

Quick Links
All Tasks Execution Plan