Eliminating Flash Sale Overselling: A PostgreSQL Multi-Phase Reservation System

In the high-stakes world of wedding attire e-commerce, one dress can make or break a customer's most important day. Lamiya Wedding Center solved one of the hardest problems in e-commerce inventory management: preventing overselling during high-concurrency flash sales while maintaining sub-second checkout performance.

This technical deep-dive breaks down the sophisticated inventory reservation architecture behind their Next.js + PostgreSQL platform, a real-world system that treats inventory as a distributed resource and delivers enterprise-grade reliability without external transaction coordinators.

The Challenge: The Inventory Concurrency Trap

Traditional e-commerce platforms handle stock management through simple decrement operations:

UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0

This approach fails catastrophically under concurrent load:

1. Race Conditions: Two customers read stock = 1 simultaneously, both proceed to checkout, but only one can actually receive the item.

2. Abandoned Carts: Stock held during checkout expires but isn't reclaimed automatically.

3. Payment Gateway Timeouts: Orders complete payment, yet the inventory was already sold to someone else.

4. Flash Sale Stampedes: Sudden traffic spikes exhaust connection pools and create phantom inventory.

For a wedding center selling limited-run designer pieces with high-value transactions, inventory accuracy wasn't optional; it was existential.

The requirements were brutal:

• Zero tolerance for overselling

• Support for flash sales with 500+ concurrent users

• Time-bounded cart holds (30-minute windows)

• Idempotent payment webhook processing

• Rollback-safe transaction orchestration

The Architecture: A Multi-Phase Reservation Protocol with Database-Native Compensating Transactions

Instead of managing complex state in the application layer, we implemented a database-native reservation pattern using tightly integrated PostgreSQL RPC functions and a dedicated reserved_stock table. While this implements compensation logic similar to Saga patterns, it operates entirely within a single PostgreSQL instance, avoiding the distributed coordination overhead of true service-oriented Sagas.

This creates a reliable safety net while keeping the application stateless and horizontally scalable.

The Data Model

-- Core inventory table (simplified)
CREATE TABLE product_color_sizes (
  product_id UUID REFERENCES products(id),
  color_id UUID REFERENCES product_colors(id),
  size_id CHAR(3) REFERENCES sizes(id),
  stock_quantity INTEGER NOT NULL DEFAULT 0,
  low_stock_threshold INTEGER DEFAULT 5,
  PRIMARY KEY (product_id, color_id, size_id)
);

