📊 Script 6 Complete Workflow (OPTIMIZED v3) - After Modifications
🎯 Purpose:
Read tracking CSV → Mark orders as "Fulfilled" on Shopify → Send tracking info to customers
🚀 What Changed (Before vs After):
Aspect ❌ BEFORE ✅ AFTER (v3)
Fetch ALL 62K+ unfulfilled orders from GraphQL batch lookup - fetch ONLY orders
Step 4 Lookup
Shopify in CSV
Lookup Time 30-45 minutes ~5-10 seconds
API Calls per 3 calls (lookup + fulfillment_orders +
1-2 calls (cached fulfillmentOrderId)
Order fulfill)
Concurrency 4 concurrent 5 concurrent
Batch Delay 2.8 seconds 2 seconds
📋 Tables Involved:
Table Purpose Action
fulfilled_orders_tracker Track which orders are already fulfilled Read + Insert (upsert)
🔄 Complete Workflow (OPTIMIZED v3):
┌─────────────────────────────────────────────────────────────────┐
│ STEP 1: Read CSV File (Instant) │
├─────────────────────────────────────────────────────────────────┤
│ │
│ node [Link] --csv=ekart_14jan.csv │
│ │
│ Auto-detects CSV format (Shiprocket/Ekart/ShipEase): │
│ ├─ Order ID: "Order Number" or "Order Id" or "Order ID" │
│ ├─ AWB: "AWB" or "AWB Code" or "AWB Number" │
│ ├─ Status: "Shipment Status" or "Current Status" │
│ └─ Courier: "Courier" or "Courier Name" │
│ │
│ Example from ekart_14jan.csv: │
│ ├─ Order Number: #141337 │
│ ├─ AWB: LUAC0000605173 │
│ ├─ Shipment Status: Out for Delivery │
│ └─ Courier: Ekart │
│ │
└─────────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────────┐
│ STEP 2: Check Already Fulfilled │
├─────────────────────────────────────────────────────────────────┤
│ │
│ Query fulfilled_orders_tracker once per store: │
│ │
│ SELECT order_id FROM fulfilled_orders_tracker │
│ WHERE store_id = 'IndiTrend' │
│ │
│ Returns Set of order IDs (O(1) lookup): │
│ { "#141000", "#141001", "#141002", ... } │
│ │
│ Example output: │
│ ✅ Found 5,873 fulfilled orders for IndiTrend │
│ ✅ Found 1,663 fulfilled orders for Evashu │
│ │
│ Purpose: Skip orders that were fulfilled in previous runs │
│ │
└─────────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────────┐
│ STEP 3: Filter Orders in Memory │
├─────────────────────────────────────────────────────────────────┤
│ │
│ For each CSV row, apply 6 conditions: │
│ │
│ ❌ SKIP if: │
│ 1. No Order ID │
│ 2. No AWB code │
│ 3. Status contains "CANCEL" │
│ 4. Unknown store (not Evashu/IndiTrend) │
│ 5. Already in fulfilled_orders_tracker │
│ │
│ ✅ INCLUDE if all conditions pass │
│ │
│ Store Detection (by Order ID threshold): │
│ #6023401 → Evashu (>= 6,020,000) │
│ #141337 → IndiTrend (>= 13,000) │
│ #1135223 → ❌ Shiprocket internal ID (NOT a Shopify order) │
│ │
│ Example output: │
│ 📊 Found 16,755 NEW orders to fulfill │
│ ⏭️ Skipped: IndiTrend: 5,471 | Evashu: 1,562 │
│ │
└─────────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────────┐
│ 🚀 STEP 4: GraphQL Batch Lookup (NEW OPTIMIZATION!) │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ⚡ BEFORE: Fetched ALL 62,000+ unfulfilled orders (30+ min) │
│ ⚡ NOW: Fetch ONLY the orders in your CSV! (~5-10 seconds) │
│ │
│ How it works: │
│ │
│ 1️⃣ Build GraphQL query for 50 orders at a time: │
│ query { │
│ orders(first: 50, query: "name:141337 OR name:141258..."){│
│ edges { node { │
│ id, name, fulfillmentStatus, │
│ fulfillmentOrders { edges { node { id }}} │
│ }} │
│ } │
│ } │
│ │
│ 2️⃣ Cache results in memory: │
│ shopifyOrderCache = { │
│ "#141337": { │
│ id: "gid://shopify/Order/123456", │
│ fulfillmentOrderId: "gid://shopify/FulfillmentOrder/789",│
│ fulfillment_status: null │
│ }, │
│ ... │
│ } │
│ │
│ 3️⃣ Benefits: │
│ ├─ Skips 1 API call per order (no individual lookup) │
│ ├─ Has fulfillmentOrderId ready (skips another API call) │
│ └─ Only fetches what you need, not all 62K+ orders │
│ │
│ Example output: │
│ 📦 Looking up 15,361 orders from IndiTrend (GraphQL batch)... │
│ Batch 1/308 (0%)... │
│ Batch 308/308 (100%)... │
│ ✅ Cached 11,507 orders from IndiTrend │
│ ℹ️ 3,854 orders not found (Shiprocket internal IDs) │
│ │
│ 📊 Lookup complete: 12,885/16,755 orders found │
│ Cache hit rate: 77% │
│ │
└─────────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────────┐
│ STEP 5: Fulfill on Shopify │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ⚡ PARALLEL PROCESSING (5 concurrent, 2s delay between) │
│ │
│ For each order: │
│ │
│ 1️⃣ Check cache (from Step 4) │
│ ├─ Cache HIT → Use cached data (NO API call!) │
│ └─ Cache MISS → Individual lookup (rare) │
│ │
│ 2️⃣ Check fulfillment status from cache │
│ ├─ Already fulfilled → Skip │
│ └─ Not fulfilled → Continue │
│ │
│ 3️⃣ Generate tracking URL │
│ buildTrackingURL("LUAC0000605173", "Ekart") │
│ → [Link] │
│ │
│ 4️⃣ Create fulfillment (uses cached fulfillmentOrderId!) │
│ POST /[Link] │
│ { │
│ location_id: 61633495143, │
│ notify_customer: true, ← Customer gets email! │
│ tracking_info: { │
│ number: "LUAC0000605173", │
│ company: "Ekart", │
│ url: "[Link] │
│ }, │
│ line_items_by_fulfillment_order: [{ │
│ fulfillment_order_id: 123456 ← FROM CACHE! │
│ }] │
│ } │
│ │
│ Result: Shopify marks order as "Fulfilled" ✅ │
│ Customer receives tracking email 📧 │
│ │
│ Example output: │
│ ⚡ Processing 449 orders in 90 batches (5 concurrent) │
│ Batch 1/90: ✅ 5 | ❌ 0 │
│ Batch 2/90: ✅ 4 | ❌ 1 │
│ ... │
│ │
└─────────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────────┐
│ STEP 6: Update Tracker (Batch Insert) │
├─────────────────────────────────────────────────────────────────┤
│ │
│ After ALL orders processed, batch insert to tracker: │
│ │
│ INSERT INTO fulfilled_orders_tracker │
│ (order_id, store_id, awb_code, fulfilled_at) │
│ VALUES │
│ ('#141337', 'IndiTrend', 'LUAC0000605173', NOW()), │
│ ('#141258', 'IndiTrend', 'LUAC0000605081', NOW()), │
│ ... │
│ ON CONFLICT (order_id) DO UPDATE │
│ │
│ Example output: │
│ 📤 Batch inserting 310 orders to tracker... │
│ ✅ Inserted 310 orders to tracker │
│ │
│ Purpose: Next run will skip these orders │
│ │
└─────────────────────────────────────────────────────────────────┘
↓
┌─────────────────────────────────────────────────────────────────┐
│ STEP 7: Summary Report │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 📊 FULFILLMENT SUMMARY │
│ ───────────────────────── │
│ ⏱️ Total Time: 288.1 seconds │
│ Total Orders Processed: 500 │
│ ✅ Successfully Fulfilled: 310 │
│ ⏭️ Skipped: 0 │
│ ❌ Errors: 190 (Shiprocket internal IDs - expected) │
│ │
│ 📈 By Store: │
│ IndiTrend: 260 fulfilled (57.9%) │
│ Evashu: 50 fulfilled (98.0%) │
│ │
└─────────────────────────────────────────────────────────────────┘
📊 Visual Flow Diagram (OPTIMIZED v3):
CSV FILE (ekart_14jan.csv)
│
▼
┌─────────────────────────────────────┐
│ STEP 1: Parse CSV │
│ Extract: Order ID, AWB, Status │
│ (Instant) │
└─────────────────────────────────────┘
│
▼
┌─────────────────────────────────────┐
│ STEP 2: fulfilled_orders_tracker │
│ (Supabase - 2-3 seconds) │
│ │
│ Check: Is order already fulfilled?│
│ ├─ YES → Skip ⏭️ │
│ └─ NO → Continue │
└─────────────────────────────────────┘
│
▼
┌─────────────────────────────────────┐
│ STEP 3: Filter Orders │
│ (Instant - in memory) │
│ │
│ ❌ No Order ID → Skip │
│ ❌ No AWB → Skip │
│ ❌ Cancelled → Skip │
│ ❌ Already fulfilled → Skip │
│ ❌ Unknown store → Skip │
│ ✅ Valid → Process │
└─────────────────────────────────────┘
│
▼
┌─────────────────────────────────────┐
│ 🚀 STEP 4: GraphQL Batch Lookup │
│ (NEW! 5-10 seconds for 15K orders) │
│ │
│ Query Shopify for ONLY CSV orders: │
│ "name:141337 OR name:141258 OR..." │
│ │
│ Cache results: │
│ ├─ Shopify internal order ID │
│ ├─ fulfillmentOrderId (saves call!)│
│ └─ fulfillment_status │
│ │
│ ⚡ OLD: Fetch ALL 62K+ orders │
│ ⚡ NEW: Fetch ONLY ~15K needed │
└─────────────────────────────────────┘
│
▼
┌─────────────────────────────────────┐
│ STEP 5: Shopify Fulfillment │
│ (5 concurrent, 2s delay) │
│ │
│ For each order: │
│ 1. ✅ Use cached data (no lookup!) │
│ 2. POST /[Link] │
│ └─ tracking_number │
│ └─ tracking_url │
│ └─ notify_customer: true 📧 │
│ └─ fulfillment_order_id (cached)│
│ │
│ API calls reduced: 3 → 1 per order │
└─────────────────────────────────────┘
│
▼
┌─────────────────────────────────────┐
│ STEP 6: fulfilled_orders_tracker │
│ (Batch Insert at END - 1 second) │
│ │
│ INSERT all fulfilled orders │
│ → Next run skips these │
└─────────────────────────────────────┘
│
▼
┌─────────────────────────────────────┐
│ STEP 7: Summary Report │
│ │
│ Shows: Time, Fulfilled, Errors │
│ By Store breakdown │
└─────────────────────────────────────┘
🎯 Key Points (Updated):
Aspect Behavior
Data Source Local CSV file (Shiprocket/Ekart/ShipEase)
Tracker Table fulfilled_orders_tracker (read + write)
Aspect Behavior
GraphQL Lookup 🚀 NEW! Batch lookup for CSV orders only
Cache Stores order ID + fulfillmentOrderId
Parallel Processing 5 concurrent, 2s delay
Customer Notification ✅ Yes! notify_customer: true
Rate Limiting Retry with exponential backoff
Supported Stores Evashu, IndiTrend only
📋 Usage Commands:
# Normal mode (500 orders max)
node [Link] --csv="ekart_14jan.csv"
# Process ALL pending orders
node [Link] --csv="ekart_14jan.csv" --all
# Test mode (5 orders only)
node [Link] --csv="ekart_14jan.csv" --test
# Specific store only
node [Link] --csv="ekart_14jan.csv" --store=Evashu
⏱️Time Comparison:
Step ❌ BEFORE ✅ AFTER (v3)
Step 1 (Parse CSV) Instant Instant
Step 2 (Check tracker) 2-3 sec 2-3 sec
Step 3 (Filter) Instant Instant
Step 4 (Lookup) 30-45 min (fetch ALL 62K) 5-10 sec (GraphQL batch)
Step 5 (Fulfill) ~3 hours ~2.5 hours (fewer API calls)
Step 6 (Update tracker) 1 sec 1 sec
TOTAL (17K orders) ~3.5-4 hours ~2.5-3 hours
🔗 Relationship with Other Scripts:
┌──────────────────────────────────────────────────────────────┐
│ COMPLETE WORKFLOW │
├──────────────────────────────────────────────────────────────┤
│ │
│ SCRIPT 1: Shopify → Database (Order Validation) │
│ └─ Fetches orders from Shopify │
│ └─ Validates (fraud, RTO, duplicates) │
│ └─ Inserts to all_order │
│ │
│ ↓ (Manual: Upload to Shiprocket/Ekart for shipping) │
│ │
│ SCRIPT 5: CSV → Database (Tracking Import) │
│ └─ Imports Shiprocket/Ekart tracking CSV │
│ └─ Updates all_order with AWB, Status │
│ └─ Detects GAP orders │
│ │
│ ↓ (Same CSV file) │
│ │
│ SCRIPT 6: CSV → Shopify (Fulfillment) 🚀 OPTIMIZED v3 │
│ └─ Reads same tracking CSV │
│ └─ 🚀 GraphQL batch lookup (NOT fetch ALL orders!) │
│ └─ Marks orders as "Fulfilled" on Shopify │
│ └─ Sends tracking email to customer! 📧 │
│ └─ Records in fulfilled_orders_tracker │
│ │
└──────────────────────────────────────────────────────────────┘
✅ Summary of Optimization:
What Changed Impact
GraphQL batch lookup instead of REST pagination 30 min → 10 sec for lookup
Cache includes fulfillmentOrderId Saves 1 API call per order
Increased concurrency (4 → 5) Faster processing
Reduced delay (2.8s → 2s) Faster processing
Total time saved ~1 hour per 17K orders