Skip to main content

Work Order Shipment Latency Report

Virtual AdminJS report resource: raw-SQL CTE, aging buckets, filter dispatch, XLS export.

Deep-dive doc split out of .claude/rules/architecture.md (which is now an index). Append new findings about this area here, not to the index.

Work Order Shipment Latency Report (workOrderShipmentLatency domain)​

A read-only "report" surface — virtual AdminJS resource, no Prisma model; backed by a raw-SQL CTE that joins WorkOrder + CustomerShipment + PurchaseOrder and computes aging.

  • Admin resource: src/routers/admin/resources/workOrderShipmentLatency/workOrderShipmentLatency.ts — isVirtual: true, name: 'WorkOrderShipmentLatency'. Declares properties, filterProperties, listProperties, and custom action handlers (listActionHandler, exportExcelActionHandler). Visibility gated by adminEditorViewerRoleAuth (role-based since INFRA-666; previously the adminBgcFactoryOpsAuth email allowlist). Filter UI components are picked from the shared Components registry (POMultiSelectFilter, DatetimeFilter, MultiSelectFilter, AgingDaysProperty).

  • Filter dispatch lives in the model (src/models/workOrderShipmentLatency/workOrderShipmentLatency.model.ts): _makeWhereClause(where) builds a Prisma.Sql array — each filter (poIds, workOrderStatus, labelStatus, estimatedShipDate.{from,to}, poCreateDate.{from,to}, shipmentWorkOrder, agingDays) appends its own clause. Adding a new filter is: (1) add to the property list with a filter component, (2) extend the Where type, (3) add a branch in _makeWhereClause, (4) add to filterProperties.

  • Aging is computed in SQL (not in JS) via CASE on cs.labelStatus — Printed aging = labelPrintedAt - estimatedShipDate, Pending/Acknowledged aging = now - estimatedShipDate, else 0. Bucket filtering (urgent/watch/safe) is applied in an outer query because the buckets reference the computed agingDays column.

  • Sort safety: _makeOrderByClause uses Prisma.raw (Prisma doesn't parameterize ORDER BY). The _canSortKeys whitelist Set guards against injection — never bypass it.

  • Factory scoping: model reads getSelectedFactoryId() from requestContext directly (raw SQL bypasses selectedFactoryExtension), and adds wo."factoryId" = $id to the CTE conditions when a session-selected factory exists.

  • XLS export (src/services/workOrderShipmentLatency/workOrderShipmentLatency.service.ts:makeExportExcel): builds an ExcelJS.Workbook via list2Excel(rows, columns). Column headers are hardcoded Chinese today; status enums are mapped through _workOrderStatus2CN / _labelStatus2CN for display. The language query param IS read by exportExcelActionHandler and threaded into makeWorkOrderShipmentLatencyWhereCondition (used for the labelStatus.optionLanguageFilter), but it is not currently passed through to makeExportExcel, so column headers don't switch on locale. Adding bilingual export means piping language from controller → service and parameterizing both column headers and the status-label maps.

  • Tracking-number column is rendered via Components.LinkProperty with custom.linkField: 'trackingNumberUrl'.

  • API endpoint for XLS download: GET /api/workOrderShipmentLatency/v1/exportExcel (session-authed, controller exportExcel). Frontend opens this URL in a new tab; the controller pipes WorkOrderShipmentLatencyService.makeExportExcel(...) through sendExcelFile(res, workbook, 'latency-dashboard-export-<date>.xlsx').

  • Frontend two-step export flow: clicking the toolbar Export button triggers a server action via apiClient.resourceAction that returns { where, sortBys }. The component (src/components/actions/ExportWorkOrderShipmentLatency/ExportWorkOrderShipmentLatency.tsx) then builds a /api/.../exportExcel?data=<encoded>&timezoneOffsetHours=<n> URL and clicks an anchor to trigger the download in a new tab. This two-step indirection lets the same filter URL state survive into the new tab.

  • Dead code — completeShipmentsTracking (workOrderShipmentLatency.service.ts:31, exposed as WorkOrderShipmentLatencyService.completeShipmentsTrackingLogError via logErrorServiceFunc). It fetches missing tracking numbers from Fulfil (fulfilGetShipmentsTracking), mutates the passed-in list in place, and persists via ShipmentService.updateTrackingNumbers. Nothing in src/ or scripts/ calls it — the only references are in workOrderShipmentLatency.service.spec.ts (~7 tests). Tracking capture happens instead at print-label time (shippinglabel.service.ts#_retrieveTrackingNumber) and in scripts/backfill-shipment-tracking.ts. Note it uses updateTrackingNumbers (tracking only) while the live print-label path uses updateTrackingNumberCarriers (tracking + carrier), so it is stale in behaviour as well as unreachable. Removing it must also delete its describe block, or the spec fails to import.

As of INFRA-673 the tracking URL is admin-managed, not hardcoded. resolveTrackingUrl(trackingNumber, carrierService, carrierMap) (src/services/workOrderShipmentLatency/trackingLink.utils.ts:23-32) replaced the old getTrackingUrl (which had hardcoded Yun Express/FedEx/USPS branches). It is pure + synchronous: the caller loads the carrier map once per request, then the per-row resolution is a Map.get + string build — no N+1.

  • The map is CarrierModel.findActiveCarrierMap() (models/carrier/carrier.model.ts:44-62), cached under carrier:map.
  • Dispatch is on the normalized carrier name (normalizeCarrierKey = trim + lowercase), matching CustomerShipment.carrierService against the Carrier.carrierKey join key. Empty/null tracking number or an absent carrier row short-circuits to '' (plain text).
  • The template uses the {tracking_number} placeholder (buildTrackingUrl, line 34-38); a URL without it falls back to url + encodedTrackingNumber. isHttpUrl (line 40-47) re-validates http/https at resolve time.

Both consumers load the map once per request and call resolveTrackingUrl per row:

  • list view — listActionHandler.ts:43 loads the map, :50 sets trackingNumberUrl on each BaseRecord
  • XLS export — workOrderShipmentLatency.service.ts:249 (buffered makeExportExcel) and :336 (streaming) load the map; transformRecordToExportItem (:197-218) sets finalRecord.trackingHyperlink.hyperlink

Adding/renaming a carrier is now an AdminJS edit (System Administration → Carrier), not a code change — no enum, no availableValues, no spec edits. The full Carrier table mechanics (writers/readers/cache/revive/XSS) are documented in carrier.md.

The carrier filter changed with it: _makeWhereClause now matches on the normalized column — lower(trim(cs."carrierService")) IN (...) (workOrderShipmentLatency.model.ts:225) — and the carrierService property's hardcoded availableValues/MultiSelectFilter were dropped in favour of Components.CarrierMultiSelectFilter (workOrderShipmentLatency.ts:160). That component fetches real Fulfil spellings (e.g. 'FedEx V2') from the Carrier resource's carrierOptions action, so the filter options are driven by the admin-managed table, not a code enum.

Who writes CustomerShipment.carrierService: two live paths plus a script, and they do not agree on vocabulary — the column holds free-form Fulfil names, which is exactly why the Carrier table keys on normalized free text rather than an enum:

  • The n8n shipment-create payload passes the carrier through verbatim (shipment.service.ts → carrierService: shpmnt.carrierService, Zod-capped at 30 chars in schemas/shipment/index.ts) — this is how non-enum values like FedEx/UPS/USPS land in the column.
  • The print-label capture path (shippinglabel.service.ts:185-189) resolves the carrier as: empty/null tracking → null; YT-prefixed tracking → CarrierService.YunExpress; otherwise → res.data[0]['carrier.rec_name'] ?? null (the actual Fulfil carrier name, since INFRA-487). So a shipment's carrier is whatever Fulfil reports at label time, which can overwrite the n8n-sent value.
  • scripts/backfill-shipment-carrier.ts applies the older prefix heuristic retroactively (YT% → YunExpress, else → Portless).

Carrier enum (src/constants/carrierServices.ts)​

CarrierService is a plain string enum whose values are the exact strings stored in CustomerShipment.carrierService (VarChar(30)), not slugs — e.g. YunExpress = 'Yun Express' (with the space). Since INFRA-673 it is no longer used by the latency report or trackingLink.utils.ts — those go through the Carrier table instead. It survives in two remaining consumers: shippinglabel.service.ts:188 (the print-label YT% → YunExpress branch) and scripts/backfill-shipment-carrier.ts (which uses Object.values(CarrierService) to detect non-enum rows — so adding a member narrows that script's backfill target set). Compare against it with === on the raw column value.