# Specialist Report — Per-project UNIQUE constraint on `Name` (5 tables)

**This change file is AI-generated and was authored as `agentneo`.**

## What was implemented

Added a composite `UNIQUE ("ProjectShardId", "Name")` constraint to 5
existing project-scoped `xyz` tables. Uniqueness is scoped per project per
reviewer guidance, and the composite form (distribution column leading) is
also a hard Citus requirement: any UNIQUE constraint on a distributed table
must include the distribution column.

| Spec name  | Real table                    | Constraint name                                |
| ---------- | ----------------------------- | ---------------------------------------------- |
| AssetType  | `xyz."AssetType"`             | `AssetType_ProjectShardId_Name_uq`             |
| SystemType | `xyz."SystemType"`            | `SystemType_ProjectShardId_Name_uq`            |
| Asset      | `xyz."Asset"`                 | `Asset_ProjectShardId_Name_uq`                 |
| System     | `xyz."CommissioningSystem"`   | `CommissioningSystem_ProjectShardId_Name_uq`   |
| Workflow   | `xyz."CommissioningWorkflow"` | `CommissioningWorkflow_ProjectShardId_Name_uq` |

The spec's "System"/"Workflow" map to `CommissioningSystem`/
`CommissioningWorkflow` — the only matching project-scoped tables — per the
plan's risk note.

## Files added

- `Database/xyz/Constraints/081_xyz_name_unique_constraints.sql` (new)

No existing files were modified. No table-create files touched. No
`non_distributed_changelog.xml` change (no new schema; the
`<includeAll path="Database/xyz/Constraints"/>` entry already picks up the
new numbered file).

## Numeric prefix chosen

`081` — the highest existing prefix in `Database/xyz/Constraints/` was `080`
(`080_xyz_model_element_asset_constraints.sql`), so the next sequential
number is `081`. Ordering is safe: all 5 target tables are created in the
earlier `Database/xyz/Tables` `includeAll` phase (file numbers 116–131),
which runs before any `Constraints` file.

## Placement & convention compliance

- Constraint lives in `Database/xyz/Constraints/` (not in a table-create
  file) — required for Citus submodule compatibility. ✔
- Each UNIQUE is composite with `ProjectShardId` as the leading column. ✔
- Idempotent `DROP CONSTRAINT IF EXISTS` → `ADD CONSTRAINT` pattern, matching
  the surrounding constraint files; changeset is `runOnChange:true` so it
  re-applies cleanly on redeploy. ✔
- Changeset authored `agentneo:081_xyz_name_unique_constraints`; object header
  uses display name `AgentNeo`. ✔
- No FK changes; `index_changes: none` in the plan, so `999_indexes.sql` was
  left untouched. ✔

A single new file was chosen over appending into the 5 existing per-table
constraint files because the mandatory author rule requires every changeset I
write to be authored `agentneo` — appending would have meant editing changesets
owned by `davewebb`. This also matches the plan's own suggested example of a
single new `NNN_..._unique.sql` file.

## Verification

- `./build`: **not run** — no `build` script exists in this checkout (only
  `./test`) and Docker is unavailable in this environment.
- `./test`: **not run** — Docker unavailable.
- Static review performed and passed:
  - All 5 tables confirmed to have `"Name" TEXT NOT NULL` and
    `"ProjectShardId" INT NOT NULL` columns.
  - New constraint file uses the plan's literal constraint names and the
    `("ProjectShardId", "Name")` column order.
  - `git status` confirms only the new `081_...` file is added; the 5
    pre-existing constraint files are unmodified.

## Deviations from the plan

None material. The plan offered the constraint placement as an example
(`NNN_xyz_commissioning_unique.sql` or appending alongside each table's
constraints); a single new `081_...` file was used to satisfy the mandatory
`agentneo` author rule while staying in the Constraints directory.

## Open questions / risks for human reviewer

1. **Soft-delete (Asset, CommissioningSystem)**: a plain UNIQUE constraint
   blocks reusing the `Name` of a *soft-deleted* row (`IsDeleted = true`).
   The plan defaulted to the hard UNIQUE, and `index_changes: none` confirms
   no partial unique index was selected at Gate 2. If names must be reusable
   after soft-delete, this needs reworking as a partial UNIQUE index
   `WHERE "IsDeleted" = false`. **Shipped the hard UNIQUE as approved.**
2. **Existing data**: constraint creation will fail on deploy if any
   environment already holds duplicate `(ProjectShardId, Name)` rows. The plan
   asserts all 5 tables are currently empty — verify before deploying to any
   non-empty environment.