Integration test: repository and storage adapter end-to-end

epic-document-attachments-foundation-task-008 — Write integration tests that exercise the complete data layer together: upload a file via SupabaseStorageAdapter, insert the corresponding record via ActivityAttachmentRepository, query active attachments to verify the record appears, generate a signed URL and assert it is non-empty, soft-delete the record and confirm it no longer appears in active queries, and assert that a second org cannot access the first org's records (RLS cross-org isolation). Use flutter_test with a test Supabase project or local emulator.

high priority medium complexity testing pending testing specialist Tier 4

Acceptance Criteria

Integration test suite runs against a real (test-project or local emulator) Supabase instance, not mocks

Test: upload a small binary file via SupabaseStorageAdapter.uploadFile and assert the returned storage path matches the expected '{orgId}/{activityId}/{fileName}' format

Test: insert an ActivityAttachment record via ActivityAttachmentRepository.insertAttachment using the returned storage path and assert the record is returned by getAttachmentsForActivity

Test: generate a signed URL via SupabaseStorageAdapter.createSignedUrl for the inserted record's path and assert the returned URL is non-empty and starts with 'https://'

Test: call ActivityAttachmentRepository.softDeleteAttachment and assert the record no longer appears in getAttachmentsForActivity

Test: RLS cross-org isolation — authenticate as a user from org B, attempt to read or soft-delete a record owned by org A, and assert that an empty result or permission error is returned (not org A's data)

All test data (storage objects and database rows) are cleaned up after each test via tearDown to prevent state leakage

Tests are tagged (e.g., @Tags(['integration'])) and can be excluded from the standard unit-test run

Test suite completes without uncaught exceptions or test timeouts

Technical Requirements

frameworks

Flutter

flutter_test

Supabase Dart SDK

supabase_flutter test helpers

apis

Supabase Storage API (upload, delete, createSignedUrl)

Supabase PostgREST API (insert, select, update)

Supabase Auth API (sign in as test users for RLS validation)

data models

ActivityAttachment

StoragePath

Supabase test users (org A user, org B user)

performance requirements

Each integration test must complete within 15 seconds; total suite within 90 seconds

Use the smallest possible test file (e.g., 100 bytes) to minimise storage I/O time

security requirements

Test Supabase credentials (URL, anon key) must be stored in a .env.test file excluded from version control (.gitignore)

Test data must be scoped to unique orgIds and activityIds (e.g., UUID generated per test run) to prevent cross-run contamination

RLS test must authenticate as a different org's service-role or test user — never bypass RLS with service-role for the cross-org check

Execution Context

Execution Tier

Tier 4

Tier 4 - 323 tasks

Can start after Tier 3 completes

View Full Execution Plan

Implementation Notes

Create a test helper file `test/helpers/supabase_test_client.dart` that initialises a SupabaseClient from environment variables for integration tests. Use `flutter_dotenv` or direct `Platform.environment` reads so CI can inject secrets via env vars without committing them. Each test should call `setUp` to create fresh UUIDs (`const Uuid().v4()`) for orgId and activityId so tests are fully isolated. For the RLS cross-org test: sign in as org B user (use Supabase Auth `signInWithPassword`), then attempt `getAttachmentsForActivity` with org A's activityId — expect an empty list (RLS filters rows) or a PostgREST 403.

For tearDown: call `storage.from('activity-attachments').remove([storagePath])` and `supabase.from('activity_attachments').delete().eq('id', insertedId)`. Document the local emulator setup in a `TESTING.md` note or in the test file header for future contributors.

Testing Requirements

Integration tests using flutter_test targeting a real Supabase test project or local Supabase emulator (supabase start). Structure: setUp creates two test users (org A, org B) via Supabase Auth, generates unique UUIDs for orgId/activityId, tears down by deleting storage objects and database rows. Test groups: (1) 'upload → insert → query' happy path; (2) 'signed URL generation' verifying non-empty HTTPS URL; (3) 'soft-delete exclusion' confirming deleted record disappears from active queries; (4) 'RLS cross-org isolation' confirming org B cannot access org A records. Tag tests with @Tags(['integration']) and document in README how to run them (SUPABASE_TEST_URL and SUPABASE_TEST_ANON_KEY env vars required).

Component

Activity Attachment Repository

data low

Dependencies (4)

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. epic-document-attachments-foundation-task-005 Add the createSignedUrl(storagePath, expiresInSeconds) method to SupabaseStorageAdapter. The method requests a time-limited signed URL from Supabase Storage for the given object path and returns the URL string. Default expiry should be 3600 seconds. Handle storage errors and return a typed Result. Write unit tests verifying the correct bucket name and path are passed to the Supabase client. epic-document-attachments-foundation-task-007 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 Implement the SupabaseStorageAdapter Dart class with typed methods: uploadFile(orgId, activityId, fileName, bytes, mimeType) uploads the file to the activity-attachments bucket under the path {orgId}/{activityId}/{fileName} and returns the storage path, and deleteFile(storagePath) removes the object from the bucket. Handle Supabase storage errors with typed exceptions. Enforce the org-scoped path convention established in the bucket RLS task. epic-document-attachments-foundation-task-006

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