# File Service Design

## Status: DRAFT
## Date: 2026-04-16
## Scope: Upload, download, delete, current-file UX, migrated-file compatibility, comment attachments, and business-action uploads

---

## 1. What We're Solving

The current product has three different file stories at once:

- a runtime upload/download path that behaves like a lightweight local attachment service
- UI paths that treat `_System_File` as a single-parent attachment table
- migrated database data that still follows the Salesforce Files model:
  - `contentdocument`
  - `contentversion`
  - `contentdocumentlink`

That creates four concrete problems:

1. New uploads do not follow a stable product model.
2. Old migrated files do not line up with the current runtime attachment flow.
3. File entry points are fragmented:
   - record detail related list
   - comment attachments
   - `Upload Archived OF`
   - archived Contract upload
   - any future upload action
4. The current design direction is heavier than the product requirement:
   - users need a simple attachment system
   - not a user-visible multi-version file platform

The product requirement is now intentionally narrow:

- users only need to see current files
- users only need upload / download / delete / file info
- users do not need version history UI
- migrated files only need latest-version compatibility

The design goal is therefore:

- use `_System_File` as the runtime attachment model for all new file flows
- use object storage as the canonical binary store
- use `Content*` tables only as migrated compatibility source data
- support latest-file-only migrated download compatibility
- keep the user-facing model simple enough to ship quickly

---

## 2. Design Decisions

This PRD makes the following decisions explicit:

1. `_System_File` is the only runtime attachment table for new uploads, downloads, deletes, and file lists.
2. `ContentDocument` / `ContentVersion` / `ContentDocumentLink` are not part of the new runtime write path.
3. For migrated data, only the latest file per linked object must be supported.
4. Migrated `Content*` data is compatibility input, not the primary runtime model.
5. Business-action uploads for `Order` and `Contract` use the same `_System_File` runtime model.
6. Post-upload status transitions for `Order` / `Contract` are server-owned business logic, not frontend-owned behavior.
7. Multi-version user experience is out of scope.

---

## 3. Product Scope

### 3.1 Must Have

- unified upload service
- unified download service
- unified delete/remove service
- current-file list and file-info presentation on object pages
- object-level visibility:
  - if a user can read the linked object, the user can read and download its files
- binary storage on S3-compatible object storage
- support for record-level files on:
  - Quote
  - Contract
  - Order
  - Case
- support for comment attachments
- support for business-action uploads on:
  - archived Contract upload
  - archived Order upload
- support for common office file formats, at minimum:
  - PDF
  - Word
  - PowerPoint / Slides
- support for latest-file-only compatibility on migrated records

### 3.2 Non-Goals for Phase 1

- user-visible version history
- historical-version download UX
- version rollback / compare UI
- dedicated file detail pages
- owner-only private download rules for normal record attachments
- full Salesforce Files parity
- public link management
- browser-direct multipart upload to object storage

### 3.3 User-Facing Contract

- users see only the current effective file list for an object
- upload, download, delete, and basic file information are the release-blocking capabilities
- replacing a file is a product-level action on the current file, not a user-visible version-history workflow
- browser preview for browser-supported formats is desirable, but not required for initial delivery

### 3.4 Business Workflow Rules

- archived-file uploads for `Contract` and `Order` are business actions layered on top of the generic file service
- when archived Contract upload succeeds under the approved business preconditions, the system must support the downstream transition to `Effective`
- when archived Order upload succeeds under the approved business preconditions, the system must support the downstream transition to `Activated`
- these status transitions must be deterministic and server-owned
- this PRD does not require Salesforce Flow as the implementation mechanism:
  - explicit backend action logic
  - or a separate automation layer
  - are both acceptable if they are idempotent and testable

### 3.5 Hard Constraints

- local disk cannot remain the canonical storage target
- frontend should not remain responsible for post-upload metadata composition
- `_System_File` must have one stable runtime meaning
- `ParentId` for runtime attachments must always mean the actual parent object or comment ID
- `Content*` tables may remain in the database, but they are compatibility input rather than runtime write targets

---

## 4. Current State

### 4.1 Runtime Behavior Today

#### 4.1.1 Upload and download backend

Routes:

- `backend/cmd/server/routes.go`

Handler:

- `backend/internal/interfaces/rest/file_upload_handler.go`

Current behavior:

- `POST /services/data/:version/sobjects/ContentVersion`
  - accepts multipart uploads
  - writes bytes to `data/uploads/{uuid}.{ext}`
  - returns a Salesforce-shaped response with `Id`, `Name`, `Size`, `Mime`, `DownloadUrl`
- `GET /services/data/:version/sobjects/ContentVersion/:fileId/VersionData`
  - validates `uuid.ext`
  - reads directly from `data/uploads`
  - does not query `_System_File`
  - does not query `contentversion`

Conclusion:

- the current wire shape looks Salesforce-compatible
- the runtime semantics are not stable enough for the target design

#### 4.1.2 Frontend upload client

Client:

- `frontend/src/infrastructure/api/files.ts`

Current behavior:

- `fetch + FormData`
- POST to `/services/data/v63.0/sobjects/ContentVersion`
- consumes an opaque upload DTO

