upsertでtarget_schools列ごと上書きすると複数placementで値が交互にフリップする

結論から

契約（contract）レベルの列に、配信枠（placement）レベルの値をそのまま書くと、1契約に複数の配信枠があるときだけ値が交互にフリップする。 外部の配信APIが送ってくる対象校の集合は「今回配信した配信枠の対象校」でしかなく契約全体の対象校ではないのに、それを契約のtarget_schools列へupsertでそのまま上書きしていた。複数の配信枠を持つ契約だけが、どちらかが再配信されるたびに別の配信枠の対象校で上書きされ、[A校]⇄[B校]と入れ替わっていた。

症状

1契約に複数の配信枠（A校枠＋B校枠）がある広告主で、効果レポートの対象校数（advertiser-metricsのtargetSchoolCount）が常にどちらか片方欠けていた。原因が見つかる前のcontractsへのupsertは次のようになっていた。

// partner-delivery.ts（修正前）
await tx
  .insert(contracts)
  .values({
    portalContractId: c.portalContractId,
    advertiserId,
    status: contractStatusValue,
    startedAt: c.startedAt ?? new Date(),
    endedAt: c.endedAt,
    monthlyFeeJpy: c.monthlyFeeJpy ?? 0,
    targetSchools: c.targetV2SchoolIds,
    createdBy: null,
    updatedBy: null,
  })
  .onConflictDoUpdate({
    target: contracts.portalContractId,
    set: {
      advertiserId,
      status: contractStatusValue,
      startedAt: c.startedAt ?? new Date(),
      endedAt: c.endedAt,
      monthlyFeeJpy: c.monthlyFeeJpy ?? 0,
      targetSchools: c.targetV2SchoolIds, // ← ここが毎回の上書き
      updatedAt: new Date(),
      updatedBy: null,
    },
  });

c.targetV2SchoolIdsは、外部APIから届いた今回のリクエストが運んできた値そのものだ。A校枠の配信が届けば[A校]、その後B校枠が再配信されれば[B校]が、同じcontracts行のtarget_schoolsへそのまま書き込まれる。1配信枠しかない契約では問題にならないが、複数配信枠を持つ契約だけが、直近に処理された配信枠の対象校だけを残し続けていた。

原因

契約（contracts）は1行、配信枠（ads）は複数行という親子関係なのに、親の列に子1件分の値をそのまま書いていた。レイヤ違いである。target_schoolsは本来「この契約でこれまでに配信対象になった校の集合」を表すべき列だが、実装は「このリクエストで届いた配信枠の対象校」という部分集合をそのまま上書きする形になっていた。1契約に配信枠が1つしかない間は部分集合＝全体集合なので症状が出ず、複数配信枠を持つ契約が現れて初めて表面化する。

直し方

既存値との和集合で「これまでに配信対象になった校の集合」を維持する方式に変えた。最初の実装案はSELECTで既存値を読み、JS側で配列をマージしてからupsertする形だったが、レビューで原子性の指摘が入った。配信は「承認直後の即時キック」と「日次cron」という2つの経路から並行して来るため、SELECTした直後にもう一方の経路が先に書き込んでしまうと、マージ結果が古い値を基準にしたlost updateになり、修正したはずの上書き症状が低頻度で再発する。そのためSELECTの段階を無くし、ON CONFLICTのDO UPDATE SET内でSQL側のjsonb併合として和集合をその場で計算する形に原子化した。

// partner-delivery.ts（修正後）
// target_schools は**和集合で保つ**。portal の targetV2SchoolIds は
// 「今回配信した placement の対象校」であり契約全体の対象校ではない。毎回上書きすると、
// 1 契約に複数 placement（A校枠＋B校枠）がある広告主で、どちらかの再配信のたびに
// [A]⇄[B] とフリップし、K1 効果レポートの対象校数（advertiser-metrics の
// targetSchoolCount）が常にどちらか片方欠ける。契約レベルの列に placement レベルの値を
// 書くレイヤ違いなので、既存値と併合して「これまでに配信対象になった校の集合」を維持する。
// ⚠ 併合は ON CONFLICT の SET 内で SQL 側が行う（原子性）。
//   select→TS 併合→upsert だと、portal 配送の 2 入口（承認直後の即時キック＋日次 cron）
//   の並行実行で lost update が起き、修正した症状が低頻度で再発する。
//   縮小（契約から校を外す）は反映されない累積集合＝ads の掃除とは非対称
//   （表示・KPI 用 Read Model のため許容）。
const mergedTargetSchoolsSql = sql`(
  select coalesce(jsonb_agg(distinct t.value), '[]'::jsonb)
  from jsonb_array_elements_text(
    coalesce(
      case when jsonb_typeof(${contracts.targetSchools}) = 'array' then ${contracts.targetSchools} else '[]'::jsonb end,
      '[]'::jsonb
    ) || excluded.target_schools
  ) as t(value)
)`;

setの中でtargetSchools: c.targetV2SchoolIdsだった行をtargetSchools: mergedTargetSchoolsSqlに差し替えるだけで、以降の挙動は「既存のtarget_schools（コンフリクト前の行）」と「今回excludedとして渡ってきた値」をjsonb_array_elements_textで展開しjsonb_agg(distinct ...)で重複無く束ね直す、という一度のSQL文で完結する。JS側でのマージも追加のSELECTも不要になった。

テストは「別のplacementが交互に届いても和集合が保たれる」ことと、「同じ集合の再送では重複しない」ことの両方を確認している。

// partner-delivery.test.ts
it("target_schools は再送で**和集合**を保つ（placement 単位の配信で相互上書き＝フリップしない）", async () => {
  const first = baseInput(); // targetV2SchoolIds: [schoolA]
  await deliver(first);

  const second = baseInput();
  second.ads[0].portalPlacementId = "cccccccc-cccc-4ccc-8ccc-cccccccccc03";
  second.ads[0].v2SchoolId = fx.schoolB;
  second.contract!.targetV2SchoolIds = [fx.schoolB];
  await deliver(second);

  const [c] = await sql<{ target_schools: string[] }[]>`SELECT target_schools FROM contracts`;
  expect([...c.target_schools].sort()).toEqual([fx.schoolA, fx.schoolB].sort());

  // 同じ集合の再送でも重複しない（冪等・distinct）。
  await deliver(second);
  const [c2] = await sql<{ target_schools: string[] }[]>`SELECT target_schools FROM contracts`;
  expect(c2.target_schools).toHaveLength(2);
});

再発防止

和集合挙動を固定するRLS回帰テストを追加し、誰かが将来「シンプルな上書き」に戻そうとしてもこのテストがすぐ落ちるようにした。あわせてAPIの契約文書にも「target_schoolsは累積和集合であり、縮小（契約から校を外す）はこの値には反映されない」という非対称性を明記している。表示・KPI用のRead Modelとしては累積和集合のままで問題ないが、配信の実体（どの校に今広告が出ているか）の掃除は別ロジック（ads側）が担うという役割分担も、同じ文書に書き残した。