Splitting a Cart Across Multiple Vendors: Data Model and Checkout Flow

A single-vendor store has one cart, one order and one fulfillment. Add a second seller and all three assumptions break at once. The customer still wants one basket and one payment, but two vendors now need to accept, pack and deliver on their own schedules — and one of them will cancel while the other is already out for delivery.

The model that survives is one order plus N sub-orders. It is not complicated, but the details decide whether partial cancellation, per-vendor commission and order status are straightforward or a permanent source of bugs.

The mistake: hanging items off the order

Almost every multi-vendor schema starts here, because it is what a single-vendor schema looks like:

orders
  └── order_items (product_id, quantity, vendor_id)

It looks fine until you try to use it. Three things immediately have no answer. What does orders.status = 'shipped' mean when one vendor of three has shipped? How do you cancel one vendor's half without refunding the whole basket? And where does the commission row live, when your rate differs per vendor?

You end up writing group by vendor_id in every query and reimplementing sub-orders in application code, badly.

One order, N sub-orders

The order becomes a thin container: who bought, what they paid, and the single payment reference. Everything that a vendor acts on moves down a level.

create table orders (
  id           uuid primary key,
  customer_id  uuid not null references users(id),
  status       text not null,        -- derived rollup, see below
  total_cents  integer not null,
  payment_ref  text,                 -- ONE PaymentIntent for the whole cart
  created_at   timestamptz not null default now()
);

create table sub_orders (
  id               uuid primary key,
  order_id         uuid not null references orders(id) on delete cascade,
  vendor_id        uuid not null references vendors(id),
  status           text not null,
  subtotal_cents   integer not null,
  delivery_cents   integer not null default 0,
  commission_cents integer not null,
  created_at       timestamptz not null default now()
);

-- The line that makes the whole thing work: items belong to a
-- sub-order, never to the parent order.
create table order_items (
  id           uuid primary key,
  sub_order_id uuid not null references sub_orders(id) on delete cascade,
  product_id   uuid not null references products(id),
  quantity     integer not null,
  unit_cents   integer not null
);

That last table is the whole design. Once items belong to a sub-order, a vendor's view of the world is one where vendor_id = ? away, and they can never see or act on another vendor's lines.

Checkout is one payment and N fulfillments

Keep these separate in your head. The customer authorises a single charge; the platform then owes money to several vendors. Those are different problems, and trying to solve them in one step is what pushes people prematurely into split payments.

Group the cart by vendor, then write the parent and its children in one transaction. If any insert fails, none of it happened.

export async function createOrder(cart: CartLine[], customerId: string) {
  const byVendor = groupBy(cart, (line) => line.vendorId);

  return db.transaction(async (tx) => {
    const [order] = await tx.insert(orders).values({
      customerId,
      status: "pending",
      totalCents: total(cart),
    }).returning();

    for (const [vendorId, lines] of byVendor) {
      const subtotal = total(lines);
      const rate = await commissionRateFor(tx, vendorId);

      const [subOrder] = await tx.insert(subOrders).values({
        orderId: order.id,
        vendorId,
        status: "pending",
        subtotalCents: subtotal,
        deliveryCents: deliveryFeeFor(vendorId),
        // Snapshot the rate. If you change a vendor's commission
        // next month, last month's orders must not move.
        commissionCents: Math.round(subtotal * rate),
      }).returning();

      await tx.insert(orderItems).values(
        lines.map((line) => ({
          subOrderId: subOrder.id,
          productId: line.productId,
          quantity: line.quantity,
          // Snapshot the price too — products get repriced.
          unitCents: line.unitCents,
        })),
      );
    }

    return order;
  });
}

Two snapshots matter more than they look. Commission rate and unit price are both copied into the row rather than joined at read time. Join to the live products and vendors tables and every historical order silently rewrites itself the next time you change a price or renegotiate a rate — which makes your ledger unauditable.

Order status is derived, never stored twice

The parent's status is a function of its children. Write it independently and you have two sources of truth that will drift the first time a webhook arrives out of order.

select
  case
    when count(*) filter (where status <> 'cancelled') = 0
      then 'cancelled'
    when count(*) filter (where status = 'delivered') = count(*)
      then 'delivered'
    when count(*) filter (where status in ('delivered', 'cancelled')) = count(*)
      then 'partially_delivered'
    else 'in_progress'
  end as order_status
from sub_orders
where order_id = $1;

If you need orders.status as a column for indexing, treat it as a cache: recompute it from this expression in the same transaction that changes any sub-order, and never write it from anywhere else.

Note the fully-cancelled case comes first. An order where every vendor cancelled is cancelled, not delivered — and the naive all delivered check returns true for an empty set.

Commission belongs to the sub-order

Each sub-order carries its own commission because rates are negotiated per vendor. That single column is enough to run a real ledger: what the platform earned, what each vendor is owed, and what has been settled.

You do not need split payments on day one. A single charge plus a commission ledger and a manual payout run is a legitimate architecture, and it is how most marketplaces start. Stripe Connect moves the split into the payment itself and removes the manual run — but it also brings onboarding, KYC and per-account compliance for every vendor. Add it when payout volume justifies that, not before.

Three places it breaks

Partial cancellation. One vendor cancels; the other two are fine. You are refunding part of a single charge, so refund the cancelled sub-order's share and leave the rest captured.

await stripe.refunds.create({
  payment_intent: order.paymentRef,
  // That vendor's goods plus their delivery fee — not a
  // proportional slice of the basket total.
  amount: subOrder.subtotalCents + subOrder.deliveryCents,
});

Delivery fees. Three vendors means three journeys, so a fee per sub-order is the honest model. If you promise one flat delivery fee, you are subsidising multi-vendor baskets — decide that deliberately and put the fee on the parent, rather than discovering it in your margins later.

Per-vendor minimums. A £15 minimum has to be validated per vendor at checkout, not against the basket total. A £40 basket that is £38 from one store and £2 from another must fail, and the error has to name which vendor — otherwise the customer cannot fix it.

Where to start

Build order_items.sub_order_id first, before any UI. Every other decision here follows from it, and it is the one thing that is genuinely painful to retrofit once you have live orders in the old shape.