Conclusion:

- the frontend cannot tell whether upload created:
  - `_System_File`
  - `contentversion`
  - `contentdocumentlink`
  - or only a local file

#### 4.1.3 Record detail Files related list

Relevant files:

- `frontend/src/features/record/components/RelatedList.tsx`
- `backend/internal/features/metadata/related_list_defaults.go`
- `backend/internal/features/metadata/metadata_layouts.go`
- `frontend/src/pages/ObjectView.tsx`

Current behavior:

- the related list queries `_System_File` with `ParentId = currentRecordId`
- upload action:
  1. uploads binary through `filesAPI.upload()`
  2. creates `_System_File`
  3. stores:
     - `ParentId = businessRecordId`
     - `StoragePath = uploaded.Id`

Current product assumption:

- `_System_File` is the record-attachment master table
- `_System_File.ParentId` is a business record ID

This assumption is the correct target for new files, but the current write semantics are incomplete.

#### 4.1.4 Comment attachments

Relevant files:

- `frontend/src/features/feed/hooks/useComments.ts`
- `frontend/src/features/feed/components/CommentList.tsx`

Current behavior:

- upload binary
- create `_System_Comment`
- create `_System_File` with `ParentId = commentId`
- render downloads with `href="/{StoragePath}"`

Conclusion:

- comment attachments already lean toward the simple `_System_File` product model
- download implementation still needs a proper storage-backed path

#### 4.1.5 `Upload Archived OF`

Relevant files:

- `frontend/src/features/record/components/FileUploadActionModal.tsx`
- `backend/internal/controlplane/packs/standard_actions.json`
- `frontend/src/registries/ActionRegistry.ts`

Current behavior:

- upload file
- resolve `{downloadUrl}` and `{fileName}`
- update `Order` fields:
  - `StandardUploadUrl__c`
  - `Archived_Order_Form__c`
  - `Archived__c`
- does not create `_System_File`
- does not create canonical runtime metadata for the uploaded file

Conclusion:

- this is a business action that must be moved onto the unified file service

### 4.2 Database Reality

Database checked:

- host: `127.0.0.1`
- port: `4000`
- db: `nexuscrm`

#### 4.2.1 File-related table counts

| Table | Rows |
|---|---:|
| `contentversion` | 11634 |
| `contentdocument` | 11512 |
| `contentdocumentlink` | 22969 |
| `_System_File` | 11633 |
| `contentdistribution` | 3492 |
| `contentdistributionview` | 784 |
| `contentnote` | 410 |
| `feedattachment` | 61 |
| `document` | 28 |
| `attachment` | 0 |
| `quotedocument` | 0 |

#### 4.2.2 `_System_File` schema today

Definition source:

- `backend/internal/bootstrap/system_tables.json`

Current fields:

- `Id`
- `ParentId`
- `Name`
- `MimeType`
- `SizeBytes`
- `StoragePath`
- `CreatedById`
- `CreatedDate`
- `LastModifiedDate`

Index:

- only `ParentId`

Conclusion:

- `_System_File` is structurally close to the desired simple attachment model
- but it needs more fields to become a reliable primary runtime table

#### 4.2.3 Migrated data does not use `_System_File` as the primary business model

Verified:

- `_System_File.Id -> contentversion.Id`: `11633 / 11633`
- `_System_File.ParentId -> contentdocument.Id`: `11633 / 11633`
- `_System_File.Name = contentversion.Title`: `11633 / 11633`
- `_System_File.MimeType = contentversion.FileType`: `11633 / 11633`
- `_System_File.SizeBytes = contentversion.ContentSize`: `11633 / 11633`
- `_System_File.StoragePath = contentversion.S3Url`: `11633 / 11633`

Conclusion:

- in migrated data, `_System_File` behaves like a projection of `contentversion`
- it is not yet the real attachment master table for those old files

#### 4.2.4 `ContentDocumentLink` is the real migrated link layer

Verified `LinkedEntityId` prefix distribution:

| Prefix | Object | Rows |
|---|---|---:|
| `005` | User | 11524 |
| `800` | Contract | 484 |
| `801` | Order | 471 |
| `0Q0` | Quote | 168 |
| `006` | Opportunity | 223 |
| `00Q` | Lead | 271 |
| `001` | Account | 151 |
| `003` | Contact | 106 |
| `500` | Case | 0 |

Additional verified facts:

- `11115` documents have multiple `contentdocumentlink` rows
- max links for one document: `11`
- `11113` documents have both:
  - user links
  - business-record links

Conclusion:

- migrated business attachments are linked through `contentdocumentlink.LinkedEntityId`
- if we want `_System_File` to become the runtime master table, migrated latest files must be projected onto parent-object-based `_System_File` rows

#### 4.2.5 Version history exists, but it is not a product requirement

Verified:

- `81` documents have multiple versions
- max versions for one document: `11`
- `_System_File` still contains `121` non-latest version rows

Conclusion:

- the migrated dataset truly contains version history
- but the product requirement only needs latest-file compatibility

#### 4.2.6 Object storage fields are already present and populated

Verified in `contentversion`:

