Project 9: Durable Notification Outbox

Build an order workflow that commits business state and durable notification responsibility atomically, while Phoenix.PubSub provides fast but disposable UI refresh.

Quick Reference

Attribute	Value
Difficulty	Level 4 — Expert
Time Estimate	22–32 hours
Language	Elixir (alternatives: Erlang, Java)
Prerequisites	Ecto transactions, PostgreSQL constraints, Phoenix.PubSub, OTP supervision, retry reasoning
Key Topics	Transactional outbox, Oban jobs, at-least-once delivery, idempotency, after-commit notification, crash recovery

1. Learning Objectives

By completing this project, you will be able to:

Separate an authoritative business fact from a transient notification and from durable asynchronous work.
Model an order transition and its handoff record as one database transaction with one commit boundary.
Explain every crash window between request receipt, database commit, job claim, external effect, and acknowledgement.
Preserve one immutable event identity across database rows, job arguments, webhook idempotency keys, logs, and PubSub refreshes.
Design an idempotent receiver that tolerates a worker crash after the external side effect succeeds.
Use PubSub as a latency optimization without making correctness depend on receiving a broadcast.
Define retry, discard, dead-letter, retention, redaction, and audit policies for durable events.
Produce failure-injection evidence proving that no committed order lacks durable responsibility.

2. Theoretical Foundation

2.1 Core Concepts

Three different promises

An order workflow normally has three outputs that look related but have different semantics:

The domain fact says that order 913 is now approved at revision 18. It belongs in the authoritative database.
The durable work item says that a required webhook, email, or downstream projection still has to happen. It must survive process and application restarts.
The transient refresh says that currently connected views may reload order 913. It optimizes freshness but may be missed without harming correctness.

Collapsing these promises is the central failure this project prevents. A PubSub broadcast is not durable work. A queued job is not the authoritative business fact. A committed row does not prove that an external webhook happened.

Transactional outbox and transactionally inserted job

The transactional outbox pattern stores the domain mutation and an immutable event record in the same local database transaction. Oban offers a closely related implementation: insert the job into the same PostgreSQL transaction as the domain mutation. Ecto.Multi groups named repository operations, and Oban’s insert variant for a Multi preserves features such as job uniqueness. Either approach moves the dangerous cross-system boundary until after the database commit.

The invariant is local and precise:

COMMIT => domain revision exists AND durable handoff exists
ROLLBACK => neither domain revision nor durable handoff exists

This does not make PostgreSQL and the eventual webhook participate in one transaction. It guarantees only that a committed fact leaves recoverable responsibility behind.

At-least-once execution and idempotency

A durable worker can crash after the receiver accepts an effect but before the worker marks the job complete. On restart, the job is delivered again. No local transaction can determine whether the remote system applied the effect if the response was lost. The correct default is therefore at-least-once delivery plus an idempotent effect contract.

Idempotency requires a stable logical identity, not merely a short time window. The event ID evt-77 remains the same across attempts. The receiver records that ID with the result of the first effective action and returns the recorded outcome for duplicates. Oban uniqueness can reduce duplicate job insertion, but its documentation is explicit that uniqueness applies at insertion time and does not serialize execution or replace effect-level idempotency.

After-commit notification

Publishing inside a database transaction creates a ghost-notification risk: subscribers may reload before the transaction becomes visible, or the transaction may roll back after the broadcast. Publishing after commit avoids notifying about rolled-back state. A process can still crash after commit and before broadcast, so the UI must not rely on that notification. Reconnect, periodic refresh, or revision-gap detection reloads authoritative state.

2.2 Why This Matters

The dual-write problem appears whenever one request must update a database and cause work in another system. A naïve implementation performs the SQL update and then publishes to a broker. A crash between those actions leaves committed state without a message. Reversing the order can publish a message for a transaction that later rolls back. There is no ordering of two independent writes that removes both failure windows.

The outbox changes the problem from “atomically write to two systems” to “atomically write two records to one system, then deliver one record at least once.” That is a tractable contract. It also makes operational recovery possible: operators can query pending work, measure the oldest event, retry safely, and audit terminal failures.

