Amazon Order Sync Health

An operational heartbeat that confirms Amazon orders are still flowing into ERPNext and surfaces sync failures hour by hour.

Purpose

On-call/ops staff confirm the Amazon order-sync pipeline is alive (orders arriving, failures not piling up).
Compare, per hour, how many Amazon Sales Orders were created vs how many Failed Sync Records were logged.
Two alarm indicators flag an open backlog of failed syncs and a suspicious gap since the last order.

How to Access

Desk > Reports > Amazon Order Sync Health, or /app/query-report/Amazon%20Order%20Sync%20Health. Report type: Script Report. Reference DocType: Sales Order.

Filters

Filter	Type	Required	Description
Hours Back	Int	No	Size of the look-back window in hours. Defaults to 48; `window_start = now - hours_back`.

Columns

Column	Type	Description
Hour	Data	`hour_bucket` — the hour the events fall in, formatted `YYYY-MM-DD HH:00`. Sorted newest-first.
Amazon Sales Orders	Int	`sales_order_count` — Sales Orders with `amazon_order_id` set, created in that hour.
Failed Syncs Created	Int	`failed_sync_count` — Amazon Failed Sync Records created in that hour.

How It Works

Two flat creation-time lists are pulled for the window, then bucketed into hours in Python (no joins).

Sales Orders: Sales Order where creation > window_start AND amazon_order_id is set (pluck="creation", limit=0 = all).
Failed syncs: Amazon Failed Sync Record where creation > window_start.

Each creation timestamp is keyed by strftime("%Y-%m-%d %H:00") and counted into its bucket; buckets are emitted sorted(..., reverse=True) so the most recent hour is first.

flowchart TD
  F[Hours Back = 48] --> W[window_start = now - hours_back]
  W --> A[Sales Order: creation > start AND amazon_order_id set]
  W --> B[Amazon Failed Sync Record: creation > start]
  A --> H[build_hourly_buckets: strftime YYYY-MM-DD HH:00]
  B --> H
  H --> R[hourly rows, newest first]
  C[count status=Failed] --> S[Summary indicators]
  D[last amazon order creation] --> S

Health indicators (summary).

Open Failed Syncs — frappe.db.count("Amazon Failed Sync Record", {"status": "Failed"}). Indicator Red if any open, else Green. (This is a live total, not limited to the window.)
Minutes Since Last Amazon Order — minutes between now and the creation of the most recent Sales Order with amazon_order_id set. Indicator Red when > 120 minutes, else Green; shows - if no Amazon order exists yet.

Notes

The hourly buckets only span the look-back window, but the two summary indicators are global (open-failed count and time-since-last-order) — they fire even if the window itself looks quiet.
A >120-minute gap is the built-in "is the pipeline stalled?" threshold; tune by widening Hours Back to see more history.
Related: Amazon Daily Order Stats.