- `S3Key` populated: `11634 / 11634`
- `S3Url` populated: `11634 / 11634`
- `VersionDataUrl` populated: `11634 / 11634`

Observed key shape:

- `content-version/{contentDocumentId}/{contentVersionId}/{filename}`

Conclusion:

- migrated files already have enough metadata to support latest-file backfill into `_System_File`

### 4.3 Core Tension

The design tension is no longer "which full file model is more correct."

The real question is:

- how do we ship a simple `_System_File`-based attachment system now
- while still supporting migrated files that live in `Content*`

The answer in this PRD is:

- `_System_File` becomes the runtime model
- `Content*` becomes compatibility source data for old files only

---

## 5. Target Runtime Model

### 5.1 `_System_File` becomes the runtime attachment table

After this change:

- all new file uploads write `_System_File`
- all new file downloads read `_System_File`
- all new file deletes act on `_System_File`
- all standard file lists query `_System_File`

One runtime row means:

- one attachment record for one file
- attached to one parent object or comment

A parent may have:

- multiple active `record` attachments
- only one active attachment for a populated `SlotKey`

This is intentionally simpler than the Salesforce Files model.

### 5.2 Runtime semantics

For runtime `_System_File` rows:

- `Id` = runtime attachment ID
- `ParentId` = actual parent object ID or comment ID
- `ParentObjectApiName` = actual parent object type
- `Status = Active` means the file is visible and downloadable
- `Status = Deleted` means the file no longer participates in runtime lists
- `SourceType = native` means the file was created by the new runtime path
- `SourceType = content_compat` means the row came from migrated `Content*` compatibility data

Comment-attachment access rule:

- if `ParentObjectApiName = _System_Comment`, authorization must first load the comment row
- comment attachment visibility inherits from the comment's owning record or feed thread
- users who can read the owning record can read and download the comment attachment
- comment attachment access must not be decided from `_System_Comment.Id` alone

### 5.3 Acceptable simplifications

This design intentionally accepts these tradeoffs:

- no user-visible version history
- no true document identity shared across multiple records at runtime
- migrated files linked to multiple records may be flattened into multiple `_System_File` rows
- latest-file-only compatibility is sufficient

Those tradeoffs are acceptable because:

- product scope is latest-file-only
- users only need attachment behavior
- shipping speed matters more than preserving the full Salesforce document model in runtime APIs

---

## 6. `_System_File` Schema Changes

### 6.1 Recommended field contract

The current table is too small for a real runtime attachment model, so this PRD should define the target contract explicitly instead of only naming a few extra columns.

Referential integrity for this model is application-enforced rather than foreign-key-enforced.
The runtime table should stay lightweight and self-owned, with parent existence and comment ownership validated in service code.

Recommended target schema:

| Field | Type / default | Required | Purpose |
|---|---|---|---|
| `Id` | `VARCHAR(255)` | yes | system-generated attachment ID; do not reuse `ContentVersion.Id` for new runtime rows |
| `ParentId` | `VARCHAR(255)` | yes | actual record ID or comment ID |
| `ParentObjectApiName` | `VARCHAR(128)` | yes | parent object type such as `Order`, `Contract`, `_System_Comment` |
| `Name` | `VARCHAR(255)` | yes | display title shown in the UI |
| `OriginalFileName` | `VARCHAR(255)` | yes | exact uploaded filename including extension |
| `Extension` | `VARCHAR(32)` | no | lowercase extension without the dot |
| `MimeType` | `VARCHAR(255)` | no | uploaded or derived content type |
| `SizeBytes` | `BIGINT NOT NULL DEFAULT 0` | yes | object size in bytes |
| `StorageProvider` | `VARCHAR(32) NOT NULL DEFAULT 's3'` | yes | storage backend selector |
| `StorageBucket` | `VARCHAR(255)` | yes | physical bucket / container |
| `StorageKey` | `VARCHAR(1024)` | yes | canonical object key used by download and delete logic |
| `StoragePath` | `TEXT` | yes | compatibility / display path only; not the canonical lookup; native rows mirror `StorageKey` |
| `Status` | `VARCHAR(32) NOT NULL DEFAULT 'Active'` | yes | runtime lifecycle: `Active`, `Deleted` |
| `SourceType` | `VARCHAR(32) NOT NULL DEFAULT 'native'` | yes | row origin: `native`, `content_compat` |
| `SourceContentDocumentId` | `VARCHAR(255)` | conditional | migrated source document ID; set only for compatibility rows |
| `SourceContentVersionId` | `VARCHAR(255)` | conditional | migrated latest version ID used to materialize the row |
| `AttachmentKind` | `VARCHAR(64) NOT NULL DEFAULT 'record'` | yes | file category: `record`, `comment`, `archived_order`, `archived_contract` |
| `SlotKey` | `VARCHAR(128)` | no | single-slot uniqueness token for replace-in-place business uploads |
| `Checksum` | `VARCHAR(128)` | no | integrity / duplicate-detection helper |
| `Description` | `TEXT` | no | optional display metadata |
| `DeletedById` | `VARCHAR(255)` | no | delete actor |
| `DeletedDate` | `DATETIME` | no | delete timestamp |
| `CreatedById` | `VARCHAR(255)` | yes | creator shown in file info |
| `CreatedDate` | `DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP` | yes | upload or preserved migrated file timestamp |
| `LastModifiedDate` | `DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP` | yes | runtime metadata update timestamp |