Phoenix.PubSub still matters. Connected users should not wait for a polling interval when an order changes. The after-commit broadcast gives low-latency freshness. Its message carries only tenant, aggregate ID, event ID, and revision; the subscriber performs an authorized reload. This keeps the transient message compact and prevents stale or sensitive payloads from becoming a second source of truth.

2.3 Historical Context / Background

Enterprise messaging systems popularized guaranteed-delivery and idempotent-receiver patterns long before modern job libraries. Database-backed outboxes became common in service architectures because distributed transactions were unavailable, too costly, or operationally undesirable. Change-data-capture systems can relay an outbox table to a broker, while database job systems such as Oban can make the durable row itself executable work.

The important evolution is not from “old queues” to “new PubSub.” It is toward explicit responsibility boundaries. A database protects local invariants. A durable queue or job table retains work. PubSub minimizes observation latency. Each mechanism is valuable because it makes a different promise.

2.4 Common Misconceptions

“Publishing inside Ecto.Multi.run makes the broker publish transactional.” It does not. The callback can perform an external side effect, but database rollback cannot recall it.
“Oban uniqueness makes the webhook exactly once.” Uniqueness limits matching inserts; a claimed job can still run again after uncertain completion.
“PostgreSQL notification means scanning is unnecessary.” Notifications are wake-ups, not durable facts; periodic recovery scans close missed-wake-up windows.
“After commit means the broadcast is guaranteed.” The process can die after commit and before broadcasting.
“A duplicate delivery is a duplicate effect.” Correct idempotency allows multiple delivery attempts to produce one effective result.
“The event payload should contain the full order.” A compact identity-and-revision notification avoids stale snapshots and unnecessary disclosure.

3. Project Specification

3.1 What You Will Build

Build a Phoenix training application with an order aggregate, an immutable durable handoff, a supervised worker, a mock external webhook receiver, and two connected order views.

The reference scenario supports one transition from authorized to approved. The command includes tenant ID, order ID, expected revision, actor ID, and request ID. A successful transaction updates the order revision and inserts either an outbox event plus relay job or an Oban job containing the immutable event envelope. An invalid state transition rolls back both operations.

After commit, the command path broadcasts a compact refresh. Two LiveViews subscribed to a server-derived tenant/order topic reload revision 18. If either misses the message, reconnecting or detecting a revision gap produces the same state.

The worker calls a local mock webhook with evt-77 as its idempotency key. The mock stores one effect result per event ID. A failure switch crashes the worker after the mock accepts the call but before local completion is recorded. Retry receives already_processed, records success, and completes the durable job.

3.2 Functional Requirements

Optimistic order transition: Reject an update whose expected revision does not match the current revision.
Atomic handoff: Update the order and insert durable responsibility in one PostgreSQL transaction.
Immutable envelope: Store event ID, tenant ID, aggregate ID, aggregate revision, event type, schema version, occurred-at time, actor/request correlation, and a minimal payload.
After-commit refresh: Broadcast only after a successful transaction result.
Authorized reload: Subscribers use authenticated tenant context to reload current state; they never trust tenant identity from the broadcast alone.
Durable execution: Pausing workers and restarting the application must leave pending work available.
Idempotent receiver: Repeated calls with the same event ID return one recorded effect result.
Failure classification: Transient network/server failures retry; permanent validation failures discard or enter a review queue; exhausted work remains observable.
Recovery scan: A periodic process finds committed handoffs not completed within the expected interval.
Audit view: Operators can inspect event status, attempt count, last error class, age, and correlation IDs without viewing secrets.

3.3 Non-Functional Requirements

Consistency: There must be no state where a committed target revision lacks a durable handoff.
Durability: Pending work survives application and worker restarts.
Idempotency: Every retry uses the original event ID; one logical event yields at most one effective mock-webhook action.
Latency: Online views normally observe committed revisions within 500 ms on the local test environment.
Recovery: A missed PubSub refresh or relay wake-up is detected within the configured scan interval.
Privacy: Job arguments and logs exclude secrets, payment data, and unnecessary customer fields.
Observability: Queue depth, oldest available job age, attempts, execution latency, discarded jobs, and refresh count are measurable.
Operability: A runbook distinguishes replaying a safe idempotent job from manually resolving a permanent business failure.

3.4 Example Usage / Output

