SQLite as a Durable Store-and-Forward Buffer

Keeping Data Flowing Reliably Through Intermittent and Unreliable Networks

In our previous article, Designing Stateful Alert Engines with SQLite, we built an alert system that could remember what was happening. Alerts survived application restarts, repeated detections were deduplicated, notifications respected cooldowns, operators could acknowledge incidents, and recovery became an explicit state transition.

But eventually, some of that information needs to leave the device.

A remote monitoring station may need to send telemetry to a central server. A factory gateway may need to upload production events. A vehicle may need to report diagnostics. An edge device may need to forward alerts to a cloud platform.

Then the network disappears.

Perhaps the connection is gone for ten seconds.

Perhaps it disappears for six hours.

Perhaps requests reach the server, but acknowledgements never make it back.

A fragile application treats this as an exception.

A reliable application treats it as normal operating conditions.

Instead of requiring the network to be available when data is produced, we can place SQLite between the producer and the network.

Application
    ↓
SQLite
    ↓
Network
    ↓
Remote System

If the network works, data flows through quickly.

If it doesn’t, SQLite keeps the data safely until delivery becomes possible again.

That is the essence of a store-and-forward buffer.

Store First, Send Second

The most important architectural decision is simple:

Persist important data before attempting to transmit it.

Consider the fragile approach:

Sensor
   ↓
Create telemetry
   ↓
Send HTTP request
   ↓
Network failure
   ↓
What happens to the data?

The application now needs to decide whether the reading exists anywhere durable.

If it only lived in memory, a restart could destroy it.

A store-and-forward design changes the order:

Sensor
   ↓
Create telemetry
   ↓
Store locally in SQLite
   ↓
Mark for delivery
   ↓
Attempt transmission

Now network availability no longer determines whether the data survives.

The local database becomes the durable boundary.

Why an In-Memory Queue Isn’t Enough

An in-memory queue can be extremely fast.

For disposable work, it may be perfectly appropriate.

But imagine an edge gateway has 4,000 unsent telemetry batches waiting for connectivity to return.

Then:

Power failure

If those batches exist only in memory:

4,000 queued batches
        ↓
Application stops
        ↓
Queue disappears

After restart, there is nothing to retry.

A durable SQLite buffer gives us:

4,000 queued batches
        ↓
Power failure
        ↓
Restart
        ↓
4,000 queued batches still exist

The application can continue where it stopped.

This matters whenever losing data is more expensive than delaying it.

Separate Business Data from Delivery State

Suppose our telemetry table already contains:

CREATE TABLE Telemetry (
    TelemetryID INTEGER PRIMARY KEY,
    DeviceID INTEGER NOT NULL,
    MetricID INTEGER NOT NULL,
    Value REAL NOT NULL,
    RecordedAt INTEGER NOT NULL
);

We could add fields such as:

Uploaded
RetryCount
LastAttemptAt

directly to every telemetry row.

That works for small systems.

But it mixes two different concerns:

Telemetry state

What happened?

Delivery state

Has the remote system received it?

For a more flexible architecture, keep delivery work separately.

CREATE TABLE OutboundQueue (
    QueueID INTEGER PRIMARY KEY,
    MessageType TEXT NOT NULL,
    Payload TEXT NOT NULL,

    Status TEXT NOT NULL DEFAULT 'pending',

    CreatedAt INTEGER NOT NULL,
    NextAttemptAt INTEGER NOT NULL,
    AttemptCount INTEGER NOT NULL DEFAULT 0,

    LastAttemptAt INTEGER,
    LastError TEXT,

    DeliveredAt INTEGER
);

Now the queue can forward more than telemetry.

It might contain:

telemetry_batch
alert
device_health
configuration_change
diagnostic_event
daily_summary

SQLite becomes a general durable outbound buffer.

Don’t Queue Every Sensor Reading Individually

As we explored when storing IoT telemetry streams with SQLite, high-frequency sensors can generate enormous numbers of measurements, making efficient batching an important part of the data pipeline.

Suppose a device produces 100 readings per second.

Creating 100 separate network messages per second is rarely efficient.

Instead:

Sensor readings
      ↓
SQLite telemetry
      ↓
Batch selection
      ↓
Outbound message
      ↓
