RBD Reorder Tool — Plan

Primary Users: Sabrina (buyer), Matt (approver)
Target: reorder.rileyblakedesigns.com
Deadline: Before May 1st (next reorder run)

Problem

Sabrina runs a bi-monthly (~1st and 15th) reorder process that currently involves:

• Manually running 4-5 NetSuite saved searches and pasting results into a massive Excel workbook (RB WOS.xlsx, 86+ columns)
• Filling formulas down hundreds of rows
• Reviewing ~2,000+ SKUs one Reorder Group at a time
• Manually adjusting daily sales figures to exclude outlier orders
• Building Excel POs from a template, then importing to NetSuite via CSV
• Emailing reorder lists to mills and distribution contacts

This process is error-prone, slow, and entirely dependent on one person's institutional knowledge.

Goal

A web app at reorder.rileyblakedesigns.com that:

1. Pulls all data from rb_warehouse MySQL (no more export/paste)
2. Calculates WOS, daily sales, and reorder quantities automatically
3. Gives Sabrina a clean review UI with SKU drill-down and outlier exclusion
4. Flags lifecycle change candidates
5. Generates Excel POs and NetSuite CSV import files
6. Emails Matt for approval

Phase 1 Scope

• In: WOS reorder workflow only
• Out: Preorder Reorder (Phase 2), Kelly's List (legacy — skip)
• Out: Full NetSuite write-back (Phase 2 — lifecycle exports for manual import for now)

Data Assessment (confirmed via DB audit 2026-04-22)

What we have in rb_warehouse MySQL

netsuite_items — Item master, fully usable:

• SKU (rbd_item_id), name (display_name), class, vendor, reorderable, lifecycle
• rbd_moq, vendor_moq, unit_type, last_purchase_price, cost
• anticipated_release_date, last_call_date, discontinued_date
• All inventory quantities: quantity_on_hand, quantity_committed, quantity_available, quantity_backordered, quantity_on_order
• rbd_print_type, color, category, theme, pattern, artist_1

netsuite_transaction_items — Sales history:

• item_sku, quantity, rate, ns_transaction_id
• rbd_trend_exclude already exists — this is the exact flag Sabrina uses to exclude orders from daily sales calc. Already wired in NS.
• is_dropship, special_order, item_type

netsuite_transactions — Transaction header:

• type (Sales Order, Invoice, Purchase Order, etc.)
• tran_date, ship_date, entity_name (customer)

netsuite_inventory_snapshots — Historical by location (for trend data)

What we need to add/clarify

• Reorder Group — derived from class + lifecycle mapping (need to confirm logic with Sabrina, likely: Batik classes → "Batik", Cotton Solid → "Solids & Basics", Designer/Licensed + lifecycle = Eternal → "D/L/Other - Eternal", etc.)
• Safety Stock — need to confirm if stored in NS or calculated
• RP (Reorder Point) / PSL — need to check if these are NS fields or calculated
• Vendor contact emails — for PO distribution (Korea mills, Riley Cloward, Jessica Taylor)
• Lead time by vendor — determines weeks before reorder threshold

Architecture

reorder.rileyblakedesigns.com
        │
        ▼
   Nginx (rb.alpineanalytica.com)
        │
   ┌────┴────────┐
   │  FastAPI    │  ← Python backend
   │  (port 800X)│
   └────┬────────┘
        │
   ┌────┴──────────────────────┐
   │  rb_warehouse MySQL       │
   │  (read: netsuite_*)       │
   │  (write: reorder_* tables)│
   └───────────────────────────┘
        │
   ┌────┴────────┐
   │  React UI  │  ← served as static build via Nginx
   └────────────┘

Backend: Python FastAPI
Frontend: React + Tailwind CSS
Auth: Simple JWT (Sabrina, Matt, Cole — 3 users to start)
Hosting: rb.alpineanalytica.com, new Nginx server block
Subdomain: reorder.rileyblakedesigns.com → coordinate with RBD IT contact (same person who set up stash tool subdomain)

Database — New App Tables

-- A reorder run (bi-monthly session)
CREATE TABLE reorder_runs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  run_date DATE NOT NULL,
  status ENUM('draft','in_review','pending_approval','approved','pos_generated') DEFAULT 'draft',
  created_by VARCHAR(100),
  submitted_to_matt_at TIMESTAMP NULL,
  approved_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Per-SKU decisions within a run
