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 adminBgcFactoryOpsAuth. 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.

getTrackingUrl(trackingNumber, carrierService) (src/services/workOrderShipmentLatency/trackingLink.utils.ts) is the single source of truth for tracking URLs. It dispatches on the carrier name, not on tracking-number format — deliberate, because only Yun Express has a recognizable prefix (YT); Portless and FedEx numbers are both bare digit strings and can't be told apart by shape. Empty/null tracking number short-circuits to ''; unknown/null carrier falls through to the USPS URL.

Both consumers already call it, so a new carrier branch reaches both surfaces with no further wiring:

  • list view — listActionHandler.ts:46 sets trackingNumberUrl on each BaseRecord
  • XLS export — workOrderShipmentLatency.service.ts:268 sets finalRecord.trackingHyperlink.hyperlink

Adding a carrier is four coordinated edits: (1) enum member in src/constants/carrierServices.ts, (2) URL-prefix constant + if branch in getTrackingUrl before the USPS fallback, (3) an availableValues entry on the carrierService property in workOrderShipmentLatency.ts so the multi-select filter offers it, (4) specs in trackingLink.utils.spec.ts + workOrderShipmentLatency.service.spec.ts. The filter's parse path (utils.ts:43) and SQL clause (workOrderShipmentLatency.model.ts:203) are value-agnostic — they split the comma-joined string and IN-match it, so they need no change.

Who writes carrierService: two paths, and they do not agree on vocabulary. 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:181-188) instead derives the carrier from the tracking-number prefix and can only ever write YunExpress or Portless. So a shipment's carrier can be overwritten to Portless at print time even if n8n originally sent something else. scripts/backfill-shipment-carrier.ts applies that same prefix heuristic retroactively. Consequence: adding an enum member does not make that value appear in the data — it only teaches the report how to render rows that already carry it.

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). Compare against it with === on the raw column value. It is shared well beyond the latency report: shippinglabel.service.ts, 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), and several specs.