Purchase Order Line Deletion Check and DingTalk Alert: A Single-Strategy Deep Dive

What This Strategy Solves

Purchase order lines get deleted over and over, but nobody is watching. After a manufacturing enterprise rolled out a new system, the business unit kept reporting mismatched line data. The root cause was rarely wrong data entry; it was the fact that PO lines were being deleted and edited repeatedly during purchasing, change, and closing. If nobody notices these anomalies in time, they pollute downstream settlement and reconciliation.

This strategy uses the deletion-count statistics from MySQL as a governance probe: once the count crosses a threshold, it pushes a DingTalk robot message to a designated group or user, triggering manual intervention. The source is a MySQL aggregation query, and the target is a DingTalk work notification (group robot or specified users). The whole chain is delivered through Qeasy (the 轻易云 data integration platform) in a private deployment.

Data Flow and Field Mapping

Data Flow: MySQL (statistics source) → Qeasy (query + assembly) → DingTalk (message delivery).

Key SQL snippet on the source side (desensitized):

• Joined tables: PO header / PO line / material / line extended attributes.
• Filters: line audited, document creation date ≥ start date, factory whitelist, deletion failure flag not Y, closure status not Y.
• Output: per-line accumulated deletion count times.
• Paging: limit :limit offset :offset, bound by Qeasy to avoid SQL injection.

Middle layer and target side: When times > 30, call DingTalk topapi/message/corpconversation/asyncsend_v2 to push a Markdown message keyed by msgKey=sampleMarkdown to the configured userIds.

Field mapping table:

How to Configure in Qeasy

1. Source platform MySQL: choose WebAPI / select / POST. The main parameter main_params must correspond one-to-one with :limit and :offset in the main SQL. Enable idCheck to avoid duplicate pushes.
2. Target platform DingTalk: choose WebAPI / execute / POST. The path is topapi/message/corpconversation/asyncsend_v2. Fields robotCode, userIds, msgKey, msgParam follow the standard.
3. Message assembly: build msgParam with _function CONCAT(...). Use now() for the database time. Make sure the copy explicitly says "over 30 lines, no auto handling, manual review needed," so on-duty engineers do not mistake it for an automated fix.
4. Private deployment note: keep robot code and userIds inside the tenant credential store of Qeasy, not hard-coded in scripts, so rotation is easy.

Implementation Steps

Phase 1: incremental baseline

• Set the SQL start date to the business go-live date, for example po_create_date >= '2025-01-01', so historical dirty data does not flood the alert channel and cause a "boy who cried wolf" effect.
• Narrow the factory whitelist to the 2–3 active sites first; add new ones later to avoid false positives.

Phase 2: full-coverage trigger

• Once steady, switch the SQL to a rolling window (for example last N days or by update_time) and control batch size with paging parameters.
• The threshold of 30 is a starting point, not a truth. Tune it later based on the false-positive rate.

Phase 3: scheduling

• Source cron: 5 14,15,16,17 * * * (5 minutes after the afternoon hours).
• Target cron: 10 14,15,16,17 * * * (5 minutes later than source, so stats are settled).
• Avoid business peaks such as 9:00 and 14:00 to reduce pressure on the source database.

Pitfalls and Lessons

• SQL placeholder binding forgotten: concatenating strings directly into :limit triggers syntax errors or full-table scans. The safe pattern is to let Qeasy do parameter binding and keep main parameters strictly aligned with placeholders.
• Hard-coded threshold causes noise: 30 is a starting point, not the final value. Make the threshold a configurable parameter in Qeasy, then recalibrate after two weeks of running.
• Hard-coded DingTalk userIds: after someone leaves, the message is sent to an empty account. A common countermeasure is to centralize userIds in Qeasy's encoding mapping module for unified maintenance and periodic sync.
• Source and target cron collide in the same minute: source not finished yet, target already starts pushing, and the message ends up as "0 lines." Offsetting the two crons by 5 minutes was the simplest and most effective fix in this project.
• Ambiguous Markdown copy: "handled" versus "manual review needed" is one word, but the on-duty reaction is completely different. The copy must clearly say "this is an alert, not an automated action."

Applicable and Non-Applicable Scenarios

Applicable: PO lines are frequently deleted/edited, document volume is high, and no one is watching. You need a lightweight governance probe, not a business writeback. Not applicable: scenarios that require strong business actions such as auto-locking orders or line-level rollback, or where the threshold needs dynamic learning and multi-source causal analysis.