MySQL and Wangdiantong Supply Chain Integration Overview

Scenario and Value

In a real project, a beverage supply chain enterprise operates multiple brands in parallel. Every day, e-commerce orders pour in from many stores. The pain point is this: after a promotion ends, the inventory and purchasing reconciliation is usually only noticed by the business team three days later. The ERP itself is not at fault; the issue is the missing automated closed loop. Sales stockouts, purchase receipts, returns, and transfers generated in Wangdiantong are not flowing back to a unified data aggregation layer in time, so BI and downstream systems never get a complete picture.

What we need to do is to glue the 34 integration strategies between Wangdiantong Qimen and MySQL into an observable, replayable, and traceable pipeline based on business semantics. MySQL does not replace Wangdiantong as the transaction system of record; it serves as the data aggregation layer for analytics, reporting, BI, and downstream consumption.

Integration Architecture and Data Flow

The overall architecture has three layers:

• Source layer: Wangdiantong Qimen, covering stores, goods, warehouses, sales orders, purchase orders, stock-in orders, returns and exchanges, stock-out, transfers, and subcontracting.
• Integration middleware layer: responsible for scheduling, transformation, code mapping, incremental watermark tracking, and exception retries. In this solution, this layer is hosted on the Qeasy Data Integration Platform (轻易云数据集成平台), acting as the ETL bridge between heterogeneous systems.
• Aggregation layer: MySQL, providing tables such as tp_wdt_shop, tp_wdt_goods, tp_wdt_sales_trade, and tp_wdt_purchase_order to serve analytics and downstream consumption.

The overall data flow is one-way synchronization: Wangdiantong → MySQL (B_TO_A). Only the strategy "Tongpu - Production Stockout Query" is of type QUERY_ONLY, used as an on-demand data acquisition point that does not write to the main pipeline.

By business semantics the data flow is split into three main streams:

1. Master data stream: store, goods, and warehouse archives. Pulled incrementally by modified, filtered by deleted=0, with full reloads supported for initialization and data repair.
2. Business document stream: sales orders, sales stockouts, purchase orders, stock-in orders, returns, transfers, subcontracting, and more. Pulled with paginated requests using start_time/end_time windows, and relies on master data for the join keys shop_no, warehouse_no, and spec_no.
3. Auxiliary data stream: goods bundles, inventory details, production stockout queries, etc. Frequency is low; 1–2 runs per day are sufficient.

Interface List

The table below lists representative strategies. All 34 strategies are expanded by business module in the expert validation document.

Implementation Notes

• Staged scheduling: master data runs first, 2–4 times per day. Business documents (sales, purchasing, returns, inventory) are staggered every 10–30 minutes to avoid overwhelming upstream APIs. Auxiliary data runs 1–2 times per day.
• Incremental watermark: unify on start_time = LAST_SYNC_TIME, end_time = CURRENT_TIME, mapped to Wangdiantong's modified/created. On first run or missing watermark, fall back to full reload.
• Full reload fallback: initialization and repair scenarios must provide a manual or scheduled full-reload entry per strategy (e.g. every Sunday). During full reload, the corresponding incremental strategy should be paused to avoid dual writes.
• Code mapping: codes for stores, goods, warehouses, and providers are centrally maintained in a mapping table within the integration layer. This solution defaults to passing original values through, leaving room for future business-side extension.
• Exception retry: network jitter and rate limiting are common. Use exponential backoff with an upper bound; a strategy that fails N times in a row should escalate to an alarm queue rather than retrying indefinitely.
• Privacy handling: connection strings, credentials, and tokens must be placed in environment variables or a secret manager. Recipient and address fields should be masked before landing.

Best Practices and Lessons Learned

1. Master data first is a hard constraint. Business documents contain many foreign keys (shop_no, warehouse_no, spec_no, provider_no) that depend on master data. Running business strategies before the master data lands produces "orphan documents." The safe approach is to make the six master-data strategies a precondition gate for stage two.
2. Business strategies must be staggered, not naively concurrent. We once triggered all nine sales-order strategies at the same time; within a few minutes we were rate-limited and some paginated data was lost. After that we staggered same-module strategies by 2–5 minutes, which retains parallelism while avoiding instantaneous peaks.
3. QUERY_ONLY strategies must not enter the main pipeline. The "Tongpu Production Stockout Query" is intended for on-demand acquisition and should not be mixed into the regular schedule, otherwise it pollutes the incremental watermark.
4. Multi-brand separation should be parameter-driven, not strategy-duplicated. Brands such as Tongpu, Fukangyuan, and Jiurunyuan within the same tenant should be split inside a single strategy via shop_no / warehouse_no filters, rather than copy-pasting N copies of the same strategy.
5. Common patterns in Qeasy customer projects: code mappings are managed centrally in the platform mapping table; header and line items are written in two stages (header first, then line item tables); in dual-channel scenarios (Qimen vs non-Qimen), the Qimen channel handles the main path while the non-Qimen channel handles supplemental data, preventing them from blocking each other.

When to Use Qeasy

When an enterprise needs to connect an e-commerce supply chain system like Wangdiantong to a self-built MySQL data warehouse, with dozens of strategies spanning master data and transaction documents, the maintenance cost of an in-house ETL grows quickly. The Qeasy Data Integration Platform (轻易云) handles such multi-strategy workloads with visual strategy orchestration, unified incremental watermarks, centralized mapping management, and an observable scheduling console. Engineers focus on business semantics instead of rebuilding scheduling and retry wheels.