Skip to main content

Order Sync Overview

Understanding how order synchronization works across BigCommerce and WooCommerce platforms.

What is Order Sync?​

Order Sync is the automated process that:

  1. Fetches new orders from e-commerce platforms (BigCommerce/WooCommerce)
  2. Normalizes platform-specific data into a unified format
  3. Verifies all product SKUs against Business Central
  4. Expands kit/bundle products into component items
  5. Writes validated orders to the middleware database
  6. Updates the platform to mark orders as processed

Supported Platforms​

BigCommerce​

  • API v3 (REST)
  • Order statuses: Awaiting Processing → Awaiting Shipment
  • Supports: Store credit, applied discounts, staff notes, custom fields

WooCommerce​

  • REST API v3
  • Order statuses: Processing → Exported (custom meta)
  • Supports: Smart coupons, PW Gift Cards, custom order meta

Sync Pipeline​

Step 1: Fetch Orders​

Criteria:

  • Status: "Awaiting Processing" (BigCommerce) or "Processing" (WooCommerce)
  • Date: Orders modified since last sync
  • Limit: 100 orders per run (configurable)
  • Filters: Excludes test orders (< $1.00)

Step 2: Normalize Order Data​

Platform-specific → Unified format:

Normalized_Order {
  order_number: "BGM-12345"
  date: "2026-03-17"
  platform_order_id: "12345"
  
  // Addresses
  bill_first_name: "John"
  bill_last_name: "Doe"
  ship_address1: "123 Main St"
  ship_city: "Los Angeles"
  ship_state: "CA"
  
  // Payment
  klarna: false
  stripe: true
  trans_id: "pi_abc123"
  
  // Line items
  line_items: [...]
}

Step 3: Verify SKUs​

Three-tier verification:

  1. Check rm_items (middleware database)

    • SKU exists?
    • Status allowed?

  2. Check Business Central OData

    • Item exists?
    • Status not blocked?

  3. Result:

    • ✅ All pass → Continue
    • ⚠️ Not in middleware → Soft fail (retry later)
    • ❌ Not in BC or blocked → Hard fail (error_orders)

Step 4: Expand Kits​

For kit parent SKUs:

Order has: KIT-PARENT-123 (qty: 2)

Kit definition (rm_kits):
  KIT-PARENT-123:
    - COMP-A (qty: 3)
    - COMP-B (qty: 1)

Expanded lines:
  Line 1: KIT-PARENT-123 (qty: 2, line_type: 1)
  Line 2: COMP-A (qty: 6, line_type: 2, parent: 1)
  Line 3: COMP-B (qty: 2, line_type: 2, parent: 1)

Step 5: Apply Store Credit​

Proportional distribution:

Order subtotal: $100
Shipping: $10
Tax: $9
Total: $119
Store credit applied: -$11.90 (10%)

Result:
  Product lines: -$10.00 credit
  Shipping line: -$1.00 credit
  Tax line: -$0.90 credit

Step 6: Write to Database​

Three tables:

rm_order:

  • Order header
  • Billing/shipping info
  • Payment flags
  • Timestamps

rm_address:

  • Separate bill/ship addresses
  • Normalized format

rm_lineitems:

  • All line items
  • Products, kits, shipping, tax, coupons
  • Import/verify tracking

Step 7: Update Platform​

BigCommerce:

  • Status: 9 (Awaiting Shipment)
  • Staff note: "Exported to middleware [timestamp]"

WooCommerce:

  • Order meta: sumatra_wc_integrator_exported = 1
  • Hidden from customer

Execution Schedule​

Default: Hourly​

0 * * * * /usr/bin/php /path/to/wp-content/plugins/gsm-middleware/cron/order-sync.php

Configurable Per Site​

  • Enable/disable order sync
  • Minimum date filter
  • Maximum orders per run
  • Skip test orders toggle

Sync Flow Diagram​

┌─────────────────────────────────────────────┐
│ E-commerce Platform                         │
│ (BigCommerce / WooCommerce)                 │
└─────────────────┬───────────────────────────┘
                  │
                  │ API Request (fetch orders)
                  ▼
┌─────────────────────────────────────────────┐
│ Order Sync Process                          │
│                                             │
│ 1. Fetch new orders                         │
│ 2. Normalize to standard format             │
│ 3. Verify SKUs (BC + middleware)            │
│ 4. Expand kits                              │
│ 5. Apply credits/discounts                  │
│ 6. Write to database                        │
└─────────────────┬───────────────────────────┘
                  │
                  │ Success
                  ▼
┌─────────────────────────────────────────────┐
│ Middleware Database                         │
│                                             │
│ • rm_order (header)                         │
│ • rm_address (addresses)                    │
│ • rm_lineitems (line items)                 │
└─────────────────┬───────────────────────────┘
                  │
                  │ Ready for export
                  ▼
┌─────────────────────────────────────────────┐
│ Business Central                            │
│ (Next step: BC Export)                      │
└─────────────────────────────────────────────┘

Error Handling​

Soft Failures (Retry Automatically)​

SKU not yet in middleware:

  • Order left on platform (not marked as processed)
  • Will retry on next cron run
  • Expected after new products added to BC

Temporary API errors:

  • Rate limiting (429)
  • Timeout errors
  • Network issues

Hard Failures (Manual Resolution Required)​

Bad SKUs:

  • SKU doesn't exist in Business Central
  • SKU has blocked status
  • Added to rm_error_orders

Data validation errors:

  • Invalid addresses
  • Missing required fields
  • Invalid payment data

Kit errors:

  • Kit component not found
  • Circular kit definitions

Maintenance Window​

Every Sunday 19:00 - Monday 04:00 UTC:

  • Business Central maintenance
  • All sync operations automatically paused
  • Resumes after window closes
  • Cannot be disabled (BC requirement)

Monitoring​

Check Sync Status​

Via Control Panel:

  • View last sync time per site
  • See pending order counts
  • Review recent errors

Via Database:

-- Check last sync time
SELECT name, order_last_run, 
       TIMESTAMPDIFF(MINUTE, order_last_run, NOW()) as minutes_ago
FROM rm_sites 
WHERE is_active = 1 AND orders_sync = 1;

-- Check pending orders
SELECT COUNT(*) as pending 
FROM rm_order 
WHERE imported = 0;

Logs​

# Real-time monitoring
tail -f /var/log/gsm-middleware/order-sync.log

# Check for errors
grep "ERROR" /var/log/gsm-middleware/order-sync.log

# Stats from last run
grep "Summary" /var/log/gsm-middleware/order-sync.log | tail -1

Performance​

Typical Execution Times​

  • Fetch 100 orders: 5-15 seconds
  • Normalize per order: 0.1-0.5 seconds
  • SKU verification (batch): 1-3 seconds
  • Database write: 0.5-1 second
  • Platform update: 0.5-2 seconds per order

Total: 2-5 minutes for 100 orders​

Optimization Tips​

  1. Increase batch size for high-volume sites
  2. Add database indexes on frequently queried columns
  3. Use persistent connections to BC OData
  4. Cache SKU verification results (5 minutes)
  5. Parallel processing for multiple sites

Data Retention​

Active orders: Keep indefinitely (needed for fulfillment)

Archived orders: Move to archive tables after:

  • 90 days (standard)
  • Order fulfilled and tracking delivered
  • No open issues

Error orders: Review and clean monthly

Next Steps​