execute(input) if input.quantity <= 0 fail InvalidInput "Quantity must be positive" end database = service "database_inventory" // Lock the base row without optional reference joins. locked = database.first({ from: "Inventory (id)", where: { id: input.inventoryId, deleted: false }, locking: { type: "forUpdate" } }) if locked == null fail NotFound "Inventory not found" end existing = database.first({ from: "Reservation (id, inventory (id), quantity)", where: { requestReference: input.requestReference, deleted: false } }) if existing != null if existing.inventory.id != input.inventoryId or existing.quantity != input.quantity fail Conflict "Request reference was already used for different work" end return { reservationId: existing.id, quantity: existing.quantity } end inventory = database.first({ from: "Inventory (id, available)", where: { id: input.inventoryId, deleted: false } }) if inventory.available < input.quantity fail Conflict "Insufficient inventory" end // The balance change and reservation are one database transaction. inventory.available = inventory.available - input.quantity inventory.save() reservation = database.create("Reservation") reservation.inventory = { id: inventory.id } reservation.requestReference = input.requestReference reservation.quantity = input.quantity reservation.save() return { reservationId: reservation.id, quantity: reservation.quantity } end