Warehouse Management System (WMS)

A warehouse/inventory management mock application demonstrating concurrency patterns, optimistic locking, and atomic multi-table operations. This mock exposed friction points that drove the Phase 6 API improvements, now fully implemented.

Domain Overview

The WMS implements a realistic warehouse inventory system with:

• Warehouses - Multiple physical warehouse locations
• Products - SKU/item definitions with minimum stock levels
• Locations - Bin/shelf locations (aisle-rack-shelf-bin)
• Stock - Current quantities with version columns for optimistic locking
• Movements - Audit trail for all stock changes (receive, transfer, pick, adjust)
• Reservations - Time-expiring holds on stock for pending orders

Schema

-- Core tables
warehouses (id, code, name, address, is_active, created_at)
products (id, sku, name, description, unit_of_measure, min_stock_level, deleted_at)
locations (id, warehouse_id, code, aisle, rack, shelf, bin, is_active)

-- Inventory tracking
stock (id, product_id, location_id, quantity, reserved_quantity,
       version,  -- Optimistic locking!
       updated_at)

-- Audit trail
movements (id, product_id, from_location_id, to_location_id, quantity,
           movement_type, reference, performed_by, created_at)

-- Reservations with expiry
reservations (id, product_id, location_id, quantity, order_reference,
              reserved_by, expires_at, created_at)

Friction Points Exposed

The WMS application exposed critical API gaps around concurrent access and atomic operations:

from l_retries := 0 until Result or l_retries > 3 loop
    l_stock := find_stock (product_id, location_id)
    if attached l_stock as s then
        l_version := s.version
        database.begin_transaction
        database.execute_with_args (
            "UPDATE stock SET quantity = quantity + ?, version = version + 1 WHERE id = ? AND version = ?",
            <>)
        if database.changes_count = 0 then
            database.rollback
            l_retries := l_retries + 1
        else
            database.commit
            Result := True
        end
    end
end

Result := db.update_versioned ("stock", stock_id, current_version,
    agent (b: SIMPLE_SQL_UPDATE_BUILDER) do
        b.set ("quantity", new_quantity)
    end)

db.atomic (agent receive_stock_operation (product, location, qty, reference))

success := db.decrement_if ("stock", "quantity", 10, "id = ? AND quantity >= ?", <>)

db.upsert ("stock",
    <>,
    <<"product_id", "location_id">>)  -- conflict columns

db.cleanup_expired ("reservations", "expires_at", agent release_reservation_callback)

Source Code

• WMS_APP - Main application facade
• WMS_WAREHOUSE - Warehouse entity
• WMS_PRODUCT - Product/SKU entity
• WMS_LOCATION - Bin/shelf location
• WMS_STOCK - Stock level with version column
• WMS_MOVEMENT - Movement audit record
• WMS_RESERVATION - Time-expiring reservation

Test Coverage

• TEST_WMS_APP - 17 core tests (CRUD, reservations, movements)
• TEST_WMS_STRESS - 8 stress tests (optimistic locking, bulk operations)

Key Patterns Demonstrated

Optimistic Locking with Version Column

-- WMS_STOCK has a version column
-- Every update increments version and checks old version
UPDATE stock
SET quantity = quantity + ?, version = version + 1, updated_at = datetime('now')
WHERE id = ? AND version = ?

-- If changes_count = 0, someone else modified the row - retry!

Atomic Stock + Movement in Transaction

database.begin_transaction
-- Update stock
database.execute_with_args (update_sql, stock_args)
-- Record movement
database.execute_with_args (movement_sql, movement_args)
database.commit

Low Stock Alerts with Aggregation

SELECT p.*, COALESCE(SUM(s.quantity), 0) as total_qty
FROM products p
LEFT JOIN stock s ON s.product_id = p.id
WHERE p.deleted_at IS NULL AND p.min_stock_level > 0
GROUP BY p.id
HAVING total_qty < p.min_stock_level
ORDER BY (p.min_stock_level - total_qty) DESC

Phase 6 Implementation (Complete)

The friction points identified by WMS drove these Phase 6 API additions, now fully implemented:

Before vs After: receive_stock

The Phase 6 APIs dramatically reduce boilerplate:

-- BEFORE: 45 lines with manual retry loops
from l_retries := 0 until Result or l_retries > 3 loop
    l_stock := find_stock (product_id, location_id)
    database.begin_transaction
    -- ... 35 more lines of version checking and rollback handling
end

-- AFTER: Clean, readable, safe
database.atomic (agent receive_stock_operation (product_id, location_id, qty, ref, user))

receive_stock_operation (...)
    do
        if database.increment_if ("stock", "quantity", qty, "id = ?", <<stock_id>>) then
            -- Record movement
        end
    end

Before vs After: transfer_stock

-- BEFORE: 55 lines with nested loops and manual version checks

-- AFTER: Uses decrement_if for atomic "check and decrement"
if database.decrement_if ("stock", "quantity", qty,
        "id = ? AND quantity >= ?", <<from_id, qty>>) then
    -- Increment destination, record movement
end