---
spec: project-file-upload
status: done
owner: Sachin.Badoni
related-routes:
  - GET /api/v2/projects/:projectId/files
  - GET /api/v2/projects/:projectId/files/:fileReferenceId
  - POST /api/v2/projects/:projectId/files
files:
  - src/api/v2/projects/projectfiles/projectfiles.routes.ts
  - src/api/v2/projects/projectfiles/projectfiles.controller.ts
  - src/api/v2/projects/projectfiles/projectfiles.validator.ts
  - src/services/projectfiles.service.ts
  - database/xyz/Functions/fn_GetProjectFileList.sql
  - database/xyz/Functions/fn_GetProjectFile.sql
  - database/xyz/Procedures/usp_InsertProjectFile.sql
  - database/xyz/Procedures/usp_UpdateProjectFileSizeBytes.sql
  - src/api/v2/api.v2.routes.ts
---

# Project File Upload (PAPI-3286)

## Goal

Allow users to upload any file type (image, video, document, etc.) to a project, retrieve file details, and download the file via a signed blob URL.

## Context

- New endpoints added so users can upload arbitrary files for a given project.
- On upload, a `fileReferenceId` is returned and can be used to fetch file details and a tokenised download URL via the GET endpoint.
- Upload supports chunked transfer so large files can be streamed without timeouts.

## Behavior changes

- New `GET /api/v2/projects/:projectId/files` — returns a list of all files for the project (metadata only, no download URLs), ordered by `InsertedOn` descending.
- New `GET /api/v2/projects/:projectId/files/:fileReferenceId` — returns file metadata and a signed blob download URL.
- New `POST /api/v2/projects/:projectId/files` — uploads a file chunk to Azure Blob Storage and manages the DB record lifecycle:
  - On `chunkIndex = 0`: blob block staged **and** `usp_InsertProjectFile` called to create the DB record.
  - On intermediate chunks: blob block staged only (no DB write).
  - On the final chunk (`chunkIndex + 1 === totalChunks`): blob committed and `usp_UpdateProjectFileStatus` called to set `IngestStatus` and `FileSizeBytes`. Returns `201`.
  - All other chunks return `200` with the `fileReferenceId` for subsequent requests.

## Database

| Object | Type | Location                                         |
|---|---|--------------------------------------------------|
| `fn_GetProjectFiles` | Function | `xyz/Functions/fn_GetProjectFileList.sql`        |
| `fn_GetProjectFile` | Function | `xyz/Functions/fn_GetProjectFile.sql`            |
| `usp_InsertProjectFile` | Procedure | `xyz/Procedures/usp_InsertProjectFile.sql`       |
| `usp_UpdateProjectFileStatus` | Procedure | `xyz/Procedures/usp_UpdateProjectFileStatus.sql` |

**Table**: `xyz."UserFile"` (not `ProjectFile`)

**Key columns**:

| Column | Type | Notes |
|---|---|---|
| `FileReferenceId` | UUID | PK |
| `ProjectShardId` | INT | derived via `fn_GetProjectShardId` |
| `FileName` | TEXT | |
| `FileExtension` | TEXT | lowercase, no dot |
| `FileType` | TEXT | mandatory; caller-defined label, e.g. `'Dashboard'`, `'Report'` |
| `CloudStoragePath` | TEXT | fully-qualified blob path |
| `XyzDisplayName` | TEXT | |
| `UploadedBy` | TEXT | |
| `FileSizeBytes` | BIGINT | NULL on insert; set on final-chunk update |
| `IngestStatus` | TEXT | `'Do_Not_Process'` on insert; caller-supplied value on update |
| `InsertedOn` | TIMESTAMPTZ | `TIMEZONE('UTC', NOW())` on insert |
| `LastModifiedBy` | TEXT | NULL on insert |
| `LastModifiedOn` | TIMESTAMPTZ | NULL on insert; `TIMEZONE('UTC', NOW())` on update |

**Blob storage path pattern**: `RawData/files/ProjectId={projectId}/{fileReferenceId}`

## API response shape

`GET /api/v2/projects/:projectId/files` — `200 OK` (array):

```json
[
  {
    "fileReferenceId": "uuid",
    "fileName": "dashboard.json",
    "fileExtension": "json",
    "fileType": "Dashboard",
    "xyzDisplayName": "Main Dashboard",
    "fileSizeBytes": 4096,
    "uploadedBy": "user@example.com",
    "insertedOn": "2026-05-05T10:00:00Z",
    "lastModifiedBy": null,
    "lastModifiedOn": null
  }
]
```

