Validate RLS org-scope policies end-to-end

epic-coordinator-proxy-registration-foundation-task-012 — Perform end-to-end validation of all Row Level Security policies introduced in this epic. Test that a coordinator from org A cannot read, insert, or modify proxy_activities or proxy_audit_log records belonging to org B. Verify that peer mentors cannot insert proxy_activities (coordinator-only write permission). Test that unauthenticated requests are fully rejected. Document all policy names, roles, and enforcement rules in a brief policy reference for future maintainers.

critical priority medium complexity infrastructure pending database specialist Tier 6

Acceptance Criteria

A coordinator authenticated as org A receives zero rows when querying proxy_activities or proxy_audit_log records scoped to org B

An INSERT attempt by a coordinator from org A into org B's proxy_activities table returns a Postgres RLS violation error (42501)

An UPDATE or DELETE attempt by a coordinator from org A on org B records is blocked and returns an RLS violation error

A peer mentor role attempting to INSERT into proxy_activities is rejected with an RLS violation error regardless of org membership

Unauthenticated Supabase client requests to proxy_activities and proxy_audit_log return empty result sets or permission errors with no data leakage

Org admin role can read all records within their organisation but cannot modify records in other organisations

All RLS policy names are documented in a policy_reference.md listing: table name, policy name, command (SELECT/INSERT/UPDATE/DELETE), applicable role, and enforcement expression

All validation scenarios are covered by automated Supabase SQL test scripts that can be re-run after future schema changes

Policy reference document is committed to the repository under docs/rls/ or equivalent

Technical Requirements

frameworks

Flutter

flutter_test

Supabase client Dart SDK

apis

Supabase REST API

Supabase Auth API

Supabase RPC

data models

proxy_activities

proxy_audit_log

user_roles

organisations

chapters

performance requirements

RLS policy evaluation must not add more than 5ms per query on the Supabase hosted instance

Validation test suite must complete within 60 seconds

security requirements

No cross-org data leakage under any authenticated or unauthenticated scenario

Peer mentor role must be structurally prevented from writing proxy_activities — not just via application logic

Unauthenticated requests must return no rows, not an error that reveals schema structure

All policy expressions must use auth.uid() and auth.jwt() claims — never trust client-supplied org_id

Policy reference document must not contain sensitive data or production credentials

Execution Context

Execution Tier

Tier 6

Tier 6 - 158 tasks

Can start after Tier 5 completes

View Full Execution Plan

Implementation Notes

Use Supabase's built-in RLS testing approach: create service-role client to seed data, then switch to anon/user-role clients for assertions. Never use service-role in tests to assert RLS — it bypasses policies entirely. For policy expressions, the org_id filter should join through the chapters or user_organisation_memberships table using auth.uid(), e.g.: `(org_id = (SELECT org_id FROM user_memberships WHERE user_id = auth.uid()))`. Avoid storing org_id in JWT claims unless it is validated server-side on every token refresh.

For the policy reference document, use a Markdown table with columns: Table | Policy Name | Command | Role | USING Expression | WITH CHECK Expression. Run `SELECT * FROM pg_policies WHERE tablename IN ('proxy_activities','proxy_audit_log')` to auto-generate the baseline. Coordinate with the team to agree on a canonical role naming convention (e.g., 'coordinator', 'peer_mentor', 'org_admin') before finalising policy expressions.

Testing Requirements

Write integration tests using the Supabase Dart client that authenticate as distinct test users representing each role (coordinator-org-A, coordinator-org-B, peer-mentor-org-A, unauthenticated). For each user, assert SELECT/INSERT/UPDATE/DELETE outcomes against both same-org and cross-org records. Use flutter_test with a dedicated Supabase test project or local Supabase instance. Include a SQL migration script that seeds minimal test organisations, chapters, and proxy_activities rows before tests run.

All scenarios must be deterministic and idempotent. Aim for 100% coverage of the four tables and five role combinations. Store test scripts under test/rls/ and run them in CI.

Component

Proxy Activity Repository

data medium

Dependencies (2)

Write unit and integration tests for ProxyActivityRepository covering: correct persistence of coordinator_id and attributed_mentor_id as separate fields, rejection of records with missing attribution fields, RLS policy enforcement preventing cross-org data access, bulk insert correctness for all mentor entries in a batch. Use flutter_test and Supabase test helpers or mock clients. Verify audit log entries are created on each insert. epic-coordinator-proxy-registration-foundation-task-009 Write tests verifying that the ProxyAuditLogger produces append-only records: no update or delete operations succeed on the audit log table (test RLS enforcement), each proxy activity insert produces exactly one audit record, bulk inserts produce one audit record per mentor, payload_snapshot captures the full activity data at write time. Also test that logCreated and logBulkCreated correctly differentiate event_type in the audit log. epic-coordinator-proxy-registration-foundation-task-010

Epic Risks (3)

high impact medium prob security

Supabase RLS policies for org-scoped proxy access may be difficult to express correctly, especially for coordinators with multi-chapter access. An overly permissive policy could allow cross-org proxy registrations, corrupting Bufdir reporting; an overly restrictive policy could block legitimate coordinators from registering.

Mitigation & Contingency

Mitigation: Write integration tests covering all access boundary cases (same org, cross-org, multi-chapter coordinator) before merging any RLS migration. Use parameterised RLS test helpers already established by the auth feature.

Contingency: If RLS proves insufficient, add a server-side Edge Function validation layer that re-checks org membership before persisting any proxy record, providing defence in depth.

medium impact low prob technical

Adding new tables and foreign key constraints to an existing production Supabase database risks migration failures or locking issues if the database already contains active sessions during deployment.

Mitigation & Contingency

Mitigation: Use additive-only migrations (no DROP or ALTER on existing tables). Test full migration sequence in a staging Supabase project before production deployment. Schedule during low-traffic window.

Contingency: Maintain a rollback migration script. If the migration fails, the feature remains unreachable behind a feature flag while the schema issue is resolved.

high impact medium prob security

Audit log entries must be immutable for compliance, but Supabase RLS by default allows row owners to update their own rows. If audit records are accidentally mutable, dispute resolution and accountability guarantees are invalidated.

Mitigation & Contingency

Mitigation: Configure the proxy_audit_log table with an RLS policy that allows INSERT for coordinators but denies UPDATE and DELETE for all roles including service_role, enforced at the database level.

Contingency: If RLS cannot fully prevent updates, create a database trigger that reverts any UPDATE to the audit table and logs the attempt as a security event.

Quick Links

All Tasks Execution Plan