Carrying a creative_id Revived an Ended Placement

The short version

Duplicate a placement row for a successor contract while leaving the original’s creative_id in place, then mark the original 終了 (ended), and another batch running in the same cron run re-matches the original on that creative_id as part of its search condition and writes the just-ended row back to 申込済 (booked). It is the old row that gets written back, not the newly duplicated one.

What it looks like

Auto-renewal of a six-month contract runs in this order inside a daily cron.

1. Find contracts nearing expiry and create a successor contract
2. Duplicate the placement rows that contract occupied, for the successor
3. Immediately after copying, update the original placement rows to availability: "終了"

The copy spreads the original with { ...p }, deletes only id / created_at / updated_at / loop, and rewrites contract_id, start_date and end_date for the new contract. What implementation review found was that creative_id alone passed straight through this copy.

// placements: duplicate as the successor contract's row, mark the original contract's row ended.
// creative_id/availability carry over = delivery is not interrupted (the original is ended, so
// expireEndedCreatives' creative rollback only affects the successor row).
if (placements.length > 0) {
  const rows = placements.map((p) => {
    const copy: Record<string, unknown> = { ...p };
    delete copy.id;
    delete copy.created_at;
    delete copy.updated_at;
    delete copy.loop;
    copy.contract_id = successorId;
    copy.start_date = period.start;
    copy.end_date = period.end;
    return copy;
  });
  // ...after insert(rows)
  await admin.from("placements").update({ availability: "終了" }).in("id", oldIds);
}

The comment on this code says “the original is ended, so expireEndedCreatives’ rollback only affects the successor row”. That premise was wrong.

Why

Within the same cron run, a separate batch expiring creatives (expireEndedCreatives) runs after contract renewal. It finds creatives whose valid_to has passed, sets status: "停止" (stopped), and if that creative is linked to a placement row, releases the placement to 申込済 (booked) ready for the next submission.

if (cr.placement_id) {
  await admin
    .from("placements")
    .update({ availability: "申込済", status: "予約", creative_id: null })
    .eq("id", cr.placement_id)
    .eq("creative_id", cr.id);
}

This update is designed to hit exactly one row where both id and creative_id match. The problem is that the placement_id on the creatives side still points at the original placement row, not at the ID newly assigned by the copy. Contract renewal never updates the creatives table, so a creative’s link is unchanged by the duplication.

So even after renewal marks the original 終了, the original’s id continues to match the creative’s placement_id, and the original’s creative_id, never cleared during the copy, continues to match the creative’s id. expireEndedCreatives’ .eq("id", cr.placement_id).eq("creative_id", cr.id) matches this original row rather than the new duplicate, writing the row just dropped to 終了 back to 申込済.

A slot’s inventory capacity is computed as the sum of rows whose availability is one of 商談中 / 申込済 / 掲載中 (in negotiation / booked / running). A row that ought to be 終了 comes back as 申込済 within the same day, so the mitigation meant to prevent that double consumption was recurring through the standard operational path — inside the daily cron.

Fixing it

The fix was to null creative_id on both the new duplicate and the original.

copy.contract_id = successorId;
copy.creative_id = null; // the creative belongs to the original period = no dangling ref into the successor
// ...after insert(rows)
await admin
  .from("placements")
  .update({ availability: "終了", creative_id: null })
  .in("id", oldIds);

A creative was submitted and approved against the original contract period, and is not something to carry straight into the successor contract’s placement. Nulling the new duplicate’s creative_id keeps a dangling reference out of the successor. And nulling the original’s creative_id means expireEndedCreatives’ .eq("creative_id", cr.id) no longer matches, so the original is never written back. Continuing to run the creative itself is left to the existing path of re-submitting from the expiry reminder; there is no automatic extension.

Preventing a repeat

When several batches update the same table at different moments, “will one pick up again, through its search condition, a row the other has finished with” cannot be answered by looking only at a state field like availability. Here, changing the state to 終了 was itself correct; the cause was not severing, during the copy, the foreign key that another batch’s search condition reads — .eq("creative_id", cr.id). When you change a row’s state, you also have to check which columns beyond the state other batches use as conditions on that row.