How to Build Inventory Management Services with Odoo and PostgreSQL

A stock reservation can fail in a surprisingly simple way: two checkout requests read available_qty = 1, both approve the order, and the warehouse later discovers that only one unit exists. This happens when inventory writes are treated like ordinary CRUD operations instead of concurrency-sensitive state transitions. Inventory Management Services need transactional stock updates, idempotent APIs, warehouse-aware data models, and an audit trail for every movement. In this architecture deep dive, we will design that core around Odoo, Python, and PostgreSQL, with patterns applicable to custom ERP and WMS implementations. If you are evaluating inventory and warehouse management solutions, the key architectural question is not only how stock is displayed, but how stock remains correct under concurrent writes.

Context and Setup

The system manages products, warehouses, bins, stock movements, reservations, purchase receipts, sales orders, returns, and adjustments. A typical request path looks like:

Client → API/ERP Layer → Inventory Service → PostgreSQL → Event/Integration Layer

The important design decision is to treat stock movement as the source of truth, rather than allowing multiple application modules to modify a quantity independently.

For example, an SKU can have:

• on_hand
• reserved
• available
• incoming
• warehouse_id
• version

The application can derive:

available = on_hand - reserved

This prevents sales, purchasing, and warehouse modules from maintaining competing definitions of stock.

Database performance also depends heavily on connection management. AWS documents an Aurora PostgreSQL test where reusing connections processed 9,042 transactions in 60 seconds versus 495 when connections were repeatedly established, an approximately 18x difference in that specific test environment.

That is why an inventory platform should use connection pooling rather than creating a database connection for every API request.

Designing Inventory Management Services for Concurrent Stock Updates

Step 1: Model stock around warehouse and SKU boundaries

The first step is separating product identity from physical stock.

A useful relational model is:

CREATE TABLE inventory_balance (
    sku_id BIGINT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    on_hand INTEGER NOT NULL DEFAULT 0,
    reserved INTEGER NOT NULL DEFAULT 0,
    version INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (sku_id, warehouse_id) -- Prevents duplicate warehouse balances
);

The composite key matters because SKU-100 may have 50 units in Warehouse A and 20 in Warehouse B. A global quantity cannot correctly represent allocation decisions.

Keep a separate movement ledger for receipts, picks, transfers, returns, and adjustments. The balance becomes the operational read model, while the movement ledger provides traceability.

Step 2: Make reservation atomic

The most dangerous operation is usually reservation. A read followed by a separate update creates a race condition.

PostgreSQL supports SELECT ... FOR UPDATE, which locks selected rows against concurrent updates until the transaction ends.

A Python implementation can therefore make the reservation decision inside one transaction:

def reserve_stock(conn, sku_id, warehouse_id, quantity):
    with conn.transaction():
        with conn.cursor() as cur:
            cur.execute("""
                SELECT on_hand, reserved
                FROM inventory_balance
                WHERE sku_id = %s AND warehouse_id = %s
                FOR UPDATE
            """, (sku_id, warehouse_id))  # Why: serializes competing reservations

            row = cur.fetchone()

            if not row:
                raise ValueError("Inventory record not found")

            on_hand, reserved = row

            if on_hand - reserved < quantity:
                raise ValueError("Insufficient available stock")

            cur.execute("""
                UPDATE inventory_balance
                SET reserved = reserved + %s,
                    version = version + 1
                WHERE sku_id = %s AND warehouse_id = %s
            """, (quantity, sku_id, warehouse_id))  # Why: update occurs under the same lock

The important part is not the Python syntax. The stock check and stock mutation happen under the same database transaction.

For workloads using DynamoDB instead of PostgreSQL, the equivalent pattern is a conditional write. AWS specifically recommends conditional writes for concurrent updates because the condition is evaluated as part of the write operation.

Step 3: Separate synchronous consistency from asynchronous integration

Not every inventory operation belongs in the same transaction.

The reservation itself should remain synchronous because the caller needs an authoritative answer: reserved or rejected.

Notifications, analytics, search indexing, ERP synchronization, and external marketplace updates can be asynchronous.

A practical sequence is:

1. Lock the inventory balance.
2. Validate available quantity.
3. Update the reservation.
4. Insert an inventory movement.
5. Commit the transaction.
6. Publish an event using an outbox pattern.
7. Process external integrations asynchronously.
8. Retry failed consumers using an idempotency key.

