Implement RLS policies for report history table
epic-bufdir-report-history-foundation-task-002 — Write and apply Row Level Security policies on the bufdir_report_history table enforcing multi-tenant organization scoping. SELECT policy: authenticated users may only read rows where organization_id matches their session claim. INSERT/UPDATE policy: restricted to users with coordinator or admin role within the same organization. DELETE policy: admin only. Verify policies with EXPLAIN ANALYZE to confirm no cross-tenant data leakage.
Acceptance Criteria
Technical Requirements
Execution Context
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.
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.
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.
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.