Split a mixed order into child orders under one parent order

Questions first. What decides which child an item belongs to: the vendor, the warehouse, or a delivery constraint such as temperature? Does the customer pay once or per child? If one child is rejected after payment, should the others continue, or is the order all-or-nothing? How many children can one parent have? Are there coupons or delivery fees that span items from different children? Does each child get its own delivery partner and its own ETA?

Scope I will assume: a parent with 2 to 5 children, grouped by fulfilment source; one customer payment; independent child lifecycles; the customer sees one order with a timeline per child; the default is partial fulfilment with automatic refund of a failed child. Out of scope: courier assignment inside each child, restaurant and warehouse internals, tax invoice rules (finance and legal define those).

Planning figures for the exercise, not facts about any company.

• Order volume: 3 million orders a day is about 35 a second (3,000,000 ÷ 86,400 = 34.7). A 10x peak is about 350 a second.
• Mixed share: 10% mixed is 300,000 parents and, at 2 children each, 600,000 child orders a day; 300,000 ÷ 86,400 = about 3.5 splits a second, about 35 at peak.
• Rows per order: parent, children, items, payment and allocation rows, status events and outbox rows come to roughly 15 per order across all orders. Peak writes: 350 × 15 = 5,250 a second, right at the 5,000 line for one primary.
• One split: a parent, 2 children and about 10 item lines is roughly 25 rows, a small transaction if they share a shard.
• Status reads: a customer watching the order refreshes about every 10 s; 200,000 watching at peak gives 20,000 reads a second, so the order-detail read comes from a cache or read model, not the primary.

The conclusions: the orders store is at the partition line, so shard it; and the shard key must put a parent and all its children on one shard, so the split can commit in a single local transaction with no distributed protocol.

1. Checkout builds a quote. Each cart line is tagged with a fulfilment_node (a restaurant or a warehouse), a price snapshot and a tax amount, all in integer minor units (paise or cents, never floats). The quote has an id and an expiry.
2. Split plan. A deterministic function groups lines by fulfilment_node and applies constraints (maximum items or weight per child, items that cannot ship together). The plan is stored with a hash of its inputs, so the same quote always gives the same plan.
3. Identifiers. parent_id is a time-ordered 64-bit id (see the time and ids chapter), allocated when the quote is built, and also the shard key. child_id is (parent_id, seq) with seq from the plan, so a retry produces the same child ids and an upsert cannot create duplicates.
4. Tables on one shard: parents(parent_id, customer_id, state, total_minor, idempotency_key, version) with a unique key on (customer_id, idempotency_key); children(parent_id, seq, fulfilment_node, state, subtotal_minor, version); child_items; payment_allocations(parent_id, seq, amount_minor, refunded_minor); outbox.

Place-order path. The client sends the quote id, which carries the parent_id, and an idempotency key, so a retry always reaches the same shard. In one local transaction on the parent's shard the service inserts the parent in PENDING_PAYMENT, the children in CREATED, items, allocations and one outbox row asking the payment service to authorise. A retry hits the unique key and returns the existing parent. Do not call restaurants or warehouses inside this transaction; the outbox is the reliable hand-off. The payment service places one authorisation for the parent total (the customer sees one charge). When it succeeds, one local transaction moves the parent to PLACED and writes one outbox row per child, and the relay publishes the child.created events. Each fulfilment service consumes its own child, idempotent by child_id, and acknowledges or rejects it within a deadline; silence becomes a rejection by a delayed job.

Child lifecycles. Each child has its own state machine (for example CREATED to ACCEPTED, PACKED, PICKED_UP, DELIVERED, or REJECTED and CANCELLED) with conditional updates on version, so duplicate or reordered events are harmless.

Parent status is derived. It is a pure function of the children: all delivered is DELIVERED; all cancelled is CANCELLED; delivered plus cancelled is PARTIALLY_FULFILLED; anything else active is IN_PROGRESS. Recompute it from the child rows on each child event instead of adding to counters, because a recompute is idempotent and a counter drifts on a duplicate. Write the result to the read model that serves the customer's order page.

Money. One authorisation, many allocations. Coupons and delivery fees are spread across children in proportion to subtotals, using integer arithmetic where the remainder cents go to the largest child, so the children's totals always sum exactly to the parent total. That sum is an invariant worth checking in a test. Capture per child when it is delivered if the payment method supports partial capture; otherwise capture the whole amount and refund the difference. A refund for a child is limited to its allocation, and each refund call carries an idempotency key of (child_id, refund_seq).

Rejection after payment. Default policy: the rejected child is cancelled, its allocation is refunded, and the other children continue, with one clear message to the customer. If the order was marked all-or-nothing (for example a gift), a rejection triggers compensation for every other child that has not reached pick-up: cancel it, refund it, and tell the customer. This is a saga on a single shard, so the compensations are ordinary conditional updates plus outbox events.

Notifications. One message per meaningful change, merged where possible: "your groceries are 10 minutes away" rather than one push for each internal state.

• One transaction on one shard vs a saga across vertical services. Co-locating parent and children makes the split atomic and simple, but ties the verticals to a shared orders store. A saga lets each vertical own its data but turns creation into several steps with compensation. Pick co-location for the order record, with the verticals' fulfilment systems consuming events; flip to a saga if verticals already own separate databases and teams.
• One payment vs one per child. One authorisation matches what the customer expects and costs one fee, but needs allocation bookkeeping for partial refunds. One payment per child isolates failures but may show several charges. Pick one payment with allocations; flip when children belong to different legal sellers who must each be paid separately.
• Derived vs stored parent status. Stored status is fast to read but can drift. Derived status is always correct and costs a few row reads. Pick derived, cached in the read model; flip to stored only if you need to query parents by status at high volume.
• Partial fulfilment vs all-or-nothing. Partial keeps the customer's meal even if the groceries fail but may leave them with half an order. All-or-nothing is predictable but wastes prepared food. Pick partial by default; flip to all-or-nothing when the customer chooses it.

• Modelling the child as a status on an item. Children need their own lifecycle, fulfilment source, money and delivery.
• Calling vendors inside the database transaction, so a slow vendor holds locks or a failure leaves a half-created order.
• Random child ids, so a retry creates duplicate children.
• Floating-point money, with rounding that makes the children not sum to the parent.
• A parent status kept by counters, which drifts on duplicate events.
• Ignoring partial failure. No answer for "one child rejected after payment".
• Sharding by child id, which forces a distributed transaction for every split.
• Letting a refund exceed what was paid for that child, with no idempotency key on the refund call.