Supabase Offline-First Sync

Queue-based sync architecture for offline-first Expo apps using SQLite and Supabase.

Architecture Overview

Local Write → SQLite → Sync Queue → Background Worker → Supabase
                              ↑____________↓ (retry on failure)

Database Schema

Sync Queue Table

sql
1CREATE TABLE sync_queue (
2  id INTEGER PRIMARY KEY AUTOINCREMENT,
3  table_name TEXT NOT NULL,
4  record_id TEXT NOT NULL,
5  operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
6  data TEXT, -- JSON string for INSERT/UPDATE
7  retry_count INTEGER DEFAULT 0,
8  error_message TEXT,
9  created_at INTEGER NOT NULL,
10  processed_at INTEGER
11);
12
13CREATE INDEX idx_sync_queue_created ON sync_queue(created_at);
14CREATE INDEX idx_sync_queue_processed ON sync_queue(processed_at) WHERE processed_at IS NULL;

Core Implementation

1. Queue Operations

typescript
1interface SyncQueueItem {
2  id?: number;
3  table_name: string;
4  record_id: string;
5  operation: 'INSERT' | 'UPDATE' | 'DELETE';
6  data?: string;
7  retry_count: number;
8  created_at: number;
9}
10
11async function enqueueSync(
12  db: SQLiteDatabase,
13  table: string,
14  recordId: string,
15  operation: 'INSERT' | 'UPDATE' | 'DELETE',
16  data?: object,
17): Promise<void> {
18  await db.runAsync(
19    `INSERT INTO sync_queue (table_name, record_id, operation, data, retry_count, created_at)
20     VALUES (?, ?, ?, ?, 0, ?)`,
21    table,
22    recordId,
23    operation,
24    data ? JSON.stringify(data) : null,
25    Date.now(),
26  );
27}

2. Process Queue

typescript
1async function processSyncQueue(db: SQLiteDatabase, supabase: SupabaseClient): Promise<void> {
2  const pending = await db.getAllAsync<SyncQueueItem>(
3    `SELECT * FROM sync_queue 
4     WHERE processed_at IS NULL AND retry_count < 5
5     ORDER BY created_at ASC
6     LIMIT 50`,
7  );
8
9  // Process DELETEs first to avoid FK conflicts
10  const deletes = pending.filter((p) => p.operation === 'DELETE');
11  const others = pending.filter((p) => p.operation !== 'DELETE');
12
13  for (const item of [...deletes, ...others]) {
14    try {
15      await processQueueItem(db, supabase, item);
16    } catch (error) {
17      await markFailed(db, item.id!, error.message);
18    }
19  }
20}

3. Process Individual Item

typescript
1async function processQueueItem(
2  db: SQLiteDatabase,
3  supabase: SupabaseClient,
4  item: SyncQueueItem,
5): Promise<void> {
6  const { error } = await supabase.from(item.table_name).upsert(
7    {
8      id: item.record_id,
9      ...(item.data ? JSON.parse(item.data) : {}),
10      updated_at: new Date().toISOString(),
11    },
12    { onConflict: 'id' },
13  );
14
15  if (error) throw error;
16
17  // Mark as processed
18  await db.runAsync('UPDATE sync_queue SET processed_at = ? WHERE id = ?', Date.now(), item.id);
19}

React Integration

Sync Context Provider

typescript
1export function SyncProvider({ children }: { children: React.ReactNode }) {
2  const { db } = useDatabase();
3  const { supabase } = useSupabase();
4  const netInfo = useNetInfo();
5
6  useEffect(() => {
7    if (!db || !supabase || !netInfo.isConnected) return;
8
9    // Sync on connection restore
10    const interval = setInterval(() => {
11      processSyncQueue(db, supabase);
12    }, 30000); // Every 30 seconds
13
14    return () => clearInterval(interval);
15  }, [db, supabase, netInfo.isConnected]);
16
17  return children;
18}

Optimistic Updates Pattern

typescript
1function useCreateJournal() {
2  const queryClient = useQueryClient();
3  const { db } = useDatabase();
4
5  return useMutation({
6    mutationFn: async (entry: JournalEntry) => {
7      // 1. Save locally
8      await db.runAsync(
9        'INSERT INTO journal (id, encrypted_body, created_at) VALUES (?, ?, ?)',
10        entry.id,
11        await encryptContent(entry.content),
12        entry.created_at,
13      );
14
15      // 2. Queue for sync
16      await enqueueSync(db, 'journal', entry.id, 'INSERT', entry);
17
18      return entry;
19    },
20    onSuccess: () => {
21      queryClient.invalidateQueries({ queryKey: ['journal'] });
22    },
23  });
24}

Conflict Resolution

Last-write-wins with server timestamp:

typescript
1async function resolveConflict(local: JournalEntry, remote: JournalEntry): Promise<JournalEntry> {
2  const localTime = new Date(local.updated_at).getTime();
3  const remoteTime = new Date(remote.updated_at).getTime();
4
5  return remoteTime > localTime ? remote : local;
6}

Background Sync (Expo)

typescript
1import * as BackgroundFetch from 'expo-background-fetch';
2import * as TaskManager from 'expo-task-manager';
3
4const SYNC_TASK = 'background-sync';
5
6TaskManager.defineTask(SYNC_TASK, async () => {
7  const db = await openDatabase();
8  const supabase = createClient();
9
10  try {
11    await processSyncQueue(db, supabase);
12    return BackgroundFetch.BackgroundFetchResult.NewData;
13  } catch {
14    return BackgroundFetch.BackgroundFetchResult.Failed;
15  }
16});
17
18async function registerBackgroundSync() {
19  await BackgroundFetch.registerTaskAsync(SYNC_TASK, {
20    minimumInterval: 15 * 60, // 15 minutes
21    stopOnTerminate: false,
22    startOnBoot: true,
23  });
24}

Retry Strategy

Exponential backoff for failed items:

typescript
1async function markFailed(db: SQLiteDatabase, queueId: number, error: string): Promise<void> {
2  await db.runAsync(
3    `UPDATE sync_queue 
4     SET retry_count = retry_count + 1,
5         error_message = ?,
6         created_at = ? -- Delay retry
7     WHERE id = ?`,
8    error,
9    Date.now() + Math.pow(2, retry_count) * 60000, // Exponential backoff
10    queueId,
11  );
12}

Best Practices

1. Process DELETEs first - Avoids foreign key constraint errors
2. Batch operations - Process 50 items at a time
3. Encrypt before sync - Never send plaintext sensitive data
4. User-scoped sync - Always filter by user_id in Supabase RLS
5. Retry limit - Max 5 retries before manual intervention
6. Conflict timestamps - Use updated_at for last-write-wins