CREATE TABLE reorder_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  run_id INT NOT NULL,
  sku VARCHAR(100) NOT NULL,
  daily_sales_calculated DECIMAL(10,4),  -- system-calculated
  daily_sales_override DECIMAL(10,4),    -- Sabrina's manual override (null = use calculated)
  suggested_qty INT,                      -- system suggestion
  adjusted_qty INT,                       -- Sabrina's adjusted quantity (null = use suggested)
  notes TEXT,
  lifecycle_flag ENUM('none','mark_yes_star','mark_discontinued','mark_special_order') DEFAULT 'none',
  lifecycle_flag_notes TEXT,
  included BOOLEAN DEFAULT TRUE,          -- false = excluded from this run
  status ENUM('pending','reviewed','approved','rejected') DEFAULT 'pending',
  reviewed_by VARCHAR(100),
  reviewed_at TIMESTAMP NULL,
  FOREIGN KEY (run_id) REFERENCES reorder_runs(id)
);

-- Generated POs
CREATE TABLE reorder_pos (
  id INT PRIMARY KEY AUTO_INCREMENT,
  run_id INT NOT NULL,
  po_number VARCHAR(100),
  vendor VARCHAR(255),
  ship_date DATE,
  expected_receipt_date DATE,
  location VARCHAR(255) DEFAULT 'Sandy Warehouse',
  status ENUM('draft','sent_to_matt','approved','rejected','imported_to_ns') DEFAULT 'draft',
  items JSON,  -- array of {sku, qty, put_up, unit_type, rate, rot, ...}
  FOREIGN KEY (run_id) REFERENCES reorder_runs(id)
);

Core Calculations (replicating WOS spreadsheet logic)

Daily Sales (per SKU)

base_window = 365 days of sales orders (type = 'Sales Order')
excluding: rbd_trend_exclude = 1, is_dropship = 1 (configurable)

daily_sales_365 = SUM(quantity WHERE tran_date > today-365) / 365
daily_sales_180 = SUM(quantity WHERE tran_date > today-180) / 180
daily_sales_90  = SUM(quantity WHERE tran_date > today-90)  / 90
daily_sales_30  = SUM(quantity WHERE tran_date > today-30)  / 30

daily_sales = MAX(daily_sales_365, daily_sales_180)  -- conservative (uses highest recent rate)
# Sabrina can override this per SKU in UI

WOS (Weeks of Supply)

wos_available = quantity_available / (daily_sales * 7)
wos_oh        = quantity_on_hand / (daily_sales * 7)
wos_oh_oo     = (quantity_on_hand + quantity_on_order) / (daily_sales * 7)

Reorder Trigger

reorder_flag = wos_available < safety_stock_weeks  (or wos_oh_oo < threshold)

QTY Needed

qty_needed = (daily_sales * lead_time_days) - quantity_available - quantity_on_order
calc_order_qty = ceil(qty_needed / put_up) * put_up  -- round up to put-up size
calc_order_qty = max(calc_order_qty, rbd_moq)         -- respect MOQ

UI Flow

1. Dashboard

• Active run status + start new run button
• Past runs with links to view/download POs
• Quick stats: SKUs flagged for reorder, pending Matt approval

2. Review Screen (main workhorse)

• Table grouped by Reorder Group (collapsible sections)
• Columns: SKU | Name | Class | Qty Avail | WOS | Daily Sales | Suggested Qty | Adjusted Qty | Notes | Lifecycle Flag | Include
• Filters: Reorder Group, Class, Show only flagged/not-reviewed
• Row click → SKU Detail Modal:
• Recent 90 days of sales orders (date, customer, qty, dropship flag)
• Outlier detection: highlight orders >3x average daily qty with "exclude?" toggle
• rbd_trend_exclude toggle per transaction item (writes to reorder_items override for this run; separate flow for actual NS update)
• Daily sales breakdown: 365d / 180d / 90d / 30d averages
• Override daily sales field
• Lifecycle flag dropdown + notes

3. PO Builder

• After review: group approved SKUs by vendor
• Show pricing from Lookups logic (base rate + surcharges by mill/put-up)
• Edit PO details: ship date, location, put-up size
• Generate Excel PO (Reorder Template format) + NetSuite CSV import file

4. Submit to Matt

• Summary email: HTML table of all suggested POs with SKU details
• Excel PO attachments
• Matt replies / approves in future web UI (Phase 1.5: add simple approval link in email)

5. Lifecycle Changes Tab

