# Specialist Report — AssetType CR Endpoints (Postgres)

All change files are **AI-generated** and authored as `agentneo` in their
`--changeset` headers (display name `AgentNeo` in the object header blocks).

## Revision addressed: "fix the api test fails"

The API2 layer (the other specialist's committed contract in
`../api2/src/services/asset.types.service.ts` and its unit/e2e tests) does
**not** match what my first pass built. I re-shaped the Postgres objects to
the API contract as observed in that service and its tests:

| API2 call (source of truth)                         | First pass (wrong)                                  | Now |
|-----------------------------------------------------|-----------------------------------------------------|-----|
| `SELECT * FROM xyz."fn_GetAssetTypeList"($1)`       | `fn_GetAssetTypes`                                  | renamed to `fn_GetAssetTypeList` |
| `SELECT * FROM xyz."fn_GetAssetType"($1,$2)`        | `fn_GetAssetType` (OK)                              | unchanged |
| `SELECT * FROM xyz."fn_InsertAssetType"($1,$2,$3,$4)` = `(projectId, name, code, createdBy)` | `usp_InsertAssetType` **procedure** w/ OUT params, requiring `commissioningWorkflowId` | replaced with **function** `fn_InsertAssetType(projectId, name, code, createdBy)` returning the row |
| 409 mapping via constraint names `AssetType_ProjectShardId_Name_key` / `AssetType_ProjectShardId_Code_key` | procedural `RAISE ... ERRCODE 'P0409'`, no such constraints | added the two named UNIQUE constraints; insert lets the violation propagate |

The API create payload carries only `name` + `code` (no
`commissioningWorkflowId`), and its DTO omits the workflow entirely — a
divergence from `_PLAN.yaml`, but the API tests are the contract for this
revision. Since `xyz."AssetType"."CommissioningWorkflowId"` is `NOT NULL`,
`fn_InsertAssetType` maps the new AssetType to the project's **default
CommissioningWorkflow** (the earliest-inserted workflow for the project —
every project gets a `'Default Workflow'` at creation via
`usp_InsertProject` / `usp_InsertProject_V2`). It raises a clear error if
the project has none.

## Files

### Added — `Database/xyz/Functions/`
- `fn_InsertAssetType.sql` — inserts and returns the full row; uniqueness is
  enforced by the DB constraints (no procedural pre-check), so a duplicate
  Name/Code surfaces as a `unique_violation` carrying the constraint name for
  the API's 409 mapping.
- `fn_GetAssetTypeList.sql` — full column set for a project, ordered by Name,
  project-isolated by `ProjectShardId`.
- `fn_GetAssetType.sql` — unchanged from prior pass (name already matched);
  full column set filtered by `ProjectShardId` + `AssetTypeId`.

### Added — `Database/xyz/Constraints/`
- `083_xyz_asset_type_name_code_unique.sql` — `AssetType_ProjectShardId_Name_key`
  UNIQUE (`ProjectShardId`, `Name`) and `AssetType_ProjectShardId_Code_key`
  UNIQUE (`ProjectShardId`, `Code`).

### Modified — `Database/xyz/Tables/`
- `118_xyz_asset_type.sql` — the `"Code"` `ADD COLUMN IF NOT EXISTS` (from the
  prior pass) is kept; its comment now points at the constraints file instead
  of the removed procedure. (`"Code"` is `TEXT NULL`; NULLs are distinct under
  the unique constraint, and the create path always supplies a code.)

### Removed
- `Database/xyz/Procedures/usp_InsertAssetType.sql` — superseded by
  `fn_InsertAssetType`.
- `Database/xyz/Functions/fn_GetAssetTypes.sql` — renamed to `fn_GetAssetTypeList`.

## Numeric prefixes
- Constraints: `083` — continues from the highest existing constraint prefix
  (`082_xyz_system_type_name_unique.sql`).

## Deviations from the plan (with reasons)
1. **Insert is a function, not `usp_InsertAssetType` procedure**, and drops the
   `commissioningWorkflowId` parameter — required to match the API2 service
   call `fn_InsertAssetType($1,$2,$3,$4)` and its request DTO. Workflow is
   auto-assigned to the project default to satisfy the `NOT NULL` column.
2. **List function is `fn_GetAssetTypeList`, not `fn_GetAssetTypes`** — matches
   the API2 `SQL.LIST` call.
3. **Uniqueness enforced by named UNIQUE constraints, not a procedural
   `P0409`** — the API2 service maps 409 by matching the constraint names in
   the DB error, so the constraints must exist and the violation must
   propagate. Consequently name uniqueness is now **case-sensitive** (the
   plan's case-insensitive check lived in the removed procedure); this matches
   the sibling `SystemType_ProjectShardId_Name_key` precedent and is not
   exercised by the API tests.
4. **Separate constraints file (`083_…`) rather than appending to
   `066_xyz_asset_type_constraints.sql`** — follows the recent, directly
   analogous team precedent (`081_…_commissioning_workflow_name_unique`,
   `082_…_system_type_name_unique`) and lets the new changeset carry the
   mandated `agentneo` author.

## Verification
- `./build` / `./test`: **not run** — Docker is unavailable in this
  environment and no `build` script is present. Runtime deploy could not be
  performed.
- Static review: functions mirror the proven `fn_GetAssetType` RETURN QUERY
  pattern (qualified `at.` columns, no OUT/column ambiguity); changelog uses
  `<includeAll>` for Functions/Procedures/Constraints, so the removed proc and
  renamed function are picked up without editing `non_distributed_changelog.xml`.
  No remaining references to the old object names exist in the repo.

## Open questions for the human reviewer
1. **Default-workflow assignment**: the API dropped `commissioningWorkflowId`
   from create, so every new AssetType is bound to the project's default
   workflow. Is that the intended semantics, or should the API/DTO be revised
   to accept a workflow id (per the original plan)?
2. **Case-sensitive Name uniqueness** is now in effect (was case-insensitive in
   the plan). Confirm acceptable, or request a functional unique index on
   `LOWER("Name")` (kept under the same constraint name so the 409 mapping
   still matches).