Orders Spreadsheet View — Findings & Roadmap¶

Why this exists¶

Before the dashboard, the merchant ran the whole cross-channel operation from a Google Sheet whose canonical view is the ALL sheet: one row per order line item, rows clustered under each buyer, with product attributes and per-item economics visible at a glance. The dashboard's original Orders surface was a thin one-row-per-order receipt list (Order · Channel · Status · Customer · Total · Created) that threw away that operator-facing density. This workstream rebuilds the spreadsheet view on top of the data the platform already owns (Medusa orders, product metadata.tcg, DCA costing, Shopee escrow).

Terminology reconciliation (load-bearing)¶

The codebase historically calls the marketplace the "channel." The merchant's mental model splits two axes; we adopt the merchant's, and the new UI uses these labels:

• Platform = where the sale originated: Shopee, Lazada, Telegram, Carousell, B2B, Card Show, Stream. (Derived from the Medusa sales channel for marketplace orders.)
• Channel / Method = how it's fulfilled: Self-Collection, Lalamove/GrabExpress, Shopee Express. (Not a clean field yet — see Phase 2.)
• Payment status = Paid / Unpaid / Deposit.

And the merchant's Status is actually three distinct concepts the platform had conflated: the operational pipeline (what they manage by), the platform status (Shopee PROCESSED, Lazada ready_to_ship), and payment status. Phase 1 surfaces Medusa's thin status + payment; the operational pipeline is Phase 2.

What shipped (Phase 1)¶

A read/display/sort redesign over existing data — no new tables.

• Buyer-grouped, per-item table. One row per order line item, clustered under a per-order buyer banner (a link to the detail page). Every row always visible — no expand/collapse. Banner: Buyer · Platform · Method · Status · Payment · Date · order total. Item rows: Item Name / Type / Variation (deterministic colored chips, reviving the legacy Dropdown-List scanning aid) · Language · Qty · Gross Sale · Gross Profit · SKU.
• Sort. Newest-first (created_at DESC) by default, with a control for oldest-first and status. total and buyer are deliberately not offered — total is a computed aggregate Medusa can't ORDER BY, and buyer would order by the synthetic Shopee email rather than the displayed username (both deferred to Phase 2).
• Enriched detail page. Economics section (Cost · Gross Profit · Comms $ · Net Profit, plus order-level Comms total / delivery fees) and batch allocations (Batch · Alloc Qty). Phase-1 placeholders (Collection Date · Deposit · Remarks) render "—" until the schema lands.
• Shared enrichment. apps/server/src/api/_lib/order-economics.ts — enrichOrderLines() (batched: variant→metadata.tcg, DCA economics, Shopee escrow) + a unit-tested pure allocateByRevenueShare() that splits order-level Shopee comms/shipping across lines by revenue share. DCA gained a batched getOrderLinesEconomics(). List and detail routes share the helper.
• RBAC. Margin fields (cost / profit / comms / net) are gated by the existing view_finance capability — the server omits them for actors without it; the UI hides the columns. OPS was granted view_finance so operators see margins, matching the legacy spreadsheet where ops staff worked with cost/profit. Batch allocations are inventory data and are not finance-gated (event-staff with view_inventory see them).
• Keyboard-first + states (/impeccable harden pass). Table keyboard navigation (↑/↓, j/k, Enter to open, / to focus filters), an orders-scoped error boundary (blame-free copy + Retry + collapsed detail), and list/detail loading skeletons. The region scored 15→~18/20 on the /impeccable audit.

Validation¶

• Server: yarn build clean; order-economics unit tests (revenue-share split incl. cents-remainder placement); orders-enriched integration tests (default newest-first sort + param acceptance, enriched items[], view_finance gating with EVENT_STAFF omission vs OPS/FINANCE inclusion on seeded orders, detail payload).
• Dashboard: tsc clean (one unrelated pre-existing publish.spec.ts error aside), next build green, 13 Playwright e2e (grouped table, chips, channel filter, sort, click-through, finance-column gating, split-batch allocation, keyboard nav, /-focus).
• Review: 5 codex review --base main rounds; every P1 (pagination truncation, net-profit accuracy) and substantive P2 resolved (see gotchas). The recurring OPS-view_finance flag is the intentional decision above, held with rationale.

Gotchas + decisions captured in flight¶

