Building Offline-First APIs for Field Operations

The Problem

At Apsis Solutions, we built a Sales Force Automation platform for a nationwide FMCG distribution network. The platform served 7,000+ field agents who visited retailers, took orders, and managed inventory — often in areas with unreliable connectivity.

Agents would spend hours in rural areas with no internet. They needed to continue working: taking orders, checking stock, recording visits. When they got back online, everything needed to sync without data loss.

The Architecture

The mobile app maintained a local SQLite database. It was a full copy of the agent's working dataset — assigned retailers, product catalog, pricing, and pending orders.

When the agent was online, the app worked in real-time via REST and WebSocket. When offline, it switched to local-first mode.

┌─────────────────┐         ┌──────────────────┐
│  Mobile App      │  sync   │  Backend API      │
│  ┌─────────────┐ │ ◄─────► │  ┌──────────────┐ │
│  │ SQLite (local)│ │         │  │ PostgreSQL    │ │
│  └─────────────┘ │         │  └──────────────┘ │
│  ┌─────────────┐ │         │  ┌──────────────┐ │
│  │ Sync Queue   │ │         │  │ Redis (locks) │ │
│  └─────────────┘ │         │  └──────────────┘ │
└─────────────────┘         └──────────────────┘

The Sync Protocol

We designed a three-phase sync protocol:

Phase 1: Push Local Changes

The mobile app sends all pending local changes to the server. Each change has a unique client-generated UUID and a timestamp.

interface SyncPayload {
  deviceId: string;
  lastSyncTimestamp: string;
  changes: {
    id: string;          // UUID generated on device
    entity: string;      // "order" | "visit" | "attendance"
    operation: "create" | "update" | "delete";
    data: Record<string, unknown>;
    timestamp: number;
  }[];
}

Phase 2: Conflict Resolution

When two devices modify the same record while offline, we need a strategy. We chose last-write-wins with server-side validation:

async function processChange(change: SyncChange): Promise<void> {
  const serverRecord = await db.find(change.entity, change.id);
 
  if (!serverRecord) {
    // New record — insert directly
    await db.insert(change.entity, change.data);
    return;
  }
 
  if (change.timestamp > serverRecord.updatedAt.getTime()) {
    // Client has newer data — apply the change
    await db.update(change.entity, change.id, change.data);
  } else {
    // Server has newer data — send conflict to client in Phase 3
    conflicts.push({
      changeId: change.id,
      serverVersion: serverRecord,
    });
  }
}

Phase 3: Pull Server Changes

The server returns updates made by other systems (admin changes, SAP updates, other agents) and any conflicts.

interface SyncResponse {
  newSyncTimestamp: string;
  updates: SyncChange[];
  conflicts: {
    changeId: string;
    resolution: "server_wins" | "client_wins";
    serverVersion: Record<string, unknown>;
  }[];
  deletedIds: string[];
}

Idempotency

The mobile app might retry sync when the network drops mid-request. Every sync endpoint had to be idempotent:

@Post('sync')
async sync(@Body() payload: SyncPayload) {
  // Use Redis lock to prevent concurrent syncs from same device
  const lockKey = `sync:${payload.deviceId}`;
  const locked = await redis.set(lockKey, '1', 'NX', 'EX', 30);
 
  if (!locked) {
    throw new ConflictException('Sync already in progress for this device');
  }
 
  try {
    // Process changes in a database transaction
    return await db.transaction(async (tx) => {
      for (const change of payload.changes) {
        // Idempotency: skip if already processed
        const existing = await tx.findProcessedChange(change.id);
        if (existing) continue;
 
        await processChange(change);
        await tx.markChangeProcessed(change.id);
      }
 
      return buildResponse(tx, payload);
    });
  } finally {
    await redis.del(lockKey);
  }
}

What We Learned

1. Timestamps aren't clocks — Device clocks drift. We used a hybrid approach: client timestamps for ordering, server timestamps for authority. Never trust a client clock for anything critical.
2. Sync is a product feature, not an infrastructure detail — The first version had agents losing data on spotty connections. After investing in sync reliability, field adoption jumped 40%.
3. Batched sync beats real-time sync for offline — Syncing 50 changes in one request is more reliable than 50 individual API calls. The mobile app queues changes and flushes them in configurable batches.
4. Conflict resolution strategy should match domain — For orders, last-write-wins works. For inventory counts, you need merge logic. Know your data before choosing a strategy.

The Result

The offline sync system handled 7,000+ agents across Bangladesh, from Dhaka city centers to remote rural areas. Agents could work uninterrupted for 6+ hours offline, then sync in under 30 seconds when back online.