Carrier Table & Admin-Managed Tracking URLs

Admin-managed Carrier table that replaces the hardcoded tracking-URL dispatch in the latency report. Maps Fulfil's carrierService spellings to editable tracking-URL templates, seeded from live shipments and kept in sync by the shipment-create reconcile.

Replaces the old getTrackingUrl hardcoded branches (Yun Express → yuntrack, FedEx → fedex.com, everything else → USPS). The dispatch now lives in the DB, so adding/renaming a carrier is an AdminJS edit, not a code change.

Schema (prisma/schema.prisma:430)

Field	Type	Notes
carrierId	Int (PK)	autoincrement
carrierKey	VarChar(30) @unique	Normalized join key: trim + lowercase of the Fulfil carrier name. Immutable — admin renames relabel carrierName, never re-point this key.
carrierName	VarChar(30)	Human display name; isTitle in AdminJS.
trackingUrl	Text?	URL template. null/blank renders tracking numbers as plain text. http/https only.
lastSeenAt	Timestamp(6)?	Stamped when the carrier is created/revived by the reconcile, or by the backfill migration.
isActive	Boolean default true	Inactive carriers drop out of findActiveCarrierMap (no tracking link).
createdAt/updatedAt/createdBy/updatedBy	audit	autoUpdateInfoExtension covers updates, not creation (creation stamps manually — see below).
isDeleted/deletedAt/deletedBy	soft-delete	Carrier is registered in softDelExtension's includeModels.

Indexes: unique on carrierKey; non-unique on isDeleted and carrierName.

Key concept: carrierKey is the join key, not carrierId

CustomerShipment.carrierService (VarChar(30)) stores the verbatim Fulfil carrier name (e.g. 'FedEx V2', 'Yun Express', 'Portless'). The Carrier row is matched by normalizing both sides with normalizeCarrierKey() (services/workOrderShipmentLatency/trackingLink.utils.ts:9-11 — trim + toLowerCase). So 'FedEx V2', 'fedex v2', ' FedEx V2 ' all resolve to one carrierKey: 'fedex v2'.

Because the key is derived from the name, a Carrier is live the moment Fulfil sends the matching name — createWithRevive derives carrierKey from carrierName with the same normalizer.

Writers

Writer	Location	Effect
AdminJS new action	routers/admin/resources/carrier/carrier.ts:113-128 (_newActionHandler) → CarrierService.createWithRevive	Creates a row, or revives a soft-deleted one. Derives carrierKey, stamps lastSeenAt + createdBy/updatedBy manually.
AdminJS edit action	routers/admin/resources/carrier/carrier.ts (default edit + after cache hook)	Relabels carrierName / edits trackingUrl / isActive. carrierKey is stripped from the payload (_carrierBeforeEditHook, line 106-114).
Shipment-create reconcile	services/carrier/carrier.service.ts:63-84 (reconcileAfterShipmentCreate), fired fire-and-forget from shipment.controller.ts:190	Ensures a live row for every distinct carrierService seen in the batch: no row → create; soft-deleted → revive; active → nothing.
Migration seed	prisma/migrations/20260923103831_add_carrier_table/migration.sql	Seeds yun express, fedex, portless (Portless kept per INFRA-673 open Q1). lastSeenAt left NULL.
Backfill migration	prisma/migrations/20260924010000_reconcile_carriers/migration.sql	One-time: INSERT ... SELECT DISTINCT lower(trim(carrierService)) FROM CustomerShipment WHERE isDeleted = false with ON CONFLICT DO UPDATE SET lastSeenAt = NOW(). Soft-deleted rows are not resurrected (the unique index still holds their key).

createWithRevive — the P2002 dodge (services/carrier/carrier.service.ts:24-51)

carrierKey is unique, but soft-deleted rows still occupy the unique index (soft delete only sets isDeleted, it does not free the key). A straight create for a previously-deleted key would throw P2002. createWithRevive first checks CarrierModel.findSoftDeletedFirst({ carrierKey }) and, if found, calls revive instead — resetting isDeleted/deletedAt/deletedBy and stamping lastSeenAt.

