summary: 'Add a read-only API2 endpoint GET /api/v2/projects/{projectId}/files/biggest that returns the full file metadata for the largest non-soft-deleted file (by FileSizeBytes) in a project, returning 200 with null body when none exist and 404 when the project does not exist. Backed by a new fn_GetBiggestFile function; no schema changes. ' target_service: api2 postgres_changes: - kind: function schema: xyz name: fn_GetBiggestFile file: Database/xyz/Functions/fn_GetBiggestFile.sql description: 'Create a new table-returning function xyz."fn_GetBiggestFile"(_projectId UUID) that resolves the ProjectShardId via xyz."fn_GetProjectShardId", then selects the single FileReference row with the largest FileSizeBytes for that ProjectShardId, excluding soft-deleted rows (DeletedOn IS NULL). FileSizeBytes is NULLable, so NULLs are treated as smallest (ORDER BY FileSizeBytes DESC NULLS LAST). Tie-break by InsertedOn ASC then FileReferenceId ASC for deterministic results. Returns at most one row; returns zero rows when no eligible file exists (API2 maps zero rows to a null body). ' returns: "TABLE (\n \"fileReferenceId\" UUID,\n \"projectId\" UUID,\n \"fileName\"\ \ TEXT,\n \"fileExtension\" TEXT,\n \"xyzDisplayName\" TEXT,\n \"description\"\ \ TEXT,\n \"cloudStoragePath\" TEXT,\n \"insertedOn\" TIMESTAMP WITH TIME ZONE,\n\ \ \"createdBy\" TEXT,\n \"lastModifiedBy\" TEXT,\n \"lastModifiedOn\" TIMESTAMP\ \ WITH TIME ZONE,\n \"fileSizeBytes\" BIGINT,\n \"fileHash\" TEXT,\n \"importedFromCDE\"\ \ BOOLEAN,\n \"thirdPartyFileId\" TEXT\n)\n" notes: 'Read-only over existing xyz."FileReference". No DDL on any table. Filter strictly by _projectShardId (never ProjectId). Project existence is implied by fn_GetProjectShardId returning NULL/no shard; the API2 layer surfaces 404 if the project does not exist. ' citus_changes: none index_changes: none reporting_changes: none seed_data_changes: none mongo_changes: none api2_changes: - method: GET path: /api/v2/projects/{projectId}/files/biggest tag: Files permissions: - FILE_VIEW db_calls: - fn_GetBiggestFile files: - src/api/v2/projects/files/files.routes.ts - src/api/v2/projects/files/files.controller.ts - src/services/files.service.ts response_dto: name: FileReference notes: 'Reuse the existing file metadata egress DTO returned by the other /files endpoints (fileReferenceId, projectId, fileName, fileExtension, xyzDisplayName, description, cloudStoragePath, insertedOn, createdBy, lastModifiedBy, lastModifiedOn, fileSizeBytes, fileHash, importedFromCDE, thirdPartyFileId). No new DTO required; if a dedicated egress mapper does not already exist for the biggest-file shape, reuse the existing file mapper. ' notes: 'Controller calls a single service method that runs SELECT * FROM xyz."fn_GetBiggestFile"($1::UUID). The function performs the project-shard resolution AND the largest-file selection in one round-trip; the controller does NOT separately fetch the project first. When the function returns zero rows, respond HTTP 200 with a null body. If the project does not exist, the service/function path surfaces NOT_FOUND which the controller maps to 404 via createApiErrorResponse. This is a single, non-paginated read (not a list), so it does NOT use the PaginationEnvelope. Ensure the route is registered BEFORE the /files/{fileReferenceId} param route so "biggest" is not captured as a fileReferenceId. ' api1_changes: none inter_service_calls: none new_permissions: none java_frozen_resources: - resource: projects note: 'projects is a dual-API resource (present in API2 and API1 hc-project). This feature only reads project context to resolve ProjectShardId in Postgres. No API1 hc-project changes are made; the Java ProjectResource is not modified. ' risks: - 'FileSizeBytes is NULLable on xyz.FileReference. The function orders DESC NULLS LAST so a project whose only files have NULL sizes will still return one of them (the deterministic tie-break winner) rather than null. Confirm with the team whether files with unknown size should be excluded entirely; current design includes them but ranks them lowest. ' - 'Ties on identical maximum FileSizeBytes are resolved by earliest InsertedOn then FileReferenceId (open question in the spec). Confirm this tie-break is acceptable. ' - 'The ''files'' resource overlaps conceptually with dual-API file handling, but FileReference is a Postgres-owned operational table and is correctly served by API2 + Postgres only. ' out_of_scope: - Returning a ranked/sorted list of files by size (only the single biggest is returned). - Filtering biggest-file selection by file type, extension, or other attributes. - Any changes to API1 services (hc-project, hc-iam, hc-bpm, hc-notification). - Including BatchFile or UserFile rows; only xyz.FileReference is considered. testing_plan: 'Postgres: add an IntegrationTest/main.py scenario (only if it materially affects query behavior) covering fn_GetBiggestFile for: a project with multiple files of differing sizes (largest returned), soft-deleted files excluded (DeletedOn set), tie-breaking on equal sizes, NULL FileSizeBytes handling, and a project with no eligible files (zero rows). Verify the function filters by ProjectShardId only. API2: add unit tests for files.service.ts and files.controller.ts asserting the new endpoint returns the mapped file metadata for the biggest file, returns HTTP 200 with null body when fn_GetBiggestFile yields zero rows, and returns 404 when the project does not exist. Add an e2e spec under test/e2e for GET /api/v2/projects/{projectId}/files/biggest covering the happy path, empty-project null body, and unknown-project 404. Ensure route ordering does not let "biggest" be matched by the {fileReferenceId} route. Run npm test and keep it green.' _meta: model: claude-opus-4-8 atom_ids: - endpoint.api2.get__api_v2_projects__projectId__files__fileReferenceId_ - endpoint.api2.get__api_v2_projects__projectId__files - endpoint.api2.post__api_v2_projects__projectId__files - endpoint.api2.get__api_v2_projects__projectId__userfiles - endpoint.api2.get__api_v2_projects__projectId__userfiles_models - endpoint.api2.get__api_v2_projects__projectId__userfiles__userFileId__ingest-status - endpoint.api2.get__api_v2_projects__projectId__batchfiles__batchFileId__ingest-status - endpoint.api2.get__api_v2_projects__projectId__activities_mapping - endpoint.api2.get__api_v2_projects__projectId__videos__videoFileId_ - endpoint.api2.get__api_v2_projects__projectId__issues__issueId__history - endpoint.api2.get__api_v2_projects__projectId__category-types - endpoint.api2.get__api_v2_projects__projectId__models_folders - pg.xyz.Project - mongo.hc-project.project - mongo.embedded.Project (iam) - dto.api1-hc-project.Project - endpoint.api1-hc-iam.syncProject - dto.api2.Project - pg.xyz.BatchFile - pg.xyz.FileReference - pg.xyz.IssueFileReferenceMapping - pg.xyz.UserFile - pg.xyz.UserFileError - pg.xyz.UserFile_backup spec: title: Get Biggest File in Project project_kind: modify user_facing_behavior: A new GET endpoint on API2 (e.g. GET /api/v2/projects/:projectId/files/biggest) returns the full file metadata object for the largest file (by byte size) belonging to the specified project. Soft-deleted files are excluded from consideration. If the project has no eligible files, the endpoint returns HTTP 200 with a null body (or null data field). data_touched: - project - file api_surface: read non_functional_requirements: - Only non-soft-deleted files are considered - Returns 200 with null when no files exist for the project - Returns standard 404 if the project itself does not exist out_of_scope: - Returning a ranked/sorted list of files by size - Filtering by file type or other attributes - Changes to API1 services open_questions: - What is the exact route path convention for this endpoint in API2? - If multiple files share the same maximum byte size, which should be returned (e.g. earliest created, most recently created)? - Does the caller need to be authorized to access the project, and if so, does the existing project-level auth middleware cover this?