Integration Solution Overview: MySQL and Jushuitan Supply Chain

Scenario and Value

In one real-world project, a retail enterprise attached multiple storefronts to Jushuitan ERP for order aggregation, but the analytics side relied on a self-built MySQL data warehouse. The problem was: by the third day after a promotional campaign, the business discovered that inventory and purchase-inbound records did not match. Tracing back, the root cause was not Jushuitan's data capture but rather the fact that sales outbound, refunds, and allocation documents were not being incrementally synced to MySQL within any time window. Data was exported manually, so it naturally fell behind.

What this solution addresses is automatically syncing Jushuitan's supply chain documents (sales outbound, purchase inbound/returns, orders, refunds, allocations, other inbound/outbound) and master data (products, warehouses, shops, suppliers, distributors, inventory) to MySQL according to defined strategies, with scheduling, retries, alerts, and cleanup in place to form an observable closed loop. The entire solution is orchestrated through 19 strategies on the Qeasy (QingyiYun) Data Integration Platform, with the source side covering both Jushuitan Qimen and non-Qimen API channels.

Integration Architecture and Data Flow

The overall data flow is unidirectional: Jushuitan (including the Qimen channel) → Qeasy Integration Platform → MySQL. Within the platform, two additional strategies (Enterprise WeChat push and 7-day data deletion) handle operational closure.

┌──────────────┐    ┌────────────────────┐    ┌──────────────┐
│  Jushuitan   │ →  │  Qeasy Integration │ →  │  MySQL       │
│  ERP         │    │  Platform          │    │  Warehouse   │
│ (Qimen/Non)  │    │  19 Strategies     │    │  Flat Tables │
└──────────────┘    └────────────────────┘    └──────────────┘
                           │
                           ├──→ Enterprise WeChat Push
                           └──→ Historical Data Cleanup

Execution is divided into three phases based on dependencies:

• Phase 1 (Master data, parallelizable): Warehouses, shops, suppliers, distributors, and product master — these five have no mutual dependencies and can be pulled simultaneously.
• Phase 2 (Business documents, dependent on Phase 1): Product inventory, orders, sales outbound (Qimen/non-Qimen), refunds (Qimen/non-Qimen), purchase orders, purchase inbound, purchase returns, other inbound/outbound, and allocations — these documents need master data from Phase 1 for code mapping and must wait until master data is in place.
• Independent strategies: Enterprise WeChat push triggered by business events; the 7-day data deletion job runs in the early-morning off-peak window.

Interface List

Implementation Key Points

Phased scheduling: There is a strong dependency between master data and business documents. Always orchestrate in the order of Phase 1 → Phase 2 → independent strategies. Do not start Phase 2 before Phase 1 is complete, or code lookups will return nothing.

Incremental fields: Use modified, created, or io_date uniformly as incremental cursors. Run a full sync on first go-live, then incremental by time window in daily operation. Jushuitan's API has an upper limit on the time window per query, so do not set it too wide.

Full-sync fallback: Run a full sync weekly or monthly during the business off-peak window (02:00–04:00), using REPLACE INTO to overwrite. This serves as a data validation mechanism.

Code mapping: Centralize mappings for sku_id, supplier_id, wh_id, and shop_id into a single code_mapping table. All downstream field mappings look up this one table instead of hardcoding per strategy.

Exception retry: Network timeouts use exponential backoff at 30s/60s/120s, up to 3 attempts. On target-database write failure, isolate the single failed record and let the batch continue. Primary key conflicts are handled with REPLACE INTO or ON DUPLICATE KEY UPDATE.

Privacy handling: Credentials and connection strings must not appear in solution documents; place them in environment variables or a secret management service. Fields containing personal information (recipient name, phone number) should be masked or access-controlled in the target database.

Failure observability: Failed data lands in sync_failed_records with source snapshots and failure reasons, supporting manual rerun by strategy and time range. Immediate alerts trigger when a single strategy fails 3 times consecutively; daily summary alerts fire when the daily failure rate exceeds 5%.

Best Practices and Pitfall Retrospective

1. Separate the Qimen and non-Qimen channels into different tables. The field structure and naming returned by Jushuitan's Qimen and non-Qimen APIs differ. A common mistake is writing both into the same target table for convenience, which leads to extremely high cleanup costs later. The safe approach is to create separate target tables per channel (for example jst_saleout_query_qm / jst_saleout_query_fqm) and union them at the consumption layer.
2. Flatten header + detail + batch into a single row. Jushuitan's document details (items) and batches (batchs) are nested arrays; writing them directly causes complaints from drivers or downstream analytics tools. The correct approach is to expand arrays into items_* and bfn_* named columns within the strategy, with primary key + line number as a composite key, writing one row at a time.
3. Handle Emoji and empty values consistently. Jushuitan product names and shop remarks often contain Emojis, which fail when written to low-charset MySQL columns. Empty strings and NULL should be uniformly converted to NULL within the strategy to avoid downstream equality checks being broken by empty strings.
4. Do not let the deletion strategy collide with business syncs. Running the "delete 7-day-old data" job at 02:00 daily is correct, but it must be staggered from the full-sync fallback. Otherwise you will see the bizarre situation of data being deleted and then immediately rewritten by the full sync.
5. Centralize code mapping. For SKU, supplier, warehouse, and shop code mappings, we recommend building a unified mapping table on Qeasy, with all strategies referencing the same mapping. When upstream coding rules change, you only update one place.

When to Use Qeasy

When a self-built MySQL data warehouse needs to pull more than 10 data objects from Jushuitan, involves both Qimen and non-Qimen channels, and requires phased scheduling and exception retry, the maintenance cost of writing custom scripts is high. The Qeasy (QingyiYun) Data Integration Platform provides ready-to-use Jushuitan connectors, visual field mapping, scheduling orchestration, and failure retry, with 19 strategies managed centrally. It is well-suited for supply chain data aggregation scenarios in mid-to-large retail and e-commerce businesses.