Attachments: EPIC 1 — Generalize Attachment Storage - JU-DEV-Bootcamps/ERAS GitHub Wiki

Description: The purpose of this EPIC is to extend the existing, already-decoupled IFileStorageService/LocalFileStorageService/IFileEncryptionService pair into a full generic attachment subsystem: a metadata model, richer content validation, a reusable service/API layer, and migration of Interventions.Attachments/AttachmentHashes. This does not replace working infrastructure — it builds the missing layer on top of it.

Acceptance Criteria:

  • Any entity can persist/retrieve attachments through a single generic service — no entity owns its own storage logic.
  • Existing behaviors are preserved: AES encryption at rest, SHA-256 dedup-on-upload.
  • Interventions no longer stores raw text[] columns; it uses the new subsystem exclusively.
  • All existing attachments (path + hash) are migrated with zero data loss.

User Story 1.1 — Define centralized Attachment Metadata Model

Description: As a system architect, I want a generic Attachment entity decoupled from any specific business entity, so metadata for files belonging to any entity can be stored and queried consistently — something the current text[] columns cannot express.

Acceptance Criteria:

  • Table attachments should be created with this columns:
Column Description
id Attachment Id
entity_type Type of Entity to which the attachment belongs
entity_id Id of the specific entity
original_file_name Original file name
storage_key Location of the file. For the Local File Storage provider, this value is the path on the file system; for OpenStack Swift, this value is the object name within its container
storage_provider Identifier of the Storage Provider. Initialy LocalFileSystem
mime_type File mime type
size_bytes Size of the file in bytes
content_hash File hash value
created_at Upload date of the File
created_by User identifier
  • A Composite index on (entity_type, entity_id) columns should created.
  • Migration should be versioned (up/down) and documented in an ER diagram.
  • Documented, permanent limitation: rows backfilled from legacy Interventions data will have original_file_name, mime_type, and size_bytes as null/unknown, since that information was never captured historically — only path and hash exist to migrate.

Tasks:

  • Task 1.1.1 — Design & implement DB migration for attachments table AC: Migration runs cleanly up/down locally; size_bytes as bigint; content_hash reuses the same SHA-256 format already produced by the current upload handler.
  • Task 1.1.2 — Add composite index and document the model AC: Query plan confirms index usage; Update ER diagram.

User Story 1.2 — Extend current IFileStorageService into the Generic Storage Contract

Description: As a developer, I want the existing IFileStorageService extended (not replaced) so the rest of the application can depend on the abstraction for both existing and new capabilities — notably, it currently has no Exists check and no URL/signed-URL concept at all (only raw stream read/write).

Acceptance Criteria:

  • Interface gains Exists(key) and a GetUrl(key) (or equivalent) capability alongside the existing SaveAsync/ReadAsync/DeleteAsync.
  • No module outside concrete provider implementations imports fs/disk APIs or a storage SDK directly (already true today — preserve it).
  • Extended interface is documented with expected inputs/outputs and error behavior.

Tasks:

  • Task 1.2.1 — Add Exists(key) to IFileStorageService and implement in LocalFileStorageService AC: Method correctly reflects on-disk presence without needing a full read; covered by unit test.
  • Task 1.2.2 — Build shared test harness for provider contract compliance Description: Reusable test suite validating any implementation of the extended interface (including the new Exists/GetUrl members), so a future OpenStack Swift provider is held to the same bar as the local one. AC: Suite runnable against any class implementing IFileStorageService; fails clearly on a missing/misbehaving method.

User Story 1.3 — Generalize the Local Provider's Partitioning Scheme

Description: As a system, I want LocalFileStorageService's existing folder-partitioning (interventions/{id}) generalized to an entity-agnostic scheme, so any entity type can use the same provider without code changes — while preserving the encryption and dedup behavior it already provides.

Acceptance Criteria:

  • Keys are generated as {entityType}/{entityId}/{uuid}.{ext} (or {entityType}/{entityId}/{yyyy}/{mm}/{dd}/{uuid}.{ext} if volume analysis in Task 1.3.2 justifies date partitioning).
  • AES encryption-at-rest and Unix file-mode restrictions continue to apply unchanged.
  • The stored key is never relied upon to recover the original file name — that lives only in the new Attachment metadata.

