Feishu Approval → MySQL: Daily Payment Request Sync Strategy Tutorial

What This Strategy Solves

In the finance shared service center of a retail enterprise, Feishu approval handles daily payment requests, while the ERP and accounting systems only recognize structured payment records in MySQL. Once the two sides drift apart, month-end reconciliation becomes a manual, line-by-line ordeal. We use the Qeasy Data Integration Platform to pull these forms from Feishu into MySQL: dedupe by primary key, land payment-type codes per ERP conventions, and flatten the budget details. The business side reconciles against a single view.

Data Flow and Field Mapping

Data flow: Feishu Approval → MySQL. The source is nested JSON (header + budget-detail array); the target is a flattened tp_fs_daily_payment_application. Key field mapping:

Only the first row (index 0) of the budget-detail array is taken; subsequent rows are not expanded.

Configuring in Qeasy

In the Qeasy Data Integration Platform, this is a classic "WebAPI pull + SQL batch write" combination.

• Source: pick the Feishu Approval connector, API /open-apis/approval/v4/instances, hard-code approval_code with the approval template ID, use {{CURRENT_TIME}} variables for the incremental window on start_time/end_time.
• Target: pick MySQL connector with batchexecute, enable primary-key check (idCheck: true), write via REPLACE INTO for upsert, queue cap 200 rows/batch.
• Centralized code mapping: keep the payment-type text → ERP code CASE inside the field mapping rather than scattered across scripts — one change applies everywhere. This is a common pattern among Qeasy customers.
• Staged header / detail writes: since the target is a single flattened table, the first budget row is mapped directly onto header fields. If you later need to expand multiple rows, sync the header first and write the detail table separately to avoid full-batch rollback on a single failure.

Implementation Steps

1. Incremental anchor: source crontab fires per configuration; on first go-live, manually pin start_time to backfill history, then roll forward with CURRENT_TIME.
2. Full trigger: manually run a full backfill in Qeasy, compare target row counts against a Feishu export; delta should be zero.
3. Schedule frequency: target crontab * * * * * (every minute), source window aligned so approval state changes land in 1–2 minutes.
4. Data validation: pick 3–5 payment types, walk through the CASE branches, confirm code mapping and amount-source logic (the actual_pay branch).
5. Rollout and monitoring: gray-launch one approval template for a week before opening all payment types.

Pitfalls and Lessons

• Typical mistake: hard-coding widget field IDs. Feishu widget IDs change when forms are adjusted — expose them as mapping variables in Qeasy so they can be swapped in one place.
• Ignored extra budget rows: source is an array, target takes only row 0. If finance needs per-row accounting, switch to a separate detail table instead of flattening onto the header — this is exactly where the staged header/detail pattern applies.
• end_time = 0 dirty data: an unfinished approval returns 0, which becomes 1970 if converted naively. Add a guard: 0 → placeholder (e.g., 1000-01-01 00:00:00), or downstream reports will treat it as "ancient records".
• Missing fallback in multi-source coalesce: if COALESCE has no fallback, all-null inputs return NULL and the target's NOT NULL constraint fails. Always include a 'fallback' literal.
• REPLACE INTO side effect: it overwrites the entire row, including historical fields. In incremental mode, either confirm the target allows overwrite, or switch to INSERT ... ON DUPLICATE KEY UPDATE to update only changed columns.

When to Use / Not Use

Use when: Feishu approval is the entry point, target is a single MySQL table, and only the header + first budget row are needed as an accounting snapshot. Do not use when: budget rows must be accounted per line, source widget IDs change frequently without versioning, or the target must preserve history instead of overwriting.