NS inventory reconciliation

What this is

Cycle counts, not annual freeze

The NS inventory reconciliation flow runs a rotating cycle count: every warehouse zone is counted once every 30 days; every SKU lands on the list at least once per month. We never do annual full freezes — the warehouse can't go dark for a day, and cycle counts catch shrink + bin-error far faster anyway.

The discipline came in after R49 — senior architect review flagged inventory drift as the silent budget-killer. Pre-cycle-counts, we'd discover quantityonhand drift only when a SO short-picked. Post-cycle-counts, variance is caught in the same week.

Diagram: ns-inventory-reconciliation.html. The flow has 10 phases. One is STUB: physical-count autoflow via barcode scanner. Today, warehouse staff walk the zone with a paper tally sheet or type counts into the NS UI mobile app. There is no scanner integration.

When to use it

Trigger conditions

Weekly cron fires per warehouse zone (rotation guarantees every SKU within 30 days).
An SO short-picks — ad-hoc count for the affected SKU and adjacent bins.
Receiver rejects a PO line for short-ship — count the receiving bin to confirm.
Post-incident: after a known damage event (forklift, water leak), the affected zone gets a manual count.
Pre-bid: before a large bid award like B5875, count the relevant SKUs to confirm fulfillment capacity.

Threshold

Auto-adjust if |variance_pct| ≤ 2% AND |variance_value| ≤ $100. Outside that band, HITL fires. Both bars must hold — a 0.5% variance on a $50K SKU is still a $250 swing and gets HITL.

Worked example

Zone B count — Monday morning

Scenario

Monday 06:00. The weekly cron picks Zone B (cold dock, 47 SKUs). Floor team (Marcus + temp) walks the zone with the paper tally sheet (STUB — no scanner). Counts entered into NS UI by 09:30. The variance engine runs at 09:35.

46 SKUs match within 2% / $100 — auto-adjusted, NS push queued, D1 mirror updated by 09:38. One outlier: SKU 21044 — Right Start Foods Egg Patty shows physical 184 cases, on-hand 142 cases — +29.6%, $1,470 value. HITL fires. Card lands at /proposed-actions.html with the variance, the prior-30-day txn list, and a suggested reason ("partial pallet not booked on PO receipt 3 days ago, likely receiving timing"). Mike taps Approve at 10:14. NS adjustment posted by 10:16. reflexion_log row written; events.inventory.adjusted fires. Customer health watcher consumes the event for the affected customer's allocation outlook.

Step-by-step what happens

The ten beats

01

Cycle count scheduled

Weekly cron per warehouse zone selects a count list. Cron rotates zones so every SKU is counted within 30 days. The list is materialized into cycle_count_queue with status='pending'.

Cron 0 6 * * 1
Rotation 4 zones / 30 days
02

Physical count — STUB autoflow

Warehouse staff walks the zone and records counts. Today: paper tally sheet or NS UI mobile keyboard entry. Barcode scanner integration is on the long-term roadmap but not wired — no scanner hardware deployed, no scanner-to-NS path. This is the single highest leverage automation gap in inventory ops.

STUB scanner autoflow
Today paper + keyboard
03

Variance detection

Diff physical_qty vs inventory_balance.quantityonhand per item × location. Compute variance_pct and variance_abs (absolute units and dollar value at current cost).

Reads inventory_balance, items.unit_cost
Writes cycle_count_results
04

Threshold branch — auto or HITL

If |variance_pct| ≤ 2% AND |variance_value| ≤ $100, route to auto-adjust. Otherwise route to HITL. Threshold configurable in guardrails.

Reads guardrails
05

HITL stage — for outliers

A proposed_actions row is inserted with action_type='inventory_adjustment'. Payload contains item_code, location, on_hand, physical_qty, variance, suggested_reason. The suggested_reason field looks at recent transactions for hints (PO receipt timing, recent damage flag, etc.).

Writes proposed_actions
Risk 2-3 depending on $ value
06

