Wiki · NS open orders Suitelet R595

Mike's words: “I'm also going to probably make an ‘open orders’ Suitelet with sections for sales orders, one section for purchase orders and one section for work orders. This way my team could easily see the system as well I guess it could either be a dashboard or a suitelet either really.” This wiki picks the recommendation: build it as a Suitelet (NS-native, accessible from the NS menu / portlet on Home) with a Pages mirror at /open-orders.html (D1-backed, mobile-friendly, faster). Both surfaces read from the same D1 mirror that already syncs every 2 minutes (R121 tier-2 transactions sync). Three lanes, three audiences: SO for sales/CS, PO for purchasing, WO for production. Inline buttons (approve / hold / escalate) all stage into proposed_actions per ADR-031 — nothing writes to NS directly. The team gets a single pane of glass, Mike keeps the HITL invariant.

A v2 workflow contract (open_orders_suitelet, risk 1) that powers two surfaces:

• NS Suitelet — customscript_gfs_open_orders (script id TBD pending Mike approval). Lives in the NS menu and/or as a Home dashboard portlet.
• Pages mirror — https://gfs-netsuite.pages.dev/open-orders.html. Same D1 data, plain HTML, faster for mobile + offline.

It is NOT a write surface in itself. Every action button stages into proposed_actions. The existing ns_push_queue drainer handles the NS write once HITL approves.

It is NOT a replacement for individual SO / PO / WO record drill-down — rows link out to NS records. It's a triage queue, not a replacement for NS.

1. Entry — user opens Suitelet OR cron tick OR manual refresh.
2. Data freshness gate — D1 mirror must be within 5 minutes; else force sync first.
3. SuiteQL pull — pull open transactions where type IN ('SalesOrd','PurchOrd','WorkOrd') AND status not closed.
4. Filter bar — apply user-selected status / customer / vendor / item / location / date range.
5. Per-row enrichment — LEFT JOIN customer_invoices (SO), item_receipts (PO), assembly_builds (WO); compute days_open + item_count + dollar_value.
6. Render Section 1 — Open SOs — columns: tranid · customer · otherrefnum · status · item_count · $ · days_open · owner · actions. Groups: Pending Fulfillment / Partially Fulfilled / On Hold. Sortable.
7. Render Section 2 — Open POs — columns: tranid · vendor · memo · status · item_count · $ · days_open · location · actions. Groups: Pending Receipt / Partially Received / Pending Bill. Path 1/2 tag derived from memo prefix or createdfrom flag. Sortable.
8. Render Section 3 — Open WOs — columns: tranid · assembly_item · memo (TBD) · status · qty · $ · days_open · location · actions. Groups: Released / In Progress / Built but Not Closed. Sortable.
9. Inline action buttons — Approve (risk 1) / Hold (risk 2, prompts hold_reason modal) / Escalate (risk 3, drafts email) all stage into proposed_actions per ADR-031.
10. Drill-in — click row opens NS SO/PO/WO record in a new tab via existing /netsuite/redirect/{type}/{id}.
11. Audit + observability — fire open_orders.dashboard_loaded + per-action open_orders.action_staged events; write workflow_run_log + reflexion_log row.
12. JSON-LD emit — inline WorkflowDefinition + per-row Order entities so chat can ingest the dashboard schema directly.

• Team sees every open SO / PO / WO on a single screen, grouped by status, with the customer PO# thread visible
• Stalled orders surface to the top by default (sort by days_open DESC)
• Any team member can stage an approve / hold / escalate action with one click; Mike (or owner once delegated) approves before any NS write
• Filters let purchasing focus on Pending Receipt, sales focus on Partially Fulfilled, production focus on Released WOs
• Chat (council v2) can answer “what's open for Driscoll” by ingesting the JSON-LD — no scraping
• Mobile-friendly Pages mirror lets Mike check on the road

workflow_type: open_orders_suitelet
risk_level: 1
trigger: cron every 2 min OR manual refresh button OR NS user-event on SO/PO/WO save
inputs_required:
  refresh_scope: ['all', 'sales_orders', 'purchase_orders', 'work_orders']
  filter:
    status: optional
    customer: optional (SO section)
    vendor: optional (PO section)
    item: optional (WO section, assembly item)
    location: optional
    date_from: optional
    date_to: optional
