# Postgres Specialist Report — Unique Name on Asset & CommissioningSystem

**AI-generated change.** All SQL/DDL in this change was authored by the agent.
The Liquibase author for the changeset touched here is `agentneo` (the
`999_indexes` changeset header is pre-existing and owned by `davewebb`; per repo
convention indexes are *appended* to that single shared changeset rather than
introducing a new one — see "Deviations" below).

## Summary

The plan's `postgres_changes` asks for two `unique_constraint` changes: `Name`
unique per project on `xyz."Asset"` and `xyz."CommissioningSystem"`. Both tables
are project-scoped, Citus-distributed by `ProjectShardId`, and carry an
`IsDeleted` soft-delete column. Per the plan's notes the uniqueness is enforced
as a **partial unique index** on `("ProjectShardId", "Name") WHERE "IsDeleted" =
FALSE` so that:

- the distribution column (`ProjectShardId`) leads the key (Citus requirement),
- a soft-deleted row's `Name` can be reused, and
- uniqueness is scoped *within a project*, not globally (see open questions).

A hard `ALTER TABLE ADD CONSTRAINT UNIQUE` (as used by the sibling files
`081_..._commissioning_workflow_name_unique.sql` / `082_..._system_type_name_unique.sql`)
**cannot** be used here, because a table constraint cannot be made partial on
`IsDeleted`. A partial unique index is the only correct Postgres mechanism.

## Files modified

### `Database/xyz/Constraints/999_indexes.sql`
Appended two grouped sections (`-- xyz.Asset`, `-- xyz.CommissioningSystem`).
Each section contains:
1. An idempotent, one-time **dedupe** `UPDATE` that renames any pre-existing
   colliding non-deleted `Name`s by appending the row's id
   (`AssetId` / `CommissioningSystemId`), keeping the earliest row
   (`ROW_NUMBER() ... ORDER BY "InsertedOn", <id>`) unchanged. Scoped to
   `IsDeleted = FALSE` rows only.
2. The partial `CREATE UNIQUE INDEX IF NOT EXISTS`:
   - `"Asset_ProjectShardId_Name_key"` on `xyz."Asset" ("ProjectShardId","Name") WHERE "IsDeleted" = FALSE`
   - `"CommissioningSystem_ProjectShardId_Name_key"` on `xyz."CommissioningSystem" ("ProjectShardId","Name") WHERE "IsDeleted" = FALSE`

Index names follow the `{Table}_{Columns}_key` convention used by the sibling
unique enforcers (081/082). There is existing precedent in this file for a
partial unique index (`ModelElementActivityMapping_UniqueActiveMapping`).

### `IntegrationTest/` (testing_plan)
- `IntegrationTest/src/name_unique/__init__.py` (new, empty package marker)
- `IntegrationTest/src/name_unique/scenario.py` (new) — seeds FK parents
  (`CommissioningWorkflow` → `AssetType`/`SystemType`) in two distinct shards,
  then asserts, for **both** tables:
  - (a) duplicate `Name` in the **same** shard is rejected (`UniqueViolation`);
  - (b) the same `Name` in a **different** shard succeeds (per-project scope);
  - (c) soft-delete then re-insert of the same `Name` succeeds (partial index).
- `IntegrationTest/main.py` — imports and runs `assert_name_unique_indexes`
  against two freshly-created projects.

## Numeric prefixes chosen
No new numeric-prefixed file was created. Indexes (including unique indexes used
for constraint enforcement) **must** go in `Database/xyz/Constraints/999_indexes.sql`
per CLAUDE.md ("Indexes are not constraints — they always go in 999_indexes.sql")
and the task brief ("never create a new file for indexes"). `999_` already sorts
last in the Constraints phase, which is correct (the dedupe + index must run
after the table-create phase).

## Deployment ordering (important)
`Constraints/` runs **before** `Patch/` in `non_distributed_changelog.xml`.
Therefore the dedupe **cannot** live in `Patch/` — the index in `Constraints/`
would build first and fail on any existing duplicates. This is exactly the
plan's *preferred option (b)*: "dedupe within the constraint changeset … so the
partial unique index applies cleanly in one deploy." The dedupe `UPDATE`
therefore sits immediately before each `CREATE UNIQUE INDEX` in the same file.
No separate `Database/Patch/NNN_patch_dedupe_...sql` file was created (that was
the rejected option (a)).

## Project-scoped table checklist
No new tables were created. For the two existing tables touched:
- Uniqueness key **leads with `ProjectShardId`** (Citus distribution column). ✔
- Enforcement lives in `Database/xyz/Constraints/`, never in the table-create
  file (submodule/Citus compatibility). ✔
- Soft-delete (`IsDeleted`) respected via the partial predicate. ✔

## Column coverage / API impact
No columns were added, removed, or renamed, so no read/write procedures or
functions required amendment. **No API2 DTO/fixture changes are implied by this
change** — the only behavioural difference is that a duplicate `Name` write
(within a project, among non-deleted rows) is now rejected at the DB level. The
API2 specialist may want a friendly error mapping for the unique-violation, but
the plan lists `api2_changes: none` and that is out of scope here.

## Verification
- `python3 -m py_compile` on the new/edited Python: **clean**.
- `./build`: **not run** — no `build` script exists in this repo (only
  `diagram` and `test`) and Docker is unavailable in this environment.
- `./test` / `./test --no-docker`: **not run** — Docker unavailable and no
  Postgres listening on 5490.
- Static review performed: SQL identifiers verified against the table-create
  files (`129_xyz_asset.sql`, `130_xyz_commissioning_system.sql`); the
  `WITH … UPDATE … FROM …` dedupe and partial `CREATE UNIQUE INDEX` are standard
  Postgres and mirror the existing partial-unique-index precedent in the file.

## Deviations from the plan
1. **`index_changes: none` vs. implementing a unique index.** The plan's
   `index_changes` list (the Gate-2 opt-in *performance* index proposals) is
   empty, and I added **zero** performance indexes. The two unique indexes I did
   add come from `postgres_changes` (`kind: unique_constraint`), whose own notes
   explicitly direct: "implement the uniqueness as the partial unique index in
   999_indexes.sql." These are constraint-enforcing indexes, a distinct concept
   from the performance-index opt-in list. Flagging for the reviewer's awareness.
2. **Dedupe placed in the constraint changeset, not in `Patch/`.** This is the
   plan's stated *preferred* approach (b), chosen over (a) for the ordering
   reason above. The `Patch/` filename referenced in the plan was intentionally
   not created.

## Open questions for the human reviewer (carried from `_PLAN.yaml`)
1. **Scope** — uniqueness is enforced **per project** (`ProjectShardId, Name`),
   not globally per table. A truly cross-project unique `Name` is not
   enforceable on a Citus-distributed table. Confirm per-project scope is the
   intended operational model.
2. **Soft-delete** — soft-deleted rows are excluded, so a deleted asset/system's
   `Name` can be reused. If deleted names must also stay unique, switch to a full
   (non-partial) `("ProjectShardId","Name")` unique constraint.
3. **Case-sensitivity** — case-sensitive (Postgres default). For
   case-insensitive uniqueness, the index would be on
   `("ProjectShardId", LOWER("Name"))`.
4. **Auto-rename of duplicates** — the dedupe renames colliding non-deleted rows
   by appending their id. This mutates display names. Confirm the team accepts
   automatic renaming, or resolve duplicates manually before deploy.