---
spec: project-file-upload
status: done
implemented-by: Sachin.Badoni
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 Hash (PAPI-3286)

## Goal

Add fileHash to projectFiles og any type (image, video, document, etc.) to a project, and return it in with GET call.

## Context

- Update upload project file and calculate file hash and store it in DB. For chunked file user redis cache to save the chunked file hash and when final chunk is received store it in DB.

## Behavior changes

- New `GET /api/v2/projects/:projectId/files` — update to return fileHash.
- New `GET /api/v2/projects/:projectId/files/:fileReferenceId` — update to return fileHash.
- New `POST /api/v2/projects/:projectId/files` — calculate file hash:
  - On `chunkIndex = 0`: calculate hash and store it in Cache don't save it in DB yet.
  - On `chunkIndex +1  == totalChunks`: calculate hash and store it in DB and remove from cache.
  

## Database

| Object                        | Type | Location                                         |
|-------------------------------|---|--------------------------------------------------|
| `fn_GetProjectFileList`       | Function | `xyz/Functions/fn_GetProjectFileList.sql`        |
| `fn_GetProjectFile`           | Function | `xyz/Functions/fn_GetProjectFile.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 |
| `fileHash`         | TEXT | NULL on insert |
| `LastModifiedBy`   | TEXT | NULL on insert |
| `LastModifiedOn`   | TIMESTAMPTZ | NULL on insert; `TIMEZONE('UTC', NOW())` on update |


## 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,
    "fileHash":"hash value of the file" , 
    "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,
  "fileHash":"hash value of the file",
  "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, with fileHash.
- [ ] `GET .../files` returns an empty array (not `404`) when the project has no files.
- [ ] `GET .../files` includes `fileHash` 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` 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.

## 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.
- DB table is `xyz."FileReference"`.
- 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.
- Stored procedures follow the existing `fn_` (function) / `usp_` (procedure) naming convention and live under `Functions/` and `Procedures/` respectively.