--- REF-6065 Pipeline Health - R360 O&F Prototype

REF-6065 Pipeline Health

PROTOTYPE
Technical Shaping

Order Pipeline Health Monitoring

REF-6065 · Order Failure Detection & Monitoring

The Problem

There is no mechanism to detect orders that are stuck, lost, or failing at any pipeline stage. The team manually checks every Mirakl instance daily — a process that doesn't scale with SFB expansion, Middle East, and Reiss coming online. Recent incidents where Noatum and GWW didn't receive orders were discovered days later. REF-3442 attempted a "Marketplace Order Error Tracking System" but was canceled.

Daily
Manual checks across every Mirakl instance — time wasted on repetitive verification
0
Automated detection of stuck, lost, or failing orders
Days
Recent fulfiller incidents (Noatum/GWW) discovered late — orders never arrived

Pipeline Stages to Monitor

The order pipeline has 4 stages, each with different failure modes. This project focuses on stages 1–3. Stage 4 (dispatch/delivery) is already covered by REF-6062.

1. Ingest
→
2. Processing
→
3. Allocation
→
4. Dispatch (REF-6062)
Marketplace
→
order_source
→
orders_order
→
fulfilment
→
shipped

Stage 1: Marketplace → order_source (Ingest)

What to check

Are orders arriving from each marketplace? Compare expected order volume against actual. Query order_source table.

Failure modes

  • Marketplace API down
  • Authentication expired
  • Integration error

Detection approach

Volume anomaly detection: compare expected vs actual order volume per distributor per day. Check for zero-volume periods during business hours — immediate alert.

Integration types to cover

mirakl
·
shopify
·
tradebyte
·
bluejayXML
·
amazon
·
ebay

Active distributors (90-day order volume)

Distributor Integration Orders (90d) Avg/day
Nordstrom mirakl 37,063 ~412
Macy's mirakl 16,330 ~181
Debenhams mirakl 9,887 ~110
Nordstrom Longview mirakl 3,182 ~35
Bloomingdales mirakl 2,655 ~30
FGH D2C mirakl 736 ~8
Needle and Thread shopify 661 ~7
Anomaly thresholds: Different distributors need different sensitivity. Nordstrom at zero orders for 2 hours is a critical incident. Needle and Thread at zero orders for a day might be normal. Thresholds must be per-distributor, accounting for day-of-week patterns (weekdays vs weekends).

Stage 2: order_source → orders_order (Processing)

What to check

Are ingested orders being processed into orders? Query order_source where created_order_id IS NULL AND tries >= 3.

Failure modes

  • No host reference found
  • Duplicate order detected
  • Processing error / exception

Detection query

SELECT os.id, d.name as distributor, os.external_order_reference, os.tries, os.created_at FROM order_source os JOIN distributor d ON d.id = os.distributor_id WHERE os.created_order_id IS NULL AND os.tries >= 3 ORDER BY os.created_at ASC

This catches order sources that have been attempted multiple times but never successfully created an order. These are stuck in a retry loop and need manual investigation or a code fix.

Highest value, simplest to build. This query runs against existing data with no schema changes. A scheduled command checking this every 30 minutes would catch processing failures immediately — instead of discovering them when a brand asks "where's my order?"

Stage 3: orders_order → fulfilment (Allocation)

What to check

Are orders being sent to fulfillers? Find orders in pending or sent to dc status past expected processing time.

Failure modes

  • Fulfiller didn't receive the order
  • Allocation failure
  • Endpoint down or misconfigured

This stage catches the Noatum/GWW scenario — orders that were created in R360 but never reached the fulfiller. Detection checks for orders that have been in an early status for longer than the expected processing window.

Overlap with REF-6062: Dispatch overdue detection in REF-6062 partially covers this, but pipeline health monitoring catches it earlier — before the dispatch SLA clock has even started, at the point where the order hasn't been acknowledged by the fulfiller at all.

Stage 4: fulfilment → shipped (Dispatch)

This stage is already covered by REF-6062's dispatch SLA monitoring and EasyPost delivery tracking. Pipeline health monitoring focuses on stages 1–3 to avoid duplication.

Stages 1–3
→
REF-6065 (this project)
Stage 4
→
REF-6062 (delivery tracking & SLA)

Detection Architecture

Volume Anomaly Detection (Stage 1)

  • Calculate rolling average order volume per distributor per day-of-week (last 4 weeks)
  • Compare today's volume against the average
  • If current volume is < 50% of expected by a certain time of day, flag as anomaly
  • Different thresholds for weekdays vs weekends
  • Zero-volume during business hours = immediate alert (severity: critical)