Note createWithRevive stamps createdBy/updatedBy from requestContextStorage manually on the create branch, because autoUpdateInfoExtension does not cover create.

Readers

Reader	Location	What it loads
findActiveCarrierMap	models/carrier/carrier.model.ts:44-62	carrierKey → trackingUrl map for active carriers, cached under carrier:map. Feeds resolveTrackingUrl.
findAllIncludingDeleted	models/carrier/carrier.model.ts:141-155	All rows (incl. soft-deleted), cached under carrier:all. Feeds the reconcile.
findMany	models/carrier/carrier.model.ts:64-70	Plain Prisma reader (auto-filters isDeleted = false via softDelExtension). Used by admin list + carrierOptions action.
carrierOptions action	routers/admin/resources/carrier/carrier.ts:142-155	{ carrierKey, carrierName } for every active carrier, ordered by name. Drives the latency report's carrier filter dropdown.

Cache layout

Two cache keys share the carrier: prefix, so the existing write paths (create/update/del/ deleteMany/revive, each calling cacheInvalidate('carrier:')) invalidate both at once:

• carrier:map — the active-carrier map (per-request load for tracking-URL resolution).
• carrier:all — the full include-deleted list (per-reconcile load).

findActiveCarrierMap filters where: { isActive: true } (in addition to the extension's isDeleted = false), so deactivating a carrier removes its tracking link without deleting the row.

Soft-delete & revive — the $queryRaw bypass gotcha

softDelExtension auto-filters isDeleted = false on carrier.findMany/findUnique/count, and converts carrier.delete/deleteMany to soft updates. It does not intercept $queryRaw or update. Three consequences, all load-bearing:

• findSoftDeletedFirst (models/carrier/carrier.model.ts:118-130) uses $queryRaw with an explicit "isDeleted" = true filter — a model-method findFirst could never see soft-deleted rows.
• findAllIncludingDeleted (models/carrier/carrier.model.ts:141-155) uses $queryRaw so the reconcile can see (and revive) soft-deleted rows instead of tripping P2002.
• revive (models/carrier/carrier.model.ts:164-176) uses carrier.update (not intercepted) to reset the soft-delete bits directly on a row whose isDeleted is true.

Tracking-URL template & XSS

trackingUrl is interpolated into an <a href> on the latency report (and into XLS hyperlinks). It must be an http/https URL. Two layers of defence:

1. Save-time — _validateTrackingUrl (routers/admin/resources/carrier/carrier.ts:87-95) rejects javascript: etc. on both new (_carrierBeforeNewHook) and edit (_carrierBeforeEditHook).
2. Resolve-time — isHttpUrl (services/workOrderShipmentLatency/trackingLink.utils.ts:40-47) re-validates the scheme on every resolution, because save-time validation alone is one bad migration/seed away from stored XSS.

The template uses the {tracking_number} placeholder; buildTrackingUrl substitutes the URL-encoded number, or appends it if the placeholder is absent. See latency-report.md for the resolution flow.

Admin UI

• Resource registered in routers/admin/admin.router.ts under parent System Administration (adminParent in routers/admin/resources/adminParent.ts).
• carrierKey is visible but never editable; isDeleted/deletedAt/deletedBy are hidden.
• listProperties: carrierName, trackingUrl, lastSeenAt, isActive.
• list uses a custom listActionHandler (full list in one page; the table is small and kept complete by the reconcile, so no read-path reconciliation).
• All CRUD actions are adminRoleAuth-gated except carrierOptions, which is deliberately unauthenticated (no isAccessible restriction) because the latency report's carrier filter is loaded by editor/viewer roles.
• The CarrierMultiSelectFilter component (src/components/properties/CarrierMultiSelectFilter/) fetches options from carrierOptions and joins multi-select values with , for the filter.

See also: latency-report.md, shipment-create.md.