# API2 Specialist Report — Enforce unique Name on 5 entity tables

## Revision context

The human reviewer requested: *"I don't need any API yet, just change the
data model in postgres, revert other changes."*

My previous iteration built the **full API2 write surface** for the five
entities (AssetType, SystemType, Asset, CommissioningSystem,
CommissioningWorkflow): new routes/controllers/validators/services under
`src/api/v2/projects/*` and `src/services/*`, with 409-on-duplicate error
mapping, plus unit and e2e tests. The reviewer's instruction supersedes that:
the feature is to be delivered **purely as a Postgres data-model change**
(DB-level unique indexes) with **no API layer for now**.

Accordingly, this revision **reverts the entire API2 surface**. The api2 repo
now needs **zero** changes for this feature.

## What changed in this repo

**Nothing.** The working tree is back to the original `master` state (clean
`git diff`). The only remaining untracked files are the two spec inputs under
`docs/specs/` (pipeline task definitions, not implementation), which were left
in place.

## Files reverted

Restored to `HEAD` (were modified):

- `src/api/v2/api.v2.routes.ts` — router wiring for the five new resources.
- `src/swagger.components.schemas.json` — request/response schemas I added.

Deleted (were newly created by me):

- `src/api/v2/projects/assetTypes/` (routes, controller, validator)
- `src/api/v2/projects/assets/` (routes, controller, validator)
- `src/api/v2/projects/commissioningSystems/` (routes, controller, validator)
- `src/api/v2/projects/commissioningWorkflows/` (routes, controller, validator)
- `src/api/v2/projects/systemTypes/` (routes, controller, validator)
- `src/services/asset.types.service.ts`
- `src/services/assets.service.ts`
- `src/services/commissioning.systems.service.ts`
- `src/services/commissioning.workflows.service.ts`
- `src/services/system.types.service.ts`
- `test/unit/api/v2/{assetTypes,assets,commissioningSystems,commissioningWorkflows,systemTypes}/`
- `test/unit/services/{asset.types,assets,commissioning.systems,commissioning.workflows,system.types}.service.spec.ts`
- `test/e2e/api/{asset.types,assets,commissioning.systems,commissioning.workflows,system.types}.e2e.spec.ts`

## Where the data-model change lives

The "change the data model in postgres" part is owned by the **Postgres
specialist** in the sibling `PostgreSQLDatabase` repo
(`storage/clones/baf6a8d39054/postgres`), not this api2 repo. The actual DDL
for these tables (and all `usp_*` procedures) lives there and is consumed by
Liquibase at deploy/e2e time — it is never checked into api2 (confirmed via
`docker/docker-compose.yml`, which mounts `${DATABASE_REPO_PATH}/PostgreSQLDatabase`).

That specialist has already implemented the change as five
`CREATE UNIQUE INDEX IF NOT EXISTS` statements appended to
`Database/xyz/Constraints/999_indexes.sql`:

| Table | Unique scope | Notes |
| --- | --- | --- |
| `AssetType` | `(ProjectShardId, CommissioningWorkflowId, Name)` | total |
| `SystemType` | `(ProjectShardId, CommissioningWorkflowId, Name)` | total |
| `Asset` | `(ProjectShardId, ParentAssetId, Name)` | partial `WHERE IsDeleted = FALSE`, `NULLS NOT DISTINCT` |
| `CommissioningSystem` | `(ProjectShardId, SystemTypeId, Name)` | partial `WHERE IsDeleted = FALSE` |
| `CommissioningWorkflow` | `(ProjectShardId, Name)` | total |

I did **not** modify the `postgres` repo — it is a separate specialist's scope
and the change is already present there.

## Deviation from the spec / plan

`docs/specs/20260625-171840-agent-feature.md` and the upstream `_PLAN.yaml`
called for API2 service-level error mapping (catch SQLSTATE 23505 → 409) plus
tests. The reviewer's revision explicitly drops the API for now, so that
behavior-change work is intentionally **not** implemented in api2. This is a
deliberate deviation driven by the reviewer instruction, which takes precedence
over the (now stale) spec.

## Build / tests

Not run. `npm`/`npx` are not on PATH in this environment and the task
instruction forbids installing a toolchain. There is nothing to test in api2
regardless — the working tree is identical to `master`, so the existing suite
is unaffected. The pipeline runs the suite itself afterward.

## Open questions for the human reviewer

1. **Confirm api2 should ship with no changes.** This revision leaves api2
   untouched. If/when you do want the API surface (write endpoints + 409
   mapping), the previous implementation can be reinstated from git history.
2. **Pre-existing duplicate data.** Creating the unique indexes will fail if any
   environment already holds colliding names. The Postgres specialist's report
   flags that the dedupe must run as a pre-deploy migration (a `Patch/` file
   runs too late). No api2 action needed, but worth tracking before deploy.
3. **Case sensitivity / scope** of the indexes are as the Postgres specialist
   documented (case-sensitive; per-project + natural parent scope). Confirm that
   matches intent before the eventual API is built on top of it.