# Outbox #3477 idempotency audit

Status: **minimal audit for the immediate repair**. Issue: #3477.
Companion docs: [outbox-redesign.md](outbox-redesign.md), [outbox-rollout-gate.md](outbox-rollout-gate.md).

The incident effects were DB-internal, but the outbox is the generic post-commit dispatcher for record-triggered automation. The immediate code repair is still the small queue-mechanics change, but `OUTBOX_PULL_WORKER` may flip only after the current-tree consumer map proves that any live external-effect consumers on this path are protected or scoped out.

## Classification Rules

- **Deterministic set:** safe under redelivery when the right-hand side is a pure function of the frozen event snapshot.
- **Create:** safe only with a stable business key and a database `UNIQUE` constraint. Read-then-write without a unique index races concurrent redelivery.
- **State-dependent or external:** not solved by the queue repair. Before `OUTBOX_PULL_WORKER` flips, current live consumers on the outbox path must have a consumer-specific idempotency/dedup contract or be excluded from the rollout.

Non-obvious traps still apply:

- A field set is deterministic only when its right-hand side is deterministic. `now()`, sequence values, append, increment, or "if empty then set" are not free.
- Autonumber must short-circuit before `Insert`; deduping after insert can still burn a sequence value.
- Append-style audit rows and insert-style permission grants are not naturally idempotent, but they are not immediate #3477 blockers unless tied to this after-save path.

## Immediate Effect Table

| Effect | Current code surface | Class | Required action |
|---|---|---|---|
| Outbox event processing | `nexuscrm-events/outbox_service.go`, `nexuscrm-persistence/outbox_repository.go` | Queue control | Replace per-batch barrier with independent pull workers; reuse `ClaimEvent`, `processing`, stale reclaim, and `MaxRetryAttempts`. |
| Same-record ordering | current `slotForEvent` hash in `outbox_service.go` | Queue control | Add `RecordKey` to outbox rows and make eligible selection skip younger same-key rows while older rows are not terminal. Stale older rows are retried first, not bypassed. |
| Failure retry bookkeeping | `processEventAtomic` / `handleClaimedEventFailure` | Queue control | Do not add a generic handler timer for this incident. On handler failure or DB lock timeout, roll back handler work to the post-claim savepoint and write retry/failed state before releasing the attempt; if the attempt transaction itself fails, keep the visible claim heartbeat-protected while detached bookkeeping lands. |
| Conversion-time related-record create (the proven #3477 duplicate) | record-create flow/action path; exact metadata column must be confirmed | Create | Use natural-key find-or-create/upsert backed by a DB `UNIQUE` index; assert redelivery creates exactly one row and does not burn autonumber. |
| After-save field-capture updates (fields/lookups set from the event snapshot) | flow metadata must be read before marking safe | Deterministic set if RHS is snapshot-derived | Confirm RHS is deterministic; add redelivery tests proving the second delivery leaves the same value. If not deterministic, add a key or rewrite to deterministic assignment. |
| Current external-effect consumers | record-triggered flows can reach email, metadata-backed webhook/action rows, connector push, or host-registered provider calls | External | For rollout, prove each live consumer is protected by an existing durable idempotency/attempt contract or keep it off the pull-worker path. Do not claim exact-once network delivery. |

## Current Queue Facts To Reuse

The existing repository already owns most of the queue state:

- `ClaimEvent(ctx, id)` atomically transitions a row from `pending` or stale `processing` to `processing`; pull workers commit that claim before handler work so other workers can skip it.
- `GetPendingEvents(ctx, limit)` includes stale `processing` rows using `LastModifiedDate`.
- Pull workers refresh `LastModifiedDate` while a claimed handler is still running, so stale reclaim means "heartbeat stopped", not merely "handler is slow".
- `IncrementRetry` returns the row to `pending` with a higher retry count.
- `UpdateStatus` marks terminal `processed` or `failed`.

Therefore the immediate design should not add a second lease system. The only new queue fact required for replacing in-memory slot ordering is `RecordKey`.

## Minimal Eligible-Event Shape

The implementation can keep the current "select candidate, then claim by id" shape, as long as candidate selection is DB-owned and claim remains the durable transition:

```sql
SELECT Id, EventType, Payload, RetryCount
FROM _System_OutboxEvent e
WHERE
  (e.Status = 'pending'
   OR (e.Status = 'processing' AND e.LastModifiedDate < :stale_cutoff))
  AND (e.Status <> 'pending'
       OR e.RetryCount = 0
       OR e.LastModifiedDate < :retry_cutoff)
  AND (
    e.RecordKey IS NULL
    OR NOT EXISTS (
      SELECT 1
      FROM _System_OutboxEvent older
      WHERE older.RecordKey = e.RecordKey
        AND (
          older.CreatedDate < e.CreatedDate
          OR (older.CreatedDate = e.CreatedDate AND older.Id < e.Id)
        )
        AND older.Status NOT IN ('processed', 'failed')
    )
  )
ORDER BY e.CreatedDate ASC, e.Id ASC
LIMIT :limit;
```

Then call the existing `ClaimEvent(ctx, id)` for the selected row. Losing a race is benign: the worker loops and claims another eligible row.

Before flipping the new worker on, drain the existing backlog or backfill `RecordKey` with the same `recordKeyFromPayload` logic used by enqueue. Otherwise a legacy row without `RecordKey` and a new row with `RecordKey` could touch the same record without being grouped.

Run `EXPLAIN` before landing the final query, but do not introduce lanes, owner tokens, or new lease columns for this repair.

## Deferred Inventory

These effects should stay documented as future work, not as part of the #3477 gate:

| Effect family | Why deferred |
|---|---|
| General email/webhook/provider ledger | Not part of the queue repair. Current live consumers still gate `OUTBOX_PULL_WORKER` unless an existing consumer-specific mechanism already proves safety. |
| General `(handler_id, source_event_id, effect_key)` ledger | Useful for future external effects, unnecessary for DB creates with natural keys. |
| Append audit rows / permission grant inserts | Need their own idempotency audit if they become part of the redelivery surface. |
| Metadata `CreateRecord` actions outside the named failing flow | Same create-if-absent rule applies, but do not block the incident repair on a global sweep. |
| Generic handler timeout | Not needed for the lock-wait incident path and increases duplicate external-effect risk before those effects have dedup. |
| Lanes/fairness/dedicated pools/poison parking | Scale and operations hardening; not required to remove the #3477 batch barrier. |

## Verdict

The immediate repair is smaller than a full outbox platform redesign:

1. DB-owned pull workers with one new `RecordKey` fact.
2. Savepoint-backed retry bookkeeping that survives handler failure or DB lock timeout without releasing the claim epoch.
3. Natural-key uniqueness for the proven duplicate create.
4. Redelivery tests for the named field-set cases.
5. A current-tree external-consumer map before the pull-worker flag flips.

Everything else is deferred until there is current-tree evidence that it participates in the rollout.