valid_from Gap Retires Future-Dated Creatives

Conclusion

Right before delivering a new creative to a placement, the code that retires every other “approved” or “live” creative in that same placement_id had no check on valid_from (the start date). That meant a paid, already-reserved creative whose start date hadn’t arrived yet got retired at the same moment as an unrelated creative’s approval, just because it happened to share the same slot.

The scenario this would have caused

A placement is designed to deliver one creative at a time: one placement, one creative. When a new creative is approved, deliverCreativeToPlacement first retires whatever old creative is left in that same placement, then queues the new one for delivery.

// 1. Retire other creatives in the same placement — delivery is always one placement, one creative
await admin
  .from("creatives")
  .update({ status: "停止" })
  .eq("placement_id", opts.placementId)
  .neq("id", opts.creativeId)
  .in("status", ["承認", "配信中"]);

That condition only looks at placement_id and status — valid_from (the creative’s own start date) never enters into it. So if the same placement happens to hold both “a creative that’s been live since today” and “a reservation that doesn’t start until next month,” approving the first one unconditionally retires both.

Because placements here are reserved by taking payment up front, a creative with a future valid_from isn’t just unscheduled — it’s already paid for and reserved, just not live yet. Had this shipped as-is, every unrelated approval anywhere else in the system would have been able to silently retire a future reservation with no notification and no reason given.

Cause

This code treated “shares the same placement_id” as equivalent to “is currently occupying this placement right now.” In reality, it’s normal for a single placement to hold multiple creatives that don’t overlap in time — matching on placement_id alone can’t distinguish between “not yet started” and “live now.”

Whether a creative is currently supposed to be showing is a question valid_from answers, not status. Because the retirement query never looked at valid_from, a single approval — meant to affect only the creative actually being replaced — ended up reaching into an unrelated future reservation.

The fix

The fix added a valid_from check to the retirement condition, limiting it to creatives that are currently supposed to be showing.

const { data: superseded, error: supErr } = await admin
  .from("creatives")
  .update({ status: "停止" })
  .eq("placement_id", opts.placementId)
  .neq("id", opts.creativeId)
  .in("status", ["承認", "配信中"])
  .or(`valid_from.is.null,valid_from.lte.${today}`)
  .select("id, title");
if (supErr) {
  // Abort (fail-closed): proceeding here would silently break the one-placement-one-creative guarantee (B-1).
  return { ok: false, error: `supersede failed: ${supErr.message}` };
}

Restricting retirement to creatives whose valid_from is null or on/before today excludes any future-dated reservation. That reservation gets delivered later by a separate process (deliverScheduledStarts) once its start date arrives, and retiring the then-current creative happens there instead.

Two more things were added at the same time. First, what happens if the retirement UPDATE itself errors: previously the code moved on to delivery regardless of the result. After the fix, an error on retirement aborts the new creative’s delivery entirely (fail-closed) — proceeding anyway would leave two creatives live in the same placement, silently breaking the separate guarantee that each placement shows exactly one creative. Second, every retirement now writes a Slack notification and an audit log entry (logAuditSystem); before the fix, nothing surfaced the outcome of a retirement to anyone.

Preventing a repeat

This was caught during an implementation review (re-review finding N-1), before any creative was being run through this code path in production. It was found by re-reading what .eq("placement_id", opts.placementId) actually narrows down to, and noticing that “shares this placement” and “is currently showing” are not the same claim.

When reviewing code that mutates state, the question to ask is whether the update’s condition narrows its target along the time axis. Conditions that only look at the current structure — placement_id, status — can’t correctly exclude data that has a time offset built into it, like a reservation that starts in the future.