$ mix outbox.scenario
TX begin tenant=42 order=913 expected_revision=17
TX commit order_revision=18 event_id=evt-77 job_id=551
pubsub topic=tenant:42:order:913 refresh_revision=18 online_views=2

worker paused
application restarted
job id=551 state=available persisted=true

attempt=1 event_id=evt-77 webhook=accepted effect_id=fx-208
fault injected point=after_remote_accept_before_local_ack
attempt=2 event_id=evt-77 webhook=already_processed effect_id=fx-208
job id=551 state=completed effective_calls=1

invalid transition expected=18 requested=cancelled reason=policy_denied
transaction=rolled_back order_revision=18 new_jobs=0
scenario result=PASS

3.5 Real World Outcome

Open the application in two browser windows as two authorized operators for tenant 42. Both windows show order 913 at revision 17 and a notification-delivery panel. Approve the order in the first window. The status card changes to revision 18 in both windows without a manual refresh. The event panel shows evt-77, job 551, and available or executing before it becomes completed.

Next, pause the worker before approving a second order. The UI shows the business transition as committed while the notification card remains pending. Restart the application. The order and pending work are still present, demonstrating that correctness did not depend on a process mailbox.

Enable the dangerous fault switch. The webhook receiver records one effect, the worker crashes before acknowledgement, and the job retries. The second request returns the original effect result. The final evidence screen shows two attempts, one effective action, one immutable event ID, and no duplicate customer notification.

Finally, attempt an invalid transition. The UI renders a policy error, the order revision remains unchanged, and the audit query reports zero new handoff records. Export the scenario transcript and invariant table as the completion artifact.

4. Solution Architecture

4.1 High-Level Design

                         TRANSIENT FRESHNESS
             after commit ───────────────────────────────┐
                                                        v
┌──────────┐ command  ┌──────────────────────────┐  ┌───────────────┐
│ LiveView │─────────▶│ Command / policy service │  │ Phoenix.PubSub│
└────┬─────┘          └────────────┬─────────────┘  └───────┬───────┘
     │                             │ one transaction         │ refresh ID/revision
     │ reload                      v                         v
     │                  ┌─────────────────────────┐    ┌────────────┐
     └─────────────────▶│ PostgreSQL              │◀───│ LiveViews  │
                        │ orders + durable handoff│    └────────────┘
                        └────────────┬────────────┘
                                     │ claim/retry
                                     v
                              ┌─────────────┐
                              │ Oban worker │
                              └──────┬──────┘
                                     │ event_id as idempotency key
                                     v
                              ┌─────────────┐
                              │ Mock webhook│
                              │ effect ledger│
                              └─────────────┘

Authoritative fact: PostgreSQL order revision
Durable responsibility: outbox/job row
Transient hint: PubSub refresh
External idempotency authority: webhook effect ledger

4.2 Key Components

Component	Responsibility	Key Decisions
Command service	Validate tenant, policy, state transition, and expected revision	Pure decision before transaction where possible
Transaction builder	Update order and insert durable handoff	Named operations and one commit boundary
Event envelope	Carry immutable logical identity and correlation	Minimal, versioned, redacted fields
After-commit notifier	Broadcast tenant/order ID and revision	Best-effort; failure does not roll back commit
Durable worker	Claim, execute, classify, and record attempts	At-least-once with bounded backoff
Mock webhook	Demonstrate external idempotency	Unique event ID maps to one effect result
Recovery scanner	Detect stale committed work	Periodic query, not notification-dependent
Audit dashboard	Expose health and evidence	No secrets or raw sensitive payloads

4.3 Data Structures

The following are schema outlines, not runnable definitions:

Order
  tenant_id          tenant authority boundary
  id                 aggregate identity
  status             current domain state
  revision           monotonically increasing integer
  updated_at         database commit-derived timestamp

EventEnvelope
  event_id           immutable UUID/ULID
  tenant_id          validated server-side
  aggregate_type     "order"
  aggregate_id       "913"
  aggregate_revision 18
  event_type         "order.approved"
  schema_version     1
  occurred_at        business occurrence time
  request_id         command correlation
  causation_id       optional parent event
  actor_ref          non-sensitive audited identity
  payload            minimal versioned fields

EffectLedger
  event_id           unique key
  effect_type        "customer_webhook"
  result_ref         fx-208
  applied_at         receiver commit time
  request_digest     detects conflicting reuse

