Model Stock Counts So Variances Explain Themselves: CountSession, BlindCountLine, Recount and StockAdjustment

#erp #database #architecture #webdev

By Isaac Muthui, CEO, Tamalaki Business Network Ltd. Originally published at factoraerp.com.

In many inventory systems, a stock count is modelled as one operation: "set quantity on hand to X." It is simple, and it destroys information. The difference between what the system expected and what was counted disappears into an overwrite, along with the reason it happened.

For food and agro manufacturers that is a real problem. A variance usually means something upstream was not recorded: a goods receipt, a production issue, a dispatch. If your data model cannot hold the variance, its recount and its cause, your users will keep "fixing" the same gap every month.

This piece is for developers modelling inventory in manufacturing ERPs. The factory-floor checklist behind it is on the FactoraERP blog (canonical link above).

The domain objects you need

StockBalance: quantity by item_id, lot_id, location_id and status (released | hold | quarantine | rework | returns | scrap). Lot and status are part of the key, not optional attributes.
CountSession: id, type (full | cycle), cutoff_at, scope (locations or items), state.
BlindCountLine (table count_line): session_id, item, lot, location, status observed on the floor, counted_qty, uom, counted_by, counted_at.
Recount: a second BlindCountLine for the same key, by a different user.
Variance: expected_qty (snapshot at cut-off), final_counted_qty, difference, value, reason_code.
StockAdjustment: an immutable movement posted from an approved variance, with approved_by and approved_at.

Invariants worth enforcing

Snapshot at cut-off. When a CountSession moves to counting, store expected_qty per key from the ledger as of cutoff_at. Movements posted after cut-off must not change the expected figure.
Blind counting. The count API and UI never return expected_qty while the session is in counting. Expose it only in reconciling.
Separation of duties. recount.counted_by != original.counted_by, and adjustment.approved_by must not be any user who counted that key.
Thresholds trigger recounts. If abs(difference) > threshold(item) (quantity or value), the variance cannot be approved until a recount exists.
No adjustment without a reason. reason_code is required (timing, production_issue, intake, dispatch, expiry_damage, uom_error, unexplained). Flag unexplained for escalation.
Adjustments are movements, not overwrites. Post a ledger entry dated at the count's cut-off. Never UPDATE stock_balance SET qty = counted.
Status mismatch is a finding. If the observed floor status differs from the system status (a "released" pallet in the quarantine bay), record it even when quantities match.

A minimal state machine

CountSession: draft -> counting -> reconciling -> approved -> posted
                         |             |
                         +--(recount)--+

draft -> counting: freeze scope, snapshot expected quantities.
counting -> reconciling: all lines counted; variances calculated.
reconciling -> approved: every variance above threshold has a recount and a reason; approver passes the role and limit checks.
approved -> posted: write StockAdjustment movements in one transaction.

Schema sketch

create table count_line (
  id bigserial primary key,
  session_id bigint not null references count_session(id),
  item_id bigint not null, lot_id bigint, location_id bigint not null,
  observed_status text not null,
  counted_qty numeric(14,3) not null, uom text not null,
  counted_by bigint not null, counted_at timestamptz not null,
  is_recount boolean not null default false
);

create table variance (
  session_id bigint not null, item_id bigint not null,
  lot_id bigint, location_id bigint not null,
  expected_qty numeric(14,3) not null,
  final_qty numeric(14,3) not null,
  value numeric(14,2) not null,
  reason_code text,
  approved_by bigint, approved_at timestamptz
);

Reporting that pays off

Once variances persist with reason codes, the useful report is trivial: variance value by item and reason across sessions. An item that shows production_issue week after week points to a recording gap on the line, not to a counting problem.

Records

In Kenya, section 23 of the Tax Procedures Act, 2015 sets a general five-year retention period for documents required under a tax law (with exceptions). If count sheets and approved adjustments are part of your users' record set, design for retention and audit trails rather than deletes.

The full operations checklist, with FAQ, is here: Stock Count Reconciliation Checklist.

DEV Community