allanninal.dev / shopware

Field notes / Inventory and Stock

Reconciler Inventory and Stock

Overselling of closeout items under concurrent checkout

A closeout item, marked isCloseout because there is no restock coming, has exactly three left. Then a sale email goes out, or the product lands on a deal site, and within the same second two or three shoppers all hit checkout. Every one of them sees the item in stock. Every one of them gets a confirmation email. But there were only three units, and now availableStock reads negative and someone paid for a unit that does not exist. Here is why Shopware lets this happen and a small script that finds it and tells you exactly which paid order is the unfulfillable one.

Python and Node.js Admin API Safe by default (dry run)
The short answer

In Shopware 6, product.availableStock is derived as stock minus the sum of quantities on open, uncancelled orders, and the legacy StockUpdater recalculates and writes that value with a plain SQL update per checkout, not a single atomic conditional decrement guarded by a row lock across the whole check-then-write span. The "is there enough stock" read and the order-placement write happen as separate steps in the request lifecycle, so two concurrent checkouts for the same closeout SKU can both read the same pre-decrement value, both pass validation, and both commit. availableStock (and eventually stock) goes below zero. The fix here is not to auto-cancel anyone. It is a reconciler that finds every closeout product with negative stock, pulls the paid orders that reference it in chronological order, and runs them through a pure FIFO allocation function that tells you precisely which order is fulfillable and which is the oversold excess, so a human can make the actual business call. Full code, tests, and sources are below.

The problem in plain words

Every checkout in Shopware asks the same question before it lets an order through: is there enough stock left. For a normal product that restocks, being a little wrong for a moment is rarely a disaster. For a closeout item, isCloseout true and no restock expected, being wrong means selling something that will never exist to fulfill.

The trouble is in how that question gets answered and when. availableStock is read once to decide if the checkout can proceed, and the order is written in a later step of the same request lifecycle. Those are two separate operations, not one atomic one. If two shoppers reach that read at close to the same moment, both see the same number, both see enough stock, and both go on to place an order. Only after both orders exist does anyone recompute availableStock, and by then it is too late, the number is negative and one of those two orders cannot actually be filled.

Checkout A reads availableStock = 1 Checkout B reads availableStock = 1 = same value, same moment A passes check order A placed B passes check order B placed Both orders committed recompute runs too late availableStock = -1 one order cannot ship
The read and the write are two separate steps. Two checkouts landing between them both see stock available, and both commit.

Why it happens

This is not a bug unique to one storefront theme or a misconfigured plugin. It is a structural property of how Shopware 6 tracks stock. A few things make it concrete:

Shopware's own 6.5 "Stock storage refactoring" discussion and the "Available stock improvements" ADR both acknowledge this dual-value, non-realtime design as the source of exactly this kind of inconsistency, and the platform has since shipped an opt-in AbstractStockStorage system specifically to close the gap. A parallel real-world case, the voucher redemption race in GitHub issue #11245, shows the identical non-atomic check-then-act pattern biting a different code path under the same kind of concurrent checkout load. See the citations at the end for the exact threads and docs.

The key insight

Once two orders have both committed against a closeout item that only had stock for one, there is no script that can safely undo that on its own. Cancelling a paid order is not a stock correction, it is telling a real customer they do not get what they paid for, and deciding which customer that is belongs to a human, not automation. What a script can safely do is the detection and the math: find every closeout product sitting at negative stock, pull its paid orders in the order they actually paid, and run them through one pure function that tells you, in FIFO order, which orders fit inside the original stock and which do not. That turns "we have a mess" into "here is exactly one order number to make a decision about."

The fix, as a flow

We never force-cancel anything and we never write to an order automatically. The script finds closeout products with negative availableStock, pulls the paid orders that reference each one in chronological order, and classifies them with a pure FIFO function. Only after a human confirms which order or orders will be cancelled does the script, still gated by DRY_RUN, log the corrective PATCH and state transition calls it would make.

Scheduled job runs on a timer Find closeout products isCloseout=true and availableStock < 0 Pull paid orders sorted by paid timestamp, FIFO Deficit > 0? allocateFifoStock yes no deficit, leave it Report oversold human confirms, then DRY_RUN guarded PATCH
The script only reports which order is the oversold excess. Cancelling it, and correcting stock back to zero, both wait on a human confirming the call.

Build it step by step

1

Get an Admin API access token

Create an integration in Shopware under Settings, System, Integrations, and note its access key id and secret key. Exchange them for a bearer token with POST /api/oauth/token using grant_type: client_credentials. Keep the shop URL and the credentials in environment variables, never in the file.

setup (shell)
pip install requests

export SHOPWARE_URL="https://yourstore.example.com"
export SHOPWARE_CLIENT_ID="SWIAXXXXXXXXXXXXXXXXXXXXXX"
export SHOPWARE_CLIENT_SECRET="..."
export DRY_RUN="true"   # start safe, change to false to log the repair intent
setup (shell)
// Node 18+ has fetch built in, no dependencies needed

