An order tracking system maintains the lifecycle of a purchase from placement through delivery, providing real-time status to customers and internal systems. Core challenges: modeling the state machine correctly (preventing invalid transitions), propagating third-party carrier events (FedEx, UPS webhooks), notifying customers at key milestones, and supporting multiple fulfillment paths (single warehouse, split shipments, dropship).
Core Data Model
CREATE TYPE order_status AS ENUM (
'placed','payment_confirmed','processing','awaiting_pickup',
'shipped','out_for_delivery','delivered','cancelled','return_initiated','returned'
);
CREATE TABLE Order (
order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL,
status order_status NOT NULL DEFAULT 'placed',
total_cents INT NOT NULL,
currency CHAR(3) NOT NULL DEFAULT 'USD',
placed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_order_user ON Order (user_id, placed_at DESC);
-- Each status transition is a row — full history preserved
CREATE TABLE OrderEvent (
event_id BIGSERIAL PRIMARY KEY,
order_id UUID NOT NULL REFERENCES Order(order_id),
from_status order_status,
to_status order_status NOT NULL,
actor TEXT NOT NULL, -- 'system', 'carrier', 'customer', 'support'
notes TEXT,
occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
metadata JSONB
);
CREATE INDEX idx_order_event ON OrderEvent (order_id, occurred_at DESC);
-- Shipment tracking (an order may have multiple packages)
CREATE TABLE Shipment (
shipment_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
order_id UUID NOT NULL REFERENCES Order(order_id),
carrier TEXT NOT NULL, -- 'fedex', 'ups', 'usps', 'dhl'
tracking_number TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'label_created',
estimated_delivery TIMESTAMPTZ,
shipped_at TIMESTAMPTZ,
delivered_at TIMESTAMPTZ
);
CREATE INDEX idx_shipment_order ON Shipment (order_id);
CREATE INDEX idx_shipment_tracking ON Shipment (carrier, tracking_number);
State Machine with Valid Transitions
from enum import Enum
from datetime import datetime, timezone
import psycopg2
class OrderStatus(str, Enum):
PLACED = 'placed'
PAYMENT_CONFIRMED = 'payment_confirmed'
PROCESSING = 'processing'
AWAITING_PICKUP = 'awaiting_pickup'
SHIPPED = 'shipped'
OUT_FOR_DELIVERY = 'out_for_delivery'
DELIVERED = 'delivered'
CANCELLED = 'cancelled'
RETURN_INITIATED = 'return_initiated'
RETURNED = 'returned'
# Valid transitions: {from_status: [allowed_to_statuses]}
VALID_TRANSITIONS: dict[OrderStatus, list[OrderStatus]] = {
OrderStatus.PLACED: [OrderStatus.PAYMENT_CONFIRMED, OrderStatus.CANCELLED],
OrderStatus.PAYMENT_CONFIRMED: [OrderStatus.PROCESSING, OrderStatus.CANCELLED],
OrderStatus.PROCESSING: [OrderStatus.AWAITING_PICKUP, OrderStatus.SHIPPED, OrderStatus.CANCELLED],
OrderStatus.AWAITING_PICKUP: [OrderStatus.SHIPPED, OrderStatus.CANCELLED],
OrderStatus.SHIPPED: [OrderStatus.OUT_FOR_DELIVERY, OrderStatus.DELIVERED],
OrderStatus.OUT_FOR_DELIVERY: [OrderStatus.DELIVERED],
OrderStatus.DELIVERED: [OrderStatus.RETURN_INITIATED],
OrderStatus.RETURN_INITIATED: [OrderStatus.RETURNED],
OrderStatus.CANCELLED: [],
OrderStatus.RETURNED: [],
}
def transition_order(conn, order_id: str, to_status: OrderStatus,
actor: str, notes: str = "", metadata: dict | None = None) -> bool:
"""
Atomically transition an order to a new status.
Validates the transition is allowed from the current state.
Returns True if transitioned, False if already in target state (idempotent).
"""
with conn.cursor() as cur:
cur.execute(
"SELECT status FROM Order WHERE order_id = %s FOR UPDATE",
(order_id,)
)
row = cur.fetchone()
if not row:
raise ValueError(f"Order {order_id} not found")
current = OrderStatus(row[0])
if current == to_status:
return False # Idempotent — already in target state
if to_status not in VALID_TRANSITIONS.get(current, []):
raise ValueError(f"Invalid transition: {current} → {to_status}")
with conn.cursor() as cur:
cur.execute(
"UPDATE Order SET status=%s, updated_at=NOW() WHERE order_id=%s",
(to_status.value, order_id)
)
cur.execute(
"""INSERT INTO OrderEvent (order_id, from_status, to_status, actor, notes, metadata)
VALUES (%s,%s,%s,%s,%s,%s)""",
(order_id, current.value, to_status.value, actor, notes,
psycopg2.extras.Json(metadata or {}))
)
conn.commit()
# Trigger customer notification for key milestones
NOTIFY_TRANSITIONS = {OrderStatus.SHIPPED, OrderStatus.OUT_FOR_DELIVERY, OrderStatus.DELIVERED}
if to_status in NOTIFY_TRANSITIONS:
enqueue_order_notification(order_id, to_status)
return True
Carrier Webhook Integration
CARRIER_STATUS_MAP = {
# FedEx status codes → internal status
"FX": {
"OC": OrderStatus.SHIPPED, # Order created
"IT": OrderStatus.SHIPPED, # In transit
"OD": OrderStatus.OUT_FOR_DELIVERY, # Out for delivery
"DL": OrderStatus.DELIVERED, # Delivered
},
# UPS status codes
"UPS": {
"I": OrderStatus.SHIPPED,
"O": OrderStatus.OUT_FOR_DELIVERY,
"D": OrderStatus.DELIVERED,
}
}
def handle_carrier_webhook(conn, carrier: str, tracking_number: str,
carrier_status: str, event_time: str):
"""
Map carrier status code to internal order status and transition.
Called from the carrier webhook endpoint after signature verification.
"""
# Find the shipment
with conn.cursor() as cur:
cur.execute(
"SELECT shipment_id, order_id, status FROM Shipment WHERE carrier=%s AND tracking_number=%s",
(carrier, tracking_number)
)
row = cur.fetchone()
if not row:
return # Unknown tracking number — ignore
shipment_id, order_id, current_ship_status = row
# Map to internal status
status_map = CARRIER_STATUS_MAP.get(carrier, {})
internal_status = status_map.get(carrier_status)
if not internal_status:
return # Unknown carrier status code — log and ignore
# Update shipment record
with conn.cursor() as cur:
update_fields = ["status = %s"]
params = [carrier_status]
if internal_status == OrderStatus.SHIPPED:
update_fields.append("shipped_at = %s")
params.append(event_time)
elif internal_status == OrderStatus.DELIVERED:
update_fields.append("delivered_at = %s")
params.append(event_time)
params.append(shipment_id)
cur.execute(
f"UPDATE Shipment SET {', '.join(update_fields)} WHERE shipment_id = %s",
params
)
conn.commit()
# Transition the order
try:
transition_order(conn, order_id, internal_status, actor="carrier",
metadata={"carrier": carrier, "tracking_number": tracking_number,
"carrier_status": carrier_status})
except ValueError:
pass # Invalid transition (e.g., out-of-order webhook) — log but don't fail
Key Interview Points
- State machine prevents invalid transitions: Without enforced transitions, a bug or race condition can move an order directly from “placed” to “delivered”, skipping payment confirmation. The VALID_TRANSITIONS map is the single source of truth. Any code that wants to change order status must call transition_order() — never UPDATE Order SET status directly.
- OrderEvent as audit trail: Every transition creates an OrderEvent row. This provides: (1) full history for customer service (“why is my order cancelled?”); (2) SLA measurement (“what percentage of orders ship within 24 hours of payment?”); (3) incident investigation (“all orders placed between 2pm and 3pm got stuck in processing”). Never derive history from the Order row alone.
- Out-of-order carrier webhooks: Carriers deliver webhooks out of order — “out for delivery” may arrive before “shipped.” The state machine rejects invalid transitions (shipped → shipped is idempotent; delivered → shipped raises ValueError). Catch ValueError from carrier webhooks and log as a warning — do not return 5xx (that triggers retry of a genuinely invalid event).
- Split shipments: A single order may have items from two warehouses, generating two Shipment rows. The order status is the minimum of shipment statuses — show “partial shipment: 2 of 3 items shipped.” Only transition the order to “delivered” when all Shipment rows have delivered_at set. Query: SELECT COUNT(*) FROM Shipment WHERE order_id = X AND delivered_at IS NULL.
- Customer notification throttle: Don’t notify on every carrier webhook — carriers send many intermediate status updates (In transit: Memphis, In transit: Nashville). Notify only for: shipped, out_for_delivery, delivered, and cancelled. The NOTIFY_TRANSITIONS set controls this. Rate-limit to one notification per status per order — idempotency key: f”notify-{order_id}-{status}”.
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How does the state machine prevent invalid order transitions?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Without enforced transitions, application bugs or race conditions can move an order to an invalid state — a "delivered" order being moved back to "shipped," or a "cancelled" order being fulfilled. The VALID_TRANSITIONS dictionary defines exactly which states can follow which. Any attempt to transition outside this map raises an exception before touching the database. The transition_order() function is the only code path that changes order status — direct UPDATE statements on the Order table are forbidden by convention (enforced by code review and grep in CI). Additionally, the FOR UPDATE in the transition query prevents two concurrent processes from simultaneously transitioning the same order — the second waits until the first commits and then re-reads the (now-changed) status.”}},{“@type”:”Question”,”name”:”Why store every transition in OrderEvent instead of just the current status?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The Order.status column gives current state; OrderEvent gives full history. You need the history to answer: "Why was this order cancelled?" (actor=support, notes="customer request"), "When did shipping happen?" (find the event with to_status=shipped), "Did this order ever go back to processing?" (look for a transition from a later state to processing), "What percentage of orders ship within 24 hours?" (compare payment_confirmed timestamp to shipped timestamp in OrderEvent). The history is also essential for customer service — agents need the full timeline to explain delays. Never answer customer questions using only the current Order.status row.”}},{“@type”:”Question”,”name”:”How do you integrate multiple carriers (FedEx, UPS, DHL) with different webhook formats?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Each carrier has a proprietary webhook format: FedEx uses XML with its own status codes; UPS uses JSON with different codes; DHL uses REST. Use the Adapter pattern: create a carrier-specific parser that normalizes each carrier’s webhook to your internal format (carrier, tracking_number, event_timestamp, carrier_status_code). The handle_carrier_webhook() function operates on the normalized format and is carrier-agnostic. Register parser functions in a registry: PARSERS = {"fedex": parse_fedex_webhook, "ups": parse_ups_webhook}. For new carriers: add a new parser without changing the core tracking logic. Validate webhook signatures (each carrier provides an HMAC or shared secret) before parsing — carrier webhooks are a common injection vector.”}},{“@type”:”Question”,”name”:”How do you handle split shipments for orders with items from multiple warehouses?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”A single order may produce multiple Shipment rows — one per warehouse, each with its own tracking number. The order status should reflect the overall fulfillment state, not just one shipment. Logic: the order is "shipped" when at least one shipment has been shipped. The order is "delivered" only when ALL shipments are delivered (check COUNT(*) FROM Shipment WHERE order_id=X AND delivered_at IS NULL = 0). For customer-facing tracking: show individual shipment statuses ("Package 1 of 2: Delivered, Package 2 of 2: In Transit"). Link each shipment to the specific order items it contains via a ShipmentItem join table, so customers can see which items are in which package.”}},{“@type”:”Question”,”name”:”How do you detect and alert on orders stuck in a state for too long?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Define SLA thresholds per state: processing → shipped within 48 hours; shipped → delivered within 7 days. Monitoring query: SELECT order_id, status, updated_at FROM Order WHERE status = ‘processing’ AND updated_at < NOW() – interval ’48 hours’. Run this every hour via a cron job or Postgres pg_cron. Alert the operations team for stuck orders. Automated escalation: if an order stays in "shipped" for 14 days, automatically open a case with the carrier and notify the customer with an apology + tracking update. Store SLA breach events in OrderEvent (action=’sla_breach’, actor=’system’) for reporting: "What percentage of orders breached shipping SLA this month?"”}}]}
Order tracking and fulfillment system design is discussed in Amazon system design interview questions.
Order tracking and e-commerce fulfillment design is covered in Shopify system design interview preparation.
Order tracking and real-time status update design is discussed in Lyft system design interview guide.