MySQL and DingTalk Integration Overview: Automated Sync of Supplier Accounts and Goods Prices via Yida Workflows
Scenario and Value
At the reconciliation center of a retail enterprise, the finance team used to verify supplier payment accounts and the latest goods prices every day. The data had to be manually exported from DingTalk Yida approval workflows into Excel and then pasted into MySQL. If an approval was completed late at night without anyone handling it, the next morning's reconciliation would miss a row, and by month-end nothing matched up.
The root problem was not ERP polling but the lack of an automated closed loop between "DingTalk approval completed" and "reconciliation system ready." We pull the payment forms from Yida (create supplier, add account, change status) and the price adjustment workflows (supply chain, product, pricing A/B) once they are approved, and write them into the MySQL supplier_account and goods_price tables. The quote workflow's execution date is exposed as a query capability for the reconciliation frontend.
After this, the moment an approval completes, the data lands. Manual export is gone. Price changes move from "effective the next day" to "near real time," and reconciliation errors are reduced to a single-row granularity.
Integration Architecture and Data Flow
The overall architecture is a one-way pipeline: DingTalk Yida (process instances) → integration platform (filter/transform/map) → MySQL reconciliation system. The platform handles three things: incremental pull, subtable expansion and code mapping, and idempotent writes.
┌───────────────────────────┐
│ DingTalk Yida │
│ - Payment forms │
│ (create/add/status) │
│ - Price workflows │
│ (supply/product/A/B) │
│ - Quote workflow │
│ (QUERY_ONLY) │
└───────────────────────────┘
│ v1.0/yida/processes/instances
│ Filter: COMPLETED + approvedResult=agree
▼
┌───────────────────────────┐
│ Integration Platform │
│ - Increment: advance │
│ LAST_SYNC_TIME │
│ - Field transform: │
│ subtable flatten │
│ - Code mapping: │
│ supplier/goods/category │
└───────────────────────────┘
│ INSERT/UPDATE
▼
┌───────────────────────────┐
│ MySQL Reconciliation DB │
│ - supplier_account │
│ - goods_price │
└───────────────────────────┘
Phase 1 — master data: strategies 1/2/5 create supplier accounts first so that supplier codes in subsequent price data can be validated. Phase 2 — goods prices: strategies 3/4/6/8 run in parallel, each distinguished by formUuid. Phase 3 — query: strategy 7 is read-only and triggered on demand.
Interface List
| Strategy ID | Data Object | Direction | Notes |
|---|---|---|---|
| S1 | Supplier account (create) | DingTalk → MySQL | INSERT, status=1 by default |
| S2 | Supplier account (add account) | DingTalk → MySQL | Expand subtable tableField_ks8du8cn |
| S3 | Goods price (supply chain) | DingTalk → MySQL | Two subtables, supplier + product combination |
| S4 | Goods price (product) | DingTalk → MySQL | Same structure as S3, different formUuid |
| S5 | Supplier account (status update) | DingTalk → MySQL | UPDATE, located by account |
| S6 | Goods price (pricing B) | DingTalk → MySQL | Shares target table with S3/S4 |
| S7 | Quote workflow (execution date) | DingTalk → query | QUERY_ONLY, no write |
| S8 | Goods price (pricing A) | DingTalk → MySQL | Needs searchFieldJson to distinguish from S6 |
Implementation Notes
Phased scheduling. The 8 strategies have no hard data dependency on each other, but we recommend running master data (phase 1) before goods prices (phase 2) so that supplier codes are already in the table when price data arrives. Phase 3 queries are scheduled independently, once per day or on demand.
Incremental fields and full backfill. Daily runs use incremental mode: createFromTimeGMT = {{LAST_SYNC_TIME|datetime}}, createToTimeGMT = {{CURRENT_TIME|datetime}}, with the timestamp advanced by the platform after each successful sync. Initialization and data repair use full mode by setting createFromTimeGMT to a historical start point and paginating.
Centralized code mapping. Four mapping categories—supplier code, goods code, category level 1/2/3, and payment method (account type)—must first be validated against the reconciliation system's master data and maintained in a single mapping table rather than scattered in each strategy's scripts.
Subtable expansion. In goods price scenarios, tableField_lhykr1el (supplier adjustment) and tableField_lnedzxul (product adjustment) are two subtables that need buildModel: true to expand. After expansion, business rules combine "supplier + product" into goods_price rows, possibly one-to-many.
Idempotency and exception retries. Every SYNC strategy uses processInstanceId as source_Id and performs idCheck before writing. S5 status updates use UPDATE and are located by account. Retries are tiered by scenario: network/5xx use exponential backoff (3 retries), 429 throttling uses a fixed 60s (5 retries), and 4xx business errors are logged without retry.
Privacy handling. Connection configs (DingTalk appType, systemToken, userId) go through environment variables or a config center. Logs only output processInstanceId and document numbers, never full request bodies.
Best Practices and Lessons Learned
Do not scatter code mapping in scripts. We have seen customers hard-code "payment method → account type" inside each strategy's scripts. When a new payment channel was added, half of the strategies were missed and reconciliation drifted. The reliable approach is to maintain mappings centrally in Qeasy (the integration platform's mapping center) so that all strategies reference the same mapping table and a single change applies globally.
Dual subtables must be explicitly expanded. Supplier adjustments and product adjustments are two subtables. Without explicit expansion in the middleware, the platform only gets nested JSON, and the master/detail row counts will not match after writing. buildModel: true is the switch, but the more critical part is to combine them by business rules after expansion—do not directly take the Cartesian product of the two subtables.
Same formUuid with multiple workflows needs business field distinction. Pricing workflows A and B share the same formUuid. Filtering only by formUuid will mix them up. The reliable approach is to use searchFieldJson with a business field (such as "pricing type=A/B") or to filter again in the AfterSourceInvoke hook.
UPDATE strategies must have a WHERE clause. S5 status update only changes status. Without a WHERE, the entire supplier_account table would be flushed to the same status. The platform must enforce a WHERE field (account) on UPDATE strategies like S5.
QUERY_ONLY strategies should not be mixed with SYNC. The quote workflow query does not write. Modeling it separately enables independent scheduling, independent rate limiting, and keeps SYNC success rate statistics clean.
When to Use Qeasy
When an enterprise needs a long-term automated closed loop between workflow-type data sources like DingTalk Yida and system-type databases like MySQL, ERP, and WMS—rather than one-off scripts—Qeasy is the choice. The platform handles scheduling orchestration, incremental timestamp maintenance, code mapping management, exception retries, and alerting, freeing engineers from glue code so they can focus on field semantics and business rules.