# Postgres Specialist Report — AssetType↔SystemType Mapping + Asset GET SystemId

All change files are **AI-generated** and authored as `agentneo` (changeset author);
the `-- Created by:` display name is `AgentNeo`, dated 15/07/2026.

## Files added

### `Database/xyz/Procedures/`
- **`usp_InsertAssetTypeSystemTypeMapping.sql`** — inserts a mapping row into
  `xyz."AssetTypeSystemTypeMapping"`. Resolves `_projectShardId` via
  `fn_GetProjectShardId`, validates the `AssetType` and `SystemType` both exist for
  the project (raises `... not found in project`), and rejects a duplicate
  `(AssetTypeId, SystemTypeId)` pair (raises `... is already mapped to ...`). Sets
  `CreatedBy`.
- **`usp_DeleteAssetTypeSystemTypeMapping.sql`** — hard-deletes the mapping by
  `AssetTypeSystemTypeMappingId` within the project. Uses
  `GET DIAGNOSTICS ... ROW_COUNT`; raises `... not found in project` when nothing
  was deleted (API maps to 404). Table has no soft-delete columns, so a DELETE is
  correct.

### `Database/xyz/Functions/`
- **`fn_GetAssetTypeSystemTypeMapping.sql`** — single mapping by id. Returns
  `AssetTypeSystemTypeMappingId, AssetTypeId, SystemTypeId, InsertedOn, CreatedBy,
  LastModifiedOn, LastModifiedBy`. Empty result set when not found (API → 404).
- **`fn_GetAssetTypeSystemTypeMappingList.sql`** — keyset-paginated list. Params
  `(_projectId, _lastFetchedIndexId INT DEFAULT NULL, _size INT DEFAULT 1000)`;
  cursor is the per-shard `Id` IDENTITY column (same pattern as `fn_GetAssetList`).
  Returns `Id` plus the same columns as the single get.

## Files modified

### `Database/xyz/Functions/`
- **`fn_GetAsset.sql`** — appended `"SystemId" UUID` to `RETURNS TABLE` and resolved
  it via `LEFT JOIN LATERAL (... LIMIT 1) ON TRUE` on
  `xyz."AssetTypeSystemTypeMapping"` keyed on `(ProjectShardId, AssetTypeId)`,
  ordered by `m."Id" ASC` (earliest-created mapping). NULL when no mapping exists.
  The lateral `LIMIT 1` guarantees **no row fan-out** even though an AssetType may
  map to multiple SystemTypes.
- **`fn_GetAssetList.sql`** — same `SystemId` resolution; `"SystemId"` inserted after
  `"DeletedBy"` and before the trailing `"Id"` cursor column. Pagination behaviour
  unchanged.
- **`fn_InsertAsset.sql`** and **`fn_UpdateAsset.sql`** — these do
  `RETURN QUERY SELECT * FROM fn_GetAsset(...)` and declare their own **12-column**
  `RETURNS TABLE`. Because `fn_GetAsset` now returns 13 columns, `SELECT *` would
  raise a structure-mismatch at runtime. I changed the inner `SELECT *` to an
  **explicit 12-column projection** that intentionally omits `SystemId`, preserving
  the existing Asset write-endpoint response shape (write endpoints are out of scope
  for `systemId`). No signature change to these two functions.

## Numeric prefixes
None chosen. All six changes are procedures/functions (idempotent, `runOnChange:true`,
one-file-per-object, auto-included by the existing `<includeAll>` for
`Database/xyz/Procedures` and `Database/xyz/Functions`). No new tables, so no numeric
table/constraint prefixes were needed. `non_distributed_changelog.xml` untouched.

## Project-scoped table confirmation
No new tables introduced — `xyz."AssetTypeSystemTypeMapping"`,
`xyz."AssetType"`, `xyz."SystemType"`, and `xyz."Asset"` all already exist. All new
routines filter by `ProjectShardId` resolved through `fn_GetProjectShardId`.

## Indexes
`index_changes: none` in the plan → **no changes to `999_indexes.sql`**. The mapping
lookups (`fn_GetAsset`/`fn_GetAssetList`) filter on `(ProjectShardId, AssetTypeId)`,
which is covered by the leading columns of `AssetTypeSystemTypeMapping_pkey`
(`ProjectShardId, AssetTypeId, SystemTypeId`), so no supporting index is required.