Notes:

- `Name` and `OriginalFileName` intentionally serve different purposes:
  - `Name` is the display title
  - `OriginalFileName` preserves the exact uploaded filename
- `StorageKey` should stay bounded and indexable; do not leave it as an unbounded free-form blob field.
- `PreviewUrl` should not be persisted in `_System_File`.
  Preview links are short-lived server outputs, not durable metadata.

### 6.2 Required invariants

The schema must carry explicit invariants so implementation does not guess them later.

- `ParentId` must always be a real record ID or comment ID in runtime rows.
- `Status = Active` rows must have non-empty `StorageProvider`, `StorageBucket`, and `StorageKey`.
- `Status = Deleted` rows remain in `_System_File` for audit and rollback, but download must deny them.
- allowed lifecycle transitions are:
  - `Active -> Deleted`
  - no transition back to `Active`; recovery creates a new active row if needed
- `DeletedById` and `DeletedDate` must either both be null or both be populated.
- `_System_File.Status` represents only visible attachment lifecycle.
  Intermediate upload states belong in `_System_FileUploadOperation`, not in `_System_File`.
- `SourceType = native` requires `SourceContentDocumentId` and `SourceContentVersionId` to be null.
- `SourceType = content_compat` requires both `SourceContentDocumentId` and `SourceContentVersionId` to be non-null.
- `ParentId`, `ParentObjectApiName`, `AttachmentKind`, and `SourceType` are immutable after insert.
- `StorageKey` is the canonical lookup for runtime download and delete.
  `StoragePath` is retained only for compatibility and observability.
  Native runtime rows should mirror `StorageKey` into `StoragePath` so older display code and validators do not see an empty path.
- `SlotKey` is null for normal multi-file attachments.
- `SlotKey` is populated for single-slot business uploads:
  - `archived_order -> SlotKey = archived_order`
  - `archived_contract -> SlotKey = archived_contract`
- rows with `AttachmentKind = archived_order` must be rejected unless `SlotKey = archived_order`
- rows with `AttachmentKind = archived_contract` must be rejected unless `SlotKey = archived_contract`

### 6.3 Required indexes and uniqueness rules

Add only the minimum indexes and uniqueness rules needed for runtime behavior:

- `_System_File` primary key on `Id`
- platform-default lookup index on `_System_File.ParentId`
- `idx_system_file_source_doc (SourceContentDocumentId, ParentId)`
- `uk_system_file_slot (ParentObjectApiName, ParentId, SlotKey)`
- `_System_FileUploadOperation` primary key on `Id`
- `uk_system_fileuploadoperation_idempotency (IdempotencyKey)`
- `idx_system_fileuploadoperation_expires (ExpiresAt)`

Why this matters:

- the primary key on `Id` serves direct download/delete fetches and idempotent replay returns.
- the default `ParentId` lookup index supports the common related-file list path without carrying extra low-selectivity columns.
- `idx_system_file_source_doc` supports compatibility projection and latest-file reconciliation without adding a wide compat unique index.
- `uk_system_file_slot` gives us DB-enforced single-slot behavior without forcing generic attachments into a fake uniqueness model.
  Generic attachments keep `SlotKey = null`, while archived uploads set a real slot token.
- `uk_system_fileuploadoperation_idempotency` makes request replay deterministic and prevents duplicate operation rows.
- `idx_system_fileuploadoperation_expires` supports cleanup and stale-operation checks without indexing low-selectivity state first.
- compatibility backfill idempotency should not rely on a wide unique multi-column index.
  Instead, the projector should derive a deterministic compat row identity from `(ParentObjectApiName, ParentId, SourceContentDocumentId)` and upsert by that row ID.
  If that row already exists, update it in place; if not, insert the deterministically derived row.
- compatibility repair jobs should resolve target rows through the deterministic compat row identity or a source manifest, not through a dedicated wide compat unique index.

### 6.4 Compatibility mapping contract

For migrated latest-file projection, the mapping into `_System_File` should be explicit:

| `_System_File` field | Compatibility source |
|---|---|
| `Id` | deterministic compat row ID derived from `(ParentObjectApiName, ParentId, SourceContentDocumentId)` unless an existing compat row is already present |
| `ParentId` | `contentdocumentlink.LinkedEntityId` |
| `ParentObjectApiName` | resolved from `LinkedEntityId` prefix or metadata |
| `Name` | latest `contentversion.Title` |
| `OriginalFileName` | latest `contentversion.PathOnClient` basename if available, otherwise `Title` |
| `Extension` | derived from original filename or title |
| `MimeType` | latest `contentversion.FileType` |
| `SizeBytes` | latest `contentversion.ContentSize` |
| `StorageProvider` | runtime provider choice, normally `s3` |
| `StorageBucket` | derived from migration storage config |
| `StorageKey` | latest `contentversion.S3Key` |
| `StoragePath` | for compat rows, latest `contentversion.S3Url`; for native rows, mirror `StorageKey` |
| `Status` | `Active` |
| `SourceType` | `content_compat` |
| `SourceContentDocumentId` | `contentdocument.Id` |
| `SourceContentVersionId` | latest `contentversion.Id` |
| `AttachmentKind` | resolved from parent context |
| `SlotKey` | normally null; set only if the migrated file is projected into a single-slot business attachment |
| `CreatedById` | preserve latest `contentversion.CreatedById` if available, otherwise migration service user |
| `CreatedDate` | preserve latest `contentversion.CreatedDate` where possible |

