summary: 'Database-only change: add unique constraints on the Name column for four

  project-scoped tables (xyz.AssetType, xyz.SystemType, xyz.Asset, and the

  commissioning "System" entity, which is named xyz.CommissioningSystem in

  the live schema). Uniqueness is scoped per project (per ProjectShardId),

  case-sensitive, enforced via unique indexes in the Postgres repo. No API2

  or API1 code changes are made in this plan.

  '
target_service: none
postgres_changes:
- kind: note
  description: "No table DDL (no new tables, no column additions/removals/alters).\
    \ The\nfour target tables already exist with a Name column:\n  - xyz.AssetType\
    \ (Name text NOT NULL)\n  - xyz.SystemType (Name text NOT NULL)\n  - xyz.Asset\
    \ (Name text NOT NULL)\n  - xyz.CommissioningSystem (Name text NOT NULL)  <--\
    \ the spec's \"System\"\nThe only changes are unique indexes, which the architect\
    \ MUST route via\nthe top-level index_changes section (not baked into postgres_changes).\n\
    See index_changes below. The Postgres specialist appends the human-ticked\nunique\
    \ indexes to Database/xyz/Constraints/999_indexes.sql.\n"
- kind: note
  description: "Scope decision (resolving open questions): uniqueness is scoped PER\n\
    PROJECT, not globally. All four tables are project-scoped (composite PK\n(ProjectShardId,\
    \ <Table>Id)), and Citus requires project-scoped indexes\nto lead with ProjectShardId.\
    \ Therefore each unique index is on\n(ProjectShardId, Name). This also satisfies\
    \ the Citus single-shard\nuniqueness enforcement requirement \u2014 a global unique\
    \ index on Name alone\nis NOT enforceable on a distributed table.\n"
- kind: note
  description: 'Case-sensitivity decision: uniqueness is CASE-SENSITIVE (plain Name

    column), matching the spec''s stated default. No LOWER() expression index

    and no citext is introduced. If case-insensitive uniqueness is later

    desired, that would be a separate change.

    '
- kind: note
  description: 'Asset and CommissioningSystem are soft-deletable (IsDeleted column).
    A

    plain unique index would block re-using a Name after a soft-delete. To

    avoid that, the unique indexes on xyz.Asset and xyz.CommissioningSystem

    are PARTIAL indexes WHERE "IsDeleted" = false, so only live rows are

    constrained. AssetType and SystemType have no IsDeleted column, so their

    unique indexes are unfiltered.

    '
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:
- "Existing duplicate Names: if any project already has two live rows with the same\
  \ Name in any of the four tables, the CREATE UNIQUE INDEX will FAIL at deploy time.\
  \ The spec marks dedup as out of scope, so the Postgres specialist must verify (per\
  \ ProjectShardId, per table, live rows only) that no duplicates exist BEFORE the\
  \ index is added. If duplicates exist, a Patch/ backfill or manual cleanup is required\
  \ first \u2014 flag to the team rather than silently deduping.\n"
- 'Spec lists a "System" table that does not exist under that name. It has been mapped
  to xyz.CommissioningSystem. If the team intended a different table, this mapping
  must be confirmed at Gate 2 before the index is added.

  '
- 'This plan is database-only per the revision request. The spec''s NFR about API
  returning a clear 409/422 on duplicate Name is NOT implemented here. The DB unique-violation
  will currently surface through whatever the existing API2 error mapping does. If
  the team wants a guaranteed 409/422 with a friendly message, a follow-up API2 change
  is needed (out of scope here).

  '
- 'Uniqueness is scoped per-project and case-sensitive (architect decisions on the
  open questions). If global-per-table or case-insensitive semantics are actually
  required, the index definitions change and this must be revisited.

  '
out_of_scope:
- "API2/API1 code changes \u2014 this is a database-only change (per revision request)."
- Explicit 409/422 error-shaping in the API layer for duplicate Name submissions.
- Backfilling or deduplicating existing rows that already share a Name.
- Case-insensitive uniqueness (citext / LOWER() expression indexes).
- Any new tables, columns, procedures, functions, or views.
testing_plan: "Postgres repo:\n  - Run ./build to validate the full changelog (including\
  \ the amended\n    999_indexes.sql) deploys clean on a fresh database.\n  - Before\
  \ adding each unique index, run a duplicate-detection query per\n    table over\
  \ live rows, e.g.:\n      SELECT \"ProjectShardId\", \"Name\", COUNT(*)\n      FROM\
  \ xyz.\"AssetType\"\n      GROUP BY \"ProjectShardId\", \"Name\" HAVING COUNT(*)\
  \ > 1;\n    and the analogous query for SystemType, and for Asset /\n    CommissioningSystem\
  \ with an added WHERE \"IsDeleted\" = false. Confirm zero\n    rows returned; if\
  \ not, escalate per the risk above.\n  - Optionally extend IntegrationTest/main.py\
  \ with a scenario that inserts a\n    duplicate (ProjectShardId, Name) into each\
  \ table and asserts the insert\n    is rejected, and that a soft-deleted row's Name\
  \ can be reused on\n    Asset/CommissioningSystem (partial-index behavior).\nNo\
  \ API2/API1 test changes \u2014 no code paths are modified by this plan."
_meta:
  model: claude-opus-4-8
  atom_ids:
  - rule.architect_rules
  - convention.postgres
  - convention.api2
  - convention.citus
  - convention.api1-hc-bpm
  - convention.api1-hc-project
  - convention.api1-hc-iam
  - endpoint.api2.put__api_v2_projects__projectId__issues_activity-categories__issueId__link
  - endpoint.api2.post__api_v2_projects__projectId__coordinates
  - pg.xyz.Tenant
  - 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: Unique constraint on Name for 4 tables
    project_kind: modify
    user_facing_behavior: Creating or updating an AssetType, SystemType, Asset, or
      System with a Name that already exists will be rejected with a validation error.
      Names must be unique within each respective table.
    data_touched:
    - AssetType
    - SystemType
    - Asset
    - System
    api_surface: write
    non_functional_requirements:
    - Uniqueness is enforced at the database level (unique index/constraint) on the
      Name column for each of the four tables.
    - API should return a clear error response (e.g. 409 Conflict or 422 Unprocessable
      Entity) when a duplicate Name is submitted.
    out_of_scope:
    - "Case-insensitivity \u2014 uniqueness is assumed case-sensitive unless architect\
      \ decides otherwise"
    - Backfilling or deduplicating existing data with duplicate Names
    open_questions:
    - Are Name uniqueness constraints scoped globally per table, or scoped within
      a parent entity (e.g. unique Name per project/tenant)?
    - How should existing duplicate Names in the data be handled before the constraint
      is applied?
    - Should uniqueness be case-insensitive (e.g. 'Pump' and 'pump' treated as duplicates)?