PO Templates → Digital Migration (Cotton · Batik · Reorder)

Primary user: Sabrina (buyer) · Matt (approver) · Kelly (policy) Target app: RBD Ordering — https://rb.alpineanalytica.com/ordering/ (~/ai-projects-local/rbd-reorder-tool/) Source session: plans/vscode/session-5.md ("Cotton PO Apps Script + Reorder Formulas")

Purpose

Two months of buying-workflow work happened in three places — the Cotton PO Google Sheet, the Batik PO Google Sheet, and the Reorder logic (partly in the web app, partly in the Cotton sheet's Data tab). Much of it is template-specific plumbing (hardcoded column indices, #REF! guards, header-position lookups) that exists only because it lives in a spreadsheet. This doc:

1. Changelog — consolidates every change so nothing is lost when the sheets are retired.
2. Gap analysis — what the web app already has vs. what the sheets have moved ahead on.
3. Port plan — phased migration of all three into the RBD Ordering app, deleting the template-specific hacks as we go.

Migration thesis: ~40% of the Apps Script code is spreadsheet-coping (column shifts, INDIRECT ranges, stacked-header matching, override-column wipes). None of it survives the port — the web app reads rb_warehouse directly and stores state in Postgres/MySQL, so those classes of bug disappear rather than get reimplemented.

PART 1 — CHANGELOG (mid-May → 2026-07-13)

A. Cotton PO template — ~/ai-projects-local/cotton-po-scripts/rb-cotton-po/ (7 scripts, Sheet 16Gx-N9HK35MSLZfz5DAGJ6FhHN6u2VLdruwmiXIL4lE)

Locked import spec (source of truth for the port): plans/cotton-po-import-web-app-port.md + session-5.md GOAL section. SKU classification order, surcharges ($0.15/15yd, $0.20/10yd), 3-PO location rules all documented there.

B. Batik PO template — ~/ai-projects-local/batik-po-scripts/batiks/ (6 scripts, separate sheet)

Open blocker (Batik): KTBT- kit SKUs are not in the Data tab yet — kits column reads NO for all, import finds no kits. Data-source gap, not a code bug. Also deferred: Claude-based PO# auto-naming from collection name.

C. Reorder logic — web app ~/ai-projects-local/rbd-reorder-tool/ (git history)

Reorder formula (current, per [[rbd-reorder-tool]]): qty = (daily_sales × 120) − qty_available − max(on_order − backordered, 0), rounded to put-up/MOQ. Safety stock removed — simplified to 120-day coverage. Cooling-trend cap: if 30d rate < 60% of window rate → cap at min(90d,180d), sets trend_adjusted=True.

Planned but NOT built: anomaly-flagging layer (plans/rbd-reorder-anomaly-layer.md, P2) — deterministic velocity/$ outlier detection + LLM triage note, injected into _calculate_run() before status flips to in_review.

PART 2 — GAP ANALYSIS: web app vs. templates

Web app today (migrations 001–013, services/{cotton_calc,cotton_pricing,netsuite_client,email_sender}.py, calculations.py 786 lines):

• ✅ Reorder module — live, deterministic, in production.
• ✅ Cotton PO module — collections list, per-SKU review, D&R calc, multiplier column, rates, Excel/CSV export.
• ⚠️ Cotton PO import — web app exports CSV; the Sheet has moved ahead to the 3-PO split + direct-to-NetSuite ambition. Not yet ported.
• ❌ Batik module — does not exist in the web app at all. All batik work is Sheet-only.
• ❌ Anomaly layer — planned, not built.

The template-specific hacks that DIE in the port (do not reimplement):

• Hardcoded DATA_COL indices + every "column-move repair" — web app uses named DB fields.
• INDIRECT/"140:"→"172:" breakout-range guards — no spreadsheet rows to overflow.
• Stacked-header lookups to avoid grabbing FQS/MSQC ship-to cols — web app queries by field.
• Override-column wipe-on-refresh — web app stores overrides as nullable DB columns.
• COUNTA(#N/A) phantom-count guards — real queries return real counts.

PART 3 — DIRECTION LOCKED (2026-07-13) + PORT PLAN

Decisions locked (Cole, 2026-07-13)

1. Encapsulated modules per PO type — NOT one generic engine. Cotton stays cotton; Boutique is done differently and stays its own self-contained module. Future Notions and Liberty = their own modules too. Deliberate: boutique-side logic differs enough that forcing a shared engine costs more than it saves.
2. Cotton quantity engine is already ported (cotton_calc.py, synced 2026-06-10) in the clean multiplier/+badge form. Verify parity vs the live sheet; do not rebuild.
3. Approval = HARD GATE. Each PO type has an assigned manager who must sign off in the app before NetSuite push: Matt → Cotton, Reba → Boutique, Jeff → Notions (Liberty TBD). No push without sign-off.
4. Direct NetSuite push via API replaces the CSV paste — the one-click submit at the end of every module.
5. Stack ranks stay a manual input. Precut yardages depend on stacking that is NOT derivable from NetSuite data (today it's the manual row-172 grid in the boutique sheet, per AdjustPORows.js). The app must give the buyer a surface to enter stacking, or the PO is incomplete. First-class requirement.
6. Deferred (not now): override-wipe-on-refresh, precision override columns (AA/AM). Revisit later. Dropship column: TBD.
7. Multipliers must be configurable, not hardcoded. They've fluctuated historically (mains ~1.8, precuts ~2, CP +2…). Each PO type gets a settings page where Sabrina edits the company-wide defaults; per-collection override still available. Two levels: type-wide default → per-collection override. Batik already does this in the sheet (How to Notes!I1:J4); Cotton currently hardcodes them in cotton_calc.py — the port must lift them into config. Changes drive real PO dollars → audit who/when on edits.
8. Boutique = Batik. The Boutique Collections template is the Batik sheet — already cloned locally at ~/ai-projects-local/batik-po-scripts/batiks/ (6 scripts). No separate sheet to obtain. (Blocker resolved 2026-07-13.)

Two distinct PO outputs — never conflate (Cole, 2026-07-14)

Every PO module produces two separate things with different audiences:

1. Import PO — goes into NetSuite (creates PO records). Internal. This is the 3-PO NetSuite push (Main/-ASYFQ/-ASY). App today: export_netsuite_csv (a simplified single-PO version — needs the 3-PO upgrade).
2. Vendor PO — a clean, readable PDF emailed to the mill to fulfill. External-facing. App today: export_vendor_xlsx (_build_vendor_xlsx layout: RBD letterhead + PO meta + SKU/Desc/QTY/Rate/Amount + TOTAL, filtered per-vendor). The wanted job renders that layout as PDF (one PDF per vendor in the collection).

These are different jobs, formats, and destinations — the import push must not be confused with the vendor PDF. Email rule: the app generates the vendor PDF (PREP only); Cole/team sends it. Never email vendors from the tool.

✅ Vendor PO (PDF) SHIPPED 2026-07-14 — PR #4 merged + deployed live. GET /cotton-pos/{id}/export-vendor-pdf (auth-gated, read-only) via reportlab 5.0.0; "Vendor PDF" button on the cotton PO detail page. Mirrors the _build_vendor_xlsx layout/rollup. Generation-only (never calls send_po_email).

✅ Import PO — preview engine built 2026-07-14 — PR #5 (open). services/cotton_import.py is a pure, golden-master-tested port of the Apps Script Populate Import.js 3-PO split (Main / -ASYFQ / -ASY). POST /cotton-pos/{id}/build-import returns a read-only JSON preview. Preview-only — no NetSuite write. No frontend button yet (lands with Phase 2). Spreadsheet-scanning machinery intentionally not ported.

🚧 BLOCKER for Import PO data layer — NetSuite read creds empty in prod (RESOLUTION KNOWN, parked by Cole 2026-07-14). Read-only diagnostic found ns_account/ns_consumer_key/ns_consumer_secret/ns_token_key/ns_token_secret all length-0 in the server .env (/var/www/html/rbd-reorder-tool/backend/.env; both systemd EnvironmentFile and pydantic env_file point there). So saved-search 2716 reads — which Phase 2 ingestion depends on — don't work live today.

The creds ALREADY EXIST — no NetSuite setup needed. The Cotton PO Apps Script Populate Search Data.js hits the identical RESTlet (account 4582045, script 2140, deploy 3, OAuth1 HMAC-SHA256) — a 1:1 match to netsuite_client.py. Reuse its 4 secrets verbatim. They live in the rb-cotton-po Apps Script Project Settings → Script Properties: consumerKey/consumerSecret/tokenKey/tokenSecret → map to ns_consumer_key/ns_consumer_secret/ns_token_key/ns_token_secret; ns_account=4582045.

To resume: Cole fills the 5 ns_* lines in the server .env (secrets must NOT transit chat / no agent writes to prod secrets) → sudo systemctl restart rbd-reorder-api → agent runs a read-only 1-row 2716 fetch to verify + capture the field inventory. These creds are READ-ONLY (RESTlet 2140). The PO write path is a separate OAuth 2.0 M2M setup, later. Cole to decide reuse-token vs new-dedicated-integration when he picks this back up.

Phase 2 data layer (next, once creds in place): extend populate_collection_items to ingest per-type quantities (precut_yds, kit_yds/kit_rate, rot_yds, cp10/cp15 qty, advanced_yds) + ns_item_id from 2716; then wire the frontend "Generate Import" preview button. The adapter's ROT/CP/kit-rate mappings are currently provisional and flagged in the endpoint's data_gaps.

Target architecture

Encapsulated per-type modules, each owning its own:

• collection → SKUs → badges → dropships → per-collection review UI (Cotton already does this; the pattern to mirror)
• classification rules, rate model (incl. boutique price-per-piece)
• a settings page — company-wide multiplier defaults (mains / precut / CP…) that Sabrina edits, feeding the engine instead of hardcoded constants; per-collection override on top
• stack-rank manual-input surface for precut yardages

Two SHARED services every module calls (built once):

• NetSuite PO push (API/RESTlet) — takes a module's generated PO line objects, creates the POs, returns internal IDs, guards idempotency.
• Approval gate — maps PO type → approver; blocks push until sign-off; records who/when.

Reorder stays its own axis (velocity/group-based, not collection-based). Untouched by this.

Reversibility principle (design law)

Every buyer edit is a non-destructive override on the stored snapshot — change a value, it's a nullable field; clear it, it falls back to computed. This is the reason the web app beats the sheet (no paste-as-values, no formatting rebuild, works from her phone). Never introduce a "wipe on refresh."

Phased plan

Phase 0 — Foundations (now)

• ☑ Changelog + gap analysis + direction lock (this doc).
• ☑ Boutique baseline located = ~/ai-projects-local/batik-po-scripts/batiks/ (Boutique = Batik).
• ☐ Put PO Apps Script repos under git (no version control today — this doc is the only history).

Phase 1 — Cotton: parity + shared services (highest-value, lowest-risk)

1. Golden-master parity — run the current reference cotton collection through the web app, confirm suggested qtys match the live sheet. Fix only real diffs.
2. Build the two shared services on the existing Cotton module: - NetSuite push endpoint POST /cotton-pos/{id}/push-to-netsuite (3-PO split: Main / -ASYFQ / -ASY; store ns_po_internal_id + last_exported_at; idempotent). - Approval gate: PO type → approver (Matt); push disabled until sign-off; audit who/when.
3. Frontend: "Submit for approval" → approver "Approve" → "Push to NetSuite" (button gated on approval). - Blocking research: NetSuite RESTlet write capability (does 2140 POST-create POs or do we deploy a new RESTlet?); does saved search 2716 return Preferred_Vendor/ROT/CP10/CP15/Advance_Sample_Yardage/Precut_Yardage; does the payload expose line-item Location. - Acceptance: reference collection → approver signs off → 2 POs created in NS matching the sheet row-for-row; double-push blocked.

Phase 2 — Boutique module (new, encapsulated)

1. Audit the cloned Boutique Collections template → spec the boutique-specific logic (differs from cotton).
2. Build the encapsulated module: collection UI + classification + multipliers + price-per-piece rates + stack-rank manual input.
3. Reuse the shared NetSuite-push + approval-gate services (approver = Reba). - Known data gap: KTBT- kit SKUs aren't in the boutique Data source yet — resolve before kit lines can import. - Acceptance: a boutique collection with manual stack ranks entered → correct precut yardages → Reba approves → POs pushed to NS.

Phase 3 — Notions / Liberty (future, as needed)

New encapsulated modules reusing the shared services (approvers Jeff / TBD). Only build when the line is ready.

Phase 4 — Anomaly layer (P2, independent)

Per plans/rbd-reorder-anomaly-layer.md — deterministic detector + bounded LLM triage note in _calculate_run(). Doesn't block PO output.

NetSuite write capability — RESEARCHED 2026-07-13

Two NS integrations already exist in the stack:

1. Reorder tool (netsuite_client.py): OAuth 1.0a TBA → RESTlet 2140/deploy 3. Read-only saved-search runner. Auth is solid + reusable; the RESTlet itself cannot create records. No writes exist anywhere in the reorder backend today.
2. Financials dashboard (financials-dashboard/backend/core/netsuite.py): OAuth 2.0 → suitetalk.api.netsuite.com, scope restlets rest_webservices, currently SuiteQL. This scope also unlocks the native record API.

Write path (ranked):

• Path A (recommended): native SuiteTalk REST record API — POST /services/rest/record/v1/purchaseOrder with a PO JSON, OAuth 2.0 (like the financials integration). No custom SuiteScript deploy. Only dependency: integration role has "Purchase Order → Create" permission.
• Path B (fallback): new/extended SuiteScript 2.x RESTlet (record.create({type:'purchaseorder'})) on the existing OAuth 1.0a creds — requires a NS admin/dev to deploy.

RESOLVED 2026-07-13 (Cole, NS admin) — Path A is a full go, no external dependency:

• ✅ REST Web Services feature enabled (Enable Features > SuiteCloud).
• ✅ M2M mapping for cert ZhjkF0E4EvQLtCdA4DDKy1h6dTuWIcP2Hz8b3GKGDJ0 (AANSConnector, active thru 2026-09-15) → Role = Administrator → PO-create permitted.
• Account 4582045, OAuth 2.0 client-credentials/JWT, creds in financials-dashboard/backend/core/netsuite.py (private key on warehouseApi prod server).
• Decision: reuse this OAuth 2.0 integration for the ordering-tool push. (Later nice-to-have: dedicated integration role/entity so PO audit trail isn't all "Cole/Administrator" — not required for v1.)
• Nothing to deploy. Build the payload generator + push against the native record endpoint directly; a dry-run preview is still worth having as a safety gate before the first live push.

Open items / blockers

• Stack-rank source — where does the buyer's stacking come from today, and what's the minimal input UI to reproduce it? (Logic lives in batik AdjustPORows.js row-172 grid.)
• Boutique KTBT- kit source — where do kit SKUs originate? (blocks boutique kit lines).
• Settings/multiplier config — decide the storage shape: one po_type_settings table (type → multiplier set) + optional per-collection override rows. Seed from batik How to Notes!I1:J4 + cotton's current constants.

Related

• [[cotton-po-import-spec]] · [[cotton-po-clasp-and-overrides]] · [[batik-po-template]] · [[batik-ah-quantity-truth]] · [[rbd-reorder-tool]] · [[cotton-po-naming]]
• plans/cotton-po-import-web-app-port.md · plans/cotton-po-module.md · plans/cotton-po-rates.md · plans/rbd-reorder-anomaly-layer.md · plans/vscode/session-5.md