Webhook ingest — the single Ecto.Multi that HOOK-06 reduces to.

Mailglass.Webhook.Plug calls ingest_multi/3 after signature verification + tenant resolution pass. One transaction writes:

1. One mailglass_webhook_events row (raw payload + audit)
2. N mailglass_events rows (one per normalized %Event{})
3. N projector updates (only for events whose provider_event_id matches a live mailglass_deliveries.provider_message_id — orphans skip the projector step per Pitfall 4)
4. Status flip on the webhook_event row to :succeeded

Composition (CONTEXT D-15 amended HOOK-06)

Inside Mailglass.Repo.transact/1:

1. SET LOCAL statement_timeout = '2s' (D-29 — DoS bound)
2. SET LOCAL lock_timeout = '500ms' (D-29)
3. Multi.run(:duplicate_check, ...) — deterministic pre-insert lookup against UNIQUE(provider, provider_event_id) inside the same snapshot as the upcoming insert. Per revision B5.
4. Multi.insert(:webhook_event, ...) with on_conflict: :nothing, conflict_target: [:provider, :provider_event_id] — UNIQUE collision is a structural no-op.
5. For each %Event{} in the input list: a. Events.append_multi({:event, idx}, fn changes -> attrs end)

    — function form resolves `delivery_id` lazily by looking up
    `mailglass_deliveries` on `provider_message_id`.

   b. Multi.run({:projector_categorize, idx}, ...) classifies the

    inserted event as matched / orphan / no-event-row (per
    revision W4 flat Multi, no nesting anti-pattern).

   c. Multi.run({:projector_apply, idx}, ...) calls

    `Projector.update_projections/2` only on `:matched`; orphans
    fall through. The outer Multi owns the rollback scope.

6. Multi.update_all(:flip_status, ...) flips mailglass_webhook_events.status = :succeeded + processed_at = Clock.utc_now/0.

Replay semantics

UNIQUE collision on (provider, provider_event_id) is a structural no-op. Per revision B5 (dropped the is_nil(webhook_event.id) heuristic — Ecto's on_conflict: :nothing, returning: true returns the conflict-target row with its existing id, so id is never nil after insert/conflict). The deterministic duplicate signal comes from the :duplicate_check step's pre-insert lookup; Plan 04's Plug returns 200 either way.

Orphan path

A normalized event whose message_id / sg_message_id doesn't match any mailglass_deliveries.provider_message_id is an "orphan" — the webhook arrived before the Delivery row committed (empirical 5-30s race window for SendGrid + Postmark). Per CONTEXT D-15 + Pitfall 4: the mailglass_events row inserts with delivery_id: nil + needs_reconciliation: true AND the projector step is SKIPPED for that event (Projector.update_projections/2 pattern-matches %Delivery{} and would FunctionClauseError on nil).

Plan 04-07's Mailglass.Webhook.Reconciler Oban cron sweeps these orphans and appends a :reconciled event when the matching Delivery later commits (D-18 — append, never UPDATE).

Output shape

{:ok, %{
  webhook_event: %WebhookEvent{},
  duplicate: true | false,
  events_with_deliveries: [{event, delivery_or_nil, orphan?}, ...],
  orphan_event_count: non_neg_integer()
}}

The 3-tuple events_with_deliveries shape (per revision B7) lets Plan 04-04's Plug drive post-commit broadcast without set-difference recomputation: {event, delivery, false} triggers Projector.broadcast_delivery_updated/3; {event, nil, true} skips (Plan 04-07 Reconciler emits :reconciled when the matching Delivery surfaces — broadcasting twice would confuse LiveView subscribers).

Returns {:error, reason} if the transact/1 raises (SQLSTATE 45A01 from append-only ledger violation, statement_timeout firing, etc.).