Two important rules:

- compatibility projection creates new parent-based runtime rows; it must not rewrite old `_System_File` rows in place from `ContentDocument.Id` parents to business-record parents
- the backfill key is the projected runtime row, not the original migrated `_System_File.Id`
- when the latest migrated version for the same `SourceContentDocumentId` changes, projection updates the existing compat row in place with the new `SourceContentVersionId`, storage metadata, and display metadata
- compat projection must remain idempotent even if the same source parent is processed multiple times; the deterministic row ID is the source of truth for deduplication

### 6.5 Parent semantics must be unified

This is the most important schema rule:

- new runtime rows must never use `ParentId = ContentDocument.Id`
- runtime `ParentId` must always be:
  - the record ID
  - or the comment ID

If this rule is violated, the design collapses back into the current mixed model.

---

## 7. File Flows

### 7.1 New upload flow

1. frontend calls a unified upload endpoint
2. backend validates:
   - parent object existence
   - parent object access
   - file policy:
     - size
     - type
     - extension
3. backend creates a storage key
4. backend uploads bytes to object storage
5. backend inserts `_System_File`
6. backend returns a normalized file DTO

For normal record attachments:

- create one new `_System_File` row per uploaded file
- no `Content*` write is required

### 7.2 New download flow

1. client requests download by `_System_File.Id`
2. backend loads `_System_File`
3. backend checks runtime access using:
   - `ParentObjectApiName`
   - `ParentId`
4. if `ParentObjectApiName = _System_Comment`, backend resolves the comment's owning business record and authorizes against that record
5. backend resolves object-storage location from:
   - `StorageProvider`
   - `StorageBucket`
   - `StorageKey`
6. backend proxies or redirects to a short-lived presigned URL

### 7.3 Delete flow

Recommended product behavior:

1. client requests delete by `_System_File.Id`
2. backend validates parent-object access and delete policy
3. if the row is already `Deleted`, return success without redoing metadata changes
4. backend marks row:
   - `Status = Deleted`
   - `DeletedById`
   - `DeletedDate`
   - clear `SlotKey` if the row occupied a single-slot business attachment
5. backend enqueues best-effort storage cleanup work when the blob is exclusively owned by that row

Delete idempotency rules:

- repeated delete requests for the same row return success
- missing blob in object storage does not turn delete into a user-visible failure
- cleanup retries are asynchronous and operationally monitored
- compat rows that share a migrated blob must not hard-delete that blob on single-row delete

Soft delete is preferred because it is easier to recover operational mistakes during rollout.

### 7.4 Replace-file semantics

The product does not expose versioning.

Therefore "replace file" means one of:

- delete old runtime attachment and upload a new one
- or, for single-slot business actions, deactivate the previous active row and create a new active row

Recommended rule:

- generic record attachments may allow multiple active rows
- business-action attachments should be single-slot per `(ParentId, AttachmentKind)`

Required single-slot replace sequence:

1. create an upload operation row keyed by idempotency key
2. upload the new blob and prepare the new `_System_File` row while the old active row remains visible
3. start one DB transaction for the slot handoff
4. lock the slot by `(ParentObjectApiName, ParentId, SlotKey)` using a row-level lock or equivalent serialized write primitive
5. mark the old row `Deleted`
6. clear `SlotKey` on the old row
7. insert the new `Active` row with the target `SlotKey`
8. commit the transaction

Concurrency contract:

- the old active row must remain visible until the new row is durably committed
- unique-key conflicts on `uk_system_file_slot` must be retried by reloading the latest slot state
- slot handoff and old-row deactivation must happen in one DB transaction
- the old row releases `SlotKey` inside that transaction before the new row claims it
- readers outside the transaction continue to see the previously committed old row until the transaction commits, so there is no user-visible gap

Example:

- `AttachmentKind = archived_order` should have only one active file per `Order`
- `AttachmentKind = archived_contract` should have only one active file per `Contract`

---

## 8. Migrated File Compatibility

### 8.1 Compatibility principle

`ContentDocument` / `ContentVersion` / `ContentDocumentLink` remain in the database as migrated source data.

They are used only for:

- latest-file discovery
- compatibility backfill
- migration audit / repair

They are not part of the new runtime write path.

### 8.2 Latest-file-only projection

For each supported linked object:

1. scan `contentdocumentlink` in bounded batches keyed by `LinkedEntityId` or by a migration manifest of target parent IDs
2. resolve the parent object from `LinkedEntityId`
3. resolve the latest version from:
   - `contentdocument.LatestPublishedVersionId`
   - or `contentversion.IsLatest = 1` as validation
