Activity Document
Data Entity
Description
Supporting file attachments linked to an activity, such as event invitations or Facebook screenshots required by NHF for Bufdir audit evidence. Stores metadata including original filename, storage path, thumbnail URL, and uploader identity.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Unique identifier for the document record, generated server-side using UUID v4. | PKrequiredunique |
activity_id |
uuid |
Foreign key referencing the parent activity this document is attached to. Cascade-deleted when the activity is removed. | required |
organization_id |
uuid |
Organization scope for RLS policy enforcement. Denormalized from the parent activity to support efficient access control without a join. | required |
file_name |
string |
Original filename as submitted by the client, including extension. Used for display in the document list and download prompts. | required |
file_size_bytes |
integer |
File size in bytes as measured before upload. Used to enforce the 10 MB per-file limit and to display human-readable sizes in the UI. | required |
content_type |
string |
MIME type of the uploaded file (e.g., image/jpeg, image/png, application/pdf). Determines whether thumbnail generation is attempted and which viewer to use. | required |
storage_path |
string |
Full object path within the Supabase Storage bucket, structured as {organization_id}/{activity_id}/{id}/{file_name}. Used to generate signed URLs for secure access. | requiredunique |
thumbnail_url |
string |
Storage path of the generated thumbnail image produced by the Supabase Edge Function after upload. Null for non-image files (e.g., PDFs). Signed URLs are generated on demand from this path. | - |
thumbnail_status |
enum |
Tracks the asynchronous thumbnail generation state. Allows UI to show a placeholder spinner while the Edge Function processes the image. | required |
uploaded_by |
uuid |
User ID of the peer mentor or coordinator who performed the upload. Retained for audit trail and coordinator oversight. | required |
uploaded_at |
datetime |
Server-side UTC timestamp of when the document metadata record was created, set automatically on insert. | required |
is_deleted |
boolean |
Soft-delete flag. When true, the document is hidden from the UI but metadata is retained for audit continuity until the storage object is physically purged. | required |
deleted_at |
datetime |
UTC timestamp of soft deletion. Null when the document is active. Used to schedule physical storage object cleanup. | - |
deleted_by |
uuid |
User ID of the actor who initiated the deletion. Null when the document is active. Supports audit trails for coordinator-initiated removals. | - |
Database Indexes
idx_activity_documents_activity_id
Columns: activity_id
idx_activity_documents_storage_path
Columns: storage_path
idx_activity_documents_uploaded_by
Columns: uploaded_by
idx_activity_documents_organization_id
Columns: organization_id
idx_activity_documents_activity_active
Columns: activity_id, is_deleted
Validation Rules
file_size_within_limit
error
Validation failed
allowed_content_types
error
Validation failed
activity_id_must_reference_existing_activity
error
Validation failed
file_name_not_empty
error
Validation failed
storage_path_unique
error
Validation failed
attachment_count_does_not_exceed_five
error
Validation failed
Business Rules
max_five_attachments_per_activity
An activity may have at most 5 document attachments. Any upload attempt that would exceed this limit must be rejected before the file is transmitted to Supabase Storage. The limit is enforced by counting non-deleted records for the given activity_id.
organization_scoped_access
A user may only read or delete documents belonging to activities within their own organization. Supabase RLS policies check that auth.uid() maps to a user_organization_membership for the document's organization_id. Coordinators within the same org may also read all documents for oversight and Bufdir audit export.
uploader_must_own_or_coordinate_activity
Only the peer mentor who owns the parent activity, or a coordinator/admin scoped to the same organization, may attach or remove documents. A peer mentor cannot attach documents to another user's activity.
cascade_storage_delete_on_activity_removal
When a parent activity is hard-deleted, all associated storage objects in Supabase Storage must also be purged to prevent orphaned files accumulating in the bucket. Soft-deleted documents must have their storage objects cleaned up within 30 days.
bufdir_audit_evidence_retention
Documents attached to activities that are included in a submitted Bufdir report must not be physically deleted for a minimum of 5 years, matching the Bufdir grant audit retention window. Soft-delete is permitted; physical purge is blocked when linked report exists.
signed_url_time_limited_access
Document files must never be exposed via public bucket URLs. All client access must be via signed URLs with a 15-minute TTL generated on demand by the Document Storage Service. This applies to both full-resolution files and thumbnails.
thumbnail_generated_asynchronously
Thumbnail generation is triggered via a Supabase Edge Function after the storage object is uploaded. The thumbnail_status field transitions from 'pending' to 'generated' or 'failed'. Files with non-image MIME types must immediately set thumbnail_status to 'not_applicable'.
CRUD Operations
Storage Configuration
Entity Relationships
Activities may have up to 5 supporting document attachments for Bufdir audit evidence