supabase_flutter_ultra 🚀

"Supabase with Offline Superpowers"

A powerful offline-first wrapper for supabase_flutter that adds intelligent caching, an offline write queue, delta sync, and multi-strategy conflict resolution — with zero breaking changes to existing Supabase APIs.

---

The Problem

supabase_flutter (573K downloads/week) has one critical gap: no offline support.

Without Ultra	With Ultra
insert throws PostgrestException when offline	Write queued, replayed on reconnect
select returns null/exception when offline	Returns cached data instantly
Realtime drops events during offline periods	Events buffered, replayed on reconnect
You rebuild SQLite + Queue + Retry in every project	One package, zero boilerplate

---

Quick Start

// main.dart
await SupabaseUltra.initialize(
  url: 'https://your-project.supabase.co',
  anonKey: 'your-anon-key',
  config: UltraConfig(
    defaultCachePolicy: CachePolicy(
      maxAge: Duration(hours: 2),
      strategy: CacheStrategy.cacheFirstThenNetwork,
    ),
    defaultConflictPolicy: ConflictPolicy.lastWriteWins,
    autoSyncOnReconnect: true,
  ),
);

// Use anywhere
final ultra = SupabaseUltra.instance.client;

// READ — returns cache instantly, refreshes in background
final todos = await ultra.ultra('todos')
    .select('id, title, done')
    .eq('done', false)
    .order('created_at')
    .limit(50)
    .fetch();

// WRITE — works online and offline
await ultra.ultra('todos').insert({'title': 'Buy milk', 'done': false});
await ultra.ultra('todos').update({'done': true}, matchColumn: 'id', matchValue: '123');
await ultra.ultra('todos').delete(id: '123');

// Bypass cache/queue (raw Supabase)
final user = ultra.raw.auth.currentUser;

---

Architecture

┌─────────────────────────────────────────────────┐
│                  UltraClient                     │  ← Your app talks to this
│              (Facade / Composition)              │
└──────┬──────────┬──────────┬──────────┬─────────┘
       │          │          │          │
  ┌────▼────┐ ┌───▼───┐ ┌───▼───┐ ┌───▼──────┐
  │  Cache  │ │ Queue │ │ Sync  │ │ Realtime │
  │ Manager │ │  Ops  │ │Engine │ │  Buffer  │
  └────┬────┘ └───┬───┘ └───┬───┘ └───┬──────┘
       │          │          │          │
  ┌────▼──────────▼──────────▼──────────▼──────┐
  │           SupabaseClient (original)          │  ← Untouched
  └─────────────────────────────────────────────┘

---

Features

🗄️ Intelligent Cache

• 5 strategies: networkFirstWithFallback, cacheFirstThenNetwork, cacheIfFreshElseNetwork, cacheOnly, networkOnly
• LRU eviction + TTL expiry — configurable per table
• Optimistic updates — UI reflects writes instantly before server confirmation
• SQLite backend (mobile/desktop) + Memory backend (web/tests)

📤 Offline Write Queue

• Every insert, update, delete while offline is persisted to SQLite and survives app restarts
• FIFO ordering — operations replay in exact enqueue order
• Exponential back-off retry with configurable max attempts
• Dead-letter for permanently failed operations

🔄 Delta Sync

• Uses updated_at > lastSyncAt filters — no full-table downloads
• Per-table sync checkpoints stored in SyncManifest
• Periodic background sync (configurable interval)
• Auto-sync on reconnect (configurable debounce delay)

⚔️ Conflict Resolution (5 Strategies)

Strategy	Use Case
serverWins	Server is authoritative (computed fields)
clientWins	Client is authoritative (personal notes)
lastWriteWins	Compares updated_at timestamps
fieldLevelMerge	Three-way merge, field by field
custom	Your own ConflictHandler function

📡 Offline-Aware Realtime

• Events received while offline are buffered (configurable TTL + capacity)
• On reconnect, buffered events are replayed to listeners in order
• Auto-resubscribe after reconnect

---

Configuration

UltraConfig(
  // Global cache policy
  defaultCachePolicy: CachePolicy(
    maxAge: Duration(hours: 1),
    maxEntries: 500,
    strategy: CacheStrategy.networkFirstWithFallback,
  ),

  // Global conflict policy
  defaultConflictPolicy: ConflictPolicy.lastWriteWins,

  // Queue limits
  maxQueueSize: 1000,
  maxRetryAttempts: 10,
  baseRetryDelay: Duration(seconds: 5),
  maxRetryDelay: Duration(minutes: 5),

  // Sync behavior
  autoSyncOnReconnect: true,
  reconnectSyncDelay: Duration(seconds: 2),
  periodicSyncInterval: Duration(minutes: 15),

  // Per-table overrides
  tableConfigs: {
    'todos': TableSyncConfig(
      cachePolicy: CachePolicy.aggressive,
      conflictPolicy: ConflictPolicy.fieldLevelMerge,
      primaryKey: 'id',
      updatedAtField: 'updated_at',
      supportsSoftDelete: false,
    ),
    'messages': TableSyncConfig(
      cachePolicy: CachePolicy.conservative,
      conflictPolicy: ConflictPolicy.custom,
      customConflictHandler: (local, server) async {
        // Your custom logic here
        return server; // example: server wins
      },
    ),
  },
)

---

Reactive UI Integration

// Sync status indicator
StreamBuilder<SyncState>(
  stream: ultra.syncStateStream,
  builder: (context, snap) {
    final state = snap.data!;
    return Text(state.status.name); // idle, syncing, completed, error
  },
);

// Pending operations badge
StreamBuilder<int>(
  stream: ultra.pendingOperationsCount,
  builder: (context, snap) => Text('${snap.data} pending'),
);

// Network status
StreamBuilder<NetworkState>(
  stream: ultra.networkStateStream,
  builder: (context, snap) {
    final online = snap.data?.isOnline ?? true;
    return Icon(online ? Icons.wifi : Icons.wifi_off);
  },
);

---

SQL Setup (Recommended)

Add these columns to your Supabase tables for full delta sync:

ALTER TABLE todos
  ADD COLUMN updated_at TIMESTAMPTZ DEFAULT now(),
  ADD COLUMN created_at TIMESTAMPTZ DEFAULT now();

-- Auto-update updated_at on every row change
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER todos_updated_at
  BEFORE UPDATE ON todos
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

---

File Structure

supabase_flutter_ultra/
├── lib/src/
│   ├── core/          # UltraClient, UltraConfig, SupabaseUltra initializer
│   ├── cache/         # CacheManager, policies, SQLite/Memory adapters, LRU/TTL eviction
│   ├── queue/         # OperationQueue, QueueProcessor, RetryStrategy, QueuePersistence
│   ├── sync/          # SyncEngine, DeltaSync, SyncScheduler, SyncManifest
│   ├── conflict/      # ConflictResolver + 5 strategy implementations + VectorClock
│   ├── network/       # ConnectivityMonitor, NetworkState, ReachabilityChecker
│   ├── realtime/      # UltraRealtime, EventBuffer, RealtimeReconciler
│   ├── query/         # UltraQueryBuilder, UltraFilterBuilder, QueryInterceptor
│   ├── models/        # SyncMetadata, LocalRecord, ConflictRecord
│   └── utils/         # IdGenerator, TimestampUtils, SerializationHelper
├── test/
│   ├── unit/          # Cache, queue, conflict unit tests
│   └── integration/   # Offline scenario tests (no live network required)
└── example/           # Full offline-first Todo app

---

License

MIT — see LICENSE