4. create or update one `_System_File` row representing that latest file for that parent

Source query contract:

- the migration job must not rely on unbounded full-table scans during steady rollout
- source reads must either:
  - use source-side indexes that support the chosen `contentdocumentlink` lookup pattern
  - or run from a bounded parent/document manifest produced before cutover
- each migration batch must have a durable resume key so the job can restart without rescanning completed work

Projection scope rules:

- supported business-record links are projected into runtime rows
- user/self links are not projected as standalone runtime attachments unless a user-owned file surface is explicitly added later
- if a document has both business-record links and user links, the business-record links win for runtime attachment projection

Projected row rules:

- `Id` is a new `_System_File` ID, not the old `ContentVersion.Id`
- `SourceType = content_compat`
- `SourceContentDocumentId = contentdocument.Id`
- `SourceContentVersionId = latest contentversion.Id`
- `ParentId = LinkedEntityId`
- `ParentObjectApiName = resolved object type`
- `CreatedDate` and `CreatedById` preserve latest source metadata where possible
- `StorageKey` / `StoragePath` derived from the latest `contentversion`

### 8.3 Multi-link migrated documents

If one migrated `contentdocument` is linked to multiple records:

- create one `_System_File` row per linked parent record

This intentionally flattens the old many-link document model into the new runtime attachment model.

That is acceptable for this phase.

Blob ownership rule for compat rows:

- multiple compat rows may share the same migrated blob and `StorageKey`
- deleting one compat row must not hard-delete a blob that is still referenced by another compat row
- phase-1 compatibility cleanup should therefore treat migrated compat blobs as shared and non-owning
- physical deletion of migrated compat blobs is allowed only in a dedicated reconciliation job that verifies no active compat rows still reference that object

### 8.4 Compatibility rollout

Recommended rollout:

1. backfill latest migrated files into `_System_File`
2. run audit jobs to detect misses
3. repair missing rows by rerunning projection for the affected parents
4. cut runtime reads directly to `_System_File` only

Migration order:

1. add new `_System_File` columns with safe defaults and nullable compatibility fields
2. ship backend writes for native runtime rows
3. backfill parent-based compatibility rows with deterministic compat row IDs
4. cut runtime read paths over to the parent-based rows
5. tighten nullability / invariants once rollout data is clean

Steady-state target:

- runtime file lists read `_System_File` only
- runtime downloads read `_System_File` only
- `Content*` is not queried on the hot path

Cutover exit criteria:

- backfill miss rate stays below an agreed rollout threshold for a full observation window
- no critical parent scopes are missing compat rows in audit samples
- no blocking `_System_FileUploadOperation` or cleanup backlog alerts remain open
- after those conditions are met, any temporary fallback or manual repair mode must be removed

### 8.5 Supported migrated scope

Phase 1 compatibility scope:

- Quote
- Contract
- Order
- Account
- Contact
- Opportunity
- Lead
- comment compatibility if needed later

Case note:

- the current migrated snapshot shows `0` `contentdocumentlink` rows for Case
- Case support must still work for net-new `_System_File` uploads

---

## 9. Business-Action Uploads

### 9.1 Principle

Business-action uploads must use the same `_System_File` runtime model as generic uploads.

They are not allowed to remain "field update only" workflows.

### 9.2 `Upload Archived OF`

Target behavior:

1. upload bytes to object storage
2. create `_System_File` with:
   - `ParentObjectApiName = Order`
   - `ParentId = Order.Id`
   - `AttachmentKind = archived_order`
   - `SlotKey = archived_order`
3. update any compatibility fields still needed by downstream logic
4. if approved business preconditions are met, transition the `Order` to `Activated`

Compatibility fields may include:

- `StandardUploadUrl__c`
- `Archived_Order_Form__c`
- `Archived__c`

But those fields are derived outputs, not the canonical file source.

### 9.3 Archived Contract upload

Target behavior:

1. upload bytes to object storage
2. create `_System_File` with:
   - `ParentObjectApiName = Contract`
   - `ParentId = Contract.Id`
   - `AttachmentKind = archived_contract`
   - `SlotKey = archived_contract`
3. update any compatibility fields still needed by downstream logic
4. if approved business preconditions are met, transition the `Contract` to `Effective`

### 9.4 Recommended implementation for status transitions

Recommended choice:

- implement archived-upload status transitions in backend action handlers

Reason:

- lighter than depending on Flow parity
- easier to keep deterministic
- easier to test end-to-end
- aligns with the "ship simple attachment behavior first" goal

This PRD therefore recommends:

- backend action handler owns:
  - file upload
  - `_System_File` write
  - derived field updates
  - status transition

### 9.5 Transactional behavior

Upload and status updates are not literally atomic across object storage and DB, but they must be application-consistent.

Required behavior:

- if storage upload fails, no `_System_File` row is written
- if `_System_File` write fails after storage succeeds, clean up or queue cleanup for the object
- if business record update fails after `_System_File` write succeeds:
  - mark the operation failed
  - soft-delete the committed `_System_File` row
  - clear `SlotKey` on that row if it was a single-slot attachment
  - queue storage cleanup if the blob should not remain
  - do not leave a silently successful uploaded file attached to a record whose business action failed