export SHOPWARE_URL="https://yourstore.example.com"
export SHOPWARE_CLIENT_ID="SWIAXXXXXXXXXXXXXXXXXXXXXX"
export SHOPWARE_CLIENT_SECRET="..."
export DRY_RUN="true"   // start safe, change to false to log the repair intent
2

Authenticate and call the Admin API

A small helper gets a bearer token once, then sends every request with Authorization: Bearer <token> and Accept: application/json. We reuse it for every search and for the guarded PATCH and state-transition calls.

step2.py
import os, requests

SHOPWARE_URL = os.environ["SHOPWARE_URL"].rstrip("/")
CLIENT_ID = os.environ["SHOPWARE_CLIENT_ID"]
CLIENT_SECRET = os.environ["SHOPWARE_CLIENT_SECRET"]

def get_token():
    r = requests.post(
        f"{SHOPWARE_URL}/api/oauth/token",
        json={"grant_type": "client_credentials", "client_id": CLIENT_ID, "client_secret": CLIENT_SECRET},
        timeout=30,
    )
    r.raise_for_status()
    return r.json()["access_token"]

def api_post(path, token, body=None):
    r = requests.post(
        f"{SHOPWARE_URL}{path}",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json", "Content-Type": "application/json"},
        json=body or {},
        timeout=30,
    )
    r.raise_for_status()
    return r.json() if r.text else {}
step2.js
const SHOPWARE_URL = (process.env.SHOPWARE_URL || "").replace(/\/$/, "");
const CLIENT_ID = process.env.SHOPWARE_CLIENT_ID;
const CLIENT_SECRET = process.env.SHOPWARE_CLIENT_SECRET;

async function getToken() {
  const res = await fetch(`${SHOPWARE_URL}/api/oauth/token`, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ grant_type: "client_credentials", client_id: CLIENT_ID, client_secret: CLIENT_SECRET }),
  });
  if (!res.ok) throw new Error(`Shopware auth ${res.status}`);
  const body = await res.json();
  return body.access_token;
}

async function apiPost(path, token, body = {}) {
  const res = await fetch(`${SHOPWARE_URL}${path}`, {
    method: "POST",
    headers: { Authorization: `Bearer ${token}`, Accept: "application/json", "Content-Type": "application/json" },
    body: JSON.stringify(body),
  });
  if (!res.ok) throw new Error(`Shopware ${res.status} on ${path}`);
  const text = await res.text();
  return text ? JSON.parse(text) : {};
}
3

Find closeout products sitting at negative stock

Search product for isCloseout equals true and availableStock less than zero. If the range filter is not supported on a given version, page through isCloseout=true products and filter client-side on availableStock < 0.

step3.py
def search_negative_closeout_products(token, limit=500):
    body = {
        "page": 1,
        "limit": limit,
        "filter": [
            {"type": "equals", "field": "isCloseout", "value": True},
            {"type": "range", "field": "availableStock", "parameters": {"lt": 0}},
        ],
        "associations": {"orderLineItems": {}},
        "total-count-mode": 1,
    }
    r = requests.post(
        f"{SHOPWARE_URL}/api/search/product",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json", "Content-Type": "application/json"},
        json=body,
        timeout=30,
    )
    r.raise_for_status()
    return r.json()["data"]
step3.js
async function searchNegativeCloseoutProducts(token, limit = 500) {
  const body = {
    page: 1,
    limit,
    filter: [
      { type: "equals", field: "isCloseout", value: true },
      { type: "range", field: "availableStock", parameters: { lt: 0 } },
    ],
    associations: { orderLineItems: {} },
    "total-count-mode": 1,
  };
  const res = await fetch(`${SHOPWARE_URL}/api/search/product`, {
    method: "POST",
    headers: { Authorization: `Bearer ${token}`, Accept: "application/json", "Content-Type": "application/json" },
    body: JSON.stringify(body),
  });
  if (!res.ok) throw new Error(`Shopware ${res.status} on product search`);
  const data = await res.json();
  return data.data;
}
4

Pull the chronological order queue for each product

For each affected product.id, search order-line-item filtered by productId, with the order and order.transactions associations, sorted by order.orderDateTime ascending. Then keep only the line items whose order has a transaction in a paid or paid_partially state, read from stateMachineState.technicalName. Those are the binding claims on the unit.

step4.py
PAID_STATES = {"paid", "paid_partially"}

def search_order_line_items_for_product(token, product_id, limit=500):
    body = {
        "page": 1,
        "limit": limit,
        "filter": [{"type": "equals", "field": "productId", "value": product_id}],
        "associations": {"order": {"associations": {"transactions": {}}}},
        "sort": [{"field": "order.orderDateTime", "order": "ASC"}],
        "total-count-mode": 1,
    }
    r = requests.post(
        f"{SHOPWARE_URL}/api/search/order-line-item",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json", "Content-Type": "application/json"},
        json=body,
        timeout=30,
    )
    r.raise_for_status()
    return r.json()["data"]

def is_paid_order(order):
    transactions = order.get("transactions") or []
    return any(
        (t.get("stateMachineState") or {}).get("technicalName") in PAID_STATES
        for t in transactions
    )

