critical priority medium complexity database pending database specialist Tier 1

Acceptance Criteria

SELECT policy named 'org_members_can_read_own_reports' allows authenticated users to read only rows where organization_id matches the JWT claim `organization_id`
INSERT policy named 'coordinators_admins_can_insert_reports' restricts inserts to users whose JWT claim `role` is 'coordinator' or 'admin' within the same organization_id
UPDATE policy named 'coordinators_admins_can_update_reports' restricts updates to coordinator and admin roles within the same organization; users cannot update another org's rows
DELETE policy named 'admins_can_delete_reports' restricts deletes to users whose JWT claim `role` is 'admin' within the same organization_id
A user from organization A cannot SELECT, INSERT, UPDATE, or DELETE rows belonging to organization B — verified by test with two distinct JWT tokens
EXPLAIN ANALYZE on a SELECT query under a valid org JWT shows RLS filter applied at plan level (no full table scan returning filtered rows in application)
A coordinator can INSERT a new report record for their own organization
A peer mentor (non-coordinator, non-admin) receives 0 rows on SELECT even if rows exist for their organization
All four policies are created within a single migration file that rolls back cleanly
Policies use `auth.uid()` and `auth.jwt() ->> 'organization_id'` (or equivalent custom claim path) consistently

Technical Requirements

frameworks
Supabase
PostgreSQL RLS
apis
Supabase Auth JWT claims
Supabase service_role for migration application
data models
bufdir_report_history
auth.users
user_roles (or JWT custom claims)
performance requirements
RLS policies must reference indexed columns (organization_id) to avoid full table scans per authenticated request
JWT claim lookups must be O(1) — avoid subqueries joining user_roles table inside RLS policy expressions where possible; prefer JWT custom claims set at login time
EXPLAIN ANALYZE must show 'Index Scan' not 'Seq Scan' for org-scoped SELECT queries after policy application
security requirements
Zero cross-tenant data leakage — enforce via EXPLAIN ANALYZE and integration test with two org JWT tokens
All policies must be RESTRICTIVE or correctly combined so that no policy gap allows unauthenticated access
service_role bypass must only be used in server-side Edge Functions, never in client-facing Flutter code
JWT custom claims (organization_id, role) must be set server-side during sign-in and cannot be modified by the client

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Supabase uses PostgreSQL JWT claims accessible via `auth.jwt()`. Ensure the `organization_id` and `role` custom claims are injected at sign-in time (typically via a `handle_new_user` trigger or a Supabase Auth hook). RLS policy expressions like `(auth.jwt() ->> 'organization_id')::uuid = organization_id` are safe and efficient because they use the indexed organization_id column. Avoid joining user_roles inside the policy expression — this creates an N+1 problem.

Instead, use JWT claims as the single source of truth for org and role at query time. For the UPDATE policy, add a USING clause (controls which rows can be updated) and a WITH CHECK clause (validates the new row state) to prevent privilege escalation via UPDATE. Example: `CREATE POLICY ... ON bufdir_report_history FOR UPDATE USING (org_check) WITH CHECK (org_check AND role_check)`.

Test policies with `SET LOCAL role = authenticated; SET LOCAL request.jwt.claims = '...';` in psql.

Testing Requirements

Write three sets of integration tests: (1) Cross-tenant isolation — create two organizations (A and B), insert one report per org, authenticate as user from org A, assert only org A's report is returned in SELECT; (2) Role enforcement — authenticate as peer_mentor role in org A, attempt INSERT — assert permission denied; authenticate as coordinator in org A, attempt INSERT — assert success; (3) Admin delete — authenticate as admin in org A, DELETE org A's report — assert success; attempt DELETE on org B's report — assert permission denied. Run tests using Supabase local dev (supabase start) with test-specific JWT tokens generated via `supabase functions serve` or the service_role key in a test harness. All tests must pass before policies are applied to staging.

Component
Report History RLS Policy Configuration
infrastructure low
Epic Risks (3)
high impact medium prob security

Incorrectly authored RLS policies could silently allow cross-organization data reads, exposing sensitive report history of one organization to coordinators of another in a multi-tenant environment.

Mitigation & Contingency

Mitigation: Write integration tests that explicitly authenticate as a user from organization A and assert zero rows are returned for organization B's history records. Use Supabase's built-in RLS testing utilities and review policies with a second developer.

Contingency: If a cross-tenant leak is discovered post-deployment, immediately revoke all active sessions for affected organizations, audit query logs for unauthorized access, and patch the RLS policy in a hotfix migration before re-enabling access.

medium impact medium prob technical

The 5-year retention policy for report files may conflict with Supabase Storage's lack of native lifecycle rules, requiring a custom pg_cron job that could fail silently and either delete files prematurely or never clean up.

Mitigation & Contingency

Mitigation: Implement the retention cleanup as a documented pg_cron job with explicit logging to a separate audit_jobs table. Add a Supabase Edge Function health check that verifies the cron job ran within the last 25 hours.

Contingency: If the cron job fails, files accumulate in storage (non-critical for compliance — over-retention is safer than under-retention). Alert the ops team via monitoring and manually trigger the cleanup function once the cron issue is resolved.

medium impact low prob integration

Signed URL generation depends on the requesting user's Supabase session being valid at the time of the call. If sessions expire during a long screen interaction, URL generation will fail with an authorization error and confuse the coordinator.

Mitigation & Contingency

Mitigation: Wrap signed URL generation in the service layer with a session-refresh check before calling Supabase Storage. Generate URLs on demand (tap-to-download) rather than pre-generating them for all list items on screen load.

Contingency: If a URL generation fails due to session expiry, surface a clear error message prompting the coordinator to re-authenticate, then automatically retry URL generation after session refresh completes.