--- REF-6062 Delivery Tracking - R360 O&F Prototype

REF-6062 Delivery Tracking

PROTOTYPE
Technical Shaping

Delivery Tracking + SLA Monitoring

REF-6062 · Delivery Tracking, SLA Monitoring & Returns Exceptions

The Problem

There is no mechanism to know whether deliveries are on time, no way to measure SLA compliance, and no automated chaser process for overdue dispatches. The team checks every fulfiller every morning manually.

6.7 days
Average Macy's delivery time (SLA: 3–5 days) — invisible today
6 emails
Manual chaser emails sent daily by Anja for overdue dispatches
0
Fulfilments with delivered_at — field doesn't exist yet

What's missing from the data model

  • No delivered_at or is_delivered on Fulfilment — we know when something shipped (via FulfilmentConfirmation.createdAt) but never when it arrived
  • No per-distributor SLA config — dispatch and delivery windows are assumed, not stored
  • No tracking event history — tracking numbers exist on fulfilments but we never query the carrier for status updates

What Changes

  • ⚙
    SLA config on Distributor new field

    Add an SLA block to distributor.config JSON. Per-distributor dispatch windows, delivery windows, timezone, working days, urgency tier. Populated from Anja's spreadsheet.

  • ⚙
    Delivery fields on Fulfilment new columns

    Add is_delivered (bool) and delivered_at (datetime) to the Fulfilment entity. Set via EasyPost webhook when carrier confirms delivery.

  • ⚙
    Display name on Endpoint new column

    Add display_name (varchar, nullable) to endpoint table. Falls back to name if null. Maps internal names (e.g. "Refined US Fulfilment") to display names (e.g. "GWW").

  • ■
    EasyPost integration new service

    Shared tracker service: register trackers, receive webhooks, store tracking events. Auto-detects carrier from tracking number. Single integration covers UPS, FedEx, USPS, OnTrac, Royal Mail, Evri.

  • ■
    SLA evaluation service new service

    Compare delivery timeline against distributor SLA config. Calculate dispatch and delivery lateness. Weekend-aware deadline calculation.

  • •
    Dispatch overdue detection scheduled command

    Hourly command: find fulfilments past their dispatch SLA window without shipment confirmation. Expose via API for n8n consumption (chaser emails + Slack alerts).

How EasyPost Integration Works

EasyPost is a shipping API that provides unified tracking across carriers. You register a tracking number, EasyPost auto-detects the carrier and sends webhook events as the status changes. One integration replaces per-carrier tracking APIs.

Carrier coverage

All active R360 carriers are supported by EasyPost:

R360 Carrier EasyPost Carrier Code Region Volume (90d)
UPSUPSUPSN-CG / UPSNUS39,693
OnTracOnTracONTRACUS11,581
Royal MailRoyalMailTPSTN16 / TPNTN16UK7,453
FedExFedExFDEGUS6,008
EvriHermes (UK)SDHUK4,136
USPSUSPSUSPSUS2,764

Two directions of tracking

EasyPost tracks both outbound deliveries and return shipments. The same integration handles both — we just register different tracking numbers at different points in the lifecycle.

Direction Source Tracking field Register when Key event Action on delivery
Outbound Fulfilment fulfilment.tracking_number Fulfilment → shipped delivered Set delivered_at, evaluate delivery SLA
Return RMA rma.tracking_number RMA created with tracking number delivered Start 48h receipt timer, alert warehouse team
Why returns tracking matters: The warehouse team has 48 hours from when a return is delivered to the DC to receipt it. Without tracking the return delivery, we can only detect aging unreceipted RMAs (weeks later). With it, we can flag "this was delivered to the DC 2 days ago and nobody processed it" — that's the difference between catching a problem at 48 hours vs discovering it at 30 days.

Registration flows

Outbound (customer delivery):

Fulfilment ships
→
Tracking number set
→
Register with EasyPost
→
Webhooks flow in
→
Delivery confirmed → SLA eval

Return (DC delivery):

