SwitchBotはDETECTED/NOT_DETECTEDの2通で検知回数が2倍になる

結論

**SwitchBot の人感センサー（PIR）は、1回の検知につき DETECTED（反応）と NOT_DETECTED（反応終わり）の2通の webhook を送る。この2通はどちらも events(type=‘presence’) にそのまま記録されるため、「presence イベントの件数」を type だけで数えると、実際の検知回数のほぼ2倍になる。**サイネージ盤面の検知回数系の表示が、まとめてこの状態になっていた。

症状

このシステムには、SwitchBot の人感センサーからの webhook を受けて events(type=‘presence’) に書き込むパターン2（サイネージ盤面の人感センサ機能）がある。盤面には「本日の検知回数」「時間帯別・日別の推移」「ヒートマップ」「センサー履歴」「24時間の検知数」といった、検知回数を見せる表示がいくつもある。

このうち回数系の集計がすべて、実際の検知回数よりほぼ倍の値を返していた。生ログ自体やセンサーの「直近いつ反応したか」という稼働判定は正しく動いており、件数を数える集計だけがおかしい、という形で現れていた。

原因

SwitchBot の PIR センサーは、人を検知した瞬間に DETECTED、検知が終わった瞬間に NOT_DETECTED という、状態が異なる2通の webhook を送ってくる仕様になっている。webhook の受信エンドポイント（apps/web/app/api/sensors/switchbot/webhook/route.ts）はペイロードを検証したあと recordPresenceEvent を呼ぶだけで、detectionState の値による分岐は無い。つまり DETECTED と NOT_DETECTED はどちらも同じ扱いで events(type=‘presence’) に書き込まれる。

これは意図的な設計である。生ログとして両方を残しておく必要があり、かつ「センサーが直近いつ反応したか」を示す稼働判定にも両方の記録が使われるためだ（packages/db/src/queries/presence-detection.ts のコメント）。

問題は、件数を数える側の集計クエリだった。修正前の getTodayPresenceCount（packages/db/src/queries/presence-today.ts）を見ると、絞り込みは type = 'presence' と対象クラス・対象日だけで、detection_state は条件に入っていない。

// 修正前（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`,
);

1回の検知で DETECTED と NOT_DETECTED の2行が events に入るので、この WHERE はその2行をどちらも数える。人が1回センサーの前を通れば、集計上は2回検知したことになる。他の集計（時間帯別・日別・ヒートマップ）も同じ type = 'presence' だけの絞り込みを使っていたので、同じ形で2倍になっていた。

直す

修正は、センサーの運用上の都合（生ログ・稼働判定は両方必要）と、件数集計の都合（1検知=1件）を同じ条件式の中に押し込まないことだった。共有の条件 countsAsPresenceDetection を切り出し、「件数として数えてよい行」だけをそこで定義する。

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

detection_state が NOT_DETECTED でなければ数える、という条件にしてある。DETECTED はもちろん数えるし、detection_state 自体を持たない行（旧 PoC 期のバックフィルなど、状態を記録する前のデータ）も「捨てずに1件として数える」ために、素通りさせる側に倒している。

この条件を、件数を数める側の WHERE にだけ追加した。

// 修正後（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`,
);

同じ countsAsPresenceDetection を、時間帯別・日別・ヒートマップの集計（packages/db/src/queries/event-stats.ts）の WHERE にも共通で追加している。生ログの保存経路（webhook 受信 → recordPresenceEvent）や、稼働判定（直近の検知時刻を見る処理）の側は変更していない。どちらも DETECTED / NOT_DETECTED の両方が必要な処理なので、触る理由が無い。

修正が効いていることは、テストで DETECTED → NOT_DETECTED → DETECTED → NOT_DETECTED → 状態なし、という5行を投入して確かめている。

// 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");
// 状態を持たない行（旧 PoC バックフィル等）は 1 回として数える。
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);

5行のうち NOT_DETECTED が2行あるので、修正前の条件なら4件（NOT_DETECTED を含む DETECTED 2件＋単独の NOT_DETECTED 2件ではなく、type一致の全5行から考えれば本来は5件になるところを除外ロジックで数えていたはず）ではなく、正しくは「NOT_DETECTED でない行」の3件（DETECTED 2件＋状態なし1件）になる。

再発しない形にする

この種の値の二重計上は、テストやコードレビューで type = 'presence' という条件だけを見ていると気づけない。type はイベントの種類を表しているだけで、「1回の物理的な検知が、記録としては複数行に分かれて入ってくる」という webhook 側の仕様までは表現していないからだ。件数を「数えてよい行かどうか」を判定する条件は、type 条件とは別の軸として明示的に持つ必要がある。

今回それを countsAsPresenceDetection という共有の定数として切り出したことで、件数を数える集計（本日件数・時間帯別・日別・ヒートマップ）は全てこの1箇所を参照する形になった。今後 SwitchBot 以外のセンサーを増やすときも、「1回の検知が何通の webhook に分かれるか」はセンサーごとに違う可能性があるため、新しい集計を書くたびに type 条件だけで件数を数めてよいかを、まずこの条件の有無で確認する必要がある。