Building an Offline-First IoT Ingestion Pipeline with Go, SQLite WAL, and Redis Streams

When building IoT infrastructure for high-density environments, assuming you will have a stable internet connection is an architectural fatal flaw.

At StampIQ, we engineer event analytics platforms for Saudi Vision 2030 mega-events. When 10,000 delegates flood a convention center within a 45-minute window, local cellular macrocells congest, and venue Wi-Fi packet loss spikes.

If your physical hardware (like an RFID gate or a POS terminal) relies on a synchronous REST API call to a cloud PostgreSQL database to validate a payload, your hardware will freeze the moment the network degrades.

To guarantee sub-5-millisecond hardware response times regardless of network health, we moved our persistence layer to the edge. Here is how we built an offline-first IoT ingestion pipeline using Go, SQLite Write-Ahead Logging (WAL), and Redis Streams.

1. The Edge Node: Concurrency with SQLite WAL

Each physical gate controller is an ARM-based edge node. Its primary job is to read passive UHF RFID tags (EPC Gen 2), validate the credential against a local manifest, and trigger a physical relay (opening a turnstile).

This must happen locally. We use SQLite as our edge datastore, but standard SQLite locks the entire database during a write transaction. If a background sync thread is reading the database to push logs to the cloud, it will block the hardware from writing new RFID scans, freezing the gate.

To fix this, we enable Write-Ahead Logging (WAL).

WAL mode decouples reads from writes. Writes are appended to a separate -wal file, meaning readers do not block writers, and writers do not block readers.

go
package main

import (
    "database/sql"
    "log"
    _ "[github.com/mattn/go-sqlite3](https://github.com/mattn/go-sqlite3)"
)

func initEdgeDB() *sql.DB {
    // Open DB with WAL mode and memory-mapped I/O enabled via DSN pragmas
    db, err := sql.Open("sqlite3", "file:/var/data/edge_telemetry.db?cache=shared&mode=rwc&_journal_mode=WAL&_synchronous=NORMAL")
    if err != nil {
        log.Fatalf("Failed to open edge database: %v", err)
    }

    // Optimize for high-frequency micro-inserts
    db.Exec(`PRAGMA temp_store = MEMORY;`)
    db.Exec(`PRAGMA mmap_size = 268435456;`) // 256MB memory map

    // Create our telemetry buffer table
    db.Exec(`
        CREATE TABLE IF NOT EXISTS scan_buffer (
            id TEXT PRIMARY KEY,
            epc_hash TEXT NOT NULL,
            portal_id TEXT NOT NULL,
            epoch_ms INTEGER NOT NULL,
            synced INTEGER DEFAULT 0
        );
        CREATE INDEX IF NOT EXISTS idx_sync ON scan_buffer(synced);
    `)

    return db
}
With this configuration, the Go routine handling the RFID scanner can insert thousands of rows per second in under 2 milliseconds per transaction, completely unaffected by network status.

2. The Sync Daemon: Exponential Backoff
A separate Go goroutine acts as a synchronization daemon. It polls the SQLite database for unsynced records (WHERE synced = 0) and attempts to push them to the cloud.

If the venue network is down, the HTTP request fails, and the daemon simply backs off. The physical gate continues to operate normally, buffering the telemetry locally.

Go
func syncWorker(db *sql.DB) {
    for {
        // Extract up to 500 unsynced records
        rows, err := db.Query(`SELECT id, epc_hash, portal_id, epoch_ms FROM scan_buffer WHERE synced = 0 LIMIT 500`)
        if err != nil {
            time.Sleep(2 * time.Second)
            continue
        }

        payload := buildJSONPayload(rows)

        // Attempt to push to the cloud API
        resp, err := http.Post("[https://api.stampiq.sa/v1/telemetry/ingest](https://api.stampiq.sa/v1/telemetry/ingest)", "application/json", payload)

        if err == nil && resp.StatusCode == 202 {
            // Success! Mark batch as synced locally
            markAsSynced(db, payload)
        } else {
            // Network failed. Back off and retry later.
            log.Println("Network degraded. Buffering telemetry at the edge.")
            time.Sleep(5 * time.Second)
        }
    }
}
3. Cloud Ingestion: Redis Streams for Burst Traffic
When the venue's network connectivity is finally restored, dozens of edge nodes will simultaneously flush thousands of buffered records to the cloud.

If we push this sudden burst directly into our primary relational database, we risk massive lock contention and CPU spikes. Instead, we use Redis Streams as an ultra-fast, in-memory ingestion buffer.

When the cloud API receives a batch payload, it maps the data and issues an XADD command. Redis Streams provide an immutable, append-only log with O(1) insertion time.

Go
func ingestHandler(w http.ResponseWriter, r *http.Request) {
    var batch TelemetryBatch
    json.NewDecoder(r.Body).Decode(&batch)

    pipe := rdb.Pipeline()
    for _, scan := range batch.Records {
        pipe.XAdd(ctx, &redis.XAddArgs{
            Stream: "stream:gate_telemetry",
            Values: map[string]interface{}{
                "epc":    scan.EPCHash,
                "portal": scan.PortalID,
                "ts":     scan.EpochMS,
            },
        })
    }

    // Execute the pipeline atomically
    if _, err := pipe.Exec(ctx); err != nil {
        http.Error(w, "Ingestion Failed", http.StatusInternalServerError)
        return
    }

    w.WriteHeader(http.StatusAccepted)
}
4. Downstream Processing
Once the data is safely buffered in Redis Streams, a cluster of backend consumer groups reads the stream at a controlled pace. They apply idempotent deduplication, update real-time venue capacity metrics, and finally write the settled data to TimescaleDB for long-term analytical storage.

The Takeaway
When engineering for the physical world, network failure is a feature, not a bug. By pushing relational persistence to the edge using SQLite WAL and utilizing Redis Streams to absorb burst ingestion, you can build IoT architectures that are virtually indestructible under load.

If you are interested in seeing how this architecture powers high-density crowd analytics and cashless payments, check out the engineering solutions at StampIQ.