- for single-slot replacement, the old active row must remain available until the new row is committed and the business transition succeeds

---

## 10. Storage Design

### 10.1 Canonical storage principle

Canonical binary storage must be S3-compatible object storage.

The database stores metadata.
Object storage stores bytes.

Local disk is not a valid steady-state runtime target.

### 10.2 Provider abstraction

The backend should expose a storage provider abstraction with at least:

- `PutObject`
- `GetObject`
- `DeleteObject`
- `HeadObject`
- `GeneratePresignedGetURL`

Recommended concrete providers:

- staging / production:
  - managed S3-compatible object storage
- local development and CI:
  - MinIO

### 10.3 Key convention

Recommended native object key shape:

- `system-file/{parentObjectApiName}/{parentId}/{systemFileId}/{originalFileName}`

This makes runtime ownership obvious and avoids mixing runtime semantics with old `content-version/...` keys.

Compatibility rule:

- `SourceType = native` rows use the native `system-file/...` key convention
- `SourceType = content_compat` rows may retain migrated legacy `S3Key` values
- runtime download and delete logic must use the stored `StorageProvider`, `StorageBucket`, and `StorageKey` literally rather than inferring key shape from `SourceType`

### 10.4 Metadata stored in `_System_File`

Recommended canonical fields for download lookup:

- `StorageProvider`
- `StorageBucket`
- `StorageKey`
- `StoragePath`

Rule:

- `StorageKey` is the canonical lookup
- `StoragePath` is retained as a convenience display / compatibility field
- download and delete code must not depend on raw `StoragePath` parsing
- migrated compatibility rows do not need storage-key normalization before rollout

---

## 11. Unified File-Service Contract

### 11.1 Principle

The frontend should no longer do:

1. upload bytes
2. interpret an opaque upload response
3. decide how to create metadata rows

The backend should own:

- storage
- metadata writes
- attachment semantics
- permission-aware response shaping

### 11.2 API direction

The app may keep Salesforce-shaped external routes if useful:

- `POST /services/data/:version/sobjects/ContentVersion`
- `GET /services/data/:version/sobjects/ContentVersion/:fileId/VersionData`

But internal semantics change completely:

- new uploads create `_System_File`
- new downloads resolve `_System_File`
- runtime no longer depends on fake `uuid.ext` identity
- runtime no longer depends on local disk
- runtime no longer depends on frontend-created metadata

### 11.3 Response DTO

The frontend should receive a normalized attachment DTO with:

- attachment ID
- parent ID
- parent object API name
- attachment kind
- display name
- original file name
- mime type
- size
- uploaded date
- uploaded by
- download URL
- delete capability / policy where applicable

### 11.4 Idempotency

Upload is not literally atomic across relational DB and object storage.

Required contract:

- every upload request carries an idempotency key
- retries with the same key must not create duplicate `_System_File` rows
- object storage writes and `_System_File` writes run under an explicit state model
- the idempotency key is bound to a `RequestHash`; the same key with a different request hash must be rejected

Recommended upload-operation table:

- `_System_FileUploadOperation`

Minimum fields:

- `Id`
- `IdempotencyKey`
- `RequestHash`
- `State`
- `ObjectKey`
- `SystemFileId`
- `ParentId`
- `ParentObjectApiName`
- `ErrorCode`
- `ErrorMessage`
- `ExpiresAt`
- `CreatedDate`
- `LastModifiedDate`

Recommended states:

1. `PREPARED`
2. `BLOB_STORED`
3. `METADATA_COMMITTED`
4. `COMPLETED`
5. `FAILED`

Replay semantics:

- if the same idempotency key arrives with the same request hash and state = `PREPARED`, resume the pending operation
- if state = `BLOB_STORED`, reuse the stored object reference and continue metadata commit
- if state = `METADATA_COMMITTED`, return the existing `_System_File` result instead of creating another row
- if state = `COMPLETED`, return the prior success response
- if state = `FAILED`, either return the recorded failure or require a new idempotency key based on error class
- if the same idempotency key arrives with a different request hash, return a conflict error and do not mutate state

### 11.5 Download strategy

Preferred flow:

1. client requests stable app URL
2. backend authenticates user
3. backend authorizes against parent-object access
4. backend resolves object storage from `_System_File`
5. backend:
   - redirects to a short-lived presigned URL by default
   - proxies only when necessary

Pinned contract:

- callers must not depend on raw storage URLs
- redirect-vs-proxy is a server-side implementation detail

---

## 12. Changes Required

### 12.1 Backend

- replace local-disk upload/download with object-storage-backed service
- expand `_System_File` schema to become the runtime master table
- keep the runtime table lean: no foreign keys, no wide compat unique index, and only the minimum slot/list indexes
- move post-upload metadata writes into backend service layer
- add `_System_File` read service for:
  - record files
  - comment files
  - business-action files
