Recipes · 3 of 6

Inventory — stock check, restock, reservations

Schema

products:     {sku, name, stock, reserved, threshold, supplier_id}
restock_log:  {sku, qty, supplier_id, ts}
reservations: {sku, user_id, qty, status, expires_at}

restock

proc restock(sku, qty, supplier_id) {
    if qty <= 0 { abort "qty must be positive" }
    let p = find_one("products", {sku: sku})
    if p == null { abort "product not found" }
    update("products", {sku: sku}, {$inc: {stock: qty}, $set: {needs_restock: false}})
    insert("restock_log", {sku: sku, qty: qty, supplier_id: supplier_id})
    return {ok: true, new_stock: p.stock + qty}
}

reserve

proc reserve(sku, user_id, qty) {
    let p = find_one("products", {sku: sku})
    if p == null                       { abort "not found" }
    if p.stock - p.reserved < qty       { abort "insufficient available stock" }
    update("products", {sku: sku}, {$inc: {reserved: qty}})
    let id = insert("reservations", {
        sku: sku, user_id: user_id, qty: qty, status: "active"
    })
    return {ok: true, reservation_id: id}
}

commit_reservation

proc commit_reservation(reservation_id) {
    let r = find_one("reservations", {_id: reservation_id})
    if r == null                      { abort "reservation not found" }
    if r.status != "active"           { abort "reservation not active" }

    update("products", {sku: r.sku}, {$inc: {stock: -r.qty, reserved: -r.qty, sold: r.qty}})
    update("reservations", {_id: reservation_id}, {$set: {status: "committed"}})
    return {ok: true}
}

release_reservation

proc release_reservation(reservation_id) {
    let r = find_one("reservations", {_id: reservation_id})
    if r == null                      { abort "reservation not found" }
    if r.status != "active"           { return {ok: true, was_active: false} }

    update("products", {sku: r.sku}, {$inc: {reserved: -r.qty}})
    update("reservations", {_id: reservation_id}, {$set: {status: "released"}})
    return {ok: true}
}

flag_low_stock

proc flag_low_stock() {
    let lows = find("products", {needs_restock: {$ne: true}})
    let flagged = 0
    for p in lows {
        if p.stock - p.reserved < p.threshold {
            update("products", {sku: p.sku}, {$set: {needs_restock: true}})
            insert("reorder_queue", {sku: p.sku, current: p.stock, target: p.threshold * 3})
            flagged = flagged + 1
        }
    }
    return {flagged: flagged}
}

inventory_report

proc inventory_report() {
    return {
        total_skus:    count("products"),
        out_of_stock:  count("products", {stock: 0}),
        low_stock:     count("products", {needs_restock: true}),
        active_reservations: count("reservations", {status: "active"}),
        by_supplier: aggregate("products", [
            {$group: {_id: "$supplier_id", n: {$sum: 1}, total_stock: {$sum: "$stock"}}}
        ])
    }
}