critical priority medium complexity testing pending backend specialist Tier 2

Acceptance Criteria

Test: coordinator JWT from org A receives zero rows when querying org B's bufdir_report_history records (SELECT returns empty, not an error)
Test: peer mentor JWT receives PostgrestException with code 42501 (insufficient_privilege) or empty result when attempting INSERT into bufdir_report_history
Test: org admin JWT can DELETE a record belonging to their own organization and the row is removed from the table
Test: unauthenticated (anon key, no JWT) request to bufdir_report_history returns empty result set or 401, confirming no public read access
Test: coordinator from org A cannot UPDATE records belonging to org B
Test: service role client can seed and tear down test data across multiple organizations without RLS interference
All tests run successfully against a local Supabase instance started via `supabase start`
Tests are isolated: each test seeds its own data via service role client and cleans up in teardown
Test suite completes in under 60 seconds total
No hardcoded organization IDs — test orgs are created dynamically and referenced by variable

Technical Requirements

frameworks
flutter_test
supabase_flutter
Supabase CLI (local dev stack)
apis
Supabase REST API (PostgREST) — table-level operations on bufdir_report_history
Supabase Auth API — JWT generation for role-specific test users
Supabase service role client — unrestricted seed/teardown
data models
bufdir_report_history
organizations
user_profiles (role + organization_id)
performance requirements
Full test suite completes in under 60 seconds on local Supabase instance
Each individual test completes in under 5 seconds
security requirements
Service role key must never appear in committed test files — load from environment variable SUPABASE_SERVICE_ROLE_KEY
Test JWTs must be generated with minimum required claims (sub, role, org_id) to validate RLS specifically
Test database must be isolated from any shared/staging environment

Execution Context

Execution Tier
Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

Implementation Notes

RLS on Supabase can either silently filter rows (SELECT returns [] instead of error) or throw 42501 depending on policy definition. Before writing tests, verify actual policy behavior in psql: `SET LOCAL role = 'authenticated'; SET LOCAL request.jwt.claims = '{"sub":"...","org_id":"..."}'`. Use `SupabaseClient` from `supabase_flutter` with `auth.setSession()` or construct client with a manually signed JWT for test users. Prefer creating test users via `supabase.auth.admin.createUser()` using service role client rather than signing up normally, to avoid email confirmation flow.

Store local Supabase URL and keys in a `.env.test` file loaded via `flutter_dotenv` or `String.fromEnvironment`. Structure test file as `bufdir_report_history_rls_test.dart` under `test/integration/`. Tag with `@Tags(['integration'])` so CI can exclude them from unit test runs. Run with `flutter test --tags integration`.

Testing Requirements

Integration tests only — no mocking of the database layer. Each test scenario requires a live local Supabase instance. Test structure: (1) setUp creates two organizations, one user per role (coordinator_a, coordinator_b, peer_mentor, org_admin_a) via service role client; (2) each test uses a dedicated Supabase client initialized with the appropriate user JWT; (3) tearDown deletes all seeded rows via service role client. Scenarios: cross-org SELECT isolation, peer mentor INSERT rejection, admin DELETE within org, unauthenticated access rejection, cross-org UPDATE rejection.

Use `expect(() async => ..., throwsA(isA()))` or verify empty result sets depending on RLS behavior (RLS may silently filter rather than throw). Document which behavior is expected per policy.

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.