build.allanninal.dev

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

An inventory reorder bot on AWS for a few dollars a month

A small shop sells more things than anyone keeps in their head. The hero product that sells out the same week a big order lands and you have nothing to ship. The packaging boxes that take three weeks to arrive from the supplier. The cleaning supplies, the coffee beans, the filter cartridges, the screws in bin 14, the seasonal item that spikes every Friday. Run out of any of them and you either lose a sale or stop the line. This post walks through the design of a small bot that watches all of it, works out when each item is about to run low, and drafts a ready-to-send purchase order so the owner can approve it in one tap — it never orders on its own.

Key takeaways

  • Three sources for stock levels: a Drive stock sheet, an inbox forwarding lane, and a live POS lane.
  • Every item ends in one of four moves on each check: stocked, watch, reorder, or urgent reorder.
  • Reorder point per item: daily sales rate × supplier lead-time days, plus a small safety buffer.
  • Drafts a purchase order for the owner to approve. It never sends an order on its own.
  • Designed on AWS for about $2 a month at typical small-business volume.

The whole system on one page

Before any code, here’s the shape of what we’re designing.

System: three sources, three pieces inside AWS At the top, three external boxes in a row. Far left, "Stock levels" — a Google Drive stock sheet listing every item with its on-hand count, daily sales rate, supplier, and lead time, plus an inbox forwarding lane and a live point-of-sale lane that update counts. Centre, "Rules and suppliers" — a Drive folder with a rules doc covering safety buffers, order caps, and quiet hours, plus a supplier doc with each supplier's email, pack size, and PO template. Far right, "Owner" — the person who approves orders; draft purchase orders land in their Slack DM or, if no Slack ID is set, their email. Each connects via an arrow to the AWS account container below. Stock levels have an outgoing arrow into AWS. Rules and suppliers feed in to ground every draft. The owner receives a draft PO with the item, the suggested order quantity, the unit cost, the supplier, and three buttons: approve, edit, skip. Inside the AWS account are three components in a row, mirroring the layout above. On the left, the Stock intake — receives counts from each source, parses inbound stock sheets via Textract and Bedrock, and writes the cleaned count into the stock sheet. In the middle, the Checker — runs daily; reads the stock list; works out a reorder point per item; picks one of four moves: stocked, watch, reorder, or urgent reorder. On the right, the Draft PO — builds the purchase order, rounds the quantity to pack size, and sends it to the owner to approve, edit, or skip. Internal arrows flow left to right. A note at the bottom reads: every draft leaves with full context — the bot never just says "something is low." Stock levels sheet, inbox, POS Rules and suppliers buffers, caps, templates Owner where drafts land counts in grounds draft PO with context AWS account Stock intake parse, normalize, update the sheet Checker picks one of four: stocked, watch, reorder, urgent Draft PO builds the order, owner approves it count move Every draft leaves with full context — the bot never just says “something is low.”
Fig 1. Three sources outside, three pieces inside AWS. Counts flow in from a Drive stock sheet, an inbox forwarding lane, and a live POS lane. The Checker runs daily and picks one of four moves. Draft PO builds the right order and sends it to the owner to approve.

What you set up once (the outside)

  • Stock levels. A Google Sheet in a Drive folder, one row per item: name, SKU (the short code that identifies the item), supplier, on-hand count, daily sales rate (how many you sell per day on average), supplier lead-time days (how long the supplier takes to deliver), safety buffer, pack size, and unit cost. You can fill it in once and let the other lanes keep it fresh; new counts can also enter via two more lanes covered in Part 2 — an inbox-forwarding lane (forward a stock-count sheet to a dedicated address and the bot proposes updated rows for one-tap approval) and a live point-of-sale lane (your till or online store tells the bot the moment something sells).
  • A rules folder. Two short Google Docs in a Drive folder. The rules doc covers the safety buffer for each item or category — the extra cushion you want on hand in case sales spike or the supplier is late — plus an order cap (the most you ever want ordered in one go) and the quiet hours. The supplier doc holds each supplier’s ordering email, pack size, minimum order, and the wording of the purchase order. Both live in Drive so a staffer can change a buffer or a supplier email without anyone redeploying code.
  • Owner. The person who approves orders. They have a Slack member ID (so the draft is a private DM, not a public ping) or, if Slack isn’t set up, an email address. Drafts land with the item name, the suggested order quantity, the unit cost, the supplier, and three buttons — Approve, Edit, Skip.