- add `_System_File` delete service
- add compatibility backfill from latest `Content*` rows into `_System_File`
- add durable upload idempotency, cleanup, and reconciliation
- implement archived upload actions on top of the unified service

### 12.2 Frontend

- stop creating `_System_File` directly after upload
- stop treating `StoragePath` as a browser path
- move related-list and comment attachment flows to the unified file service
- consume normalized attachment DTOs instead of raw upload DTOs
- treat `_System_File` as the only attachment model in runtime UI

### 12.3 Metadata / related-list layer

- keep file-related UI and related-list resolution on `_System_File`
- remove any assumption that migrated file lists must query `contentdocumentlink` directly at runtime
- only backfill / migration tooling may depend directly on `Content*`

---

## 13. Implementation Plan

### Phase 1: `_System_File` runtime foundation

Goal:

- make `_System_File` the real runtime attachment table for new files

Changes:

- add required `_System_File` fields and the reduced index set
- add slot-based uniqueness enforcement for single-slot business attachments
- add object-storage provider abstraction
- move new upload to object storage
- move new download to object storage
- add backend-owned `_System_File` write path
- add backend-owned delete path
- stop new runtime dependence on local disk

Checkpoint:

- new uploads, downloads, and deletes use `_System_File` only

### Phase 2: Business-action convergence

Goal:

- make archived upload actions use the same runtime model

Changes:

- connect `Upload Archived OF` to `_System_File`
- add archived Contract upload on `_System_File`
- move derived field updates into backend handlers
- implement deterministic status transitions:
  - `Order -> Activated`
  - `Contract -> Effective`

Checkpoint:

- archived upload actions no longer bypass the attachment model

### Phase 3: Migrated latest-file compatibility

Goal:

- support old files without making `Content*` the runtime model

Changes:

- backfill latest migrated files into `_System_File`
- create new parent-based compatibility rows instead of mutating old legacy projections in place
- flatten multi-link old documents into parent-based `_System_File` rows
- run bounded audit and repair jobs for rollout misses
- measure and fix backfill gaps

Checkpoint:

- migrated latest files show up and download through `_System_File`

### Phase 4: Runtime cleanup

Goal:

- remove mixed-model runtime behavior

Changes:

- remove local-disk reads from runtime
- remove any new runtime dependence on direct `Content*` queries
- document `Content*` as compatibility-only data source
- keep migration tooling for repair and audit

Checkpoint:

- steady-state runtime file operations depend on `_System_File` only

### Phase 5: Validation

Goal:

- prove the simplified runtime model is correct enough for rollout

Validation categories:

- unit tests:
  - `_System_File` upload semantics
  - delete semantics
  - attachment kind slot behavior
  - status-transition handler behavior
- integration tests:
  - object-store upload/download/delete
  - idempotency-key replay
  - cleanup on partial failure
  - latest-file backfill from `Content*`
- contract tests:
  - stable download URL semantics
  - current-file list behavior
  - object-level permission inheritance
  - archived Order / Contract post-upload status behavior
- operational checks:
  - backfill miss-rate dashboard and alert
  - stale `_System_FileUploadOperation` alert
  - delete-cleanup backlog alert
  - explicit cutover checklist for removing fallback / repair mode

Checkpoint:

- current-file behavior is automated before rollout beyond local testing

---

## 14. Decision Matrix

| Dimension | `_System_File` runtime model | `Content*` runtime model |
|---|---|---|
| Matches product scope | Strong | Medium |
| Implementation speed | Strong | Medium |
| Runtime simplicity | Strong | Weak |
| New upload path simplicity | Strong | Weak |
| Old file compatibility | Medium | Strong |
| Latest-file-only support | Strong | Strong |
| Multi-version support | Weak | Strong |
| Long-term attachment simplicity | Strong | Weak |
| Best fit for this phase | Yes | No |

---

## 15. Risks

### Risk 1: mixed `ParentId` semantics

If `_System_File.ParentId` continues to mean `ContentDocument.Id` for some rows and record ID for others, the runtime model becomes unusable.

### Risk 2: incomplete migrated latest-file backfill

If the latest-file projection from `Content*` into `_System_File` is incomplete, old files will appear missing even though they still exist in source tables.

### Risk 3: flattening multi-link old documents

One migrated `contentdocument` may become multiple `_System_File` rows.
That is acceptable, but it must be documented and handled consistently.

### Risk 4: frontend-owned metadata writes

If the frontend continues to compose `_System_File` rows directly, runtime semantics will fragment again.

### Risk 5: status side effects split across layers

If archived upload field updates and status transitions are split across frontend, backend, and ad hoc automation without one owner, the business flow will drift.

### Risk 6: local-disk fallback lingering too long

If local-disk reads remain indefinitely, the system keeps two storage models alive and operational complexity stays high.

---

## 16. Open Questions

1. Should migrated latest-file backfill be:
   - one-time migration plus bounded audit/repair window
   - or a scheduled reconciler until migrated compat coverage is considered stable?
2. Should browser preview for PDF and other browser-safe formats be Phase 1 or Phase 2?
3. Do we need antivirus / DLP checks before broader file rollout?
4. Do we want soft delete only in Phase 1, or full hard-delete cleanup in the same request path?