critical priority high complexity database pending database specialist Tier 3

Acceptance Criteria

RLS is enabled on mv_peer_mentor_stats and mv_chapter_stats (ALTER TABLE ... ENABLE ROW LEVEL SECURITY)
A coordinator querying mv_peer_mentor_stats receives only rows where chapter_id IN (SELECT chapter_id FROM coordinator_chapter_memberships WHERE coordinator_id = auth.uid())
A coordinator querying mv_chapter_stats receives only rows where chapter_id IN their authorized set via the same subquery
A peer mentor querying mv_peer_mentor_stats receives only the single row where peer_mentor_id = auth.uid() (own data only)
A peer mentor querying mv_chapter_stats receives zero rows (no chapter-level aggregate visible to peer mentors)
An authenticated user with neither coordinator nor peer_mentor role receives zero rows from both views
A cross-chapter read attempt (coordinator A querying chapter B which they are not a member of) returns zero rows and no error
Service role (postgres / service_key) bypasses RLS and can read all rows — verified for use by REFRESH CONCURRENTLY and edge functions
All CREATE POLICY statements use IF NOT EXISTS to be idempotent and safe to re-run
Migration includes a regression SQL script (regression_rls_test.sql) that seeds two coordinators with different chapter sets and asserts the isolation using SET ROLE and SELECT assertions

Technical Requirements

apis
Supabase Auth — auth.uid() and auth.jwt() functions in policy expressions
Supabase PostgREST — RLS enforced automatically on REST queries
data models
coordinator_chapter_memberships
user_roles
mv_peer_mentor_stats
mv_chapter_stats
performance requirements
RLS policy subquery (coordinator chapter membership lookup) must use an index — verify with EXPLAIN that the subquery uses an index scan on coordinator_chapter_memberships(coordinator_id)
Policy evaluation overhead must not add more than 20 ms to view queries verified by EXPLAIN ANALYZE before/after RLS enable
security requirements
No policy must allow a peer mentor to read another peer mentor's data
No policy must allow a coordinator to read data from chapters outside their membership
Service role bypass is intentional but must be documented — add a SQL comment explaining why BYPASSRLS is required for REFRESH CONCURRENTLY
auth.uid() must be used in all policy WHERE clauses — never rely on application-level filtering as the sole access gate
Policies must be SELECT-only (no INSERT/UPDATE/DELETE policies needed on materialised views)

Execution Context

Execution Tier
Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

Implementation Notes

Materialised views in PostgreSQL do not directly support RLS — you must either (a) convert them to views that internally join to a base table with RLS, or (b) apply RLS on a wrapper view that SELECTs from the materialised view with a policy filter. Approach (b) is simpler: create view v_peer_mentor_stats AS SELECT * FROM mv_peer_mentor_stats and enable RLS on the wrapper view. Enable RLS on the materialised view itself in newer PostgreSQL versions (15+) if available in Supabase's managed instance — check the Supabase PostgreSQL version before choosing. Use a SECURITY DEFINER function for the coordinator chapter lookup to avoid N+1 policy evaluations: CREATE OR REPLACE FUNCTION get_coordinator_chapter_ids() RETURNS SETOF uuid SECURITY DEFINER AS $$ SELECT chapter_id FROM coordinator_chapter_memberships WHERE coordinator_id = auth.uid() $$ LANGUAGE sql STABLE.

Index coordinator_chapter_memberships(coordinator_id) before enabling RLS to prevent full-table scans on every row evaluation.

Testing Requirements

SQL regression test script (regression_rls_test.sql): (1) CREATE two test coordinator users with non-overlapping chapter sets using SET LOCAL ROLE / SET LOCAL jwt.claims; (2) SELECT from mv_peer_mentor_stats as each coordinator and assert only their chapters are returned using ASSERT or a pgTAP ok() call; (3) SELECT from mv_chapter_stats as a peer mentor and assert zero rows; (4) SELECT own row from mv_peer_mentor_stats as a peer mentor and assert exactly one row; (5) attempt cross-chapter read as coordinator A for coordinator B's chapter and assert zero rows. Run this script in CI after every migration. Additionally, write a Flutter integration test using Supabase's test client to verify that a real coordinator JWT scopes the PostgREST response correctly.

Component
Supabase Stats Database Views
infrastructure high
Epic Risks (3)
medium impact medium prob technical

Materialized views over large activity tables may have refresh latency exceeding the 2-second SLA under high insert load, causing stale data to appear on the dashboard immediately after a peer mentor registers an activity.

Mitigation & Contingency

Mitigation: Design the materialized view refresh trigger to run asynchronously via a Supabase Edge Function rather than a synchronous trigger, and set a maximum staleness tolerance of 5 seconds documented in the feature spec. Add a CONCURRENTLY refresh strategy so reads are never blocked.

Contingency: If refresh latency cannot meet SLA, fall back to a regular (non-materialized) view for the dashboard and accept slightly higher query cost per request. Revisit materialized approach once Supabase pg_cron or background workers are available.

high impact medium prob integration

The aggregation counting rules for the dashboard may diverge from those used in the Bufdir export pipeline (e.g., which activity types count, how duplicate registrations are handled), creating a reconciliation burden for coordinators at reporting time.

Mitigation & Contingency

Mitigation: Run the BufDir Alignment Validator against a shared reference dataset before any view is merged to main. Encode the counting rules as a shared Supabase function called by both the stats views and the export query builder so there is a single source of truth.

Contingency: If divergence is discovered post-launch, ship a visible banner on the dashboard stating that numbers are indicative and may differ from the export until the reconciliation fix is deployed. Prioritize the fix as a P0 defect.

high impact low prob security

Multi-chapter coordinators (up to 5 chapters per NHF requirement) require RLS policies that filter on an array of chapter IDs, which is more complex than single-value RLS and could be misconfigured, leaking data across chapters or blocking legitimate access.

Mitigation & Contingency

Mitigation: Write integration tests that verify cross-chapter isolation for a coordinator assigned to chapters A and B cannot see data from chapter C. Use parameterized RLS policies with auth.uid()-based chapter lookup to avoid hardcoded values.

Contingency: If RLS misconfiguration is detected in testing, temporarily restrict coordinator queries to single-chapter scope (coordinator's primary chapter) and ship multi-chapter support as a fast-follow patch once RLS logic is verified.