Handling Partial Device Connectivity

In this lesson, you will learn how to design a resilient queuing system that guarantees data integrity when a device loses its connection to the central server.

The Outbox Pattern

When building an offline-first application, the biggest challenge is ensuring that writes made while the device is "disconnected" are eventually processed by the server without data loss or duplication. We handle this by implementing an Outbox Pattern directly within our local database.

Instead of attempting a remote network call every time a user saves data, your application should perform a local write to a specialized outbox table. This table acts as a transactional buffer. Because the local database is embedded, this write is instantaneous and reliable, regardless of the network state.

Structuring the Queue

A robust outbox table needs to track the state of every operation. Consider this schema:

CREATE TABLE outbox (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    payload JSON NOT NULL, -- The data to be synced
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'pending' -- 'pending', 'processing', 'failed'
);

When your application is offline, it performs two operations in a single local transaction:

1. Update the local business state (e.g., INSERT INTO tasks ...).
2. Record the intent in the outbox table (INSERT INTO outbox ...).

Processing the Queue

Once the network becomes available, a background process queries the outbox for status = 'pending'. It attempts to push these items to the cloud. Only after the server confirms receipt (often using an idempotent key or UUID generated locally) does the client update the local outbox status to 'completed' or delete the record.

If the network cuts out mid-sync, the background worker simply stops. Because the status remains 'pending', the system will automatically retry the remaining items during the next connectivity window. By using local transactions to tie the business logic to the outbox insertion, you ensure that the queue remains perfectly synchronized with the state of the local database, creating a "source of truth" that survives restarts and connection drops.

Key Takeaway: Use a local outbox table to decouple data ingestion from network transmission, ensuring all writes are queued and persisted until a reliable connection allows for successful synchronization.