def paid_orders_queue(line_items):
    queue = []
    for item in line_items:
        order = item.get("order") or {}
        if not is_paid_order(order):
            continue
        queue.append({
            "orderId": order.get("id"),
            "quantity": item.get("quantity", 0),
            "paidAt": order.get("orderDateTime"),
        })
    return queue
step4.js
const PAID_STATES = new Set(["paid", "paid_partially"]);

async function searchOrderLineItemsForProduct(token, productId, limit = 500) {
  const body = {
    page: 1,
    limit,
    filter: [{ type: "equals", field: "productId", value: productId }],
    associations: { order: { associations: { transactions: {} } } },
    sort: [{ field: "order.orderDateTime", order: "ASC" }],
    "total-count-mode": 1,
  };
  const res = await fetch(`${SHOPWARE_URL}/api/search/order-line-item`, {
    method: "POST",
    headers: { Authorization: `Bearer ${token}`, Accept: "application/json", "Content-Type": "application/json" },
    body: JSON.stringify(body),
  });
  if (!res.ok) throw new Error(`Shopware ${res.status} on order-line-item search`);
  const data = await res.json();
  return data.data;
}

function isPaidOrder(order) {
  const transactions = order.transactions || [];
  return transactions.some((t) => PAID_STATES.has(t.stateMachineState?.technicalName));
}

function paidOrdersQueue(lineItems) {
  const queue = [];
  for (const item of lineItems) {
    const order = item.order || {};
    if (!isPaidOrder(order)) continue;
    queue.push({ orderId: order.id, quantity: item.quantity || 0, paidAt: order.orderDateTime });
  }
  return queue;
}
5

Decide, with one pure function

Keep the FIFO allocation separate from any network call. Given the physical stock at closeout activation and the list of paid orders, sort by paidAt ascending, tie-break by orderId for determinism, then walk the list accumulating quantity. Every order that fits stays fulfillable. The first order that would push the running total over the stock, and everything after it, becomes the oversold excess. Nothing here talks to the network, so it is trivial to test with fixture data.

decide.py
def allocate_fifo_stock(initial_stock, paid_orders):
    ordered = sorted(paid_orders, key=lambda o: (o["paidAt"], o["orderId"]))

    fulfillable = []
    oversold = []
    running_total = 0
    over_limit = False

    for order in ordered:
        running_total += order["quantity"]
        if not over_limit and running_total <= initial_stock:
            fulfillable.append(order["orderId"])
        else:
            over_limit = True
            oversold.append(order["orderId"])

    total_quantity = sum(o["quantity"] for o in paid_orders)
    deficit = max(0, total_quantity - initial_stock)

    return {"fulfillable": fulfillable, "oversold": oversold, "deficit": deficit}
decide.js
export function allocateFifoStock(initialStock, paidOrders) {
  const ordered = [...paidOrders].sort((a, b) => {
    if (a.paidAt < b.paidAt) return -1;
    if (a.paidAt > b.paidAt) return 1;
    return a.orderId < b.orderId ? -1 : a.orderId > b.orderId ? 1 : 0;
  });

  const fulfillable = [];
  const oversold = [];
  let runningTotal = 0;
  let overLimit = false;

  for (const order of ordered) {
    runningTotal += order.quantity;
    if (!overLimit && runningTotal <= initialStock) {
      fulfillable.push(order.orderId);
    } else {
      overLimit = true;
      oversold.push(order.orderId);
    }
  }

  const totalQuantity = paidOrders.reduce((sum, o) => sum + o.quantity, 0);
  const deficit = Math.max(0, totalQuantity - initialStock);

  return { fulfillable, oversold, deficit };
}
6

Report only, never auto-repair

When oversold is not empty, log the classification and stop. The only writes this script ever makes, and only when a human has confirmed which order or orders will be cancelled, are a DRY_RUN guarded PATCH /api/product/{id} that corrects availableStock back to zero, never negative, and, per confirmed order, POST /api/_action/order/{orderId}/state/cancel followed by POST /api/_action/order_transaction/{transactionId}/state/cancel or /refund if already captured. Both are gated behind the same flag, and both only log the intended call while DRY_RUN is true.

apply.py
def correct_available_stock(token, product_id, dry_run):
    if dry_run:
        log.info("DRY_RUN: would PATCH product %s availableStock to 0", product_id)
        return
    r = requests.patch(
        f"{SHOPWARE_URL}/api/product/{product_id}",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json", "Content-Type": "application/json"},
        json={"availableStock": 0},
        timeout=30,
    )
    r.raise_for_status()

def cancel_confirmed_order(token, order_id, transaction_id, dry_run, already_captured=False):
    if dry_run:
        log.info("DRY_RUN: would cancel order %s and its transaction %s", order_id, transaction_id)
        return
    api_post(f"/api/_action/order/{order_id}/state/cancel", token)
    transition = "refund" if already_captured else "cancel"
    api_post(f"/api/_action/order_transaction/{transaction_id}/state/{transition}", token)
