Inventory reconciliation (cycle count)

0 · Visual flow Inventory reconciliation (cycle count) — 10 fan-out targets, 3 verify checks

idle

1 · Trigger FIRES WHEN…

kind

cron

schedule (cron)

0 6 * * MON

description

Weekly per-zone cycle count, every Monday 06:00 ET

2 · Inputs required

name	required	type / hint
`zone_id`	required	integer
`counted_at`	required	string
`counts`	required	array

3 · Context loaded D1 queries run before fan-out

name

zone

tables

inventory_zones

returns

row

SELECT
  id, name, location_id
  FROM inventory_zones
  WHERE id = ?

name

current_balances

tables

inventory_balance · inventory_zones

returns

rows

SELECT
  item_code, quantityavailable, last_counted_at
  FROM inventory_balance
  WHERE location_id = (SELECT location_id
  FROM inventory_zones
  WHERE id = ?)

name

recent_movements

tables

transactions · inventory_zones

returns

rows

SELECT
  item_code, type, qty, trandate
  FROM transactions
  WHERE trandate >= datetime('now','-7 days')
  AND location_id = (SELECT location_id
  FROM inventory_zones
  WHERE id = ?)

name

variance_tolerance

tables

guardrails

returns

row

SELECT
  pct_threshold, dollar_threshold
  FROM guardrails
  WHERE name = 'inventory_variance'

4 · Preconditions checked before any fan-out

check	rule	severity
`zone_exists`	`zone_id IN (SELECT id FROM inventory_zones)`	block
`counts_non_empty`	`length(counts) > 0`	block
`counted_at_recent`	`counted_at >= datetime('now','-24 hours')`	warn
`no_pending_movements`	`NOT EXISTS (SELECT 1 FROM ns_pending_pushes WHERE zone_id=? AND status='pending')`	warn

5 · HITL gate

Risk level 3 ≥ 3 — runner stages a proposed_actions row before fan-out runs. Mike must approve in proposed-actions.html before any side-effect step executes (real or stub).

action_type

workflow_inventory_reconciliation (proposal envelope)

entity_ref

workflow:inventory_reconciliation:run_<run_id>

approver

mike (single-admin)

6 · Fan-out targets 10 total · 3 real · 7 stub

#1`compute_variance` d1_write STUB

table

inventory_count_log

operation

insert

stub — not yet implemented in src/lib/workflow_runner.ts (kind d1_write hits the placeholder branch). Documented intent only.

INSERT INTO inventory_count_log (zone_id, item_code, counted_qty, system_qty, variance, counted_at)
  VALUES (?,?,?,?,?,?)

#2`classify_variance` flag STUB

table

inventory_count_log

field

classification

value

needs_review

condition (if)

abs(variance) > tolerance.pct_threshold OR abs(variance_dollar) > tolerance.dollar_threshold

stub — not yet implemented in src/lib/workflow_runner.ts (kind flag hits the placeholder branch). Documented intent only.

#3`stage_adjustments` stage_proposed_action REAL

action_type

inventory_adjustment

entity_type

inventory_zone

#4`hitl_approve` stage_proposed_action REAL

action_type

inventory_adjustment_approval

entity_type

workflow_run

#5`ns_push_adjustments` ns_push STUB

NS record_type

inventoryadjustment

queue

NS_PUSH_QUEUE

on_failure

retry_3_then_alert

stub — not yet implemented in src/lib/workflow_runner.ts (kind ns_push hits the placeholder branch). Documented intent only.

#6`d1_update_balances` d1_write STUB

table

inventory_balance

operation

update

stub — not yet implemented in src/lib/workflow_runner.ts (kind d1_write hits the placeholder branch). Documented intent only.

UPDATE inventory_balance
  SET quantityavailable=?, last_counted_at=datetime('now')
  WHERE item_code=?
  AND location_id=?

#7`kv_invalidate_inventory` kv_invalidate REAL

keys

inventory:zone:?zone_id
inventory:summary

#8`variance_summary_notify` hitl_email_draft STUB

tool

propose_email_to_customer

mailbox

warehouse@globalfoodsolutions.co

template

weekly_variance_summary

stub — not yet implemented in src/lib/workflow_runner.ts (kind hitl_email_draft hits the placeholder branch). Documented intent only.

#9`recompute_cost_basis` chat_tool STUB

tool

recompute_assembly_cost_rollup

condition (if)

any_adjusted_item IN assembly_bom

stub — not yet implemented in src/lib/workflow_runner.ts (kind chat_tool hits the placeholder branch). Documented intent only.

#10`reflexion_write` d1_write STUB

table

reflexion_log

operation

insert

stub — not yet implemented in src/lib/workflow_runner.ts (kind d1_write hits the placeholder branch). Documented intent only.

INSERT INTO reflexion_log (workflow_run_id, entity_type, entity_id, outcome, tags)
  VALUES (?,'inventory_zone',?,?,'inventory,cycle_count,variance')

