Implement Supabase Storage RLS for export bucket
epic-bufdir-reporting-export-foundation-task-006 — Configure Storage access policies on the 'bufdir-exports' bucket so that users can only read and write files under their own org_id path prefix. Deny all cross-org path access. Implement signed URL generation logic with a configurable TTL (default 15 minutes) for secure, time-limited file downloads.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Supabase Storage policies are defined on the storage.objects table using SQL expressions referencing (storage.foldername(name))[1] to extract the first path segment (org_id). Policy expression for INSERT: (auth.jwt() ->> 'org_id') = (storage.foldername(name))[1]. Same expression applies to SELECT and DELETE policies. Create policies via Supabase migration SQL or the dashboard Storage > Policies tab — choose migration SQL for reproducibility.
The signed URL TTL should be passed as a Duration in the Dart adapter (task-009) and converted to integer seconds before calling createSignedUrl. Test the short-TTL edge case by creating a signed URL with expiresIn=1, sleeping 2 seconds, then asserting the URL is rejected. Ensure the bucket MIME type restrictions are configured to allow only text/csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, and application/json to prevent arbitrary file uploads.
Testing Requirements
Integration tests against Supabase test instance or local supabase CLI. Test cases: (1) upload a file to '{org_id}/{export_id}.csv' with matching org JWT — expect 200; (2) upload to '{other_org_id}/{export_id}.csv' with mismatched org JWT — expect 400/403; (3) generate signed URL for own file — expect valid URL with expiresIn matching TTL; (4) attempt to generate signed URL for a file in another org's prefix — expect error; (5) access signed URL before expiry — expect file bytes returned; (6) access signed URL after TTL has elapsed (mock clock or use very short TTL in test) — expect 400; (7) upload a file with a path containing '..' — expect rejection at path validation layer before reaching storage. Use flutter_test with real Supabase test credentials injected via environment variables.
RLS policies for the audit log and schema config tables must correctly handle multi-chapter membership hierarchies (up to 1,400 local chapters for NHF). Incorrect policies could either over-expose data across organisations or prevent legitimate coordinator access, both of which are serious compliance failures.
Mitigation & Contingency
Mitigation: Design RLS policies using the existing org hierarchy resolver pattern. Write integration tests that verify cross-organisation isolation with representative fixture data covering NHF's multi-level hierarchy before merging.
Contingency: If RLS policies prove too complex to express safely in Postgres, implement a Supabase Edge Function as a data access proxy that enforces isolation in application code, with RLS serving as a secondary defence layer.
Bufdir's column schema is expected to evolve as Norse Digital Products negotiates a simplified digital reporting format. If the schema config versioning model is too rigid, applying Bufdir schema updates without a code deployment could fail, forcing emergency releases.
Mitigation & Contingency
Mitigation: Design the schema config table to store the full JSON column mapping as a JSONB field with a version number. Provide an admin API to upsert new versions without any schema migration required.
Contingency: If the versioning model is insufficient for a Bufdir schema change, fall back to a code deployment with the updated default schema, using the database config only for org-specific overrides.