Building Offline-First Sync with sqflite and Riverpod

The Illusion of Constant Connectivity

In the software ecosystem here in Benin City, we often build for environments where mobile data is a luxury, not a constant. Many engineers fall into the trap of assuming a persistent connection to the backend. They treat their API as the 'source of truth' and the local cache as a mere afterthought. This is a fundamental architectural error. When you design for the 99% uptime, you break the experience for the 1%—which, in a developing economy, happens during every tunnel, basement visit, or network fluctuation.

Offline-first isn’t just a feature; it’s a commitment to data integrity. Your application must behave as if it is always offline, using the network only as a background synchronization mechanism. To achieve this, we need to treat the database as our primary state provider and use Riverpod to expose that state reactively to our UI. If you are still fetching data directly into a ViewModel or a Cubit without a local persistence layer, you are building on sand.

Rethinking the Provider Graph for Local Persistence

In a standard Riverpod setup, we often see providers pointing directly to repositories that perform HTTP calls. This is insufficient for offline-first. Your provider graph should be layered: the UI talks to a Notifier, the Notifier talks to a Repository, and the Repository acts as a mediator between your sqflite instance and your API client.

Crucially, the UI should never know about the network state directly. It should only subscribe to the database state. When the network is available, the repository performs a background sync, updates the sqflite database, and because our Riverpod providers are watching the database, the UI updates automatically. This creates a single source of truth: the local database. If the database updates, the UI updates. This is the definition of reactive local-first architecture.

Step-by-Step: Constructing the Sync Engine

To build this, we need to decouple our network logic from our local state observation. Let’s walk through the implementation.

1. Defining the Database Interface

We don't want to expose raw sqflite queries to our UI. We encapsulate the database interaction in a repository.

class TaskRepository {
  final Database _db;

  TaskRepository(this._db);

  // Stream the data from the DB
  Stream<List<Task>> watchTasks() {
    return _db.query('tasks').asStream().map((rows) => rows.map(Task.fromMap).toList());
  }

  Future<void> addTask(Task task) async {
    await _db.insert('tasks', task.toMap());
  }

  // Sync logic performed in the background
  Future<void> syncWithServer() async {
    final pending = await _db.query('tasks', where: 'synced = 0');
    // ... push to API, then update local status to synced = 1
  }
}

2. Creating the Riverpod Provider

We expose the data stream as a StreamProvider. This is the secret sauce. By using a StreamProvider, Riverpod automatically handles the subscription lifecycle and the asynchronous state transitions (loading, error, data).

final taskRepositoryProvider = Provider((ref) => TaskRepository(databaseInstance));

final tasksProvider = StreamProvider<List<Task>>((ref) {
  return ref.watch(taskRepositoryProvider).watchTasks();
});

final taskNotifierProvider = NotifierProvider<TaskNotifier, void>(TaskNotifier.new);

class TaskNotifier extends Notifier<void> {
  @override
  void build() {}

  Future<void> addTask(Task task) async {
    final repo = ref.read(taskRepositoryProvider);
    await repo.addTask(task);
    // Trigger sync without blocking the UI
    ref.read(syncServiceProvider).enqueueSync(); 
  }
}

The Architecture Pattern: The Sync Service

One common mistake I see is developers trying to bundle sync logic into the TaskRepository. Do not do this. Syncing is a side effect. It should live in its own SyncService which is triggered by an event-bus or a simple Riverpod command pattern.

By segregating the sync logic, you gain the ability to implement exponential backoff, connection checking, and error logging without complicating the data retrieval logic. The SyncService should listen for connectivity changes using connectivity_plus and trigger its internal loops accordingly. When a sync is successful, the SyncService writes the finalized data back into sqflite, which triggers our StreamProvider to push a fresh list to the UI. The UI doesn't know the network existed. It just sees the data change.

Addressing the Complexity of State Sync

What happens if the server update fails? What if the user deletes a local task that hasn't been pushed yet? These are the real-world problems that keep senior engineers up at night.

My advice: use a 'local state' flag for every row in your database. Every record should have a synced boolean (or a version timestamp). When the UI modifies a record, you set synced = 0. The sync engine only looks for rows where synced = 0. Once successfully pushed to the backend, the engine sets synced = 1. If the network is absent, the data remains in your local cache, perfectly safe.

Tips and Troubleshooting

1. Avoid the 'Big Rebuild': When using StreamProvider with sqflite, be careful with how you trigger stream emissions. If you emit a new list on every row insert, you might trigger too many rebuilds. Consider using a BehaviorSubject or a throttled stream to debounce database updates.

2. Handle schema changes: sqflite versioning is manual. Write a robust migration strategy from Day 1. If you change your schema and don't handle the migration, the user loses their locally stored data. In an offline-first app, this is a catastrophic data loss event.

3. Use Code Generation: Manually mapping JSON to Dart objects is a recipe for bugs. Use freezed and json_serializable. It enforces the type safety required to keep your local database and your remote API in sync.

4. The family Modifier for Detail Screens: If you have a task detail screen, don't pass the whole task object. Pass the id to a taskByIdProvider.family(id) and let the provider perform a local database lookup. This ensures that the detail screen is always watching the most current version of that record.

Decision Framework: Is Offline-First Right For You?

Before you start, ask yourself these three questions. If the answer to any of them is 'Yes', commit to this pattern. If not, you might be over-engineering.

1. Data Sovereignty: Does the user need to view or edit their data while in 'airplane mode'? If the app displays a blank screen without 4G, you have failed the user.
2. Latency Sensitivity: Is your UI feel-good factor dependent on sub-100ms response times? Network calls are non-deterministic; local SQL queries are fast. If you want a 'snappy' UI, local-first is your only path.
3. Reliability Requirements: Does your application deal with critical data (e.g., invoices, logs, financial entries)? If a network interruption means the user loses their progress, you have built a fragile application.

Conclusion

Building an offline-first application with Riverpod and sqflite isn't about writing more code; it's about shifting your mindset. You are no longer building a 'web-view wrapper' that happens to run on a phone; you are building a data-management engine. By treating the local database as the primary source of truth and the network as a mere synchronization participant, you create an application that is inherently more resilient and user-friendly.

In our teams here in Nigeria, we've found that this pattern reduces the 'it works on my machine' bug reports by nearly 70%. When you stop relying on the network to provide the application state, you remove the biggest variable in your software's performance. Start by building your stream-based repository, hook it into a StreamProvider, and let the reactive nature of Riverpod handle the rest. Your users deserve an app that works whenever they decide to open it, regardless of the signal bars on their device.