Document Attachment
Data Entity
Description
A file (PDF, JPEG, PNG) attached to an activity record as supporting evidence for Bufdir audit purposes. Used by NHF to attach event invitations, Facebook screenshots, and promotional flyers. Files are validated against allowed MIME types and a 10 MB size limit before upload to organization-scoped Supabase Storage buckets.
Data Structure
| Name | Type | Description | Constraints |
|---|---|---|---|
id |
uuid |
Immutable primary key generated on insert. Used as the stable reference for the attachment record across all services and the storage path segment. | PKrequiredunique |
activity_id |
uuid |
Foreign key referencing the parent activity record this attachment supports. An attachment must always be linked to exactly one activity; orphaned attachments are not permitted. | required |
organization_id |
uuid |
Foreign key referencing the organization that owns this attachment. Used as the first path segment in the Supabase Storage bucket path to enforce org-scoped isolation. Denormalized from the parent activity for efficient RLS policy evaluation without a join. | required |
file_name |
string |
Original filename as supplied by the user at upload time, including the file extension. Stored for display in the attachment thumbnail grid and preview modal. Must not be empty and must not exceed 255 characters. | required |
mime_type |
enum |
IANA media type of the uploaded file. Constrained to the three allowed formats: PDF documents and JPEG/PNG images. Validated server-side by the attachment upload service before the file is written to storage. | required |
storage_path |
string |
Fully qualified path of the object within the Supabase Storage bucket, following the pattern '{organization_id}/{activity_id}/{id}/{file_name}'. Used by the Supabase Storage Adapter to generate signed download URLs and to delete the object when the record is soft-deleted. | requiredunique |
file_size_bytes |
integer |
Size of the uploaded file in bytes as measured server-side after upload. Must be greater than zero and must not exceed 10,485,760 bytes (10 MB). Stored for display in the UI and for audit purposes. | required |
uploaded_at |
datetime |
UTC timestamp recording when the attachment record was persisted to the database. Set automatically by the database on insert and never updated. Used to order attachments chronologically in the thumbnail grid. | required |
uploaded_by_user_id |
uuid |
Foreign key referencing the authenticated user who performed the upload. Captures audit provenance — typically a coordinator for NHF. Required for the immutable audit trail required by Bufdir. | required |
is_deleted |
boolean |
Soft-delete flag. When true, the record is excluded from all active queries but retained for audit history. The corresponding Supabase Storage object is removed asynchronously after this flag is set. Defaults to false on creation. | required |
deleted_at |
datetime |
UTC timestamp set when is_deleted is set to true. Null for active attachments. Stored as timestamptz. Retained for audit log completeness — records exactly when a document was removed from an activity. | - |
deleted_by_user_id |
uuid |
Foreign key referencing the authenticated user who performed the soft delete. Null for active attachments. Required for the audit trail when is_deleted is true. | - |
Database Indexes
idx_document_attachment_activity_id
Columns: activity_id
Supports the primary read pattern: fetching all non-deleted attachments for a given activity to populate the thumbnail grid.
idx_document_attachment_organization_id
Columns: organization_id
Supports RLS policy evaluation and org-scoped attachment queries, including the Bufdir export bundler that fetches attachments for all activities within a reporting period for a given organization.
idx_document_attachment_activity_is_deleted
Columns: activity_id, is_deleted
Composite index optimizing the most common active-record query: WHERE activity_id = $1 AND is_deleted = false.
idx_document_attachment_storage_path
Columns: storage_path
Enforces storage path uniqueness at the database level and supports fast lookup when the storage adapter resolves a path to a record for deletion or URL generation.
idx_document_attachment_uploaded_by
Columns: uploaded_by_user_id
Supports audit queries filtering attachments by the uploading user, used by coordinator audit reporting.
idx_document_attachment_organization_uploaded_at
Columns: organization_id, uploaded_at
Supports the Bufdir export bundler when querying attachments within a reporting period, filtering by organization and upload date range.
Validation Rules
allowed_mime_types_only
error
Validation failed
file_size_max_10mb
error
Validation failed
file_size_min_1_byte
error
Validation failed
file_name_non_empty
error
Validation failed
storage_path_unique
error
Validation failed
deleted_at_consistency
error
Validation failed
signed_url_expiry_enforced
warning
Validation failed
activity_not_deleted_on_attach
error
Validation failed
Business Rules
attachment_must_reference_existing_activity
Every document attachment must reference a valid, non-deleted activity record. An attachment cannot exist without its parent activity. If the parent activity is deleted (cascading), all associated attachments are also soft-deleted and their storage objects removed.
organization_scope_isolation
A document attachment's organization_id must match the organization_id of its parent activity record. Coordinators may only attach documents to activities belonging to their own organization. Enforced via Supabase RLS policies and validated server-side before upload.
immutable_after_creation
Attachment records are immutable once created. No UPDATE operations are permitted on any field. To replace a file, the existing attachment must be soft-deleted and a new one created. This preserves the integrity of the Bufdir audit trail.
soft_delete_triggers_storage_removal
When is_deleted is set to true, the attachment-upload-service must asynchronously delete the corresponding object from Supabase Storage using the stored storage_path. The database record is retained permanently for audit purposes. deleted_at and deleted_by_user_id must be populated atomically with is_deleted.
bufdir_export_includes_attachments
When generating a Bufdir report export, the export orchestrator must include all non-deleted document attachments associated with activities within the reporting period. The Bufdir attachment bundler produces a ZIP archive of physical files and a manifest linking each file to its activity record.
uploader_must_be_coordinator_or_admin
Only users with the coordinator or organization admin role may attach documents to activity records. Peer mentors cannot upload attachments. This restriction reflects the NHF workflow where coordinators attach audit evidence on behalf of the organization.
CRUD Operations
Storage Configuration
Entity Relationships
An activity record may have multiple supporting document attachments (invitations, screenshots, flyers) for Bufdir audit evidence