CATEGORY-CARD-FIELDS.PDF · THE IMPLEMENTATION RECORD

What shipped

Every field on the card, traced to its source. Ten already existed and were only re-presented; eleven are new on the API; five are computed in the browser and never leave it.

Re-presented: 10no backend change
New API fields: 11additive to the DTO
Derived in the browser: 5no API field
Migrations: 0no table, column or cron

the shape of the change

Purely additive, and entirely on existing tables. No migration, no new route — the DTO grew and the query was restructured. Six tables already in the schema carry all of it: camp_inventory_items, equipment_models, equipment_categories, service_contracts, manufacturers, work_orders.

One existing output was checked for drift rather than assumed safe: TierTotal.manufacturerCount is mathematically unchanged despite its query being restructured.

Already there, only re-presented10

These needed no backend work — the card just shows them differently. The last one is not data at all.

labelCategory name — equipment_categories.name, ’Uncategorized’ when null

clinicalImportanceThe Critical badge — clinical_importance (1–4, NULL→4); badge when === 1

fleetHealthPctCOUNT(*) FILTER (risk_level=’Healthy’) / COUNT(*)

countAssets in the category — COUNT(*)

modelCountCOUNT(DISTINCT equipment_model_id), non-null only

atRiskCOUNT(*) FILTER (risk_level=’At Risk’)

onContractservice_contracts.id IS NOT NULL and the contract has not expired

totalReplacementCostSUM(equipment_models.benchmark_new_cost) — an AI benchmark, not purchase price

topEquipment[].name / .manufacturerTop 4 models by unit count, manufacturer via manufacturers.name

— (no API field)The category icon and stripe: a hard-coded FE map in data/manage-inventory-config.ts, not from the database

New on the API11

vendorCountDistinct non-null Inventory.manufacturer_id, pre-grouped then counted

avgAgeYearsROUND(AVG(CASE WHEN purchase_year > 0 THEN <year> - purchase_year ELSE 0 END), 1)

replacementCostDueWithin12MoSUM(benchmark_new_cost) where COALESCE(purchase_date, Jan 1 of purchase_year) + useful_life lands in the next 12 months

openWorkOrderswork_orders.status IN (’Open’,’In Progress’), deleted_at IS NULL, joined on wo.inventory_id

workOrdersPastSlaOf those, wo.due_at IS NOT NULL AND wo.due_at < NOW()

riskDrivers[]The at-risk count split by cause — the four At-Risk arms of compute_risk_level_for_row

topEquipment[].unitsCOUNT(*) per (category, model)

topEquipment[].atRiskCOUNT(*) FILTER (risk_level=’At Risk’) ÷ units — that model’s share, not the category’s

topEquipment[].supportStatusended = is_past_eos · announced = is_eol_notice · supported = neither · null = no linked model

otherModels{modelCount, units, atRisk}Window sums over the same CTE, minus what was emitted — the “207 other models” row

exposureCallout{manufacturer, units}The footer warning — is_past_eol AND NOT <contract on>, grouped by manufacturer, top 1

How “why units are at risk” adds upriskDrivers[]

Each asset is attributed to one driver by precedence, so the five counts sum exactly to atRisk. Precedence and display order are deliberately different.

recalllabel Open recallwins 1stshown 4th

sourcerecall_status=’Open, Classified’ AND recalls_count>0 AND not is_recall_resolved

past-eollabel Past end of supportwins 2ndshown 1st

sourceequipment_models.is_past_eol

past-useful-lifelabel Beyond useful lifewins 3rdshown 2nd

sourceage > equipment_categories.useful_life

contract-expiredlabel Contract expiredwins 4thshown 3rd

sourcecontract_expiration_date < CURRENT_DATE

otherlabel Otherwins lastshown 5th

sourceUnreachable unless the DB function gains a 5th arm

Worth noting: other is unreachable unless compute_risk_level_for_row gains a fifth arm — it exists so the bar still balances if the database rule changes before the card does.

Derived in the browser5 — no API field

At risk 58% of categoryatRisk / count

Under contract 0.4%onContract / count, one decimal

10,271 gapcount - onContract

Plan remediation ↗Not data — fires drillIntoGroup(group), the same as clicking the card

“Sorted by unit count, highest first”A caption stating the server’s existing count DESC order

the cheapest item answered the first question

The docx asked “How are these ordered?”. The sort was already correct — count DESC on the server — so the fix was a caption saying so. No query change, no new field.