apply.js
async function correctAvailableStock(token, productId, dryRun) {
  if (dryRun) {
    console.log(`DRY_RUN: would PATCH product ${productId} availableStock to 0`);
    return;
  }
  const res = await fetch(`${SHOPWARE_URL}/api/product/${productId}`, {
    method: "PATCH",
    headers: { Authorization: `Bearer ${token}`, Accept: "application/json", "Content-Type": "application/json" },
    body: JSON.stringify({ availableStock: 0 }),
  });
  if (!res.ok) throw new Error(`Shopware ${res.status} on product patch`);
}

async function cancelConfirmedOrder(token, orderId, transactionId, dryRun, alreadyCaptured = false) {
  if (dryRun) {
    console.log(`DRY_RUN: would cancel order ${orderId} and its transaction ${transactionId}`);
    return;
  }
  await apiPost(`/api/_action/order/${orderId}/state/cancel`, token);
  const transition = alreadyCaptured ? "refund" : "cancel";
  await apiPost(`/api/_action/order_transaction/${transactionId}/state/${transition}`, token);
}
7

Wire it together with a dry run guard

The loop ties every piece together. For each negative-stock closeout product, pull its paid orders in chronological order, run allocateFifoStock, and log the classification. The repair calls only ever run against orders a human has separately confirmed, and even then only once DRY_RUN is explicitly set to false.

Run it safe

This script never auto-cancels or auto-refunds. It reports which paid order is the oversold excess and leaves the decision to a human. Only PATCH the product's stock, and only cancel or refund a specific order, after that decision is made, and always start with DRY_RUN=true so the first thing you see is the intended calls, not their effects.

The full code

Here is the complete script in one file for each language. It authenticates, finds closeout products with negative stock, pulls each one's paid orders in FIFO order, classifies them with the pure function, and logs the guarded repair calls it would make, respecting DRY_RUN throughout.

View this code on GitHub Full runnable folder with tests in the shopware-fixes repo.

reconcile_overselling_closeout.py
"""Find Shopware 6 closeout products oversold under concurrent checkout, and
classify their paid orders in FIFO order, without ever auto-cancelling or
auto-refunding a customer's order.

availableStock is stock minus the sum of quantities on open orders, and the
legacy StockUpdater recalculates and writes it with a plain SQL update per
checkout rather than one atomic conditional decrement guarded by a row lock
across the whole check-then-write span. Two concurrent checkouts for the same
closeout SKU (isCloseout=true, no restock expected) can both read the same
pre-decrement value, both pass validation, and both commit, driving
availableStock below zero.

This script finds every closeout product sitting at negative availableStock,
pulls the paid orders that reference it in chronological order, and runs them
through allocate_fifo_stock, a pure function, to identify which order(s) are
legitimately fulfillable and which are the unfulfillable oversell. Report
only. All writes (the availableStock correction and any order cancel or
refund) are gated behind DRY_RUN, default true, and only ever run against
orders a human has separately confirmed.
"""
import os
import logging
import requests

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("reconcile_overselling_closeout")

SHOPWARE_URL = os.environ["SHOPWARE_URL"].rstrip("/")
CLIENT_ID = os.environ["SHOPWARE_CLIENT_ID"]
CLIENT_SECRET = os.environ["SHOPWARE_CLIENT_SECRET"]
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"

PAID_STATES = {"paid", "paid_partially"}


def allocate_fifo_stock(initial_stock, paid_orders):
    """Pure decision. No I/O. Classifies paid orders against physical stock."""
    ordered = sorted(paid_orders, key=lambda o: (o["paidAt"], o["orderId"]))

    fulfillable = []
    oversold = []
    running_total = 0
    over_limit = False

    for order in ordered:
        running_total += order["quantity"]
        if not over_limit and running_total <= initial_stock:
            fulfillable.append(order["orderId"])
        else:
            over_limit = True
            oversold.append(order["orderId"])

    total_quantity = sum(o["quantity"] for o in paid_orders)
    deficit = max(0, total_quantity - initial_stock)

    return {"fulfillable": fulfillable, "oversold": oversold, "deficit": deficit}


def get_token():
    r = requests.post(
        f"{SHOPWARE_URL}/api/oauth/token",
        json={"grant_type": "client_credentials", "client_id": CLIENT_ID, "client_secret": CLIENT_SECRET},
        timeout=30,
    )
    r.raise_for_status()
    return r.json()["access_token"]


def api_post(path, token, body=None):
    r = requests.post(
        f"{SHOPWARE_URL}{path}",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json", "Content-Type": "application/json"},
        json=body or {},
        timeout=30,
    )
    r.raise_for_status()
    return r.json() if r.text else {}


def search_negative_closeout_products(token, limit=500):
    body = {
        "page": 1,
        "limit": limit,
        "filter": [
            {"type": "equals", "field": "isCloseout", "value": True},
            {"type": "range", "field": "availableStock", "parameters": {"lt": 0}},
        ],
        "associations": {"orderLineItems": {}},
        "total-count-mode": 1,
    }
    r = requests.post(
        f"{SHOPWARE_URL}/api/search/product",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json", "Content-Type": "application/json"},
        json=body,
        timeout=30,
    )
    r.raise_for_status()
    return r.json()["data"]


