Feishu Expense Reimbursement to MySQL: A Single-Strategy Integration Tutorial

What This Strategy Solves (Scenario & Value)

A Feishu approval expense reimbursement form carries over forty fields, plus two embedded sub-forms (a budget detail table and an expense detail table), while the downstream MySQL reimbursement table only accepts flat fields. In one of our projects, a customer wanted every newly submitted reimbursement form pulled into the financial staging database every five minutes for BI and reconciliation. The real challenges were three: how to flatten nested forms, how to map business-partner codes, and how to handle placeholder timestamps when an approval is still in progress. We delivered this strategy on the Qeasy (轻易云) data integration platform, compressing a 1:N approval form into a single flat table.

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

Source: Feishu approval API /open-apis/approval/v4/instances, queried with the form's approval_code and a start_time/end_time window. Middle layer: Qeasy's field-mapping expression engine, where CASE, COALESCE, FROM_UNIXTIME and similar functions perform transformations. Target: MySQL reimbursement table, primary key id, with serial_number / instance_code / bfn_num as business deduplication keys. Writes use REPLACE INTO in batches of up to 200 rows.

Key field mapping:

Business meaning	Source field	Target field	Mapping type	Transform rule
Approval serial number	serial_number	serial_number	DIRECT	Pass-through
Approval instance ID	instance_code	instance_code	DIRECT	Pass-through
Unique ID	bfn_num	bfn_num	DIRECT	Dedup key
Reimbursement type	widget17131584834230001_text	reimbursement_type	DIRECT	Pass-through
Loan situation	4 candidate widgets	loan_situation	TRANSFORM	COALESCE first non-null, fallback otherwise
Budget department	widget17579334192410001.0.widget17580115872700001_text	budget_department	DIRECT	First row of budget array
Current payment amount	widget17579334192410001.0.widget17612731842700001	current_payment_amount	DIRECT	First row of budget array
Expense department	widget17579337089770001_widget17592232346890001_text	expense_department	DIRECT	Selected row of expense table
Business partner code	widget*	employee_id	TRANSFORM	CASE by customer/supplier/employee/other
Application payment amount	widget*	application_payment_amount	TRANSFORM	Refund→refund amount; otherwise→payment amount
Approval end time	end_time	end_time	TRANSFORM	0→placeholder date; else ms→datetime

How to Configure on Qeasy

Create a Feishu source connector pointing to /open-apis/approval/v4/instances. Hard-code approval_code and use {{CURRENT_TIME}}000 for start_time/end_time so the platform injects the scheduled time automatically.
Create a MySQL target connector with batchexecute, enable idCheck: true on primary key id, and use REPLACE INTO for writes.
Drag a field-mapping node onto the strategy canvas: pass-through mappings reference {{widget...}} directly; nested array access uses dot notation such as {{widget17579334192410001.0.widget...}} to lock to the first row; selected rows in the expense table use underscore notation such as {{widget17579337089770001_widget...}}.
Centralize code mapping: turn the business-partner → employee_id logic into a reusable CASE expression under Qeasy's "shared mappings" so other strategies can reference it without duplication.
Add a timestamp fallback on end_time: IF({{end_time}}=0, '1000-01-01 00:00:00', FROM_UNIXTIME(FLOOR({{end_time}}/1000))).

Implementation Steps

Phase 1: Incremental starting point. Run a one-time backfill of the last seven days of historical forms to verify the code mapping and the flattening logic. After that, switch the schedule to incremental mode with a rolling start_time/end_time window.

Phase 2: Full backfill trigger. Manually trigger a full reload inside Qeasy and compare the target row count against the source instance count. Any mismatch usually points to forms whose expense table has no selected row.

Phase 3: Schedule frequency. In production, set the cron to */5 * * * * and add a small offset on start_time so boundary records are not missed.

Phase 4: Monitoring. Enable Qeasy's built-in alerts on success rate, latency, and record count. Two consecutive empty runs should trigger human investigation.

Pitfall Review

Wrong row in a nested array. A common mistake is to reference widget17579334192410001.xxx directly, which returns the entire JSON string and breaks the pipeline. Always pin the first row explicitly with widget17579334192410001.0.xxx; only later switch to a sub-table mapping if multi-row expansion is required.
Empty selected row in expense table. When a user does not select a row, Feishu returns an empty string, which leaks dirty data into MySQL. Allow NULL on nullable target columns and add a pre-check that filters empty selected rows.
Scattered code mapping. We have seen customers embed the same CASE expression inside every single strategy, so changing a supplier code means hunting across dozens of places. A common pattern among Qeasy users is to centralize code mappings in a shared mapping table or function library.
end_time=0 placeholder. In-progress approvals return end_time=0, which downstream BI would otherwise interpret as 1970. A 0-value fallback is mandatory; use a business-agreed placeholder date (such as 1000-01-01) instead of NULL so subsequent status filters stay simple.
Side effects of REPLACE INTO. Covering rows by primary key means that if any non-critical field is later corrected, the old value is silently overwritten. The dual-track approach mitigates this: run */5 * * * * incremental writes day-to-day, and run a monthly full reconciliation that compares discrepancies as a separate workstream.

Applicable and Non-applicable Scenarios

Applicable: Lightweight financial scenarios with stable form structure, fewer than 50 fields, flat-to-single-table writes acceptable, and minute-level reconciliation latency, such as expense reimbursement and purchase payments. Not applicable: Scenarios that need to retain multiple detail rows in MySQL (such as multi-row budget persistence), that require strong historical-version traceability, or where downstream already uses a lakehouse that prefers raw nested JSON. In those cases, switch to a master-detail table design or sync directly into a data warehouse.