allanninal.dev/build

Part 1 of 7 · Stock count reconciler series ~10 min read

A stock count reconciler on AWS for a few dollars a month

Every business that holds stock runs a count now and then, and every count tells the same uncomfortable story: the shelf and the system disagree. There are 432 bolts on the rack and the system swears there are 480. A tin of paint the system has never heard of is sitting in aisle three. Forty-eight units gone and nobody can say whether it walked out the door, was sold without ringing through, or was never delivered in the first place. Reconciling that by hand — line by line, working out each gap and chasing each cause — is slow, dull, and the first thing that gets skipped when the count runs late. This post walks through the design of a small system that takes a physical count, compares it against the system of record, flags every variance with a likely cause, and queues the adjustments for sign-off. It never moves stock on its own.

Key takeaways

  • Two ways to submit a count: a scanner export or a filled-in sheet. Both land as clean counted lines.
  • Every counted line lands in one of three states: in agreement, a minor variance that’s logged, or a material variance that’s queued.
  • Each material gap is labelled with one of four likely causes: shrinkage, miscount, unrecorded sale, or receiving error.
  • The variance arithmetic is plain code. The model only suggests the cause — it never moves stock.
  • Designed on AWS for about $2.20/month at typical small-business volume. No adjustment posts without a manager’s sign-off.

The whole system on one page

Before any code, here’s the shape of what we’re designing. A stock count reconciler is not a reorder bot — it never buys anything. It takes a physical count, lines it up against the system of record, and tells you where the two disagree and why. The buying decision stays with you.

System architecture: three sources, three pieces inside AWS At the top, three external boxes in a row. Far left, "Stock counters" — the staff who walk the aisles and submit a physical count, either as a scanner export or as a filled-in spreadsheet. Centre, "System of record" — the current inventory quantities the count is reconciled against, plus the count sessions that group a count, plus a short rules doc holding the materiality tolerance and the approver per location. Far right, "Managers" — the people who sign off adjustments; material variances land in their email with the counted quantity, the system quantity, the gap, a suggested cause, and Approve and Reject buttons. Each connects via an arrow to the AWS account container below. Counts flow in from the counters. The system of record grounds every comparison. Managers receive the flagged gap and send back a decision. Inside the AWS account are three components in a row, mirroring the layout above. On the left, the Count capture — receives the scan or sheet from S3, ties it to a count session, and normalises it into clean counted lines of SKU, location, and counted quantity. In the middle, the Variance and cause engine — joins each counted line to the system quantity, computes the gap in units, percent, and value with plain code, decides whether it crosses the materiality tolerance, and for each material gap asks one Bedrock call for a likely cause from a fixed list of four. On the right, the Approval and write-back — queues each material variance, emails the right manager, records Approve or Reject, and on approve posts the adjustment back to inventory with a full audit trail. Internal arrows flow left to right. A note at the bottom reads: the model only suggests causes — the arithmetic is plain code, and nothing posts to inventory without a manager's sign-off. Stock counters scan or sheet System of record quantities, sessions, rules Managers where variances land count in grounds gap with a cause AWS account Count capture read, tie to session, make clean lines Variance & cause compute the gap, suggest a likely cause Approval & write-back approve, then post lines gap The model only suggests causes — the arithmetic is plain code, and nothing posts without a sign-off.
Fig 1. Three sources outside, three pieces inside AWS. A count flows in as a scan or a sheet. The engine computes every variance and labels the material ones with a likely cause. The approval piece queues each one for a manager, who approves or rejects before any adjustment posts.

What you set up once (the outside)

  • The system of record. Wherever your current inventory quantities already live — a stock spreadsheet, the export from your point-of-sale or warehouse system. One row per stocked line: SKU, item name, location, the system quantity, and a unit cost. You already keep this; the reconciler just needs to read it. A small sync mirrors it into a DynamoDB table, stkr-inventory, so every comparison runs against a stable snapshot rather than a live system mid-edit.
  • A rules doc. One short doc holding the materiality tolerance — how big a gap has to be before it’s worth a manager’s time. A common set: a variance is material if it exceeds 2 units and 1% of the system quantity, or if the value of the gap exceeds £20, whichever trips first. Anything smaller is logged but posts nothing. The doc also names the approver per location and the quiet hours for notifications.
  • Managers. The people who sign off adjustments — usually a store or warehouse manager. Each has an email address. Material variances land with the SKU, the counted quantity, the system quantity, the gap in units and value, the suggested cause with its one-line rationale, and two buttons: Approve and Reject. A button click never moves stock by itself — Approve posts the adjustment and records who did it; Reject discards it and the count line stands.

