Process DELETE scheduled changes before CREATE_UPDATE at the same instant

Date: 06-08-2026 Author(s): Jayden Vicarey Source: EM-3691 rate-guard team discussion

Status

accepted

Context

Promotions and standard-rate scheduled changes may legally start and end at the same instant ("touch" is allowed; overlap is not). When two scheduled_changes rows share the same changing_at, ProcessScheduledChangesJob processes them in the order returned by ScheduledChangeService::getDuePendingScheduledChangesForTenant. That query previously ordered only by changing_at, so same-instant rows had non-deterministic (typically insertion/id) order.

If a CREATE_UPDATE (e.g. a new promotion or a standard-rate change) runs before a DELETE (e.g. ending the previous promotion) at that instant, rates can briefly violate standard_rate < promotional_rate < 100, and storefront/order paths can observe the wrong rate.

Decision Drivers

• Allow touching boundaries without a forced 1-minute gap
• Guarantee deterministic processing when changing_at values coincide
• Prefer a query-level fix over broadening rate-conflict validation buffers
• Keep the job itself a thin runner over the service's ordered collection

Considered Options

Order by changing_at, then DELETE before CREATE_UPDATE

Secondary sort on operation so deletes/reverts run before creates/updates for the same timestamp.

Enforce a minimum temporal gap in rate-conflict validation

Reject schedules that share an instant (e.g. require ≥ 1 minute separation).

Sort in PHP after fetch

Leave SQL unordered beyond changing_at; reorder the collection in the job or service in memory.

Decision Outcome

Chosen: Order by changing_at, then DELETE before CREATE_UPDATE.

Justification: Touching instants are a desired product rule. Ordering deletes first at the query boundary makes the runner safe without inventing an artificial gap, and keeps a single source of truth for processing order.

Implementation: orderByRaw("CASE operation WHEN 'delete' THEN 0 WHEN 'create/update' THEN 1 ELSE 2 END") after orderBy('changing_at').

Consequences

Positive

• Same-instant promo end + promo start, and promo end + standard-rate change, process safely
• Rate conflict guards can treat exclusive-end / inclusive-start (touch) as allowed
• Job characterisation remains "process in service return order"; service owns ordering

Negative

• Ordering knowledge lives in SQL CASE and must stay aligned with Operation enum values
• Unknown future operation values sort last (ELSE 2) until the CASE is extended

Neutral

• Legacy discount/cashback DELETE support remains until those processors/endpoints are removed (see ADR 0003)