def is_paid_order(order):
    transactions = order.get("transactions") or []
    return any(
        (t.get("stateMachineState") or {}).get("technicalName") in PAID_STATES
        for t in transactions
    )


def paid_orders_queue(token, product_id, limit=500):
    body = {
        "page": 1,
        "limit": limit,
        "filter": [{"type": "equals", "field": "productId", "value": product_id}],
        "associations": {"order": {"associations": {"transactions": {}}}},
        "sort": [{"field": "order.orderDateTime", "order": "ASC"}],
        "total-count-mode": 1,
    }
    r = requests.post(
        f"{SHOPWARE_URL}/api/search/order-line-item",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json", "Content-Type": "application/json"},
        json=body,
        timeout=30,
    )
    r.raise_for_status()
    line_items = r.json()["data"]

    queue = []
    for item in line_items:
        order = item.get("order") or {}
        if not is_paid_order(order):
            continue
        queue.append({
            "orderId": order.get("id"),
            "quantity": item.get("quantity", 0),
            "paidAt": order.get("orderDateTime"),
        })
    return queue


def correct_available_stock(token, product_id):
    if DRY_RUN:
        log.info("DRY_RUN: would PATCH product %s availableStock to 0", product_id)
        return
    r = requests.patch(
        f"{SHOPWARE_URL}/api/product/{product_id}",
        headers={"Authorization": f"Bearer {token}", "Accept": "application/json", "Content-Type": "application/json"},
        json={"availableStock": 0},
        timeout=30,
    )
    r.raise_for_status()


def cancel_confirmed_order(token, order_id, transaction_id, already_captured=False):
    if DRY_RUN:
        log.info("DRY_RUN: would cancel order %s and its transaction %s", order_id, transaction_id)
        return
    api_post(f"/api/_action/order/{order_id}/state/cancel", token)
    transition = "refund" if already_captured else "cancel"
    api_post(f"/api/_action/order_transaction/{transaction_id}/state/{transition}", token)


def run():
    token = get_token()
    products = search_negative_closeout_products(token)
    log.info("Found %d closeout product(s) with negative availableStock.", len(products))

    for product in products:
        attrs = product.get("attributes", product)
        product_id = product.get("id") or attrs.get("id")
        stock = attrs.get("stock", 0)

        paid_orders = paid_orders_queue(token, product_id)
        result = allocate_fifo_stock(stock, paid_orders)

        log.info(
            "Product %s: %d fulfillable, %d oversold, deficit=%d",
            product_id, len(result["fulfillable"]), len(result["oversold"]), result["deficit"],
        )
        if result["oversold"]:
            log.warning(
                "Product %s has oversold order(s) needing a human decision: %s",
                product_id, result["oversold"],
            )
            # Corrective action only proceeds once a human has confirmed which
            # order(s) above are the ones being cancelled. Nothing here does
            # that automatically. correct_available_stock(token, product_id)
            # and cancel_confirmed_order(...) are logged-only under DRY_RUN and
            # are meant to be invoked explicitly, per order, after that
            # confirmation, not looped over automatically.


if __name__ == "__main__":
    run()
reconcile-overselling-closeout.js
/**
 * Find Shopware 6 closeout products oversold under concurrent checkout, and
 * classify their paid orders in FIFO order, without ever auto-cancelling or
 * auto-refunding a customer's order.
 *
 * availableStock is stock minus the sum of quantities on open orders, and the
 * legacy StockUpdater recalculates and writes it with a plain SQL update per
 * checkout rather than one atomic conditional decrement guarded by a row lock
 * across the whole check-then-write span. Two concurrent checkouts for the
 * same closeout SKU (isCloseout=true, no restock expected) can both read the
 * same pre-decrement value, both pass validation, and both commit, driving
 * availableStock below zero.
 *
 * This script finds every closeout product sitting at negative availableStock,
 * pulls the paid orders that reference it in chronological order, and runs
 * them through allocateFifoStock, a pure function, to identify which order(s)
 * are legitimately fulfillable and which are the unfulfillable oversell.
 * Report only. All writes are gated behind DRY_RUN, default true, and only
 * ever run against orders a human has separately confirmed.
 *
 * Guide: https://www.allanninal.dev/shopware/overselling-closeout-concurrency/
 */
import { pathToFileURL } from "node:url";

const SHOPWARE_URL = (process.env.SHOPWARE_URL || "https://example.test").replace(/\/$/, "");
const CLIENT_ID = process.env.SHOPWARE_CLIENT_ID || "dummy-client-id";
const CLIENT_SECRET = process.env.SHOPWARE_CLIENT_SECRET || "dummy-secret";
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";

const PAID_STATES = new Set(["paid", "paid_partially"]);

