supabase-js count Query Returns null on Failure

A daily integration-health monitor let a failed collection query look exactly like a true zero, so the monitor’s own failure went undetected.

The short version

A daily brief’s integration-health monitor looked identical to “no issues” even when the queries that collect its own monitoring data failed outright. count and data were falling back through ?? 0 and ?? [] without ever checking error, so a genuine zero (healthy) and a failed collection (monitoring not working) were indistinguishable. The fix is simple: aggregate each query’s error, and if there is even one, show it before any other issue.

What it looked like (how it was found)

This wasn’t caught by a real alert slipping through in production. It surfaced during a hardening review, where the reviewer pointed out:

• The daily brief’s “integration health” section — which collects v2 liveness, sync_outbox backlog, and companies delivering creative with no link — had no way to represent a failure of the collecting queries themselves.
• A failed query doesn’t throw; count or data simply become null. That leaves a path where the caller treats it as “zero = no issue.”

This brief was built on the premise that “it’s a one-person operation, nobody checks a dashboard every day,” so it’s designed to proactively push anomalies to Slack. That very proactive channel goes silent in the worst possible way — by saying nothing — exactly when collection fails.

Why

buildIntegrationHealthLines is written as a pure function that assembles the lines sent to Slack.

// src/lib/integration-health.ts:30-40 (before)
export function buildIntegrationHealthLines(
  input: IntegrationHealthInput
): string[] {
  const issues: string[] = [];
  const { senderConfigured, v2Health, outbox, unlinkedDeliveringCompanies } =
    input;

  if (v2Health === "ng") {

The type definition also had no field representing whether collection itself had failed.

// src/lib/integration-health.ts:17-26 (before)
export type IntegrationHealthInput = {
  senderConfigured: boolean;
  v2Health: V2HealthStatus;
  outbox: { pending: number; failed: number; dead: number };
  unlinkedDeliveringCompanies: string[];
};

The calling daily cron looked like this.

// src/app/api/cron/notify/route.ts:234-297 (before, excerpt)
const [outboxPendingRes, outboxFailedRes, outboxDeadRes] = await Promise.all([
  admin.from("sync_outbox").select("id", { count: "exact", head: true }).eq("status", "pending"),
  admin.from("sync_outbox").select("id", { count: "exact", head: true }).eq("status", "failed"),
  admin.from("sync_outbox").select("id", { count: "exact", head: true }).eq("status", "dead"),
]);
const outboxCounts = {
  pending: outboxPendingRes.count ?? 0,
  failed: outboxFailedRes.count ?? 0,
  dead: outboxDeadRes.count ?? 0,
};
// ...
const { data: deliveringCreatives } = await admin
  .from("creatives")
  .select("company_id")
  .in("status", ["配信中", "承認"])
  .not("company_id", "is", null)
  .limit(2000);
// ...
if (deliveringCompanyIds.length > 0) {
  const { data: cos } = await admin
    .from("companies")
    .select("id, name, kimiteras_v2_advertiser_id")
    .in("id", deliveringCompanyIds);
  // ...
}

supabase-js never throws on a failed query; it just returns {data, error, count} as-is. None of these calls capture error, so on failure outboxPendingRes.count simply becomes undefined (→ 0 via ?? 0), and deliveringCreatives or cos become undefined (→ treated as [] via ?? []). From the caller’s side, there is no way to tell that apart from “the target genuinely had zero rows.”

And buildIntegrationHealthLines was designed — as its own comment says — to omit any section that is “zero (= no issue).” If even one collecting query fails, outbox looks like {pending:0, failed:0, dead:0} and unlinkedDeliveringCompanies looks like [], and the function has no way to conclude anything other than “everything’s fine.” A monitoring failure produced input that was byte-for-byte identical to the monitored system actually being healthy.

The fix

Add queryErrors to the type, and have the collector explicitly capture and aggregate each query’s error.

// src/lib/integration-health.ts:17-31 (after)
export type IntegrationHealthInput = {
  senderConfigured: boolean;
  v2Health: V2HealthStatus;
  outbox: { pending: number; failed: number; dead: number };
  unlinkedDeliveringCompanies: string[];
  /**
   * Errors from queries that failed while collecting health data itself (fail-to-green prevention).
   * Always shown, distinct from "no issue," when monitoring data couldn't be collected.
   */
  queryErrors?: string[];
};

// src/app/api/cron/notify/route.ts:237-320 (after, excerpt)
const healthQueryErrors: string[] = [];
const [outboxPendingRes, outboxFailedRes, outboxDeadRes] = await Promise.all([/* ... */]);
for (const [label, res] of [
  ["outbox(pending)", outboxPendingRes],
  ["outbox(failed)", outboxFailedRes],
  ["outbox(dead)", outboxDeadRes],
] as const) {
  if (res.error) healthQueryErrors.push(`${label}: ${res.error.message}`);
}
// ...
const { data: deliveringCreatives, error: delivErr } = await admin
  .from("creatives")
  .select("company_id")
  .in("status", ["配信中", "承認"])
  .not("company_id", "is", null)
  .limit(2000);
if (delivErr) healthQueryErrors.push(`creatives: ${delivErr.message}`);
// ...
const { data: cos, error: cosErr } = await admin
  .from("companies")
  .select("id, name, kimiteras_v2_advertiser_id")
  .in("id", deliveringCompanyIds);
if (cosErr) healthQueryErrors.push(`companies: ${cosErr.message}`);
// ...
const healthLines = buildIntegrationHealthLines({
  senderConfigured,
  v2Health,
  outbox: outboxCounts,
  unlinkedDeliveringCompanies,
  queryErrors: healthQueryErrors,
});

On the receiving side, if queryErrors has even one entry, it’s shown before any other issue.

// src/lib/integration-health.ts:38-53 (after)
export function buildIntegrationHealthLines(
  input: IntegrationHealthInput
): string[] {
  const issues: string[] = [];
  const { senderConfigured, v2Health, outbox, unlinkedDeliveringCompanies } =
    input;
  const queryErrors = input.queryErrors ?? [];

  if (queryErrors.length > 0) {
    const shown = queryErrors
      .slice(0, 2)
      .map((e) => escapeSlack(e))
      .join(" / ");
    issues.push(
      `⚠️ Failed to collect health data (${queryErrors.length}): the checks below may be incomplete: ${shown}`
    );
  }
  if (v2Health === "ng") {

The point is that it’s always shown first. Putting the queryErrors check ahead of every other if tells the reader, up front, that when collection itself is broken, none of the other results (v2Health, outbox) can be trusted either.

The generalizable shape

This shape isn’t specific to this one monitor. Combine “zero = healthy” judgment logic with a call style that falls back to null on query failure, and you get the exact same hole every time.

• For any SDK that returns errors as a {data, error}-style tuple instead of throwing — not just supabase-js — you need to check error before branching on data (or count) alone.
• Any monitor or alert designed to “only fire when there’s an anomaly” always has a third state: the monitor itself failing. Design it around only two values, healthy and unhealthy, and a collection failure gets absorbed into the “healthy” side.
• In an environment like a one-person operation, where the premise of “someone actively watches a dashboard and notices” doesn’t hold, that absorption leads directly to “nobody can notice that monitoring itself is broken.”