Fixing Silent Stock Lockups in E-Commerce: The Abandoned Checkout Problem

The Bug Report

A simple complaint: "Can't checkout — insufficient stock." The product showed as available in the admin panel. Stock quantity looked fine. But every attempt to place an order failed with a stock validation error.

How Stock Reservation Works

The system uses a two-phase stock model:

• available_quantity — total stock on hand
• reserved_quantity — units held by active orders
• purchasable = available_quantity - reserved_quantity

When a customer places an order, reserved_quantity increases immediately. It only decreases when the order is cancelled or fulfilled (shipped). This is intentional, it prevents overselling when multiple customers checkout simultaneously.

The checkout validation is straightforward:

const purchasable = stock.available_quantity - stock.reserved_quantity;
return purchasable >= quantity;

If purchasable hits zero, no one can buy, even if the physical stock is sitting in a warehouse.

The Root Cause: Leaked Reservations

Querying the database revealed the problem immediately:

SELECT v.variant_name, s.available_quantity, s.reserved_quantity,
  (s.available_quantity - s.reserved_quantity) AS purchasable
FROM inventory_stocks s
JOIN product_variants v ON v.id = s.variant_id
WHERE (s.available_quantity - s.reserved_quantity) <= 0;

Several variants had purchasable = 0 despite having physical stock. The reserved quantity matched or exceeded available quantity entirely.

Digging into which orders were holding those reservations revealed the culprit: 19 orders in pending status with payment_status = 'awaiting_payment', some over 15 days old. These were abandoned checkouts where the customer opened the payment gateway page and never completed payment.

The system had no mechanism to expire them. So their stock reservations held forever.

Why Stock Was Never Released

The releaseStock() function existed and was called correctly, but only in one place: the explicit order cancellation flow. That flow requires a user (admin or customer) to actively cancel the order.

For abandoned payments:

• The customer closed the browser tab
• The payment gateway timed out silently
• No webhook fired (no payment = no webhook)
• No user ever hit "cancel"
• The order sat in pending + awaiting_payment forever
• Reserved stock: permanently locked

This is a classic reservation leak, a two-phase commit where phase 2 (release) is contingent on a user action that may never come.

The Fix: Two Parts

Part 1: Immediate Data Repair

The existing leaked reservations needed to be corrected directly in the database:

BEGIN;

-- Release reserved stock from abandoned orders
UPDATE inventory_stocks s
SET reserved_quantity = GREATEST(0, s.reserved_quantity - sub.qty_to_release)
FROM (
  SELECT oi.variant_id, SUM(oi.quantity) AS qty_to_release
  FROM sales_orders o
  JOIN sales_order_items oi ON oi.order_id = o.id
  WHERE o.status = 'pending'
    AND o.payment_status = 'awaiting_payment'
    AND o.created_at < NOW() - INTERVAL '24 hours'
    AND o.deleted_at IS NULL
    AND oi.item_type = 'product'
  GROUP BY oi.variant_id
) sub
WHERE s.variant_id = sub.variant_id;

-- Cancel the abandoned orders
UPDATE sales_orders
SET
  status = 'cancelled',
  cancellation_reason = 'Payment not completed within 24 hours',
  cancelled_at = NOW()
WHERE status = 'pending'
  AND payment_status = 'awaiting_payment'
  AND created_at < NOW() - INTERVAL '24 hours'
  AND deleted_at IS NULL;

COMMIT;

Note the GREATEST(0, ...) guard. This prevents reserved_quantity from going negative if the data was already inconsistent.

Part 2: Permanent Fix, Auto-Expire Scheduler

The existing project already had a NestJS scheduler service for auto-completing delivered orders. The auto-expire job was added alongside it:

@Cron(CronExpression.EVERY_HOUR)
async autoExpireAbandonedPayments(): Promise<void> {
  const cutoffDate = new Date();
  cutoffDate.setHours(cutoffDate.getHours() - 24);

  // payment_status isn't mapped on the TypeORM entity, so raw query is needed
  const expiredOrders = await this.orderRepository.query(
    `SELECT id, order_number FROM sales_orders
     WHERE status = $1
       AND payment_status = 'awaiting_payment'
       AND created_at < $2
       AND deleted_at IS NULL`,
    [OrderStatusEnum.PENDING, cutoffDate],
  );

  for (const order of expiredOrders) {
    const items = await this.orderItemRepository.find({
      where: { order: { id: order.id } },
    });

    // Release reserved stock before cancelling
    for (const item of items) {
      if (item.item_type === CartItemTypeEnum.PRODUCT && item.variant_id) {
        await this.inventoryStocksService.releaseStock(
          item.variant_id,
          item.quantity,
          null, // system-initiated, no causer user
        );
      }
    }

    await this.orderRepository.update(order.id, {
      status: OrderStatusEnum.CANCELLED,
      cancellation_reason: 'Payment not completed within 24 hours',
      cancelled_at: new Date(),
    });

    await this.orderTrackingService.createEvent(
      order.id,
      OrderEventTypeEnum.CANCELLED,
      'Order auto-cancelled: payment not completed within 24 hours',
    );
  }
}

A Key Gotcha: Unmapped Columns

The payment_status column existed in the database but wasn't mapped as a @Column() on the TypeORM entity. TypeORM's find() and findOne() can only filter on mapped fields. For unmapped columns, a raw query via repository.query() is the escape hatch, no need to modify the entity just for a scheduler query.

The Pattern to Watch For

Any system that increases a counter at checkout but only decreases it on explicit user action has this vulnerability. The fix always has the same shape:

1. Identify the reservation leak (orders holding stock past their useful life)
2. Write a one-time correction query with safe guards (GREATEST(0, ...))
3. Add a scheduled job that runs the same logic automatically going forward
4. Release the resource before changing the order status, never after

The last point matters: if the stock release fails mid-loop, the order stays pending and the next hourly run will retry it. If you cancel first and release after, a failure between those two steps leaves a cancelled order with stock that's still reserved.