Purchase Order Close/Terminate: MySQL to DingTalk Message Alert Sync with Qeasy
What This Strategy Solves
In a real project, we ran into a concrete pain point: at a manufacturing client, when purchase orders in the MOM system were closed, terminated, or deleted, buyers and procurement org heads often did not notice until reconciliation or a supplier complaint came back. Reaction time was unacceptably late.
We used the Qeasy data integration platform to do one focused thing: pull these three event types out of MySQL in near real time and push them as Markdown notifications into DingTalk groups. Within minutes, the right people receive a fully contextualized alert. It sounds simple, but it involves incremental sync, multi-table joins, Markdown assembly, and org-based routing. Get any step wrong and you either miss messages, scramble recipients, or spam the wrong leaders.
Data Flow and Field Mapping
Data flow: MySQL (MOM) → Qeasy middleware → DingTalk work notification API.
The source is the qyy_pur_message table, a flat structure where each row corresponds to one pending notification. The middleware joins four tables: dingtalk_user (job number → DingTalk userid), mt_po_header and mt_po_line (close/terminate metadata), and mt_po_line_attr (item description, model, plan tracking number). The target is the DingTalk topapi/message/corpconversation/asyncsend_v2 endpoint; each source row triggers one independent message send.
Key field mapping:
| Source field | Origin | Target field | Transform notes |
|---|---|---|---|
| id | Primary table PK | Incremental cursor | Increment anchor, prevents resends |
| pur_no, pur_line_no | Primary table | msgParam.text order/line | Direct pass-through |
| ITEM_DESC / ITEM_MODEL / MTONO | Join mt_po_line_attr | msgParam.text item/model/plan no | ATTR_NAME = ITEM_DESC, ITEM_MODEL, PLAN_NUM |
| name | Join dingtalk_user | msgParam.text buyer | Job number → DingTalk display name |
| rk_status | Primary table | msgParam.text status | CASE: COMPLETED→done, RELEASED→issued, else→unknown |
| message | Primary table | msgParam.text hint | Branch by rk_status, different business copy |
| userid | SQL concat | userIds | Branch by orgid, defines recipient scope |
| now() | System time | msgParam.text notify time | Server time at send moment |
| — | Constant | robotCode / msgKey / msgParam.title | Fixed values for bot and template |
How to Configure It on Qeasy
In Qeasy, this strategy is modeled as a source integration flow plus a target integration flow.
Source configuration: API type WebAPI, effect QUERY. Put the main SQL into main_sql under otherRequest, bind :limit and :offset to main_params. Use a.create_time >= '{{LAST_SYNC_TIME|datetime}}' for incremental filtering; Qeasy automatically advances the timestamp on each schedule.
Target configuration: effect EXECUTE, method POST, API path goes into the URL field. Build msgParam with _function CONCAT as a JSON string. This is a common pattern we see at Qeasy customers: centralized template management. All template fields live in one place, so future tweaks touch only this block. robotCode and msgKey are constants; userIds is {{userid}} directly.
Implementation Steps
Stage 1: Seed the incremental cursor. On first setup, set LAST_SYNC_TIME to a sensible backfill point (for example, midnight on the deployment day) so historical unnotified events get pushed. After that, the 8-minute schedule takes over.
Stage 2: Full-volume trigger. Early in rollout we usually schedule a manual full run: verify recipient scope, copy formatting, and rate limits on the bot. Once clean, switch to scheduled mode.
Stage 3: Production scheduling. Source cron is */8 * * * *, target is */10 * * * *. Source runs slightly faster than target, leaving a small processing window. Qeasy supports a dual-track pattern natively: incremental for daily use, full-trigger for replay.
Stage 4: Monitoring and reconciliation. Reconcile "new source messages" vs "successful DingTalk sends" daily; investigate any drift beyond threshold.
Lessons Learned from the Field
Pitfall 1: Putting now() in the source SQL. On our first attempt, notification time lived in the source SELECT. After messages queued for 8 minutes, every one showed the same stale time. The safe pattern: now() belongs in the target _function CONCAT, taking the time at the actual send moment.
Pitfall 2: Join order producing cartesian blow-up. mt_po_line_attr holds multiple ATTR_NAME rows. Without filtering ATTR_NAME in the join, you get a cartesian product and duplicate messages per line. We now enforce: multi-attribute joins must each be independent, and each join condition must pin ATTR_NAME.
Pitfall 3: Hard-coding userids in SQL. Initially we embedded org-leader DingTalk userids inside CASE expressions. New hires or transfers silently stopped receiving alerts. The fix: externalize people mappings to a config table, change SQL to a join, so changes only touch the config table.
Pitfall 4: Missing ELSE in CASE branches. When rk_status holds a value not in the enumeration, SQL returns NULL, and DingTalk JSON shows literal "null". Standard: every CASE must include an else fallback.
Pitfall 5: Wrong Markdown newline. DingTalk Markdown needs \n (two spaces + newline); a bare \n collapses into one paragraph. We hit this on site once and learned the hard way.
When It Fits and When It Does Not
Fits: relational databases have an event-message table that needs near-real-time push to IM or OA; recipient scope varies by org; messages need joined rich context.
Does not fit: recipient scoping requires complex role-based dynamic computation with approval chains (this strategy does not converge permissions); nor should it replace IM as a message bus for business systems. It is for reminders and notifications only.