Channel Info Sync from JikeCloud Reconciliation to MySQL: A Qeasy Hands-On Tutorial

What This Strategy Solves

Channel info is classic master data. It does not generate transactions by itself, yet it is a prerequisite for orders, reconciliation, and settlement. In one real engagement, the customer's pain was straightforward: the channel master maintained in JikeCloud was needed on the reconciliation side for channel-level roll-ups, but the channel table in the downstream MySQL SRM database was either missing fields or lagging by a week. At month-end close the two channel lists disagreed, and IT ended up holding the bag. This strategy aims to keep the MySQL channel table aligned with the JikeCloud reconciliation channel master in near real time, using gmtModified as the incremental cursor and an hourly polling window.

Data Flow and Field Mapping

The source is JikeCloud's erp.sales.get query API (POST, paginated, time-window based). The target is the channel table in the customer's on-premises MySQL SRM database (INSERT). The Qeasy platform sits in the middle, handling pagination, cursor bookkeeping, field mapping, and persistence.

Key field mapping (core columns):

Geographic region fields (countryId/Name, provinceId/Name, cityId/Name, townId/Name, streetId/Name) are kept denormalized in the target so reporting layers can read directly without joins.

How to Configure It in Qeasy

On customer sites we generally configure in a "source first, then target, mapping in the middle" order:

1. Source platform: Pick JikeCloud, API erp.sales.get (POST, QUERY). Pin pageIndex to 0 and pageSize to 50. Bind gmtModifiedStart to the built-in variable {{LAST_SYNC_TIME|datetime}}. Leave gmtModifiedEnd empty or bind it to the same reference. Leave code and name empty to scan all records.
2. Pagination and dedup: Enable auto-pagination. Configure channelCode as number (business code) and channelId as id (primary key), and set idCheck to true. This determines the dedup granularity—always use a stable business field, never the name.
3. Target platform: Select the customer's on-premises MySQL, API execute (POST, EXECUTE). Paste the full INSERT INTO lhhy_srm.channel ... SQL into main_sql.
4. Centralized code mapping: A common Qeasy customer pattern—extract all "JikeCloud code → MySQL code" dictionaries (channel type, online platform type, warehouse code, etc.) into a centralized mapping table, then resolve source fields through it before binding them into SQL placeholders. We recommend this for channelTypeId, onlinePlatTypeCode, and warehouseCode; otherwise value changes later require editing SQL.
5. Persistence strategy: Default to INSERT with idempotency on the business key. Add an UPDATE branch only if change tracking is later required. A single dual-write (INSERT/UPDATE) tends to overwrite create_time, so the safe approach is to separate them into two phases.

Implementation Steps

Phased scheduling is another common Qeasy pattern—incremental and full loads run side by side:

1. Initial full load: Hard-code gmtModifiedStart to 1970-01-01 00:00:00, trigger once manually, and land the entire historical channel master into MySQL. Immediately afterwards, switch the cursor back to {{LAST_SYNC_TIME|datetime}}.
2. Incremental start point: Source strategy cron 20 1-8 * * * (minute 20 past every hour from 01:00 to 08:00). Target strategy cron 55 1-8 * * * (minute 55 past the same hours). The target runs 35 minutes behind the source to leave headroom for source fetch and pagination merge.
3. Frequency: Master data does not change frequently, so hourly is sufficient. If the customer has heavy channel creation during the day, the window can be expanded to 0–23, but we recommend not going below 30 minutes to avoid pressuring the source API.
4. Dependencies and order: In the customer's broader integration chain this strategy belongs to sequence B (master data). Downstream sales order sync (sequence A) consumes channel codes, so confirm this strategy runs reliably before OMS.

Lessons Learned

1. Classic mistake: using channel name as the dedup key. The source allows duplicate names without warning; using name as number causes Qeasy to let later records overwrite earlier ones, silently losing data. The safe choice is channelCode as number and channelId as id—double insurance.
2. gmtModified time zone pitfall. JikeCloud returns timestamps without a time zone. If MySQL stores them in local time, the incremental window misses data across day boundaries. The safe move is to format to UTC explicitly in the Qeasy mapping layer and have MySQL store and compare in UTC.
3. SQL errors when geographic fields are empty. Some legacy channels have no province/city filled in, but the SQL placeholders <{xxx: }> write empty strings, which currently pass. If the customer later adds a NOT NULL constraint, it will break. Normalize empty values to NULL in the mapping layer up front.
4. Do not mix source_Id with channelId. We have seen customers use the source system PK directly as the target primary key id. When the source changes its PK strategy later, the entire table needs to be rebuilt. Keeping source_Id separate is a lifesaver for backtracking and replay.
5. Forgot to switch back to the incremental cursor after the initial full load. Data was accurate for the first week after go-live, then suddenly stopped. Investigation showed gmtModifiedStart was still pinned to the historical timestamp, so subsequent full loads could not progress. This "forgot to switch back" is a classic pitfall—put a red note in the strategy's description field as a reminder.

When to Use and When Not to Use

Use when: Master data needs to flow from a SaaS ERP to an on-premises business database on an incremental time-window basis, and downstream order and reconciliation flows consume that master data. Do not use when: You need bidirectional sync, sub-second latency (prefer change notifications or CDC), or the source has no reliable modification timestamp—in the last case, full scheduled overwrite is the only option.