Inventory Management Services: Building Concurrency-Safe Stock APIs with Node.js and PostgreSQL

A checkout API can confirm two orders for the same product even when the warehouse has only one unit left. This happens when concurrent requests read the same stock value before either request updates it. The result is overselling, incorrect availability, and reconciliation work for warehouse teams.

Inventory Management Services need to solve this problem at the data layer, not just through frontend validation. A reliable implementation must coordinate stock reservations, order processing, warehouse movements, and retries across distributed systems.

This guide explains how to build a stock reservation API using Node.js, PostgreSQL, and transactional database operations. It focuses on preventing race conditions while keeping the design practical for ecommerce platforms, distribution businesses, and multi-warehouse applications. For broader implementation requirements, explore inventory and warehouse management services.

Context and Setup

An inventory service maintains the quantity of each SKU at a specific warehouse and determines how much stock can be promised to customers.

Consider a system with the following components:

1. API layer: Node.js with Express receives reservation requests.
2. Inventory database: PostgreSQL stores stock balances and reservation records.
3. Order service: Creates orders and coordinates payment workflows.
4. Event processing: A message broker distributes committed inventory changes to downstream services.
5. Monitoring: Metrics track failed reservations, database contention, and stock discrepancies.

For a basic implementation, Node.js, Express, PostgreSQL, and the pg database driver are sufficient.

Inventory accuracy is a business metric worth monitoring alongside API latency. APQC reports a median inventory accuracy of 95.0% across 8,660 companies in its Open Standards Benchmarking data. This figure provides operational context, although it does not measure API correctness or prove that a particular architecture will achieve the same result.

Designing Inventory Management Services for Concurrent Requests

The central design principle is to make stock reservation an atomic operation. Checking availability and decrementing stock must not become independent operations that competing requests can interleave.

Step 1: Define the Inventory Data Model

Separate physical stock, reserved stock, and available stock conceptually.

A simple inventory table can store the first two quantities:

CREATE TABLE inventory (
    sku TEXT NOT NULL,
    warehouse_id TEXT NOT NULL,
    on_hand INTEGER NOT NULL CHECK (on_hand >= 0),
    reserved INTEGER NOT NULL DEFAULT 0
        CHECK (reserved >= 0 AND reserved <= on_hand),
    PRIMARY KEY (sku, warehouse_id)
);

Available stock is calculated as on_hand - reserved.

The composite primary key prevents duplicate balance records for the same SKU and warehouse. The constraints also protect against negative quantities and reservations exceeding physical stock.

For production systems, maintain a separate reservation table with unique reservation identifiers, expiration timestamps, and lifecycle states. This supports auditing and controlled reservation expiry.

Step 2: Reserve Stock Within a Database Transaction

The API should reserve inventory only when sufficient unreserved stock remains.

Install the required packages:

npm install express pg

The following example assumes a configured PostgreSQL connection pool named pool. It uses a transaction and row-level locking to prevent concurrent requests from reserving the same available units.

async function reserveStock(sku, warehouseId, quantity) {
  // Why: reject invalid quantities before opening a transaction.
  if (!Number.isInteger(quantity) || quantity <= 0) {
    throw new Error("Quantity must be a positive integer");
  }

  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    // Why: serialize competing reservations for this inventory row.
    const result = await client.query(
      `SELECT on_hand, reserved
       FROM inventory
       WHERE sku = $1 AND warehouse_id = $2
       FOR UPDATE`,
      [sku, warehouseId]
    );

    if (result.rowCount === 0) {
      throw new Error("Inventory record not found");
    }

    const { on_hand, reserved } = result.rows[0];

    // Why: reject reservations that exceed currently available stock.
    if (on_hand - reserved < quantity) {
      throw new Error("Insufficient inventory");
    }

    await client.query(
      `UPDATE inventory
       SET reserved = reserved + $1
       WHERE sku = $2 AND warehouse_id = $3`,
      [quantity, sku, warehouseId]
    );

    // Why: expose the reservation only after the transaction commits.
    await client.query("COMMIT");
    return { sku, warehouseId, quantity, status: "reserved" };
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release();
  }
}

