# Specialist Report — `agent-feature` (UNIQUE constraint on `Name`)

**Spec:** `docs/specs/20260630-093618-agent-feature.md`
**Status:** No code changes required in this repository (`platform-api` / api2).
**Date:** 2026-06-30

---

## TL;DR

The spec asks for a **database-level `UNIQUE` constraint on the `Name` column** of
`xyz."Asset"` and `xyz."CommissioningSystem"` (each scoped within its own project
shard), preceded by a **backfill patch** to dedupe any pre-existing `Name` values.

The spec is explicit and repeated that this is a **Postgres-only schema change with
no API surface change**. After a full investigation, **this change does not belong in
the api2 repository** — the schema/migration source lives in a separate database repo.
I made **no production code changes** here. Doing otherwise would have contradicted the
spec and fabricated scope.

---

## Files changed

| File | Change |
| --- | --- |
| `docs/specs/20260630-093618-agent-feature.md` | Added a **Decisions** section recording why no api2 changes were made and where the change belongs. |
| `_SPECIALIST_REPORT.md` | This report (new). |

**No** changes to `src/`, `test/`, routes, controllers, validators, services, or
swagger — by design.

---

## Why no api2 changes (evidence)

1. **The spec forbids it.** Goal: *"This is a Postgres-only schema change plus a
   backfill patch… No new tables, no API surface change."* The plan's API2 changes are
   verbatim `none`. Out of scope explicitly lists *"API2 / API1 runtime changes,
   validation messages, or error-handling changes."* The `files:` frontmatter is empty
   (`# (none listed)`).

2. **The target tables don't exist in this repo.** `grep` for `Asset` /
   `CommissioningSystem` (case-insensitive) across `src/` and `test/` returns **no**
   table references. The only related surface is the commissioning *workflows* and
   *system-types* endpoints, which are unrelated to the `Asset` /
   `CommissioningSystem` operational tables in the spec.

3. **The migration source lives in a different repository.** `docker/docker-compose.yml`
   mounts the schema from `${DATABASE_REPO_PATH}`:
   ```yaml
   - ${DATABASE_REPO_PATH}/PostgreSQLDatabase:/liquibase/changelog/PostgreSQLDatabase
   - ${DATABASE_REPO_PATH}/CitusDistributionLogic:/liquibase/changelog/Distributed
   ```
   The local changelogs (`docker/distributed_changelog_local.xml`,
   `docker/patch_changelog_local.xml`) only `includeAll` paths under that mounted
   `PostgreSQLDatabase/Database/...` tree — none of which is checked into api2. The
   `xyz."<table>"/Constraints` and backfill `Patch/*.sql` files therefore must be
   authored in the **database repository**, not here.

4. **The e2e seed dump doesn't touch these tables either.**
   `docker/test-data-dump/e2e-test-data-dump.sql` seeds ~50 `xyz.*` tables but neither
   `Asset` nor `CommissioningSystem`, so there is no api2-side fixture to deduplicate or
   guard against the new constraint.

Because enforcement is explicitly required at the **database** level (and app-layer
validation is out of scope), there is no faithful api2 edit to make.

---

## Recommended implementation (for the database-repo owner)

This is a hand-off, **not** applied here. Two ordered Liquibase change sets per table,
mirroring the existing `PostgreSQLDatabase/Database/Patch/*.sql` + `xyz/Constraints`
layout.

### 1. Backfill patch — resolve duplicate `Name` values first

Run as a `Patch/NNN_patch_dedupe_<table>_name.sql` change set **before** the constraint.
Disambiguate duplicates deterministically (keep oldest, suffix the rest). Adjust the
shard/scope column and tie-break column to the real schema:

```sql
-- Within each project shard, suffix duplicate Names so the UNIQUE constraint can apply.
WITH ranked AS (
  SELECT ctid,
         ROW_NUMBER() OVER (
           PARTITION BY "<projectShardColumn>", "Name"
           ORDER BY "InsertedOn", ctid          -- keep the earliest row unchanged
         ) AS rn
  FROM xyz."Asset"
)
UPDATE xyz."Asset" a
SET "Name" = a."Name" || ' (' || r.rn || ')'
FROM ranked r
WHERE a.ctid = r.ctid
  AND r.rn > 1;
-- Repeat the same block for xyz."CommissioningSystem".
```

### 2. UNIQUE constraint — include the Citus distribution column

Both tables are project-scoped and **Citus-distributed**, so a UNIQUE constraint must
include the distribution (shard) column — hence "scoped within its own project shard"
in the spec. As a `xyz/Constraints/*.sql` change set:

```sql
ALTER TABLE xyz."Asset"
  ADD CONSTRAINT "UQ_Asset_ProjectShard_Name"
  UNIQUE ("<projectShardColumn>", "Name");

ALTER TABLE xyz."CommissioningSystem"
  ADD CONSTRAINT "UQ_CommissioningSystem_ProjectShard_Name"
  UNIQUE ("<projectShardColumn>", "Name");
```

Wrap each in a Liquibase `<changeSet>` with a matching `<rollback>` (drop constraint),
following the existing change sets in `distributed_changelog_local.xml` /
`patch_changelog_local.xml`.

---

## Open questions for the human reviewer

1. **Confirm the change repo.** Please confirm the constraint + backfill should be
   authored in the database repository (`${DATABASE_REPO_PATH}` →
   `PostgreSQLDatabase/Database/...`). If the intent was actually to enforce uniqueness
   in api2 (validator/service), the spec must be amended — it currently lists that as
   out of scope.
2. **Distribution/shard column name.** What is the real distribution column on
   `xyz."Asset"` and `xyz."CommissioningSystem"` (e.g. `"ProjectId"`)? The UNIQUE
   constraint must include it for Citus. The SQL above uses a `<projectShardColumn>`
   placeholder.
3. **Case sensitivity.** Spec defers case-insensitive uniqueness unless data analysis
   requires it. If two `Name`s differing only by case must collide, switch to a unique
   functional index on `(<projectShardColumn>, lower("Name"))` instead.
4. **Dedupe tie-break + rename strategy.** The backfill above keeps the earliest row by
   `InsertedOn` and suffixes the rest with ` (n)`. Confirm the tie-break column exists
   and that auto-renaming duplicates (vs. failing for manual cleanup) is acceptable.

---

## Commands run

None. Per the task instructions, no toolchain hunt or build/test run was performed, and
there are no api2 code changes to compile or test. (The change set for the database repo
would be validated by that repo's Liquibase pipeline.)