Multi-Warehouse Inventory Sync Architecture for Retail in Lebanon and MENA

Retail and distribution companies managing inventory across multiple warehouses or store locations eventually hit the same architectural problem: the system works correctly at two locations and becomes unreliable at ten. The failure modes are predictable, and they come from underestimating the concurrency and consistency requirements that multi-location inventory introduces.

The fundamental data model problem

The obvious data model for multi-location inventory is a table with one row per product per location:

CREATE TABLE stock (
    product_id   UUID,
    warehouse_id UUID,
    quantity     INTEGER,
    PRIMARY KEY (product_id, warehouse_id)
);

This model is adequate for low-traffic reporting but fails for operational systems where multiple users are reserving stock simultaneously from different locations. Two sales agents in different Beirut branches can both read available_quantity = 5, both submit orders for 5, and both succeed, producing an oversold state that the fulfillment team discovers later.

The correct model: separate state from movements

The production model separates the current inventory state (a materialized level) from the historical log of movements. The current level table is updated atomically with each movement. The movements table is append-only and used for auditing, correction, and consistency verification.

CREATE TABLE inventory_levels (
    tenant_id    UUID NOT NULL,
    warehouse_id UUID NOT NULL,
    product_id   UUID NOT NULL,
    quantity     INTEGER NOT NULL DEFAULT 0,
    reserved     INTEGER NOT NULL DEFAULT 0,
    updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (tenant_id, warehouse_id, product_id)
);

CREATE TABLE inventory_movements (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id     UUID NOT NULL,
    warehouse_id  UUID NOT NULL,
    product_id    UUID NOT NULL,
    quantity_delta INTEGER NOT NULL,
    movement_type TEXT NOT NULL,
    reference_id  UUID,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The available quantity for any product at any location is always quantity - reserved. The reserved column holds stock committed to open orders but not yet fulfilled. This prevents overselling without requiring a SELECT FOR UPDATE on every read.

Atomic stock reservation in Go

The reservation operation must fail rather than allow overselling, must be atomic, and must not lock the row during unrelated reads. The pattern that satisfies all three constraints:

const reserveStockQuery = `
UPDATE inventory_levels
SET reserved = reserved + $1,
    updated_at = NOW()
WHERE tenant_id = $2
  AND warehouse_id = $3
  AND product_id = $4
  AND (quantity - reserved) >= $1
RETURNING id
`

The WHERE clause performs the availability check and the update atomically. If the available quantity is less than the requested amount, the UPDATE matches zero rows and the application returns an out-of-stock error without any explicit locking. The movement record is written in the same database transaction.

func (s *InventoryService) ReserveStock(ctx context.Context, p ReserveParams) error {
    return s.db.WithTx(ctx, func(tx pgx.Tx) error {
        tag, err := tx.Exec(ctx, reserveStockQuery,
            p.Quantity, p.TenantID, p.WarehouseID, p.ProductID)
        if err != nil {
            return fmt.Errorf("reserve stock: %w", err)
        }
        if tag.RowsAffected() == 0 {
            return ErrInsufficientStock
        }
        _, err = tx.Exec(ctx, insertMovementQuery,
            p.TenantID, p.WarehouseID, p.ProductID,
            -p.Quantity, "reservation", p.ReferenceID)
        return err
    })
}

Warehouse transfers and consistency

Transferring stock between warehouses is a two-sided operation: debit from the source and credit to the destination. If these two updates happen in separate transactions, a failure between them leaves inventory in an inconsistent state: stock has left the source but not arrived at the destination.

For same-database warehouse transfers, wrap both updates in one transaction:

func (s *InventoryService) TransferStock(ctx context.Context, p TransferParams) error {
    return s.db.WithTx(ctx, func(tx pgx.Tx) error {
        // Debit from source
        tag, err := tx.Exec(ctx, debitSourceQuery,
            p.Quantity, p.TenantID, p.SourceWarehouseID, p.ProductID)
        if err != nil {
            return err
        }
        if tag.RowsAffected() == 0 {
            return ErrInsufficientStock
        }
        // Credit to destination
        _, err = tx.Exec(ctx, creditDestinationQuery,
            p.Quantity, p.TenantID, p.DestWarehouseID, p.ProductID)
        return err
    })
}

For distributed systems where warehouses are on separate database instances, the outbox pattern is the correct approach. The transfer event is written to an outbox table in the source database transaction. A background processor reads the outbox and applies the credit to the destination, with retry logic for failures.

Performance at scale in multi-tenant systems

For SaaS platforms serving multiple retail companies in Lebanon and the Gulf, the inventory model needs tenant isolation at the index level. All queries against inventory_levels must filter by tenant_id first:

CREATE INDEX inventory_levels_tenant ON inventory_levels
    (tenant_id, warehouse_id, product_id);

CREATE INDEX inventory_movements_tenant ON inventory_movements
    (tenant_id, product_id, created_at DESC);

Aggregate queries (total stock across all warehouses for a product) should use the inventory_levels table, never recompute from the movements log. For high-frequency reporting, materialize the aggregates into a summary table updated by a trigger on inventory_levels changes.

Handling connectivity loss in Lebanon

Inventory systems deployed in Lebanon need offline operation built into the design from the start. A branch that loses internet access needs to continue receiving stock, processing sales, and recording adjustments. The sync happens when connectivity restores.

The architecture for this: each branch runs a local inventory database with a queue of operations to sync. When connectivity is available, the branch sends its operation log to the central system, which applies the operations and returns the updated state.

Conflict resolution requires defined rules before implementation: if two branches both sold the last unit while disconnected, one transaction must be reversed and the customer contacted. The systems that handle this correctly define the conflict resolution policy upfront and encode it in the sync logic. The systems that do not define it upfront make ad-hoc decisions in production during the first connectivity restoration after a split.

Reporting and visibility across locations

Multi-warehouse inventory creates a reporting requirement that single-location systems do not have: visibility into total stock versus in-transit stock versus reserved stock across all locations simultaneously.

A view that aggregates this without recomputing from movements:

CREATE VIEW inventory_summary AS
SELECT
    tenant_id,
    product_id,
    SUM(quantity)          AS total_quantity,
    SUM(reserved)          AS total_reserved,
    SUM(quantity - reserved) AS total_available,
    COUNT(DISTINCT warehouse_id) AS warehouse_count
FROM inventory_levels
GROUP BY tenant_id, product_id;

This view runs in milliseconds because it reads from the materialized levels table, not from the movements log. Operational dashboards for retail companies in Lebanon and the Gulf use this pattern to show real-time stock visibility across all locations without query timeouts.

Key lessons from production

Multi-warehouse inventory correctness comes from the data model, not from application logic. Get the model right: materialized levels, separate movements log, atomic reservation with row-count validation, and in-transaction transfers for same-database scenarios. Connectivity resilience must be planned upfront for Lebanon deployments. Tenant isolation in indexes separates a system that scales from one that degrades as tenant count grows.

The teams that skip the offline-first design for Lebanese deployments fix it later under pressure, at higher cost, with worse outcomes. It is always cheaper to design for the constraint from the start.

Ready to build this?

Voxire designs and builds inventory management systems and multi-location operational platforms for companies in Lebanon and across the MENA region.

https://voxire.com/get-a-quote/