RLS policies for organisation-scoped periodic_summaries
epic-periodic-summaries-foundation-task-002 — Define and apply Supabase Row Level Security policies on the periodic_summaries table ensuring that peer mentors can only SELECT rows where organisation_id matches their active organisation context, coordinators can SELECT all rows within their organisation, and no role can INSERT or UPDATE directly (writes are service-role only). Verify policies block cross-organisation reads completely.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Use `(auth.jwt() -> 'app_metadata' ->> 'active_organisation_id')::uuid` to extract the organisation UUID from the JWT — this avoids joining the profiles/users table per row. Ensure the JWT claim is populated via a Supabase Auth `custom_access_token` hook when the user selects their active organisation at login time. Create policies in a new migration file (not the same file as task-001 schema) for clean separation of concerns. Use `USING` clause for SELECT policies and omit `WITH CHECK` since writes are blocked.
Add a comment in the migration explaining why direct writes are disallowed (writes go through service-role Edge Functions to ensure audit trails and business logic enforcement).
Testing Requirements
Integration tests using Supabase's RLS test utilities or pgTAP `set_config` approach: (1) simulate a `peer_mentor` JWT for org A and assert SELECT returns only org A rows, (2) simulate a `coordinator` JWT for org A and assert same org-scoped result, (3) simulate a `peer_mentor` JWT for org A and assert zero rows returned for org B's summaries, (4) assert that an INSERT attempted with an authenticated role JWT throws a permission denied error, (5) assert unauthenticated SELECT returns zero rows. Document test organisation UUIDs as fixture constants. Tests must run in CI against a fresh `supabase db reset` environment.
Supabase RLS policies for aggregation views are more complex than single-table policies. A misconfigured policy could silently allow a coordinator in one organisation to see data from another, causing a data breach and breaking trust with participating organisations.
Mitigation & Contingency
Mitigation: Write automated RLS integration tests that create two separate organisations with distinct data, then assert that queries authenticated as org-A users return only org-A rows. Run these tests in CI on every PR touching the database layer.
Contingency: If an RLS bypass is discovered post-deployment, immediately disable the periodic summaries feature flag, revoke affected sessions, audit access logs, notify affected organisations, and patch the policy before re-enabling.
Activity records may span multiple sessions types, proxy registrations, and coordinator bulk entries. Incorrect JOIN logic or missing filters in the aggregation query could double-count sessions or omit activity types, producing inaccurate summaries that erode user trust.
Mitigation & Contingency
Mitigation: Build a fixture dataset covering all activity registration paths (direct, proxy, bulk) and assert expected aggregated counts in integration tests before any UI consumes the repository.
Contingency: If inaccurate counts are reported post-launch, mark affected summaries as invalidated in the database and re-trigger generation once the query is corrected. Communicate transparently to affected users via an in-app banner.
The local cache must be invalidated when a new summary arrives via push notification. If the push token is stale or the FCM/APNs delivery is delayed, the device may show an outdated summary for an extended period, confusing users who see different numbers online versus offline.
Mitigation & Contingency
Mitigation: Implement a TTL on cached summaries (max 48 hours) so stale data is auto-cleared even without a push notification. Also trigger cache refresh on app foreground if the current period's summary is older than 24 hours.
Contingency: Provide a manual pull-to-refresh on the summary card that bypasses the cache and fetches directly from Supabase when a network connection is available.