summary: "Adds four API2 endpoints to manage AssetType\u2194SystemType mappings (create,\n\ delete, list, get-by-id) backed by the existing xyz.AssetTypeSystemTypeMapping\n\ table, and augments both Asset GET endpoints to include a scalar systemId\nfield\ \ derived from the asset's AssetType\u2192SystemType mapping (null when no\nmapping\ \ exists). No new tables are introduced \u2014 all target tables already\nexist.\ \ This is a modify feature entirely on API2 + Postgres.\n" target_service: api2 postgres_changes: - kind: procedure schema: xyz name: usp_InsertAssetTypeSystemTypeMapping intent: "Insert a new AssetType\u2194SystemType mapping row into\nxyz.\"AssetTypeSystemTypeMapping\"\ . Resolves _projectShardId via\nfn_GetProjectShardId. Validates that both the\ \ AssetType and SystemType\nexist for the project (raising NOT_FOUND / BAD_REQUEST\ \ as appropriate),\nand that no duplicate (AssetTypeId, SystemTypeId) pair already\ \ exists for\nthe project (raising a constraint/BAD_REQUEST). Sets CreatedBy.\n" parameters: - _projectId UUID - _assetTypeId UUID - _systemTypeId UUID - _createdBy TEXT returns: 'The inserted mapping row (AssetTypeSystemTypeMappingId, AssetTypeId, SystemTypeId, InsertedOn, CreatedBy). ' file: Database/xyz/Procedures/usp_InsertAssetTypeSystemTypeMapping.sql - kind: procedure schema: xyz name: usp_DeleteAssetTypeSystemTypeMapping intent: 'Hard-delete the mapping row identified by _assetTypeSystemTypeMappingId for the resolved _projectShardId. Raises NOT_FOUND if the mapping does not exist within the project. (Table has no soft-delete columns, so this is a DELETE.) ' parameters: - _projectId UUID - _assetTypeSystemTypeMappingId UUID returns: none file: Database/xyz/Procedures/usp_DeleteAssetTypeSystemTypeMapping.sql - kind: function schema: xyz name: fn_GetAssetTypeSystemTypeMapping intent: 'Return a single mapping row by AssetTypeSystemTypeMappingId for the resolved _projectShardId. Returns AssetTypeSystemTypeMappingId, AssetTypeId, SystemTypeId, InsertedOn, CreatedBy, LastModifiedOn, LastModifiedBy. Empty result set when not found (API2 maps to 404). ' parameters: - _projectId UUID - _assetTypeSystemTypeMappingId UUID returns: 'TABLE(assetTypeSystemTypeMappingId UUID, assetTypeId UUID, systemTypeId UUID, insertedOn TIMESTAMPTZ, createdBy TEXT, lastModifiedOn TIMESTAMPTZ, lastModifiedBy TEXT) ' file: Database/xyz/Functions/fn_GetAssetTypeSystemTypeMapping.sql - kind: function schema: xyz name: fn_GetAssetTypeSystemTypeMappingList intent: 'Return all mappings for the resolved _projectShardId, paginated via a lastFetchedIndexId cursor on the serial Id column plus a size limit, matching the existing API2 list-pagination pattern. Returns the same columns as fn_GetAssetTypeSystemTypeMapping plus the Id used as the pagination cursor. ' parameters: - _projectId UUID - _lastFetchedIndexId INT - _size INT returns: 'TABLE(id INT, assetTypeSystemTypeMappingId UUID, assetTypeId UUID, systemTypeId UUID, insertedOn TIMESTAMPTZ, createdBy TEXT, lastModifiedOn TIMESTAMPTZ, lastModifiedBy TEXT) ' file: Database/xyz/Functions/fn_GetAssetTypeSystemTypeMappingList.sql - kind: function schema: xyz name: fn_GetAsset intent: "AMEND the existing general Asset read function to add a top-level\nsystemId\ \ column. The systemId is resolved by joining\nxyz.\"AssetTypeSystemTypeMapping\"\ \ on the asset's AssetTypeId (within the\nsame ProjectShardId) and returning SystemTypeId,\ \ or NULL when no mapping\nexists. Because the AssetType\u2192SystemType relationship\ \ may be many\n(see risks), this join MUST be constrained to return at most one\ \ row\nper asset (e.g. LEFT JOIN LATERAL ... LIMIT 1 or MIN by a deterministic\n\ order) so the Asset row is not fan-out-duplicated. The full existing\nAsset column\ \ set (AssetId, AssetTypeId, ParentAssetId, Name, CreatedBy,\nInsertedOn, LastModifiedOn,\ \ LastModifiedBy, IsDeleted, DeletedOn,\nDeletedBy) is preserved; systemId is\ \ appended. If the current file uses\nthe fn_GetAssetList naming instead, amend\ \ that one too \u2014 both the single\nand list Asset read functions must expose\ \ systemId consistently.\n" parameters: - _projectId UUID - _assetId UUID returns: 'Existing Asset column set PLUS systemId UUID NULL. ' file: Database/xyz/Functions/fn_GetAsset.sql - kind: function schema: xyz name: fn_GetAssetList intent: 'AMEND the existing general Asset list function the same way as fn_GetAsset: add a top-level systemId column resolved from xyz."AssetTypeSystemTypeMapping" (at most one row per asset, NULL when none). Preserve the full existing Asset list column set and pagination behavior; only append systemId. ' parameters: - existing parameters unchanged (projectId + pagination cursor/size) returns: 'Existing Asset list column set PLUS systemId UUID NULL. ' file: Database/xyz/Functions/fn_GetAssetList.sql citus_changes: none index_changes: none reporting_changes: none seed_data_changes: none mongo_changes: none api2_changes: - method: POST path: /api/v2/projects/{projectId}/asset-types/system-type-mappings tag: AssetTypeSystemTypeMappings permissions: - ASSET_TYPE_EDIT db_calls: - usp_InsertAssetTypeSystemTypeMapping request_dto: name: CreateAssetTypeSystemTypeMappingRequest properties: assetTypeId: type: string format: uuid required: true systemTypeId: type: string format: uuid required: true response_dto: name: AssetTypeSystemTypeMappingResponse properties: assetTypeSystemTypeMappingId: type: string format: uuid assetTypeId: type: string format: uuid systemTypeId: type: string format: uuid insertedOn: type: string format: date-time createdBy: type: string files: - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.routes.ts - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.controller.ts - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.validator.ts - src/services/asset.type.systemtype.mappings.service.ts notes: "The procedure validates AssetType/SystemType existence and duplicate\n(AssetTypeId,\ \ SystemTypeId) pairs internally, so the controller does NOT\npre-fetch the AssetType\ \ or SystemType separately \u2014 a single round-trip.\n" - method: DELETE path: /api/v2/projects/{projectId}/asset-types/system-type-mappings/{mappingId} tag: AssetTypeSystemTypeMappings permissions: - ASSET_TYPE_EDIT db_calls: - usp_DeleteAssetTypeSystemTypeMapping response_dto: none files: - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.routes.ts - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.controller.ts - src/services/asset.type.systemtype.mappings.service.ts notes: 'usp_DeleteAssetTypeSystemTypeMapping raises NOT_FOUND when the mapping does not exist for the project; the controller does not fetch first. ' - method: GET path: /api/v2/projects/{projectId}/asset-types/system-type-mappings/{mappingId} tag: AssetTypeSystemTypeMappings permissions: - ASSET_TYPE_VIEW db_calls: - fn_GetAssetTypeSystemTypeMapping response_dto: name: AssetTypeSystemTypeMappingResponse properties: assetTypeSystemTypeMappingId: type: string format: uuid assetTypeId: type: string format: uuid systemTypeId: type: string format: uuid insertedOn: type: string format: date-time createdBy: type: string lastModifiedOn: type: string format: date-time nullable: true lastModifiedBy: type: string nullable: true files: - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.routes.ts - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.controller.ts - src/services/asset.type.systemtype.mappings.service.ts notes: 'Empty result set from the function maps to a 404 NotFoundError. ' - method: GET path: /api/v2/projects/{projectId}/asset-types/system-type-mappings tag: AssetTypeSystemTypeMappings permissions: - ASSET_TYPE_VIEW db_calls: - fn_GetAssetTypeSystemTypeMappingList response_dto: name: PaginationEnvelope properties: records: type: array recordCount: type: integer lastFetchedIndexId: type: integer files: - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.routes.ts - src/api/v2/projects/assettypes/systemtypemappings/asset.type.systemtype.mappings.controller.ts - src/services/asset.type.systemtype.mappings.service.ts notes: 'Uses parsePagingQueryParam + buildPaginatedQueryResponse per the standard list pattern; cursor is the serial Id returned by the function. ' - method: GET path: /api/v2/projects/{projectId}/assets/{assetId} tag: Assets permissions: - ASSET_VIEW db_calls: - fn_GetAsset response_dto: name: AssetResponse (MODIFIED) properties: systemId: type: string format: uuid nullable: true files: - src/services/asset.service.ts - src/models/egress.ts notes: "COLUMN-CHANGE CONSISTENCY: the existing Asset egress DTO gains a new\ntop-level\ \ `systemId` field (scalar UUID or null). The egress mapper\n(mapRowToAsset) must\ \ map the new systemId column returned by the amended\nfn_GetAsset. Existing Asset\ \ unit/e2e tests, mocks, and fixtures MUST be\nupdated to include `systemId` so\ \ `npm test` stays green. No ingress DTO\nchange (write endpoints are out of scope).\ \ The controller does NOT make a\nsecond call to resolve systemId \u2014 it comes\ \ from the single fn_GetAsset\nround-trip.\n" - method: GET path: /api/v2/projects/{projectId}/assets tag: Assets permissions: - ASSET_VIEW db_calls: - fn_GetAssetList response_dto: name: AssetResponse (MODIFIED, list item) properties: systemId: type: string format: uuid nullable: true files: - src/services/asset.service.ts - src/models/egress.ts notes: "COLUMN-CHANGE CONSISTENCY: same as the single GET \u2014 each list record\ \ now\ncarries `systemId`. Update the shared Asset egress mapper, and update the\n\ Asset list unit/e2e tests, mocks, and fixtures accordingly so `npm test`\nstays\ \ green.\n" api1_changes: none inter_service_calls: none new_permissions: none java_frozen_resources: none risks: - "Open question (cardinality): the spec asks whether AssetType\u2192SystemType is\ \ 1:1 or 1:many. The existing xyz.\"AssetTypeSystemTypeMapping\" table has a composite\ \ PK (ProjectShardId, AssetTypeId, SystemTypeId), which structurally ALLOWS multiple\ \ SystemTypes per AssetType. But the Asset GET response requires a SCALAR systemId.\ \ The plan resolves this by deterministically picking at most one mapping per asset\ \ in fn_GetAsset/fn_GetAssetList to avoid row fan-out. Confirm with the team whether:\ \ (a) uniqueness should be enforced (reject a second SystemType for an AssetType),\ \ or (b) the scalar systemId is intentionally \"the first/primary\" mapping. If\ \ (a), add a unique constraint on (ProjectShardId, AssetTypeId) \u2014 flag for\ \ the Postgres specialist. If (b), the deterministic pick is acceptable but semantically\ \ lossy.\n" - 'Open question (uniqueness of pairs): usp_InsertAssetTypeSystemTypeMapping is planned to reject duplicate (AssetTypeId, SystemTypeId) pairs, which the composite PK already enforces at the DB level. Confirm this is the desired behavior (409/400 on duplicate). ' - "Open question (authorization): permissions are set to ASSET_TYPE_EDIT for write\ \ and ASSET_TYPE_VIEW / ASSET_VIEW for reads, mirroring assumed existing AssetType/Asset\ \ management permissions. If these permission names differ in src/api/auth/authorities.types.ts,\ \ the API2 specialist must substitute the correct existing names \u2014 no new permissions\ \ are being introduced.\n" - 'Open question (cascade on delete): deleting an AssetType or SystemType and its effect on mappings is out of scope here. If the team wants cascade/blocking behavior, it must be handled in the AssetType/SystemType delete procedures in a separate change. ' - 'Adding `systemId` to the Asset egress DTO is an additive, non-breaking field per the spec, but any consumer/contract tests that assert exact response shape (no-extra-keys) will need updating. ' out_of_scope: - Updating Asset write endpoints (POST/PUT/PATCH) to accept systemId. - Returning a nested SystemType object (only the scalar systemId is returned). - Bulk create/delete of mappings. - Cascade/blocking behavior when an AssetType or SystemType is deleted. testing_plan: 'Postgres: add/extend an IntegrationTest/main.py scenario covering usp_InsertAssetTypeSystemTypeMapping (happy path + duplicate rejection + missing AssetType/SystemType), usp_DeleteAssetTypeSystemTypeMapping (happy path + not-found), fn_GetAssetTypeSystemTypeMapping, fn_GetAssetTypeSystemTypeMappingList (pagination), and the amended fn_GetAsset / fn_GetAssetList returning systemId (both a mapped asset and an unmapped asset returning NULL, verifying no row fan-out when multiple SystemTypes exist for one AssetType). Run ./build to validate the changelog deploys clean. API2: unit tests for the new service functions and controllers for all four mapping endpoints (create/delete/get/list, incl. 404 and validation paths), plus e2e specs under test/e2e/api/v2/projects/assettypes/systemtypemappings/. CRITICALLY, update the EXISTING Asset GET unit/e2e tests, mocks, and fixtures to include the new top-level systemId field (both single and list) so `npm test` stays green.' _meta: model: claude-opus-4-8 atom_ids: - endpoint.api2.get__api_v2_projects__projectId__activities_mapping - endpoint.api1-hc-bpm.getTask - endpoint.api1-hc-notification.getNotification - endpoint.api1-hc-notification.getNotificationTemplate - endpoint.api2.get__api_v2_projects__projectId__markers__id_ - endpoint.api1-hc-iam.getAudit - endpoint.api1-hc-iam.getAuthorityCategory - endpoint.api1-hc-iam.getAuthority - endpoint.api1-hc-iam.getAuthoritySubcategory - endpoint.api1-hc-project.getSyncDataLogBySyncId - endpoint.api1-hc-project.getCoordinate - endpoint.api1-hc-project.getDashboard - pg.reporting.CalculationMethod - pg.reporting.ProgressOutput - pg.reporting.ProjectCalculationMethod - pg.reporting.ProjectPerformanceSnapshot - pg.reporting.ProjectProgress - pg.staging.DuplicatedMigratedMongoElement spec: title: "AssetType\u2194SystemType Mapping Endpoints + Asset GET SystemId" project_kind: modify user_facing_behavior: "1) Four new endpoints to manage AssetType\u2194SystemType\ \ mappings: create a mapping, delete a mapping, list all mappings, get a single\ \ mapping by ID. 2) Both Asset GET endpoints (get by ID, get list) now include\ \ a top-level `systemId` field (scalar ID or null) reflecting the SystemType\ \ relation for that asset's AssetType." data_touched: - AssetType - SystemType - AssetTypeSystemTypeMapping - Asset api_surface: both non_functional_requirements: - Mapping endpoints follow existing API2 REST conventions - systemId field is always present in Asset GET responses (null when no mapping exists) - No breaking changes to existing Asset GET response shape beyond adding the new field out_of_scope: - Updating Asset write endpoints (POST/PUT/PATCH) to accept systemId - "Returning a nested SystemType object \u2014 only the scalar ID is returned" - Bulk create/delete of mappings open_questions: - "Is the AssetType\u2194SystemType mapping 1:1 or can one AssetType map to multiple\ \ SystemTypes? This affects how systemId is resolved on the Asset (single value\ \ vs. array)." - Should mapping endpoints enforce uniqueness (e.g. reject duplicate AssetType+SystemType pairs)? - "What are the authorization rules for the mapping endpoints \u2014 same roles\ \ as existing AssetType management, or different?" - Should deleting an AssetType or SystemType cascade-delete its mappings, or be blocked if a mapping exists?