Implement RLS policies for contact_chapters

epic-multi-chapter-membership-handling-foundation-task-002 — Define and apply Row Level Security policies on the contact_chapters table to enforce multi-organisation data isolation. Policies must restrict read and write access so that coordinators can only query memberships within their own organisation scope, and peer mentors can only read their own chapter affiliations. Validate policies against the existing RLS pattern used elsewhere in the Supabase project.

critical priority medium complexity infrastructure pending infrastructure specialist Tier 1

Acceptance Criteria

RLS is confirmed enabled on `contact_chapters` (`ALTER TABLE contact_chapters ENABLE ROW LEVEL SECURITY` present in migration or applied in this task)

A SELECT policy named `contact_chapters_select_coordinator` allows coordinators to read all memberships where the chapter's `organization_id` matches the coordinator's organisation extracted from JWT claims

A SELECT policy named `contact_chapters_select_peer_mentor` allows peer mentors to read only rows where `contact_id` matches their own `contacts.id` linked to `auth.uid()`

An INSERT policy named `contact_chapters_insert_coordinator` allows coordinators to insert memberships only within their own organisation scope

A DELETE policy named `contact_chapters_delete_coordinator` allows coordinators to remove memberships only within their own organisation scope

No UPDATE policy is defined — chapter affiliation changes must be done via delete + insert to maintain audit clarity

Peer mentors have no INSERT or DELETE permissions on `contact_chapters`

The `service_role` key bypasses RLS as per Supabase default — this is intentional and documented

Policy definitions are included in a migration file (not applied ad hoc via Studio) so they are version-controlled

Manual verification confirms that a JWT for Organisation A cannot read rows belonging to Organisation B

Technical Requirements

frameworks

Supabase

apis

Supabase PostgreSQL 15 RLS

Supabase Auth JWT claims

data models

contact_chapter

contact

performance requirements

RLS policy expressions must reference indexed columns (`contact_id`, `chapter_id`) to avoid full table scans on policy evaluation

JWT claim extraction (`auth.jwt() ->> 'organisation_id'`) should be evaluated once per query via a stable function wrapper if called in multiple policies

security requirements

All policies must use `USING` clause for filtering — never rely solely on application-layer filtering

Organisation ID must be derived from the authenticated JWT (`auth.jwt()`) — never from a URL parameter or request body

Write policies (INSERT, DELETE) must include both `WITH CHECK` and `USING` clauses where applicable

Policies must be tested with the `anon` role (unauthenticated) confirming zero rows returned

RLS bypass via `set role service_role` must be documented and restricted to server-side Edge Functions only

Execution Context

Execution Tier

Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

View Full Execution Plan

Implementation Notes

Follow the existing RLS pattern in the project — inspect other tables (e.g., `activities`, `contacts`) to understand how `auth.jwt() ->> 'organisation_id'` is used and replicate the same helper function if one exists (e.g., `get_user_organisation_id()`). For the peer mentor SELECT policy, a subquery join through `contacts` table is needed: `contact_id IN (SELECT id FROM contacts WHERE user_id = auth.uid())`. Ensure this subquery is covered by an index on `contacts.user_id`. Avoid `SECURITY DEFINER` functions in policies unless already established as a pattern — they can bypass RLS on the called function's tables.

Test with at least two organisations seeded to confirm cross-org isolation.

Testing Requirements

Write SQL test scripts using `SET LOCAL role = authenticated; SET LOCAL request.jwt.claims = ...` to simulate coordinator and peer mentor JWTs. Assert: (1) coordinator sees only their organisation's memberships; (2) peer mentor sees only their own rows; (3) unauthenticated request returns 0 rows; (4) coordinator from Org A cannot insert a membership for a chapter in Org B; (5) peer mentor cannot delete any row. Run these assertions in the Supabase SQL editor or as a `pgTAP` test suite. Include test results in the PR as a screenshot or exported test output.

Component

Supabase Contact Chapter Adapter

infrastructure medium

Dependencies (1)

Create a Supabase database migration that introduces the contact_chapters junction table with composite primary key (contact_id, chapter_id), foreign key constraints to contacts and chapters tables, required indexes for query performance, and CHECK constraints. Include created_at and updated_at timestamps and any additional metadata columns needed by the domain model. epic-multi-chapter-membership-handling-foundation-task-001

Epic Risks (2)

high impact medium prob security

Supabase RLS policies for a junction table that spans organisations are non-trivial. An incorrectly scoped policy could expose chapter affiliations from other organisations to coordinators, constituting a data breach.

Mitigation & Contingency

Mitigation: Draft RLS policies in a staging environment and run an explicit cross-organisation isolation test suite before merging. Use Supabase policy testing tools and peer review all policy definitions.

Contingency: If a policy error reaches review, roll back the migration and apply a corrective patch. Ensure no production data has been exposed by auditing Supabase logs for cross-organisation query results.

medium impact medium prob technical

The contact_chapters table migration may conflict with existing foreign key structures or require a backfill for contacts already assigned to a single chapter, causing migration failures in production.

Mitigation & Contingency

Mitigation: Write the migration as an additive, non-destructive operation. Backfill existing single-chapter assignments by deriving them from the existing contact records. Test the full migration on a production-sized dataset clone before release.

Contingency: Provide a rollback migration script that removes the new table without touching existing contact records. Coordinate with operations for a maintenance window if a re-run is needed.

Quick Links

All Tasks Execution Plan