← Back to all projects
Ready

RBD Design & Release Planning Tool

Context

Riley Blake's design team plans 5 releases/year (soon 6) across cotton, batik, and licensed substrates. Today that planning lives in two disconnected places:

  • Asana — the production skeleton. Year-based MASTER RELEASE SCHEDULE boards (2024→2028) plus a separate Batiks MASTER RELEASE SCHEDULE. Sections are Release + Month (R1 - SEPTEMBER 2027). Each collection is a top-level task grouped under all-caps header tasks (COTTONS, BATIKS, LICENSED, LIBERTY). Only Status is a structured field (enum); designer, collection name, and SKU count are crammed into the free-text task name. No dollars anywhere. Batiks are multi-homed (same task ID in two boards).

  • Google Sheet "2026/27 Forward Plan" — the money layer. A 46-column-wide, month-by-month grid of Presales | Actual | Collection | SKUs | Status. Projections are round gut-guesses that miss badly both ways (Evening on the Prairie projected $350K → actual $899K); actuals are hand-typed; designer is buried in a text blob so nothing is computable; no learning loop.

These two halves have never been joined. This tool marries them: pull the Asana skeleton, attach a warehouse-derived projected $ and a live actual $ (from rb_warehouse/NetSuite), and present it as a fast, draggable Release/Month pill-board with rollups and a data-driven forecaster. It replaces the Google Sheet (seeded once from its current values).

Cole is the primary user (revenue-planning cockpit). Deploys at rb.alpineanalytica.com/design/ like the other tools. v1 scope = full board + forecaster.

Architecture

  • Stack: FastAPI + React/Vite/TypeScript/Tailwind — cloned from the rbd-reorder-tool patterns.
  • Deploy: rb.alpineanalytica.com/design/, backend on port 8002, systemd + Apache, JWT auth.
  • Three data sources, joined: 1. Asana (read-only pull) → the skeleton: which collection, designer, release/month, category, status. 2. rb_warehouse (read-only on netsuite_*) → actual presales + designer history for projections. 3. App DB (design_* MySQL tables) → plan state: drag positions, overrides, crosswalk, targets, forecast pills.

Data model — new design_* tables (MySQL, same DB as reorder tool)

  • design_designers — name, tier(A/B/C/Licensed). Seeded from designers.db.

  • design_pills — the collection cards: id, asana_task_gid (nullable), designer_id, collection_name, category(cotton|batik|licensed|liberty), release_code (R{1-5}-{YY}), month, year, status, sku_count, sku_source(asana|sheet|manual), theme, projected_presale, projected_source(auto|manual), rbd_collection_key (crosswalk FK, nullable), is_placeholder, is_forecast, notes, updated_at.

  • design_crosswalk — collection_name+designer → netsuite_items.rbd_collection, confidence, confirmed.

  • design_actuals_cache — per rbd_collection + release month: presold, shipped, backordered, still_open, refreshed_at. Refreshed from the warehouse on a schedule.

  • design_targets — scope(month|release|year), key, target_amount.

  • design_month_context — per month(1-12) (+ optional year): themes (e.g. patriotic, halloween, christmas), notes, historical_top_collections. Seeds the board's month headers with theme bands + "what historically sold this month" context. Sourced from the Designer Rank & Release workbook (History by Month + Theme tabs / their local HTML exports).

  • design_users — JWT auth (mirror reorder_users).

Phases

Phase 0 — Setup

  • Stash the Asana PAT: add ASANA_PAT to the tool's server .env (and encrypted secrets for local). Rotate the currently-pasted token afterward (it's in the chat transcript).

  • Scaffold ~/ai-projects-local/rbd-design-planning/ (backend/, frontend/) from the reorder-tool template: base: '/design/', VITE_API_BASE=/design-api, BrowserRouter basename="/design", port 8002. git init; add .claude/ to .gitignore.

Phase 1 — Data spine & seed

  • Create design_* tables.
  • Seed design_designers (+ tiers) and historical cadence from rbd-designer-scheduling/database/designers.db.

  • One-time import of the "2026/27 Forward Plan" sheet → seed design_pills (projected_presale, sku_count, status, theme, category, month). This is the "replace the sheet" migration.

  • Seed design_month_context by mining the Designer Rank & Release workbook's History by Month + Theme tabs (local HTML exports at ~/ai-projects/Riley-Blake-Designs/Design/Designer Rank & Release Schedule/) → per-month theme bands + historical top-collection context shown in the board's month headers.

