---
spec: agent-feature
status: ready
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.