Configure RLS policies on activity_attachments table
epic-document-attachments-foundation-task-002 — Define Row Level Security policies on activity_attachments scoped to org_id. Coordinators and admins within the same organisation may SELECT, INSERT, and soft-delete (UPDATE deleted_at). Peer mentors may only SELECT non-deleted rows belonging to their own org_id. Enable RLS on the table and verify policies reject cross-org access in pgTAP tests.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 1 - 540 tasks
Can start after Tier 0 completes
Implementation Notes
Extract org_id and role from JWT using a helper function such as `current_user_org_id()` returning `(auth.jwt() ->> 'org_id')::uuid` and `current_user_role()` returning `auth.jwt() ->> 'role'`. Keep this logic in a shared function to avoid duplication across policies. Use `USING` clause for SELECT/DELETE and `WITH CHECK` for INSERT/UPDATE. For the soft-delete UPDATE policy, restrict the `WITH CHECK` to only allow changes where the only modified column is `deleted_at` — this can be enforced by adding a trigger that raises an exception if any other column is changed in the same UPDATE statement.
Coordinate with the Auth team to confirm JWT claim field names for `org_id` and `role` match those set by BankID/Vipps login flows.
Testing Requirements
pgTAP tests covering: (1) coordinator in org A can SELECT active attachments in org A; (2) coordinator in org A cannot SELECT attachments in org B; (3) coordinator can INSERT attachment with matching org_id; (4) coordinator can UPDATE deleted_at (soft-delete); (5) coordinator cannot hard-delete (DELETE) a row; (6) peer mentor can SELECT active attachments in own org; (7) peer mentor cannot INSERT; (8) peer mentor cannot UPDATE; (9) unauthenticated request returns 0 rows. Each test sets the Supabase JWT claim accordingly using `SET LOCAL role` and `SET LOCAL request.jwt.claims`.
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.