IoT edge nodes (such as Raspberry Pi sensors) are frequently deployed on networks with poor connection stability (like cellular interfaces or remote building WiFi). If the edge daemon tries to upload telemetry synchronously, network dropouts will crash the script or lead to log data loss. A resilient telemetry client must write readings to a local database queue first, uploading them asynchronously and caching payloads during offline periods.

By implementing a local SQLite buffer queue in Python, edge clients can withstand long network blackouts without losing telemetry data.

1. Designing the SQLite Caching Buffer

We write a class manager to save telemetry logs locally and handle uploading batches when connection checks succeed:

# local_buffer.py
import sqlite3
import json
import requests
import time

class TelemetryBuffer:
    def __init__(self, db_path="telemetry_buffer.db"):
        self.conn = sqlite3.connect(db_path)
        self.create_table()
        
    def create_table(self):
        with self.conn:
            self.conn.execute("""
                CREATE TABLE IF NOT EXISTS queue (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    payload TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
            """)

    def enqueue_log(self, payload):
        with self.conn:
            self.conn.execute(
                "INSERT INTO queue (payload) VALUES (?)",
                (json.dumps(payload),)
            )

    def flush_queue(self, api_url):
        cursor = self.conn.cursor()
        cursor.execute("SELECT id, payload FROM queue ORDER BY id ASC LIMIT 50")
        rows = cursor.fetchall()
        
        if not rows:
            return
            
        payloads = [{"id": r[0], "data": json.loads(r[1])} for r in rows]
        
        try:
            # Post batch to backend server
            res = requests.post(api_url, json={"batch": payloads}, timeout=5)
            if res.status_code == 200:
                # Delete successfully uploaded rows
                row_ids = [p["id"] for p in payloads]
                with self.conn:
                    self.conn.executemany("DELETE FROM queue WHERE id = ?", [(i,) for i in row_ids])
                print(f"Uploaded and cleared {len(row_ids)} telemetry logs.")
        except requests.RequestException as e:
            print(f"Network offline: Buffering telemetry logs locally. Details: {e}")

2. The Execution Thread Loop

The sensor thread runs continuously, saving readings to SQLite, while a background upload thread calls the flush routine periodically, ensuring telemetry uploads do not block sensor polling loops.