export function allocateFifoStock(initialStock, paidOrders) {
  const ordered = [...paidOrders].sort((a, b) => {
    if (a.paidAt < b.paidAt) return -1;
    if (a.paidAt > b.paidAt) return 1;
    return a.orderId < b.orderId ? -1 : a.orderId > b.orderId ? 1 : 0;
  });

  const fulfillable = [];
  const oversold = [];
  let runningTotal = 0;
  let overLimit = false;

  for (const order of ordered) {
    runningTotal += order.quantity;
    if (!overLimit && runningTotal <= initialStock) {
      fulfillable.push(order.orderId);
    } else {
      overLimit = true;
      oversold.push(order.orderId);
    }
  }

  const totalQuantity = paidOrders.reduce((sum, o) => sum + o.quantity, 0);
  const deficit = Math.max(0, totalQuantity - initialStock);

  return { fulfillable, oversold, deficit };
}

async function getToken() {
  const res = await fetch(`${SHOPWARE_URL}/api/oauth/token`, {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ grant_type: "client_credentials", client_id: CLIENT_ID, client_secret: CLIENT_SECRET }),
  });
  if (!res.ok) throw new Error(`Shopware auth ${res.status}`);
  const body = await res.json();
  return body.access_token;
}

async function apiPost(path, token, body = {}) {
  const res = await fetch(`${SHOPWARE_URL}${path}`, {
    method: "POST",
    headers: { Authorization: `Bearer ${token}`, Accept: "application/json", "Content-Type": "application/json" },
    body: JSON.stringify(body),
  });
  if (!res.ok) throw new Error(`Shopware ${res.status} on ${path}`);
  const text = await res.text();
  return text ? JSON.parse(text) : {};
}

async function searchNegativeCloseoutProducts(token, limit = 500) {
  const body = {
    page: 1,
    limit,
    filter: [
      { type: "equals", field: "isCloseout", value: true },
      { type: "range", field: "availableStock", parameters: { lt: 0 } },
    ],
    associations: { orderLineItems: {} },
    "total-count-mode": 1,
  };
  const res = await fetch(`${SHOPWARE_URL}/api/search/product`, {
    method: "POST",
    headers: { Authorization: `Bearer ${token}`, Accept: "application/json", "Content-Type": "application/json" },
    body: JSON.stringify(body),
  });
  if (!res.ok) throw new Error(`Shopware ${res.status} on product search`);
  const data = await res.json();
  return data.data;
}

function isPaidOrder(order) {
  const transactions = order.transactions || [];
  return transactions.some((t) => PAID_STATES.has(t.stateMachineState?.technicalName));
}

async function paidOrdersQueue(token, productId, limit = 500) {
  const body = {
    page: 1,
    limit,
    filter: [{ type: "equals", field: "productId", value: productId }],
    associations: { order: { associations: { transactions: {} } } },
    sort: [{ field: "order.orderDateTime", order: "ASC" }],
    "total-count-mode": 1,
  };
  const res = await fetch(`${SHOPWARE_URL}/api/search/order-line-item`, {
    method: "POST",
    headers: { Authorization: `Bearer ${token}`, Accept: "application/json", "Content-Type": "application/json" },
    body: JSON.stringify(body),
  });
  if (!res.ok) throw new Error(`Shopware ${res.status} on order-line-item search`);
  const data = await res.json();
  const lineItems = data.data || [];

  const queue = [];
  for (const item of lineItems) {
    const order = item.order || {};
    if (!isPaidOrder(order)) continue;
    queue.push({ orderId: order.id, quantity: item.quantity || 0, paidAt: order.orderDateTime });
  }
  return queue;
}

async function correctAvailableStock(token, productId) {
  if (DRY_RUN) {
    console.log(`DRY_RUN: would PATCH product ${productId} availableStock to 0`);
    return;
  }
  const res = await fetch(`${SHOPWARE_URL}/api/product/${productId}`, {
    method: "PATCH",
    headers: { Authorization: `Bearer ${token}`, Accept: "application/json", "Content-Type": "application/json" },
    body: JSON.stringify({ availableStock: 0 }),
  });
  if (!res.ok) throw new Error(`Shopware ${res.status} on product patch`);
}

async function cancelConfirmedOrder(token, orderId, transactionId, alreadyCaptured = false) {
  if (DRY_RUN) {
    console.log(`DRY_RUN: would cancel order ${orderId} and its transaction ${transactionId}`);
    return;
  }
  await apiPost(`/api/_action/order/${orderId}/state/cancel`, token);
  const transition = alreadyCaptured ? "refund" : "cancel";
  await apiPost(`/api/_action/order_transaction/${transactionId}/state/${transition}`, token);
}

