Return Exception Detection + Tracking
REF-6063 · Delivery Tracking, SLA Monitoring & Returns Exceptions
The Problem
Returns delivered to the DC but never processed — no visibility until someone manually checks weeks later. Partial receipts where the DC receives 2 of 4 items go completely undetected — the remaining items are lost. There are 3,203 stale RMAs that were never receipted and 137 partial receipts where the DC skipped lines. No alert, no follow-up.
Exception Types
Four types of return exception, each detected differently:
| Type | What It Means | Detection Method | Trigger |
|---|---|---|---|
| Partial receipt | DC processes a receipt batch but some items are missing | Event-driven | Compare received quantities against RMA line quantities when receipt batch is processed |
| No receipt after delivery | EasyPost confirms return delivered to DC, but no receipt batch arrives within 48 hours | Event + timer | Depends on return tracking from REF-6062. Key connection to EasyPost integration. |
| Aging unreceipted | RMA exists but has no receipt and no delivery confirmation. Per-distributor thresholds. | Scheduled | Daily check. Nordstrom: 30 days. Macy's: 10 days. Uses distributor.returns_config. |
| Customer return overdue | Customer was given a return label but tracking shows no movement | Scheduled | Lower priority. Label issued but no carrier scan after X days. |
delivered_to_dc_at field added by the EasyPost return tracking in REF-6062. Without that field,
we can only detect aging unreceipted RMAs (weeks later). With it, we catch problems at 48 hours instead of 30 days.
ReturnException Entity
New entity with lifecycle: DETECTED → ACKNOWLEDGED → FLAGGED_DC → FLAGGED_PLATFORM → RESOLVED
return_exception table
return_exception_activity table
Activity log for the exception management UI — tracks every action taken on an exception.
Detection Logic
1. Partial Receipt (event-driven)
Detected immediately when a receipt batch is processed. Compare received quantities against RMA line quantities.
2. No Receipt After Delivery (event + timer)
The connection point to REF-6062's return tracking. When EasyPost confirms delivery to the DC,
the delivered_to_dc_at field is set (by REF-6062). A scheduled check then looks for
RMAs where delivery was confirmed but no receipt batch arrived within 48 hours.
3. Aging Unreceipted (scheduled)
Daily command finds RMAs past their threshold with no receipt and no delivery confirmation.
Per-distributor thresholds stored in the existing distributor.returns_config JSON field.
Per-distributor thresholds from distributor.returns_config:
Nordstrom
30 days receipt threshold. Customer returns volume is high; longer window accounts for batch processing.
Macy's
10 days receipt threshold. Tighter turnaround expected. Faster escalation needed.
Lifecycle & Escalation
Each exception follows a status flow with automatic escalation timers. Manual actions advance the status, but if no action is taken, auto-escalation kicks in.
| Status | What It Means | How You Get Here | Auto-escalation |
|---|---|---|---|
| DETECTED | Exception created, appears in the list | System detects the exception automatically | — |
| ACKNOWLEDGED | Someone has seen it and is investigating | Manual action — user clicks "Acknowledge" | — |
| FLAGGED_DC | Escalated to the DC / warehouse team | Manual action, or auto after 48h from detection if not acknowledged | 48h from DETECTED (configurable per distributor) |
| FLAGGED_PLATFORM | Escalated to the marketplace platform | Manual action, or auto after 48h from DC flag if unresolved | 48h from FLAGGED_DC (configurable per distributor) |
| RESOLVED | Closed with resolution notes | Manual action — user resolves with notes explaining outcome | — |
distributor.returns_config.
The escalation_to_dc_hours and escalation_to_platform_hours fields control
how long the system waits before automatically advancing the status. A scheduled command checks for
exceptions that have exceeded their timer and advances them.
Dependencies
-
REF-6062: EasyPost Return Tracking dependency
Provides
delivered_to_dc_aton RMA, which powers the "no receipt after delivery" exception type. Without REF-6062, only partial receipt and aging unreceipted detection are possible. -
REF-6064: Exception Admin UI dependency
Exception list view and detail view for managing the lifecycle — acknowledge, escalate, resolve. Without the UI, exceptions are detected but not actionable.
-
distributor.returns_config populate existing
JSON field already exists on the distributor table but is currently empty. Needs populating with per-distributor receipt thresholds and escalation timers.
Entity Changes Summary
| Entity / Table | Change | Detail |
|---|---|---|
return_exception |
new table | Exception entity with type, status lifecycle, RMA FK, timestamps, resolution notes. Core table for all exception tracking. |
return_exception_activity |
new table | Activity log for exception management. Tracks every action (acknowledge, flag, contact, resolve) with user and timestamp. |
distributor.returns_config |
populate existing | JSON field exists but is empty. Populate with receipt_threshold_days, escalation_to_dc_hours, escalation_to_platform_hours per distributor. |
return_merchandise_authorization |
new columns (REF-6062) | is_delivered_to_dc (bool), delivered_to_dc_at (datetime), receipt_deadline (datetime). Added by REF-6062, consumed by this ticket's detection logic. |
Implementation Order
Phased so that each step delivers value independently:
-
ReturnException entity + partial receipt detection no external dependency
Createreturn_exceptionandreturn_exception_activitytables. Implement event-driven partial receipt detection — fires when a receipt batch is processed and quantities don't match. Delivers immediate value for the 137 known partial receipt cases. -
Aging unreceipted detection no external dependency
Daily scheduled command. Populatedistributor.returns_configwith per-distributor thresholds. Catches the 3,203 stale RMAs and any future ones. No EasyPost dependency. -
No receipt after delivery detection depends on REF-6062
Scheduled check for RMAs wheredelivered_to_dc_atis set but no receipt within 48h. Requires REF-6062's EasyPost return tracking to be live. This is the highest-value detection type — catches problems at 48 hours instead of 30 days. -
Auto-escalation timers no external dependency
Scheduled command to advance exception status when escalation timers expire. Ensures exceptions don't sit in DETECTED forever. Configurable per distributor viareturns_config.