← Back to all projects
Complete PREP (Mission Control executes phases, Cole reviews UAT gates) Inventory Velocity + Restock Risk Reporting Created 2026-03-31

Plan: Restock Report — Fabric Outlet + Shiplap

Objective

Build a restock report that surfaces SKUs at risk of selling out across Fabric Outlet and Shiplap, based on current inventory levels and sales velocity. Output to Google Sheets on a weekly schedule with risk tiers (Critical / Warning / Monitor).

Context

  • Shopify SQLite database: ~/ai-projects-local/shopify-bigquery-pipeline/data/shopify.db
  • Database has: orders, order_line_items, products, inventory_levels
  • Existing related scripts: lori_holt_restock_alert.py, inventory_velocity_analysis.py, spring_velocity_tracker_enhanced.py
  • Spring velocity tracker already does sell-through for Spring 2026 — restock report is broader (all active SKUs, not just seasonal)
  • Stores: Fabric Outlet + Shiplap (Sundance optional Phase 2)
  • End users: Mary + Deanna (buyers for FO/Shiplap) — not Sabrina (RBD buyer only)
  • Output: Weekly HTML email to Mary + Deanna (not Google Sheets)
  • Email sent via Gmail API using same OAuth credentials as payable schedule project
  • Schedule: weekly, Monday morning with the other Monday scripts

Key files to check first:

  • ~/ai-projects-local/mission-control/scripts/inventory_velocity_analysis.py — reuse logic
  • ~/ai-projects-local/mission-control/scripts/lori_holt_restock_alert.py — reuse patterns
  • ~/ai-projects-local/mission-control/docs/database-schema.md — exact table/column names
  • ~/ai-projects-local/mission-control/tasks/lessons.md — inventory data patterns

Phase 1: Data Audit + Velocity Baseline

DISPATCH tasks:

  1. Read database-schema.md to confirm exact table/column names for inventory and orders
  2. Query shopify.db to assess data completeness: ```sql -- Check inventory data freshness SELECT store, COUNT(*) as sku_count, MAX(updated_at) as last_updated FROM inventory_levels GROUP BY store;

-- Check order line items available for velocity SELECT store, COUNT(DISTINCT variant_id) as variants_with_sales, MIN(created_at) as earliest_order, MAX(created_at) as latest_order FROM order_line_items GROUP BY store; ```

  1. Read inventory_velocity_analysis.py to understand existing logic — document what it does and what we can reuse
  2. Calculate velocity baseline for FO + Shiplap: - Units sold per SKU over last 30, 60, 90 days - Average daily sell rate - Current inventory on hand - Estimated days-to-stockout = inventory / daily_sell_rate
  3. Write findings to reports/restock-data-audit-2026-03-31.md

Acceptance Criteria:

  • Inventory data freshness confirmed (<48h stale)
  • Velocity calculation working for at least 100 SKUs
  • Days-to-stockout estimate available per SKU
  • Known data gaps documented (e.g. SKUs with no recent sales = infinite days to stockout)

[STOP — Ready for UAT: Phase 1] Cole reviews: audit report. Are the inventory counts roughly what you'd expect? Does the velocity logic make sense for fabric (yardage vs discrete items)?

Phase 2: Risk Scoring + Tiering

