Resigned Employee Account Disable Alert: Practical Integration Solution from MySQL Multi-Source Aggregation to DingTalk Message Push

What Problem Does This Strategy Solve (Scenario & Value)

In a retail/manufacturing enterprise, HR, the Sihua system, Kingdee, MOM, and the quotation system each maintain their own employee status records. "An employee has resigned, but the corresponding account in another system has not been disabled" is one of the most common issues IT teams get flagged on during audits. In the past, this relied on manually running SQL comparisons every day, resulting in a high miss rate and a broken accountability chain. We used the Qeasy Data Integration Platform to build a strategy: from MySQL, a multi-source join finds "accounts that have been disabled but not synced to DingTalk," then formats each one into a Markdown message and pushes it to designated DingTalk recipients. The value is turning "people finding data" into "data finding people," with unified, traceable alert semantics.

Data Flow and Field Mapping (Source → Middle Layer → Target)

The overall flow is MySQL → DingTalk. On the source side, UNION ALL aggregates records from five categories—Sihua, Kingdee, MOM, quotation, and resigned personnel—into a unified structure. The middle layer normalizes field names in Qeasy. The target side calls DingTalk's topapi/message/corpconversation/asyncsend_v2 to push Markdown messages.

Note: this strategy does not involve _findCollection encoding mapping. All mappings are DIRECT / CONSTANT / TRANSFORM, which is the biggest difference between "alert-type sync" and "master data sync."

How to Configure in Qeasy

1. Source connector: Choose MySQL's "WebAPI select" mode and paste main_sql verbatim. Leave :limit and :offset as pagination parameters bound to main_params input fields; Qeasy will inject them automatically at schedule time.
2. Target connector: Choose the DingTalk "robot/work notification" API. Fill in robotCode and the robot code as constants—do not scatter them inside the strategy. One of Qeasy's common patterns is "centralized encoding mapping management": place such constants at the platform-level parameter area, and let strategies only reference variables.
3. Field mapping: All fields except msgParam are DIRECT. For msgParam, use the "expression" type, select _function CONCAT, and concatenate multiple {{}} variables with fixed strings into JSON.
4. Rate limiting and pagination: Keep the < N count check in each sub-query; it ensures a single push does not exceed DingTalk's rate limit. Qeasy adds :limit :offset as a secondary pagination safety net.
5. Observability: In Qeasy's "Run Logs," mark system and job_number as key fields, so when an alert fails you can directly locate which system and which record is involved.

Implementation Steps

We recommend three phases: incremental starting point → full-volume trigger → scheduling frequency, running on dual tracks:

• Phase 1 (Incremental starting point, ~1 hour window): Use the date(update_time)>=CURDATE() and hour(update_time)=hour(now()) in the resigned personnel sub-query as the "incremental anchor." On first go-live, widen the update_time window to "last 24 hours" for one run to verify message body assembly, recipient list, and DingTalk rate limit.
• Phase 2 (Full-volume trigger, baseline verification): Use Qeasy's "manual trigger" to run a full pass, checking each pushed Markdown for field completeness and proper escaping (quotes inside JSON, double newlines). The most common pitfall here is Markdown's \n and \n (two spaces + newline) being eaten by the platform—be sure to verify on a real DingTalk client.
• Phase 3 (Scheduling frequency): Source at 28 12,18 * * 1-6, target at 30 12,18 * * 1-6, twice on weekdays at noon and evening. The source runs 2 minutes earlier than the target—this is a lightweight version of the "staged header/body" approach common in Qeasy: reserve execution time for the source, let the target only handle pushing, with clear responsibility separation, and on failure you can distinguish "cannot fetch" from "cannot push."

Pitfall Review

1. Column counts in UNION ALL branches don't match—only reported at runtime. A safe practice is to add a comment at the end of the SQL noting the standard column count per branch, and use Qeasy's "Data Preview" to confirm all 5 branches return 10 columns.
2. DingTalk userids are hardcoded as JSON arrays inside SQL; recipient changes require editing SQL. A more engineering-friendly approach is to extract userid into a "per-system-type" configuration table, use JOIN in SQL to fetch them, and let Qeasy only handle system-based routing.
3. now() is directly embedded in the message body, causing "alert content and push time don't match" confusion. We recommend separating "event time" (source update_time) from "push time" (now())—do not mix them.
4. Chinese punctuation and two-space newline characters in Markdown get escaped by DingTalk's server. Always verify on a real device during testing—don't just inspect the assembled JSON in logs.
5. DingTalk robot/app code leakage. robotCode is sensitive credential. In Qeasy, it should go into the credential center; the strategy only references the variable name, avoiding plaintext in logs.

Applicable and Non-Applicable Scenarios

Applicable: Account governance, offboarding compliance, "status inconsistency" alerts across multiple business systems, and any scenario where you need to turn database alerts into IM messages to designated recipients. Not applicable: Two-way synchronization, scenarios requiring historical version retention, or master data sync that needs encoding mapping (e.g., materials, customers, suppliers). For those, please use Qeasy's "Master Data Sync" strategy template.