Network

A batch might contain:

500 readings

or:

30 seconds of telemetry

depending on the workload.

Batching reduces:

• HTTP overhead

• TLS overhead

• Connection churn

• Remote API requests

• Queue rows

• Delivery bookkeeping

The best batch size depends on payload size, available memory, network conditions, and server limits.

Two Useful Queue Designs

There are two broad ways to represent outbound data.

Payload Queue

The queue stores the actual serialized payload:

QueueID
MessageType
Payload
Status

Advantages:

• The exact message is frozen at creation time.

• Delivery doesn’t need to reread source tables.

• Retries resend the same payload.

Disadvantages:

• Data may exist twice, once in the source table and once in the queue.

• Large payloads increase database size.

Reference Queue

The queue stores references:

QueueID
FirstTelemetryID
LastTelemetryID
Status

The delivery worker builds the payload when needed.

Advantages:

• Less duplicated data.

• Queue rows remain small.

Disadvantages:

• Source rows must remain available until delivery succeeds.

• Rebuilding payloads must be deterministic.

• Retention becomes more complicated.

Neither design is universally better.

For critical messages where retrying the exact same content matters, storing the payload can be attractive.

For huge telemetry streams, references may reduce duplication.

The Queue Needs Explicit States

A useful outbound message should have a lifecycle.

For example:

PENDING
   ↓
IN_FLIGHT
   ↓
DELIVERED

Failures may produce:

PENDING
   ↓
IN_FLIGHT
   ↓
RETRY

and permanent failures may eventually become:

FAILED

So our operational state might be:

pending
in_flight
retry
delivered
failed

Explicit states make it much easier to answer:

• What is waiting?

• What is currently being attempted?

• What failed?

• What should be retried?

• What has been delivered?

A queue that only contains a boolean Sent flag quickly becomes limiting.

Claim Work Before Sending It

Suppose two delivery workers are running.

Both execute:

SELECT QueueID, Payload
FROM OutboundQueue
WHERE Status = 'pending'
ORDER BY QueueID
LIMIT 100;

They could select the same messages.

Both may then send them.

We need to claim work.

A worker can start a short transaction:

BEGIN IMMEDIATE;

select eligible work, then update it:

UPDATE OutboundQueue
SET
    Status = 'in_flight',
    LastAttemptAt = ?
WHERE QueueID IN (...);

Then commit.

Only after the transaction completes should the worker perform the network request.

This is important.

Never Hold a SQLite Transaction Open During Network I/O

A network request might take:

50 ms

or:

30 seconds

or never complete until a timeout occurs.

Do not keep a write transaction open while waiting.

Avoid:

BEGIN
   ↓
Select queue rows
   ↓
Send HTTP request
   ↓
Wait...
   ↓
Wait...
   ↓
COMMIT

Instead:

BEGIN
   ↓
Claim work
   ↓
COMMIT

Send HTTP request

BEGIN
   ↓
Record result
   ↓
COMMIT

SQLite transactions should protect database state.

They should not become network locks.

What Counts as Successful Delivery?

This sounds obvious:

HTTP 200 = success

But reliable delivery is more subtle.

Imagine:

Device sends batch 812
        ↓
Server receives batch
        ↓
Server stores it
        ↓
Connection drops
        ↓
Device never receives response

Did delivery succeed?

From the server’s perspective:

Yes.

From the device’s perspective:

Unknown.

The device must retry because it cannot safely assume success.

Now the server may receive batch 812 twice.

This is one of the central problems in distributed communication.

Exactly Once Is the Wrong Mental Model

Developers often want:

Send every message exactly once.

Across an unreliable network, guaranteeing that at the transport level is much harder than it sounds.

A more practical design is:

At-least-once delivery
+
Idempotent processing

The sender may transmit the same logical message more than once.

The receiver recognizes duplicates and ensures they don’t produce duplicate effects.

This distinction is essential.

Give Every Message a Stable Identity

Each outbound message should have an identifier that survives retries.

For example:

device-42:telemetry:812

or a UUID:

550e8400-e29b-41d4-a716-446655440000

Add it to the queue:

CREATE TABLE OutboundQueue (
    QueueID INTEGER PRIMARY KEY,
    MessageID TEXT NOT NULL UNIQUE,
    MessageType TEXT NOT NULL,
    Payload TEXT NOT NULL,
    Status TEXT NOT NULL DEFAULT 'pending',
    CreatedAt INTEGER NOT NULL,
    NextAttemptAt INTEGER NOT NULL,
    AttemptCount INTEGER NOT NULL DEFAULT 0,
    LastAttemptAt INTEGER,
    LastError TEXT,
    DeliveredAt INTEGER
);

Every retry uses the same MessageID.

Do not generate a new identifier each time the HTTP request is attempted.

Otherwise, the receiver cannot tell that two requests represent the same logical message.

The Receiver Must Participate

Suppose the central server keeps:

CREATE TABLE ReceivedMessage (
    MessageID TEXT PRIMARY KEY,
    ReceivedAt INTEGER NOT NULL
);

When a message arrives:

MessageID already exists?
        ↓
YES → already processed
NO  → process and record

The receiver can safely acknowledge repeated delivery without repeating the business operation.

For example:

First request:
Store telemetry batch
Record MessageID
Return success

Retry:
MessageID already exists
Return success
Do not store telemetry again

Now network ambiguity becomes manageable.

Retries Need Backoff

When a network request fails, retrying immediately can make things worse.

Suppose 10,000 edge devices lose access to the server simultaneously.

If every device retries constantly:

failure
retry
failure
retry
failure
retry

the recovering server may be overwhelmed before it can stabilize.

Instead, use exponential backoff.

For example:

Attempt 1 → wait 5 seconds
Attempt 2 → wait 10 seconds
Attempt 3 → wait 20 seconds
Attempt 4 → wait 40 seconds
Attempt 5 → wait 80 seconds

The delay grows as failures continue.

Store the next eligible attempt directly:

NextAttemptAt

Then workers can query:

SELECT *
FROM OutboundQueue
WHERE Status IN ('pending', 'retry')
  AND NextAttemptAt <= ?
ORDER BY QueueID
LIMIT 100;

The database itself now tells us what work is eligible.

Add Jitter

Imagine 5,000 devices all lose connectivity at 10:00.

They all retry after:

5 seconds
10 seconds
20 seconds
40 seconds

Even with exponential backoff, they remain synchronized.

At every interval, thousands of devices hit the server together.

Add a small randomized component:

Delay = Backoff + RandomJitter

Now retries spread out.

For example:

Device A → 42 seconds
Device B → 47 seconds
Device C → 39 seconds
Device D → 51 seconds

This reduces synchronized retry storms.

The application can calculate the delay and persist the resulting NextAttemptAt.

Not Every Failure Should Be Retried

Suppose the server responds:

503 Service Unavailable

Retrying later makes sense.

But suppose it responds:

400 Bad Request

Sending the same invalid payload 50 more times probably won’t help.

Failures should be classified.

For example:

Network timeout      → retry
Connection failure   → retry
HTTP 429             → retry later
HTTP 503             → retry
HTTP 400             → permanent failure
Invalid payload      → permanent failure
Authentication error → policy-dependent

A message that cannot succeed without intervention should eventually move to:

failed

rather than remaining in the active queue forever.

Failed Messages Need Somewhere to Go

A permanently failed message should not simply disappear.

Keep it for diagnosis.

Status: failed
AttemptCount: 8
LastError: invalid_payload

This is similar to a dead-letter queue.

Operators can inspect:

• The message

• Its creation time

• Number of attempts

• Last failure

• Message type

Depending on the system, they may correct the underlying problem and requeue it.

Durability includes preserving failures long enough to understand them.

Recover Messages Left In Flight

Now imagine this sequence:

Worker claims messages
      ↓
Status = in_flight
      ↓
Application crashes

After restart, those rows are still:

in_flight

If the application only processes pending rows, they are stuck forever.

We need a lease or timeout.

Add:

ClaimedAt
ClaimedBy

A worker owns the message only temporarily.

If:

CurrentTime - ClaimedAt > LeaseTimeout

the message becomes eligible for recovery.

For example:

UPDATE OutboundQueue
SET
    Status = 'retry',
    ClaimedAt = NULL,
    ClaimedBy = NULL,
    NextAttemptAt = ?