• Medusa list methods + query.graph default to 15 rows. Every batched enrichment query (DCA allocations / adjustments / batch-items, Shopee escrow, the variant query.graph) must paginate to exhaustion or pass an explicit take. Left unbounded they silently truncate at 15 — undercounting COGS (overstating profit), dropping escrow (null comms/net on later orders), and losing variant metadata — on any normal-sized page. Tiny test fixtures hid it; codex static analysis caught it. Takeaway: treat the 15-row default as a footgun on every new batched lister/graph call.
• query.graph ordering lives in pagination.order, not a top-level orderBy. A top-level orderBy key type-checks (the query is cast to any) but is silently ignored at runtime — sorting appears to work in code and does nothing. Verified against @medusajs/types. Multi-key order is supported (object insertion order = priority); we add created_at + the unique id as stable tiebreakers so offset pagination can't skip/duplicate rows on ties (e.g. status sort).
• Order-level marketplace fees, per-line economics. Shopee escrow stores commission + actual shipping at the order level; they're split across lines by revenue share so per-item Gross/Net Profit are meaningful, with the order totals also shown on the detail page for transparency.
• net_profit is null when marketplace fees are pending. For a Shopee order whose escrow hasn't been fetched (ordersn present, no escrow row), comms/shipping are unknown — returning gross_profit as net would overstate it, so net is null. Non-marketplace orders (no ordersn) correctly keep net = gross (no marketplace fees apply).
• metadata.tcg over line-item snapshots. Item Name / Type / Variation / Language come from the variant's parent product metadata.tcg (the structured-naming identity), falling back to product_title → line title for custom/manually-created lines. batch_number is a number (per the DCA model), and a line can carry 1..N allocations (surfaced as an array; the detail joins batch numbers and sums allocated qty).

Phase-1 gaps (documented in code, deferred)¶

Captured as —/null with code comments, to be filled as the supporting data lands:

• Per-line refunds are not yet subtracted from economics (overstates refunded lines).
• Comms %, Collection Date, Deposit, order Remarks, and non-Shopee (manual) delivery fees are not captured yet — Comms % isn't stored; the rest need additive schema (Phase 3).
• Method is best-effort (order metadata.delivery_method, falling back to the shipping-method name); mostly blank until Phase 2 makes it first-class.

Roadmap¶

Phase 2 — operational status pipeline + first-class Method + SPX waybill¶

Rebuild the merchant's real fulfillment lifecycle as managed state, not Medusa's thin pending/canceled:

• Operational status: For Allocation → Allocated → For Generating → For Printing → Completed, plus Cancelled / Insufficient Stock / Re-Allocation / Update Method. Ties into DCA batch allocation. (Aligns with the order_status_event table already sketched in the Data Model.)

  Shipped — the implemented vocabulary differs from the sketch above

  The operational-status spine landed as an OperationalStage union stored on order.metadata.operational_status, not an order_status_event table. The sketch above is the original design; the values actually implemented are:

  unpaid · paid · allocated · for_generating · for_printing · shipped · completed · insufficient_stock · cancelled · returned · unknown

  Source of truth: apps/server/src/modules/connectors/shared/order-stage.ts (mirrored in packages/shared-types/src/orders.ts). Most values are derived from the connector's native status per-marketplace; the operator's stored override wins over the derived value.

  Two are operator-set only and are never produced by deriveOperationalStage:

  • insufficient_stock — internal, no platform equivalent.
  • paid — manual channels only (Telegram / event / walk-in / Carousell / Instagram), added 2026-07-30. A manual order has no Medusa payment collection, so payment_status is permanently not_paid and the operator's "money received" mark had nowhere to live. It is rejected with 422 on a marketplace order: the buyer there always paid the platform, so those orders track payout/escrow, not payment — and because a stored override wins over the derived stage, allowing it would mask a Shopee order's real workflow status until someone noticed.

  For Allocation, Re-Allocation and Update Method from the sketch were not implemented.

  • Method as a first-class field that gates allocation; Shopee Express forks to For Generating → an SPX waybill XLSX export (the legacy Apps Script flow: address parse, dedupe-by-address, hardcoded parcel weight/instruction, mark For Printing).
  • Unlocks server-side Method / Payment filtering and a real sortable buyer column.

Phase 3 — manual order creation¶

Operator-initiated orders for walk-in / Card Show / Telegram / pre-order sales that never came through a marketplace, via Medusa's draft-order workflow + a new authoring UI. Introduces the Deposit concept (amount + a Deposit payment status), Collection Date, and order Remarks — at which point those Phase-1 placeholder columns light up.

See also¶

• Data Model — computeOrderLineProfit, batch allocation, and the sketched order_status_event table.
• Development Workflow — the /impeccable audit→polish→harden cadence this region followed.
• Phase 5 Implementation Plan — the dashboard, RBAC, and off-the-shelf stack this builds on.