Designing Inventory Ledgers for Multi-Branch Businesses

The ledger event model

We model stock as an append-only table of signed quantity events. Stock is never updated in place — new rows are inserted, and the six event types cover everything that can physically happen to inventory:

CREATE TABLE inventory_events (
  id           BIGSERIAL PRIMARY KEY,
  occurred_at  TIMESTAMPTZ NOT NULL,
  branch_id    BIGINT NOT NULL,
  product_id   BIGINT NOT NULL,
  event_type   TEXT NOT NULL CHECK (event_type IN (
                 'purchase_receive','sale','return',
                 'transfer_out','transfer_in','adjustment')),
  qty_delta    INTEGER NOT NULL,     -- signed: positive in, negative out
  unit_cost    NUMERIC(12,4),        -- captured at event time for valuation
  reason_code  TEXT,                 -- REQUIRED when event_type='adjustment'
  reference_id BIGINT NOT NULL,      -- sale_id / po_id / transfer_id
  actor_id     BIGINT NOT NULL       -- who triggered the event
);

-- Example: product #1042 at branch #3 over one day
INSERT INTO inventory_events
  (occurred_at, branch_id, product_id, event_type, qty_delta, reason_code, reference_id) VALUES
  ('2026-08-01 09:12+06', 3, 1042, 'purchase_receive',  +120, NULL,        9001),
  ('2026-08-01 11:40+06', 3, 1042, 'sale',                -2, NULL,        9155),
  ('2026-08-01 12:05+06', 3, 1042, 'sale',                -5, NULL,        9160),
  ('2026-08-01 14:22+06', 3, 1042, 'return',               +1, NULL,        9155),
  ('2026-08-01 18:30+06', 3, 1042, 'transfer_out',       -30, NULL,        7741),
  ('2026-08-01 20:00+06', 3, 1042, 'adjustment',           -3, 'damaged',   7802);

Transfers write two events — a negative transfer_out at the source branch and a matching positive transfer_in at the destination — linked by the same reference_id. If only one half arrives during a sync failure, the unmatched half is exactly what your reconciliation report surfaces. Adjustments require a reason code, which turns “shrinkage” from a mystery into a queryable category.

Stock availability formula

Availability is derived, never stored as truth:

available = SUM(inventory_events.qty_delta) − SUM(committed)

Committed covers quantities promised but not yet shipped: unpaid baki orders being held, an in-flight transfer not yet received, items reserved for an invoice awaiting payment. In practice we materialize this sum into a per-branch balance table that the ledger updates transactionally on every insert — fast reads, with the event stream remaining the source of truth you can replay to rebuild balances any time.

For multi-branch retailers, stock availability must be computed from branch-level inventory transactions, never a single global quantity.

The distinction is operational, not academic. When a customer at the Dhanmondi branch asks for 10 units, the answer that matters is Dhanmondi's available stock — not a company-wide total where 40 units sit in a Chattogram warehouse. Global totals still exist for purchasing decisions, but every checkout, transfer suggestion, and reorder point evaluates branch-level sums.

Reconciliation & audit trail

Because events are append-only and immutable, a physical count never edits history. A stock count produces a proposed adjustment; approving it inserts a new adjustment event with the variance and reason code. The gap between book stock and counted stock stays permanently visible, per branch, per product, per actor — which means recurring negative variance on one employee's shift or one supplier's deliveries becomes a report, not a rumor. Every event also carries actor_id and reference_id, so “why did this SKU drop by 12 last Tuesday?” is a one-query answer instead of an afternoon of camera footage.

FIFO layer tracking approach

For cost accounting we track consumption against FIFO layers rather than averaging costs into a single price. Each purchase_receive opens a layer with its own unit_cost; every sale consumes quantity from the oldest open layer first, recording COGS at that layer's cost. When a sale spans two layers (30 units sold, 12 left in layer one, remainder from layer two), the ledger writes the split explicitly. This gives you correct gross margins even when purchase prices swing month to month — common for imported electronics and grocery staples alike — and makes FEFO a natural extension for pharmacies by ordering layers by expiry instead of receipt date.

Need inventory software that stays accurate?

Book a free consultation. We'll show you how a ledger-based system handles your branches, transfers, and audits.