Designing Multi-Vendor Order-Splitting Engines and PostGIS Spatial Discovery in Node.js

1. The ParentOrder -> VendorSubOrder State Machine

When a customer on Bipeers Marketplace buys shoes from Merchant A, groceries from Merchant B and books a local freelancer in one checkout, a flat orders table breaks at once. What is the order's status if Merchant A has shipped and Merchant B is still packing? Who is refunded if only the groceries are cancelled? Which merchant sees the customer's whole basket?

A single-vendor store never has to answer these questions, which is why bolting marketplace behaviour onto a single-vendor schema fails. Status becomes a string like partially_shipped that no code path fully understands, and every merchant query needs a filter that somebody eventually forgets.

The model that works has two levels. One immutable parent order records what the customer agreed to: the full basket, the total and the payment. Under it sits one sub-order per merchant, each with its own items, its own totals and its own status. The customer pays once. Each merchant sees, fulfils and gets paid for only its own sub-order.

• Parent order: customer, payment intent, grand total, delivery address. Written once, never edited.
• Vendor sub-order: merchant, line items, subtotal, commission, payout amount, fulfilment status.
• Ledger entries: one row per movement of money, so every payout can be traced to an order.

2. The Schema

The tables below are reduced to the columns that carry the design. Money is stored as integer minor units, never floating point. Prices are copied onto the order line at checkout, so a later price change in the catalogue cannot rewrite history. The commission rate is copied too, for the same reason: a merchant's new rate applies to new orders, not old ones.

