summary: 'Add a database-level UNIQUE constraint on the Name column for both xyz."Asset" and xyz."CommissioningSystem" (each scoped within its own project shard, since both are project-scoped operational tables). This is a Postgres-only schema change plus a backfill patch to resolve any pre-existing duplicate Name values before the constraint applies. No new tables, no API surface change. ' target_service: api2 postgres_changes: - kind: unique_constraint schema: xyz table: Asset note: "Both Asset and CommissioningSystem are EXISTING project-scoped\ntables (pre-May-2026\ \ create files are non-editable). Per repo\nconvention, UNIQUE constraints live\ \ in\nDatabase/xyz/Constraints/, NOT in the table-create file (required\nfor Citus\ \ distributed-deployment compatibility). Add the unique\nconstraint as a new constraint\ \ file (continuing the numeric\nprefix scheme in Database/xyz/Constraints/, e.g.\n\ NNN_xyz_asset_name_unique.sql), runOnChange:true.\n\nSCOPING DECISION: Asset is\ \ project-scoped and distributed by\nProjectShardId. A UNIQUE constraint on a\ \ Citus distributed table\nMUST include the distribution column (ProjectShardId)\ \ \u2014 a\nglobally-unique constraint that omits the shard key cannot be\nenforced\ \ across shards in production. Therefore the uniqueness is\nenforced on (ProjectShardId,\ \ Name): Name is unique WITHIN a\nproject. This matches the open question on scoping\ \ and the Citus\nrequirement.\n\nAsset also has soft-delete (IsDeleted). A plain\ \ UNIQUE on\n(ProjectShardId, Name) would block re-using the name of a\nsoft-deleted\ \ asset. Recommend a partial unique index instead\n(see index_changes) WHERE \"\ IsDeleted\" = false, rather than a hard\ntable constraint, so soft-deleted rows\ \ don't collide. The Postgres\nspecialist should implement the uniqueness as the\ \ partial unique\nindex in 999_indexes.sql (proposed in index_changes) and NOT\ \ add a\nblanket ALTER TABLE ADD CONSTRAINT UNIQUE that ignores IsDeleted.\n" columns: - ProjectShardId - Name - kind: unique_constraint schema: xyz table: CommissioningSystem note: 'CommissioningSystem is project-scoped, distributed by ProjectShardId, and ALSO has soft-delete (IsDeleted). Same treatment as Asset: enforce uniqueness on (ProjectShardId, Name) via a partial unique index WHERE "IsDeleted" = false. Implement in Database/xyz/Constraints/999_indexes.sql (proposed in index_changes). The unique key MUST include ProjectShardId (Citus distribution-column requirement). ' columns: - ProjectShardId - Name - kind: patch schema: xyz file: Database/Patch/NNN_patch_dedupe_asset_commissioningsystem_name.sql note: 'One-time backfill that MUST run before the unique indexes are applied in the deployment so the index creation does not fail on existing duplicates. Because Patch/ runs LAST in the non_distributed_changelog (after Constraints), a unique index that collides with pre-existing duplicates would fail to create on the Constraints pass. The Postgres specialist must coordinate this: either (a) run a dedupe Patch as a prerequisite deploy step and add the index in a SUBSEQUENT release, or (b) implement the dedupe inside the same changeset that creates the index using a preConditions/SQL block that renames duplicates prior to index creation. Preferred approach: dedupe within the constraint changeset (rename colliding non-deleted rows by appending a disambiguator suffix derived from AssetId/CommissioningSystemId) so the partial unique index applies cleanly in one deploy. Scope dedupe per (ProjectShardId, Name) among non-soft-deleted rows only. ' citus_changes: none index_changes: none reporting_changes: none seed_data_changes: none mongo_changes: none api2_changes: none api1_changes: none inter_service_calls: none new_permissions: none java_frozen_resources: none risks: - "SCOPING AMBIGUITY (open question): The spec asks for uniqueness\n\"across all rows\ \ in the table\" (global per table). However, both\nAsset and CommissioningSystem\ \ are Citus-distributed by\nProjectShardId, and a unique constraint/index on a distributed\n\ table MUST include the distribution column. A truly global\n(cross-project) unique\ \ constraint on Name alone is NOT enforceable\nin the production Citus deployment.\ \ This plan therefore scopes\nuniqueness to (ProjectShardId, Name) \u2014 unique\ \ WITHIN a project.\nConfirm this is acceptable; a cross-project global uniqueness\ \ would\nrequire a different (non-distributed) enforcement strategy and is\nlikely\ \ not what the operational model wants.\n" - "SOFT-DELETE INTERACTION: Both tables have IsDeleted. A hard UNIQUE\nconstraint\ \ would prevent reusing the Name of a soft-deleted record.\nThis plan uses a PARTIAL\ \ unique index (WHERE \"IsDeleted\" = false).\nConfirm the desired behavior \u2014\ \ if soft-deleted names must also stay\nunique, switch to a full (ProjectShardId,\ \ Name) unique constraint.\n" - "PRE-EXISTING DUPLICATES (open question): There may be existing\nduplicate Name\ \ values per project in either table. The unique index\ncreation WILL FAIL if collisions\ \ remain. A dedupe Patch must run\n(and be verified) before/alongside index creation.\ \ The renaming\nstrategy (suffixing colliding names) is destructive to display\n\ names \u2014 confirm the team is comfortable auto-renaming, or have the\nduplicates\ \ resolved manually first.\n" - 'CASE-SENSITIVITY (open question): This plan uses case-sensitive uniqueness (default Postgres). If case-insensitive uniqueness is required, the index should be on (ProjectShardId, LOWER("Name")). Left as case-sensitive per spec default; confirm if the existing data implies otherwise. ' - 'These tables are EXISTING (pre-May-2026). The constraint/index goes in Database/xyz/Constraints/, never into the original table-create file, per repo convention and Citus submodule compatibility. ' out_of_scope: - Case-insensitive uniqueness (unless data analysis at implementation reveals it's required). - Any column/table changes beyond adding the unique enforcement on Name. - API2 / API1 runtime changes, validation messages, or error-handling changes. - No new tables, so no Citus distribution changes are needed. testing_plan: "- Postgres specialist: extend the IntegrationTest harness\n (IntegrationTest/main.py)\ \ with a scenario that (a) inserts two rows\n with the same Name and same ProjectShardId\ \ into xyz.\"Asset\" and\n asserts the second insert is rejected once the partial\ \ unique index\n exists; (b) inserts two rows with the same Name in DIFFERENT\n\ \ ProjectShardId values and asserts BOTH succeed (per-project scope);\n (c) soft-deletes\ \ a row then re-inserts the same Name and asserts it\n succeeds (partial index\ \ behavior). Repeat for\n xyz.\"CommissioningSystem\".\n- Verify the dedupe Patch\ \ runs cleanly against a dataset seeded with\n intentional duplicates and that\ \ the unique index then creates\n without error.\n- Run ./build to confirm the\ \ full changelog deploys clean on a fresh\n DB, and ./test for the integration\ \ scenarios.\n- No API2/API1 code changes, so no application test changes are\n\ \ required; existing tests must remain green." _meta: model: claude-opus-4-8 atom_ids: - rule.architect_rules - convention.api2 - convention.postgres - convention.citus - convention.api1-hc-iam - endpoint.api2.post__api_v2_projects - convention.api1-hc-bpm - convention.api1-hc-project - endpoint.api2.put__api_v2_projects__projectId__models_folders__folderId__rename - endpoint.api2.put__api_v2_projects__projectId__issues_activity-categories__issueId__link - endpoint.api2.post__api_v2_projects_create-project - endpoint.api2.post__api_v2_projects__projectId__models__modelId__move - pg.reporting.CalculationMethod - pg.reporting.ProgressOutput - pg.reporting.ProjectCalculationMethod - pg.reporting.ProjectPerformanceSnapshot - pg.reporting.ProjectProgress - pg.staging.DuplicatedMigratedMongoElement spec: title: Unique constraint on Name for Asset and CommissioningSystem project_kind: modify user_facing_behavior: The Name field must be unique across all rows in the Asset table and separately unique across all rows in the CommissioningSystem table. Attempts to insert or update a record with a duplicate Name will be rejected at the database level. data_touched: - Asset - CommissioningSystem api_surface: write non_functional_requirements: - Uniqueness enforced via a database-level UNIQUE constraint (not just application logic) - Migration must handle any pre-existing duplicate Name values before applying the constraint out_of_scope: - Case-insensitivity (unless architect determines existing data requires it) - Changes to any other columns or tables - API or UI validation error message changes open_questions: - Are there existing duplicate Name values in either table that need to be resolved before the constraint can be applied? - Should the uniqueness be case-insensitive (e.g. via a unique index on LOWER(name)) or case-sensitive (default Postgres behavior)? - Should the constraint be scoped globally per table, or scoped within a tenant/organization partition?