summary: 'Enforce uniqueness of "Name" per entity scope on five existing project-scoped xyz tables: AssetType, SystemType, Asset, CommissioningSystem (the "System" in the spec), and CommissioningWorkflow (the "Workflow" in the spec). Uniqueness is scoped per project (ProjectShardId) and, where the entity has a natural parent scope, per that parent. Duplicates must be cleaned up before the unique constraint is applied. No new tables are introduced. ' target_service: multiple postgres_changes: - kind: clarification note: "Spec entities map to actual schema tables as follows:\n - AssetType \ \ -> xyz.\"AssetType\" (scope: ProjectShardId, CommissioningWorkflowId)\n\ \ - SystemType -> xyz.\"SystemType\" (scope: ProjectShardId,\ \ CommissioningWorkflowId)\n - Asset -> xyz.\"Asset\" \ \ (scope: ProjectShardId, ParentAssetId nullable)\n - System ->\ \ xyz.\"CommissioningSystem\" (scope: ProjectShardId, SystemTypeId)\n - Workflow\ \ -> xyz.\"CommissioningWorkflow\"(scope: ProjectShardId)\nAll five are\ \ existing project-scoped tables (Asset/CommissioningSystem have\nsoft-delete\ \ via IsDeleted). No new tables, no new columns.\n" - kind: data_cleanup_patch schema: xyz file_intent: Patch/NNN_patch_dedupe_entity_names.sql note: "One-time backfill to resolve pre-existing duplicate names BEFORE the unique\n\ constraints are added (constraints will fail to apply otherwise). For each\ntable,\ \ detect rows that collide on the proposed unique key and rename the\nlater-inserted\ \ duplicates by appending a disambiguator (e.g. \" (2)\", \" (3)\")\nbased on\ \ Id ordering. For soft-deletable tables (Asset, CommissioningSystem)\nonly consider\ \ rows WHERE \"IsDeleted\" = FALSE, since the unique constraints\nbelow are partial\ \ on IsDeleted = FALSE. The specialist must:\n - run the cleanup deterministically\ \ (order by \"Id\"),\n - log each renamed row's old/new name for audit,\n -\ \ place the file in Patch/ with the next numeric prefix.\nNOTE: a unique CONSTRAINT\ \ cannot be partial; partial uniqueness is expressed\nvia a partial UNIQUE INDEX\ \ (see index_changes). The Postgres specialist owns\nwhether full-table UNIQUE\ \ constraint (non-soft-delete tables) vs partial\nUNIQUE index (soft-delete tables)\ \ is used, per the index_changes entries.\n" - kind: amend_procedure schema: xyz object: usp_InsertAssetType note: 'Amend (or create if absent) the insert/upsert proc for AssetType so it raises a clear unique-violation error (mapped to 409 by API2) when a name collides within (ProjectShardId, CommissioningWorkflowId). Resolve shard id via xyz."fn_GetProjectShardId"(_projectId). Standard DbException EXCEPTION block. Cover the FULL column set the entity exposes on write. ' - kind: amend_procedure schema: xyz object: usp_InsertSystemType note: 'Same treatment for SystemType, uniqueness within (ProjectShardId, CommissioningWorkflowId). ' - kind: amend_procedure schema: xyz object: usp_InsertAsset note: 'Same treatment for Asset. Uniqueness scoped per project; if ParentAssetId is part of the scope decision (see risks/open questions), include it. Only enforce among non-deleted rows (IsDeleted = FALSE). Cover the full Asset write column set including audit + soft-delete columns. ' - kind: amend_procedure schema: xyz object: usp_UpdateAsset note: 'Asset is mutable; the update proc must also surface the unique violation on rename. Amend the GENERAL update proc to cover Asset''s full mutable column set so the rename path and other field updates remain consistent. ' - kind: amend_procedure schema: xyz object: usp_InsertCommissioningSystem note: 'Same treatment for CommissioningSystem ("System"), uniqueness within (ProjectShardId, SystemTypeId), among non-deleted rows only. ' - kind: amend_procedure schema: xyz object: usp_UpdateCommissioningSystem note: 'CommissioningSystem is mutable; the update proc must surface the unique violation on rename. Amend the general update proc to cover the full mutable column set. ' - kind: amend_procedure schema: xyz object: usp_InsertCommissioningWorkflow note: 'Same treatment for CommissioningWorkflow ("Workflow"), uniqueness within (ProjectShardId). ' - kind: amend_procedure schema: xyz object: usp_UpdateCommissioningWorkflow note: 'CommissioningWorkflow is mutable; the update proc must surface the unique violation on rename. Amend the general update proc to cover the full mutable column set. ' - kind: note note: 'The specialist should confirm the exact existing proc names by inspecting Database/xyz/Procedures/. If a given entity is currently inserted/updated via a differently-named general proc, amend THAT proc rather than creating a parallel one. The DB-level unique enforcement (index_changes below) is the authoritative guard; the proc changes provide a clean, mapped error. ' citus_changes: none index_changes: none reporting_changes: none seed_data_changes: none mongo_changes: none api2_changes: - kind: error_mapping services: - asset.types.service.ts - system.types.service.ts - assets.service.ts - commissioning.systems.service.ts - commissioning.workflows.service.ts notes: "For each of the five entity write paths (create + update where applicable),\n\ catch the DB unique-violation (DB_ConstraintViolationError / SQLSTATE 23505\n\ or the explicit RAISE from the amended procs) and map it to a 409 Conflict\nusing\ \ a typed error from src/types/errortypes.ts (e.g. a Conflict/duplicate\nerror),\ \ with a clear message naming the entity and the colliding name.\nDo NOT add inline\ \ SQL \u2014 continue to call the existing usp_* procedures via\nthe existing\ \ const query strings. The actual service filenames must be\nconfirmed against\ \ the repo; the names above are the conventional lowercase\ndot-separated forms.\n" db_calls: - usp_InsertAssetType - usp_InsertSystemType - usp_InsertAsset - usp_UpdateAsset - usp_InsertCommissioningSystem - usp_UpdateCommissioningSystem - usp_InsertCommissioningWorkflow - usp_UpdateCommissioningWorkflow - kind: tests_and_dtos notes: "No DTO shape changes are required (Name field already exists). However,\n\ because the write procs are amended, existing API2 unit/e2e tests, mocks,\nand\ \ fixtures for these five entities must be updated so that:\n - duplicate-name\ \ create/update returns 409,\n - non-duplicate create/update still returns the\ \ existing success codes.\nUpdate fixtures/mocks so `npm test` stays green.\n" api1_changes: none inter_service_calls: none new_permissions: none java_frozen_resources: none risks: - 'Uniqueness SCOPE is an open question in the spec. This plan assumes per-project scoping (ProjectShardId), plus a natural parent scope where one exists (AssetType/SystemType -> CommissioningWorkflowId, CommissioningSystem -> SystemTypeId, Asset -> ParentAssetId). If the team wants strictly project-global uniqueness, the index column lists must be narrowed before approval. Confirm at Gate 2. ' - 'Case-sensitivity is unspecified. This plan assumes case-SENSITIVE uniqueness (matches Postgres default text comparison). If case-insensitive is desired, the unique indexes must be on LOWER("Name") expressions and the cleanup patch must dedupe case-insensitively. Confirm at Gate 2. ' - "Pre-existing duplicate names will cause the unique index creation to FAIL. The\ \ cleanup patch (Patch/) MUST run and succeed before the index changes deploy. Deployment\ \ order in this repo runs Patch/ LAST, after Constraints/ where 999_indexes.sql\ \ lives \u2014 so the dedupe patch cannot rely on running before the index. The\ \ Postgres specialist must sequence this safely: either (a) perform the dedupe as\ \ a pre-deploy data migration ahead of the index PR, or (b) gate index creation\ \ behind verified-clean data. This ordering hazard must be resolved before merge.\n" - 'ParentAssetId is nullable. A naive unique index treats NULLs as distinct, leaving top-level assets unconstrained. The specialist must use PG16 NULLS NOT DISTINCT (or a COALESCE expression index) to enforce uniqueness among root-level assets. ' - "Partial unique indexes (WHERE IsDeleted = FALSE) cannot be expressed as UNIQUE\ \ CONSTRAINTs, only as unique INDEXes \u2014 which is why all enforcement is routed\ \ through index_changes rather than postgres_changes constraint files.\n" - 'Exact existing procedure names (usp_Insert*/usp_Update* for these entities) must be confirmed in Database/xyz/Procedures/. If an entity is written via a general/shared proc under a different name, amend that one. ' out_of_scope: - No changes to read endpoints or read procedures/functions. - No new columns added to any table; only constraint/index + proc error handling. - No case-insensitive normalization unless the open question is resolved that way at Gate 2. - No new entities or tables. testing_plan: 'Postgres: extend IntegrationTest/main.py with scenarios that (1) insert a duplicate name into each of the five tables and assert the unique index rejects it, (2) for soft-deletable tables, assert a soft-deleted row''s name can be reused, (3) for Asset, assert same name under different parents is allowed while same name under same parent is rejected, (4) verify the dedupe patch renames colliding rows deterministically. Run ./build to validate the full changelog deploys clean against a fresh DB containing seeded duplicates. API2: update existing unit and e2e tests for the five entity write paths so duplicate create/update returns 409 Conflict and valid create/update still succeeds; update mocks/fixtures so `npm test` stays green.' _meta: model: claude-opus-4-8 atom_ids: - rule.architect_rules - convention.postgres - convention.api2 - convention.api1-hc-iam - convention.api1-hc-project - convention.api1-hc-bpm - convention.citus - endpoint.api2.put__api_v2_projects__projectId__issues_activity-categories__issueId__link - endpoint.api2.post__api_v2_projects__projectId__coordinates - mongo.embedded.SyncDataErrorRecord - endpoint.api2.post__api_v2_projects__projectId__activities_categories - endpoint.api2.put__api_v2_projects__projectId__models_folders__folderId__rename - pg.reporting.CalculationMethod - pg.reporting.ProgressOutput - pg.reporting.ProjectCalculationMethod - pg.reporting.ProjectPerformanceSnapshot - pg.reporting.ProjectProgress - pg.staging.DuplicatedMigratedMongoElement spec: title: Enforce unique Name on 5 entity tables project_kind: modify user_facing_behavior: Creating or updating an AssetType, SystemType, Asset, System, or Workflow with a name that already exists (for the same scope) will be rejected with a clear error. Names must be unique per entity type. data_touched: - AssetType - SystemType - Asset - System - Workflow api_surface: write non_functional_requirements: - Uniqueness constraint enforced at the database level - API returns a meaningful error (e.g. 409 Conflict) when a duplicate name is submitted - Existing duplicate names (if any) must be resolved before the constraint is applied out_of_scope: - "Case-insensitivity not specified \u2014 assumed case-sensitive unless architect\ \ decides otherwise" - No changes to read endpoints open_questions: - Is uniqueness global or scoped (e.g. per project, per tenant, per parent entity)? For example, can two different projects have an Asset with the same name? - Are there existing duplicate names in any of these tables that need a migration/cleanup strategy? - Should uniqueness be case-insensitive (e.g. 'Pump' and 'pump' treated as the same)?