Order Lookup

Overview

Support rapid operational lookup to retrieve order records from Shopify. Returns filtered order line items and product identifiers. Excludes non-relevant items (warranty, digital, reshipment).

Goal: One-call retrieval API for UI workflows — returns normalized order data with product resolution.

API endpoints

Method	Path	Spec	Description
GET	/orders	order.yaml	List orders with filters (phase, customer, date range, search)
GET	/orders/{order_id}	order.yaml	Get order detail with ?include=lineItems
GET	/orders/{order_id}/line-items	order.yaml	List order line items with tax lines and adjustments

The scenario doc previously referenced POST /api/orders/lookup as a single-call convenience endpoint. In the new API, this is achieved by GET /orders?search={query} followed by GET /orders/{order_id}?include=lineItems for detail.

Schema tables

Table	Role
order_header	Order header (id, number, status, timestamps)
order_line_item	Order line items (product, quantity, price)
order_line_item_tax_line	Tax lines on line items
order_line_item_adjustment	Discounts/adjustments on line items
order_summary	Versioned financial snapshots
product	Product reference data for metadata joins

Infrastructure

Resource	Purpose
Internal Worker (CF Worker)	Serves order lookup endpoints behind CF Access
PlanetScale (Postgres via Hyperdrive)	Primary data store for order tables
Shopify API (fallback)	Live query fallback when local data is stale

Main success scenario

1. Staff user enters order identifier in the UI
2. GET /orders?search={query} to find matching order(s)
3. API validates Access
4. API queries order_header by order_id OR order_number
5. API fetches matching order_line_item rows and applies exclusion filters
6. Returns product_ids, quantities, titles; optionally joins product for metadata

DTOs

Request (search):

GET /orders?search=1000123456

Response (detail with line items):

{
  "data": {
    "uid": "ord_01J5XYZABC123",
    "name": "Order #1042",
    "currency_code": "USD",
    "status": { "phase": "pending" },
    "create_ts": "2025-10-01T14:30:00Z",
    "line_items": [
      {
        "uid": "oli_01J5XYZ001",
        "name": "Classic T-Shirt -- Medium / Black",
        "quantity": 2,
        "unit_price": 29.99
      }
    ]
  }
}

Exception flows

Condition	Fallback
Order not found	Return 404 error
Multiple matches for order_number	Return ambiguous results; require disambiguation
Local data stale	Optionally fall back to Shopify live query (with rate-limit protection)

Acceptance criteria

• PASS: Given known order ID/number, API returns correct product_ids from order line items; excluded items are filtered out
• FAIL: Returns excluded items; misses valid items; full table scan exceeding latency expectations