# Specialist Report — PostgreSQLDatabase (PAPI-3631, Asset PATCH)

> This change file is **AI-generated** and authored as `agentneo` in all
> Liquibase changeset headers.

## Interpretation (GitHub feedback round)

One line per comment/failure — how I read it before touching code:

- **copilot [RFC 7396 reference is misleading]** — I read this as: the header
  comment claims "RFC 7396 merge-patch semantics", but RFC 7396 uses an
  *explicit null* to clear a field, whereas this function treats a NULL
  parameter as "omitted → leave unchanged". Those are different. Fix: remove
  the RFC 7396 claim from the DB file. RFC 7396 lives at the API layer; the
  DB function only does a partial update (NULL param = column unchanged).
- **copilot [AssetTypeId not validated; ParentAssetId; LastModifiedBy nulling]**
  — I read this as: the update path added relational params (`AssetTypeId`,
  `ParentAssetId`) that the plan lists as **out of scope**, and those params
  are what create the broken-reference and null-clearing risks. Fix: remove
  the out-of-scope relational params entirely (bringing the function back to
  the plan's `(_projectId, _assetId, _name, _lastModifiedBy)` signature),
  which eliminates the validation gap. `_lastModifiedBy` is always supplied
  from the authenticated session on an update, so its direct assignment is
  correct and intended (matches `fn_UpdateIssue`).
- **copilot [COALESCE cannot clear ParentAssetId to NULL]** — I read this as:
  a genuine limitation of COALESCE, but only relevant because `ParentAssetId`
  was patchable. Since patching `ParentAssetId` is out of scope per the plan,
  the fix is to remove the param, not to add a `_clearParentAssetId` flag
  (that would be scope creep beyond the approved plan).
- **dave-webb [COALESCE won't clear an existing parent asset id]** — I read
  this as the same point as above; resolved by removing `ParentAssetId` from
  the patch surface. Recorded as a flag below in case the team *does* want a
  future parent-clearing PATCH — that needs a plan change (presence indicator
  or JSONB merge-patch input).
- **XYZPlatformApi PR #857 build failure** — I read this as: the API2 code
  and the DB function signature had drifted (the DB function carried 6 params
  incl. the two out-of-scope relational fields, while the plan's `db_call`
  contract is name-only). Realigning the DB signature to the plan's
  `(_projectId, _assetId, _name, _lastModifiedBy)` restores the contract. The
  API2 repo itself is outside this repo; the API2 specialist must confirm its
  call site matches the 4-param signature. Flagged for cross-repo coordination.

## Files modified

`Database/xyz/Functions/`
- `fn_UpdateAsset.sql` — removed the out-of-scope `_assetTypeId` and
  `_parentAssetId` parameters (and their `COALESCE` assignments), leaving the
  plan-sanctioned patch surface of `Name` only. Reworded the header comment to
  drop the inaccurate RFC 7396 reference. Signature is now
  `fn_UpdateAsset(_projectId UUID, _assetId UUID, _name TEXT, _lastModifiedBy TEXT)`,
  matching the plan's `usp_UpdateAsset` parameter contract.

## Numeric prefixes

None — functions are named by object, not numeric-prefixed.

## Deviations from the plan

- The plan requested a **procedure** `usp_UpdateAsset`. The sibling Asset
  objects (`fn_InsertAsset`, `fn_GetAsset`) are all **functions** returning
  the full row via `RETURN QUERY SELECT * FROM fn_GetAsset(...)`. The prior
  round implemented `fn_UpdateAsset` to match that local convention and to
  return the updated representation in one round-trip; the reviewers accepted
  this shape and commented only on its contents. Kept as-is to avoid churn.
  Behaviourally equivalent to the plan's proc (resolves shard, filters by
  ProjectShardId + AssetId, sets LastModifiedOn/By).

## Open questions for human reviewer

- If the team later wants PATCH to **clear** `ParentAssetId` or **change**
  `AssetTypeId`, that is beyond this plan's scope (relational fields are
  explicitly out of scope). It requires a plan revision with a presence
  indicator (e.g. a `_clearParentAssetId` flag) or a JSONB merge-patch input
  so NULL can be distinguished from "omitted", plus AssetType existence
  validation like `fn_InsertAsset`. Not implemented here per the ground rules.
- API2 PR #857 build: confirm the API2 call site invokes the 4-param
  signature `fn_UpdateAsset(_projectId, _assetId, _name, _lastModifiedBy)`.

## Verification

- Runtime verification could **not** be performed: there is no `./build`
  script in this repo and Docker is unavailable in this environment, so a
  from-scratch changelog deploy could not be run.
- Static review performed:
  - Signature reduced to `(_projectId UUID, _assetId UUID, _name TEXT,
    _lastModifiedBy TEXT)` — matches the plan's `usp_UpdateAsset` contract.
  - Only `Name` is patched via `COALESCE(_name, a."Name")`; no `AssetTypeId`
    or `ParentAssetId` references remain.
  - No RFC 7396 reference remains in the file.
  - `runOnChange:true`, `endDelimiter:/` header intact; body opens with
    `DROP FUNCTION IF EXISTS` and the file's last line is `/`.
  - `fn_GetAsset(_projectId, _assetId)` (the row returned) exists and is
    unchanged.