Clearing Intermediate Table Data Older Than 90 Days with Qeasy: A Practical Operations Strategy

What Problem Does This Strategy Solve (Scenario and Value)

In a real supply-chain integration project, we implemented bidirectional synchronization of sales outbound orders and sales return orders between WMS/OMS and an ERP. After two months of operation, the intermediate table had accumulated close to a million records. The platform itself did not show obvious performance degradation, but the customer's IT team was concerned — the audit policy required keeping traceable records for 90 days only, and anything older had to be cleaned up, otherwise it would consume storage and slow down report exports.

This is exactly where the "delete data older than 90 days" strategy comes in. It is not a synchronization strategy but an operations/cleanup strategy that runs on Qeasy on a schedule, batch-deleting records from the intermediate table that are older than 90 days. Its value lies in keeping the intermediate table in a state where hot data stays warm and cold data is removed — meeting compliance while not slowing down daily synchronization.

Data Flow and Field Mapping (Source → Intermediate → Target)

The data flow of this strategy is special: it is not the typical "source system → intermediate table → target system" pattern, but rather a closed-loop cleanup of the intermediate table itself. Understanding this is the prerequisite to avoid pitfalls during configuration.

In the source interface's request fields, the object design with target_1 and target_2 is a common pattern on Qeasy: each target represents a business strategy to be cleaned up and can be extended on demand. The response uses _autoFillResponse for automatic population, passing the queried datetime and params downstream.

How to Configure on Qeasy

We use the Qeasy Data Integration Platform to handle this strategy, and usually follow these key configuration points:

1. Create a WebAPI-type source interface: Set the API name to DeleteStrategyData, request method POST, effect to QUERY, and enable autoFillResponse and idCheck so the platform automatically handles the response structure.
2. Declare the cleanup target objects in the request: Treat target_1 and target_2 as a "cleanup object list", with each object containing the business strategy identifier instead of hardcoding document types. When new business modules are added, you only need to copy the target.
3. Use an "empty write operation" for the target interface: Here we use a common technique — the target side does not need to call any business interface, it only needs Qeasy to execute the cleanup parameters passed from upstream, so a placeholder WebAPI with an empty response is sufficient.
4. Separate the schedule crons: Source at 20 8 * * *, target at 23 2 * * *. The two crons are offset to avoid concurrent pressure on the intermediate table at the same minute.

With this setup, the customer's IT colleagues only need to maintain the target object list on Qeasy — no SQL required.

Implementation Steps (Phased Scheduling: Incremental Start / Full Trigger / Frequency)

We generally split the rollout of this strategy into three steps:

• Step 1: Verify the cleanup scope. Run it manually one day before going live, export the count of records to be deleted for the customer to confirm, to avoid accidental deletion. Qeasy's autoFillResponse will populate the hit datetime and params, which can be checked directly.
• Step 2: Small-volume canary. Change the schedule to "once a week" first, observe for one to two weeks whether the intermediate table size, cleanup hit rate, and dependent strategies (such as that day's sales outbound sync) behave normally.
• Step 3: Switch to the production cron. Source 20 8 * * *, target 23 2 * * *, executed every morning. A common pattern among Qeasy customers is: header and body in phases — header kept for 90 days, body kept for 180 days, with this cleanup strategy handling only the header and a separate one for the body; incremental and full-volume dual track — daily incremental cron, plus a monthly full-volume reconciliation to confirm no residue.

Lessons Learned (Pitfalls Recap)

1. A typical mistake is running the cleanup strategy before the business sync. If cleanup runs before that day's sync, the audit trail breaks. The safe approach is to use Qeasy's "dependent strategy" feature to place it after all sync strategies, or set its cron later than the latest sync of the day.
2. Target objects not centrally managed, scattered across multiple strategies. One retail customer experienced this in the early stage, and later consolidated all cleanup objects into a single mapping table, appending a target for new business modules without modifying the main strategy.
3. Ignoring the fields returned by _autoFillResponse. After the cleanup task runs, the hit datetime is not persisted, making it impossible to trace back "which batches were cleaned on which day" during audits.
4. Not considering the idempotency of the empty write operation on the target side. If the source succeeds one day but the target fails due to network jitter, the next day's rerun will request the same IDs — the target side needs deduplication, or the source side needs idCheck filtering.

Applicable and Non-applicable Scenarios

Applicable: Supply-chain integration projects where the intermediate table has a compliance retention requirement, the cleanup action is stable and schedulable, and there is no strong dependency on the business sync chain.

Not applicable: Scenarios with real-time audit traceability requirements, or where the intermediate table is still feeding a long-term BI / data warehouse — cleanup will directly break the downstream chain, and archiving rather than deletion should be used instead.