## Column-coverage note for the API2 specialist
- `fn_GetAsset` / `fn_GetAssetList` now return a top-level **`SystemId` (UUID, nullable)**.
  The Asset egress mapper (`mapRowToAsset`) must map this new column, and existing
  Asset GET unit/e2e tests, mocks, and fixtures must include `systemId` (single + list)
  so `npm test` stays green. No ingress DTO change.
- **`usp_InsertAssetTypeSystemTypeMapping` return shape (see deviation below):** the
  procedure takes the 4 plan inputs `(_projectId, _assetTypeId, _systemTypeId,
  _createdBy)` and returns the two **server-generated** values via trailing INOUT
  params: **`_assetTypeSystemTypeMappingId UUID`** and **`_insertedOn TIMESTAMPTZ`**.
  API2 builds the POST response DTO from these two outputs plus the request values
  (`assetTypeId`, `systemTypeId`, `createdBy`) — a single round-trip, no pre-fetch.

## Deviations from the plan
1. **Insert procedure return mechanism.** The plan lists only the 4 input params and
   states it "returns the inserted mapping row." A PostgreSQL `PROCEDURE` cannot
   `RETURN QUERY`, so — following the repo's established INOUT convention
   (`usp_InsertProject_V2`, `usp_InsertModelFolder`) — the two generated columns are
   returned via INOUT params. Only `AssetTypeSystemTypeMappingId` and `InsertedOn`
   are server-generated; the other DTO fields are request inputs API2 already holds.
   This satisfies the single-round-trip requirement without a follow-up read.
2. **`fn_InsertAsset` / `fn_UpdateAsset` inner projection** changed from `SELECT *`
   to an explicit 12-column list (not mentioned in the plan). This is required to
   avoid a runtime structure-mismatch once `fn_GetAsset` gained `SystemId`, and it
   keeps the Asset write-endpoint contract byte-for-byte identical. No behavioural
   change to write endpoints.

## Verification
- **`./build` was NOT run** — Docker is unavailable in this environment and no
  `build` script is present (only `test`, which also needs Docker). Runtime
  changelog verification could not be performed.
- Static review performed:
  - New procs/functions follow the mandated headers (`agentneo` author,
    `runOnChange:true`, `endDelimiter:/`, files end with `/`, object header block).
  - Column names verified against the table create files
    (`120_xyz_asset_type_system_type_map.sql`, `118_xyz_asset_type.sql`,
    `119_xyz_system_type.sql`): `AssetTypeSystemTypeMappingId`, `AssetTypeId`,
    `SystemTypeId`, `InsertedOn`, `CreatedBy`, `LastModifiedOn`, `LastModifiedBy`.
  - Audit param `_createdBy` matches the target `CreatedBy` column.
  - All callers of `fn_GetAsset` audited and fixed (`fn_InsertAsset`,
    `fn_UpdateAsset`); no other callers of the amended functions exist in `Database/`.

## Open questions for human reviewer
1. **Cardinality (plan risk):** the scalar `SystemId` is resolved as the
   earliest-created mapping (`ORDER BY m."Id" ASC LIMIT 1`) — interpretation (b)
   "first/primary mapping". If the team instead wants 1:1 enforcement
   (interpretation (a)), add a unique constraint on
   `(ProjectShardId, AssetTypeId)` to `AssetTypeSystemTypeMapping` — a separate
   change, not done here.
2. **Duplicate handling:** duplicates are rejected with a plain `RAISE EXCEPTION`
   (message contains "already mapped"). Confirm API2 maps this to 409/400 as desired.
3. **Integration tests:** the plan's `testing_plan` asks for `IntegrationTest/main.py`
   scenarios. I did not add them because they cannot be executed here (no Docker) and
   unverified Python risks breaking the suite. Recommend the API2/integration owner
   add: insert happy-path + duplicate + missing AssetType/SystemType; delete
   happy-path + not-found; get + list pagination; and `fn_GetAsset`/`fn_GetAssetList`
   returning `SystemId` (mapped, unmapped→NULL, and no fan-out with multiple
   SystemTypes per AssetType).