▸ AGENT NEO: BACKEND SOFTWARE DEVELOPER

deviation specialist:postgres

**No true DB-level constraint.** Direct SQL / bulk loads / other code paths that bypass these procs can still create duplicates. Only the `usp_*` procs enforce the rule.

Accept

Dismiss

deviation specialist:postgres

**TOCTOU race under concurrency.** Two concurrent inserts of the same name can both pass the `IF EXISTS` check and both commit. A unique index is the only race-free fix. If the team wants real enforcement, re-enable the relevant entries in `index_changes` (partial `UNIQUE INDEX ... WHERE "IsDeleted" = FALSE` for Asset/CommissioningSystem; `NULLS NOT DISTINCT` for Asset's nullable `ParentAssetId`) — that is a Gate-2 decision, not mine.

Accept

Dismiss

deviation specialist:postgres

**Ordering hazard (plan Risk 3) is now moot.** It only existed because Patch runs after Constraints; with no index created, the dedupe Patch ordering no longer matters.

Accept

Dismiss

deviation specialist:postgres

**Dedupe Patch rationale changed.** Its stated precondition ("constraints will fail to apply otherwise") no longer holds. I kept it because it still makes existing data consistent with the new rule and is required by the plan as an explicit deliverable. It is non-destructive beyond renaming colliding rows with a ` (n)` suffix and logs every rename via `RAISE NOTICE`.

Accept

Dismiss

open_question specialist:postgres

**Re-enable unique indexes?** With `index_changes: none`, enforcement is proc-only (racy, bypassable). Confirm this is the intended trade-off, or re-add the partial unique indexes at Gate 2 for real DB-level enforcement.

Accept

Dismiss

open_question specialist:postgres

**Case-sensitivity** and **exact scope** (per-parent vs project-global) are still flagged as open in the plan — current impl is case-sensitive + per-parent/per-type. Confirm.

Accept

Dismiss

open_question specialist:postgres

**Dedupe Patch** renames live data with a ` (n)` suffix; acceptable? It now runs without a following constraint, so duplicates could reappear via non-proc paths until indexes exist.

Accept

Dismiss

open_question specialist:postgres

Should `usp_UpdateAsset`/`usp_UpdateCommissioningSystem` allow setting a nullable FK (`ParentAssetId`) back to NULL? Current COALESCE convention does not. ```

Accept

Dismiss

deviation specialist:api2

**New API2 surface created.** The spec's frontmatter `files:` list was empty and its `services:` names were "conventional" placeholders; the entities did not exist. Following the override instruction, the full write surface was created rather than amending non-existent files. The spec's "no new entities/tables" refers to the **database**; no DB tables were added here.

Accept

Dismiss

deviation specialist:api2

**Single-object creates** (not bulk). Sibling `categoryTypes` does bulk JSON inserts, but the spec frames uniqueness per single Name and the update paths are single-entity, so single-object create keeps create/update symmetric and the 409 message able to name the colliding entity.

Accept

Dismiss

deviation specialist:api2

**e2e scope.** 201/204/409 happy + conflict paths need the DB proc/constraint migration (DB-side, out of repo). e2e specs cover the 400/403 paths that run before the DB; 201/409 logic is covered by service unit tests stubbing `query`.

Accept

Dismiss

open_question specialist:api2

**Proc signatures.** The `usp_*` procs do not exist in this repo, so parameter order was modelled on sibling conventions (`(projectId, [parentId,] name, description, user)` for inserts; `(projectId, entityId, name, description, user)` for updates). Confirm these match the actual amended procs / DB migration once that lands, and adjust the `SQL_QUERIES` argument arrays if they differ.

Accept

Dismiss

open_question specialist:api2

**Case-insensitivity.** Spec out-of-scope item leaves normalization to "Gate 2". No case-folding is applied; uniqueness is whatever the DB constraint enforces.

Accept

Dismiss

open_question specialist:api2

**Authority.** Writes are guarded by `PROJECT_EDIT`. If a dedicated asset/commissioning authority is intended, add it to `src/api/auth/authorities.types.ts` and swap it into the routes.

Accept

Dismiss

open_question specialist:api2

**Build/tests not executed.** `node_modules` is not installed and the task forbids installing the toolchain. `npx tsc --noEmit`, `npm test`, and the e2e suite were **not run** locally — the pipeline runs them afterward. The edited swagger JSON was validated as parseable via `node`.

Accept

Dismiss

open_question specialist:postgres

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

Accept

Dismiss

open_question specialist:postgres

**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")`.

Accept

Dismiss

open_question specialist:postgres

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

Accept

Dismiss

open_question specialist:api2

**Confirm api2 should ship with no changes.** This revision leaves api2 untouched. If/when you do want the API surface (write endpoints + 409 mapping), the previous implementation can be reinstated from git history.

Accept

Dismiss

open_question specialist:api2

**Pre-existing duplicate data.** Creating the unique indexes will fail if any environment already holds colliding names. The Postgres specialist's report flags that the dedupe must run as a pre-deploy migration (a `Patch/` file runs too late). No api2 action needed, but worth tracking before deploy.

Accept

Dismiss

open_question specialist:api2

**Case sensitivity / scope** of the indexes are as the Postgres specialist documented (case-sensitive; per-project + natural parent scope). Confirm that matches intent before the eventual API is built on top of it.

Accept

Dismiss