TLDR: If you have order history, you already have inventory history. Replay the transactions, build the snapshots.

The Setup

We were building a supply chain dashboard for an ecommerce business and I wanted a line chart — how has on-hand inventory actually moved over the past 90 days?

Problem: we had no historical snapshots.

We'd just hooked Shopify up, imported current levels, and called it a win. Point-in-time history? Never recorded.

So I did what you probably do when you hit this wall: I started thinking about how to cobble together old data, maybe from CSVs, maybe from Shopify's reporting exports…

Then I stopped and actually thought about it.

The Thing I Should Have Seen Immediately

Shopify keeps every order. Every fulfilled order is a quantity delta — units out, date stamped.

My Postgres database also had every PO receipt. Units in, date stamped.

So if I replay every inbound receipt and outbound sale in chronological order — starting from today's known-good on-hand — I can walk backward through time and reconstruct exactly what the count was on any prior date.

The order history IS the transaction log.

I already had it. I just wasn't reading it right.

The Fix

One Postgres function:

  1. Pull all orders + PO receipts, sorted by date ascending
  2. Start from current on-hand (the one source of truth I had)
  3. Walk backward — add sales back, subtract receipts — to derive each day's snapshot
  4. Write rows into inventory_snapshots with a snapshot_date

Then I wired up an SVG line chart on the /history page, computed run rates per snapshot, scoped to reportable canonical SKUs.

One npm run backfill command. No manual entry, no CSVs, no scraping.

DONE.

Why Snapshots Alone Would Have Hurt Me

Here's the contrast: periodic snapshots sound fine until the job stops firing.

I know this firsthand. My Fathom ingest poller dropped out on April 29 due to a missed launchd bootstrap — silent, no alert. When I restored it two weeks later, the state file's window logic only looked back 3 days. I missed 11 days of transcripts entirely. Had to run --backfill 14 by hand just to fill the gap.

Snapshots are only as durable as the job that takes them.

Transaction logs survive an outage by definition — the events already happened. The only thing that stops you from replaying them is not having them at all.

The Forward Fix

Once I felt how useful a complete transaction log is, I got paranoid about not having one for lot tracking.

So I added lot_transactions (migration 021_lot_transactions.sql) right away. Every quantity-impacting mutation — receive, adjust, delete, reconcile to Shopify on-hand — auto-writes a row. Decreases require a reason code: sold_shipped | damaged_discarded | expired | sample_giveaway | other. other forces a free-text note.

No more "where did these units go."

The ledger tells you.

Why This Matters to Me

The reflex when you want history is "take periodic snapshots." But the right move is to write the ledger now and derive the snapshots later — not the other way around.

If you're not writing an append-only transaction log for state that matters, you're betting nothing will ever go wrong.

It will.

P.S. Lot tracking is now the canonical source of truth in this app — not ShipStation, not a spreadsheet. Which makes the audit log non-negotiable.