# Postgres Specialist Report — Unique Name constraint on 4 tables

**This change file is AI-generated.** All additions were authored under the
changeset author `agentneo` (the existing `999_indexes.sql` changeset is
unchanged and retains its original author `davewebb`).

## Revision context — "why no change?"

The first run produced **no change**, and that was technically correct under
the hard rule "when `index_changes` is none/empty, do nothing." The plan's
top-level `index_changes:` field is literally `none`.

However, the plan is **self-contradictory**: its `summary` and all four
`postgres_changes` notes state that the *entire* deliverable is four unique
indexes, and one note explicitly says "The Postgres specialist appends the
human-ticked unique indexes to `Database/xyz/Constraints/999_indexes.sql`."
The architect described the indexes in full (tables, columns, partial-index
WHERE clauses) but failed to mechanically populate the `index_changes` list —
so the "do nothing" outcome was an artifact of that omission, not a deliberate
Gate-2 rejection.

The reviewer's feedback **"why no change?"** is a direct human instruction
resolving the contradiction in favour of implementing the indexes. Because the
notes specify them exactly, this is implementing the documented spec, not
inventing indexes. The four indexes are now added.

## Files modified

### `Database/xyz/Constraints/999_indexes.sql`
Appended four `CREATE UNIQUE INDEX IF NOT EXISTS` statements, each grouped
under a `-- xyz.<Table>` header comment per the file's existing convention.
The file's existing changeset (`runOnChange:true`, `endDelimiter:;`) covers the
new statements — no new file and no new changeset header was created
(per the rule: never create a separate file for indexes).

| Table | Index name | Columns | Filter |
|-------|-----------|---------|--------|
| `xyz.AssetType` | `AssetType_Name_key` | `("ProjectShardId", "Name")` | none |
| `xyz.SystemType` | `SystemType_Name_key` | `("ProjectShardId", "Name")` | none |
| `xyz.Asset` | `Asset_Name_key` | `("ProjectShardId", "Name")` | `WHERE "IsDeleted" = FALSE` |
| `xyz.CommissioningSystem` | `CommissioningSystem_Name_key` | `("ProjectShardId", "Name")` | `WHERE "IsDeleted" = FALSE` |

No files added.

## Design decisions (taken verbatim from the plan notes)

- **Per-project scope:** all four tables have a composite PK
  `("ProjectShardId", "<Table>Id")`. Each unique index leads with
  `"ProjectShardId"`, satisfying Citus's single-shard uniqueness-enforcement
  requirement (a global unique index on `Name` alone is not enforceable on a
  distributed table).
- **Case-sensitive:** plain `"Name"` column, no `LOWER()` / citext.
- **Partial indexes for soft-deletable tables:** `xyz.Asset` and
  `xyz.CommissioningSystem` have an `"IsDeleted" BOOLEAN` column; their unique
  indexes are filtered `WHERE "IsDeleted" = FALSE` so a Name can be re-used
  after a soft-delete. `xyz.AssetType` and `xyz.SystemType` have no
  `IsDeleted` column (verified in the table files), so theirs are unfiltered.

## Column verification

Confirmed against the table-create files:
- `118_xyz_asset_type.sql` — `ProjectShardId`, `Name`; no `IsDeleted`.
- `119_xyz_system_type.sql` — `ProjectShardId`, `Name`; no `IsDeleted`.
- `129_xyz_asset.sql` — `ProjectShardId`, `Name`, `IsDeleted BOOLEAN NOT NULL DEFAULT FALSE`.
- `130_xyz_commissioning_system.sql` — `ProjectShardId`, `Name`, `IsDeleted BOOLEAN NOT NULL DEFAULT FALSE`.

Naming follows CLAUDE.md's unique convention `"{Table}_{Column}_key"`.

## Verification status

- `./build` — **not run.** No `./build` script is present in the repo and
  Docker is unavailable in this environment, so a from-scratch changelog
  deploy could not be performed. Static review only.
- `./test` — present but requires a running Postgres on :5490 / Docker; **not run.**
- Static review: index names are unique within the file, all referenced
  tables/columns exist, `IF NOT EXISTS` makes the statements idempotent under
  `runOnChange:true`, and partial-index predicates reference real columns.

## Deviations from the plan

- The plan's `index_changes` is `none`; per the reviewer instruction these four
  unique indexes were nonetheless implemented exactly as described in the
  plan's `postgres_changes` notes. This deviation is the explicit subject of
  the revision request.

## Open questions / risks for the human reviewer

1. **Pre-existing duplicates (deploy-time failure risk).** `CREATE UNIQUE INDEX`
   will FAIL if any project already has two constrained rows sharing a `Name`
   (live rows only for `Asset` / `CommissioningSystem`). This could not be
   checked here (no DB access). Before deploying, run per table:
   ```sql
   SELECT "ProjectShardId", "Name", COUNT(*)
   FROM xyz."AssetType"
   GROUP BY "ProjectShardId", "Name" HAVING COUNT(*) > 1;
   ```
   (analogous for `SystemType`; add `WHERE "IsDeleted" = FALSE` for `Asset`
   and `CommissioningSystem`). If any rows return, a Patch/ cleanup or manual
   dedup is required first — do not silently dedup.
2. **"System" → `CommissioningSystem` mapping.** The spec's "System" table maps
   to `xyz.CommissioningSystem`. Confirm this is the intended table.
3. **API 409/422 shaping is out of scope.** A duplicate insert/update will now
   raise a DB unique-violation; the friendly 409/422 response is a separate
   API2 change not done here.