Duplicating a Placement Without Ending the Old One

The short version

Where an occupied inventory row is duplicated to create the successor contract’s row, the remaining-capacity sum keeps counting both the old and new rows unless the original is set to “ended”. Every automatic renewal piles up phantom capacity consumption, which eventually surfaces as “the slot should be free and I cannot book it”.

What had been implemented

The commit implementing the daily batch that auto-renews subscription-style ad slots (one “loop” has a capacity that several companies can occupy) created a successor contract for the next period each time a contract expired, and duplicated the occupying placements rows as the successor contract’s rows.

// Duplicate placements as the successor contract's rows (do not interrupt delivery).
// id and audit columns get new values.
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; // strip the embedded join
    copy.contract_id = successorId;
    copy.start_date = period.start;
    copy.end_date = period.end;
    if (copy.start_month != null) {
      copy.start_month = period.start.slice(0, 7);
    }
    return copy;
  });
  const { error: plErr } = await admin.from("placements").insert(rows);
  if (plErr) {
    // A failed inventory-row copy risks a delivery outage = surface it in red
    // (the contract itself stands, so continue).
    await notifySlack(
      `🔴 自動更新: placements 複製に失敗（契約 ${successorId}）→手動確認 ${slackLink("/admin/loops", "枠を開く")}`
    );
  }
}

After a successful copy there is no step changing the original row’s status. The original contract is updated to completed right after, but that concerns the contracts table; on the placements (slot occupancy) side a new row is simply added and the old one stays.

Why

This loop’s remaining capacity is the sum of placements rows whose availability is one of 商談中 / 申込済 / 掲載中 (in negotiation / booked / running). The body of the RPC that actually reserves a slot is this.

select coalesce(sum(units), 0) into v_used
  from public.placements
  where loop_id = p_loop_id
    and availability in ('商談中', '申込済', '掲載中');
if v_loop.capacity - v_used < p_units then
  return null;
end if;

The successor row created by the copy inherits the original’s availability as-is. So if the original contract’s slot was 掲載中 (running), the successor is inserted as 掲載中 too. If the original row has not been moved to a state outside the capacity sum, such as 終了 (ended), sum(units) counts both rows.

One renewal costs only one extra slot of capacity, but this contract auto-renews every six months, so each renewal adds another “past row left un-ended” and another increment of phantom consumption. The loop’s capacity does not change, so the more renewals accumulate, the smaller the capacity actually available becomes.

Fixing it

Update the source row to 終了 only when the copy succeeded. When it fails, leave the original row (the safe branch that does not stop delivery).

const { error: plErr } = await admin.from("placements").insert(rows);
if (plErr) {
  // A failed inventory-row copy risks a delivery outage = surface it in red
  // (the contract itself stands, so continue).
  // Do NOT end the original row (stopping it with no duplicate cuts delivery instantly
  // = the safe side is to leave things as they are).
  await notifySlack(
    `🔴 自動更新: placements 複製に失敗（契約 ${successorId}）→手動確認 ${slackLink("/admin/loops", "枠を開く")}`
  );
} else {
  const oldIds = placements
    .map((p) => p.id as string | undefined)
    .filter((v): v is string => !!v);
  if (oldIds.length > 0) {
    await admin
      .from("placements")
      .update({ availability: "終了" })
      .in("id", oldIds);
  }
}

The copy and the ending update sit inside the same conditional, closing “end the original only when the copy succeeded” into one place. Ending the original while the copy failed would lose the slot with no successor row, stopping delivery on the spot. This fix ensures that in either outcome only one of “a double-counted row” or “an interrupted delivery” can happen, never both.

Making it not recur

The sources behind this article go only as far as the fact that the fix was flagged in code review on the same day as the implementation commit and applied; what monitoring or periodic verification was added afterwards is not included. Only the structural point can be made.

If you adopt a design of “duplicate an occupied row to transfer the right”, the success of the copy and the status change of the original have to be treated as one transactional unit. Implement only the copy and leave tidying up the original to a separate step or PR, and the aggregation logic can only see “the duplicated row and the original as two ordinary rows with the same availability value”, quietly miscounting by however much tidying was skipped.