What runs on every count (the inside)

  • The count capture. A count starts a session — a row in stkr-counts recording the location, who’s counting, and when. Staff submit either a scanner export or a filled-in sheet; both land in S3, which fires a count-submitted event. The capture Lambda reads the file, ties it to the open session, and normalises it: de-duplicates repeat scans, drops blank rows, maps each entry to a known SKU, and converts cases to eaches where needed. Out comes a clean list of counted lines — SKU, location, counted quantity — with nothing reconciled yet. Part 2 covers this.
  • The variance and cause engine. Runs as soon as a session is captured. For each counted line it looks up the system quantity, subtracts, and computes the gap three ways: units, percent, and value. It applies the materiality tolerance to sort each line into in agreement, a minor variance (logged, no adjustment), or a material variance (queued). For each material gap it builds a small evidence bundle — the gap, recent sales, the last receiving, the last count date — and asks one Bedrock Haiku 4.5 call to pick a likely cause from a fixed list: shrinkage, miscount, unrecorded sale, or receiving error. The arithmetic is plain Python; the model only labels. Parts 3 and 4 cover this.
  • The approval and write-back. Takes each material variance and queues it for the right manager. The email carries the full picture and the suggested cause, with Approve and Reject. On approve, the adjustment is posted to stkr-inventory atomically and a row is written to stkr-adjustments with a before-and-after snapshot, so the trail is auditable. A daily sweep re-surfaces any variance left unactioned, and a monthly summary writes a short narrative: lines counted, value caught in variances, the split of causes. Part 5 covers this.

In plain words

The Saturday team counts the fixings aisle and submits the scan. The capture turns it into clean lines and the engine reconciles them. Most agree. One does not: SKU BOLT-M6-50, counted 432, system says 480 — a gap of 48 units, 10%, worth £16.80 at £0.35 each. That clears the materiality tolerance, so it’s queued. The engine pulls the evidence: no delivery of M6 bolts in the last month, steady sales, last counted eight weeks ago. One Bedrock call returns likely cause: shrinkage with the rationale “a sustained shortage with no recent receiving and normal sales points to loss rather than a recording error.” The manager, Priya, gets an email: “BOLT-M6-50 — short 48 units (−10%, −£16.80). Likely shrinkage. [Approve] [Reject].” She recounts the rack, confirms it, and taps Approve. The system posts −48 against the SKU and logs the adjustment. The leak is now visible, dated, and explained — not discovered six months later when the numbers stop adding up.

Running this costs about $2.20 a month at SMB volume. The cost of not running it is shrinkage that hides inside “the count was a bit off,” receiving errors nobody traces back to the supplier, and a system of record that drifts further from the shelf with every month that passes.

Design rules that shaped every decision

  • The arithmetic is plain Python against a tolerance in a doc. No model decides whether a gap is real or how big it is.
  • Three states for every line: in agreement, a minor variance that’s logged, or a material variance that’s queued. There is no fourth.
  • Four likely causes, always: shrinkage, miscount, unrecorded sale, receiving error. The model picks from that list and explains itself in a sentence.
  • It never moves stock on its own. A manager approves every adjustment, and the email carries the exact gap.
  • It reconciles; it does not reorder. Working out what you have is a different job from deciding what to buy.
  • Every action is logged. Audit an adjustment next year and you can see who posted it, when, and why.

Why this shape

Most small teams handle a stock count one of three ways: they count, eyeball the obvious gaps, and overwrite the system with the new numbers; they count and never reconcile, so the sheet goes in a drawer; or they reconcile by hand on a spreadsheet that takes a day and gets abandoned halfway. Overwriting the system loses the variance entirely — you never learn that 48 bolts went missing, only that the number is now 432. Never reconciling means the system and the shelf drift apart until nobody trusts either. And reconciling by hand is real work that competes with everything else on a manager’s desk.

The setup above keeps the system of record as the source of truth, takes the count as a fact, and puts a small system between them that computes every gap, explains the material ones, and changes nothing until a person says so. The gaps inside tolerance get logged and ignored, so the manager only ever looks at the handful that matter. Each of those arrives with a likely cause already attached, so the decision is “recount or accept,” not “start investigating.” And nothing is ever posted automatically — the reconciler proposes, a human decides.

The next four posts walk through each piece in turn: how a count gets captured, how a variance gets found, how a likely cause gets suggested, and how an adjustment gets approved. One diagram per post. A cost breakdown and a final engineering reference at the end.

All posts