critical priority low complexity infrastructure pending infrastructure specialist Tier 1

Acceptance Criteria

RLS is enabled on `periodic_summaries` (`ALTER TABLE periodic_summaries ENABLE ROW LEVEL SECURITY`)
Policy `select_own_org_peer_mentor` allows SELECT for the `authenticated` role where organisation_id matches the JWT claim `app_metadata.active_organisation_id` AND the user's role claim is `peer_mentor`
Policy `select_own_org_coordinator` allows SELECT for the `authenticated` role where organisation_id matches the JWT claim AND the user's role claim is `coordinator` OR `org_admin`
No INSERT, UPDATE, or DELETE policy exists for the `authenticated` role — all write operations are blocked for non-service roles
Service role bypasses RLS entirely for write operations (Supabase default behaviour confirmed)
A user authenticated as `peer_mentor` for org A receives zero rows when querying summaries for org B — verified via a test with two distinct organisation UUIDs
A user authenticated as `coordinator` for org A receives all summaries for org A but zero rows for org B
Unauthenticated requests (no JWT) receive zero rows (deny-by-default)
Policy definitions are stored in a versioned migration file, not applied manually via dashboard

Technical Requirements

frameworks
Supabase RLS
PostgreSQL policy language
Supabase CLI migrations
apis
Supabase Auth JWT claims
auth.jwt()
auth.uid()
data models
periodic_summaries
user_roles
organisations
performance requirements
RLS policy expressions must use indexed columns only — organisation_id is indexed, JWT claim extraction is O(1)
Policy must not perform subqueries against the users or roles table on every row — use JWT claims directly to avoid N+1 policy evaluation
EXPLAIN ANALYZE with RLS enabled must show the same index scan as without RLS for single-org queries
security requirements
JWT claim `app_metadata.active_organisation_id` must be set server-side by Supabase Edge Function or Auth hook — never trust client-supplied organisation_id
Role claim must come from `app_metadata` (server-controlled), not `user_metadata` (client-writable)
Cross-organisation data leakage must be impossible even with a valid JWT — policy logic must not use OR conditions that could be exploited
Service role client must never be exposed to the Flutter app — used only in backend/Edge Functions

Execution Context

Execution Tier
Tier 1

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.

Component
Organisation Data Isolation Guard
infrastructure low
Epic Risks (3)
high impact medium prob security

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.

medium impact medium prob technical

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.

low impact low prob integration

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.