# Outbox #3477 repair - pull workers, minimal DB-owned state

Status: **design (minimal repair)**. Issue: #3477.
Companion docs: [outbox-idempotency-audit.md](outbox-idempotency-audit.md), [outbox-rollout-gate.md](outbox-rollout-gate.md), [cascade-delete-lock-scope.md](cascade-delete-lock-scope.md).

## Problem

A delete cascade held a parent row's `FOR UPDATE` lock across child recursion. The after-save outbox worker then amplified one stuck event into a whole-drain freeze because one `ProcessOutbox` tick fans a batch into per-slot channels and waits behind a single `wg.Wait()` barrier. If one slot is blocked on lock contention, every other slot in that batch waits too.

The fix should address the incident mechanism, not solve every future queue concern. Current code already has the important database primitives:

- `_System_OutboxEvent.Status` moves through `pending`, `processing`, `processed`, and `failed`.
- `OutboxRepository.ClaimEvent` atomically updates a pending or stale-processing row to `processing`.
- `GetPendingEvents` already reclaims stale `processing` rows after `OutboxStaleProcessingSeconds` using `LastModifiedDate`.
- `MaxRetryAttempts` already bounds retry attempts.

So the repair should reuse those facts and remove the in-memory batch coupling.

## Locked Semantics

```
Outbox delivery is at-least-once.
Handlers and DB effects must be idempotent under redelivery.
The pull-worker path is gated by OUTBOX_PULL_WORKER until the rollout gate is green.
```

The immediate incident does not require a general external-effect ledger, lane scheduler, fairness scheduler, owner-token lease system, or durable email/webhook sender. Those are deferred as implementation work. They are not a waiver for current external consumers: before `OUTBOX_PULL_WORKER` flips, the live outbox consumer map must prove that email, webhook, connector, or provider effects on this path are already protected by consumer-specific idempotency/dedup semantics or are scoped out of the rollout.

## Immediate Design

### 1. Replace The Batch Barrier With Pull Workers

Start `parallelism` long-lived workers. Each worker loops independently:

```
for {
  ev := nextEligibleEvent(ctx)        // current GetPendingEvents shape, limit 1/small batch
  if ev == nil { sleep(); continue }
  if !ClaimEvent(workerCtx, ev.ID) { continue }  // short visible claim
  processClaimedAttempt(workerCtx, ev)           // heartbeat while running
}
```

Important constraints:

- There is no central dispatcher and no per-tick `wg.Wait()` barrier.
- A blocked event occupies only the worker that claimed it.
- The first durable state change is the existing `ClaimEvent` transition to `processing`; an in-memory queue never owns the "already running" fact.
- Pull workers make the claim visible before handler work and refresh `LastModifiedDate` while the handler runs, so other workers can skip the live attempt and stale-processing reclaim is reserved for stopped heartbeats. Do not add `OwnerToken`, `LockedUntil`, `NextVisibleAt`, or lanes in this repair.
- Do not add a new generic handler wall-clock timeout in this repair. The #3477 lock-wait path already returns from the database; an extra timeout would only increase redelivery surface before external-effect dedup exists.

### 2. Preserve Per-Record FIFO With One New Fact

The old slot hash preserved same-record ordering in memory. Pull workers need that ordering in the database.

Add `RecordKey` to `_System_OutboxEvent`, populated from the same record identity currently used by `slotForEvent`:

```
ObjectAPIName + "/" + Record.Id
```

For new rows with malformed payloads, fall back to the event Id so they do not block unrelated records.

Because this is an additive physical column, all runtime use is schema-tolerant.
On an already-bootstrapped database that restarts before schema apply, ordinary
enqueue uses the legacy column set, `OUTBOX_PULL_WORKER` falls back to the
legacy batch drainer, and backfill is a no-op until `RecordKey` exists. The
generated schema catalog is not sufficient proof that the live table has been
mutated.

`nextEligibleEvent` must exclude an event when any older event with the same `RecordKey` is not terminal (`processed` or `failed`). "Older" means earlier in stable `(CreatedDate, Id)` order. A stale older event is still the head of that record's line: it may be reclaimed and retried, but younger same-record events must not run beside it.

That is the only new queue fact needed for the #3477 repair.

### 3. Do Not Add A Handler Timer; Keep Retry Bookkeeping Live

Do not add a new per-attempt timeout for this incident. The observed stuck path is database lock wait, and the database already returns a lock-wait error. A generic handler timer would make the same outbox event run more often and can duplicate email/webhook side effects before this design adds any external-effect dedup.