context_to_load:
  - sql: SELECT id, type, tranid, entity, status, total, trandate,
              otherrefnum, memo, createdby
         FROM transactions
         WHERE type IN ('SalesOrd','PurchOrd','WorkOrd')
           AND status NOT IN ('Closed','Cancelled','Billed',
                              'Fully Received','Fully Fulfilled','Built and Closed')
  - sql: customer_invoices (for SO progress)
  - sql: item_receipts (for PO progress)
  - sql: assembly_builds (for WO progress)
  - sql: transaction_lines (for item_count)
preconditions:
  - D1 tables fresh within 5 minutes (else trigger manual sync first)
  - source_record_exists for any inline action
fan_out_targets:
  - load_open_sales_orders (chat_tool / d1_read)
  - load_open_purchase_orders (chat_tool / d1_read)
  - load_open_work_orders (chat_tool / d1_read)
  - render_three_section_view (chat_tool)
  - enable_action_buttons (chat_tool — wires approve/hold/escalate to proposed_actions)
post_actions:
  - log_run
  - event(open_orders.dashboard_loaded)
  - event(open_orders.row_drilled)        — per row drill click
  - event(open_orders.action_staged)      — per action button click
  - reflexion (tracks which sections users interact with most)
verify_checks:
  - data_age_within_5min (+0s)
  - all_three_sections_rendered (+0s)
HITL:
  All NS writes (approve / hold / escalate outcomes) flow through proposed_actions per ADR-031.
  No direct NS write from the Suitelet itself.
NS Suitelet:
  Script id: customscript_gfs_open_orders (TBD pending Mike approval)
  Deployed Suitelet record: customdeploy_gfs_open_orders (TBD)
  Portlet placement: Home dashboard portlet (TBD: confirm w/ Mike)
Pages mirror:
  Path: /open-orders.html
  Backed by: D1 via Pages Functions (no caching, live read)
  JSON-LD: inline WorkflowDefinition + per-row Order entities

D1 tables read

Apply migration:

npx wrangler d1 execute gfs-netsuite --remote --file migrations/schema/135_open_orders_suitelet.sql

Verify contract:

npx wrangler d1 execute gfs-netsuite --remote --command "SELECT workflow_type, display_name, risk_level, contract_version FROM workflow_definitions WHERE workflow_type = 'open_orders_suitelet';"

Manual refresh (test):

curl -X POST https://api.ai-globalfoodsolutions.co/api/open-orders/refresh \
  -H "X-Edit-Token: $TOKEN" \
  -d '{"refresh_scope":"all"}'

Open the Pages mirror:

https://gfs-netsuite.pages.dev/open-orders.html

Open the NS Suitelet (once customscript_gfs_open_orders is deployed):

NS → Customization → Scripting → Scripts → "GFS Open Orders" → Deployments → opens at /app/site/hosting/scriptlet.nl?script=customscript_gfs_open_orders&deploy=customdeploy_gfs_open_orders

Roll back (remove contract only — tables stay):

npx wrangler d1 execute gfs-netsuite --remote --command "DELETE FROM workflow_definitions WHERE workflow_type = 'open_orders_suitelet';"

• TBD Mike approves customscript_gfs_open_orders Suitelet script id + deployed Suitelet record
• TBD Mike confirms Home dashboard portlet placement (yes / no / which slot)
• TBD Mike confirms WO customer PO# threading field (memo or elsewhere)
• Per-section saved-view persistence — remember filter combos per user
• Bulk actions — select N rows · approve/hold all (HITL still single-gate)
• Section count badges in the NS menu icon (e.g. "Open Orders (47)")
• Slack / email digest variant — daily 8am AM email with top-10 stalled orders per section
• Mobile-first restyle of /open-orders.html (Pages mirror) — current pass is desktop-leaning
• Add "Recently closed" toggle — surface orders closed in last 24h for sanity-check
• JSON-LD chat ingestion test — eval-set entry: "what's open for Driscoll" → chat answers from JSON-LD