Database constraints should enforce tenant/aggregate identity, unique event identity, nondecreasing revision policy through the command update, and one effect ledger entry per event/effect pair.

4.4 Algorithm Overview

Algorithm: Atomic transition and handoff

INPUT authenticated_actor, tenant_id, order_id, expected_revision, command
AUTHORIZE actor for tenant and order
DECIDE transition against current state
CREATE immutable event_id and envelope
TRANSACTION
  UPDATE order WHERE revision = expected_revision
  ASSERT exactly one row changed
  INSERT durable job/outbox using same envelope
COMMIT
BEST-EFFORT broadcast refresh(tenant_id, order_id, new_revision, event_id)
RETURN committed revision and event identity

Algorithm: Idempotent effect

CLAIM durable job
SEND event with Idempotency-Key = event_id
RECEIVER transaction:
  if ledger contains event_id and digest matches: return recorded result
  if ledger contains event_id and digest differs: reject identity conflict
  otherwise apply effect and record result under event_id
ON response success/already_processed: complete job
ON transient failure: retry with same event_id
ON permanent contract failure: discard or move to review state

Complexity analysis

Transactional mutation and keyed inserts are O(log n) with normal indexes.
Recovery scan is O(k log n) for k due/unfinished rows when indexed by state and scheduled time.
Receiver deduplication is O(log n) by unique event key.
Space is O(e), where e is retained event/effect history; retention and audit requirements determine compaction.

5. Implementation Guide

5.1 Development Environment Setup

Use a disposable PostgreSQL database and local webhook fixture. The setup transcript should resemble:

$ elixir --version
Elixir 1.20.x (compiled with Erlang/OTP 29)

$ docker compose up -d postgres
postgres health=healthy port=5432

$ mix ecto.create
database created

$ mix ecto.migrate
migrations applied: orders, oban_jobs, webhook_effects

Resolve versions through the project lockfile rather than copying this snapshot blindly. Record the Elixir, OTP, Ecto, PostgreSQL, Oban, Phoenix, and Phoenix.PubSub versions in the final evidence.

5.2 Project Structure

durable_notification_outbox/
├── lib/
│   ├── orders/                 # aggregate, command policy, transaction boundary
│   ├── notifications/          # envelope, worker, failure classification
│   ├── webhooks/               # mock receiver and effect ledger
│   └── web/                    # order LiveView and audit dashboard
├── priv/repo/migrations/       # constraints and Oban schema
├── test/
│   ├── orders/                 # transaction invariants
│   ├── notifications/          # retry/idempotency/failure injection
│   └── integration/            # restart and two-client scenarios
├── docs/
│   ├── failure-matrix.md
│   └── operations-runbook.md
└── evidence/                   # redacted transcripts and metric snapshots

5.3 The Core Question You’re Answering

“How do I guarantee that committed state eventually triggers required work without pretending the database and message system share one transaction?”

Before implementation, write the guarantee in one sentence. It must mention one local atomic commit, durable responsibility, at-least-once execution, stable identity, and idempotent effects. If it says “exactly once,” identify the specific storage constraint and boundary that make that statement true; do not use it as an end-to-end slogan.

5.4 Concepts You Must Understand First

Database transaction boundaries
- Which operations can PostgreSQL commit or roll back together?
- Why can an HTTP call inside the callback not be rolled back?
- Reference: Ecto Ecto.Multi and repository transaction documentation.
Transactional outbox or transactional job insertion
- Is the durable record an integration event, an executable job, or both?
- Which component owns relay and retention?
- Reference: Oban insert/4 with Ecto.Multi; Debezium outbox event router documentation.
At-least-once delivery
- Which crash window leaves completion uncertain?
- Why must every attempt preserve event identity?
- Book Reference: “Enterprise Integration Patterns” — Guaranteed Delivery and Idempotent Receiver.
Optimistic concurrency
- How does expected revision prevent lost updates?
- What should a stale caller receive?
- Book Reference: “Designing Data-Intensive Applications, 2nd Edition” — Transactions.
PubSub as invalidation
- Why should a view reload current authorized state?
- What happens if the notification is missed or reordered?
- Reference: Phoenix.PubSub API documentation.

