How to Design ERP Development Services for Concurrent Inventory and Order Workflows

An ERP can produce incorrect inventory even when every individual API appears to work correctly. The failure usually happens when two users, integrations, or background workers attempt to reserve the same stock at nearly the same time. A typical example is an order service reading available_quantity = 10, processing an order for 7 units, while another request reads the same value before either transaction commits.

This is an architecture problem, not simply an API bug. Good ERP Development Services must define transaction boundaries, concurrency rules, idempotency, and observability before scaling the application.

For teams building a custom ERP, this architecture pattern can be implemented with Node.js, PostgreSQL, Docker, and AWS. Organizations evaluating custom ERP development services can use the same principles when designing inventory, sales, procurement, or finance workflows.

Context and Setup

The scenario is a modular ERP deployed as containerized services:

Web / Mobile Clients
        |
    API Gateway
        |
   Node.js Services
        |
  PostgreSQL Database
        |
   Event / Job Workers
        |
 AWS SQS / CloudWatch

The important boundary is PostgreSQL. Inventory availability, order creation, and reservation should not depend on application-level timing alone.

PostgreSQL's default READ COMMITTED isolation level provides statement-level visibility, but concurrent updates still require deliberate transaction design. PostgreSQL documents SELECT ... FOR UPDATE as a mechanism for locking selected rows against concurrent updates.

There is also a useful AWS performance lesson: AWS recommends monitoring latency outliers such as the 99.9th and 99.99th percentiles instead of relying only on average latency. One slow request in 1,000 or 10,000 can still affect users.

That matters for ERP systems because order creation may trigger inventory writes, payment calls, tax calculations, and external integrations within one business workflow.

Designing ERP Development Services Around Transaction Safety

Step 1 - Define the Transaction Boundary

The first step is deciding which operations must succeed or fail together.

For inventory reservation, the minimum transaction should normally include:

1. Read the inventory row.
2. Lock the row.
3. Validate available quantity.
4. Reduce or reserve stock.
5. Create the reservation record.
6. Commit the transaction.

Do not place unrelated network calls inside this transaction. A payment provider or external shipping API can take considerably longer than a database update and unnecessarily extend lock duration.

A useful model is:

Order Request
     |
     v
Validate Request
     |
     v
Begin DB Transaction
     |
     +--> Lock Inventory Row
     |
     +--> Validate Quantity
     |
     +--> Create Reservation
     |
     +--> Update Inventory
     |
     v
Commit
     |
     v
Publish Event

The event can then trigger downstream work such as notifications, fulfillment, analytics, or synchronization with another ERP.

Step 2 - Lock Inventory at the Database Layer

The second step is protecting the shared inventory record inside the transaction.

A simplified Node.js example using PostgreSQL looks like this:

const client = await pool.connect();

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

  // Why: locks the inventory row so concurrent orders cannot modify it simultaneously.
  const result = await client.query(
    `SELECT available_quantity
     FROM inventory
     WHERE product_id = $1
     FOR UPDATE`,
    [productId]
  );

  const available = result.rows[0]?.available_quantity ?? 0;

  if (available < quantity) {
    throw new Error("Insufficient inventory");
  }

  // Why: update occurs while the row lock is still held.
  await client.query(
    `UPDATE inventory
     SET available_quantity = available_quantity - $1
     WHERE product_id = $2`,
    [quantity, productId]
  );

  // Why: reservation and stock mutation become part of one atomic operation.
  await client.query(
    `INSERT INTO inventory_reservations(product_id, quantity)
     VALUES ($1, $2)`,
    [productId, quantity]
  );

  await client.query("COMMIT");
} catch (error) {
  await client.query("ROLLBACK");
  throw error;
} finally {
  client.release();
}

PostgreSQL specifies that FOR UPDATE locks selected rows against conflicting updates until the transaction ends.

The important detail is not the SQL syntax. It is the sequence. Reading inventory first and updating it later without a transaction creates a race condition.

Step 3 - Add Idempotency and Observability

Database locking solves concurrent modification, but it does not solve duplicate HTTP requests.

For example, a mobile client may retry an order after a timeout even though the first request succeeded. Without an idempotency key, the ERP may create two orders.

A practical implementation stores an idempotency key with the business transaction:

POST /orders
Idempotency-Key: 7f82...

        |
        v
Check existing key
        |
   +----+----+
   |         |
Found      New
   |         |
Return      Process
existing    transaction
order          |
               v
          Store key + result

For production monitoring, capture:

• Order API P50, P95, and P99 latency
• Database lock wait time
• Transaction rollback rate
• Duplicate request rate
• Inventory reservation failures
• Queue depth
• External API latency

AWS specifically recommends recording database transactions, slow queries, I/O latency, HTTP throughput, and service latency as workload performance metrics.

The alternative is to monitor only CPU and memory. That can hide the actual ERP bottleneck because a database lock or slow dependency may exist while application CPU remains normal.

Real-World Application

In one of our ERP implementations at Oodles, Alpex, an international trading company focused on photovoltaic modules and solar systems, required a customized ERP system covering production planning, inventory management, and quality control. The implementation used Odoo 9 and included barcode-based recording of material and IV curve data for each module. Oodles reports that the solution incorporated quality controls resulting in 100% qualified modules.

The architectural lesson is that ERP development is not only about adding modules. Production, inventory, quality, and traceability create relationships between business transactions. Those relationships need explicit data ownership and validation rules.

For broader examples of ERP architecture and implementation work, Oodles covers custom ERP, Odoo, ERPNext, planning, inventory, CRM, and ERP integration work.

Key Takeaways

• ERP Development Services should start with transaction boundaries, not UI modules.
• Use PostgreSQL row-level locking when multiple workflows can modify the same inventory record concurrently.
• Use idempotency keys to protect order creation from client and integration retries.
• Keep external API calls outside short database transactions whenever possible.
• Monitor P95/P99 latency, lock waits, rollbacks, and queue depth instead of relying only on average response time.

Discuss Your ERP Architecture

If you are designing inventory reservations, order orchestration, multi-tenant ERP modules, or integrations and want to review the architecture with an engineering team, share your scenario in the comments.

For a technical discussion about, ERP Development Services can be used to start a conversation with Oodles.

FAQ

1. What are ERP Development Services?

ERP Development Services cover the architecture, development, customization, integration, deployment, and maintenance of enterprise resource planning software. They can include inventory, sales, finance, procurement, HR, manufacturing, workflow automation, reporting, and integrations with external business systems.

2. Why does concurrency matter in custom ERP systems?

Concurrency matters because multiple users, APIs, and background workers can modify the same business record simultaneously. Without transaction control, two orders may reserve the same inventory. Database transactions, row locking, constraints, and idempotency help preserve business data integrity.

3. Should an ERP use microservices from the beginning?

Not necessarily. A modular monolith can be a better starting point when business domains are tightly coupled and the team is small. Services can be separated later when independent scaling, deployment, ownership, or integration boundaries justify the additional operational complexity.

4. How should ERP performance be measured?

ERP performance should be measured using business operations and technical metrics together. Track P50, P95, and P99 API latency, database query duration, lock waits, throughput, error rates, queue depth, and important workflow completion times under realistic concurrent load.

5. How do ERP Development Services support integrations?

ERP Development Services can expose REST or event-driven APIs for payment systems, marketplaces, accounting platforms, logistics providers, CRM systems, and internal applications. Idempotency, retries, authentication, schema validation, and audit logging should be designed into integration workflows rather than added after failures occur.