This approach is preferable to placing external API calls inside the database transaction. External calls can be slow or unavailable, unnecessarily extending lock duration.

For queue-like workloads, PostgreSQL also provides SKIP LOCKED, which can allow multiple consumers to avoid waiting on already-locked rows. PostgreSQL notes that this is appropriate for queue-style processing rather than general-purpose consistent reads.

Real-World Application

In one of our inventory-focused projects at Oodles, My Mandi required an inventory management ERP and mobile marketplace supporting its B2B2C operating model. Oodles implemented the inventory ERP with Odoo, Python, Flutter, and DevOps components, connecting inventory workflows with the marketplace experience. The project supported a membership base of more than 200 on one platform and enabled users to book orders while providing improved reporting visibility.

Another relevant Oodles implementation, Ecom Express, involved Odoo customization across logistics, supply chain, inventory and warehouse operations, storage management, and order fulfillment. Oodles reports a 35% improvement in operational efficiency and a 20% reduction in delivery times for the implementation.

These projects illustrate why Inventory Management Services often need to extend beyond a stock table. The architecture has to connect inventory state with order processing, warehouse execution, procurement, logistics, and reporting.

For more examples of ERP and engineering implementations, visit Oodles.

Performance Considerations

Performance should be measured against the actual workload instead of an arbitrary requests-per-second target.

AWS has demonstrated DynamoDB workloads exceeding 1.1 million requests per second in a benchmark involving distributed reads and writes. That result is a capacity demonstration, not a promise for every inventory application, because schema, item size, access patterns, hot keys, and infrastructure configuration materially affect throughput.

For an inventory service, measure:

• Reservation latency at p50, p95, and p99
• Database lock wait time
• Transaction rollback rate
• Stock conflict rate
• Queue processing latency
• API throughput by warehouse
• Integration retry volume

These measurements expose the real bottleneck. A service handling 500 requests per second with correct transactional behavior can be more useful than one handling thousands of requests while occasionally overselling stock.

Conclusion / Key Takeaways

• Model inventory by SKU and warehouse, not as one global quantity.
• Keep reservation validation and mutation inside the same transaction.
• Use PostgreSQL row locks or DynamoDB conditional writes for concurrent stock changes.
• Keep external integrations outside the critical transaction path and use an outbox or event-driven workflow.
• Benchmark connection pooling, lock contention, reservation latency, and queue throughput under realistic concurrency.

Building or modernizing an inventory platform? Share your architecture, concurrency requirements, warehouse model, or current bottleneck in the comments. We can discuss database locking, event-driven inventory, ERP integration, and warehouse workflows from an implementation perspective.

For a technical discussion with Oodles, contact us about Inventory Management Services.

FAQ

1. What are Inventory Management Services?

Inventory Management Services are software engineering and implementation capabilities for tracking, reserving, moving, replenishing, and auditing stock across products and warehouse locations. They can include ERP/WMS configuration, custom APIs, barcode workflows, integrations, reporting, forecasting, and inventory synchronization.

2. How do you prevent overselling inventory?

Prevent overselling by performing the availability check and reservation update atomically. PostgreSQL applications can use row-level FOR UPDATE locks, while DynamoDB applications can use conditional writes. Both approaches ensure concurrent requests cannot independently approve the same remaining inventory.

3. Should inventory quantity be stored or calculated?

Store an operational balance for fast reads, but maintain an immutable movement ledger for traceability. The balance can represent current on_hand and reserved quantities, while receipts, picks, transfers, returns, and adjustments provide the audit history required to reconstruct inventory changes.

4. When should inventory processing become asynchronous?

Make operations asynchronous when they do not determine the immediate stock decision. Analytics, notifications, search indexing, marketplace synchronization, and reporting are good candidates. Reservation and allocation should normally remain synchronous because the caller requires an authoritative inventory decision before confirming the order.

5. Can Inventory Management Services support multiple warehouses?

Yes. A multi-warehouse design should scope inventory balances, reservations, movements, and allocation rules by warehouse or fulfillment location. This enables the system to answer not only whether an SKU exists, but where it exists, how much is available, and which location should fulfill a particular order.