How to build an inventory tracker

What matters here

• An inventory tracker is four parts: items, movements as a ledger, reconciliation, and reorder points.
• Never store quantity as an editable field. Record movements and derive the count, or you lose the ability to explain a discrepancy.
• Every movement needs a reason and a person. "Adjustment, minus 4" with no reason is where trust in the numbers dies.
• Reconcile against a physical count on a schedule and record the variance rather than silently correcting it.
• Reorder points need lead time built in. Reordering when you hit zero means being out of stock for the length of the delivery.
• Location matters as much as quantity. Four in the wrong warehouse is not four in stock.

Who this is for

You hold physical things and the record of what you hold is wrong often enough to be a problem. Equipment, stock, consumables, spare parts, marketing collateral, laptops waiting to be issued. What you want is a count you can trust and a warning before you run out.

How it works in practice

What happens to a thing from the moment it arrives to the moment the count disagrees.

1. 1

   Everything that moves is recorded as a movement

   Received, issued, transferred, returned, adjusted, written off. The stock level is derived from those and never stored as a field anybody can edit.

2. 2

   Every adjustment carries a reason and a name

   Minus four with no reason attached is the entry that makes people stop trusting the whole system.

3. 3

   Location is part of the count

   A transfer is one movement out and one in, recorded together, so four in the wrong warehouse never reads as four in stock.

4. 4

   Available is shown next to on hand

   Reserved, allocated and in-transit stock is excluded, because promising something already spoken for is the expensive error.

5. 5

   Counts record the variance rather than absorbing it

   The counter enters what they found before seeing the system figure, and the difference becomes its own adjustment.

6. 6

   Reorder points fire with the lead time built in

   Ranked by days of cover, so the item you use daily outranks the one you use twice a year.

1. 1

   Items, and movements as a ledger

   The one structural decision that decides whether the numbers stay true.

   Copy

   In this Helix project, build me an inventory tracker.
   
   Two tables, and the second one is the important one.
   
   Items: a code, a name, a description, a unit of measure, a category, a
   default location, a supplier, a unit cost, and whether the item is
   active.
   
   Movements: an append-only ledger. Item, quantity, a type from a fixed
   list (received, issued, transferred, returned, adjusted, written off),
   the location it moved from and to, a reason, who did it, and when.
   
   Derive the stock level from the movements. Never store a quantity field
   that somebody can edit. The moment two people can change the same number,
   the record stops being able to explain itself, and the question you will
   actually be asked is where the missing four went.
   
   Require a reason on every adjustment and write-off. An adjustment of
   minus four with no reason attached is how people stop believing the
   system.
   
   I sign in through Helix, so do not build a login page. Read the signed-in
   user from the runtime. And do not put any key or connection string in the
   code: if this needs to reach another system, tell me and I will create the
   connection in my workspace.

   Worth knowing. Movements as a ledger rather than a quantity field is the whole design. It is also the part that feels like extra work until the first time somebody asks what happened.

2. 2

   Locations, and what counts as in stock

   Because four in the wrong place is not four available.

   Copy

   Add locations, and make them real rather than a text field.
   
   A location has a name, a type (warehouse, stockroom, van, desk, in
   transit, with a customer), and optionally a parent, so a bin sits inside
   a stockroom inside a site.
   
   Stock is now per item per location, still derived from movements. A
   transfer is one movement out and one movement in, recorded together so
   the ledger balances.
   
   Then define available separately from on hand. On hand is everything
   physically present. Available excludes anything reserved, in transit, or
   already allocated to something. Show both, because the gap between them
   is what causes somebody to promise stock that is already spoken for.
   
   Let me allocate stock to a job, an order or a person, and have that
   allocation reduce available without touching on hand.

3. 3

   Reconcile, and keep the variance

   Because a count that silently corrects itself teaches you nothing.

   Copy

   Add reconciliation.
   
   Let me start a count for a location or a category, list the items to
   count, and enter what was physically found. Show the derived figure only
   after the counter has entered theirs, so the count is not anchored by the
   system.
   
   Where they differ, record the variance as its own adjustment movement,
   with the count as the reason. Do not quietly overwrite the number.
   
   Then report variance over time by item, by location and by person doing
   the counting. Persistent variance on one item is usually a unit of
   measure problem. Persistent variance in one location is usually a process
   problem. Persistent variance under one counter is a third thing, and you
   want to be able to tell them apart.
   
   Let me schedule counts so high-value or fast-moving items are counted
   more often than everything else, rather than one enormous annual count
   nobody enjoys.

   Worth knowing. Recording the variance rather than absorbing it is what turns a stock count from an inconvenience into the report that tells you why your numbers drift.

