Activity Attachment Repository
Component Detail
Description
Data access layer for the activity_attachments table in Supabase. Handles insert, soft delete, and query operations for attachment metadata records, enforcing org_id scoping at query level as a second line of defence behind RLS.
activity-attachment-repository
Summaries
The Activity Attachment Repository is the data integrity layer that ensures every file uploaded by field staff is correctly registered against the right activity record and organisation. Without this component, attachments stored in cloud storage would have no traceable link to the activities they document, making compliance reporting impossible and audit trails unreliable. The org_id scoping enforced at the query level provides a secondary safeguard that keeps participant data segregated between organisations even if higher-level access control is misconfigured, directly reducing the risk of data leakage incidents that could carry regulatory penalties or reputational harm.
The Activity Attachment Repository is a low-complexity data access component with no external service dependencies — it communicates only with the Supabase database. The primary delivery consideration is agreeing on the `activity_attachments` table schema (columns, indexes, soft-delete strategy) early so that this repository, the Upload Service, and the Bufdir export query can be developed in parallel without schema conflicts. Testing must verify: successful insert and query round-trips, soft-delete returning the correct storage path for subsequent object removal, `countByActivityId` returning accurate totals for limit enforcement, and `getAttachmentsForExport` correctly joining multi-activity queries for the Bufdir export batch. Ensure org_id is included in all query WHERE clauses before QA sign-off.
The Activity Attachment Repository is a typed data access object over the `activity_attachments` Supabase table. `insertAttachment(record)` should accept a strongly typed record object (omitting `id` and `deleted_at`) and return the inserted row including its generated `id`. `deleteAttachment(attachmentId, orgId)` performs a soft delete by setting `deleted_at = now()` and returns the `storage_path` so the caller (Upload Service) can remove the corresponding storage object. All SELECT queries must filter on `deleted_at IS NULL` and include `org_id = orgId` as an explicit WHERE clause as a defence-in-depth measure alongside RLS.
`getAttachmentsForExport(activityIds)` should accept an array of UUIDs and return a flat result set suitable for batch inclusion in Bufdir export payloads. Consider adding a composite index on `(activity_id, org_id, deleted_at)` for query performance under load.
Responsibilities
- Insert attachment metadata record after successful storage upload
- Soft-delete attachment record and return storage path for object removal
- Query all active attachments for a given activity ID
- Count attachments per activity for limit enforcement
- Include attachment metadata in Bufdir export queries
Interfaces
insertAttachment(record)
deleteAttachment(attachmentId, orgId)
getByActivityId(activityId)
countByActivityId(activityId)
getAttachmentsForExport(activityIds)
Relationships
Dependents (2)
Components that depend on this component
Related Data Entities (1)
Data entities managed by this component