`GET /api/v2/projects/:projectId/files/:fileReferenceId` — `200 OK`:

```json
{
  "fileReferenceId": "uuid",
  "fileName": "drawing.pdf",
  "fileExtension": "pdf",
  "fileType": "Report",
  "xyzDisplayName": "Site Drawing",
  "fileSizeBytes": 204800,
  "uploadedBy": "user@example.com",
  "insertedOn": "2026-05-05T10:00:00Z",
  "lastModifiedBy": null,
  "lastModifiedOn": null,
  "downloadUrl": "https://..."
}
```

> `status` in the JSON response is mapped from the `IngestStatus` column (`row.IngestStatus`). The list endpoint omits `downloadUrl`; use the single-file GET to obtain a signed URL.

`POST /api/v2/projects/:projectId/files` — `200 OK` (intermediate chunk):

```json
{ "fileReferenceId": "uuid", "message": "Project file chunk uploaded successfully" }
```

`POST /api/v2/projects/:projectId/files` — `201 Created` (final chunk):

```json
{ "fileReferenceId": "uuid", "message": "Project file uploaded successfully" }
```

## Acceptance criteria

- [ ] `GET .../files` returns an array of file metadata for all files in the project, ordered most-recent first.
- [ ] `GET .../files` returns an empty array (not `404`) when the project has no files.
- [ ] `GET .../files` includes `fileType` in every item.
- [ ] `GET .../files/:fileReferenceId` returns all file metadata fields including `fileType` and a signed `downloadUrl`.
- [ ] `GET .../files/:fileReferenceId` returns `404` when the file does not exist.
- [ ] `POST .../files` with `chunkIndex=0` creates a DB record in `xyz."UserFile"` with `IngestStatus = 'Do_Not_Process'` and returns `fileReferenceId`.
- [ ] `POST .../files` with missing `fileType` returns `400`.
- [ ] `POST .../files` intermediate chunks return `200` without writing to the DB.
- [ ] `POST .../files` final chunk updates `IngestStatus` and `FileSizeBytes` in `xyz."UserFile"` and returns `201`.
- [ ] `POST .../files` with no file body returns `400`.
- [ ] `POST .../files` with `chunkIndex > 0` and no `fileReferenceId` returns `400`.
- [ ] All endpoints enforce `IMAGE_VIEW` / `IMAGE_UPLOAD` authorities respectively.

## Out of scope

- Sorting or filtering by `IngestStatus`.
- Bulk status updates.
- Migrating historical rows.
- Triggering downstream ingest pipelines (handled separately; `Do_Not_Process` explicitly opts out).

## Test plan

- **Unit**: `test/unit/api/v2/projects/projectfiles/projectfiles.controller.spec.ts` and `test/unit/services/projectfiles.service.spec.ts`.
- **E2E**: `test/e2e/api/projectfiles.e2e.spec.ts`.
- Run with: `npm test` (unit) and `npm run test:e2e:file -- test/e2e/api/projectfiles.e2e.spec.ts` (e2e).

## Decisions

- Route mounted as `/:projectId/files` under the v2 project router — consistent with all other project-scoped resources.
- Chunked upload mirrors the video upload pattern exactly (`uploadChunk` staging blocks then committing on last chunk). DB record is created on first chunk only; status updated on last.
- Two new authorities added to `Authorities` enum: `FILE_VIEW = "FileView"` and `FILE_UPLOAD = "FileUpload"`.
- DB table is `xyz."UserFile"` with `IngestStatus` column (not `Status`). Initial `IngestStatus` is `'Do_Not_Process'` so downstream ingest pipelines ignore the record until explicitly opted in.
- Controller maps `row.IngestStatus` → `status` in the JSON response for a stable API surface regardless of DB column naming.
- `fileType` is mandatory on upload (validated in `projectfiles.validator.ts`) and stored as `FileType` in `xyz."UserFile"`. It is a free-text caller label (e.g. `'Dashboard'`, `'Report'`) — no enum enforced at the API layer.
- List endpoint (`GET .../files`) omits `downloadUrl`; callers needing a signed URL must call the single-file GET. This avoids generating N signed URLs per list request.
- List function `fn_GetProjectFiles` returns rows ordered by `InsertedOn DESC` and excludes `CloudStoragePath` since no download URL is generated.
- Stored procedures follow the existing `fn_` (function) / `usp_` (procedure) naming convention and live under `Functions/` and `Procedures/` respectively.
- Directory named `projectfiles` (not `files`) to avoid ambiguity with other file-type directories.