Implement ActivityAttachmentRepository soft-delete

epic-document-attachments-foundation-task-005 — Add the softDeleteAttachment(attachmentId) method to ActivityAttachmentRepository. The method sets deleted_at to the current UTC timestamp for the specified record, verifying the caller's org_id matches the record's org_id before updating. Return a typed Result or throw a domain exception on org mismatch or record not found. Write unit tests using a mocked Supabase client.

high priority low complexity backend pending backend specialist Tier 3

Acceptance Criteria

softDeleteAttachment(attachmentId) sets deleted_at to DateTime.now().toUtc() on the target record in Supabase

Method verifies org_id of the caller matches the record's org_id before executing the update; throws OrgMismatchException if they differ

Method returns a typed Result<void, AttachmentRepositoryError> (or throws a domain exception) rather than a raw Supabase response

AttachmentNotFoundError is returned/thrown when no record with the given attachmentId exists

Soft-deleted records (deleted_at IS NOT NULL) are excluded from all active-attachment queries in the repository (e.g., getAttachmentsForActivity)

The deleted_at timestamp is stored in UTC and round-trips correctly when read back

Unit tests cover: successful soft-delete, org mismatch case, record-not-found case, and that the deleted record is excluded from subsequent active queries

All unit tests pass using a mocked Supabase client with no real network calls

Technical Requirements

frameworks

Flutter

Supabase Dart SDK

apis

Supabase PostgREST update API

Supabase PostgREST select API

data models

ActivityAttachment (id, org_id, activity_id, storage_path, file_name, mime_type, file_size_bytes, created_by, created_at, deleted_at)

performance requirements

Soft-delete must complete in a single Supabase update call — no multi-round-trip fetch-then-update

org_id filter applied at the Supabase query level (.eq('org_id', orgId)) to leverage RLS and avoid over-fetching

security requirements

org_id equality check must be enforced in the query filter (.eq('org_id', orgId).eq('id', attachmentId)) so RLS acts as a second layer of defence

deleted_at column must not be settable to arbitrary past timestamps from the client — always use server-side NOW() or DateTime.now().toUtc()

No attachment data (file name, path, org) is returned in error messages to avoid information leakage

Execution Context

Execution Tier

Tier 3

Tier 3 - 413 tasks

Can start after Tier 2 completes

View Full Execution Plan

Implementation Notes

Use Supabase's `.update({'deleted_at': DateTime.now().toUtc().toIso8601String()}).eq('id', attachmentId).eq('org_id', orgId).select('id')` pattern and check that the returned list is non-empty to distinguish not-found from org-mismatch (or use `.count()` with `CountOption.exact`). Define the error hierarchy as a sealed class or freezed union: `AttachmentRepositoryError` with variants `notFound`, `orgMismatch`, `unexpected(Object error)`. Do NOT hard-delete the record — the physical file deletion in storage is handled by a separate flow. Ensure all active-query methods in the repository already use `.isNull('deleted_at')` (added in task-004); if not, add that filter guard here.

Keep the method signature consistent with the rest of the repository (async, returns `Future>`).

Testing Requirements

Unit tests only (flutter_test). Use mockito or mocktail to mock the SupabaseClient. Test cases: (1) happy path — update is called with correct eq filters and deleted_at value; (2) org mismatch — Supabase returns 0 updated rows for org filter mismatch, repository throws/returns OrgMismatchException; (3) record not found — Supabase returns 0 rows, repository throws/returns AttachmentNotFoundError; (4) active-query exclusion — getAttachmentsForActivity query includes .isNull('deleted_at') filter and is verified by checking query builder arguments. Target 100% branch coverage for the softDeleteAttachment method.

Component

Activity Attachment Repository

data low

Dependencies (1)

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. epic-document-attachments-foundation-task-004

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.

Quick Links

All Tasks Execution Plan