---
spec: agent-feature
status: done
owner: feature-pipeline-agent
related-routes:
files:
  # (none listed)
---

# agent-feature

## Goal

Enforce uniqueness of "Name" per entity scope on five existing project-scoped
xyz tables: AssetType, SystemType, Asset, CommissioningSystem (the "System"
in the spec), and CommissioningWorkflow (the "Workflow" in the spec).
Uniqueness is scoped per project (ProjectShardId) and, where the entity has a
natural parent scope, per that parent. Duplicates must be cleaned up before the
unique constraint is applied. No new tables are introduced.


## Behavior changes

- {db_calls: [usp_InsertAssetType, usp_InsertSystemType, usp_InsertAsset, usp_UpdateAsset,
    usp_InsertCommissioningSystem, usp_UpdateCommissioningSystem, usp_InsertCommissioningWorkflow,
    usp_UpdateCommissioningWorkflow], kind: error_mapping, notes: "For each of the\
    \ five entity write paths (create + update where applicable),\ncatch the DB unique-violation\
    \ (DB_ConstraintViolationError / SQLSTATE 23505\nor the explicit RAISE from the\
    \ amended procs) and map it to a 409 Conflict\nusing a typed error from src/types/errortypes.ts\
    \ (e.g. a Conflict/duplicate\nerror), with a clear message naming the entity and\
    \ the colliding name.\nDo NOT add inline SQL \u2014 continue to call the existing\
    \ usp_* procedures via\nthe existing const query strings. The actual service filenames\
    \ must be\nconfirmed against the repo; the names above are the conventional lowercase\n\
    dot-separated forms.\n", services: [asset.types.service.ts, system.types.service.ts,
    assets.service.ts, commissioning.systems.service.ts, commissioning.workflows.service.ts]}
- {kind: tests_and_dtos, notes: "No DTO shape changes are required (Name field already\
    \ exists). However,\nbecause the write procs are amended, existing API2 unit/e2e\
    \ tests, mocks,\nand fixtures for these five entities must be updated so that:\n\
    \  - duplicate-name create/update returns 409,\n  - non-duplicate create/update\
    \ still returns the existing success codes.\nUpdate fixtures/mocks so `npm test`\
    \ stays green.\n"}

## Acceptance criteria

- [ ] The behavior described above is implemented on the listed files.
- [ ] Existing behavior is unchanged when the new feature is not exercised.
- [ ] Unit and (where observable) e2e tests cover the new behavior.

## Out of scope

- No changes to read endpoints or read procedures/functions.
- No new columns added to any table; only constraint/index + proc error handling.
- No case-insensitive normalization unless the open question is resolved that way at Gate 2.
- No new entities or tables.

## Notes / open questions

- Generated by the feature pipeline from the approved architect plan. If any
  acceptance criterion is ambiguous, ask the human before implementing.

## Decisions

- **The five entities did not previously exist in api2.** A repo-wide search found
  no services, routes, or `usp_Insert*/usp_Update*` references for AssetType,
  SystemType, Asset, CommissioningSystem, or CommissioningWorkflow. Per the
  explicit "implement the FULL API2 surface needed" instruction, the write paths
  (create + update where the spec lists an update proc) were created from scratch
  as new project-scoped resources, mirroring the established `categoryTypes`
  sibling (routes → controller → validator → service).
- **409 mapping** reuses the existing `ResourceConflictError` (409) from
  `src/types/errortypes.ts` rather than introducing a new error type. Each write
  service catches `DB_ConstraintViolationError` (already produced by `db.ts` for
  SQLSTATE 23505) — or an explicit proc `RAISE` whose message contains
  "already exists" — and rethrows it as a `ResourceConflictError` naming the
  entity and colliding name.
- **No inline SQL.** Services continue to `CALL xyz."usp_*"(...)` via const query
  strings, matching the repo convention.
- **Scoping** follows the natural parent hierarchy: AssetType/SystemType are
  project-scoped; Asset is scoped by `assetTypeId`; CommissioningSystem by
  `systemTypeId`; CommissioningWorkflow by `commissioningSystemId`. Parent ids are
  supplied in the request body for create.
- **Authority:** writes are guarded by `PROJECT_EDIT` (no asset/commissioning
  specific authority exists in `authorities.types.ts`).
- **No read endpoints added** (spec out-of-scope). Resources expose only the
  enumerated write paths.
- **e2e coverage:** happy-path 201/204 and duplicate-name 409 assertions require
  the DB-side proc + unique-constraint migration, which lives outside this API
  repo. e2e specs therefore cover the validation (400) and authorization (403)
  paths, which execute before the DB layer; the 201/409 behavior is covered by
  service unit tests that stub the `query` layer.
- Build/tests were **not run**: `node_modules` is not installed in this
  environment and the task instruction forbids installing the toolchain. The
  pipeline runs the suite afterward.