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}”.
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.