DISPATCH tasks:

  1. Define risk tiers (confirm with Cole's existing thresholds or use these defaults): - CRITICAL: ≤14 days to stockout AND selling ≥1 unit/day - WARNING: 15–30 days to stockout AND selling ≥0.5 units/day - MONITOR: 31–60 days to stockout AND selling ≥0.25 units/day - OK: >60 days or slow-moving (<0.25 units/day) - DEAD STOCK: 0 sales in last 90 days AND inventory >0
  2. Write scripts/restock_report.py: - Accept --store fo|shiplap|all and --window 30|60|90 args - Calculate velocity for each window - Score and tier each SKU - Output columns: SKU | Product Title | Variant | Store | Inventory | 30d Sales | 60d Sales | Velocity (units/day) | Days to Stockout | Risk Tier | Last Sold Date
  3. Add: "Reorder Candidates" subset — CRITICAL + WARNING items filtered to only those with positive margin history
  4. Print summary: counts per tier per store

Acceptance Criteria:

  • Risk tiers populated for all active SKUs (inventory > 0)
  • CRITICAL tier has ≤50 SKUs (if more, thresholds may need adjusting)
  • Dead stock identified separately
  • Summary count prints cleanly

[STOP — Ready for UAT: Phase 2] Cole reviews: look at the CRITICAL and WARNING lists. Do you recognize these products as actually running low? Do any seem wrong (false positives)? Should the tier thresholds be adjusted?

Phase 3: Google Sheets Output

DISPATCH tasks:

  1. Write scripts/restock_sheet_writer.py: - Target sheet: "Restock Report — FO + Shiplap" (create if not exists) - Tabs: CRITICAL, WARNING, MONITOR, Dead Stock, All SKUs, Summary - Summary tab: counts per tier, total inventory value at risk, top 10 most urgent SKUs
  2. Formatting: - CRITICAL rows: red background - WARNING rows: yellow background - MONITOR rows: light orange - Dead stock: gray - Freeze header row, auto-resize columns
  3. Add "Last Updated" timestamp in cell A1 of Summary tab
  4. Add a "Reorder Qty Suggestion" column: max(0, (30_day_sales * 2) - inventory) — 2-month buffer
  5. Test: write full report for both stores, verify sheet is clean and readable

Acceptance Criteria:

  • Sheet exists with all 6 tabs
  • Color coding works
  • Reorder qty suggestion populated
  • Timestamps current
  • No formula errors

[STOP — Ready for UAT: Phase 3] Cole reviews: open the Google Sheet. Is it easy to read? Would you share this with your buying team right now? What needs changing?

Phase 4: Email Delivery + Automation

DISPATCH tasks:

  1. Write HTML email template for Mary + Deanna: - Subject: Weekly Restock Alert — X Critical, Y Warning (Week of [date]) - Section 1: CRITICAL SKUs (red) — full table: SKU, Product, Store, Inventory, Days Left, Suggested Reorder Qty - Section 2: WARNING SKUs (yellow) — same columns - Section 3: brief summary counts - Clean, readable on mobile
  2. Write scripts/restock_email_sender.py — uses Gmail API (same credentials as payable schedule) to send to Mary + Deanna's addresses - Note: if Gmail OAuth not yet set up, use SMTP with app password as fallback - Recipient addresses: add to secrets.env.enc as RESTOCK_EMAIL_MARY + RESTOCK_EMAIL_DEANNA
  3. Also send Slack alert to mission control channel: "Restock email sent — X Critical, Y Warning"
  4. Write LaunchAgent plist: com.missioncontrol.restockreport.plist - Schedule: Monday 8:45am (after database sync health check at 8:45am — adjust if conflict) - Wrapper: ~/bin/cron-restock-report.sh
  5. Write the cron wrapper (iCloud sandbox pattern)
  6. Load and test LaunchAgent
  7. Update integrations-status.md with new automation
  8. Document: docs/restock-report-guide.md

Acceptance Criteria:

  • Runs Monday morning without manual trigger
  • Slack alert fires with correct counts
  • Sheet updates automatically
  • Guide written

[STOP — Ready for UAT: Phase 4 — Final] Cole reviews: let it run automatically for 2 weeks. Are the alerts actionable? Is the data fresh and accurate? Any threshold tuning needed?

Execution Log

Phase 1 — COMPLETE (2026-03-31)

Findings:

  • Database: shopify.db — 6 tables, no inventory_levels table; inventory in variants.inventory_quantity
  • Table names confirmed: line_items (not order_line_items), variants (not separate inventory table)
  • FO: 2,265 in-stock variants; Shiplap: 3,537 in-stock variants
  • Velocity working for 1,096 FO SKUs and 1,491 Shiplap SKUs (30d window)
  • 15 CRITICAL FO SKUs — Lori Holt bundles mostly 0.8–12 days from stockout
  • 2 CRITICAL Shiplap SKUs — Confetti POP! subscription (13d), 3-Yard Mystery Bundle (2.9d)
  • Risk tier counts: FO (CRITICAL:15, WARNING:14, MONITOR:18, OK:771), Shiplap (CRITICAL:2, WARNING:32, MONITOR:87, OK:1,163)
  • Dead stock: FO 1,125 variants, Shiplap 1,188 variants
  • DATA FRESHNESS FLAG: Inventory 19 days stale (March 12). Must sync before go-live.
  • Sundance excluded: inventory data corrupt (-70.5M units)

Key schema notes for Phase 2:

  • Join on variant_id not just sku (some variants have NULL sku)
  • Negative inventory → treat as 0
  • line_items.store + orders.store join pattern confirmed
  • o.financial_status NOT IN ('refunded', 'voided') filter needed

Audit report: reports/restock-data-audit-2026-03-31.md

[STOP — Ready for UAT: Phase 1] — Cole reviews audit report

Phases 2–4 — COMPLETE (2026-05-12)

What was built:

  • restock_report.py — pulls live inventory + velocity from WarehouseAPI MySQL (FO + Shiplap). FO = all active SKUs, Shiplap = Restock-tagged SKUs only.
  • restock_email_template.py — FO renders flat Critical/Warning sections; Shiplap renders by Restock category. HTML + PDF via wkhtmltopdf.
  • restock_email_sender.py — sends FO PDF to Mary, Shiplap PDF to Deanna, combined to Cole via Gmail API.
  • ~/bin/cron-restock-report.sh — wrapper with dedupe guard.
  • com.missioncontrol.restockreport.plist — LaunchAgent loaded, fires Monday 8:45am.

First send (2026-05-12): 83 Critical, 160 Warning across both stores.

Known tuning items:

  • FO has ~65 CRITICAL SKUs — Mary to review and flag false positives so thresholds can be adjusted.
  • Slack alert not firing (SLACK_BOT_TOKEN not set for this script).
Source: ~/ai-projects/mission-control/plans/restock-report.md