# Postgres Specialist Report — System (CommissioningSystem) CR Endpoints

All change files are **AI-generated**, authored in the changeset headers as
`agentneo` (display name `AgentNeo`).

## Revision addressed

**Reviewer feedback: "fix the failing api test."**

Root cause: the API2 `systems.service.ts` invokes the create path as a
**function** call —
`SELECT * FROM xyz."fn_InsertCommissioningSystem"($1, $2, $3, $4)` — but my
prior work implemented the insert as a **procedure**
(`usp_InsertCommissioningSystem`, returning via `OUT` params). A procedure is
not callable via `SELECT * FROM ...`, so the create endpoint (and every e2e
scenario that first creates a system: pagination, get-by-id, 404 mapping)
failed.

Fix: replaced the procedure with a function that mirrors the established
`fn_InsertAsset` pattern (the Asset CR endpoints from PAPI-3618, which the plan
names as the reference pattern).

## Files added / modified

### `Database/xyz/Functions/`
- **`fn_InsertCommissioningSystem.sql`** (NEW) — replaces the removed procedure.
  - Signature `(_projectId UUID, _systemTypeId UUID, _name TEXT, _createdBy TEXT)`
    — positionally matches the API2 `INSERT` call `($1,$2,$3,$4)`.
  - Resolves `_projectShardId` via `xyz."fn_GetProjectShardId"(_projectId)`.
  - Validates the `SystemType` belongs to the same project; raises
    `'SystemType with id % not found in project'` when it does not. The API2
    `mapError` matches on the substring `'SystemType with id'` and maps it to a
    `NotFoundError` → HTTP 404, so this message is deliberately preserved.
  - Inserts by `ProjectShardId` (never `ProjectId`) and returns the created row
    by delegating to `xyz."fn_GetCommissioningSystem"` — single source of truth
    for the returned column set.
- **`fn_GetCommissioningSystem.sql`** (unchanged from prior work) — matches API2
  `GET_BY_ID` call `fn_GetCommissioningSystem($1,$2)`.
- **`fn_GetCommissioningSystemList.sql`** (unchanged from prior work) — matches
  API2 `LIST` call `fn_GetCommissioningSystemList($1,$2,$3)` =
  `(projectId, lastFetchedIndexId, size)`; my params are
  `(_projectId, _lastFetchedIndexId, _pageSize DEFAULT 1000)`. Returns the `Id`
  serial as the keyset cursor.

### `Database/xyz/Procedures/`
- **`usp_InsertCommissioningSystem.sql`** — **DELETED**. Superseded by the
  function above.

## Column / call alignment with API2

API2 `mapRow` reads: `CommissioningSystemId`, `Name`, `SystemTypeId`,
`CreatedBy`, `InsertedOn`, `LastModifiedOn`, `LastModifiedBy` (plus `Id` as the
pagination `indexId`). All are returned by the three functions. Column names
were verified against `Database/xyz/Tables/130_xyz_commissioning_system.sql`
and the `SystemType` reference against `119_xyz_system_type.sql`.

## Numeric prefixes

None chosen — all three objects are functions (one-file-per-object, named after
the object), and no table/constraint/index files were touched.

## Project-scoped table checklist

Not applicable — no new table was created. `CommissioningSystem` already exists
(`130_xyz_commissioning_system.sql`, PK `("ProjectShardId","CommissioningSystemId")`,
FKs in `078_xyz_commissioning_system_constraints.sql`). No index changes
(`index_changes: none` in the plan).

## Build / test status

- `./build` and `./test` could **not** be run — Docker is unavailable in this
  environment. Static verification was performed instead:
  - Function bodies parse and follow the `fn_InsertAsset` template.
  - All three DB objects match the API2 service call sites by name, arity, and
    returned column names (verified against `api2/src/services/systems.service.ts`
    and `api2/test/e2e/api/systems.e2e.spec.ts`).
  - No remaining references to the deleted `usp_InsertCommissioningSystem` in
    `Database/` or `non_distributed_changelog.xml`.

## Deviations from the plan

- The plan's `postgres_changes` specified the create path as a **procedure**
  (`usp_InsertCommissioningSystem`). It was implemented as a **function**
  (`fn_InsertCommissioningSystem`) to match how the API2 layer actually calls
  it (`SELECT * FROM fn_...`) and to follow the reference `fn_InsertAsset`
  pattern the plan itself names. This is the fix for the failing API test.

## Open questions for reviewer

- None. If the plan's `db_calls`/`postgres_changes` naming is used to drive any
  downstream automation, note it should now read `fn_InsertCommissioningSystem`.