# 🔄 Lesson 14 — Close database/MQTT failure gaps

## 🎬 Start with the system story

**❓ Question:** **The API commits a command to PostgreSQL, then the process crashes before publishing it. How will the device ever receive the accepted command?**

**💬 Answer:** The database must persist both the command and the intent to publish it in the same transaction. A worker can later publish unsent outbox rows. MQTT session storage and a Paho outgoing queue do not replace this application-level record.

```mermaid
flowchart LR
    H[HTTP/API request] --> T[DB transaction]
    T --> C[Command row]
    T --> O[Outbox row]
    O --> W[Retrying publisher worker]
    W -->|PUBLISH QoS 1| B[Mosquitto]
    B --> D[Device simulator]
    D -->|application result| B
    B --> I[Ingestion]
    I --> R[Persist result and command state]
```

The database owns accepted business intent. The outbox worker owns attempts to transmit that intent. The broker owns eligible MQTT session delivery. The device owns its deduplication/effect record. Each layer can repeat work, so stable IDs and idempotency remain necessary.

## 🪟 1. Draw every failure window

> **❓ Question:** **What if the publish reaches the broker, but the backend crashes before marking the outbox row sent?**

Walk each boundary separately and write the durable state before and after a crash:

| Failure point | Durable state | Recovery behavior |
| --- | --- | --- |
| Before command transaction commits | No accepted command/outbox | Caller can retry using its HTTP idempotency key |
| After commit, before worker publishes | Command and pending outbox row | Worker publishes after restart |
| After PUBLISH, before broker PUBACK | Outbox still pending/attempting | Retry may duplicate; preserve command ID |
| After PUBACK, before outbox marked sent | Broker accepted that hop; row may still be pending | Worker may publish again; device deduplicates |
| After device acts, before its result reaches backend | Device may have acted; backend status is uncertain | Retry/reconcile by stable command ID; do not infer failure from timeout |
| After result commit, before application receipt reaches device | Result is stored; response may be lost | Duplicate request/result returns stored prior outcome |

`PUBACK` lets the backend record evidence about a publisher-to-broker hop. It is not proof that a device received or completed the command. A timeout after publishing is an unknown business outcome unless the device later supplies evidence.

## 📤 2. Use a transactional outbox

> **❓ Question:** **How can the API accept a command and guarantee the intent to publish is not lost in a crash?**

In one database transaction, create the command row and an outbox row containing the serialized/versioned publication intent, target topic, stable command ID, expiry/deadline, and attempt state. Commit both together. A worker claims pending rows, publishes with a bounded context, records the returned MQTT result, and retries eligible failures with backoff. Keep the same command ID across every retry.

The outbox closes the “database committed, publish intent forgotten” gap. It does not make the remote publish and local sent marker atomic. If the broker accepts the publication and the backend crashes before updating the row, a later worker can publish again. The device must deduplicate by command ID and replay the prior application result.

Do not hold a database transaction open while waiting for a network publish. Claim work with a short transaction, publish outside it, then record the attempt outcome. Use a lease/claim strategy so two workers do not continuously publish the same row at once; still assume duplicates can occur around crashes.

## 🧾 3. Persist device results and idempotency

> **❓ Question:** **A device result arrives twice, or arrives after the command timed out. Should status regress?**

Validate the response topic, payload, device identity, command ID, and Correlation Data before applying it. In one transaction, store the result under a unique identity and update command state only through allowed transitions. A duplicate terminal result should not repeat an effect or regress completed/rejected state. A late result can resolve an earlier unknown outcome if it matches the command; preserve the history that a timeout had occurred.

Classify outcomes explicitly:

- **Retryable transport failure:** no reliable evidence that the peer accepted the publish; retry within expiry/deadline policy.
- **Permanent rejection:** broker or application reports a non-retryable reason; retain that reason safely.
- **Expired command:** do not publish stale work; mark it expired and expose that outcome.
- **Unknown device outcome:** publication may have arrived, but the application response is missing. Do not claim the device did nothing.
- **Completed/rejected by device:** validated application result is persisted; duplicates do not change the terminal result.

Correlation Data helps match a response to a request but is not authorization or deduplication. The application command ID and device-side deduplication state remain necessary.

## 🔀 4. Keep the three queue/state mechanisms distinct

> **❓ Question:** **Can a file-backed MQTT queue replace the PostgreSQL outbox?**

No. These mechanisms protect different state:

| Mechanism | Owner | What it can preserve | What it does not establish |
| --- | --- | --- | --- |
| Transactional outbox | Application database | Business intent and retry state atomically with command state | Device receipt or completion |
| Paho outgoing queue | MQTT client library/application configuration | Publications queued for transport according to that queue's semantics | Atomicity with the business DB transaction or device effect |
| MQTT session state | Client and broker | Protocol/session continuity and eligible queued QoS delivery | Application business state, deduplication, or physical action |

Review the pinned AutoPaho queue example and persistence API before enabling a file-backed queue. Record exactly where its files live and what a successful enqueue result means. Do not infer broker delivery from local enqueue success.

## ⏳ 5. Apply retries, expiry, and backpressure

> **❓ Question:** **Should an old price update or expired ping be published after several hours of retry?**

Give the command an application deadline and MQTT 5 Message Expiry Interval when appropriate. The worker checks the application deadline before publishing; broker expiry limits queued MQTT delivery after publication. The device checks its own deadline too, because time can be spent in the outbox, network, broker session, and device queue. These are separate clocks and checks.

Use bounded retry counts or retry windows with backoff and jitter. Stop or park permanently rejected and expired rows. Bound concurrent publishes and database claims; observe pending outbox count, oldest row age, attempts, publish latency, PUBACK results, application response latency, duplicate results, and unknown outcomes.

## 🧪 6. Inject crash windows

Run only against the dedicated learning broker and database, with synthetic commands:

1. Commit a command/outbox row, stop the publisher worker, restart it, and verify the pending row is attempted.
2. Stop the backend immediately after publish but before marking the outbox row sent. Restart and verify the duplicate publication uses the same command ID and produces one simulated effect.
3. Drop the device response after the simulated effect. Confirm status becomes unknown or remains pending according to the documented deadline policy; it must not report definite failure.
4. Expire a command before publish and verify the worker does not send it.
5. Restart the broker and compare broker session recovery with the still-pending application outbox row.

For each run, record prediction, injected failure, database rows, MQTT packet evidence, device effect counter, final status, and cleanup. Restore the lab by stopping only the project-named learning services and clearing only test data you created.

### 🧠 Review questions

1. Which transaction removes the lost-intent gap between accepting a command and publishing it?
2. Why can the device receive a duplicate after the broker already sent PUBACK to the backend?
3. What can a Paho outgoing queue prove, and what can it not prove?
4. Which state is owned by the broker session versus the command database?
5. What should the API report when the device may have acted but no result arrived?

> [!answer]- 📋 Short answers
>
> 1. Commit the command and outbox intent atomically.
> 2. The backend can crash after PUBACK but before marking the outbox row sent, then publish again after restart.
> 3. It can prove local queue admission according to its API; it cannot prove broker delivery or business completion.
> 4. Broker session state tracks MQTT subscriptions/in-flight/eligible queued delivery; the command database tracks application intent, status, and results.
> 5. An unknown outcome, until validated device evidence resolves it.

**Done when:** Every outbox crash window has an explainable recovery path, repeated publication causes one simulated effect, expiry prevents stale delivery, and command status never treats PUBACK as device completion.

**Next:** [Section 15](../../COURSE.md#section-15--split-api-and-ingestion-processes) moves API and ingestion into separate processes while keeping persisted status as the shared source of truth.