RMA created
→
Return tracking number
→
Register with EasyPost
→
Webhooks flow in
→
Delivered to DC → 48h timer starts

EasyPost auto-detects the carrier from the tracking number format in both cases — no need to pass the carrier explicitly.

Webhook events

EasyPost sends a webhook on every status change. Actions differ by direction:

EasyPost Status What It Means Outbound Action Return Action
pre_transit Label created, not yet picked up Store event Store event
in_transit Carrier has the package Store event Store event
out_for_delivery On the delivery vehicle Store event Store event (arriving at DC)
delivered Confirmed delivered Set delivered_at on Fulfilment. Trigger SLA evaluation. Set delivered_at on RMA. Start 48h receipt timer. Alert warehouse.
return_to_sender Package being returned to origin Store event. Flag for investigation. Store event. Flag — return not reaching DC.
failure Delivery attempt failed Store event. Flag for investigation. Store event. Flag — DC delivery failed.

Delivery strategy: webhooks + polling fallback

Webhooks are the primary mechanism — we get events within minutes. A scheduled poll runs as a safety net to catch any missed webhooks (EasyPost webhook delivery isn't guaranteed).

Primary: Webhooks

  • Single endpoint: POST /api/webhooks/easypost
  • Verifies webhook signature
  • Idempotent (dedup on tracking_number + status + occurred_at)
  • Processed async via message queue
  • Near real-time event delivery

Fallback: Scheduled Poll

  • Runs every 2 hours
  • Queries EasyPost for all active (undelivered) trackers
  • Catches anything the webhook missed
  • Same processing logic — idempotent, so duplicates are harmless
  • Also useful for on-demand "check now" scenarios

New entity: TrackingEvent

Stores the full event history from EasyPost. Both outbound fulfilments and return RMAs share this table.

tracking_event id INT (PK) tracking_number VARCHAR(255) trackable_type ENUM('fulfilment', 'rma') -- polymorphic: what we're tracking trackable_id INT -- FK to fulfilment.id or rma.id carrier VARCHAR(64) -- auto-detected by EasyPost status VARCHAR(64) -- pre_transit, in_transit, delivered, etc. detail TEXT -- human-readable description from carrier location VARCHAR(255) -- city, state direction ENUM('OUTBOUND', 'RETURN') occurred_at DATETIME -- when the event happened (carrier time) created_at DATETIME -- when we received it source ENUM('webhook', 'poll') -- how we got this event
Volume estimate: With ~25,700 shipped fulfilments and ~10,000 RMAs per month (Nordstrom + Nordstrom Longview with tracking numbers), that's ~35,700 trackers registered per month. At ~6 events per shipment, roughly 7,000 webhooks per day. The fallback poll would check ~12,000 active trackers every 2 hours. Both are comfortably within EasyPost standard plan limits.

New fields on RMA

The return_merchandise_authorization table already has tracking_number. We need to add delivery tracking fields:

return_merchandise_authorization + is_delivered_to_dc BOOL (default false) + delivered_to_dc_at DATETIME (nullable) + receipt_deadline DATETIME (nullable) -- delivered_to_dc_at + 48h

When EasyPost confirms delivery, we set these fields and the receipt timer begins. The return exception detection (REF-6063) checks for RMAs where is_delivered_to_dc = true and receipt_deadline has passed without a corresponding receipt batch.

SLA Configuration

SLA calculation is more nuanced than simple hours/days. An order at 9am and 23:59 shouldn't be treated the same. Weekends and bank holidays differ by region. Some marketplaces measure delivery from order placed (end-to-end), others from dispatch. The config needs to handle all of this.

Two layers of config

1. Regional working day calendars — system-level, managed by ops. Stored in a new sla_region table (or a shared config). Defines what counts as a working day per region.

// sla_region table { "code": "US", "timezone": "US/Eastern", "working_days": [1, 2, 3, 4, 5], // 1=Mon, 7=Sun "holidays": [ "2026-01-01", // New Year "2026-01-19", // MLK Day "2026-05-25", // Memorial Day "2026-07-04", // Independence Day "2026-09-07", // Labor Day "2026-11-26", // Thanksgiving "2026-12-25" // Christmas ] } { "code": "UK", "timezone": "Europe/London", "working_days": [1, 2, 3, 4, 5], "holidays": [ "2026-01-01", // New Year "2026-04-03", // Good Friday "2026-04-06", // Easter Monday "2026-05-04", // Early May "2026-05-25", // Spring Bank "2026-08-31", // Summer Bank "2026-12-25", // Christmas "2026-12-28" // Boxing Day (substitute) ] }

2. Per-distributor SLA config — on distributor.config JSON. References the regional calendar.

{ "sla": { "dispatch": { "window": 1, // number of units "unit": "working_days", // working_days | calendar_days | hours "cutoff_time": "14:00", // orders after this count as next day "region": "US" // which working day calendar to use }, "delivery": { "window": 5, // number of units "unit": "calendar_days", // working_days | calendar_days "measured_from": "order_placed", // order_placed | dispatch "region": "US" }, "chaser_delay_hours": 4, // delay after SLA breach before first chaser "urgency_tier": "high" // high | standard | low } }

How the cut-off works

The cutoff_time determines when the dispatch clock starts. Orders placed after the cut-off are treated as if they arrived the next working day.

Order placed Cut-off Counts as Dispatch SLA (1 working day) Deadline
Friday 09:00 14:00 Friday +1 working day Monday end of day
Friday 16:30 14:00 Monday (next working day) +1 working day Tuesday end of day
Friday 23:59 14:00 Monday (next working day) +1 working day Tuesday end of day
Saturday 10:00 14:00 Monday (next working day) +1 working day Tuesday end of day
Thursday 15:00 (before Good Friday) 14:00 Tuesday (next working day, skip Fri+Mon) +1 working day Wednesday end of day

Real distributor examples

Distributor Dispatch SLA Cut-off Delivery SLA Delivery measured from
Nordstrom 1 working day (US) 14:00 ET 5 calendar days Order placed
Macy's 1 working day (US) 14:00 ET 3–5 calendar days Order placed
Debenhams 2 working days (UK) 17:00 GMT 7 working days Dispatch
Bloomingdales 1 working day (US) 14:00 ET 5 calendar days Order placed

Values are illustrative — Anja's spreadsheet has the actual SLAs per distributor.

Dispatch SLA

Time from order received to fulfilment shipped. Measured from order.ordered_at (adjusted by cut-off) to FulfilmentConfirmation.createdAt.

Uses the regional calendar for working day calculation. Cut-off time determines which "day" an order belongs to.

Delivery SLA

Time from order placed (or dispatch) to customer delivery. Measured against fulfilment.delivered_at (from EasyPost).

measured_from controls whether the clock starts at order placement (end-to-end marketplace SLA) or dispatch (carrier transit SLA).

Urgency Tier

  • High: immediate Slack alert when overdue
  • Standard: included in daily digest
  • Low: weekly report only

Chaser Delay

Hours after the dispatch SLA breaches before the first chaser email is sent via n8n. Prevents chasing within minutes of the deadline.

Admin UI: The SLA config screen (REF-6064) will need two sections: (1) per-distributor SLA settings with dispatch/delivery config, cut-off times, and unit selection, and (2) regional working day calendar management with holiday editing. See prototype.

Dispatch Overdue Detection

Hourly scheduled command
→
Find overdue fulfilments
→
Expose via API
→
n8n: chaser email + Slack

How it works

  1. Scheduled Symfony command runs hourly
  2. Queries fulfilments in sent to dc or pending status
  3. For each, looks up the distributor's SLA config and regional calendar
  4. Determines which "dispatch day" the order belongs to using ordered_at + cutoff_time
  5. Calculates the dispatch deadline by adding dispatch.window working/calendar days (respecting regional holidays)
  6. If current time > deadline + chaser_delay_hours, the fulfilment is overdue
  7. Results exposed via GET /api/fulfilments/overdue

API endpoint

GET /api/fulfilments/overdue Query params: distributor_id (optional) endpoint_id (optional) urgency (optional: high, standard, low) Response: [ { "order_number": "ORD-18442", "external_ref": "MACYS-92301", "distributor": "Macy's", "endpoint": "GWW", "ordered_at": "2026-04-09T08:15:00Z", "dispatch_deadline": "2026-04-10T08:15:00Z", "hours_overdue": 32.5, "items": 3, "urgency": "high" }, ... ]

n8n polls this endpoint and routes alerts based on urgency tier: high-urgency distributors get immediate Slack alerts, standard ones get included in a daily digest email to the ops team.

Delivery SLA Evaluation

Triggered automatically when EasyPost sends a delivered webhook. Compares the actual delivery timeline against the distributor's SLA config.

EasyPost: delivered
→
Set delivered_at
→
Calculate lateness
→
Store result

Calculation

delivery_config = distributor.config.sla.delivery region = sla_regions[delivery_config.region] // Determine the start point if delivery_config.measured_from == "order_placed": start_time = order.ordered_at else: start_time = fulfilment.confirmations.earliest.createdAt // ship time // Calculate elapsed time in the configured unit delivery_time = fulfilment.delivered_at // from EasyPost webhook if delivery_config.unit == "working_days": elapsed = working_days_between(start_time, delivery_time, region) else: elapsed = calendar_days_between(start_time, delivery_time) lateness = elapsed - delivery_config.window // positive = late // Note: an order can have multiple fulfilments, each shipped // at different times. SLA is evaluated per-fulfilment, not per-order.

This powers the Delivery Lateness tab in the prototype, the fulfiller KPI dashboard (on-time delivery rate), and the orders dashboard lateness filter (REF-6064).

Note: Delivery SLA evaluation only works once EasyPost is integrated and sending delivered webhooks. Dispatch overdue detection works immediately with existing data (no EasyPost dependency). This means we can ship dispatch monitoring first, then layer in delivery tracking.

Entity Changes Summary

Entity / Table Change Detail
Fulfilment new columns is_delivered (bool, default false), delivered_at (datetime, nullable)
distributor.config new JSON key Add sla block with dispatch config (window, unit, cutoff_time, region), delivery config (window, unit, measured_from, region), chaser delay, urgency tier
sla_region new table Regional working day calendars: code, timezone, working_days (array), holidays (date list). Managed via admin UI.
endpoint new column display_name (varchar, nullable) for human-friendly names in UI
return_merchandise_authorization new columns is_delivered_to_dc (bool), delivered_to_dc_at (datetime), receipt_deadline (datetime). Set via EasyPost when return delivered to DC.
tracking_event new table Polymorphic: tracks both fulfilments (outbound) and RMAs (returns). Status, carrier, location, timestamp, source (webhook/poll).

Suggested Implementation Order

These can be done in phases — each is independently useful:

  1. SLA config + regional calendars + endpoint display names no external dependency
    Create sla_region table with US/UK calendars and holidays. Add SLA block to distributor config with dispatch/delivery sub-configs, cut-off times, and unit types. Populate from Anja's spreadsheet. Add display_name to endpoints. This unlocks dispatch overdue detection immediately.
  2. Dispatch overdue detection no external dependency
    Scheduled command + API endpoint. Replaces Anja's manual morning checks. n8n consumes the API for chaser emails. Can ship this before EasyPost is integrated.
  3. EasyPost integration + TrackingEvent entity external: EasyPost account
    Register trackers on ship, webhook controller, event storage. Needs an EasyPost account and API key. Delivery fields on fulfilment.
  4. Delivery SLA evaluation depends on #3
    Triggered by delivered webhook. Powers the KPI dashboard and lateness views.
Quick win: Steps 1 and 2 can ship independently and deliver immediate value (replacing manual chaser process) without waiting for EasyPost. The EasyPost integration then layers on top for delivery tracking.
Refined 360° · REF-6062 · April 2026