• All SKUs flagged during review
• Export as CSV (columns: Internal ID, Reorderable) for NS import

Pricing Logic (from Lookups tab)

Base rate by Mill + Reorder type:

• Y&K Together: RD=1.80, RB=1.75, Solid=1.60, Digital=2.10
• TaiHan: RD=1.80, RB=1.80, Solid=1.60, Digital=2.10
• Daechun: RD=1.90, RB=1.83, Solid=1.65

Surcharges (by mill):

• Panel Surcharge: Y&K=0.30, TaiHan=0.00, Daechun=0.20
• Sparkle: Y&K=0.30, TaiHan=0.30, Daechun=varies
• Glow in Dark: Y&K=0.30, Daechun=0.30

These map to the Lookups tab in Reorder Template.xlsx.

Build Sequence

Pre-build (this week)

• ☐ Confirm Reorder Group derivation logic with Sabrina (30 min call or email)
• ☐ Confirm Safety Stock source (NS field or manual?)
• ☐ Get vendor contact emails for PO distribution
• ☐ Contact RBD IT for subdomain setup (can start now, doesn't block dev)

Week 1 (Apr 22-28)

• ☐ Set up project: ~/ai-projects-local/rbd-reorder-tool/ (FastAPI + React monorepo)
• ☐ DB connection + read-only queries for items + transactions
• ☐ Calculation engine: daily sales, WOS, reorder qty, outlier detection
• ☐ New reorder_* tables migration (run on rb.alpineanalytica.com)
• ☐ FastAPI endpoints: GET /items, GET /runs, POST /runs, PATCH /items/:id
• ☐ React skeleton: auth, dashboard, basic review table

Week 2 (Apr 28 - May 1)

• ☐ SKU detail modal + outlier detection + trend exclude toggle
• ☐ Lifecycle flag UI
• ☐ PO generation (openpyxl → Excel, CSV for NS import)
• ☐ Email to Matt (sendgrid or SMTP)
• ☐ Deploy to rb.alpineanalytica.com
• ☐ Nginx config + subdomain
• ☐ Walk Sabrina through the tool before her May run

Open Questions

1. Reorder Group logic — How exactly does the spreadsheet derive Reorder Group from class + lifecycle? Need to confirm mapping (e.g., "D/L/Other - Past Lifecycle" vs "D/L/Other - Eternal" — is this class = Cotton Designer/Licensed + lifecycle != Eternal?)
2. Safety Stock — Is this a NetSuite field (not currently synced) or does Sabrina maintain it manually per class?
3. RP and PSL — What are these fields? Reorder Point and... Pick/Ship Level? Are they in NS?
4. Lead time by vendor — Each vendor has different production lead times. Where is this maintained?
5. Matt's email — What's the right email address?

Execution Log

Next Steps

Deploy

1. Set up .env on rb.alpineanalytica.com — copy .env.example, fill in SECRET_KEY and SMTP
2. Run DB migration — mysql -u laravel -p$(grep DB_PASSWORD /var/www/html/warehouseApi/.env | cut -d= -f2) rb_warehouse < backend/migrations/001_create_reorder_tables.sql
3. Create first users — Sabrina (buyer), Cole (admin), Matt (approver) via POST /admin/users
4. Set up systemd service for the FastAPI process on rb server
5. Nginx config — new server block for reorder.rileyblakedesigns.com
6. DNS — request subdomain from RBD IT contact

Pre-first-run verification (before Sabrina's May 1 run)

7. Verify entity exclusion names match NS exactly — run this on the server: sql SELECT DISTINCT entity_name FROM netsuite_transactions WHERE entity_name LIKE '%Bonaventure%' OR entity_name LIKE '%Zulily%' OR entity_name LIKE '%Marketing%' OR entity_name LIKE '%RBD Kits%' OR entity_name LIKE '%Lehi%' OR entity_name LIKE '%Jiangsu%' OR entity_name LIKE '%Test%' OR entity_name LIKE '%Ricrac%' ORDER BY entity_name; Compare against EXCLUDED_ENTITY_NAMES in calculations.py. Fix any spelling mismatches before first run.

8. Review safety stock days with Sabrina — confirm the title-derived values are correct: - Solids & Basics: 150d - D/L/Other / Designer / Liberty / Notions: 120d - Batiks: 180d - Other Vendor: 60d All adjustable in Admin → Class Config without a redeploy.

9. Walk Sabrina through the tool before May 1st run