Integration test export storage bucket RLS policies
epic-bufdir-report-export-foundation-task-016 — Write integration tests against the real Supabase test environment verifying: coordinator can upload and download files for their own org_id, coordinator cannot access files under a different org_id path, signed URL expiry is enforced, and upload of oversized files returns the correct error. Run tests against the staging Supabase project to validate that bucket policies are correctly applied before any production deployment.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Create two dedicated test Supabase auth users in the staging project: test-coordinator-org-a@example.com and test-coordinator-org-b@example.com, each assigned to their respective org via user metadata or a profiles row. Store credentials in CI secrets. The bucket path convention should match the production design: {org_id}/{export_id}.{ext} — test both valid and invalid path patterns. For the signed URL expiry test, note that Supabase enforces expiry server-side so a 1-second TTL is reliable; document the 2-second wait to avoid flakiness.
The oversized file test requires knowing the configured max_file_size for the bucket — retrieve this from the staging Supabase dashboard and document it in a comment. Use addTearDownLast to ensure cleanup runs even if the test assertion fails. These tests act as the production gate for RLS policy correctness — a failing test here means the bucket policy SQL migration is wrong, not the Flutter code.
Testing Requirements
Integration tests using the flutter_test integration_test package connected to the real staging Supabase project. Use Supabase.instance.auth.signInWithPassword() with dedicated test user accounts (one per test org) created in staging. Wrap each test in setUp/tearDown that authenticates and cleans up uploaded files. Test the signed URL expiry by setting expiresIn: 1 in the createSignedUrl call and using Future.delayed(Duration(seconds: 2)) before asserting expiry.
Assert HTTP status codes directly from the StorageException or Response object. Tag tests with @Tags(['integration']) so they are excluded from unit test runs. Store staging URL and anon key in CI environment variables SUPABASE_TEST_URL and SUPABASE_TEST_ANON_KEY.
NHF's three-level hierarchy (national / region / chapter) with 1,400 chapters may have edge cases such as chapters belonging to multiple regions, orphaned nodes, or missing parent links in the database. Incorrect scope expansion would silently under- or over-report activities, which could invalidate a Bufdir submission.
Mitigation & Contingency
Mitigation: Obtain a full hierarchy fixture export from NHF before implementation begins. Write exhaustive unit tests covering boundary cases: single chapter, full national roll-up, chapters with no activities, and chapters assigned to multiple regions. Validate resolver output against a known-good manual count.
Contingency: If hierarchy data quality is too poor for automated resolution at launch, implement a manual scope override in the coordinator UI that allows the coordinator to explicitly select org units from a tree picker, bypassing the resolver.
The activity_type_configuration table may not cover all activity types currently in use, leaving a subset unmapped at launch. Bufdir submissions with unmapped categories will be incomplete and may be rejected by Bufdir.
Mitigation & Contingency
Mitigation: Run a query against production activity data before implementation to enumerate all distinct activity type IDs. Cross-reference with Bufdir's published category schema (request from Norse Digital Products). Flag every gap as a known issue and build the warning surface into the preview panel.
Contingency: Implement a fallback 'Other' category bucket for unmapped types and surface a prominent warning in the export preview requiring coordinator acknowledgement before proceeding. Log unmapped types for post-launch cleanup.
Supabase RLS policies on generated_reports and the storage bucket must enforce strict org isolation. A misconfigured policy could allow a coordinator from one organisation to read another organisation's export files, creating a serious data breach with GDPR implications.
Mitigation & Contingency
Mitigation: Write RLS integration tests that attempt cross-org reads with explicitly different JWT tokens and assert that all attempts return empty sets or 403 errors. Include RLS policy review in the pull request checklist. Use Supabase's built-in policy tester during development.
Contingency: If a policy gap is discovered post-deployment, immediately revoke all signed URLs for affected exports, audit the access log for unauthorised reads, and issue a coordinated disclosure to affected organisations per GDPR breach notification requirements.