WHERE Status = 'in_flight'
  AND ClaimedAt < ?;

Now a crash cannot permanently strand work.

Don’t Assume Connectivity Checks Are Truth

A common approach is:

Ping server
   ↓
Online?
   ↓
Send data

But network state can change immediately after the check.

A device may have Wi-Fi connectivity but no route to the server.

DNS may fail.

TLS negotiation may fail.

The remote API may be down.

A connectivity indicator can help scheduling, but the actual delivery attempt is the real test.

Design the queue so failed sends are normal.

Don’t design it around perfect network prediction.

Prioritize Important Messages

Suppose a remote device reconnects after twelve hours.

Its buffer contains:

200,000 telemetry readings
4 critical alerts
24 health summaries
3 configuration acknowledgements

The critical alerts created by our stateful SQLite alert engine may represent active incidents that require human attention, so treating them exactly like routine telemetry would defeat the purpose of assigning delivery priorities.

Should everything be delivered strictly in creation order?

Maybe not.

Critical alerts may deserve priority.

Add:

Priority INTEGER NOT NULL DEFAULT 100

Then select:

SELECT *
FROM OutboundQueue
WHERE Status IN ('pending', 'retry')
  AND NextAttemptAt <= ?
ORDER BY Priority ASC, QueueID ASC
LIMIT 100;

For example:

10   Critical alert
20   Device health
50   Configuration response
100  Telemetry

Now important operational messages can move ahead of bulk data.

But Be Careful with Ordering

Priority introduces another question.

Some messages must remain ordered.

Suppose:

Configuration version 11
Configuration version 12

Delivering version 12 before version 11 may or may not be acceptable.

Likewise, some event streams rely on sequence.

The queue therefore needs to distinguish between:

Messages that may be reordered

and:

Messages that must preserve stream order

For ordered streams, a useful identity might be:

StreamID
SequenceNumber

The receiver can detect:

• Duplicates

• Missing sequence numbers

• Out-of-order delivery

Reliability isn’t only about eventually delivering bytes.

Sometimes order carries meaning.

Queue Growth Must Be Bounded

Imagine the network is unavailable for three weeks.

Telemetry continues arriving.

SQLite keeps storing it.

Eventually:

Disk full

Durability does not mean unlimited storage.

A production system needs a capacity policy.

Measure:

Average data generated per hour
Expected maximum outage
Available disk capacity
Reserved free space
Queue growth rate

Suppose:

Telemetry generation = 100 MB/day
Expected worst outage = 7 days

Then the buffer needs at least:

700 MB

plus indexes, database overhead, WAL space, safety margin, and other application data.

Capacity planning is part of reliability.

What Should Happen When the Buffer Is Nearly Full?

This is a business decision, not merely a database decision.

Possible strategies include:

• Stop accepting low-priority telemetry

• Increase aggregation

• Downsample older queued data

• Delete expendable diagnostics

• Preserve alerts while dropping routine metrics

• Apply retention rules

• Enter a degraded operating mode

For example:

Storage < 70%
Normal operation

Storage 70–85%
Reduce nonessential diagnostics

Storage 85–95%
Aggressively aggregate routine telemetry

Storage > 95%
Preserve only critical operational data

The exact policy depends on what data can safely be sacrificed.

What matters is deciding before the disk becomes full.

Downsampling Can Protect the Buffer

Our earlier time-series architecture becomes useful again.

Suppose raw telemetry has waited for upload for several days.

The rollup and retention techniques from our guide to downsampling time-series data with SQLite can also help decide whether older buffered telemetry still needs to be preserved at its original resolution.

Instead of preserving every second-level reading indefinitely, the system might eventually forward:

Minute summaries

or:

Hourly summaries

for older periods.

Whether this is acceptable depends on the application.

A regulatory system may require every raw measurement.

A weather station may be perfectly happy sending hourly summaries after a long outage.

The store-and-forward policy should understand the value of the data, not merely its age.

WAL Helps, but Watch the Workload

For a system continuously writing telemetry while delivery workers update queue state, WAL mode is often useful:

SQLite's official Write-Ahead Logging documentation explains how WAL allows readers and a writer to operate concurrently, while also documenting checkpoint behavior and the operational considerations that come with WAL mode.

