# Specialist Report — AssetType CR DB surface (Postgres)

All change files are **AI-generated** and authored with the changeset author
`agentneo` (display name `AgentNeo`).

## Revision addressed

**Reviewer feedback:** "fix the pagination for get asset type list, follow the
pattern in other GetWhateverList ones on how they advance to next pages."

`fn_GetAssetTypeList` previously returned a flat, unpaginated list ordered by
`InsertedOn`. It now follows the keyset-pagination pattern used by the other
list functions (anchor: `xyz."fn_GetCoordinateList"`):

- Added parameters `_lastFetchedIndexId INTEGER DEFAULT NULL`,
  `_limit INTEGER DEFAULT 1000`, and
  `_lastSyncDateTime TIMESTAMP WITH TIME ZONE DEFAULT NULL`.
- Pages advance on the `Id` INT identity column: the caller passes the last
  `Id` it saw as `_lastFetchedIndexId`, and the query filters `at."Id" > _lastFetchedIndexId`.
- Added `"Id" INTEGER` to the `RETURNS TABLE` shape so the API2 layer can read
  it back as `lastFetchedIndexId` for the next request.
- Ordering changed from `InsertedOn ASC` to `Id ASC` (required for correct,
  stable keyset paging), with `LIMIT _limit`.
- Incremental-sync filter mirrors the anchor: when `_lastSyncDateTime` is
  supplied, filters on `COALESCE(at."LastModifiedOn", at."InsertedOn")`
  (AssetType has no `UpdatedOn`; the anchor's `UpdatedOn` maps to this table's
  `LastModifiedOn`).

**API2 impact:** the list function now returns an extra `Id` column and accepts
three new optional args. The API2 specialist must call `fn_GetAssetTypeList`
with the pagination args and build the `PaginationEnvelope` (`records`,
`recordCount`, `lastFetchedIndexId`) from the returned `Id`, consistent with
`fn_GetCoordinateList` consumers.

## Files

### Modified — `Database/xyz/Functions/`
- `fn_GetAssetTypeList.sql` — added keyset pagination (this revision).

### Added (prior work, unchanged this round) — `Database/xyz/Functions/`
- `fn_GetAssetType.sql` — single AssetType by project + id.
- `fn_InsertAssetType.sql` — inserts an AssetType, validates the referenced
  CommissioningWorkflow in the same project, returns the created row.

## Column verification

All functions read/write the exact `xyz."AssetType"` column set from
`Database/xyz/Tables/118_xyz_asset_type.sql`: `Id`, `InsertedOn`, `AssetTypeId`,
`ProjectShardId`, `CommissioningWorkflowId`, `Name`, `CreatedBy`,
`LastModifiedOn`, `LastModifiedBy`. All filter by `ProjectShardId` (resolved via
`fn_GetProjectShardId`), never `ProjectId`.

## Deviations from plan

- The plan specified the insert surface as a **procedure**
  (`usp_InsertAssetType`); prior work implemented it as a **function**
  (`fn_InsertAssetType`) that returns the created row. This predates the current
  revision and was left intact per the revision instructions. Flagging for
  reviewer awareness — if the API2 layer expects a procedure signature this
  should be reconciled.
- The plan's v1 notes said "no pagination in v1 (flat list)". The reviewer has
  now explicitly requested pagination, superseding that note; implemented per
  the established list-function pattern.

## Verification

- `./build` and Docker are **not available** in this environment — runtime
  from-scratch deploy could not be performed.
- Static review: header/changeset conventions match the anchor
  (`runOnChange:true`, `endDelimiter:/`, trailing `/`); parameter and column
  names verified against the table create file; pagination logic mirrors
  `fn_GetCoordinateList` exactly.

## Open questions

- Confirm insert surface should stay a function vs. the planned procedure.
- Default `_limit` of 1000 matches the anchor; confirm this is the desired page
  size for AssetType.