DETECTED/NOT_DETECTED: SwitchBot Webhooks Double-Count

Conclusion

SwitchBot’s PIR presence sensor sends two webhooks per detection: DETECTED when a person is sensed, and NOT_DETECTED when the detection ends. Both are recorded as-is in events(type=‘presence’), so any count of “presence events” that filters only on type reports roughly double the real number of detections. Every count-based widget on the signage dashboard was affected.

The failure mode

The system has a pattern (the signage dashboard’s presence-sensor feature) that receives SwitchBot webhooks and writes them into events(type=‘presence’). The dashboard shows several count-based widgets: today’s detection count, hourly/daily trends, a heatmap, sensor history, and a 24-hour detection count.

Every one of these count aggregates was returning roughly double the real detection count. The raw log itself, and the liveness check for “when did this sensor last respond,” were both working correctly — only the count queries were wrong.

Cause

SwitchBot’s PIR sensor is specified to send two webhooks with different states: DETECTED the instant a person is sensed, and NOT_DETECTED the instant the detection ends. The webhook endpoint (apps/web/app/api/sensors/switchbot/webhook/route.ts) validates the payload and then simply calls recordPresenceEvent — there’s no branch on detectionState. Both DETECTED and NOT_DETECTED are written into events(type=‘presence’) identically.

This is intentional by design: both states need to be kept in the raw log, and both are used by the liveness check that reports when a sensor last responded (see the comment in packages/db/src/queries/presence-detection.ts).

The problem was in the query that counts detections. Before the fix, getTodayPresenceCount (packages/db/src/queries/presence-today.ts) filtered only on type, the target class, and the target date — detection_state was never part of the condition.

// Before the fix (as of 083c3043)
const where = and(
  eq(events.type, "presence"),
  sql`${events.payload} ->> 'class_id' = ${classId}`,
  sql`(${events.occurredAt} at time zone 'Asia/Tokyo')::date = ${jstDate}::date`,
);

Since one detection produces two rows (DETECTED and NOT_DETECTED) in events, this WHERE clause counts both. A single pass in front of the sensor registers as two detections in the aggregate. The other aggregates (hourly, daily, heatmap) used the same type = 'presence'-only filter, so they doubled in the same way.

The fix

The fix was to stop cramming the sensor’s operational needs (both states required in the raw log and liveness check) and the counting needs (one detection = one countable row) into the same condition. A shared predicate, countsAsPresenceDetection, was extracted to define, in one place, which rows are allowed to be counted.

// packages/db/src/queries/presence-detection.ts
export const countsAsPresenceDetection = sql`coalesce(upper(${events.payload} ->> 'detection_state'), '') <> 'NOT_DETECTED'`;

The condition counts any row where detection_state is not NOT_DETECTED. DETECTED rows count, of course, and rows that have no detection_state at all (backfilled data from the old PoC period, written before the state was tracked) also pass through and count — deliberately erring on the side of not discarding them.

This condition was added only to the WHERE clause of the counting queries.

// After the fix (as of b51f46b3)
const where = and(
  eq(events.type, "presence"),
  countsAsPresenceDetection,
  sql`${events.payload} ->> 'class_id' = ${classId}`,
  sql`(${events.occurredAt} at time zone 'Asia/Tokyo')::date = ${jstDate}::date`,
);

The same countsAsPresenceDetection predicate was also added to the hourly/daily/heatmap aggregates (packages/db/src/queries/event-stats.ts). The raw ingestion path (webhook → recordPresenceEvent) and the liveness check (reading the most recent detection time) were left untouched — both still need both DETECTED and NOT_DETECTED, so there was no reason to change them.

The fix is verified by a test that seeds five rows in sequence — DETECTED → NOT_DETECTED → DETECTED → NOT_DETECTED → no state — and checks the resulting count.

// packages/db/__tests__/rls/presence-today.test.ts
await seedPresence(fx.schoolA, CLASS_A1, 0, 1, "DETECTED");
await seedPresence(fx.schoolA, CLASS_A1, 0, 2, "NOT_DETECTED");
await seedPresence(fx.schoolA, CLASS_A1, 0, 3, "DETECTED");
await seedPresence(fx.schoolA, CLASS_A1, 0, 4, "NOT_DETECTED");
// A row with no state (e.g. old PoC backfill) counts as one detection.
await seedPresence(fx.schoolA, CLASS_A1, 0, 5);

const n = await withTenantContext(
  db,
  ctxA(),
  (tx) => getTodayPresenceCount(tx, CLASS_A1, today),
  APP,
);
expect(n).toBe(3);

Of the five seeded rows, two are NOT_DETECTED. The correct count is the three rows that are not NOT_DETECTED — two DETECTED rows plus the one state-less row — not the five rows that merely match type = 'presence'.

Making it not recur

This kind of double-counting is easy to miss in tests or code review if the only thing being checked is type = 'presence'. type only describes the kind of event; it says nothing about the webhook-level fact that a single physical detection can arrive as multiple rows. Whether a row is “countable” needs to be an explicit condition, kept separate from the type condition.

Extracting countsAsPresenceDetection as a shared constant means every count query (today’s count, hourly, daily, heatmap) now goes through this one place. As more sensor types are added beyond SwitchBot, how many webhooks a single detection produces may differ per sensor — so any new aggregate should first check whether this kind of “countable row” condition exists before assuming a plain type filter is enough.