🏠 Overview › ✅ Implementation Tasks ›✅ epic-coordinator-statistics-dashboard-foundation-task-008

Add RLS policy validation for stats views

epic-coordinator-statistics-dashboard-foundation-task-008 — Verify that Supabase Row Level Security policies on the pre-aggregated views enforce org_id and coordinator_id boundaries at the database level. Document the dual-layer security model (RLS + RoleAccessValidator). Write SQL tests that confirm coordinators cannot read peer data outside their chapter even with direct Supabase client access.

critical priority medium complexity database pending database specialist Tier 2

Acceptance Criteria

Every pre-aggregated stats view has RLS enabled and a SELECT policy that restricts rows to the authenticated user's org_id (sourced from JWT claim auth.jwt()->'app_metadata'->>'org_id')
A coordinator JWT can only read rows where org_id matches their claim and coordinator_id matches their user id
An org-admin JWT can read all rows within their org_id but not rows from other organisations
Direct Supabase anon-key access to stats views returns zero rows (RLS blocks unauthenticated reads)
SQL pgTAP tests (or equivalent) are written and pass for: cross-org read returns empty, cross-coordinator read returns empty, valid coordinator read returns data, org-admin read returns all org rows
The dual-layer security model (RLS as database enforcement, RoleAccessValidator as application enforcement) is documented in a migration comment and in the codebase
All RLS policies use USING clauses, not WITH CHECK (stats views are read-only)
Migration script is idempotent — running it twice does not produce errors or duplicate policies

Technical Requirements

frameworks
Dart
Flutter
apis
Supabase PostgreSQL 15
data models
activity
activity_type
annual_summary
assignment
performance requirements
RLS policy evaluation must not prevent use of existing indexes on org_id and coordinator_id columns
EXPLAIN ANALYZE on a filtered stats query must show index scan, not sequential scan after RLS is applied
security requirements
JWT claim extraction in RLS policies must use auth.jwt()->'app_metadata' not auth.uid() alone — app_metadata is set server-side and not user-modifiable
Service role key bypasses RLS by design — confirm it is never used in mobile client code
Policies must be reviewed for the 'coordinator accessing another coordinator's peer mentors within same org' vector — this must be denied

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

RLS policy pattern for coordinator stats view: CREATE POLICY coordinator_own_stats ON coordinator_stats_view FOR SELECT TO authenticated USING (org_id = (auth.jwt()->'app_metadata'->>'org_id')::uuid AND (coordinator_id = auth.uid() OR (auth.jwt()->'app_metadata'->>'role') = 'org_admin')). For org-admin: the role check in the USING clause is sufficient. Document the dual-layer model clearly: 'RLS is the authoritative enforcement layer at the database level. RoleAccessValidator in the application layer provides early rejection and clear error messages before the network call, improving UX and reducing unnecessary Supabase round trips.

Both layers must be maintained independently.' Store documentation in a migration comment and a SECURITY.md section.

Testing Requirements

Write SQL test scripts (usable with psql or Supabase's built-in SQL editor) that: (1) set LOCAL ROLE to a coordinator JWT and SELECT from each stats view — verify row count matches only that coordinator's data; (2) set LOCAL ROLE to another coordinator in the same org — verify zero rows from first coordinator's data; (3) set LOCAL ROLE to org-admin — verify all org rows returned; (4) set LOCAL ROLE to anon — verify zero rows. Document the test script in the migration file. Additionally write a Dart integration test that performs the same checks via the Supabase Flutter SDK.

Component
Role Access Validator
service low
Dependencies (2)
Implement the RoleAccessValidator class that enforces coordinator-scoped vs org-admin-scoped query boundaries as a defence-in-depth complement to Supabase RLS. Validate that coordinators can only query their own chapter's data and that org-admins can query across their organization. Throw typed access denial exceptions for out-of-scope queries. epic-coordinator-statistics-dashboard-foundation-task-003 Write the database migration that creates pre-aggregated Supabase views keyed by org_id + coordinator_id + month. Views must replace real-time aggregation of activity rows with sub-100ms indexed lookups. Include indexes on the composite key, period filtering columns, and activity_type columns. Sequence and test the migration against a staging Supabase instance. epic-coordinator-statistics-dashboard-foundation-task-002
Epic Risks (3)
high impact medium prob technical

Pre-aggregated Supabase views may still be slow for orgs with very large activity datasets (NHF with 1,400 chapters). If the view query plan performs sequential scans, dashboard load times could exceed acceptable thresholds and degrade the perceived value of the feature.

Mitigation & Contingency

Mitigation: Design views with composite indexes on (org_id, coordinator_id, month) from the start. Run EXPLAIN ANALYZE during development against a seeded dataset of realistic scale. Add materialized view refresh strategy if needed.

Contingency: If live view performance is insufficient, convert to materialized views refreshed on a schedule or on activity-write triggers. Expose the refresh delay transparently in the UI with a 'last updated' timestamp.

high impact low prob security

Supabase RLS policies for the stats views may not be configured correctly during initial migration, potentially allowing cross-coordinator data leakage before the RoleAccessValidator layer is reached. This is a security and compliance risk.

Mitigation & Contingency

Mitigation: Write RLS integration tests as part of this epic that explicitly verify a coordinator JWT cannot read another coordinator's stats rows. Apply RLS policies in the migration script itself, not as a manual step.

Contingency: If an RLS gap is discovered post-deployment, immediately disable the stats screen via a feature flag, apply the corrected RLS migration, and re-enable after verification. Log and audit all queries that ran during the gap window.

medium impact medium prob integration

Cache invalidation logic may not be triggered correctly when a new activity is registered by a peer mentor or when an expense approval is granted. Stale data could cause coordinators to make decisions based on outdated KPIs, undermining trust in the dashboard.

Mitigation & Contingency

Mitigation: Define explicit invalidation event contracts with the activity registration and expense approval pipelines. Implement an event bus subscription within StatsCacheManager. Document the invalidation contract in code.

Contingency: If event-driven invalidation proves unreliable, add a manual 'Refresh' pull-to-refresh gesture on the dashboard and reduce TTL to 5 minutes as a fallback degradation strategy.

Quick Links
All Tasks Execution Plan