The retry-bookkeeping invariant still matters:

- On handler failure or database lock timeout, the attempt transaction rolls back handler writes to the savepoint created after `ClaimEvent`.
- Publish failures write retry/failed state before the attempt transaction returns; transaction/setup/commit failures use detached bookkeeping while the visible claim remains heartbeat-protected.
- A just-failed row is not immediately eligible again in the same pull loop; this preserves the old "next poll" cadence without adding a scheduler column or long backoff.
- Never release the claim epoch before retry/failed bookkeeping is written; that reopens the same-event race under worker concurrency.

### 4. Fix The Proven Duplicate Create

The proven duplicate — a conversion-time after-save effect that auto-creates a related record — is a database-internal create effect. It does not need event-id plumbing or a dedup ledger. It needs the standard database solution:

- choose the current-tree natural key (expected: source lead id, but confirm from metadata/code before implementation);
- add a database `UNIQUE` constraint for that key;
- implement find-or-create/upsert so redelivery short-circuits before `Insert`;
- add a redelivery/concurrent-redelivery test proving exactly one related row is created and no autonumber is burned.

Deterministic field-set effects (after-save captures that set a field or lookup from the frozen event snapshot) need confirmation and redelivery tests, not a ledger, if their right-hand side is a deterministic function of that snapshot.

## Why This Works

The outbox repair isolates unrelated records from a stuck record path. In #3477, the stale after-save fields and the duplicate create effect were on records that did not need to wait behind the delete-cascade's record path. Once workers pull independently, free workers can claim and process those unrelated records while another worker is blocked or retrying the delete-related work.

Events that touch the record being deleted are different: this design does not make those succeed. They may fail with lock-wait or missing-record errors instead of freezing the whole drain. Making the cascade itself faster is the separate `Delete` lock-scope track below.

External side effects are also different: this design does not make email exact-once and does not make future provider calls idempotent by default. Network effects need their own durable idempotency contract. Until that current-tree proof exists for every live external consumer, keep `OUTBOX_PULL_WORKER` off or scope those consumers out.

## Cascade Track

The cascade lock hold is real, but it is a separate L0 `Delete` contract question. Its design lives in [cascade-delete-lock-scope.md](cascade-delete-lock-scope.md). The outbox worker repair does not depend on changing cascade semantics.

## Deferred

These are intentionally not part of the #3477 queue-mechanics implementation:

- external-effect ledger for email/webhook/provider calls;
- `source_event_id`, `handler_id`, `effect_key`, and `attempt_id` plumbing for external effect identity;
- durable send-command rows and separate sender workers;
- owner-token leases, `LockedUntil`, `NextVisibleAt`, and retry backoff scheduling;
- generic handler wall-clock timeout;
- lanes, reserved capacity, tenant fairness, candidate spreading, and dedicated worker DB pools;
- poison-head parking policy after terminal failure.

Keep these as future design work when after-save external effects need a generic platform ledger. Current live external consumers still belong in the rollout gate; the queue repair simply does not implement a universal ledger for them.

## Rejected Shortcuts

| Shortcut | Why rejected |
|---|---|
| Keep `ProcessOutbox` as a per-tick batch with `wg.Wait()` | Recreates the incident coupling: one blocked slot stalls the batch. |
| Let in-memory queues own "already processing" | Splits the source of truth between DB and process memory. |
| Release the claim before retry writes | Allows another worker to reclaim the same event before the failed attempt's bookkeeping lands. |
| Add a generic handler timeout now | The incident path already returns from DB lock wait; extra redelivery can duplicate external side effects before dedup exists. |
| Add a generic event ledger for the duplicate create | The duplicate is a DB create with a natural key; a unique index is simpler and stronger. |

## Rollout Order

1. Add `RecordKey` and pull-worker isolation using the existing claim/reclaim machinery.
2. Drain the old backlog or backfill `RecordKey` from the same `recordKeyFromPayload` logic before flipping `OUTBOX_PULL_WORKER`, so old and new rows for the same record cannot bypass each other.
3. Add savepoint-backed retry bookkeeping for failure paths.
4. Add natural-key uniqueness and find-or-create for the proven duplicate create.
5. Add redelivery/concurrent-redelivery tests for the four #3477 signatures.
6. Derive the current-tree external-effect consumer map; prove live consumers are protected or scoped out.
7. Keep the old drainer and the new pull worker mutually exclusive behind one flag until the gate is green.