Assigning Assets to Analysts Developer Guide - edmozley/freeitsm GitHub Wiki

Assigning assets to analysts โ€” developer guide

How users_assets came to hold two kinds of person, and the one failure mode that made this more than a column addition.

User-facing page: Assigning assets to analysts.

The schema

users_assets gained analyst_id, and user_id became nullable.

`user_id`    INT NULL,     -- was NOT NULL
`analyst_id` INT NULL,
UNIQUE KEY `uq_user_asset`    (`user_id`, `asset_id`),
UNIQUE KEY `uq_analyst_asset` (`analyst_id`, `asset_id`),

Exactly one of the two columns is set on any row. The database cannot express that โ€” a CHECK across two columns is not portable to every MySQL version this product supports โ€” so AssetsService enforces it, and that is precisely why assignment must go through the service rather than an INSERT somewhere convenient.

Two unique keys, not one

MySQL treats repeated NULLs in a unique key as distinct. So uq_user_asset stopped constraining analyst rows the moment user_id became nullable โ€” every analyst row has user_id IS NULL, so they never collide. Without uq_analyst_asset the same analyst could be given the same asset any number of times, while the index looked like it was guarding against it.

Relaxing NOT NULL on an existing install

api/system/db_verify.php uses the probe-then-MODIFY shape already used for five other columns:

SELECT IS_NULLABLE FROM information_schema.columns WHERE ... column_name = 'user_id'
if ($row && strtoupper($row['IS_NULLABLE']) === 'NO') {
    $conn->exec("ALTER TABLE `users_assets` MODIFY `user_id` INT NULL");
}

Safe because every existing row already names a requester, so relaxing the rule cannot invalidate anything. fk_users_assets_user is unaffected: a NULL never violates a foreign key, it simply has nothing to check.

๐Ÿ”ด The failure this would have caused, and why six files changed

Six places display who holds an asset. Every one of them had to change, and the reason is that the failure is silent:

  • The holder list joined users with an INNER JOIN. Correct while every holder was a requester, and it silently drops the row the moment one is not.
  • The count beside it was COUNT(ua.user_id). COUNT ignores NULL, so an analyst row counted as zero.

Together, an analyst's laptop would not have errored. It would have shown as held by nobody, with the count one short, and nothing on screen to say look here. That is worse than a crash.

The record preview was the sharpest case: the count and the name came from different queries, so it would have displayed "1 holder" with no name beside it โ€” the two halves contradicting each other on the same line.

The fix everywhere is the same shape:

LEFT JOIN users u     ON u.id  = ua.user_id
LEFT JOIN analysts ha ON ha.id = ua.analyst_id
WHERE ... AND (u.id IS NOT NULL OR ha.id IS NOT NULL)

COALESCE(u.display_name, ha.full_name) keeps display_name as the field name, so nothing reading the payload had to change. holder_type is added beside it rather than replacing anything.

That trailing AND (u.id IS NOT NULL OR ha.id IS NOT NULL) handles orphan rows โ€” there is no foreign key on the older data, so rows pointing at a deleted person exist on real installs, and without it they render as a holder with no name.

Only 6 of the 30 files touching users_assets display holders; the rest only filter. Measuring that first is what kept this to a manageable change.

The service

AssetsService::assignAnalyst() and unassignAnalyst() are siblings of the user versions rather than a flag on them. They validate against a different table, write a different column and produce a different audit line โ€” one function taking "one of these two" would be two functions sharing a body and an if at every step.

Two details worth keeping:

  • asset_checkout_log.user_id names a requester. An analyst handover records the name and leaves the id NULL, rather than writing an analyst id into a requester column where it would later read back as whichever requester holds that number.
  • The workflow payload's user.id gets 0, not the analyst id, for the same reason: a rule comparing it against a requester id would match the wrong person.

The endpoints

assign_asset_user.php and unassign_asset_user.php were extended rather than twinned, so the UI has one call site. Sending both user_id and analyst_id is rejected rather than resolved by picking one โ€” a caller that sends two has a bug, and honouring one of them hides it until somebody wonders why the other name never appeared.

api/assets/get_analysts.php is the analyst half of the picker. It is deliberately not tenant-scoped: analysts are install-wide and every other analyst picker in the product shows the whole desk, so scoping this one would be the odd one out without hiding anything.

See also