Configure Supabase Storage bucket and bucket RLS
epic-document-attachments-foundation-task-003 — Create the 'activity-attachments' storage bucket in Supabase with private access. Implement bucket-level RLS policies so only authenticated users whose org_id matches the path prefix (e.g., {org_id}/{activity_id}/{filename}) can read or write objects. Coordinators and admins may upload and delete; peer mentors may only download via signed URL. Document the path convention.
Acceptance Criteria
Technical Requirements
Implementation Notes
Create the bucket via Supabase CLI seed or dashboard, then export the config to `supabase/config.toml` or a seed SQL file for reproducibility. Storage RLS policies are defined as SQL in `supabase/migrations/` targeting the `storage.objects` table. The `name` column in `storage.objects` contains the full path; use `split_part(name, '/', 1)` to extract the org_id segment and compare against the JWT claim. For signed URLs, implement a Supabase Edge Function or Dart repository method that calls `supabase.storage.from('activity-attachments').createSignedUrl(path, 3600)` and returns the URL to the client — never expose the path directly to peer mentor clients.
Set a bucket file size limit via the Supabase dashboard (`file_size_limit`) and an `allowed_mime_types` allowlist (e.g., `image/jpeg`, `image/png`, `application/pdf`).
Testing Requirements
Manual + scripted integration tests against a local Supabase instance: (1) coordinator uploads a file to `{own_org_id}/{activity_id}/test.pdf` — expect 200; (2) coordinator attempts upload to `{other_org_id}/{activity_id}/test.pdf` — expect 403; (3) peer mentor attempts direct download without signed URL — expect 403; (4) peer mentor downloads via signed URL generated for their org — expect 200; (5) peer mentor attempts upload — expect 403; (6) coordinator deletes an object — expect 200; (7) peer mentor attempts delete — expect 403. Document these test cases as a runbook in the repo.
Supabase RLS policies may not cover all query paths (e.g., service-role key usage in edge functions), potentially exposing attachment metadata or objects from another organisation to an unauthorised actor, breaching GDPR requirements.
Mitigation & Contingency
Mitigation: Add org_id scoping as an explicit WHERE clause at the Dart repository level as a second line of defence. Document which queries use the anon key versus service-role key, and audit all edge function calls that touch the storage bucket.
Contingency: If a bypass is discovered post-deployment, immediately revoke the affected signed URLs, rotate the service-role key, add the missing org_id filter, and deploy a patch. Notify affected organisations per GDPR breach protocol.
Supabase free/pro tier storage quotas may be exceeded earlier than expected if organisations upload large PDFs frequently, causing upload failures with no graceful degradation for users.
Mitigation & Contingency
Mitigation: Configure a 10 MB per-file cap enforced in the upload service (Epic 2), and add a storage usage monitoring alert at 80% of the allocated quota. Document the upgrade path in runbooks.
Contingency: If the quota is hit, temporarily disable new uploads via the org-level feature flag (attachments_enabled) and upgrade the Supabase plan. Communicate clearly to affected coordinators with an estimated restoration time.
The feature documentation specifies a migration order dependency: the activity_attachments table must be created after the activities table and before the Bufdir export join query is updated. Running migrations out of order will cause foreign-key or join failures.
Mitigation & Contingency
Mitigation: Add the migration to the numbered Supabase migration sequence immediately after the activities table migration. Add a CI check that runs migrations in order against a clean schema.
Contingency: If a deployment runs migrations out of order, roll back via the Supabase migration rollback script, reorder, and redeploy. No data loss occurs as attachments do not exist yet at that point.