build.allanninal.dev

Part 3 of 7 · Inventory reorder bot series ~5 min read

How the bot spots a low item

Once a day, early in the morning, an EventBridge Scheduler rule fires the checker Lambda. The Lambda reads the stock list, looks at one row at a time, works out how low the item is allowed to get before you have to order, and decides whether to do nothing or to draft a reorder — and if so, how urgent. The whole decision is plain Python. No model. No guessing. Every number lives in the stock sheet, where a staffer can edit it without a deploy.

Key takeaways

  • The bot runs once a day via EventBridge Scheduler, early in the morning, local time.
  • Reorder point per item = daily sales rate × supplier lead-time days + safety buffer. All from the sheet.
  • Four moves per item, every check: stocked, watch, reorder, urgent reorder.
  • DynamoDB tracks what’s already on-order so the bot doesn’t draft the same item twice.
  • The bot itself never calls a model. The decision is entirely deterministic.

The decision flow, per item

Decision flow per item on every daily check A vertical decision flow diagram. At the top, an input box "Item from stock sheet" with the row's name, SKU, on-hand count, supplier, sales rate, lead time, and DynamoDB state. Below that, a step "Compute reorder point" — daily sales rate times supplier lead-time days, plus the safety buffer. Below that, a check "Already on-order?" — if yes, route to "Stocked" (do nothing this check, an order is already in flight). If no, continue. The next step "Compare on-hand to reorder point" — works out how the current count sits against the point and a lower urgent line. The next step "Which band is the count in?" — if on-hand is comfortably above the point, route to "Stocked". If on-hand is just above the point (within the watch margin from the rules doc), route to "Watch" — note it for the digest, no order yet. If on-hand is at or below the point but above the urgent line, route to "Reorder" — draft a purchase order for the owner. If on-hand is at or below the urgent line (so low you may run out before the next delivery), route to "Urgent reorder" — draft the PO and flag it urgent. Each terminal box — Stocked, Watch, Reorder, Urgent reorder — emits an event to EventBridge with the move and the item context. A note at the bottom: the stock sheet holds every number; the bot's code only enforces them — change a buffer in the sheet and tomorrow's check uses the new value. Item from stock sheet name · SKU · on-hand · supplier Step 1 Compute reorder point rate × lead time + buffer Step 2 Already on-order? read DDB ir-orders table Step 3 Compare on-hand to point e.g. on-hand 58 vs point 60 Step 4 Which band is it in? above point → stocked below urgent → urgent Step 5 Within the watch margin? read margin from rules doc Stocked do nothing Watch getting close, no order Reorder at or below point Urgent reorder below urgent line if yes above urgent close at point The stock sheet holds every number — change a buffer and tomorrow’s check uses the new value.
Fig 3. The bot’s decision tree, per item, per daily check. Five steps decide which of four moves applies. The stock sheet holds every number; the bot only enforces them.

The reorder point: rate × lead time + buffer, all from the sheet

The reorder point is the answer to one plain question: how low can the on-hand count get before I have to order, or I’ll run out while the new stock is on its way? Work it out from three numbers in the sheet. The daily sales rate — how many you sell per day on average. The supplier lead-time days — how long the supplier takes to deliver after you place an order. And the safety buffer — the cushion you keep in case sales spike or the supplier is late. The point is simply: rate × lead time + buffer.

An example. House Blend beans sell about 8 bags a day. The roaster takes 5 days to deliver. You keep a 20-bag buffer. So the reorder point is 8 × 5 + 20 = 60 bags. The moment the count drops to 60, ordering now means the new batch lands roughly as the buffer runs down — you never hit zero. Below 60 and you’re eating into the cushion; the further below, the more urgent. The urgent line is the buffer on its own (here, 20 bags): at or below it, you’re likely to sell out before any normal order can arrive, so the draft is flagged urgent so the owner sees it first.

Per-item overrides exist too. The sheet has an optional point_override column. Type a number there and the bot uses your reorder point for that one row instead of computing it. This is the right escape hatch for the item with seasonal demand you understand better than any formula, or the one where you simply want a round number.

Four moves, always

Every item, every check, lands in exactly one of four buckets. The names are simple on purpose.

  • Stocked. The on-hand count is comfortably above the reorder point, or the item is already on-order. Do nothing. Most items, most days, are stocked.
  • Watch. The count is getting close to the reorder point but hasn’t reached it — within the watch margin set in the rules doc (say, 10% above the point). No order yet, but the item is noted in the weekly digest so the owner can see what’s coming.
  • Reorder. The count is at or below the reorder point but above the urgent line. Draft a purchase order with full context and a sensible order quantity, and send it to the owner to approve. Write a row to the ir-orders DynamoDB table marking the draft as pending.
  • Urgent reorder. The count is at or below the urgent line — you may run out before the next delivery. Draft the same purchase order but flag it urgent so it sorts to the top of the owner’s queue, and consider the rules-doc option to suggest the faster (often pricier) supplier if one is configured. Mark it urgent in DynamoDB. This is the one case where the bot is allowed to nag daily until the owner acts.

State that makes the decision deterministic

The checker reads one DynamoDB table every check. ir-orders records every draft and every approved order: (item_id, status, draft_date, qty, supplier) where status is one of pending, on-order, or received. With that table, the move-decision logic is a few dozen lines of Python and zero magic. A given item with a given on-hand count, a given reorder point, and a given order status always produces the same move. Re-running the check produces no duplicate drafts (because the state in DDB shows what’s already pending or in flight).

When a delivery lands and the count goes back up (via Lane 1 or the POS lane), the item’s status flips to received and it becomes eligible for a fresh draft next time it dips. Part 5 covers the approve-and-order flow in detail.

Why the daily check uses no model

The bot could call a model on the check to forecast demand or write a smarter draft. It doesn’t. Two reasons. First, the daily check should be the one part of the system that is utterly predictable — if the sheet says rate 8, lead time 5, buffer 20, the reorder point is 60 and the draft fires at 60. A model in that loop introduces variance the owner can’t reason about, and the cost of a wrong reorder is real cash. Second, model calls cost money, and most days most items are stocked, so the call would be wasted nine days out of ten.

Bedrock fires elsewhere — on the inbound parsing lane in Part 2, and on the monthly summary mentioned in Part 6. Not on the daily check. The checker itself is plain Python that reads a sheet and writes events.

Next post: how a draft PO finds the right supplier, how quiet hours are honored, and how the order quantity gets a sensible number before the draft lands in front of the owner.

All posts