-- Reservation ledger; the secret sauce
CREATE TABLE reserved_stock (
  product_id UUID NOT NULL,
  color_id UUID NOT NULL,
  size_id CHAR(3) NOT NULL,
  checkout_session_id UUID NOT NULL,
  quantity INTEGER NOT NULL,
  status TEXT DEFAULT 'active', -- active | consumed | released
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

The reserved_stock table serves as a journal of intent. Customers reserve the right to purchase within a time window rather than directly holding physical inventory.

Phase 1: Atomic Reservation (reserve_stock_rpc)

When checkout begins, the system acquires row-level locks to prevent race conditions:

CREATE OR REPLACE FUNCTION reserve_stock_rpc(
  p_checkout_session_id uuid,
  p_items jsonb,
  p_reservation_minutes integer default 20
) RETURNS jsonb AS $
DECLARE
  v_item jsonb;
  v_product_id uuid;
  v_color_id uuid;
  v_size_id char(3);
  v_quantity integer;
  v_actual_stock integer;
  v_total_reserved integer;
  v_available integer;
  v_now timestamptz := NOW();
  v_out jsonb := '[]'::jsonb;
BEGIN
  FOR v_item IN SELECT jsonb_array_elements(p_items) LOOP
    v_product_id := (v_item->>'product_id')::uuid;
    v_color_id := (v_item->>'color_id')::uuid;
    v_size_id := (v_item->>'size_id')::char(3);
    v_quantity := (v_item->>'quantity')::integer;

    -- Row-level lock prevents concurrent modifications
    SELECT stock_quantity INTO v_actual_stock
    FROM product_color_sizes
    WHERE product_id = v_product_id
      AND color_id = v_color_id
      AND size_id = v_size_id
    FOR UPDATE;

    -- Effective available stock = physical stock minus active reservations
    SELECT COALESCE(SUM(quantity), 0) INTO v_total_reserved
    FROM reserved_stock
    WHERE product_id = v_product_id
      AND color_id = v_color_id
      AND size_id = v_size_id
      AND status = 'active'
      AND expires_at > v_now;

    v_available := v_actual_stock - v_total_reserved;

    IF v_available < v_quantity THEN
      RETURN jsonb_build_object('success', false, 'error', 'Out of stock');
    END IF;
  END LOOP;

  -- Idempotent: clear previous reservations for this session
  DELETE FROM reserved_stock WHERE checkout_session_id = p_checkout_session_id;

  -- Create new reservations
  INSERT INTO reserved_stock(...)
  SELECT ... FROM jsonb_array_elements(p_items);

  RETURN jsonb_build_object('success', true, 'reservations', v_out);
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

Key Design Decisions:

• Pessimistic locking (FOR UPDATE) for high-contention scenarios. At Lamiya's scale (~500 concurrent checkouts on hot items during flash sales), row-level locks are held for milliseconds, well within PostgreSQL's comfort zone.

• Dual-source availability calculation (physical stock vs. soft holds) ensures accurate inventory visibility.

• Idempotent cleanup on retries prevents duplicate reservations.

Phase 2: Order Creation with Automatic Compensation (create_order_rpc)

All critical steps; coupon validation, address handling, and stock reservation, happen inside one atomic transaction:

CREATE OR REPLACE FUNCTION create_order_rpc(
  p_user_id uuid,
  p_checkout_id uuid,
  p_order_address_id text DEFAULT NULL,
  p_payment_method text DEFAULT NULL,
  p_address jsonb DEFAULT NULL,
  p_save_address BOOLEAN DEFAULT false,
  p_coupon_code text DEFAULT NULL
) RETURNS jsonb AS $
DECLARE
  v_checkout record;
  v_stock_result jsonb;
  v_total_amount numeric := 0;
BEGIN
  -- Lock checkout record to prevent concurrent order creation
  SELECT id, user_id, status, created_at
    INTO v_checkout
    FROM checkout
    WHERE id = p_checkout_id AND user_id = p_user_id AND status = 'pending'
    FOR UPDATE;

  IF NOT FOUND THEN
    RETURN jsonb_build_object('success', false, 'error', 'Checkout not found');
  END IF;

  -- Expiry check
  IF v_checkout.created_at < NOW() - INTERVAL '30 minutes' THEN
    RETURN jsonb_build_object('success', false, 'error', 'Checkout expired');
  END IF;

  -- Aggregate totals from checkout items
  SELECT SUM(ci.product_price * ci.quantity) INTO v_total_amount
  FROM checkout_items ci
  WHERE ci.checkout_id = p_checkout_id;

  -- Reserve stock atomically
  v_stock_result := reserve_stock_rpc(
    p_checkout_session_id := p_checkout_id,
    p_items := (SELECT jsonb_agg(...) FROM checkout_items WHERE checkout_id = p_checkout_id),
    p_reservation_minutes := 30
  );

  IF NOT (v_stock_result->>'success')::boolean THEN
    RETURN jsonb_build_object('success', false, 'error', v_stock_result->>'error');
  END IF;

  -- Create order and items...
  INSERT INTO orders (...) VALUES (...);
  INSERT INTO order_items (...) SELECT ...;

  -- Mark checkout as processing
  UPDATE checkout SET status = 'processing' WHERE id = p_checkout_id;

  RETURN jsonb_build_object('success', true, 'order_id', v_order_id);

EXCEPTION WHEN OTHERS THEN
  -- Compensating action: release reserved stock on any failure
  PERFORM release_stock_rpc(p_checkout_session_id := p_checkout_id);
  RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

This exception handler implements the compensating transaction pattern: if anything fails after stock reservation, the reserved stock is automatically released. This prevents zombie inventory holds without requiring a separate orchestration service.

Phase 3: Idempotent Webhook Processing with State Machine

The payment webhook handler implements a rank-based state machine to gracefully handle duplicates, late arrivals, and out-of-order events:

CREATE OR REPLACE FUNCTION complete_order_from_webhook_rpc(
  p_order_id TEXT,
  p_status TEXT,
  p_webhook JSONB DEFAULT NULL
) RETURNS jsonb AS $
DECLARE
  v_payment record;
  v_current_rank INT;
  v_incoming_rank INT;
  v_effective_status TEXT;
BEGIN
  -- Lock payment record
  SELECT p.id, p.order_id, p.status INTO v_payment
  FROM payments p
  JOIN orders o ON o.id = p.order_id
  WHERE p.cf_order_id = p_order_id
  FOR UPDATE;

  -- Normalize gateway status
  v_effective_status := CASE upper(p_status)
    WHEN 'SUCCESS' THEN 'paid'
    WHEN 'FAILED' THEN 'failed'
    WHEN 'USER_DROPPED' THEN 'dropped'
    ELSE 'unpaid'
  END;

  -- Rank-based state machine (prevents regression)
  v_current_rank := CASE v_payment.status
    WHEN 'unpaid' THEN 1
    WHEN 'dropped' THEN 2
    WHEN 'failed' THEN 3
    WHEN 'paid' THEN 4
    ELSE 1
  END;

  v_incoming_rank := CASE v_effective_status
    WHEN 'unpaid' THEN 1
    WHEN 'dropped' THEN 2
    WHEN 'failed' THEN 3
    WHEN 'paid' THEN 4
    ELSE 1
  END;

  -- Only advance state, never regress
  IF v_incoming_rank < v_current_rank THEN
    v_effective_status := v_payment.status;
  END IF;

  UPDATE payments SET status = v_effective_status WHERE id = v_payment.id;

  -- If paid, consume stock
  IF v_effective_status = 'paid' THEN
    PERFORM complete_order_rpc(...);
  END IF;

  RETURN jsonb_build_object('success', true, 'status', v_effective_status);
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

States progress only forward: unpaid → dropped → failed → paid. Stale events are safely ignored, eliminating duplicate processing risks from payment gateway retries.

Phase 4: Final Inventory Consumption (complete_order_rpc)

Stock is deducted only after confirmed payment, with strict guards against negative inventory:

CREATE OR REPLACE FUNCTION complete_order_rpc(
  p_user_id UUID,
  p_order_id UUID,
  p_checkout_id UUID,
  p_payment_id UUID
) RETURNS JSONB AS $
DECLARE
  v_items_count INTEGER;
  v_updated_count INTEGER;
BEGIN
  -- Validate and lock order, checkout, and payment
  PERFORM 1 FROM orders WHERE id = p_order_id FOR UPDATE;
  PERFORM 1 FROM checkout WHERE id = p_checkout_id FOR UPDATE;
  PERFORM 1 FROM payments WHERE id = p_payment_id FOR UPDATE;

  -- Count items for atomicity check
  SELECT COUNT(*) INTO v_items_count FROM checkout_items WHERE checkout_id = p_checkout_id;

  -- Decrement stock only if reservations exist
  WITH items AS (
    SELECT product_id, color_id, size_id, quantity
    FROM checkout_items WHERE checkout_id = p_checkout_id
  ), updated AS (
    UPDATE product_color_sizes pcs
    SET stock_quantity = pcs.stock_quantity - i.quantity,
        updated_at = NOW()
    FROM items i
    WHERE pcs.product_id = i.product_id
      AND pcs.color_id = i.color_id
      AND pcs.size_id = i.size_id
      AND pcs.stock_quantity >= i.quantity  -- Guard: prevent negatives
    RETURNING 1
  )
  SELECT COUNT(*) INTO v_updated_count FROM updated;

  -- Atomicity check: all items must update or fail
  IF v_updated_count <> v_items_count THEN
    RAISE EXCEPTION 'Stock deduction failed for some items';
  END IF;

  -- Mark reservations as consumed
  UPDATE reserved_stock
  SET status = 'consumed', updated_at = NOW()
  WHERE checkout_session_id = p_checkout_id AND status = 'active';

  -- Finalize order and payment
  UPDATE orders SET status = 'pending', payment_status = 'paid' WHERE id = p_order_id;
  UPDATE payments SET status = 'paid' WHERE id = p_payment_id;
  UPDATE checkout SET status = 'paid' WHERE id = p_checkout_id;

  RETURN jsonb_build_object('success', true);
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

The pcs.stock_quantity >= i.quantity guard ensures that even if stock was manually adjusted between reservation and payment, the transaction fails cleanly rather than creating negative inventory.

The Result: Engineering Excellence in Production

Observed Improvements

Key Wins:

• Zero overselling during high-concurrency events

• Automatic dead-cart recovery via expired reservations

• Resilient webhook processing that survives network flakes and duplicates

• Full audit trail through the reserved_stock table

• Safe administrative operations stock adjustments don't conflict with active checkouts

Architectural Trade-offs

Gained:

• Absolute inventory accuracy at Lamiya's scale (~500 concurrent checkouts)

• Stateless horizontal scaling of the application layer

• Resilience to payment gateway failures without external orchestration

Costs and Limitations:

• Row-level lock contention: The FOR UPDATE locks on product_color_sizes create a serial bottleneck at the inventory row. At Lamiya's observed peak (500 concurrent users on hot flash-sale items), lock wait times remained under 50ms, acceptable for their use case. At significantly higher scales (10,000+ concurrent checkouts on single SKUs), this pattern would require:

  • Connection pool tuning (increase max connections)

  • Inventory sharding by product category

  • Or migration to an external reservation service (Redis, etc.)

• Storage overhead: The reserved_stock table grows with checkout volume. A background cleanup job purges consumed/released reservations older than 30 days.

• Complexity: ~400 lines of PL/pgSQL replace simple UPDATE statements. Team expertise in PostgreSQL transactions and exception handling is required for maintenance.

Key Takeaways

• Treat inventory as a distributed systems problem, not simple CRUD. The reservation pattern separates "intent to purchase" from physical stock movement.

• Pessimistic locking + compensating transactions provide strong consistency guarantees within a single database. This is simpler and faster than true distributed Sagas for bounded contexts that fit within one PostgreSQL instance.

• Database-native state machines (rank-based progression) eliminate webhook idempotency headaches without external infrastructure.

• Compensation at the database level (EXCEPTION WHEN OTHERS THEN...) ensures cleanup happens even if the application server crashes mid-transaction.

Conclusion

Lamiya Wedding Center's inventory system demonstrates that PostgreSQL is far more than a data store, it can serve as a robust platform for enforcing critical business invariants directly at the database level.

By building a multi-phase reservation protocol with database-native compensating transactions inside PostgreSQL RPC functions, the team achieved serializable isolation, automatic rollback on failure, and idempotent webhook processing, all without a single external coordinator or infrastructure dependency beyond PostgreSQL itself.

This pattern has proven effective in production at Lamiya's scale and is portable to any high-concurrency e-commerce system built on PostgreSQL. It eliminates an entire class of inventory bugs that commonly plague traditional application-layer approaches, while remaining honest about its scaling boundaries and operational requirements.