export async function run() {
  const token = await getToken();
  const products = await searchNegativeCloseoutProducts(token);
  console.log(`Found ${products.length} closeout product(s) with negative availableStock.`);

  for (const product of products) {
    const attrs = product.attributes || product;
    const productId = product.id || attrs.id;
    const stock = attrs.stock || 0;

    const paidOrders = await paidOrdersQueue(token, productId);
    const result = allocateFifoStock(stock, paidOrders);

    console.log(
      `Product ${productId}: ${result.fulfillable.length} fulfillable, ${result.oversold.length} oversold, deficit=${result.deficit}`
    );
    if (result.oversold.length) {
      console.warn(`Product ${productId} has oversold order(s) needing a human decision: ${result.oversold.join(", ")}`);
      // Corrective action only proceeds once a human has confirmed which
      // order(s) above are the ones being cancelled. Nothing here does that
      // automatically. correctAvailableStock(token, productId) and
      // cancelConfirmedOrder(...) are logged-only under DRY_RUN and are meant
      // to be invoked explicitly, per order, after that confirmation, not
      // looped over automatically.
    }
  }
}

if (import.meta.url === pathToFileURL(process.argv[1]).href) {
  run().catch((err) => { console.error(err); process.exit(1); });
}

Add a test

The FIFO allocation is the part most worth testing, because it decides which order gets reported as the oversold excess. Because allocate_fifo_stock is pure and takes only plain data, the test needs no network, no Shopware store, and no message queue.

test_allocate_fifo_stock.py
from reconcile_overselling_closeout import allocate_fifo_stock


def po(order_id, quantity, paid_at):
    return {"orderId": order_id, "quantity": quantity, "paidAt": paid_at}


def test_all_fulfillable_when_stock_covers_everything():
    orders = [po("o1", 1, "2026-07-01T10:00:00Z"), po("o2", 1, "2026-07-01T10:00:01Z")]
    result = allocate_fifo_stock(5, orders)
    assert result["fulfillable"] == ["o1", "o2"]
    assert result["oversold"] == []
    assert result["deficit"] == 0


def test_oversold_when_two_concurrent_checkouts_exceed_stock():
    orders = [po("o1", 1, "2026-07-01T10:00:00.000Z"), po("o2", 1, "2026-07-01T10:00:00.001Z")]
    result = allocate_fifo_stock(1, orders)
    assert result["fulfillable"] == ["o1"]
    assert result["oversold"] == ["o2"]
    assert result["deficit"] == 1


def test_tie_break_by_order_id_when_paid_at_matches():
    orders = [po("o2", 1, "2026-07-01T10:00:00Z"), po("o1", 1, "2026-07-01T10:00:00Z")]
    result = allocate_fifo_stock(1, orders)
    assert result["fulfillable"] == ["o1"]
    assert result["oversold"] == ["o2"]


def test_deficit_never_negative_when_stock_exceeds_demand():
    orders = [po("o1", 2, "2026-07-01T10:00:00Z")]
    result = allocate_fifo_stock(10, orders)
    assert result["deficit"] == 0


def test_everything_after_first_overflow_is_oversold():
    orders = [
        po("o1", 2, "2026-07-01T10:00:00Z"),
        po("o2", 2, "2026-07-01T10:00:01Z"),
        po("o3", 1, "2026-07-01T10:00:02Z"),
    ]
    result = allocate_fifo_stock(3, orders)
    assert result["fulfillable"] == ["o1"]
    assert result["oversold"] == ["o2", "o3"]
    assert result["deficit"] == 2


def test_no_paid_orders_means_nothing_oversold():
    result = allocate_fifo_stock(5, [])
    assert result["fulfillable"] == []
    assert result["oversold"] == []
    assert result["deficit"] == 0
allocate-fifo-stock.test.js
import { test } from "node:test";
import assert from "node:assert/strict";
import { allocateFifoStock } from "./reconcile-overselling-closeout.js";

const po = (orderId, quantity, paidAt) => ({ orderId, quantity, paidAt });

test("all fulfillable when stock covers everything", () => {
  const orders = [po("o1", 1, "2026-07-01T10:00:00Z"), po("o2", 1, "2026-07-01T10:00:01Z")];
  const result = allocateFifoStock(5, orders);
  assert.deepEqual(result.fulfillable, ["o1", "o2"]);
  assert.deepEqual(result.oversold, []);
  assert.equal(result.deficit, 0);
});

test("oversold when two concurrent checkouts exceed stock", () => {
  const orders = [po("o1", 1, "2026-07-01T10:00:00.000Z"), po("o2", 1, "2026-07-01T10:00:00.001Z")];
  const result = allocateFifoStock(1, orders);
  assert.deepEqual(result.fulfillable, ["o1"]);
  assert.deepEqual(result.oversold, ["o2"]);
  assert.equal(result.deficit, 1);
});

test("tie break by order id when paidAt matches", () => {
  const orders = [po("o2", 1, "2026-07-01T10:00:00Z"), po("o1", 1, "2026-07-01T10:00:00Z")];
  const result = allocateFifoStock(1, orders);
  assert.deepEqual(result.fulfillable, ["o1"]);
  assert.deepEqual(result.oversold, ["o2"]);
});

test("deficit never negative when stock exceeds demand", () => {
  const orders = [po("o1", 2, "2026-07-01T10:00:00Z")];
  const result = allocateFifoStock(10, orders);
  assert.equal(result.deficit, 0);
});