What runs on every check (the inside)

  • The stock intake. Three sources feed the stock sheet. The Drive sheet itself is the canonical store. Counts can also be updated via the inbox forwarding lane (forward a stock-count sheet or a supplier price list to stock@your-company.com, the bot uses Textract to read it and Bedrock Haiku 4.5 to pull out item, count, and price, then drops a one-tap approval card in the owner’s Slack before any row changes) and the live POS lane (your till or store posts each sale to the bot, which lowers the on-hand count in near real time).
  • The checker. Runs once a day, early in the morning. Reads the stock list. For each item, works out the reorder point — how low the on-hand count can get before you have to order, allowing for how fast it sells and how long the supplier takes. Picks one of four moves. Stocked: plenty on hand — do nothing. Watch: getting close to the reorder point — note it for the digest, no order yet. Reorder: at or below the reorder point — draft a purchase order for the owner. Urgent reorder: already so low you may run out before the next delivery — draft the PO and flag it as urgent. The checker doesn’t call a model on the daily check — the move logic is plain Python.
  • Draft PO. Reads the supplier doc, builds the purchase order for the chosen move, rounds the order quantity up to the supplier’s pack size, caps it at the rules-doc limit, and sends it to the owner. Slack DMs go through the Slack API. Email goes through SES outbound. Both honor quiet hours (no pings between 6pm and 8am local by default). Nothing is ever ordered until the owner taps Approve. Every draft writes a row in DynamoDB so the next day’s check knows the item is already pending. A weekly digest summarizes what got ordered and what’s on watch. A monthly summary writes a board-ready paragraph: spend by supplier, items that hit urgent, stockouts avoided.

In plain words

Your best-selling coffee beans sell about 8 bags a day. The supplier takes 5 days to deliver, and you keep a safety buffer of 20 bags. So the reorder point is 8 × 5 + 20 = 60 bags — once you’re down to 60, it’s time to order or you’ll run dry while the new batch is in transit. The owner is you. Monday morning the bot sees the count has dropped to 58. It drafts a purchase order for 120 bags (a full case, rounded to pack size) at $4.20 a bag to your usual roaster, and DMs you in Slack: “Reorder — House Blend beans, 120 bags at $4.20 = $504, supplier Bayside Roasters — on hand 58, sells ~8/day.” with Approve, Edit, Skip buttons. You tap Approve. The PO email goes to the roaster, the item is marked on-order, and the bot won’t draft it again until the delivery lands. No stockout, no panic order at a premium, no Saturday spent counting shelves.

The cost of running this is about $2 a month at SMB volume. The cost of not running it is the one weekend you sell out of your hero product, or the packaging that didn’t arrive in time, or the rush order you paid double for because nobody noticed the bin was empty.

Design rules that shaped every decision

  • Every draft ships with full context — item, suggested quantity, unit cost, supplier, on-hand count. The owner never has to dig.
  • Four moves, always. Stocked, watch, reorder, urgent reorder. There is no fifth.
  • Nothing is ordered without a human tap. The bot drafts; the owner approves, edits, or skips.
  • Quiet hours are respected. A reorder draft at 11pm helps nobody; it waits for morning.
  • The stock list lives in Drive. Adding an item, changing a supplier, or shifting a buffer doesn’t need a deploy.
  • Every action is logged. Audit an order next year and you can see exactly what was sent and by whom.

Why this shape

Most shops track stock in one of three places: a spreadsheet that nobody updates, a gut feeling about what’s “getting low,” or the moment a customer asks for something and the shelf is bare. The spreadsheet works until it doesn’t — one busy week and the counts go stale. The gut feeling is fine for the three items you think about and useless for the other two hundred. And the empty shelf, of course, is the most expensive way to find out: by then you’ve already lost the sale.

The setup above moves the source of truth into a sheet the team already edits, but adds a small system that looks at that sheet every day and acts only when something needs ordering. Drafts come early enough to order at a normal price from your normal supplier. They include enough context that the owner doesn’t have to go check the shelf. And nothing leaves without a human saying yes — because an order placed by mistake costs real money and ties up real cash. The bot is invisible most days; visible only on the days something actually needs reordering.

The next four posts walk through each piece in turn: how a stock level gets read, how the bot spots a low item, how a draft PO reaches the owner, and how a reorder gets approved and the cycle restarts. One diagram per post. A cost breakdown and a final engineering reference at the end.

All posts