4. 4

   Reorder before you run out

   With the lead time built in, not the level.

   Copy

   Add reorder points, and build the lead time into them.
   
   For each item: a minimum level, a reorder quantity, and the supplier lead
   time in days. Calculate the reorder point from average consumption over a
   period multiplied by the lead time, plus a buffer I set, rather than
   asking me to guess a number.
   
   Reordering when stock hits zero means being out for however long delivery
   takes, which is the failure this is meant to prevent.
   
   Flag items below their reorder point, ranked by how many days of cover
   remain rather than by how low the number is. Two units of something you
   use twice a year is not urgent; two units of something you use daily is.
   
   Then report consumption trends per item, items that have not moved at all
   for a period, which is capital sitting on a shelf, and stock value by
   location for the finance conversation.

5. 5

   Connect it to real systems

   The step that turns a working prototype into something with your data in it.

   Copy

   Now connect it to the systems we actually run, so it works on real data
   rather than the rows you seeded.
   
   Anything in angle brackets is a placeholder. Swap it for whatever we use
   instead, and have the app name the connection it wants rather than assume
   a vendor, so changing my mind later is a config change and not a rewrite.
   
   <NetSuite>. Read purchase orders so a receipt can be matched to what was
   ordered, and push the stock valuation back.
   
   <Coupa>. Raise the reorder as a real purchase request rather than an email
   to a supplier.
   
   <Okta>. Read each person's location attribute, which is what the app uses
   to default a movement to their own site.
   
   <Slack>. Warn the owner when an item drops below its reorder point, ranked
   by days of cover.
   
   <Google Sheets>. Take the opening count as counted, since the first stock
   list is always somebody walking the shelves with a sheet.
   
   <Snowflake>. Land every movement, so consumption per site across a season
   can be modelled rather than eyeballed from one month.
   
   <Jamf>. For an IT stockroom, reconcile issued devices against what device
   management can actually see.
   
   Only write where I have said to write. Everything else is read only.
   
   Do not write a key, a token or a connection string anywhere in the code,
   and do not ask me to paste one. Tell me which connections you need and I
   will create each as an authentication in my Helix workspace. Reference
   them by alias.

   Worth knowing. Every name in angle brackets is a placeholder for whatever you run. Helix holds the credential as a workspace authentication, so the app references an alias and never the secret itself.

6. 6

   Ship it

   The last step of every build: a URL, and the right people on it.

   Copy

   Deploy my app.

   Worth knowing. Your assistant runs helix deploy underneath and the app comes back as a URL. Stock records feed a finance figure and an audit, so set Access Control before sharing: people record movements for their own location, and finance sees the valuation.

What it connects to

Most of what an inventory tracker needs already exists somewhere: what was ordered, what was paid for, and who holds it.

• NetSuite

  Read purchase orders so a receipt can be matched to what was ordered, and push the stock valuation back.

• Coupa

  Raise the reorder as a real purchase request rather than an email to a supplier.

• Okta

  Read each person's location attribute, which is what the app uses to default a movement to their own site.

• Slack

  Warn the owner when an item drops below its reorder point, ranked by days of cover.

• Google Sheets

  Take the opening count as counted, since the first stock list is always somebody walking the shelves with a sheet.

• Snowflake

  Land every movement, so consumption per site across a season can be modelled rather than eyeballed from one month.

• Jamf

  For an IT stockroom, reconcile issued devices against what device management can actually see.

Each line says whether the app reads, writes or both. Anything not described as writing should be read only.

Named systems are the ones most teams actually run, not a list of the only ones that work. Swap in whatever you use.

Each connection is an authentication in your Helix workspace, referenced by alias. The app names the connection it wants and never holds the credential, so nothing here ends up in your code and any of it can be rotated or revoked without a redeploy.

Related guides