Implement ActivityAttachmentRepository insert and query
epic-document-attachments-foundation-task-004 — Implement the ActivityAttachmentRepository Dart class with typed methods: insertAttachment(ActivityAttachment) inserts a new record and returns the persisted entity, getActiveAttachmentsForActivity(activityId) returns all non-deleted attachments for an activity, and countAttachmentsForActivity(activityId) returns the integer count. Use the Supabase Flutter client. All queries must filter by org_id from the active session context to enforce isolation.
Acceptance Criteria
Technical Requirements
Execution Context
Tier 2 - 518 tasks
Can start after Tier 1 completes
Implementation Notes
Follow the existing repository pattern in the codebase (e.g., `ActivityRepository`). Inject `SupabaseClient` via constructor for testability. Extract `org_id` using `supabase.auth.currentSession?.user.userMetadata?['org_id']` or the equivalent JWT claim accessor used elsewhere in the project — check existing repositories for the canonical pattern. For `countAttachmentsForActivity`, use `.count(CountOption.exact)` on the Supabase query builder rather than fetching rows and calling `.length`.
For `insertAttachment`, call `.insert(attachment.toJson()).select().single()` to get the server-persisted record back. Define `ActivityAttachment.fromJson` to handle nullable `deleted_at` gracefully. Place the class in `lib/data/repositories/activity_attachment_repository.dart` and register it as a Riverpod `Provider` or `StateNotifierProvider` following the project's DI convention.
Testing Requirements
Unit tests using `flutter_test` with a mocked `SupabaseClient` (using `mocktail` or `mockito`). Test cases: (1) `insertAttachment` — mock returns inserted row JSON, assert returned model matches; (2) `insertAttachment` — mock throws PostgrestException, assert typed exception is rethrown; (3) `getActiveAttachmentsForActivity` — mock returns list JSON, assert returned list length and field mapping; (4) `getActiveAttachmentsForActivity` — empty result, assert empty list returned (not null or exception); (5) `countAttachmentsForActivity` — mock returns count response, assert correct integer; (6) all methods — verify org_id filter is applied by inspecting the query builder calls via mock verification. Target 100% branch coverage on the repository class.
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.