Deterministic event_id + synchronous, failure-resistant ingest

Deciders:

Status: Accepted Date: 2026-06-12 Deciders: Clinton (Clintonrocha98) Supersedes: ADR-0002 — only the wire-contract event_id (random → deterministic) and the ingest mechanism (async job → synchronous). The single-raw-table, schema-on-read, denylist/groups-only, and "no entity modeling" parts of ADR-0002 stand.

Context

The goal was sharpened to: a data lake of the important WhatsApp data, resistant to failures (no important event is lost). Two pillars of ADR-0002 contradict that goal:

Random event_id (randomUUID()). ADR-0002 chose it deliberately and accepted duplicate-on-reemit as "tolerable (rare, e.g. reconnect replay)". The current code proves "rare" is wrong: the collector forwards messages.upsert append (offline buffer) indistinctly from notify, and re-fetches groups.metadata on every reconnect — so re-emit is recurrent, and a random id makes every re-emit a new row. The deterministic id was the right call; the random switch was a mistake.
Async ingest (queued job + 202 before persisting). Production uses QUEUE_CONNECTION=redis, so the 202 is returned before the row is committed. If the job then fails (poison payload, sustained DB outage), the event is lost in failed_jobs while the collector already deleted it from its outbox — silent loss. (Masked today by QUEUE_CONNECTION=sync, but prod is Redis.)

Plus: payloads can contain bytes Postgres jsonb rejects (�, invalid UTF-8); the outbox retries permanently-failing items forever (no dead-letter); and some group events are dropped by a JID-extraction bug.

The full replan and BDD scenarios live in ../plans/0002-fault-tolerant-lake-replan.md.

Decision

1. Deterministic `event_id`, computed on the collector

Restore a per-type UUIDv5 event_id minted on the collector (not randomUUID). It becomes the outbox PRIMARY KEY again, which revives source-side dedup (INSERT OR IGNORE). Combined with the backend firstOrCreate(event_id) + UNIQUE, idempotency is enforced twice — at the origin and at the sink. The envelope stays lean { type, chat_jid, payload }; only the event_id derivation returns to the bot (no other content interpretation, no materialized fields).

Per-type canonical key (dedup by content — identical re-emit collapses, real change is a new row):

Type	Key	Rationale
`messages.upsert`	`(type, remoteJid, key.id)`	`notify`/`append` of the same message collapse → idempotent backfill
`messages.reaction`	`(type, target.key.id, reactor_jid, reaction.text)`	re-emit collapses; emoji change/removal = new row. `senderTimestampMs` is excluded (unstable across re-emit)
`group-participants.update`	`(type, group_jid, canonicalHash(payload))`	identical re-emit collapses; different delta = new row
`groups.metadata`	`(type, group_jid, canonicalHash(meta))`	identical snapshot collapses; roster change = new snapshot
generic / unknown	`(type, chat_jid, canonicalHash(payload))`	safe default: any identical re-emit collapses

canonicalHash = stable JSON (sorted keys, participants sorted by id, volatile fields dropped) before hashing — otherwise reconnect snapshots differ on field order and defeat dedup.

2. Synchronous, failure-resistant ingest (no ingest queue)

The controller delegates to an invokable Action (StoreWhatsAppEventAction) that sanitizes then firstOrCreates synchronously, and returns 2xx only after commit. There is no ingest queue: 2xx means persisted. On a DB error the request returns 5xx, the collector does not delete from its outbox, and retries — no loss.

QUEUE_CONNECTION=redis is app-wide (the moderation classification pipeline and bot-discord enforcement need a real async queue). The WhatsApp lake does not use it — its durability comes from the collector's durable outbox + 2xx-after-commit, independent of the queue driver. A future derived step (domain events → activity) may use the queue, but the lake write stays synchronous.

The ProcessWhatsAppEvent job is removed from the ingest path.

3. Poison-payload tolerance

The Action sanitizes the payload before persisting: strip �, substitute invalid UTF-8 (JSON_INVALID_UTF8_SUBSTITUTE). The column stays jsonb (queryable). The insert never hard-fails on content → always 2xx → collector never loops, raw (minus meaningless control bytes) preserved.

4. Outbox resilience (collector)

The sender classifies the HTTP response: 5xx/network/408/429 → retry; 401/403 → retry + alert (recoverable misconfig, e.g. desynced secret); 422/400 → move to a local dead-letter (outbox_dead, preserved, out of the active loop) + alert. Overflow → alert; never auto-discard. Adds a fetch timeout (AbortController).

5. Minimal channel security

The HMAC signs ${eventId}.${rawBody} (the dedup key becomes tamper-proof). The shared secret env names are aligned across repos with fail-loud on persistent 401. Replay window, route throttle, body-size limit and secret rotation are deferred to a dedicated hardening phase.

6. Backfill

Keep forwarding messages.upsert append (now-idempotent reconnection backfill). Keep messaging-history.set denylisted (bulk, low marginal value; companion-device history is bounded anyway).

Consequences

Positive

No loss, no duplicates: double idempotency + 2xx-after-commit + poison tolerance + dead-letter.
Simpler backend: the queued job is deleted; ingest is a thin synchronous Action.
No schema change: event_id uuid UNIQUE already fits UUIDv5; dev starts fresh (migrate:fresh).

Negative / deferred

The collector is slightly less dumb: it derives the event_id and canonicalizes snapshots (minimal payload interpretation, not modeling).
Deep historical backfill is unavailable (companion-device history is bounded; messaging-history.set stays off).
Security hardening beyond the minimum (replay window, throttle, body limit, rotation) is deferred.
Governance/LGPD/retention explicitly out of scope for this cycle.

Review trigger

Same as ADR-0002: revisit when the data team defines the metrics worth keeping — at which point derived processing (and the reserved queue) comes into play.