Sync After-Sales Orders from Jushuitan to MySQL: A Practical Integration Strategy

What This Strategy Solves

In the daily after-sales operations of a retail enterprise, Jushuitan generates a large volume of after-sales orders (returns, exchanges, re-shipments, etc.). Finance, supply chain, and data teams need these records landed into MySQL for reconciliation, complaint attribution, and inventory offset analysis. After-sales fields are messy, statuses are diverse, and the Qimen open interface involves pagination and incremental mechanisms — polling it directly tends to miss or duplicate records. This strategy aims to write Jushuitan after-sales orders into MySQL through the Qimen interface in a stable and traceable way, so that same-day orders land in MySQL on the same day.

Data Flow and Field Mapping

The data flow is Jushuitan·Qimen (B) → Qeasy middleware layer → MySQL (A). The source calls the Qimen after-sales order query interface; the middleware handles field cleansing, code mapping, and pagination aggregation; the target writes into the MySQL after-sales header table and line-item table using the business primary key.

Key field mapping table:

Business meaning	Source field (Jushuitan·Qimen)	Target field (MySQL)	Handling notes
After-sales order no.	as_id / oid	after_sales_no	Primary key for idempotent dedup
Order type	type	after_sales_type	Dictionary mapping: return/exchange/re-ship
Original order no.	src_oid	src_order_no	Link to sales outbound
Customer code	shop_id / co_id	customer_code	Converted via Qeasy central mapping table
SKU code	sku_id	sku_code	Aligned with material master data
After-sales qty	qty	after_sales_qty	Numeric, watch out for null defaults
After-sales amount	amount	after_sales_amount	Keep 2 decimals
Status	status	after_sales_status	Store enum verbatim, translate downstream
Modified time	modified	modified_at	Used as the incremental cursor

Headers and lines are landed in two phases: write the header first, then the lines on success — this prevents "orphan" child records.

Configuring on Qeasy

On the Qeasy Data Integration Platform, we split this strategy into three configuration blocks:

Source data source: register the Jushuitan·Qimen account, select the after-sales order query interface, and set request parameters (start time, end time, page number, page size). Use the modified field as the incremental cursor — switch to rolling-by-modified-time after the initial full sync.
Data cleansing and mapping: in Qeasy's field mapping layer, map the Qimen response fields to MySQL fields per the table above; customer codes and SKU codes go through the centrally managed code mapping table, so that future material master changes only need to be edited in one place.
Target write: on the MySQL side, configure target tables (t_after_sales_header, t_after_sales_line); use an UPDATE policy instead of INSERT on primary key conflicts to ensure idempotency.

Centralized code mapping management on Qeasy is a common practice among Qeasy customers — it avoids scattering mapping logic across multiple strategies and saves significant maintenance effort later on.

Implementation Steps

Step 1: Full initial load Manually trigger a full sync to pour historical after-sales orders into MySQL. Observe write throughput and target table index behavior, confirm there are no slow SQLs. This step is usually executed during business off-peak hours.

Step 2: Switch to incremental After the full load completes, switch the cursor to the modified field and set the schedule frequency based on business volume: for enterprises with high after-sales order volume, every 5 minutes; for lower volumes, every 15-30 minutes. Qeasy's built-in incremental + full dual-track mode fits this scenario well — run incremental daily, run periodic full-sync compensation.

Step 3: Validation and reconciliation After each incremental run, use Qeasy's data validation feature to compare source order counts against target counts, and trigger an alert when the difference exceeds a threshold. Also verify business validation items such as total after-sales amounts and existence of linked order numbers.

Step 4: Handle abnormal orders For after-sales orders with abnormal status (e.g., partial returns, multiple modifications), set up a dedicated retry channel — don't let a single abnormal record block the entire batch.

Pitfalls and Lessons Learned

Wrong incremental start point, missing the first batch Earlier we used creation time as the cursor and missed modifications to historical orders. The safe approach is to use creation time for the initial full load, then switch to the modified field for incremental, while persisting the cursor watermark reliably.
Header and lines written in the wrong order, producing orphan child records The sync logic wrote lines before headers; if the header insert failed, lines became "orphans." A typical mistake is looping through arrays directly in a script with no transaction protection. We later standardized on a "header first, then lines" two-stage commit.
Customer codes and SKU codes landed verbatim, mismatches later The source code system did not align with the downstream data warehouse, and after 3 months of reconciliation the numbers didn't match. The best practice is to build a centralized mapping table in Qeasy so that source code changes only touch the mapping and don't affect already-landed data.
Qimen pagination size set too high, dragging the source down Setting page size too large triggered source-side rate limiting. The safe approach is to keep page size between 50 and 100, combined with Qeasy's rate-limiting configuration, to avoid being banned by the source.
Idempotency only via primary key, exploited by concurrent updates The same after-sales order modified multiple times in a short window — relying only on primary key dedup caused "newer data overwritten by older data" ordering issues. We recommend introducing modified_at as an optimistic lock on the MySQL side: only changes newer than the current record are landed.

Applicable and Non-Applicable Scenarios

Applicable: After-sales orders need to enter the data warehouse for financial reconciliation, complaint analysis, and inventory offset; enterprises with moderate volume and frequent after-sales status transitions.

Not applicable: Customer service agent scenarios that need real-time (second-level) after-sales status; scenarios where the after-sales business process involves multi-level approval and complex workflow orchestration — those are better served by a BPM or dedicated after-sales system rather than simple sync-and-landing.