Replaying a variance, what a cycle count has to store

Most cycle counts end the same way. Somebody walks an aisle, writes down what they found, and a number in a field gets replaced by a different number. The count was correct. The system is now more accurate than it was. And the only evidence that anything happened is gone.

The field that got overwritten was the input to a decision someone made last month. Overwriting it means nobody can tell you why the stock was wrong, whether the same SKU has been wrong four times this quarter, or whether the correction you are trusting today will need making again next Tuesday.

Why an absolute write loses the story

Setting a quantity is a statement about the present. Adjusting a quantity is a statement about a difference, and a difference can be replayed, aggregated and attributed.

Shopify's own inventory model makes the distinction for you. One level ties a single item to a single location, and it carries available, on-hand, incoming and committed as four separate quantities rather than one, so the number is already four numbers before anybody counts anything. And for the write path, the documentation for setting quantities is blunt about who may do it, restricting the mutation to callers "acting on behalf of a system that acts as the source of truth". That sentence is the whole ownership argument in one line. If two systems both believe they are the source of truth, the last absolute write wins, and neither of them can explain the result.

The table I actually want

create table stock_adjustment (
  id            bigserial primary key,
  location_code text not null,
  sku           text not null,
  delta         integer not null,          -- signed
  reason        text not null,             -- count_variance | mis_pick | damage | receipt_short | return_unsealed
  count_run_id  text,                      -- null when not from a count
  found_by      text not null,
  recorded_at   timestamptz not null default now(),
  unique (count_run_id, location_code, sku)
);

Three things fall out of this shape.

The current quantity is a sum, so it can be recomputed from events at any point in time and compared against what the live record says. That comparison is the reconciliation query, and it is the only way to notice that somebody edited a number directly.

select sku, location_code, sum(delta) as replayed
from stock_adjustment
where recorded_at < now() - interval '30 days'
group by 1,2
order by 1;

The unique constraint on the count run makes a retried submission harmless. A packing station that posts the same count twice because the network hiccuped should not move your stock twice, and an idempotency key on the write is the cheapest correctness you will ever buy.

The reason code turns a pile of corrections into a report.

select reason, count(*) as events, sum(abs(delta)) as units
from stock_adjustment
where recorded_at > now() - interval '90 days'
group by 1 order by 3 desc;

That query is the difference between "we have inventory problems" and "the receipt_short reason accounts for two thirds of our variance, and it is always the same supplier carton". One of those sentences gets fixed. The other one gets a meeting.

The mis-pick creates a pair, not a number

Here is the case that breaks a set-based design. A customer receives SKU A but ordered SKU B. The count says A is over by one and B is short by one. If your correction is two absolute writes, you have fixed both numbers and destroyed the fact that they were one event.

Model it as two signed rows sharing a count_run_id, with a reason of mis_pick, and the pair stays a pair. Six months later, when the same two SKUs keep appearing as mirror-image adjustments, you can see that they are a labeling problem on adjacent bins rather than two unrelated counting errors.

What this buys you at the moment it matters

When a channel oversells, the question is not what the number was. It is what the number was derived from, and who else wrote to it. An event table answers that with a query. A current-quantity field answers it with a shrug and a support ticket.

We keep this shape across three buildings in China, Suzhou the largest at 13,000 square meters, Dongguan next at 8,000, Shenzhen smallest at 3,000, under the FulfillNexa by SBT (fulfillnexa.com) name, and the reason is not a preference for append-only anything. It is that stock arguments between a seller and a provider are always about a past moment, and a system that cannot reconstruct past moments cannot settle anything except by paying a credit.

The quantity states and the source-of-truth wording are Shopify's published documentation. Read the current version the day you design against it, because the field that saves your reconciliation is the one that still exists.