SQLのbackfillでEXISTSが1つだけだと is_pilot の条件漏れが起きる

結論

既存データへ一度だけ流す UPDATE ... WHERE EXISTS (...) 形式のbackfillで、判定根拠になりうる条件が複数あるのに EXISTS を1本しか書かないと、もう一方の条件だけで「本物」と判定されるべき行が対象から漏れる。 このケースでは「契約を持つ会社」だけを実証実験（pilot）扱いにしており、契約を結ばず広告素材だけを預けている会社が除外されずに残った。pilotフラグは顧客向け自動メールの送信先を絞り込む唯一の判定材料として使われているため、フラグが立たなかった会社には本番相当のリマインドメールがそのまま送られる経路になっていた。

症状

キミテラスportalには、本番の広告主と区別すべき「実証実験中の会社」を companies.is_pilot で管理する仕組みがある。is_pilot = true の会社は admin KPI・対応待ちキュー・顧客向け自動メールのすべてから除外される。この区分を導入した際、既存データに対して一度だけ流すbackfillのUPDATE文が書かれた。

-- 修正前（導入コミット時点）
update public.companies c
set is_pilot = true
where c.kind = 'advertiser'
  and exists (select 1 from public.contracts k where k.company_id = c.id);

「2026-07-11時点で契約を1件でも持つadvertiserは全て実証実験」という前提でこのSQLは書かれていた。ところが実際のデータには、契約をまだ結んでいないが広告素材（creatives）だけを預かっている会社のパターンが存在した。このSQLはそうした会社を素通りし、is_pilot は false のまま残った。

原因

is_pilot は、顧客向けの自動メール全種の送信対象を絞り込む唯一のゲートになっている。src/lib/pilot.ts の getPilotCompanyIds が is_pilot = true の会社IDを集め、src/lib/customer-reminders.ts 側でそれを除外リストとして使う。

// src/lib/customer-reminders.ts（修正後の時点でも変更なし）
let pilotList: string;
try {
  pilotList = pilotNotInList(await getPilotCompanyIds(admin, { failClosed: true }));
} catch (e) {
  console.error("[customer-reminders] pilot companies read failed (skip all):", e);
  return result;
}

ここでの failClosed: true は「pilot一覧の読み取り自体に失敗したら、不明なまま実証相手に送るより全種スキップする」という意味であり、読み取りが成功した場合の判定そのもの（＝backfillでどの会社が true になっているか）は保証しない。つまりこの実装は「IDの集合が取れなかった場合」には強いが、「集合の中身が最初から欠けている場合」には何も検知しない。

実際にbackfillが対象として漏らした「契約なし・素材のみ」の会社は、is_pilot が false のまま、以後の .not("company_id", "in", pilotList) という除外フィルタをすべて素通りする。この会社に紐づく契約が将来作られてリマインド対象の期間に入れば、エラーも例外も発生せず、ごく普通の正常系として本番相当のメールが実証相手に飛ぶ。backfillの対象漏れは、起きたときにログもアラートも残らない種類の欠陥だった。

直す

修正は、EXISTS を1本追加して OR で結合するだけだった。

-- 修正後
update public.companies c
set is_pilot = true
where c.kind = 'advertiser'
  and (
    exists (select 1 from public.contracts k where k.company_id = c.id)
    or exists (select 1 from public.creatives cr where cr.company_id = c.id)
  );

コミットのコメントには「現DBの広告主は実証範囲。素材だけの会社（実写広告5社パターン）も含めないと素材期限リマインドが実証相手に飛ぶ」と明記されている。この一文の通り、is_pilot を「本番とみなせる根拠が1つでもあるか」という意味で設計したなら、根拠の種類ごとに EXISTS を追加して OR で広げる必要があった。AND で絞る側の条件（c.kind = 'advertiser'）と、OR で広げる側の条件（「本物だと示す根拠」）を同じ WHERE 節の中で混在させるとき、後者を1本だけ書いて満足してしまうのがこの欠陥の形だった。

逆側の設計判断として、契約も素材も持たない純粋なリード（商談中の会社）はpilotにしない、という除外もこのSQLには残っている。これは「将来の本契約候補を誤って本番KPIから恒久的に除外しない」ためで、backfillの対象を広げる側だけでなく、広げすぎない側の判断も同じ1文のWHERE節に同居している。

再発防止

このbackfillは一度きりのデータ移行用UPDATE文であり、このコミット自体に自動テストは追加されていない。契約だけを見ていた最初の実装も、文法的には正しく動き、対象行が0件になるような壊れ方もしない。「構文エラーにならない」「例外を投げない」という形では検知できない欠陥だったため、今回はコードレビューでの指摘（Reviewer M-1）が唯一の発見経路になった。

今後、新しいパターンの「実証相手だが本番データに近い会社」が増えたときにこの一度きりのbackfillが再実行されるわけではない。src/app/admin/companies/[id]/page.tsx の管理画面にある is_pilot チェックボックスを人が手動で立てる運用に委ねられている。つまりこの修正は「今あるデータの穴を塞いだ」だけであり、「判定条件に新しい根拠の種類を追加し忘れる」という同じ形の見落としを構造的に防ぐ仕組みにはなっていない。複数の根拠をORで束ねて1つの真偽値を作る設計を書くときは、「根拠の種類を列挙し切ったか」を一度きりのSQLであっても見直す価値がある。