Provision Supabase Storage bucket with org-scoped RLS
epic-bufdir-report-export-foundation-task-003 — Create the bufdir-exports Supabase Storage bucket. Configure org-scoped access policies so that files stored at path {org_id}/{report_id}.{ext} are only accessible by members of that organisation. Enable public read URLs with expiring signed URLs (7-day TTL) for re-download. Write the bucket policy migration script and document the path convention. Bucket must pass security review before any file writes begin.
Acceptance Criteria
Technical Requirements
Implementation Notes
In Supabase, storage RLS policies are written against the `storage.objects` table. The `name` column contains the full path including bucket name in some versions — use `storage.foldername(name)` helper to extract path segments reliably. The `(storage.foldername(name))[1]` expression extracts the first folder (org_id in the path convention). The user_roles lookup in the RLS policy will execute once per row evaluation — ensure the user_roles table has an index on `user_id` (it likely already exists from auth setup).
Do NOT make the bucket public even for signed URLs — signed URLs work on private buckets via Supabase's signed URL mechanism which bypasses RLS using a time-limited token. Define the 7-day TTL as a constant in the migration SQL comment and reference it in the Flutter adapter (task-004) to keep them in sync. If the Supabase project uses a custom schema for user_roles (not public), adjust the RLS policy schema prefix accordingly.
Testing Requirements
Manual verification steps documented as a checklist in the PR: (1) Upload a test file as service role to `{test_org_id}/{test_report_id}.csv`, (2) Authenticate as a user from test_org and verify GET succeeds, (3) Authenticate as a user from a different org and verify GET returns 403, (4) Attempt INSERT as authenticated user (not service role) and verify 403, (5) Generate a signed URL and verify it works without auth headers, (6) Verify the signed URL is rejected after manual expiry (or use a short test TTL). No automated flutter_test coverage required for this infrastructure task — manual checklist is the acceptance gate.
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.