Designing an Offline-Resilient MQTT Buffer with SQLite

The most crucial question for a data collection service over MQTT (Message Queuing Telemetry Transport) Protocol what happens when the downstream service disappears for 20 minutes? That's the question I had when designing a lightweight client to collect data from devices working on MQTT protocol. At first I thought of forwarding every MQTT message directly to an HTTP API. But I had to consider for the unreliable network problem which is common in distributed systems. The moment the downstream API becomes slow, or timeouts, or has an auth issue, the whole ingestion path would start inheriting failures. So I had to build a system methodically. The goal was simple: keep accepting data even when the rest of the pipeline is having problems. I ended up with a lightweight Python service that: subscribes to MQTT topics keeps a local durable buffer in SQLite forwards records downstream in batches retries when delivery fails survives process restarts without losing unacknowledged data by the dow