# Postgres Specialist Report — Asset CR Endpoints (PAPI-3523)

All change files are **AI-generated**, authored as `agentneo` (changeset author)
with the `AgentNeo` display name in the object header comment blocks.

## Revision addressed: "Fix the failed API tests"

The API2 e2e suite (`api2/test/e2e/api/assets.e2e.spec.ts`) drives the real
database. Reading the API2 consumer (`api2/src/services/assets.service.ts`)
revealed the contract the tests actually exercise, and my original Postgres
surface did not match it on the create path:

| API2 expects | My original surface | Result |
|---|---|---|
| `SELECT * FROM xyz."fn_InsertAsset"($1,$2,$3,$4)` — a **function** returning the full row | `usp_InsertAsset` — a **procedure** with 6 params + `INOUT` `_assetId` | `SELECT *` on a procedure fails; create endpoint 500s |
| params `(projectId, assetTypeId, name, createdBy)` — no `parentAssetId` | included `_parentAssetId` | signature/arity mismatch |
| error text must contain `'AssetType with id'` (API2 `mapError` maps it to `NotFoundError` → 404) | raised `'No AssetType found for AssetTypeId % in this project'` | 404 test would get 500 |

The `fn_GetAsset` and `fn_GetAssetList` signatures already matched what API2
calls (`fn_GetAsset($1,$2)`, `fn_GetAssetList($1,$2,$3)`) and returned the full
column set incl. the internal `"Id"` used as the pagination cursor — left as-is.

## Files changed (grouped by directory)

### Added — `Database/xyz/Functions/`
- **`fn_InsertAsset.sql`** — new table-returning function
  `(_projectId, _assetTypeId, _name, _createdBy)`. Resolves the shard via
  `fn_GetProjectShardId` (raises on unknown project → 404), validates the
  asset type exists in the project raising
  `'AssetType with id % not found in project'` (substring `AssetType with id`
  matches API2's `mapError` → 404), inserts, and returns the created row via
  `RETURN QUERY SELECT * FROM xyz."fn_GetAsset"(_projectId, _newAssetId)`.
  Mirrors the existing `fn_InsertAssetType.sql` pattern.

### Removed — `Database/xyz/Procedures/`
- **`usp_InsertAsset.sql`** — deleted. Superseded by `fn_InsertAsset`; API2
  never called the procedure, so it was dead, contract-diverging code.

### Unchanged (from prior work, already correct)
- `Database/xyz/Functions/fn_GetAsset.sql`
- `Database/xyz/Functions/fn_GetAssetList.sql`

## Numeric prefixes
None chosen — all objects are functions (named-file, idempotent,
`runOnChange:true`, `endDelimiter:/`), picked up by the
`<includeAll path="Database/xyz/Functions"/>` entry. No numeric ordering
applies and `non_distributed_changelog.xml` was not touched.

## Table / constraint / index confirmation
No table DDL, constraints, or indexes were added or changed. `xyz."Asset"`
is a pre-existing table used as-is (`index_changes: none` in the plan was
respected — `999_indexes.sql` untouched). Column names used
(`ProjectShardId`, `AssetId`, `AssetTypeId`, `Name`, `CreatedBy`, `IsDeleted`,
`InsertedOn`, `LastModifiedOn`, `LastModifiedBy`, `DeletedOn`, `DeletedBy`)
were verified against `Database/xyz/Tables/129_xyz_asset.sql`.

## Column coverage
`fn_GetAsset`, `fn_GetAssetList`, and `fn_InsertAsset` all return the full
current `Asset` column set. `ParentAssetId` is returned by the read functions
but is **not** an input to `fn_InsertAsset` — the API2 create path does not
accept a `parentAssetId` (see `assets.validator.ts` / `assets.controller.ts`),
so new assets are inserted with `ParentAssetId` NULL. No API2 DTO change is
required for this revision.

## Build / test status
`./build` and `./test` could **not** be run — Docker is not available in this
environment and no Postgres is listening on :5490/:5432. Verification was
static: signatures cross-checked against the API2 service SQL and e2e
assertions, column names checked against the table create file, and the new
function follows the proven `fn_InsertAssetType` structure.

## Deviations from the plan
- **Plan said `usp_InsertAsset` (procedure, incl. `parentAssetId`, `INOUT`
  return); implemented `fn_InsertAsset` (function, 4 params) instead.** The
  plan's `db_calls` predate the API2 implementation, which calls
  `fn_InsertAsset` via `SELECT *` and reads the returned row directly (no
  post-insert `fn_GetAsset` round-trip). Matching the actual consumer is
  required to make the API tests pass; keeping the procedure would leave the
  create endpoint broken. This is the minimal change to satisfy the review
  feedback without expanding scope.

## Open questions for reviewer
- `parentAssetId` was in the plan's request DTO but the shipped API2 surface
  omits it. If parent-asset linkage is still wanted for v1, both `fn_InsertAsset`
  and the API2 create path need it added — flagging rather than assuming.