is_pilot Backfill Misses Companies With Only One EXISTS

Conclusion

In a one-time UPDATE ... WHERE EXISTS (...) backfill, if there are multiple conditions that could each justify a “real” verdict but the SQL only checks one EXISTS, rows that only satisfy the other condition get skipped. Here, only companies with a signed contract were flagged as pilot (test) companies. Companies that hadn’t signed a contract yet but already had creatives on file were left out. Because the pilot flag is the sole gate used to filter who receives automated customer-facing email, any company that didn’t get flagged stays on the path to receiving production-grade reminders.

Symptom

The Kimiteras portal tracks “pilot” companies — ones that should be excluded from admin KPIs, pending-action queues, and all customer-facing automated email — via companies.is_pilot. When this flag was introduced, a one-time backfill UPDATE was written to set it on existing data.

-- Before the fix (as introduced)
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);

The SQL assumed that, as of 2026-07-11, every advertiser with at least one contract was a pilot. In reality, some companies hadn’t signed a contract yet but already had creatives (creatives) on file. The backfill passed right over them, leaving is_pilot as false.

Cause

is_pilot is the only gate used to filter out companies from every kind of automated customer email. getPilotCompanyIds in src/lib/pilot.ts collects the IDs of companies where is_pilot = true, and src/lib/customer-reminders.ts uses that set as an exclusion list.

// src/lib/customer-reminders.ts (unchanged by the fix)
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 here means “if reading the pilot list itself fails, skip every reminder rather than risk sending to an unknown pilot company.” It says nothing about whether the set it successfully reads is actually complete. The implementation is defensive against “the ID set couldn’t be fetched,” but has no defense against “the set was incomplete to begin with.”

Any contract-less, creatives-only company the backfill skipped keeps is_pilot = false, so it passes straight through every later .not("company_id", "in", pilotList) filter. If that company later signs a contract and enters a reminder window, nothing throws — it’s treated as a completely normal case, and production-grade email goes out to what is still a pilot partner. A gap like this leaves no log and triggers no alert when it happens.

The fix

The fix was to add one more EXISTS and combine it with OR.

-- After the fix
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)
  );

The commit message spells it out: “Advertisers in the current DB are all pilot-scope. Companies with only creatives (the 5-advertiser creative-only pattern) must be included too, or creative-expiry reminders will reach pilot partners.” If is_pilot is meant to capture “is there at least one reason to treat this as production,” then each kind of reason needs its own EXISTS, OR-ed in. The shape of this defect is exactly that: writing one AND condition to narrow the set (c.kind = 'advertiser') and one OR condition to widen it (evidence of being “real”), then stopping after writing only one of the widening conditions.

The other side of that design judgment is still in the SQL too: pure leads with neither a contract nor creatives are deliberately not marked as pilot, so a future real customer doesn’t get permanently excluded from production KPIs by accident. Both the “widen” and “don’t widen too far” decisions live in the same WHERE clause.

Preventing a repeat

This backfill is a one-time data-migration UPDATE, and no automated test was added for the commit itself. The original version — checking only contracts — was syntactically correct, ran without error, and never produced zero affected rows, so it had none of the failure shapes that “doesn’t throw” or “isn’t a syntax error” checks would catch. The only reason this was caught at all was a code review comment (Reviewer M-1).

Re-running this one-time backfill isn’t how new pilot-like companies get flagged going forward; that’s delegated to a human manually checking the is_pilot checkbox in the admin screen at src/app/admin/companies/[id]/page.tsx. So the fix closed the hole in the data that already existed, but it doesn’t structurally prevent the same shape of mistake — forgetting to add a new kind of evidence to the condition — from happening again. Any time a boolean is built by OR-ing several kinds of evidence together, it’s worth re-checking whether every kind has actually been enumerated, even for SQL meant to run only once.