Phase 2 — Asana pull (read-only, services/asana_client.py)

  • Pull year MASTER RELEASE SCHEDULE boards (cotton + licensed) + Reba's Batiks MASTER RELEASE SCHEDULE.

  • Dedup batiks by task gid (multi-homed). Section → release/month; parent header task → category; parse task name → designer + collection + optional (NN SKUs); map the Status enum straight through. Ignore subtasks, assignees, due dates (production noise Cole doesn't want).

  • Upsert into design_pills keyed by asana_task_gid. Scheduled refresh (e.g. daily) + on-demand button.

Phase 3 — Warehouse money engine (services/warehouse.py)

  • Vetted presale query (lifted from rep-sales-reconciliation/reconcile_rep.py), GROUP BY rbd_collection: type='SalesOrd', tran_date < release_start, anticipated_release_date in the release window, expense_account_name LIKE '4%' AND NOT LIKE '40600%' (AR-only, exclude tariff surcharge), exclude Sales Order : Cancelled/Pending Approval + excluded entities; prorate net_amount by quantity_ship_recv/quantity into presold/shipped/backordered/still_open. Surface the phantom-line caveat.

  • Crosswalk builder: fuzzy-match collection_name+designer (and anticipated_release_date month) against netsuite_items.rbd_collection / artist_1. Auto-map high-confidence; UI to confirm the ambiguous few.

  • Projection engine: compute each designer's trailing average and $/SKU from warehouse history → auto-fill projected_presale (projected_source=auto); manual override always available.

Phase 4 — Board UI (React)

  • Draggable pills via @dnd-kit (not present in reorder tool — new dep). Columns = Release/Month; swimlanes = cotton / batik / licensed. Drag a pill to another month/release → optimistic update → persist.

  • Snappy nav: month slider/scrubber to jump; zoom control (1 / 3 / 6 / 12 months visible). All data preloaded, client-side state (react-query cache) so drags and jumps are instant.

  • Rollups: per-month, per-release, per-year totals + SKU counts vs design_targets; projected-vs-actual coloring on each pill. Pill detail drawer to edit $/SKU/theme/status/notes.

Phase 5 — Forecaster (in v1)

  • Port suggest_releases.py logic (historical-month match + active-but-missing-year) → "who's due" ghost-pills with an auto-projected $. Designer profile cards (cadence, trailing avg, typical themes) from designer_patterns.

  • Fill-a-month-to-target: given a release's gap to target, propose a set of due designers/placeholders that closes it. Accept → materializes a is_forecast pill.

Phase 6 — Deploy

  • systemd rbd-design-api (port 8002) + Apache Alias /design/ & ProxyPass /design-api/ in financials-le-ssl.conf; rsync backend/frontend; seed design_users. Same manual procedure as reorder tool (NOT deploy.sh).

Reuse (don't reinvent)

  • Template: rbd-reorder-tool/backend/{main,config,database,models,routers,services}.py, frontend/src/lib/api.ts, deploy/rbd-reorder-api.service, Apache financials-le-ssl.conf stanzas. DB connection = SQLAlchemy+pymysql to 127.0.0.1:3306 rb_warehouse user laravel (tunnel/local, no SSH code).

  • Brain: rbd-designer-scheduling/database/designers.db (designers/tiers/collections + designer_patterns view), scripts/suggest_releases.py, scripts/analyze_gaps.py. Theme taxonomy (9 values) → reuse as enum.

  • Warehouse: rep-sales-reconciliation/reconcile_rep.py (vetted presale waterfall + gotchas), rbd-reorder-tool/backend/services/cotton_calc.py::populate_collection_items (collection-level aggregation by rbd_collection).

  • Data-integrity discipline (memories): AR double-count filter, refund-sign handling, tariff 40600 exclusion, unapproved payments, item_type filter, reconcile to P&L. Actuals must tie out or the tool loses trust.

Verification

  • Parser: unit tests on Asana task-name → (designer, collection, SKUs) across the messy real examples; assert batik dedup (a multi-homed gid appears once).

  • Actuals tie-out: pick 5 known collections; confirm the tool's actual presale $ matches reconcile_rep.py output and the sheet's Actual column / Tableau within tolerance. If it doesn't reconcile, stop.

  • Projection sanity: spot-check a designer's auto-projection against their last few actuals.

  • UI: drag persists across reload; slider + zoom work; month/release/year rollups equal the sum of their pills; projected-vs-actual coloring correct; forecaster ghost-pills carry a projected $ and close a target gap.

  • Deploy smoke: /design/ loads behind auth; /design-api/health OK; Asana refresh button repopulates.

Notes

  • Copied to ~/ai-projects/mission-control/plans/rbd-design-planning-tool.md per the mission-control system.
  • /grill-me gate still pending after the scope decision below is settled.

Revisions after Opus plan-review (ADOPTED)

Correctness (must-fix before actuals code):

  • Separate app database. App state lives in its own rb_design MySQL database, NOT as design_* tables inside the shared rb_warehouse. Two connections: read-only engine → rb_warehouse (netsuite_*), read/write engine → rb_design. Eliminates blast-radius/backup/collision risk. Use Alembic for migrations (no create_all() on a shared server). Creating rb_design on the prod MySQL is a state-mutating prod action → needs Cole's + Brett's approval; until then dev runs against a LOCAL MySQL/SQLite for app state.

  • Crosswalk: never confuse "unmapped" with "$0". Add crosswalk_status on each pill (unmapped | auto_high | auto_low | confirmed | conflict). Only confirmed mappings feed actuals. Unmapped/unreleased pills show "–", never $0. Hard ±2-month anticipated_release_date window filter BEFORE fuzzy scoring. Unit test: same designer, same collection name, different year must NOT cross-map.

  • Actuals grain (v0 tie-out finding). Aggregate by rbd_collection across ALL its SKUs, with the presale cutoff = the collection's own release date (min anticipated_release_date), NOT a fixed single-month window. Verified: Evening on the Prairie → $939,662 vs sheet Actual $898,955 (~5%, sheet is hand-typed) = tie-out. A rigid month window under-counted it to $705K by dropping off-month SKUs. THE metric = pre-sold at release (matches the sheet = locked-in demand), so it compares apples-to-apples against the projected pre-sales. Total-sold / invoiced is explicitly NOT shown by default (it folds in post-release at-once reorders and would break the projected-vs-actual comparison) — available only as an opt-in reference column. (Cole, confirmed.)

  • Tie-out deep dive (35 collections, 2025). Median abs diff vs the hand-typed sheet = 9%, and warehouse is systematically higher. Ruled OUT as causes (with data): item_type (all revenue lines are already InvtPart/NonInvtPart → $0 effect) and tariffs ($0 in the pre-sale window). Root cause = the sheet is a stale, inconsistently hand-captured snapshot — plotting sheet value vs the pre-sold accumulation curve (-90/-60/-30/rel) shows the sheet lands at a different point per collection. Conclusion: the sheet is NOT the accuracy yardstick — it IS the ±9% noise we're replacing. Validate the tool against NetSuite/Tableau (same source the warehouse mirrors) → expect ~0% error. Next gate: reconcile ONE collection to NetSuite to the penny. Also: rbd_collection groups the full family (main + coords + precuts + widebacks) — confirm that's the intended presale unit. And the crosswalk must exclude basics/notions/kits/continuity (Notion, Confetti Cotton, Bee Dots, Kit-of-the-Month) — evergreen SKUs that dwarf real releases and aren't design-planning items.

  • Phantom lines. v1 actuals = presold (booked) + shipped only. still_open/backordered are unreliable (no delete pass in the mirror) → excluded from headline numbers, shown only as a low-confidence detail. Never display a number we can't trust behind just a tooltip.

  • Projection engine (methodology fix). Layered fallback per pill: (1) manual override → (2) same-designer same-theme history → (3) designer trailing median (robust to the Christmas outlier) × tier factor → (4) category average. Separate licensed vs in-house. Show a confidence band (narrow/wide) from sample size + variance. No false-precision single-point numbers.

Customizability (Cole, v0 finding):

  • Editable designer roster + aliases. design_designers is warehouse-seeded (netsuite_items.artist_1) but user-extendable: add brand-new designers (no history yet), edit tier, and maintain an alias map (e.g. MME → My Mind's Eye) that the parser consults so abbreviations score as known. New/no-history designers project via a tier/category comp with a manual-override field — never a permanent "–".

Robustness:

  • Parser-first. Build the Asana task-name parser as a standalone tested function BEFORE backend wiring; test set: standard, TBC, Placeholder, emoji section prefix (🟧 R1- SEPTEMBER 2027 … LAST CALL), header task (excluded), no-SKU, batik multi-home dedup. Unparseable → pill flagged, never silently dropped.

  • Asana pull = idempotent background job, wrapped in a transaction (commit only after all boards fetched); 5-min cooldown on the manual refresh button; store + display refreshed_at.

  • Pill/Asana sync policy. position_overridden (drag beats Asana section on re-sync); asana_deleted soft-flag instead of hard delete; manual pill that later appears in Asana links by gid.

  • Actuals staleness. Board header shows "Actuals as of "; warn if >24h; nightly refresh (~2am, after the warehouse mirror).

  • Month-context seed from a version-controlled static JSON (or live via google-workspace Sheets MCP), NOT a fragile HTML-export parser.

  • Indexes on design_crosswalk(collection_name,designer) and design_actuals_cache(rbd_collection).

Migration safety:

  • Do NOT retire the Google Sheet at launch. Import as seed, then run tool + sheet in parallel 2–4 weeks; add a CSV export endpoint so the sheet can be regenerated from tool state. Retire only once actuals have tied out across a full release cycle.

  • Rotate the Asana PAT before writing code (it's in the transcript); store via encrypted secrets / a chmod 600 www-data-owned .env on the server.

Revised delivery sequence (DECISION NEEDED — see message)

The critic argues v1 (Asana + crosswalk + actuals + drag board + forecaster at once) bundles 5 risky systems. Recommended re-sequence — same end state, de-risked order:

  • v0 — verified data spine, no UI: rb_design schema + Asana parser (tested) + crosswalk (10 known collections) + actuals query tied out to reconcile_rep.py and the sheet's Actual column. If dollars don't reconcile, stop here. This is the gate that protects every number downstream.

  • v1 — read-only board: pills from seed + Asana, actuals on confirmed pills, rollups, auth, deploy.

  • v1.5 — drag (@dnd-kit) + manual overrides + targets.
  • v2 — forecaster (who's-due + fill-to-target), once actuals are validated over a real cycle.
Source: ~/ai-projects/mission-control/plans/rbd-design-planning-tool.md