Sales Order History Cleanup Strategy: Implementing a "Delete Data Older Than 90 Days" Policy with Qeasy

What this strategy solves (scenario and value)

In a sales order integration pipeline, intermediate stores and shadow tables grow relentlessly. After six months in production at one retail enterprise, three reconciliation tables — "Retail Sales Order Integration (Business Line 1)," "Liton Retail Sales Order Integration (Business Line 1)," and "Sales Order Integration (Distribution Business Line 2)" — had accumulated more than a hundred days of historical records. Log queries slowed down, and downstream sync strategies suffered from ever-increasing scan times.

The "delete data older than 90 days" strategy does not move any business data; instead, it trims the pipeline itself by removing closed historical documents from the reconciliation tables. Its value is not in functional complexity but in keeping the data lifecycle under control: incremental runs become lighter, troubleshooting becomes more focused, and compliance audits only target live data.

Data flow and field mapping (source → intermediate → target)

What is special about this strategy is that both its "source" and "target" reside inside Qeasy's own intermediate layer, and do not directly read or write either the source or target system.

The source api: DeleteStrategyData is a query-type interface that locates candidate records; the target api: write no-op is an execution-type interface whose real deletion is performed by Qeasy per object. target_2 is marked optional so additional cleanup objects can be added later without rebuilding the strategy.

How to configure it on Qeasy

On the Qeasy data integration platform, the typical configuration points for a cleanup-type strategy are:

1. Strategy classification: Place it under the System Maintenance module in the strategy list, not under Business Documents. It does not participate in forward business flow and only runs as background maintenance.
2. Source metadata: Use the DeleteStrategyData interface with type=QUERY, effect=QUERY, method=POST, marking it as "select by condition" rather than "fetch incremental."
3. Request objects: Explicitly declare target_1, target_2, and target_3 as object types, with target_2 set to is_required: false for future extension. This follows a common Qeasy customer pattern of "centralized code-mapping management": objects of the same type are cleaned up in a single strategy rather than scattered across a dozen sync strategies.
4. Target metadata: Use "write no-op" as a placeholder API. The key is to enable idCheck: true so the platform knows to match by the id dimension.
5. Platform ownership: Both source and target reference the same platform_id (Qeasy itself) to avoid being misread as a cross-system sync.

Implementation steps

We split the rollout into three phases; the order matters.

Phase 1: Full trigger (rollout day)

Manually trigger once to clean up data accumulated before go-live. A common approach is to temporarily widen the time window — for example, deleting everything older than 180 days first, observing the result, and then tightening it to 90 days. Qeasy's autoFillResponse: false is critical here: we don't want the platform to auto-fill the response structure, which could cause pending-deletion records to flow back as new documents.

Phase 2: Incremental start (the next day at 02:23)

The target crontab: 23 2 * * * fires daily at 02:23. This is the typical Qeasy on-site pattern of "run maintenance during the off-peak window": cleanup happens during the business low period and does not compete with sync resources around noon.

Phase 3: Steady-state scheduling (source-side prep)

The source crontab: 3 8 * * * runs at 08:03 each day, pre-selecting candidate records generated the previous day so that the 02:23 deletion job can match them directly. The two crons are staggered, forming a "select in the morning, act at night" rhythm consistent with the "dual-track of incremental and full" engineering habit.

Phased scheduling summary:

Pitfall retrospective

1. Treating the cleanup strategy as a sync strategy in the business chain A typical mistake is slotting it into a sequence A→B chain of 15 business strategies, where the cleanup action is dragged down by business dependencies. The safe approach is to mark it under the System Maintenance module and isolate it from the business orchestration diagram.

2. Choosing the wrong time-anchor field number: datetime looks generic, but the semantic of "time field" differs across reconciliation tables: some use the document date, others use the sync ingestion timestamp. Picking the wrong field can wipe out live, unclosed documents. This is where projects commonly fail. The safe approach is to confirm with the business owner exactly what "90 days" means for each target_x.

3. Forgetting to enable idCheck If idCheck: false, the platform may match against a different field and produce unpredictable deletion results. This seemingly trivial toggle, once misconfigured, can cause "accidental deletion"-level incidents.

4. Cleanup running too frequently Running once a day is fine, but if three target_xx objects run simultaneously and each overlaps with other strategies, the database gets hammered around dawn. The safe approach is to stagger the schedule and keep cleanup tasks serial.

5. No audit log retained Once a cleanup strategy goes live, the first question when something breaks is "what did we delete yesterday?" It is recommended to reserve an audit field in the response structure on Qeasy, persisting the daily deletion count and the earliest/latest timestamps so that incidents can be traced back.

When to use it and when not to

Use it for: reconciliation intermediate tables where business closure is stable, historical data has no analytical value, and compliance retention is shorter than 90 days; shadow tables, temporary reconciliation tables, and batch-replenishment logs. Do not use it for: primary business tables that act as the single source of truth, financial documents subject to compliance audits (typically retained for 3–5 years), or sync tables that are read in real time by other BI systems.