Ruo Restock Planner

RUO Restock Planner

Use this skill to calculate weekly or event-triggered RUO Peptides restock recommendations from Supabase stock, demand, restock, and SKU cost data. Read-only decision support: it recommends what to reorder, it never writes.

Quick Rules

• Work read-only. Never create restocks, payments, stock movements, supplier orders, or dashboard mutations. Recommendations only.
• Aggregate demand = empirical customer-SALE velocity from order history. This is the only demand basis by default. Do NOT invent demand.
• Per-patient protocol figures are a shape, not a rate. The monthly_vials/cycle_vials in protocol/stack artifacts are PER-PATIENT-PER-CYCLE consumption (one customer's burn on that protocol), NOT an aggregate monthly demand rate. Never use them as a reorder rate. (Proof: MOTS-C sells ~36/mo aggregate vs a per-patient figure of 5.)
• Stacks are new and have zero sales history. Any stack/BOM demand is a forward ASSUMPTION, off by default. Only surface it when a human supplies an uptake number, and label every such figure ASSUMPTION_DRIVEN. Never fold assumed demand into the default recommendation.
• Out-of-stock ≠ no demand. A stocked-out SKU has unobservable demand (you cannot sell what you lack) — never label it low-demand, NO_DEMAND, COA_UNECONOMIC, or discontinue. Park it until restocked; its forecast is a lower bound (FORECAST_IS_LOWER_BOUND). See Demand Observability.
• Before recommending any supplier order, run the transfer gate: check the other location for surplus first (see Transfer Gate).
• Cap every order by shelf life (2-year lyophilized default) and by the COA economic-batch floor (see Economic Check). A batch whose COA floor exceeds what sells within shelf life is COA_UNECONOMIC — do not auto-order it.
• Separate customer demand from staff/internal transfers, gifts, samples, stock transfers, returns, losses, and supplier short/substitution corrections (see reason codes in config).
• Suspended/inactive SKUs are not recommended unless Pafi asks to seed a first delivery/reactivation.
• Every recommendation must state the data it rests on: the demand window, the restock sample size behind the lead time, and any assumption flags. No false precision.

Required Source Hierarchy

1. v_stock_by_location — current per-location stock (drives the transfer gate).
2. skus — identity, active flag, Bucharest stock basis, sell price, cost fields.
3. orders + order_items — customer demand velocity (SALE only). If an order/line carries a stack_tag/protocol_id, record whether the component sale was standalone or in-stack; this makes future stack demand measurable without double-counting.
4. restocks + restock_items — incoming/open stock, and lead-time evidence (paid_date → received_date).
5. stock_movements — exclusions, transfer context, sanity checks only.

No stacks/protocols/bundles/subscriptions table exists (verified). Stack composition and per-patient shape live in project artifacts, used only for the optional forward scenario, never as live demand. If sources conflict, report it and use the newest explicit Pafi decision or audited correction; never silently merge contradictory figures.

Demand Model

Layer 1 — empirical velocity (DEFAULT, the only live basis):

base_daily_demand(SKU) = max(daily_demand_recent_30d, daily_demand_all_data)
                         # all-data window ~= full history; state the window in output
forecast_daily = base_daily_demand * demand_multiplier   # 1.0 normal, 1.25 high-velocity, 1.5 manual launch

Layer 2 — stack-BOM forward scenario (OPTIONAL, default OFF, assumption-flagged):

# Only when a human supplies expected_stack_uptake for a stack.
assumed_stack_daily(SKU) = Σ over supplied stacks containing SKU
                           ( expected_stack_uptake_daily * bom_qty_per_sale(SKU, stack) )
# Displayed additively but labeled ASSUMPTION_DRIVEN; NOT added to the default recommended qty.

Cannibalization guard: once stacks actually sell, their component draws appear in order history, so Layer-1 velocity already captures them. Do not add Layer-2 on top of measured stack sales — that double-orders. The standalone-vs-in-stack tag (source 3) is what lets Layer 2 graduate from assumption to measured later.

Demand Observability (3-state)

Classify every active SKU by whether its demand is observable, not just by its sales number:

• DEMAND_KNOWN — in stock (current_stock > 0) with recent sales. Velocity is trustworthy; plan normally.
• DEMAND_KNOWN_LOW — in stock but little/no recent sales. This is genuine low demand; do not over-order (can still be NO_DEMAND for reorder purposes).
• DEMAND_UNKNOWN_CENSORED — out of stock now (current_stock <= 0 AND incoming <= 0). Demand is unobservable regardless of sales. This SKU bypasses the normal reorder/economic-batch/shelf-life machinery (a censored forecast ≈ 0 would wrongly zero any order) and is routed to PARKED (held, no action) until restocked. censored_with_signal = it had prior sales despite being out of stock (stronger evidence of latent demand).

Parked, not reordered. A stocked-out SKU (no stock, no incoming) is emitted as PARKED in a separate parked_skus section — never in the main purchase plan and never auto-ordered. Each parked line shows whether a restock is already incoming in an open PO (restock_scheduled, with the incoming qty) or none is scheduled. Parked SKUs un-park automatically when stock is received (a restock arriving flips them back to the normal reorder path). No discovery probe is proposed — the business restocks via its own POs, not speculative buys.

Stock-state history (down-payment). When --stock-state-log <path> is set, each run appends per-SKU {date, sku_id, code, current_stock, incoming} to a local JSONL. No estimator consumes it yet; over ~4-6 weeks it makes days-in-stock (and therefore availability-adjusted true demand) computable with no upstream schema change. Run it on the weekly cadence so the history accrues.

Reorder Logic

review_days      = 7
min_stock_floor  = 5
planning_lead_days = max(observed_receipt_lt + coa_readiness_lag_days,
                         supplier_quoted_lead_days,
                         lead_time_hard_floor_days)     # conservative single number
coverage_days    = planning_lead_days + review_days + safety_cover_days
minimum_stock    = max(min_stock_floor, ceil(forecast_daily * safety_cover_days))
reorder_point(s) = ceil(forecast_daily * (planning_lead_days + review_days) + minimum_stock)
target_stock(S)  = ceil(forecast_daily * coverage_days + minimum_stock)
available_pipeline = current_sellable_stock + open_incoming_qty
recommended_order_qty = max(0, S - available_pipeline)   # order only when pipeline <= s, up to S

Lead time is a single conservative number, not a fitted distribution — the restock sample is tiny (measured receipt ~5 days, n=4, plus one order stuck 31+ days). Take the max of measured-receipt-plus-COA-lag, the supplier's quoted lead time, and a hard floor set from the worst observed delay. Always stamp the recommendation with the restock sample size it used. received_date is physical receipt, not sellable-ready; coa_readiness_lag_days covers COA/testing before sale.

Config-Driven Tiers, COA, Stacks & Budget

Pass --sku-config <path> (sample: references/sku-config.sample.json). It carries per-SKU {market_priority_tier, personal_fraction, coa_cost_usd} plus supplier_moq, coa_group_rate, tier_floors, unproven_floor_cap, budget_cap_usd, and stacks. Unknown SKU/stack codes surface in config_warnings (loud, non-fatal).

• Market priority ≠ own sales. market_priority_tier (HIGH/MED/LOW) is a HUMAN-set input from EXTERNAL EU market research, reviewed quarterly — never derived in code (at ~0.3 customer-orders/SKU/month there is no reliable per-SKU signal). It sets the min-stock floor only.
• Floor is a floor, never a ceiling. minimum_stock = max(applied_floor, ceil(forecast_daily * safety_cover_days)), and reorder_point adds velocity lead-demand on top. So a high-velocity SKU (e.g. a personal-use-heavy one) is ALWAYS reordered on its velocity — the tier can only raise the floor, never starve replenishment.
• Unproven cap. A SKU with no real sell-through (velocity_90d ≤ unproven_velocity_threshold_90d) has its floor capped at unproven_floor_cap (default 3) regardless of tier — HIGH tier expresses growth intent, not a speculative deep buy on unproven demand.
• Personal-use split (advisory). replenishment_velocity (full outflow) drives the reorder quantity — you replace everything that leaves, personal or not. market_velocity = outflow × (1 − personal_fraction) is shown as an advisory signal only, never changes the order. personal_fraction ≥ 0.5 flags PERSONAL_USE_SKEW. personal_fraction is a manual per-SKU value — it cannot be inferred (orders are not tagged personal-vs-customer).
• Per-SKU COA cost. effective_coa_usd = coa_cost_usd × coa_group_rate feeds the MOQ_QA economic-batch floor per SKU (economic_batch = max(MOQ_QA_floor, supplier_moq); supplier_moq comes from config/CLI — 10 in the operating config). High-tier COA SKUs ($450 raw) get a real minimum batch; tiny lots trip COA_UNECONOMIC.
• Joint stack floors. For each stack (GLOW = GHK-Cu+BPC-157+TB-500; KLOW = +KPV), if any component is below its floor, ALL components are lifted to their full tier floor (STACK_PARTNER_RESTOCK) so a blend is never left half-out-of-stock.
• Boxes. Orders round up to whole boxes (round_to_pack_size; 10 vials/box = supplier MOQ in the operating config, script default 1). Output carries order_boxes alongside vials.
• Budget ceiling. --budget-cap-usd (or config): if aggregate suggested spend exceeds it, growth-driven excess is trimmed first (largest first), never below each SKU's velocity-driven need; emits a budget block + BUDGET_EXCEEDED.

Economic Check

coa_per_vial       = coa_fixed_usd_per_sku / order_qty        # allocate fixed COA over the batch
MOQ_QA_floor       = ceil(coa_fixed_usd_per_sku / (coa_cap_fraction * unit_gross_margin_eur))
economic_batch     = max(MOQ_QA_floor, supplier_moq)
shelf_life_cap     = floor(forecast_daily * shelf_life_days * shelf_usage_cap)   # 2yr default
final_order_qty    = 0 if recommended_order_qty == 0
                     else min(max(recommended_order_qty, economic_batch), shelf_life_cap)
fully_loaded_unit_cost = cost_total_usd + coa_per_vial

If shelf_life_cap < MOQ_QA_floor, the SKU cannot recoup its COA within shelf life → label COA_UNECONOMIC, do not auto-order (transfer-source or discontinue candidate). If no FX rate is provided, keep USD cost and EUR sell price separate and flag FX_REQUIRED_FOR_MARGIN. Missing unit_gross_margin/supplier_moq/shelf_life_days degrade the affected cap to DATA_GAP, never to zero.

Transfer Gate

Before any supplier-order recommendation, check v_stock_by_location: if another location holds surplus above its own reorder point, emit TRANSFER_FIRST with transfer_qty = min(surplus, shortfall) and net it against the PO. An intra-EU transfer of an already-tested lot avoids a fresh COA and the full lead-time pipeline. Transfers are recommendations only — never a stock-movement write.

Recommendation Log

Every run appends a line per recommended SKU to the recommendation log (date, SKU, recommended_qty; human-ordered_qty backfilled later). This log is the feedback loop: it validates whether recommendations are trusted, and it is the objective trigger for graduating deferred features (e.g. turn Layer 2 on only after real stack sales appear and the log shows Layer 1 under-recommending stacked components). Never guess accuracy — read it from the log.

Owner Inputs (one-time)

Ask once and store in config: per top-mover, the cost of a stockout vs the cost of an expiry write-off; and the supplier's quoted lead time. At this data scale these two answers size safety stock better than any statistical formula.

Script

Prefer the deterministic helper:

python3 scripts/restock_planner.py --input snapshot.json --output report.json

Live read-only Supabase (credentials via environment only, never printed or stored):

RUO_SUPABASE_URL=... RUO_SUPABASE_SERVICE_ROLE_KEY=... \
python3 scripts/restock_planner.py --live --output report.json

Useful options: --supplier-quoted-lead-days, --coa-readiness-lag-days, --lead-time-hard-floor-days, --safety-cover-days, --review-days 7, --min-stock-floor 5, --coa-fixed-usd-per-sku, --coa-cap-fraction 0.10, --shelf-life-days 730, --shelf-usage-cap 0.8, --supplier-moq, --fx-usd-eur, --stack-scenario-file, --log-file, --include-inactive, --plan-zero-demand, --trigger-order-id <uuid>.

Implementation note: the deterministic helper currently segregates customer demand from non-sale outflows via text/notes heuristics (staff/internal, gift/sample, return, stock transfer). The canonical reason-code enum (SALE|TRANSFER|SAMPLE|GIFT|LOSS|RETURN|SUBSTITUTION|TEST_CONSUMED), the TEST_CONSUMED-deducts-usable-stock rule, and the standalone-vs-in-stack stack_tag attribution are the TARGET Supabase schema (owned by Narcis) and are not yet enforced in the script — until those fields exist, treat reason-code/stack-tag handling as advisory and rely on the heuristic exclusion. The stack-BOM scenario is available only via --stack-scenario-file and is always ASSUMPTION_DRIVEN.

Output Contract

Return:

• top reorder recommendations by priority, each with final_order_qty and the binding constraint that set it
• per-SKU: current stock + stock-by-location, incoming qty, demand window used, reorder point (s), target stock (S), days-of-supply
• lead-time assumption used (value + restock sample size) and any SS_HEURISTIC flag
• economic-batch detail (COA per vial, MOQ_QA floor, shelf-life cap, binding constraint)
• transfer recommendations (TRANSFER_FIRST + qty) checked before any PO
• cost/COA warnings and margin flags; excluded SKUs with reasons
• any Layer-2 figures clearly ASSUMPTION_DRIVEN and separated from the default recommendation
• source timestamp, query scope, write status (READ_ONLY)

Also return a top-level parked_skus list (out-of-stock SKUs + incoming-PO status), and each recommendation carries demand_state + censored_with_signal. Statuses: ORDER_NOW, TRANSFER_FIRST, WATCH, PARKED, OK, COA_UNECONOMIC, DATA_GAP, NO_DEMAND, ASSUMPTION_DRIVEN. (NO_DEMAND means only in-stock with no sales; out-of-stock SKUs are PARKED (held until restocked), never NO_DEMAND.) Use PASS_WITH_EXCEPTIONS when recommendations are usable but some SKUs lack cost, COA, FX, supplier price, shelf life, or open-restock line data. Use BLOCKED only when source data is too inconsistent to compute a sane proposal. Open restocks with no structured restock_items must be surfaced as exceptions, not silently treated as zero incoming.

Verification

Before emitting, confirm: (1) every ORDER_NOW passed the transfer gate; (2) final_order_qty ≥ economic_batch, or 0, or COA_UNECONOMIC; (3) no per-patient monthly_vials/cycle_vials used as an aggregate rate; (4) demand basis is empirical velocity unless a human supplied a stack assumption, which is flagged ASSUMPTION_DRIVEN and excluded from the default recommendation; (5) every excluded outflow carries a reason code; (6) each recommendation states its demand window and restock sample size; (7) write status is READ_ONLY.

Deferred (not in this version)

EOQ, multi-component statistical safety stock, lead-time distribution decomposition, and intermittent-demand forecasting (ADI/CV² classification, TSB) are deliberately excluded until there are ≥15 restocks with timestamps and real stack sales. Adding them now would fit models to a handful of data points and lend false authority to a purchasing decision. The recommendation log + timestamps are what unlock them later.

When to Use vs Alternatives

• ruo-restock-planner: weekly reorder planning, customer-order-triggered replenishment checks, demand/min-stock planning, COA-aware restock sizing, transfer-vs-buy decisions.
• ruo-supabase-guardrails: use together for technically read-only Supabase access and Hermes-owned mutation handoffs; this planner never executes a mutation.
• ruo-financial-audit-controller: use together when the result changes cost basis, supplier payments, COA allocation, treasury, or published audit numbers.