PRAGMA journal_mode = WAL;

It allows readers and the writer to coexist more smoothly.

But the queue can generate significant write activity:

Insert
Claim
Retry update
Claim again
Delivery update
Delete later

At high volume, queue bookkeeping itself becomes part of the workload.

Batch operations wherever practical.

Instead of marking 500 messages delivered with 500 separate transactions, update them in one short transaction.

Delivered Rows Should Not Live Forever

After successful delivery, do we need to keep every queue record permanently?

Usually not.

The original telemetry may already exist elsewhere, and the remote system has accepted the message.

Keeping millions of:

Status = delivered

rows forever turns the delivery queue into another historical database.

Instead, define retention.

For example:

Pending / retry:
Keep until resolved

Failed:
Keep 30 days

Delivered:
Keep 7 days

The short delivered retention window can still help with diagnostics.

Afterward, clean it up in batches.

Delete Queue History in Batches

Avoid massive cleanup transactions.

For example:

DELETE FROM OutboundQueue
WHERE QueueID IN (
    SELECT QueueID
    FROM OutboundQueue
    WHERE Status = 'delivered'
      AND DeliveredAt < ?
    ORDER BY QueueID
    LIMIT 5000
);

Run the cleanup periodically.

As with telemetry retention, SQLite can reuse the freed pages for future queue activity.

The database file does not need to shrink after every cleanup.

Monitor the Queue, Not Just the Network

A healthy network does not guarantee a healthy delivery system.

Useful operational metrics include:

Pending message count
Oldest pending message age
Retry count
Failed message count
Messages delivered per minute
Bytes waiting
Average delivery latency
Current backoff delay
Queue growth rate

One metric is especially useful:

age of the oldest undelivered message

Suppose the queue contains only 30 messages.

That sounds healthy.

But if the oldest has been waiting for four days, something may be wrong.

Queue depth and queue age tell different stories.

Store Health State Locally

A monitoring table might record:

CREATE TABLE DeliveryHealth (
    Destination TEXT PRIMARY KEY,
    LastSuccessAt INTEGER,
    LastFailureAt INTEGER,
    ConsecutiveFailures INTEGER NOT NULL DEFAULT 0,
    LastError TEXT
);

Now the device can answer:

When did cloud delivery last work?
How many attempts have failed?
How long have we been disconnected?

That information remains available even when the remote monitoring platform is unreachable.

This is particularly valuable for technicians diagnosing edge devices locally.

Multiple Destinations Complicate Delivery

Suppose the same event must be sent to:

Cloud analytics
Operations platform
Audit service

If one succeeds and another fails, a single Delivered flag is no longer enough.

Delivery state belongs to the destination.

A separate structure can model this:

CREATE TABLE MessageDelivery (
    MessageID TEXT NOT NULL,
    Destination TEXT NOT NULL,
    Status TEXT NOT NULL,
    AttemptCount INTEGER NOT NULL DEFAULT 0,
    NextAttemptAt INTEGER,
    DeliveredAt INTEGER,

    PRIMARY KEY (MessageID, Destination)
);

Now:

Message 812

Cloud analytics     delivered
Operations          retry
Audit service       delivered

Each destination progresses independently.

Store-and-Forward Is Not Synchronization

These concepts overlap, but they solve different problems.

Synchronization asks:

How do two systems reconcile changing state?

Store-and-forward asks:

How do we reliably move data that has already been produced?

A sync engine may need:

• Conflict resolution

• Version comparison

• Pull and push

• Record merging

A store-and-forward pipeline may simply need:

Create message
Persist message
Deliver message
Confirm message
Remove message

Keeping these concepts separate prevents unnecessary complexity.

Store-and-Forward Is Also Not a Full Message Broker

SQLite can provide an excellent durable local buffer.

It doesn’t need to become Kafka, RabbitMQ, or a cloud messaging platform.

Those systems solve much broader distributed messaging problems.

SQLite is particularly compelling when the queue belongs to:

One application
One device
One gateway
One local service

and the requirement is:

Don’t lose important outbound work when the network or process fails.

That’s a narrower problem, and SQLite handles it very well.

A Complete Edge Delivery Architecture

We can now put the system together.

Sensors
   ↓