5.5 Questions to Guide Your Design

Event identity
- Is the ID generated before transaction retry, and can retries accidentally create a new logical event?
- How do you detect the same ID reused with a different payload?
Commit and notify
- Who owns after-commit broadcast?
- How does the UI recover when that owner crashes before broadcasting?
Failure classification
- Which HTTP errors are transient, rate-limited, permanent, or ambiguous?
- When does an exhausted job require operator action?
Retention and privacy
- How long must event metadata remain auditable?
- Which fields must never enter job arguments or logs?
Operational budgets
- What oldest-job age pages an operator?
- What queue depth triggers degraded noncritical delivery?

5.6 Thinking Exercise

Draw this timeline on paper and mark each crash point:

request -> decision -> DB begin -> order update -> handoff insert -> commit
        -> PubSub refresh -> worker claim -> receiver effect -> receiver response
        -> local completion acknowledgement

For every arrow, answer:

What durable evidence exists immediately before and after it?
Can the step be repeated safely?
Can a duplicate delivery occur?
Can a duplicate effect occur?
What process or scan resumes progress?
What would the user observe?

The dangerous case is receiver effect success followed by worker death. Your design is not complete until this case has a deterministic answer.

5.7 The Interview Questions They’ll Ask

“What problem does the transactional outbox solve, and what does it not solve?”
“Why isn’t a broker publish inside Ecto.Multi.run atomic with PostgreSQL?”
“How do you handle a crash after a side effect but before acknowledgement?”
“What is the difference between Oban job uniqueness and idempotent execution?”
“Why keep a periodic scan if PubSub or PostgreSQL notification wakes the worker?”
“When would you choose an outbox plus broker over a database job?”

5.8 Hints in Layers

Hint 1: Start with the invariant

Write a query that finds approved order revisions with no handoff. Make the test fail under a two-write implementation, then remove the window with one transaction.

Hint 2: Broadcast identities, not snapshots

Use a conceptual refresh message:

{:order_changed, tenant_id, order_id, revision, event_id}

The subscriber authorizes and reloads. Treat the tuple as a protocol outline, not the source of truth.

Hint 3: Preserve identity through retries

Put the immutable event ID in job arguments and the receiver idempotency header. Never derive a new ID from attempt number.

Hint 4: Test uncertainty, not only obvious failure

Crash after receiver commit but before worker completion. A pre-call crash proves only ordinary retry; the post-effect crash proves idempotency.

5.9 Books That Will Help

Topic	Book	Chapter / Pattern
Guaranteed delivery	“Enterprise Integration Patterns”	Guaranteed Delivery, Idempotent Receiver
Local transactions and derived data	“Designing Data-Intensive Applications, 2nd Edition”	Transactions; Stream Processing
Service messaging	“Building Microservices, 2nd Edition”	Asynchronous Communication
Failure and recovery	“Release It!, 2nd Edition”	Stability Patterns

5.10 Implementation Phases

Phase 1: Atomic Domain Handoff (8–10 hours)

Goals

Define the order transition and immutable event envelope.
Commit domain revision and durable handoff together.

Tasks

Create order, handoff/job, and effect-ledger schemas with database constraints.
Write the valid and invalid transition decision table.
Build the transaction outline and inspect its named operations before execution.
Add the orphan query and rollback tests.
Record event identity and correlation policy.

Checkpoint: 1,000 injected failures around transaction operations produce zero committed revisions without a handoff and zero handoffs for rolled-back revisions.

Phase 2: Durable Execution and Idempotency (8–12 hours)

Goals

Execute pending work under retry.
Prove the dangerous crash produces one effect.

Tasks

Add the worker and explicit transient/permanent failure table.
Implement the mock receiver’s unique event ledger.
Inject faults before call, after receiver commit, and before local acknowledgement.
Pause workers and restart the application.
Add queue-age, attempt, and outcome telemetry.

Checkpoint: The same event is delivered at least twice after the post-effect crash, but the receiver reports one effective action and the job completes.

Phase 3: Transient UX and Operations (8–14 hours)

Goals

Add fast online refresh without coupling correctness to PubSub.
Produce an operator-grade audit and recovery story.

Tasks