Mike approves at /proposed-actions.html

Admin reviews variance and reason. X-Edit-Token required for confirm. Approval flips proposed_actions.status='approved' via the R560 atomic claim.

Writes proposed_actions.status
Atomic R560 claim
07

NS push — inventoryadjustment

Approval drops a row in ns_pending_pushes targeting record_type='inventoryadjustment'. PushMutexDO drains and writes via the NS RESTlet OAuth1. Retry policy: 3 attempts, exponential backoff.

Writes NS inventoryadjustment
Retry 3 attempts
08

D1 mirror update

inventory_balance.quantityonhand reflects the new value. Reservation rows recomputed. Sync engine confirms NS parity on the next 5-min sync.

Writes inventory_balance
Confirm next sync
09

Audit trail

reflexion_log row written with reason + delta + approved_by. events.inventory.adjusted fires onto the event ledger. Customer health watcher, anomaly detector, and timeline UI all consume this event.

Writes reflexion_log, events
10

Variance report

The admin-dashboard tile surfaces last 30 days of variances by zone, item, and value. Patterns inform the next cron schedule — if Zone B keeps producing outliers, the cron raises its weight.

Reads cycle_count_results
Surfaces /admin-dashboard

Outcomes

What's different after a count

Variance

Resolved

auto or HITL

Audit trail

Written

reflexion + event

NS ↔ D1

In sync

5-min warm tier

Allocation

Accurate

SO short-picks ↓

NS and D1 agree on quantityonhand per SKU per location.
Every adjustment has a reason in reflexion_log.
Patterns (recurring outliers, zone-level drift) inform the next cron.
Downstream allocation, customer health, and bid promise dates reflect the true inventory.

Failure modes

What can go wrong

Scanner STUB — count error risk

Paper tallies introduce transcription errors. Mitigation: floor lead reviews the tally before NS entry. Detection: HITL rate > 5% of count items indicates systemic miscounting. Long-term fix: scanner integration with NS UI mobile.

Count happens during active picking

If picking happens mid-count, physical_qty becomes a moving target. Mitigation: zone-level pick hold for the count window. Detection: variance + pick-list timestamps that overlap the count window.

NS push fails — D1 ahead of NS

D1 has the adjustment, NS doesn't. The reconciliation cron at 0 */15 * * * catches this within 15 min and re-enqueues. Manual recovery: POST /admin/ns-push/retry?action_id=<id>.

Auto-adjust threshold drift

If many counts cluster just under the threshold, real drift hides. Detection: variance histogram on the admin-dashboard tile. Recovery: tighten the threshold or trigger ad-hoc HITL review.

Adjacent flows + diagrams

For developers

Code paths + invariants

Concern	Where
Cycle count cron	wrangler.toml — `0 6 * * 1`
Count queue	cycle_count_queue (D1)
Variance engine	src/index.ts cycle_count handler
Thresholds	guardrails.inventory_variance_pct, _value
HITL action_type	proposed_actions.action_type='inventory_adjustment'
NS push record	record_type='inventoryadjustment'
Event emitted	events.event_type='inventory.adjusted'
Audit row	reflexion_log entity_type='inventory_adjustment'
STUB — scanner	no scanner hardware; paper/keyboard only

// Threshold branch (simplified) const withinPct = Math.abs(variance_pct) <= 0.02; const withinValue = Math.abs(variance_value) <= 100; if (withinPct && withinValue) { autoAdjust(item, location, physical_qty); } else { stageProposedAction({ action_type: 'inventory_adjustment', risk_level: variance_value > 1000 ? 3 : 2, payload: { item, location, on_hand, physical_qty, variance, suggested_reason } }); }

Changelog

Dated trail · spot stale claims

Dated trail of when this doc was last touched, what changed, and what to look at if it feels stale.

