BOM Interface Push Failure DingTalk Notification Strategy Tutorial: End-to-End Configuration from MySQL Exception Log to DingTalk Message

What This Strategy Solves

In a real-world project, we encountered a scenario where a manufacturing enterprise occasionally failed to push BOM master data from its ERP to its MES. The failed records were scattered in a MySQL interface log table, and the business team only discovered a day later that unapproved material codes had prevented BOM distribution, halting the production line. This "BOM Interface Push Failure - DingTalk Notification" strategy transforms exception handling from "passively digging through logs" to "proactive alerting". By business type (41=sync, 43=modify), it pushes the responsible person and the solution to a DingTalk group, so frontline operators receive prompts within minutes.

Data Flow and Field Mapping

Overall pipeline: MySQL interface log table → Qeasy query component → intermediate field mapping → DingTalk group robot webhook.

The source side (MySQL, WebAPI/POST query) filters records from the interface request log table that are "unsuccessful and not recovered within 10 minutes" or "with business type 41/43". Key fields:

Source Field	Meaning	Intermediate Processing	Target Field (DingTalk msgParam)
json_result	Interface error message	Concatenate first Message and BillId	`### Returned Error: {{json_result}}`
business_type	41/43 business code	case when to Chinese	`### Document Type: {{business_type}}`
create_by / real_name	Initiator / name	Join user table for real name	`### Operator: {{real_name}}`
create_time	Creation time	Pass-through	`### Operation Time: {{create_time}}`
userid	DingTalk recipient	Join DingTalk user table, fallback to a fixed account	`userIds` (array)
Solution	Solution text	case when with different hints per business type	`### Solution Hint: {{Solution}}`

The target side calls DingTalk topapi/message/corpconversation/asyncsend_v2, with msgKey set to sampleMarkdown, and msgParam dynamically assembled via a string concatenation function to build title, body and Markdown markers.

How to Configure on Qeasy

In the Qeasy Data Integration Platform, this strategy is split into "source + target" two metadata cards. The source side is declared as a select-type WebAPI, the main SQL statement goes into otherRequest.main_sql, and the main parameter main_params serves as the pagination placeholder (:limit :offset), so the pagination logic is directly handled by Qeasy's built-in paginator, and engineers do not need to write loops.

The target side declares topapi/message/corpconversation/asyncsend_v2, hard-codes the robot code, userIds and msgKey into the request body, and uses Qeasy's _function CONCAT(...) function for template assembly in msgParam. A common pattern among Qeasy customers is to centralize such "dynamic Markdown templates" in the function-style field on the target side for maintenance, avoiding scattering across multiple strategies—so business terminology changes only need to be made in one place.

idCheck is disabled on the source side (deduplicate by business primary key, not by the interface log auto-increment id), and enabled on the target side, ensuring the same failure will not be pushed repeatedly.

Implementation Steps

Phase one: Full trigger. Run manually for the first time to push all accumulated failure records at once, letting the business side confirm the alert text format.

Phase two: Incremental starting point. Align the create_time starting point in the source SQL to the timestamp after the full run, and subsequently only take new records or "unrecovered after 10 minutes" records. In Qeasy, the metadata.number field saves the last maximum id to implement an incremental cursor, which is a typical dual-track approach for incremental and full synchronization.

Phase three: Scheduling frequency. Source crontab set to */29 8-21 * * *, target */30 8-21 * * *, staggered at 29/30 minutes to avoid empty messages sent by the target before the source finishes querying. The working hours cover 8 AM to 9 PM, aligned with on-site production scheduling, and non-working hours rely on the database's own alerting as a fallback to avoid nighttime message flooding.

Phase four: Joint acceptance. Intentionally create a BOM with an unapproved code on the ERP side and observe whether DingTalk receives the Markdown message within two minutes, and whether the operator's name is correct.

Pitfall Review

Do not mix placeholder syntaxes. The :limit :offset in the source SQL is Qeasy's dynamic syntax; do not write ? for convenience, otherwise the paginator will fail directly, and the first page will query the entire table and overwhelm the target side.
userid empty values need a fallback. If the operator is not bound to DingTalk, user3.userid is NULL, and the JSON array concatenated by CONCAT will contain the "null" string, causing DingTalk interface to return illegal parameters. The safe approach is ifnull(user3.userid,''), plus a fixed duty account as a fallback recipient to ensure messages are not lost.
Do not use real line breaks in msgParam. Line breaks in Markdown should use \n (two spaces plus \n). Direct line breaks will be eaten by the DingTalk Markdown renderer, making everything look crammed together.
Do not disable idCheck on both sides. The source idCheck is off and the target idCheck is on, so that "rerunning the same historical failure" only sends once. If both sides are disabled, rerunning will resend historical failures, flooding the group.
Do not double-calculate time zones with now(). The source now() takes the database time zone, and the target msgParam should not do time zone conversion again, otherwise two timestamps will appear at the same time, confusing the business side during reconciliation.

Applicable and Inapplicable Scenarios

Applicable: ERP→MES/PLM interface failure alerts, document push exception notifications, and work group messages that need to be precisely delivered by responsible person. Inapplicable: high-frequency transactional notifications (hundreds of messages per minute will trigger DingTalk rate limiting), scenarios requiring two-way interaction (DingTalk robot group messages are one-way push, replies require separate sessions), and detailed transmission containing sensitive credentials (should go through encrypted channels rather than group robots).