Monthly margin review

What this is

Monthly margin review

Monthly margin review is the recurring health check that keeps the platform honest about whether pricing decisions are landing where they should. It runs on the first of every month at 07:00 ET, so Mike sees the prior month's margin state with his morning coffee.

The cascade aggregates margin by customer × brand × category, compares against the trailing 3-month average, and surfaces compressions greater than 5 percentage points. Each outlier gets a propose_margin_target_pricing row in Mike's queue.

Risk level 3 (medium). Nothing pushes to NS directly — every proposed change still flows through the standard bid_price_update or vendor_cost_update path with its own HITL gate.

When to use it

Trigger conditions

First-of-month cron at 07:00 ET — the default path.
Manual re-run from /admin-dashboard.html when a mid-month review is needed (e.g. after a vendor cost spike).
After a deploy that changed margin computation logic — useful to confirm the new math.

Heuristic

The compression_threshold_pp input defaults to 5pp. Tighter thresholds (3pp) generate noisier queues; looser (8pp) miss real compressions. The default has held for 6+ months of monthly cycles.

Step-by-step what happens

The 3 beats

01

Stage price proposals for compressed lines

For each customer × brand × category where this month's margin dropped >5pp vs trailing 3-mo, a proposed_actions row stages with action_type=propose_margin_target_pricing. Mike approves or rejects per line.

Writes proposed_actions

Time ~80ms per outlier

Kind stage_proposed_action

Status real
02

Render executive summary

POST /api/digest/email-now with payload=monthly_margin_summary. Generates a PDF or HTML artifact summarizing the month, top compressions, top expansions, and the staged proposals.

Writes R2 + email body

Time ~5s

Kind http_call

Status stub
03

Deliver summary to Mike

A draft email to Mike with the summary as attachment / inline body, status=pending_review. Mike approves to confirm-receive (and sometimes forwards to the leadership team).

Writes outbound_email_log

Time ~80ms

Kind hitl_email_draft

Status stub

Note target · mike

Outcomes

What's different after the workflow runs

Margin pass

Monthly

0 7 1 * *

Outliers staged

Variable

one_per compression

Threshold

5pp default

configurable

Summary

Delivered

PDF/email artifact

Each outlier is a propose_margin_target_pricing row in Mike's queue.
The summary email has the month's health snapshot.
workflow_runs records the cron tick with totals.
reflexion_log carries a monthly_margin_review tagged row.
No NS writes happen here — downstream proposals own that.

Failure modes

What can go wrong and how to recover

Pricing data stale (>7d)

Precondition warns. Likely a sync_log issue — check /admin-dashboard sync status. The review still runs but flags the staleness in the summary.

No compressions found

A clean month. The summary email still sends so Mike sees the all-clear; reflexion logs it as a no-op tick.

Summary render times out

Browser Rendering occasionally stretches. The proposals still stage; only the summary artifact is delayed. Manual re-render: POST /api/digest/email-now?type=margin_summary.

Excessive outliers (signal spike)

If the count exceeds the historical mean by 3σ, Mike gets a heads-up note in the summary. Often signals a vendor cost spike or a margin-rule misconfiguration.

Adjacent workflows + diagrams

For developers

Code paths + invariants

Concern	Where
Workflow contract	workflow_definitions WHERE workflow_type='monthly_margin_review'
Cron	0 7 1 * * America/New_York
Compression threshold	5pp default (input override)
Aggregation view	v_pricing_with_brand
Summary endpoint	/api/digest/email-now
Reflexion tag	monthly_margin_review
Risk level	3
Expected duration	~60 min
Trigger	cron · 0 7 1 * * (America/New_York)

// Compression detection const compressions = lastMonth.filter(m => { const trailing = trailing3mo.get(m.key); return (trailing.avg_margin - m.avg_margin) >= 5; }); // One propose row per compression for (const c of compressions) stageAction('propose_margin_target_pricing', c);

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='monthly_margin_review' 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 · monthly_margin_review · risk_level · 3.

Inputs (required + optional)

Field	Type	Description
`month`	`string`	YYYY-MM. Required.
`threshold_margin_pct`	`decimal`	Below this triggers a flag; default 8%.
`scope`	`string`	'all' \| 'customer:' \| 'category:'

D1 tables written

Table	Operation	Trigger
`margin_review_runs`	INSERT	Run record
`proposed_actions`	INSERT (one per below-threshold line)	Bid_price_update suggestions
`events`	INSERT (margin.review_complete)	audit + dashboard

Endpoints called

Method	Path	Purpose
`POST`	`/api/workflow/execute`	Runner entrypoint
`GET`	`/api/margin-review/:month`	Run snapshot

Events fired

event_type	When	Subscribers
`margin.review_complete`	Post-run	monthly digest email
`margin.below_threshold`	Per-item flag	bid_price_update trigger candidate

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 · Review run but no proposals staged

All margins were above threshold, or scope was too narrow.

Verify threshold: SELECT threshold_margin_pct FROM margin_review_runs WHERE id=<run_id>
Re-run wider scope: Run with scope=all if narrow scope returned nothing.

Scenario · Same items flagged every month with no action

Flags aren't enforced; Mike sees them but doesn't always action.

Dashboard view: Surface chronic offenders prominently.
Auto-escalate: After 3 consecutive months below threshold, escalate to a forced bid_price_update proposal.

Scenario · Cost data was wrong — margin looked bad but actually fine

Vendor cost sync was stale.

Verify cost: SELECT cost, updated_at FROM vendor_costs WHERE item_id=<id>
Re-sync: POST /api/sync/run?tier=hot&table=vendor_costs
Re-run review: After sync, re-run.

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
Per-customer threshold
DOE accepts lower margin; commercial demands higher. Today single threshold.
STUB
Margin-trend visualization
Today a snapshot. Should show 6-month trend per item.
DEFER
Margin attribution
Why did margin drop? Cost up vs price down vs mix shift — not surfaced today.