Add after-commit refresh and authorized reload.
Run two-client, missed-message, reconnect, and stale-revision tests.
Add periodic recovery scan and oldest-work alert.
Document retention, redaction, discard, and manual resolution.
Run the complete scenario and export evidence.

Checkpoint: Both online views refresh quickly; a disconnected view catches up from the database; pending work survives restart; the failure report explains every attempt.

5.11 Key Implementation Decisions

Decision	Options	Recommendation	Rationale
Durable handoff	Custom outbox; Oban job; outbox relayed to broker	Begin with transactional Oban insertion, document when a neutral outbox is needed	Smallest lab that preserves atomic responsibility
Notification payload	Full snapshot; ID + revision	ID + revision	Avoids stale duplicate state and reduces disclosure
Event ID	Per attempt; per logical event	Per logical event	Required for idempotency and trace continuity
Receiver dedupe	Time cache; durable unique ledger	Durable unique ledger	Survives restart and exposes conflicting reuse
Retry	Immediate loop; exponential backoff with jitter	Bounded exponential backoff with jitter	Limits synchronized pressure during outage
Recovery wake-up	PubSub only; notification plus periodic scan	Notification plus periodic scan	Notification reduces latency; scan preserves progress

6. Testing Strategy

6.1 Test Categories

Category	Purpose	Examples
Unit	Verify transition and error classification	Valid revision, stale revision, transient vs permanent response
Transaction integration	Prove atomic database invariant	Fail before update, between operations, before commit
Worker integration	Prove retry and idempotency	Crash after receiver effect, repeated event ID
PubSub integration	Prove refresh is optional	Two clients, missed broadcast, reconnect reload
Restart	Prove durable responsibility	Worker pause, app restart, database restart
Property/state-machine	Explore operation sequences	Commands, failures, retries, duplicate delivery
Operational	Verify metrics and runbook	Oldest age alert, discard visibility, redaction

6.2 Critical Test Cases

Successful transition: Revision 17 becomes 18 and exactly one durable handoff contains the matching identity.
Invalid transition: Policy failure rolls back; no revision or handoff changes.
Optimistic conflict: Two commands expect revision 17; one commits and one receives a conflict without a second event.
Crash before commit: Neither domain nor handoff change persists.
Crash after commit before PubSub: Durable work remains and reconnect reloads revision 18.
Worker restart: Available or executing work becomes runnable after restart according to queue semantics.
Crash after external effect: Retry occurs with the same ID and receiver effect count stays one.
Identity collision: Same event ID with a different digest is rejected and alerted.
Missed wake-up: Periodic scan finds the pending item.
Redaction: Secrets and customer payload fields are absent from job args, logs, metrics, and exported evidence.

6.3 Test Data

tenant: 42
order: 913
initial_status: authorized
initial_revision: 17
valid_command: approve expected_revision=17 request=req-501
event: evt-77 type=order.approved revision=18 schema=1
job: 551 queue=notifications max_attempts=8
receiver_result: fx-208

invalid_cases:
  - approve expected_revision=16 -> optimistic_conflict
  - cancel approved_order -> policy_denied
  - evt-77 with altered order_id -> idempotency_identity_conflict
  - receiver 503 -> retry
  - receiver 422 unsupported_schema -> permanent_failure

The test suite should generate additional event IDs deterministically from a fixed seed and should print the seed on failure.

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

Pitfall	Symptom	Solution
Separate transactions	Approved order has no job	Commit mutation and handoff in one transaction
External call inside transaction	Long locks and ghost effects after rollback	Keep external effects in durable post-commit work
New ID per retry	Duplicate receiver actions	Preserve logical event ID across attempts
Treating Oban uniqueness as execution lock	Concurrent jobs still run	Configure concurrency and make effects idempotent
Broadcast before commit	Subscriber reloads old state or sees rolled-back event	Broadcast after successful transaction result
PubSub-only recovery	Disconnected views remain stale	Reload on mount and detect revision gaps
Unbounded job payload	Sensitive/stale snapshots persist	Store minimal identifiers and versioned fields
Retry every error	Permanent poison work loops forever	Classify discard, retry, and review outcomes

7.2 Debugging Strategies

