critical priority low complexity backend pending backend specialist Tier 2

Acceptance Criteria

`ActivityAttachmentRepository` Dart class is implemented with the three specified public methods
`insertAttachment(ActivityAttachment attachment)` inserts a row and returns the persisted `ActivityAttachment` with server-assigned `id` and `created_at`
`getActiveAttachmentsForActivity(String activityId)` returns `List<ActivityAttachment>` filtered by `activity_id` and `deleted_at IS NULL`
`countAttachmentsForActivity(String activityId)` returns an `int` count of active attachments for the given activity
All three methods automatically include `org_id` from the active Supabase session context — no caller needs to pass org_id explicitly
`ActivityAttachment` is a typed Dart model with `fromJson` / `toJson` covering all 10 database columns
Repository throws a typed `AttachmentRepositoryException` (or equivalent project exception type) on Supabase errors
Unit tests cover happy path and error path for all three methods using a mocked Supabase client
Repository follows the project's existing repository pattern and is injectable via Riverpod or BLoC provider

Technical Requirements

frameworks
Flutter
Riverpod
supabase_flutter
apis
Supabase REST API (via supabase_flutter client)
Supabase Auth (session for org_id)
data models
ActivityAttachment
performance requirements
`getActiveAttachmentsForActivity` must return results within 500ms for activities with up to 50 attachments
`countAttachmentsForActivity` must use a COUNT query, not fetch all rows and count in Dart
security requirements
org_id must always be sourced from the authenticated session JWT — never from caller-supplied parameters
No raw SQL construction — use only the Supabase Flutter client's typed query builder to prevent injection
Repository must not cache results in memory beyond the scope of a single request

Execution Context

Execution Tier
Tier 2

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.

Component
Activity Attachment Repository
data low
Epic Risks (3)
high impact medium prob security

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.

medium impact low prob dependency

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.

high impact low prob integration

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.