SQLite Telemetry
   ↓
Local Analysis
   ↓
Alerts / Summaries
   ↓
Outbound Queue
   ↓
Delivery Worker
   ↓
Network
   ↓
Remote API

When connectivity fails:

Outbound Queue
      ↓
Persist
      ↓
Backoff
      ↓
Retry

When the application crashes:

Restart
   ↓
Recover expired claims
   ↓
Resume eligible work

When the server receives a duplicate:

MessageID
   ↓
Already processed?
   ↓
YES
   ↓
Return success

When the device reconnects after a long outage:

Critical messages
      ↓
Priority delivery
      ↓
Bulk telemetry
      ↓
Backlog drains gradually

The network has stopped being a single point of failure.

Example: Remote Pump Station

Imagine a pump station in a remote agricultural area.

It records:

Pressure
Flow rate
Motor temperature
Vibration
Power consumption

Normally, telemetry uploads continuously.

At 02:14, the mobile connection disappears.

SQLite continues storing local measurements.

The anomaly detector notices increasing motor temperature.

The alert engine creates one active incident.

The outbound buffer stores:

Telemetry batches
Motor alert
Device health reports

Delivery attempts fail.

The queue backs off.

At 03:00, the motor alert escalates locally.

The network is still unavailable, but the alert state remains intact.

At 05:37, connectivity returns.

The delivery worker first sends:

Critical motor alert

then:

Device health

then begins draining:

Telemetry backlog

Halfway through a telemetry upload, the connection drops again.

The sender doesn’t know whether the server received the last batch.

It retries the same MessageID.

The server recognizes the duplicate and returns success without storing the batch twice.

Eventually, the backlog reaches zero.

Nothing required the network to remain continuously available.

Nothing important existed only in memory.

And duplicate transmission did not become duplicate processing.

That is what durable store-and-forward gives us.

Best Practices

When using SQLite as a store-and-forward buffer:

• Persist important data before attempting network delivery.

• Don’t rely on memory-only queues for work that must survive restarts.

• Separate business data from delivery state.

• Batch high-volume telemetry when practical.

• Give every logical message a stable identifier.

• Design for at-least-once delivery.

• Make receivers idempotent.

• Use explicit queue states.

• Claim work transactionally.

• Never hold database transactions open during network requests.

• Use exponential backoff for repeated failures.

• Add jitter when many devices may reconnect together.

• Distinguish transient from permanent failures.

• Preserve permanently failed messages for diagnosis.

• Use leases so crashed workers don’t strand in-flight work.

• Treat connectivity checks as hints, not guarantees.

• Prioritize critical messages when appropriate.

• Preserve ordering where the business process requires it.

• Plan for maximum queue capacity.

• Define degradation behavior before storage becomes full.

• Batch queue updates and cleanup.

• Monitor queue age as well as queue depth.

• Persist delivery health locally.

• Track delivery independently when messages have multiple destinations.

• Retain delivered queue records only as long as they remain useful.

Closing Thoughts

Reliable applications cannot assume reliable networks.

Connections disappear. Requests time out. Servers restart. Responses get lost. Devices lose power. Applications crash halfway through delivery.

Trying to eliminate those failures is unrealistic.

A better architecture makes them survivable.

SQLite can sit between the application and the network as a durable buffer, preserving outbound work until delivery becomes possible, remembering retry state across restarts, controlling backoff, prioritizing important messages, and giving every logical message a stable identity.

The most important shift is conceptual:

The application does not need the network to accept data when the data is created.

It only needs enough local durability to preserve that data until the network becomes useful again.

Once we adopt that model, however, duplicate delivery becomes normal. A request can succeed remotely while appearing to fail locally, causing the sender to retry.

That leads directly to our next problem:

Designing Idempotent Data Pipelines with SQLite

Preventing duplicate processing when operations are retried.

Subscribe Now

Build Reliable Systems That Keep Working Offline

Networks fail. Devices restart. Requests time out. Reliable systems are designed to keep important data safe and moving anyway.

Subscribe to SQLite Forum for practical tutorials on durable pipelines, edge systems, offline-first design, telemetry, performance, and production-ready SQLite architecture.

Subscribe and keep building systems that stay reliable even when the network does not.