Query the invariant first: Count committed target revisions without matching handoff rows.
Trace one event ID: Search structured logs, job args, receiver ledger, and refresh metrics for evt-77.
Inspect transaction operations: Use the Multi’s canonical operation list in tests rather than poking at internal fields.
Freeze workers: Pausing execution separates commit behavior from delivery behavior.
Move the fault point: A named fault switch before/after each boundary makes uncertainty reproducible.
Compare delivery and effect counts: Delivery attempts may exceed one; effective actions must not.
Reconnect the UI: If state repairs on reconnect, the transient notification path—not the authority—is suspect.

7.3 Performance Traps

Holding a transaction open during HTTP or broker I/O increases lock time and contention.
Scanning the entire outbox without a state/time index becomes progressively expensive.
Immediate retries amplify downstream outages; use backoff, jitter, and queue concurrency limits.
Large job arguments increase database I/O and retention cost.
Broadcasting full aggregates increases serialization and mailbox pressure.
Retaining every completed job and effect forever requires partitioning/archival policy.

8. Extensions & Challenges

8.1 Beginner Extensions

Add an audit page that follows one event ID through all stages.
Add a second idempotent notification type with a separate effect key.
Show a visible “refresh missed; reloaded from revision” badge after reconnect.

8.2 Intermediate Extensions

Replace direct job execution with a neutral outbox table and supervised relay.
Add per-tenant queue concurrency and fair scheduling.
Add retention partitions and a redacted archival export.
Use a property-based command model to generate transaction/fault sequences.

8.3 Advanced Extensions

Relay the outbox through change-data capture to a durable broker and compare guarantees.
Implement an inbox ledger for a second service and prove end-to-end idempotent projection.
Exercise region failover with a promoted database replica and document recovery point limits.
Define SLOs for commit-to-effect latency and automate a burn-rate alert.

9. Real-World Connections

9.1 Industry Applications

Commerce: Order approval, payment settlement, and fulfillment messages must not disappear after commit.
Banking: Ledger changes and downstream reporting require immutable identity and audit trails.
Healthcare: Clinical state changes may trigger durable notifications while UI refresh remains transient.
SaaS billing: Subscription changes drive emails, entitlements, and external accounting integrations.
Internal operations: Incident changes refresh dashboards immediately while escalations execute durably.

Oban: PostgreSQL-backed job processing with transactional insertion.
Ecto: Database wrapper and transaction composition for Elixir.
Debezium: Change-data-capture outbox event routing.
Phoenix.PubSub: Transient local and distributed fanout.

9.3 Interview Relevance

This project demonstrates the difference between delivery attempts and effects, the limits of local transactions, and practical recovery design. In a system-design interview, use the failure timeline rather than claiming exactly-once delivery. Explain which database constraint gives local atomicity, which durable row retains responsibility, which key makes the receiver idempotent, and how a disconnected UI reloads facts.

10. Resources

10.1 Essential Reading

“Enterprise Integration Patterns” by Gregor Hohpe and Bobby Woolf — Guaranteed Delivery, Idempotent Receiver, and Message Store patterns.
“Designing Data-Intensive Applications, 2nd Edition” by Martin Kleppmann et al. — Transactions, distributed failure, and stream-derived data.
“Building Microservices, 2nd Edition” by Sam Newman — asynchronous integration and service boundaries.
“Release It!, 2nd Edition” by Michael Nygard — retry, timeout, and stability patterns.

10.2 Video Resources

Ecto and Oban conference talks from the official ElixirConf channels; select sessions that show transaction boundaries and failure recovery.
PostgreSQL transaction and locking lectures that demonstrate commit visibility and optimistic updates.

10.3 Tools & Documentation

Ecto.Multi — grouping named repository operations in one transaction.
Oban insertion API — inserting jobs through Ecto.Multi.
Oban unique jobs — insertion-time uniqueness and its limits.
Phoenix.PubSub — after-commit transient refresh.
PostgreSQL transaction isolation — visibility and concurrent-update behavior.

Project 7: Mailbox Pressure Laboratory prepares bounded transient delivery and overload reasoning.
Project 5: LiveView Operations Board establishes reconnect reload, while Project 3: Contract-Aware Domain Event Hub establishes authorized topic derivation.
Project 10: Three-Node PubSub Laboratory tests missed transient revisions during partitions.
Project 11: Tenant-Safe Broker Edge Bridge moves durable responsibility across service boundaries.
Project 12: Multi-Tenant Real-Time Event Platform combines outbox, broker, PubSub, and SRE certification.