PostgreSQL documents that SELECT ... FOR UPDATE prevents competing transactions from modifying or locking the selected rows until the current transaction ends. This makes it useful for coordinating concurrent stock reservations.

The example illustrates the core transaction. A production API should also use typed errors, safe rollback handling, request validation, and a defined policy for retryable database failures.

Step 3: Add Idempotency and Reservation Expiry

Database locking prevents competing transactions from claiming the same available units, but it does not prevent duplicate reservations when a client retries a request after a network timeout.

Use an idempotency key for every reservation request.

1. Require a unique request identifier from the client.
2. Store the identifier with the reservation record and enforce uniqueness in PostgreSQL.
3. Return the existing reservation result when the same request is submitted again.
4. Reject reuse of the same key with a different SKU or quantity.
5. Expire abandoned reservations through a scheduled worker that releases stock transactionally.

An alternative is an atomic conditional update, such as UPDATE ... WHERE on_hand - reserved >= quantity. This can be efficient for simple stock decrements, but reservation records and idempotency still need separate handling.

Keep transactions short. Do not call payment gateways or external APIs while holding database locks. Commit the reservation first, then coordinate payment and order-state changes through an explicit workflow.

Real-World Application

Inventory implementations become more difficult when multiple warehouses, order channels, and enterprise systems share stock data.

At Oodles, inventory-related implementation work should be evaluated against concrete operational requirements, including stock synchronization, warehouse-level availability, ERP integration, and order-processing rules. The architecture above provides a practical baseline for addressing those requirements.

For a client-facing case study, the implementation should document the actual system, the concurrency or synchronization problem, the technologies deployed, and measured outcomes. Useful metrics include reservation failure rates, p95 API latency, duplicate-order incidents, and inventory reconciliation discrepancies.

No verified Oodles project measurements were provided for this article, so assigning a numerical improvement to a specific Oodles engagement would be misleading. Establish a baseline, run a representative load test, and report measured before-and-after results before publishing a performance claim. Learn more about Oodles.

Conclusion: Key Takeaways

• Protect inventory at the database layer: Frontend checks cannot prevent concurrent reservation conflicts.
• Use transactions and appropriate locks: Make stock validation and reservation updates one atomic operation.
• Design for retries: Idempotency keys prevent duplicate reservations caused by network failures.
• Separate reservation from fulfillment: Model reservation expiry, payment failure, shipment, and cancellation as explicit state transitions.
• Measure real workloads: Track p95 latency, lock waits, reservation failures, and inventory accuracy before tuning the architecture.

For implementation discussions, integration planning, or custom Inventory Management Services, contact our team.

Frequently Asked Questions

1. What are Inventory Management Services in software development?

Inventory Management Services are software components that track stock quantities, warehouse locations, reservations, receipts, and stock movements. They expose inventory data to systems such as ecommerce platforms, ERP applications, order management tools, and warehouse operations software.

2. How do you prevent overselling in a Node.js application?

Use database transactions and atomic stock updates. In PostgreSQL, SELECT ... FOR UPDATE can serialize competing modifications to the same inventory row. Validate available stock inside the transaction, commit the reservation, and use idempotency keys to handle repeated requests safely.

3. Should inventory availability be stored in Redis or PostgreSQL?

PostgreSQL is a strong choice for authoritative inventory balances because it supports transactions, constraints, and row-level locking. Redis can accelerate availability reads or coordinate supporting workflows, but a cache should not independently authorize reservations without a consistency strategy.

4. How should inventory be synchronized across multiple warehouses?

Maintain stock balances by SKU and warehouse, and record transfers as explicit inventory movements. Use transactional updates for local changes and reliable event delivery for downstream synchronization. Define ownership, event ordering, retry handling, and reconciliation rules to detect discrepancies.

5. How do you measure inventory API performance?

Track request throughput, p50 and p95 latency, database lock waits, transaction failures, reservation conflicts, and timeout rates. Pair these technical metrics with stockout incidents and physical-to-system inventory variance. Load tests should reproduce realistic SKU contention and concurrent order traffic.