Tasks:

  • Task 1.3.1 — Add Exists() and generalize the folder/key scheme in LocalFileStorageService AC: Passes the shared provider contract test suite (Task 1.2.2); existing Interventions upload/download/delete integration tests still pass unmodified against the new key scheme.
  • Task 1.3.2 — Evaluate whether date-partitioning is needed given current attachment volume AC: Decision documented with rationale (file counts per entity today vs. a configurable per-directory threshold); implemented only if justified.

User Story 1.4 — Generic AttachmentService & REST Endpoints

Description: As a frontend/consumer of the API, I want a single reusable set of endpoints to upload, list, download, and delete attachments for any entity, replacing the Intervention-specific MediatR commands and the IAssessmentRepository array-mutation methods that exist today.

Acceptance Criteria:

  • AttachmentService exposes uploadAttachment, listAttachments, getAttachmentUrl/downloadStream, deleteAttachment, backed by the attachments table and IFileStorageService.
  • Upload is transactional: if physical storage fails, no metadata record is orphaned (and vice versa).
  • The service absorbs the existing SHA-256 dedup-on-upload behavior (currently living in UploadInterventionAttachmentsCommandHandler) generically, keyed by (entity_type, entity_id, content_hash).
  • The service enforces a per-entity-type configurable max-attachment count, generalizing the hardcoded "5 per intervention" rule from the unmerged branch — coordinate so that branch's change is superseded here rather than merged separately.
  • REST endpoints: POST /attachments, GET /attachments, GET /attachments/:id/download, DELETE /attachments/:id, parametrized by entityType/entityId.
  • entityType is validated against a whitelist of registered entities.

Tasks:

  • Task 1.4.1 — Implement AttachmentService methods AC: Unit tests cover success, dedup-skip, max-count-rejection, and rollback-on-failure paths for upload.
  • Task 1.4.2 — Implement REST endpoints and entityType whitelist validation AC: Requests with an unregistered entityType return 4xx with a clear error; integration tests cover all four endpoints.

User Story 1.5 — Real Content Validation on Upload

Description: As a system, I want to validate real file content (magic bytes) and actually enforce the configured size limit, replacing today's extension-only check and the currently dead MaxFileSizeBytes setting.

Acceptance Criteria:

  • Uploaded files are inspected via magic bytes at upload time; a renamed executable disguised as .jpg is rejected.
  • FileStorageSettings.MaxFileSizeBytes is actually enforced — oversized uploads are rejected with a descriptive error, not silently accepted as today.
  • Extension alone is never the sole validation signal.

Tasks:

  • Task 1.5.1 — Integrate magic-byte/content-type detection AC: Test case with a renamed executable disguised as an allowed extension is rejected before persistence.
  • Task 1.5.2 — Enforce MaxFileSizeBytes and make the allowed-type whitelist configurable per environment AC: Oversized upload returns a descriptive 4xx; whitelist changeable without code changes.

User Story 1.6 — Legacy Data Migration & Interventions Refactor

Description: As a system owner, I want the existing Interventions.Attachments/AttachmentHashes arrays migrated into the new model and Interventions refactored to call AttachmentService, so the legacy columns can be retired.

Acceptance Criteria:

  • One Attachment record is created per existing (path, hash) pair — the hash carries over directly since it's already computed today; original_file_name/mime_type/size_bytes remain null for these rows (documented limitation, per US 1.1).
  • Post-migration validation confirms migrated record count equals original path count per intervention.
  • AssessmentsController/handlers delegate to AttachmentService instead of calling IFileStorageService/IAssessmentRepository directly.
  • The legacy text[] columns are removed only after migration is validated in production.

Tasks:

  • Task 1.6.1 — Write and run the data migration script AC: Dry-run mode available; count validation passes per intervention; backup taken before running against production.
  • Task 1.6.2 — Refactor Interventions upload/download/delete to use AttachmentService AC: All existing Interventions attachment integration/unit tests (UploadInterventionAttachmentsCommandHandlerTests, DeleteInterventionAttachmentCommandHandlerTests) pass against the new implementation, or are ported to test AttachmentService directly with equivalent coverage.
  • Task 1.6.3 — Deprecate and remove attachments/attachment_hashes columns on Interventions AC: Removed via a separate, reversible migration; no remaining code references either column.