# Postgres 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."*

The previous iteration enforced uniqueness in **application logic** (an
`EXISTS` pre-check inside `usp_Insert*`/`usp_Update*` procedures that
`RAISE`d a `unique_violation`) plus a one-off dedupe **patch**. None of it
was an actual data-model constraint.

This revision replaces that with a pure **data-model** change — DB-level
partial/total unique indexes — and reverts everything else (the procedures
and the patch), per the reviewer's instruction.

## Files modified

- `Database/xyz/Constraints/999_indexes.sql` — appended five
  `CREATE UNIQUE INDEX IF NOT EXISTS` statements (one per entity), grouped
  by table with the standard `-- xyz.<Table>` header comments. No new index
  file was created (per convention, all indexes live in `999_indexes.sql`).

## Files reverted (deleted)

These were the API-facing / one-off changes the reviewer asked to drop:

- `Database/xyz/Procedures/usp_InsertAsset.sql`
- `Database/xyz/Procedures/usp_InsertAssetType.sql`
- `Database/xyz/Procedures/usp_InsertSystemType.sql`
- `Database/xyz/Procedures/usp_InsertCommissioningSystem.sql`
- `Database/xyz/Procedures/usp_InsertCommissioningWorkflow.sql`
- `Database/xyz/Procedures/usp_UpdateAsset.sql`
- `Database/xyz/Procedures/usp_UpdateCommissioningSystem.sql`
- `Database/xyz/Procedures/usp_UpdateCommissioningWorkflow.sql`
- `Database/Patch/018_patch_dedupe_entity_names.sql`

Working tree is now clean apart from the single `999_indexes.sql` edit.

## The data-model change (unique indexes)

All five are expressed as unique **indexes** (not table `CONSTRAINT`s)
because two of them are partial (`WHERE "IsDeleted" = FALSE`), and a partial
unique key can only be an index. The other three could have been
`UNIQUE` constraints, but they are kept as indexes so all five live together
in `999_indexes.sql` and read consistently.

| 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 |

Scope columns were confirmed against the table create files
(`118_xyz_asset_type.sql`, `119_xyz_system_type.sql`, `129_xyz_asset.sql`,
`130_xyz_commissioning_system.sql`, `116_xyz_commissioning_workflow.sql`).
Only `Asset.ParentAssetId` is nullable — hence `NULLS NOT DISTINCT` there so
root-level assets (NULL parent) are also constrained to unique names. The
soft-delete tables (`Asset`, `CommissioningSystem`) use a partial predicate
so a soft-deleted name can be reused. `NULLS NOT DISTINCT` is already used
elsewhere in the repo (`011_xyz_categorytype_constrains.sql`) and the
deployment image is `postgres` (latest, PG16+), so the syntax is supported.

These statements are AI-generated. The unique-index lines carry no
`--changeset` of their own — they were appended to the existing
`davewebb:999_indexes` changeset, which already has `runOnChange:true` and
re-runs on change, exactly as the conventions require for index additions.

## Deviation from `_PLAN.yaml` (intentional, driven by reviewer)

`_PLAN.yaml` listed `index_changes: none` and routed enforcement through
amended procedures. The reviewer's revision explicitly asked for a
data-model change with the API/procedure work reverted, which can only be
satisfied with DB-level unique indexes. I followed the reviewer's
instruction over the stale plan field. This supersedes the plan's
proc-based approach and its dedupe patch.

## Build / test

- `./build` — **not available** in this repo (no such script; the brief's
  reference to it does not match this checkout).
- `./test` / `./test --no-docker` — **could not run**: Docker is not
  available in this environment and no Postgres is running on 5490.

Runtime verification was therefore not performed. Static review done:
column names and nullability verified against the table files; index syntax
(`NULLS NOT DISTINCT` before `WHERE`) matches the Postgres `CREATE INDEX`
grammar; the edit is confined to the existing index changeset.

## Open questions for the human reviewer

1. **Existing duplicate data / deployment ordering.** These tables are
   newly added (June 2026) so there is most likely no production data to
   dedupe, and a fresh `./build` deploys clean. But if any environment
   already holds colliding names, creating these unique indexes will FAIL.
   The previous dedupe patch did not actually solve this — `Patch/` runs
   *after* `Constraints/999_indexes.sql`, so it would run too late. If you
   want a safety net, the dedupe must be a **pre-deploy data migration run
   ahead of this change**, not a `Patch/` file. I reverted the patch as
   "other changes"; let me know if you want it reinstated as a pre-deploy
   step.
2. **Case sensitivity.** These indexes are case-**sensitive** (`'Pump'` vs
   `'pump'` are distinct), matching the plan's default assumption. If
   case-insensitive uniqueness is wanted, the indexes need to be on
   `LOWER("Name")`.
3. **Uniqueness scope.** Per-project plus natural parent scope, as the plan
   assumed (AssetType/SystemType → CommissioningWorkflowId, Asset →
   ParentAssetId, CommissioningSystem → SystemTypeId, CommissioningWorkflow
   → project only). Confirm this matches intent.