Date	Round	Change	Touched by
`2026-05-26`	`R586`	Added CHANGELOG · SCHEMA · RUNBOOK · BACKLOG sections — wiki became best-in-class operating documentation.	Mike + Claude
`2026-05-25`	`R584/R585`	Wiki originally shipped — 8-section structure (hero / what / when / steps / outcomes / failure-modes / related / for-developers).	Mike + Claude

If today is more than 60 days past the latest changelog row, treat live system behavior as the source of truth. The doc may have drifted — verify against the workflow contract in workflow_definitions WHERE workflow_type='ns_inventory_reconciliation' before acting on these claims.

Schema · data contract

The machine-readable spec

Canonical fields, table names, endpoint signatures. What code should match, what tests should assert. workflow_type · ns_inventory_reconciliation · risk_level · N/A (cron-driven sync).

Inputs (required + optional)

Field	Type	Description
`location_id`	`integer?`	Optional scope; defaults to all locations.
`as_of_date`	`datetime`	Snapshot timestamp.

D1 tables written

Table	Operation	Trigger
`inventory_snapshots`	INSERT	Periodic snapshots
`inventory_drift`	INSERT	Diff vs prior snapshot
`proposed_actions`	INSERT	Adjustments > threshold

Endpoints called

Method	Path	Purpose
`POST`	`/api/sync/run?tier=warm&table=inventoryitem`	Inventory sync
`GET`	`/api/inventory/drift`	Drift dashboard

Events fired

event_type	When	Subscribers
`inventory.snapshot.taken`	Post-snapshot	audit
`inventory.drift.detected`	When drift > threshold	investigation queue

Runbook · when it breaks

It broke at 2am — what now

Different from "how do I use this." This is the page Mike pulls up when something is wrong: logs to check, recovery steps, who to escalate to.

Scenario · Drift flagged but on hand is actually correct

Sync timing race: snapshot taken mid-transaction.

Wait + re-snapshot: Take another snapshot 10 minutes later.
Mark as transient: Close the drift with reason='snapshot_race'.

Scenario · Large drift (>10% of on-hand)

Actual physical-vs-system discrepancy. Investigate.

Find the items: SELECT * FROM inventory_drift WHERE pct_diff > 0.10 ORDER BY snapshot_at DESC
Physical count: Warehouse counts the suspect items.
Adjust: Post inventory adjustment in NS; reconcile D1.

Scenario · Inventory sync stuck

Cron lock held or sync errored.

Check lock: SELECT * FROM cron_locks WHERE cron_name LIKE '%inventory%'
Release stuck lock: DELETE FROM cron_locks WHERE cron_name=? AND acquired_at < datetime('now','-30 minutes')
Manual sync: POST /api/sync/run?tier=warm&table=inventoryitem

Logs to check

workflow_run_log · top-level run audit
workflow_step_log · per-step trace
workflow_verify_results · post-window verify outcomes
cron_locks · stuck cron lock detection
events · workflow.completed / workflow.failed event trail
reflexion_log · per-run narrative (if reflexion_enabled)
npx wrangler tail · live Worker logs

Kill switch · emergency stop

If this workflow is misbehaving in a high-impact way (creating bad proposed_actions in volume, pushing wrong things to NS), flip a kill switch:

kill:ns_writes · stops every NS push platform-wide
kill:proposed_apply · stops HITL approvals from executing fan-out
kill:high_risk_ops · stops risk_level >= 4 fan-out

See kill-switches-state-machine.html for the full state machine + recovery procedure.

Escalation

Primary: Mike Levine (single-admin) · mikelevine@globalfoodsolutions.co. For prolonged outage during business hours, notify warehouse lead + accounting lead so they can defer dependent work.

Backlog · open questions

What's not done · what's uncertain

What's not done, what's uncertain, what we punted. Captured so it survives context switches and doesn't die in someone's head.

OPEN
Drift threshold per-category
Today flat threshold. Perishables vary more than shelf-stable.
STUB
Auto-stage adjustment proposal
Today drift is flagged but adjustment is manual.
DEFER
Cycle-count integration
Tie drift to scheduled cycle counts.