Stuck Order Detection (Stages 2–3)

  • Scheduled command runs every 30 minutes
  • Checks for order_source records stuck in processing (tries >= 3, no order created)
  • Checks for orders stuck in early statuses past threshold
  • Results exposed via API for n8n consumption
Scheduled command (every 30m)
→
Detect issues
→
Expose via API
→
n8n: Slack alert + email

API Endpoint

GET /api/pipeline/issues Query params: distributor_id (optional) stage (optional: ingest, processing, allocation) severity (optional: critical, warning, info) Response: [ { "id": 1, "stage": "processing", "severity": "critical", "distributor": "Nordstrom", "order_ref": "NORD-384821", "detail": "order_source stuck after 3 tries — no host reference found", "detected_at": "2026-04-13T09:30:00Z", "age_hours": 4.5, "order_source_id": 928441 }, { "id": 2, "stage": "ingest", "severity": "warning", "distributor": "Macy's", "order_ref": null, "detail": "Volume 62% below expected for Sunday (expected ~90, actual 34)", "detected_at": "2026-04-13T14:00:00Z", "age_hours": 0, "order_source_id": null }, { "id": 3, "stage": "allocation", "severity": "warning", "distributor": "Debenhams", "order_ref": "DEB-19283", "detail": "Order in 'sent to dc' status for 18 hours — fulfiller may not have received", "detected_at": "2026-04-13T08:00:00Z", "age_hours": 18, "order_source_id": null } ]

n8n polls this endpoint and routes alerts based on severity: critical issues get immediate Slack alerts, warnings get included in a periodic digest.

Context: REF-3442 (Previous Attempt)

REF-3442 "Marketplace Order Error Tracking System" was created by Dotun and then canceled. It had a very detailed framework covering duplicate orders, no host references, stuck orders, and Noatum-specific issues.

Key differences in this approach

Aspect REF-3442 (canceled) REF-6065 (this project)
Scope Full error tracking system with ownership assignment Detection + API — simpler, focused on surfacing issues
Data approach Separate logging system Query existing tables (order_source, orders_order)
Notifications Custom notification system n8n handles routing (Slack/email) — no custom build
Coverage All stages including dispatch Stages 1–3 only — dispatch is handled by REF-6062

Entity Changes

No new entities required for MVP — this is read-only monitoring of existing data. The detection commands query order_source, orders_order, and distributor tables directly.

  • ■
    API endpoints expose detected issues new endpoint

    GET /api/pipeline/issues — returns all currently detected pipeline issues with stage, severity, distributor, and age.

  • •
    Scheduled detection commands new commands

    Symfony commands for stuck order detection (every 30m) and volume anomaly detection (hourly). No schema changes needed.

  • •
    n8n polls the API for alert routing n8n workflow

    n8n handles the notification layer — Slack alerts for critical, email digest for warnings. No custom notification code in R360.

Optional future entity: A pipeline_issue table could be added later if we want to track acknowledged/resolved issues (similar to return exceptions). But the MVP can start without it — each detection run produces a fresh view of current issues from live data.

Suggested Implementation Order

Each step is independently useful and can ship on its own:

  1. Stuck order_source detection no dependency
    Simplest and highest value. Scheduled command queries order_source for records with tries >= 3 and no created order. Catches processing failures immediately. Uses existing data, no schema changes.
  2. Volume anomaly detection per distributor no dependency
    Rolling average comparison per distributor per day-of-week. Catches ingest failures — API outages, authentication expiry, integration errors. Critical alert for zero-volume during business hours.
  3. Allocation monitoring no dependency
    Detect orders stuck in early statuses past expected processing time. Catches the Noatum/GWW scenario — orders created but never reaching the fulfiller.
  4. API endpoint + n8n integration depends on #1–3
    Unified /api/pipeline/issues endpoint exposing all detected issues. n8n polls and routes alerts to Slack and email based on severity.
Quick win: Step 1 (stuck order_source detection) can be built and deployed in isolation. It queries existing data with no schema changes and catches the most common processing failures immediately.

Dependencies

REF-6062

Handles stage 4 (dispatch monitoring + delivery tracking). No overlap needed — pipeline health focuses on stages 1–3.

REF-6064

Provides the pipeline health dashboard UI. Consumes the /api/pipeline/issues endpoint to display current issues visually.

Refined 360° · REF-6065 · April 2026