11. Self-Assessment Checklist

11.1 Understanding

I can explain why no ordering of two independent writes eliminates both dual-write failure windows.
I can distinguish domain fact, durable work, transient refresh, and external effect.
I can identify the crash-after-effect window and explain why it creates redelivery.
I can explain why Oban uniqueness is not end-to-end exactly-once execution.
I can defend every field in the event envelope and identify excluded sensitive data.
I can explain how a view repairs itself after missing PubSub.

11.2 Implementation

Valid transitions commit one matching handoff; invalid transitions commit neither change.
Optimistic revision conflicts cannot create duplicate events.
Worker/app restart preserves pending work.
Post-effect crash causes retry with the same event ID.
Receiver ledger records one effective action.
Recovery scan finds missed wake-ups.
Metrics and evidence are redacted and correlated.

11.3 Growth

I documented one false assumption I previously held about exactly-once delivery.
I can compare transactional Oban insertion, a custom outbox relay, and broker publication.
I can present the failure matrix in a system-design interview.
I can name the operational signal that indicates stuck durable responsibility.

12. Submission / Completion Criteria

Minimum Viable Completion

One valid order transition atomically commits revision and durable handoff.
One invalid transition rolls back both.
One connected view refreshes after commit.
Paused work survives application restart.
Event identity is visible across database, worker, and mock receiver.

Full Completion

All minimum criteria plus optimistic conflict, two-client refresh, missed-message reload, recovery scan, and explicit failure classification.
The post-effect crash produces at least two deliveries and exactly one effective action.
Queue age, attempts, terminal failures, and refresh latency are observable.
Retention, privacy, redaction, and operator-resolution policies are documented.
The complete deterministic scenario transcript reports all invariants as passing.

Excellence (Going Above & Beyond)

A property/state-machine test explores command, crash, and retry sequences with reproducible seeds.
An alternative CDC/broker relay is benchmarked against direct database-job execution.
Commit-to-effect SLOs and alert thresholds are justified with load evidence.
The learner can disable PubSub entirely and prove durable facts and work still recover.

This guide was expanded from LEARN_ELIXIR_PUBSUB_DEEP_DIVE.md. For the complete expanded project index, see README.md.

Project 9: Durable Notification Outbox

Quick Reference

1. Learning Objectives

2. Theoretical Foundation

2.1 Core Concepts

2.2 Why This Matters

2.3 Historical Context / Background

2.4 Common Misconceptions

3. Project Specification

3.1 What You Will Build

3.2 Functional Requirements

3.3 Non-Functional Requirements

3.4 Example Usage / Output

3.5 Real World Outcome

4. Solution Architecture

4.1 High-Level Design

4.2 Key Components

4.3 Data Structures

4.4 Algorithm Overview

5. Implementation Guide

5.1 Development Environment Setup

5.2 Project Structure

5.3 The Core Question You’re Answering

5.4 Concepts You Must Understand First

5.5 Questions to Guide Your Design

5.6 Thinking Exercise

5.7 The Interview Questions They’ll Ask

5.8 Hints in Layers

5.9 Books That Will Help

5.10 Implementation Phases

Phase 1: Atomic Domain Handoff (8–10 hours)

Phase 2: Durable Execution and Idempotency (8–12 hours)

Phase 3: Transient UX and Operations (8–14 hours)

5.11 Key Implementation Decisions

6. Testing Strategy

6.1 Test Categories

6.2 Critical Test Cases

6.3 Test Data

7. Common Pitfalls & Debugging

7.1 Frequent Mistakes

7.2 Debugging Strategies

7.3 Performance Traps

8. Extensions & Challenges

8.1 Beginner Extensions

8.2 Intermediate Extensions

8.3 Advanced Extensions

9. Real-World Connections

9.1 Industry Applications

9.2 Related Open Source Projects

9.3 Interview Relevance

10. Resources

10.1 Essential Reading

10.2 Video Resources

10.3 Tools & Documentation

10.4 Related Projects in This Series

11. Self-Assessment Checklist

11.1 Understanding

11.2 Implementation

11.3 Growth

12. Submission / Completion Criteria