summary: "Add a per-project UNIQUE constraint on the Name column of 5 existing\nproject-scoped\
  \ xyz tables: AssetType, SystemType, Asset, CommissioningSystem,\nand CommissioningWorkflow\
  \ (the latter two are the actual table names for the\nspec's \"System\" and \"Workflow\"\
  ). Uniqueness is scoped per project \u2014 the\nconstraint is composite (ProjectShardId,\
  \ Name) \u2014 per reviewer guidance.\nDB-schema-only change owned by the Postgres\
  \ repo; no API1/API2 code changes.\n"
target_service: api2
postgres_changes:
- kind: unique_constraint
  schema: xyz
  table: AssetType
  constraint_name: AssetType_ProjectShardId_Name_uq
  columns:
  - ProjectShardId
  - Name
  notes: 'Add a composite UNIQUE constraint scoped per project. These are

    pre-May-2026 project-scoped tables; the constraint goes in the

    Constraints directory (NOT the table-create file), required for

    Citus submodule compatibility. Place in/append to a numbered

    Constraints file alongside the related table''s constraints, e.g.

    Database/xyz/Constraints/NNN_xyz_commissioning_unique.sql with

    runOnChange:true. Citus requires the distribution column

    (ProjectShardId) be part of any UNIQUE constraint on a distributed

    table, so the composite form is also a hard technical requirement.

    '
- kind: unique_constraint
  schema: xyz
  table: SystemType
  constraint_name: SystemType_ProjectShardId_Name_uq
  columns:
  - ProjectShardId
  - Name
  notes: 'Composite UNIQUE (ProjectShardId, Name), per-project scope. Same

    Constraints-directory placement and Citus requirement as above.

    '
- kind: unique_constraint
  schema: xyz
  table: Asset
  constraint_name: Asset_ProjectShardId_Name_uq
  columns:
  - ProjectShardId
  - Name
  notes: "Composite UNIQUE (ProjectShardId, Name), per-project scope. Note Asset\n\
    has a soft-delete flag (IsDeleted); a plain UNIQUE constraint will also\nblock\
    \ reusing a name after soft-delete. Flagged in risks \u2014 confirm\nwhether a\
    \ partial unique index WHERE \"IsDeleted\" = false is preferred.\n"
- kind: unique_constraint
  schema: xyz
  table: CommissioningSystem
  constraint_name: CommissioningSystem_ProjectShardId_Name_uq
  columns:
  - ProjectShardId
  - Name
  notes: "Spec's \"System\" maps to the real table xyz.\"CommissioningSystem\".\n\
    Composite UNIQUE (ProjectShardId, Name), per-project scope. This table\nalso has\
    \ IsDeleted \u2014 same soft-delete consideration as Asset (see risks).\n"
- kind: unique_constraint
  schema: xyz
  table: CommissioningWorkflow
  constraint_name: CommissioningWorkflow_ProjectShardId_Name_uq
  columns:
  - ProjectShardId
  - Name
  notes: 'Spec''s "Workflow" maps to the real table xyz."CommissioningWorkflow".

    Composite UNIQUE (ProjectShardId, Name), per-project scope.

    '
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:
- 'The spec named "System" and "Workflow" but no such tables exist. The actual

  project-scoped tables are xyz."CommissioningSystem" and

  xyz."CommissioningWorkflow". The plan targets those. Confirm this mapping

  is correct before approval.

  '
- 'Asset and CommissioningSystem have soft-delete (IsDeleted/DeletedOn). A

  plain UNIQUE (ProjectShardId, Name) constraint will prevent reusing the

  name of a soft-deleted row. If the team wants names reusable after

  soft-delete, the Postgres specialist should instead use a partial UNIQUE

  INDEX (in 999_indexes.sql) WHERE "IsDeleted" = false. Defaulting to a hard

  UNIQUE constraint as specified; flag for human decision at Gate 2.

  '
- "These are existing tables that may already contain data in some\nenvironments.\
  \ The spec asserts all 5 tables are currently empty, so no\nbackfill/dedup is needed.\
  \ If any environment has duplicate (ProjectShardId,\nName) rows, the constraint\
  \ creation will fail on deploy \u2014 the specialist\nshould verify emptiness before\
  \ deploying.\n"
- 'In production these tables are Citus-distributed by ProjectShardId. Adding

  a UNIQUE constraint on an already-distributed table requires the

  distribution column be included (it is), and may require the constraint be

  added via the distributed deployment path. No new distribution command is

  needed (tables already distributed); the specialist coordinates the

  constraint apply order with the existing Citus setup.

  '
out_of_scope:
- Any API1 or API2 code changes (DB-schema-only change)
- Data migration / backfill / dedup scripts
- Uniqueness on any column other than Name
- Any tables beyond the 5 listed
- "Global (cross-project) uniqueness \u2014 uniqueness is scoped per project per reviewer\
  \ guidance"
testing_plan: 'Postgres specialist runs ./build to validate the full changelog deploys

  cleanly with the 5 new UNIQUE constraints on a fresh DB. Optionally extend

  IntegrationTest/main.py with a scenario inserting two rows with the same

  (ProjectShardId, Name) into one of the affected tables and asserting the

  second insert raises a unique violation, and that the same Name under a

  different ProjectShardId succeeds (proving per-project scope). No API2/API1

  test changes since no application code is touched.'
_meta:
  model: claude-opus-4-8
  atom_ids:
  - rule.architect_rules
  - convention.postgres
  - convention.citus
  - convention.api2
  - convention.api1-hc-iam
  - convention.api1-hc-bpm
  - convention.api1-hc-project
  - endpoint.api2.put__api_v2_projects__projectId__models_folders__folderId__rename
  - convention.api1-hc-notification
  - endpoint.api2.post__api_v2_projects__projectId__files
  - endpoint.api2.patch__api_v2_projects__projectId__model-versions__modelVersionId_
  - endpoint.api2.post__api_v2_projects
  - pg.reporting.CalculationMethod
  - pg.reporting.ProgressOutput
  - pg.reporting.ProjectCalculationMethod
  - pg.reporting.ProjectPerformanceSnapshot
  - pg.reporting.ProjectProgress
  - pg.staging.DuplicatedMigratedMongoElement
  spec:
    title: Add UNIQUE constraint on Name for 5 tables
    project_kind: modify
    user_facing_behavior: No user-facing change. The Name column on AssetType, SystemType,
      Asset, System, and Workflow tables will be enforced as unique at the database
      level.
    data_touched:
    - AssetType
    - SystemType
    - Asset
    - System
    - Workflow
    api_surface: read
    non_functional_requirements:
    - "Change is limited to DB schema only \u2014 no API or application code modifications"
    - No data migration scripts required; all affected tables are currently empty
    - Unique constraint must be applied to the Name column on each of the 5 tables
    out_of_scope:
    - Any changes to API1 or API2 codebases
    - Data migration or backfill scripts
    - Any other columns or tables beyond the 5 listed
    open_questions:
    - Should the uniqueness be scoped (e.g. unique per tenant/project/parent), or
      globally unique across the entire table? Assumed globally unique since no scope
      was specified.
    - "Which database/schema owns these tables \u2014 API2 (Postgres) or API1 (MongoDB)?\
      \ Assumed Postgres (API2) given the relational table naming, but architect should\
      \ confirm."