CREATE TABLE parent_orders (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id     UUID NOT NULL REFERENCES users(id),
  idempotency_key TEXT NOT NULL UNIQUE,
  grand_total     BIGINT NOT NULL,          -- minor units
  payment_status  TEXT NOT NULL DEFAULT 'pending',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE vendor_sub_orders (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  parent_order_id UUID NOT NULL REFERENCES parent_orders(id),
  merchant_id     UUID NOT NULL REFERENCES merchants(id),
  subtotal        BIGINT NOT NULL,
  commission_rate NUMERIC(5,4) NOT NULL,    -- copied at checkout
  commission      BIGINT NOT NULL,
  payout          BIGINT NOT NULL,
  status          TEXT NOT NULL DEFAULT 'pending',
  UNIQUE (parent_order_id, merchant_id)
);

CREATE TABLE order_lines (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sub_order_id UUID NOT NULL REFERENCES vendor_sub_orders(id),
  product_id   UUID NOT NULL,
  unit_price   BIGINT NOT NULL,             -- copied at checkout
  quantity     INT NOT NULL CHECK (quantity > 0)
);

CREATE TABLE ledger_entries (
  id           BIGSERIAL PRIMARY KEY,
  sub_order_id UUID NOT NULL REFERENCES vendor_sub_orders(id),
  account      TEXT NOT NULL,   -- 'merchant_payable' | 'platform_commission'
  amount       BIGINT NOT NULL, -- positive = credit, negative = reversal
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

The UNIQUE (parent_order_id, merchant_id) constraint guarantees one sub-order per merchant per checkout. The unique idempotency_key on the parent is what makes a retried checkout safe, as the next section shows.

3. Checkout as One Transaction

Splitting a basket creates several rows across four tables. If the process stops halfway, the customer may be charged for an order one merchant never receives. The whole split therefore runs inside a single ACID transaction: either every row exists or none does.

Three details matter. Prices are read from the database inside the transaction, never trusted from the app. Stock is decremented with a conditional update, so two customers cannot both buy the last item. And the idempotency key means a retry after a dropped connection returns the order that already exists instead of creating a second one.

export async function checkout(input: {
  customerId: string;
  idempotencyKey: string;
  items: { productId: string; quantity: number }[];
}) {
  return db.transaction(async (tx) => {
    const existing = await tx.parentOrders.findByKey(input.idempotencyKey);
    if (existing) return existing; // Retry: return the first result.

    // Authoritative prices and merchants come from the database.
    const products = await tx.products.findMany(input.items.map((i) => i.productId));
    const byMerchant = groupBy(input.items, (i) => products.get(i.productId)!.merchantId);

    let grandTotal = 0;
    const parent = await tx.parentOrders.insert({
      customerId: input.customerId,
      idempotencyKey: input.idempotencyKey,
      grandTotal: 0,
    });

    for (const [merchantId, lines] of byMerchant) {
      for (const line of lines) {
        // Fails the whole transaction if stock ran out.
        await tx.inventory.decrementOrThrow(line.productId, line.quantity);
      }
      const subtotal = sum(lines, (l) => products.get(l.productId)!.price * l.quantity);
      const rate = await tx.merchants.commissionRate(merchantId);
      const commission = Math.round(subtotal * rate);

      const sub = await tx.subOrders.insert({
        parentOrderId: parent.id, merchantId, subtotal,
        commissionRate: rate, commission, payout: subtotal - commission,
      });
      await tx.orderLines.insertMany(sub.id, lines, products);
      grandTotal += subtotal;
    }

    await tx.parentOrders.setTotal(parent.id, grandTotal);
    return { ...parent, grandTotal };
  });
}

Payment is captured against the parent order's total. Ledger entries are written when the payment is confirmed, not at checkout, so an abandoned payment leaves no money trail to clean up. Push notifications to merchants are sent after the transaction commits, for the same reason given in our real-time systems article: never announce something that might still roll back.

4. Independent State Machines per Merchant

Each sub-order moves through its own lifecycle. Allowed transitions are written down as data, and every status change goes through one function that checks them. A merchant cannot mark an order delivered before it was dispatched, and nobody can revive a cancelled one.

The parent order has no status column of its own for fulfilment. Its status is derived from its children when it is read, which means it can never disagree with them.

type SubOrderStatus =
  | "pending" | "accepted" | "preparing"
  | "dispatched" | "delivered" | "cancelled";

const ALLOWED: Record<SubOrderStatus, SubOrderStatus[]> = {
  pending: ["accepted", "cancelled"],
  accepted: ["preparing", "cancelled"],
  preparing: ["dispatched", "cancelled"],
  dispatched: ["delivered"],
  delivered: [],
  cancelled: [],
};

export function assertTransition(from: SubOrderStatus, to: SubOrderStatus) {
  if (!ALLOWED[from].includes(to)) {
    throw new InvalidTransitionError(from, to);
  }
}

export function deriveParentStatus(children: SubOrderStatus[]) {
  const active = children.filter((s) => s !== "cancelled");
  if (active.length === 0) return "cancelled";
  if (active.every((s) => s === "delivered")) return "completed";
  if (active.some((s) => s === "dispatched" || s === "delivered")) {
    return "partially_fulfilled";
  }
  return "in_progress";
}

Authorisation follows the same shape. Every merchant query is scoped by merchant_id at the data layer, ideally with row-level security in the database, so a merchant dashboard is unable to read another merchant's sub-order even if an API handler forgets a filter.

5. Commissions, Payouts and Partial Refunds

The ledger is append-only. When a payment is confirmed, each sub-order gets two entries: the merchant's payable amount and the platform's commission. Together they equal the sub-order subtotal, and across all sub-orders they equal what the customer paid. That equality is checked by a nightly job; a mismatch is a bug to find today, not at the end of the quarter.

A partial refund is where the two-level model pays for itself. If the grocery merchant cancels, only that sub-order changes state. The customer is refunded that subtotal, and the ledger receives reversing entries for that sub-order alone. The shoe merchant's order, payout and commission are untouched. Nothing is ever updated in place, so the full history of every payout can be reconstructed.

• Payouts are computed from ledger entries for delivered sub-orders, never from live order rows.
• Refunds add negative entries; they do not delete or edit earlier ones.
• Commission rates are read from the sub-order, so a rate change never alters past orders.
• A hold period between delivery and payout covers returns without clawing money back.

6. Hyper-Local Discovery with PostGIS

Marketplaces that sell local goods and services need a second capability: finding what is near the user. In Karnival, a city-based classifieds app, and in the storefront discovery of Bipeers, the first screen is a question of distance.

The naive approach loads listings into the application and computes the Haversine distance for each in a loop. It works for a hundred rows and collapses at a hundred thousand, because every search reads the whole table.

PostgreSQL with the PostGIS extension does this in the database with a spatial index. Locations are stored as GEOGRAPHY(Point, 4326), which means latitude and longitude on the WGS 84 globe with distances in metres. A GiST index on that column lets ST_DWithin discard everything outside the radius without measuring it, which is how radius filtering stays under 50ms on large tables.

ALTER TABLE listings
  ADD COLUMN location GEOGRAPHY(Point, 4326) NOT NULL;

CREATE INDEX listings_location_gix ON listings USING GIST (location);

-- Listings within 5 km of the user, nearest first.
-- Note the argument order: longitude first, then latitude.
SELECT id, title, price,
       ST_Distance(location, ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)::geography) AS metres
  FROM listings
 WHERE status = 'active'
   AND category_id = :category_id
   AND ST_DWithin(location, ST_SetSRID(ST_MakePoint(:lng, :lat), 4326)::geography, 5000)
 ORDER BY metres
 LIMIT 30;

Two mistakes are common. Swapping latitude and longitude in ST_MakePoint returns plausible-looking results from the wrong part of the world. And wrapping the indexed column in a function, or filtering on ST_Distance(...) < 5000 instead of ST_DWithin, silently skips the index and brings back the full table scan.

7. Clustering Map Pins on the Server

A map of a busy city can contain thousands of listings in view. Sending them all to the phone and drawing a marker for each makes the map unusable and the frame rate collapse.

The app sends the visible bounding box and zoom level, and the database returns clusters. At low zoom, points are snapped to a coarse grid and each cell comes back as one marker with a count. As the user zooms in, the grid gets finer until individual listings appear. The phone only ever draws a few dozen markers, which is how Karnival keeps its map at 60fps.

-- :cell is the grid size in degrees, chosen from the zoom level.
SELECT ST_X(cell) AS lng,
       ST_Y(cell) AS lat,
       count(*)   AS listings,
       min(id::text) AS sample_id
  FROM (
    SELECT id,
           ST_SnapToGrid(location::geometry, :cell) AS cell
      FROM listings
     WHERE status = 'active'
       AND location::geometry && ST_MakeEnvelope(
             :west, :south, :east, :north, 4326)
  ) snapped
 GROUP BY cell;

When a cluster's count is one, the app uses sample_id to show the listing itself. An index on the geometry cast of the column keeps the bounding-box filter fast.

8. Putting It Together

Order splitting and spatial search look like separate topics, but they follow the same rule: put the invariant in the database. An order is split in one transaction, so it cannot be half-created. Statuses change through declared transitions, so they cannot skip steps. Money moves only through an append-only ledger, so it can always be explained. Distance is computed by an index built for it, so it stays fast as the catalogue grows.

Bipeers shipped as a full ecosystem in 14 weeks on this foundation, with customer, merchant and freelancer roles in one architecture. Karnival went from concept to release in eight.

If you are planning a marketplace, classifieds platform or any product with several sellers behind one checkout, see our marketplace and SaaS development service and the backend and API engineering behind it, or describe your project and we will outline the data model with you.