payload.adId Ignored, Ad Impressions Stay Zero Forever

When event attribution splits into two mutually exclusive FK paths and the aggregation query only follows one of them, events recorded solely on the other path stay at zero forever.

The short version

When event attribution splits into two mutually exclusive FK paths, and the aggregation query only follows one of them, events recorded solely on the other path stay at zero forever. The advertiser-facing effectiveness report’s impressions/taps aggregation only followed the chain “advertiser → contract → contract content → content → event content_id.” But the ad-reaction events actually produced by signage playback carry no content_id at all — they’re recorded through a separate path with the ad id stored in the event payload’s adId. Following only the content chain meant the advertiser’s impressions and taps were structurally stuck at zero, no matter how many ads were delivered. The fix adds the ad-id attribution as a second aggregation and sums the two paths’ results. Because the two paths’ event sets are mutually exclusive (one carries content_id and never payload.adId, the other the reverse), summing them does not double-count.

Symptom

The advertiser effectiveness report API aggregates a month’s impressions and taps from the events table. The original query joined from advertiser through contract, contract content, and content, down to the event’s content_id.

// advertiser-metrics.ts (before the fix, at 06221f4e, aggregateTotals)
const rows = await db
  .select({
    impressions: distinctOfType("view"),
    taps: distinctOfType("tap"),
    asks: distinctOfType("ask"),
  })
  .from(advertisers)
  .leftJoin(contracts, contractActiveInMonth(advertiserId, monthStart, nextMonthStart))
  .leftJoin(contractContents, eq(contractContents.contractId, contracts.id))
  .leftJoin(contents, eq(contents.id, contractContents.contentId))
  .leftJoin(events, reactionInScope(monthStart, nextMonthStart))
  .where(eq(advertisers.id, advertiserId));

const row = rows[0] ?? { impressions: 0, taps: 0, asks: 0 };

return {
  impressions: row.impressions,
  taps: row.taps,
  asks: row.asks,
  dwellSeconds,
  presence,
};

This query never errors. With no contract, or no event tied to the content, the leftJoin simply comes back empty and a plausible-looking impressions: 0 is returned. But this zero didn’t mean “no reactions” — it meant “nothing was reachable through this path.”

Why

The ad-reaction events sent by signage devices on the ground never populate the content_id column. The event-ingestion code’s own comment spells this out.

// event-ingest.ts (lines 113-119)
if (raw.adId != null) {
  // UUID of the ad currently showing (effective_ads_per_class.ad_id). For the
  // advertiser reach aggregation (F07 user story). events.content_id is an FK
  // to contents, so the ad id can't go there — it's carried in payload instead.
  if (typeof raw.adId !== "string" || !UUID_RE.test(raw.adId)) {
    return { ok: false, message: "adId is invalid." };
  }
  payload.adId = raw.adId;
}

content_id is a foreign key into the contents table, and an ad (a row in the ads table) is not a content. So the ad id never lands in content_id — it’s recorded as a value inside the event’s JSON payload.adId. In other words, the path an event’s attribution actually takes splits into two systems:

• Content path: events.content_id → contents → contract_contents → contracts → advertisers
• Ad path: events.payload->>'adId' → ads.id → ads.advertiser_id

The aggregation query was written to follow only the content path; it never followed the ad path. Because every ad-reaction event signage actually produces is recorded on the ad path, a query that only follows the content path kept the advertiser’s impressions/taps structurally at zero, no matter how many ads were delivered.

The two paths are mutually exclusive. A content-path event carries content_id and never payload.adId. An ad-path event carries payload.adId and never content_id (the ingestion code writes one shape or the other, never both). That exclusivity is exactly what makes the summing fix below safe.

The fix

We added a new aggregation function, aggregateAdReactions, that follows the ad-id attribution, and summed its result with the existing content-path result. The join condition itself wasn’t designed from scratch — an internal operations dashboard already used the identical shape and it worked, so that already-verified join was reused directly.

// advertiser-metrics.ts (after the fix, at e420233f)
const [dwellSeconds, presence, adRow] = await Promise.all([
  sumDwellSeconds(db, advertiserId, monthStart, nextMonthStart),
  countPresence(db, targetSchoolIds, monthStart, nextMonthStart),
  aggregateAdReactions(db, advertiserId, monthStart, nextMonthStart),
]);

// Sum of content path + ad path (mutually exclusive, so no double-counting; see aggregateAdReactions).
return {
  impressions: row.impressions + adRow.impressions,
  taps: row.taps + adRow.taps,
  asks: row.asks + adRow.asks,
  dwellSeconds,
  presence,
};

aggregateAdReactions itself joins events directly to ads on ads.id::text = events.payload->>'adId', counting only reactions to ads belonging to that advertiser.

// advertiser-metrics.ts (after the fix, at e420233f)
async function aggregateAdReactions(
  db: Selectable,
  advertiserId: string,
  monthStart: ReturnType<typeof sql>,
  nextMonthStart: ReturnType<typeof sql>,
): Promise<{ impressions: number; taps: number; asks: number }> {
  const rows = await db
    .select({
      impressions: distinctOfType("view"),
      taps: distinctOfType("tap"),
      asks: distinctOfType("ask"),
    })
    .from(events)
    .innerJoin(ads, adJoinOn)
    .where(
      and(
        eq(ads.advertiserId, advertiserId),
        gte(events.occurredAt, monthStart),
        lt(events.occurredAt, nextMonthStart),
        inArray(events.type, [...REACTION_EVENT_TYPES]),
      ),
    );
  return rows[0] ?? { impressions: 0, taps: 0, asks: 0 };
}

The per-school breakdown (by_school) got the same treatment: aggregateAdReactionsBySchool was added, and the content-path breakdown and the ad-path breakdown are merged by school id. Fixing only the totals without this would leave the totals and the by_school sum disagreeing with each other, so both needed the same fix.

Preventing a repeat

“The aggregation query returns zero” and “that target actually had zero reactions” are not the same fact. When an event’s attribution is split across FK paths by design, you need to separately confirm — whenever a new path is added — that the existing aggregation query was updated to follow it. A path that’s forgotten keeps returning zero silently, with no error and no warning. When fixing it, confirm first whether the existing path’s event set and the new path’s event set are actually mutually exclusive before summing them. Skipping that confirmation and just adding the counts together can, depending on the design, introduce a different kind of double-counting.