Handling Concurrent Database Writes in Offline-First Flutter

Introduction: The Fallacy of the Simple Sync

In the world of enterprise Flutter development, we often see junior architects treat offline sync as a simple "upload all changes when the internet returns" problem. They implement a basic queue, hit an API, and call it a day. But in a platform-scale environment—where 10, 50, or even 100 field agents in a remote facility are performing simultaneous updates on the same supply-chain records—that approach fails within the first hour.

When you build for offline-first, you are not just synchronizing state; you are managing a distributed system. You have to account for latency, network partitions, and the inevitable "Split-Brain" scenario where two agents update the same field in contradictory ways while disconnected. As a platform architect, I’ve learned that the moment you disconnect a device, you lose the illusion of global consistency. The challenge then becomes how to reconstruct that consistency once connectivity is restored. To do this, we must move beyond "Last-Write-Wins" (LWW) and start thinking in terms of Vector Clocks, Operational Transforms, and intent-based delta-tracking.

The Conflict Scenario Matrix

Before writing a single line of Dart code, you must categorize your conflict scenarios. Not all data is created equal, and applying the same resolution strategy to every field in your JSON payload is a recipe for data corruption. I use a conflict matrix to categorize how we handle data collision:

1. Atomic State Updates: These are fields where the final state is independent of the previous state (e.g., changing a status from 'PENDING' to 'COMPLETED'). LWW is acceptable here, provided you have a reliable source of truth via server-side timestamps.
2. Additive/Commutative Changes: These are counters, inventory increments, or log entries. If Agent A adds 5 units and Agent B adds 3 units while offline, the result must be +8. A LWW strategy would result in either +5 or +3, losing the other agent's work entirely. We track these as deltas.
3. Relational/Structural Changes: These occur when one user deletes an entity while another is attempting to update a child record. This requires a tombstone-based reconciliation strategy.

Designing for these scenarios requires that your local Flutter storage (usually Drift or SQFlite) preserves metadata that isn't sent to the UI. You need a sync_metadata table that tracks local_modified_at, sync_status, and a version_vector for every row in your database.

Step-by-Step Implementation of an Intent-Based Sync Engine

To build a robust conflict-resolution engine in Flutter, you need to decouple the state of the local database from the synchronization intent.

Step 1: Implement Local Versioning

Don’t rely on database row IDs alone. Add a version column and a client_id to every table. Every time a record is mutated offline, increment the version and update the last_modified_timestamp.

Step 2: The Reconciliation Loop

Instead of pushing the state, push a log of operations. If the server detects a version mismatch, the client is responsible for pulling the latest state and attempting a merge based on your predefined rules.

Step 3: Define Resolution Handlers

Implement a handler interface that defines how to merge two versions of a record.

abstract class ConflictResolver<T> {
  T resolve(T local, T remote, T ancestor);
}

class InventoryResolver implements ConflictResolver<int> {
  @override
  int resolve(int local, int remote, int ancestor) {
    // Delta = (Current - Original)
    int localDelta = local - ancestor;
    int remoteDelta = remote - ancestor;
    return ancestor + localDelta + remoteDelta;
  }
}

Step 4: Validate with Asynchronous Simulation

Before deploying to field agents, run a simulation that forces 100 concurrent sync requests against a mock server. If your conflict resolution logic causes deadlocks or data loss, your simulation will catch it.

Handling Conflict in the Flutter Data Layer

In Flutter, we use the Repository pattern to abstract the sync complexity from the UI. The Repository layer checks the sync_status flag. If the flag is set to SYNC_PENDING, the Repository doesn't just return the current state; it returns a merged state calculated on the fly. This prevents the UI from flickering or showing incorrect data while the background sync worker is negotiating with the server.

// A robust repository approach for handling pending syncs
class ProductRepository {
  final LocalDb _db;
  final ApiService _api;

  Future<Product> getProduct(String id) async {
    final local = await _db.getProduct(id);
    if (local.syncStatus == SyncStatus.pending) {
      // Check for remote update and run reconciliation
      final remote = await _api.fetchProduct(id);
      return reconcile(local, remote);
    }
    return local;
  }
}

One common pitfall is the "Zombie Sync" issue. This occurs when a user closes the app while the sync loop is halfway through. Your database schema must support idempotent operations. If you attempt to apply the same patch twice because of a crash, the server or the local database should recognize that the version_vector has already moved forward and reject the older update. Never assume the sync will complete; design the database to be self-healing.

Pro Tips for Large-Scale Offline Syncing

• Pro Tip 1: The 'Tombstone' Pattern: Never perform a hard delete in an offline-first app. If a record is deleted, mark it with an is_deleted flag and a deleted_at timestamp. This allows your sync engine to propagate the deletion to other clients without losing the record history required for conflict resolution.
• Pro Tip 2: Batching and Chunking: When a device reconnects after being offline for a week, do not send 5,000 individual JSON requests. Bundle them into a single transaction blob that the server can process atomically.
• Pro Tip 3: Delta Snapshots: Instead of sending the full object, send a JSON patch (RFC 6902). This reduces bandwidth and minimizes the chances of a conflict occurring on an unrelated field within the same record.
• Pro Tip 4: Monitoring Sync Health: Include a background monitoring service in your Flutter app that logs sync duration, failure rates, and conflict frequency to an analytics provider (like Sentry or Firebase). If a specific user consistently hits conflict-heavy scenarios, your UI might need a custom 'Conflict Resolution' screen to allow the user to manually select the correct data.

Conclusion: The Architecture of Resilience

Handling concurrent database writes is the hardest part of building offline-first enterprise software. It is a balancing act between user experience—allowing them to work without thinking about the network—and data integrity. If you ignore the edge cases, you end up with a system where data silently vanishes, or worse, where incorrect inventory and audit logs are generated.

By treating the sync engine as a distributed system, implementing intent-based versioning, and using a rigorous conflict resolution matrix, you can build a system that is not only offline-ready but also robust enough to handle the chaotic nature of real-world field operations. Remember, the goal of an architect is not to eliminate conflicts—that is impossible in a distributed environment—but to ensure that when they occur, they are resolved predictably, transparently, and without compromising the integrity of the enterprise data. The complexity is the price of the reliability; do not try to shortcut it with simple solutions. Embrace the conflict-driven design, and your architecture will survive the test of time.