7 · Post actions declared + runner-automatic

id	action	source
`runner_log_run`	INSERT into `workflow_run_log` (run_id, workflow_type, status, started_at, completed_at, summary_json)	runner automatic
`runner_reflexion`	INSERT into `reflexion_log` (tags=`inventory_reconciliation`, run_id, narrative)	runner automatic (reflexion_enabled=1)
`log_run`	INSERT workflow_run_log	declared in contract
`reflexion`	INSERT reflexion_log with tags=inventory,cycle_count	declared in contract
`event_completed`	FIRE event workflow.completed (workflow_type=inventory_reconciliation)	declared in contract

8 · Verify checks written to `workflow_verify_results`

name

counts_logged

when_to_run

5 min after run

expected

n > 0

SELECT
  COUNT(*) AS n
  FROM inventory_count_log
  WHERE zone_id=?
  AND counted_at > datetime('now','-1 hour')

name

adjustments_pushed

when_to_run

60 min after run

expected

n >= staged_count

SELECT
  COUNT(*) AS n
  FROM ns_pending_pushes
  WHERE record_type='inventoryadjustment'
  AND created_at > datetime('now','-1 hour')

name

d1_balances_updated

when_to_run

120 min after run

expected

n >= staged_count

SELECT
  COUNT(*) AS n
  FROM inventory_balance
  WHERE last_counted_at > datetime('now','-2 hours')

9 · Retry policy

max_attempts

3

backoff

exponential

base_ms

2000

max_ms

60000

alert_on_final_failure

true

10 · What changes when this workflow runs aggregated side effects

system	table / resource	action	status	source
D1	`proposed_actions`	INSERT (action_type=inventory_adjustment)	REAL	fan-out #3 (stage_adjustments)
D1	`proposed_actions`	INSERT (action_type=inventory_adjustment_approval)	REAL	fan-out #4 (hitl_approve)
KV (CACHE)	`inventory:zone:?zone_id, inventory:summary`	invalidate	REAL	fan-out #7 (kv_invalidate_inventory)
D1	`workflow_run_log`	INSERT (run summary)	REAL	runner automatic
D1	`reflexion_log`	INSERT (tags=inventory_reconciliation)	REAL	runner automatic
Event	`workflow.completed (or workflow.failed)`	fire	REAL	runner automatic
D1	`workflow_verify_results`	INSERT pending × 3	REAL	runner verify staging
D1	`proposed_actions`	INSERT (HITL gate envelope)	REAL	runner HITL gate
D1	`inventory_count_log`	insert	STUB	fan-out #1 (compute_variance)
D1	`inventory_count_log`	set classification=needs_review	STUB	fan-out #2 (classify_variance)
NetSuite (via NS_PUSH_QUEUE)	`inventoryadjustment`	push	STUB	fan-out #5 (ns_push_adjustments)
D1	`inventory_balance`	update	STUB	fan-out #6 (d1_update_balances)
D1	`proposed_actions`	INSERT (email draft via propose_email_to_customer)	STUB	fan-out #8 (variance_summary_notify)
Chat tool	`recompute_assembly_cost_rollup`	invoke	STUB	fan-out #9 (recompute_cost_basis)
D1	`reflexion_log`	insert	STUB	fan-out #10 (reflexion_write)

0 · Visual flow Inventory reconciliation (cycle count) — 10 fan-out targets, 3 verify checks

1 · Trigger FIRES WHEN…

2 · Inputs required

3 · Context loaded D1 queries run before fan-out

4 · Preconditions checked before any fan-out

5 · HITL gate

6 · Fan-out targets 10 total · 3 real · 7 stub

#1compute_variance d1_write STUB

#2classify_variance flag STUB

#3stage_adjustments stage_proposed_action REAL

#4hitl_approve stage_proposed_action REAL

#5ns_push_adjustments ns_push STUB

#6d1_update_balances d1_write STUB

#7kv_invalidate_inventory kv_invalidate REAL

#8variance_summary_notify hitl_email_draft STUB

#9recompute_cost_basis chat_tool STUB

#10reflexion_write d1_write STUB

7 · Post actions declared + runner-automatic

8 · Verify checks written to workflow_verify_results

9 · Retry policy

10 · What changes when this workflow runs aggregated side effects

#1`compute_variance` d1_write STUB

#2`classify_variance` flag STUB

#3`stage_adjustments` stage_proposed_action REAL

#4`hitl_approve` stage_proposed_action REAL

#5`ns_push_adjustments` ns_push STUB

#6`d1_update_balances` d1_write STUB

#7`kv_invalidate_inventory` kv_invalidate REAL

#8`variance_summary_notify` hitl_email_draft STUB

#9`recompute_cost_basis` chat_tool STUB

#10`reflexion_write` d1_write STUB

8 · Verify checks written to `workflow_verify_results`