test("everything after first overflow is oversold", () => {
  const orders = [
    po("o1", 2, "2026-07-01T10:00:00Z"),
    po("o2", 2, "2026-07-01T10:00:01Z"),
    po("o3", 1, "2026-07-01T10:00:02Z"),
  ];
  const result = allocateFifoStock(3, orders);
  assert.deepEqual(result.fulfillable, ["o1"]);
  assert.deepEqual(result.oversold, ["o2", "o3"]);
  assert.equal(result.deficit, 2);
});

test("no paid orders means nothing oversold", () => {
  const result = allocateFifoStock(5, []);
  assert.deepEqual(result.fulfillable, []);
  assert.deepEqual(result.oversold, []);
  assert.equal(result.deficit, 0);
});

Case studies

Flash closeout

Three units, a newsletter blast, and two paid orders

A furniture store marked a discontinued lamp isCloseout with three units left, then sent it out in a closeout newsletter to twenty thousand subscribers. Within the first two minutes, four checkouts for that lamp had all read the same availableStock and all committed. availableStock settled at negative one, and support had no way to tell which of the four paid orders was the one that could not ship.

Running the reconciler found the product immediately, since isCloseout=true and availableStock < 0 is a narrow, cheap filter. allocateFifoStock sorted the four paid orders by their paid timestamp and named the exact order that arrived after the other three had already claimed the three units. Support reached out to that one customer specifically, offered a substitute item, and closed the ticket without guessing.

Marketplace sync

A closeout SKU sold on two channels at once

A sporting goods seller pushed the same closeout SKU to their own storefront and a marketplace integration that synced stock every few minutes rather than in real time. During the sync gap, both channels accepted orders against the same handful of remaining units, and Shopware ended up with paid orders arriving from two different sales channels within seconds of each other for a product with only one unit left.

The team ran the reconciler nightly across all closeout SKUs. When a deficit showed up, the FIFO classification gave them a clean, defensible answer for who ordered first regardless of which channel the order came through, and the team used that to decide who got the unit and who got a refund and an apology, a call the script never had to make on its own.

What good looks like

After this runs on a schedule, a closeout oversell stops being a mystery negative number and becomes a short, specific list: this product, this many units short, this exact order that cannot be fulfilled. The FIFO rule is deterministic and auditable, so the same input always produces the same answer, and the actual cancellation or refund still requires a person to look at the order and confirm it. The script's only job is to make that person's decision fast and unambiguous.

FAQ

Why does Shopware 6 oversell a closeout item during a traffic spike?

availableStock is stock minus the quantities on open orders, and the legacy StockUpdater recalculates and writes that value with a plain SQL update rather than a single atomic conditional decrement guarded by a row lock across the whole check-then-write span. When two checkouts for the same closeout SKU happen close together, both can read the same pre-decrement availableStock, both pass the stock check, and both commit, pushing availableStock below zero.

Is it safe to just PATCH availableStock back to zero once I find the oversold product?

It is safe once you have identified which paid order or orders are the actual oversold excess, because that is a business decision about which customer loses their order. The script in this guide only reports the FIFO classification and logs the PATCH and cancel calls it would make. It never fixes stock or cancels an order automatically, and every write is gated behind DRY_RUN.

How do I decide which paid orders are legitimate and which are the oversell?

Sort the paid orders for the affected product chronologically by paid timestamp, then walk them in that order accumulating quantity against the physical stock at the moment the closeout was activated. Every order that fits within that stock is fulfillable. The first order that would push the running total over the stock, and every order after it, is the oversold excess. This is the same FIFO rule the allocateFifoStock function in this guide implements as a pure function.

Related field notes

Shopify orders stuck Unpaid after a payment made outside checkout Shopify orders stuck Unfulfilled past your shipping SLA Browse all Shopware field notes

Citations

On the problem:

  1. Stock storage refactoring, shopware/shopware Discussion #3172. github.com/shopware/shopware/discussions/3172
  2. Race Condition in Shopware Voucher Submission, shopware/shopware Issue #11245. github.com/shopware/shopware/issues/11245
  3. Demystifying the StockUpdater, Shopware Community Forum. forum.shopware.com/t/demystifying-the-stockupdater/70474

On the solution:

  1. Shopware Developer Documentation: Available stock improvements ADR. developer.shopware.com/docs/resources/references/adr/2022-03-25-available-stock.html
  2. Shopware Developer Documentation: Stock Manipulation API ADR. developer.shopware.com/docs/resources/references/adr/2023-05-15-stock-api.html
  3. Shopware Developer Documentation: Implementing Your Own Stock Storage. developer.shopware.com/docs/guides/plugins/plugins/content/stock/implementing-your-own-stock-storage.html

Stuck on a tricky one?

If you have a problem in Shopware orders, inventory, the state machine, or the message queue that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.

Contact me on LinkedIn

Did this untangle an overselling mess?

If this saved you a confusing negative stock number or an awkward call to a customer, you can buy me a coffee. It is the best way to keep these field notes free and growing.

Buy me a coffee on Ko-fi

Back to all Shopware field notes