# Specialist Report — Postgres (fn_GetBiggestFile)

> This change is **AI-generated**. The Liquibase changeset for the new function is
> authored as `agentneo` and the object header uses the display name `AgentNeo`.

## Summary

Implements the Postgres side of `GET /api/v2/projects/{projectId}/files/biggest`: a
read-only, table-returning function `xyz."fn_GetBiggestFile"(_projectId UUID)` that returns
the metadata for the largest non-soft-deleted file (by `FileSizeBytes`) in a project. No DDL
on any table — read-only over the existing `xyz."FileReference"`.

## Revision addressed

Human reviewer feedback: **"fix the tests"**. The prior run added the function but no
integration coverage, while `_PLAN.yaml`'s `testing_plan` calls for a Postgres scenario
covering `fn_GetBiggestFile`. This revision adds that coverage. The function file was left
intact — it was already correct against the actual schema (verified column-by-column below).

## Files added / modified

### `Database/xyz/Functions/`
- `fn_GetBiggestFile.sql` *(added, from prior run — unchanged this revision)* — changeset
  `agentneo:fn_GetBiggestFile`, `runOnChange:true`, `endDelimiter:/`, terminating `/`.

### `IntegrationTest/` (new test coverage — this revision)
- `src/biggest_file/__init__.py` *(added)* — package marker.
- `src/biggest_file/setup.py` *(added)* — inserts a deterministic `FileReference` set per
  project and computes, in Python, the expected winning `FileReferenceId` (or `None`) by
  replaying the function's `ORDER BY`.
- `src/biggest_file/assert_queries.py` *(added)* — calls `fn_GetBiggestFile`, asserts the
  expected file (or zero rows), and cross-checks every returned metadata column against the
  underlying `FileReference` row so the column mapping is verified end to end.
- `main.py` *(modified)* — imports and runs the new scenario after the key-milestone block.

## Test scenarios covered (from `testing_plan`)

- **Largest returned** — multiple live files of differing sizes; the biggest is returned.
- **Soft-deleted excluded** — a `DeletedOn`-set file that is physically the largest
  (1,000,000 bytes) must not be selected.
- **Tie-break** — two live files share the maximum size (5,000); the earlier `InsertedOn`
  wins (matches `ORDER BY FileSizeBytes DESC NULLS LAST, InsertedOn ASC, FileReferenceId ASC`).
- **NULL `FileSizeBytes`** — a NULL-size file ranks lowest (`DESC NULLS LAST`) and is not
  selected when sized files exist.
- **Zero rows** — a project whose only file is soft-deleted returns no rows.
- **Filters by `ProjectShardId` only** — two projects each get their own file set and the
  function returns each project's own biggest file (cross-project isolation).

## Schema verification (function vs. actual `xyz."FileReference"`)

Every column the function selects exists on the current table after all alters:
- Base table `033_xyz_image_video_tables.sql`: `FileReferenceId`, `FileName`,
  `FileExtension`, `XyzDisplayName`, `Description`, `CloudStoragePath`, `InsertedOn`,
  `CreatedBy`, `LastModifiedBy`, `LastModifiedOn`, `DeletedOn`, `ProjectShardId`.
- `051_…`: `FileSizeBytes BIGINT NULL`.
- `054_…`: `FileHash TEXT NULL`.
- `068_…_part3`: `ImportedFromCDE BOOLEAN NOT NULL`, `ThirdPartyFileId TEXT NULL`.
- Dropped columns (`CloudStorageURL`, `UseLegacyStorageContainer` in `043_…`) are **not**
  referenced by the function. Confirmed.

## Notes / deviations

- **`fn_GetProjectShardId` raises, it does not return NULL.** The plan's notes describe the
  404 path as "fn_GetProjectShardId returning NULL/no shard"; the actual function `RAISE`s an
  exception for an unknown project. This still produces the intended 404 at the API2 layer
  (the error propagates), so the function is unchanged. The integration test therefore does
  **not** call `fn_GetBiggestFile` with a non-existent project (that path raises by design and
  is the API2 specialist's 404 concern, not a Postgres zero-rows case).
- No schema changes, no constraints, no indexes (`index_changes: none`). Nothing added to
  `999_indexes.sql`.
- **Column coverage:** the function returns the full egress column set listed in the plan's
  `response_dto` (fileReferenceId, projectId, fileName, fileExtension, xyzDisplayName,
  description, cloudStoragePath, insertedOn, createdBy, lastModifiedBy, lastModifiedOn,
  fileSizeBytes, fileHash, importedFromCDE, thirdPartyFileId). No table column was
  added/removed, so no read/write procs needed amending.

## Verification

- **Static:** all three Python files parse (`ast.parse`) and are `black`-formatted
  (`--target-version py312`). The new function follows the function template (drop-then-create,
  `endDelimiter:/`, trailing `/`).
- **Runtime:** `./build` and `./test` could **not** be run in this environment — `docker`,
  `liquibase`, and `psql` are all unavailable, so a from-scratch changelog deploy and the live
  integration run were not performed. The test was written against the real helper API
  (`PostgresqlHelper`, `setup_projects_for_portfolio`) and the verified table schema.

## Open questions for human reviewer

1. **NULL-size files** — confirm files with unknown `FileSizeBytes` should remain eligible
   (ranked lowest) rather than excluded entirely (matches the plan's stated design).
2. **Tie-break** — confirm earliest `InsertedOn` then `FileReferenceId ASC` is the desired
   deterministic tie-break on equal maximum sizes.