Supabase + Drift: The Ultimate Flutter Data Layer

Supabase gives you a powerful PostgreSQL backend. Drift gives you type-safe SQLite locally. Together, they form a data layer that's fast, reliable, and works offline. Here's how to wire them up properly.

Why Both?

Drift alone: Your app works offline with a local SQLite database. Data is type-safe and reactive. But there's no cloud sync, no cross-device access, and no backup.

Supabase alone: Your app has a powerful cloud backend. But every operation requires network, the UI waits for API responses, and offline users see loading spinners forever.

Together: Users get instant responses from the local database while changes sync to the cloud in the background. Best of both worlds.

The Data Source Pattern

Keep data sources dumb. They just do CRUD operations:

abstract class ProductLocalDataSource {
  Future<List<Product>> fetchAll();
  Future<Product?> getById(String id);
  Future<void> save(Product product);
  Future<void> delete(String id);
  Future<List<Product>> getDirty();     // Records needing sync
  Future<void> markClean(String id);    // Mark as synced
  Stream<List<Product>> watchAll();     // Drift reactive queries
}

That getDirty() and markClean() pair is the foundation of offline sync. Every local record has an isDirty flag. When you modify a record locally, it's marked dirty. When the sync service successfully pushes it to Supabase, it's marked clean.

Drift: Type-Safe Local Storage

Drift generates type-safe Dart code from your table definitions:

class ProgressTable extends Table {
  IntColumn get dayNumber => integer()();
  BoolColumn get isCompleted => boolean().withDefault(const Constant(false))();
  TextColumn get notes => text().nullable()();
  DateTimeColumn get updatedAt => dateTime()();
  BoolColumn get isDirty => boolean().withDefault(const Constant(false))();

  @override
  Set<Column> get primaryKey => {dayNumber};
}

Reactive Queries

Drift's watch() queries are perfect with Riverpod streams. Your UI automatically updates when the database changes:

// In your local data source
Stream<List<ProgressRow>> watchAll() {
  return (select(progressTable)
    ..orderBy([(t) => OrderingTerm.asc(t.dayNumber)])
  ).watch();
}

Connect this to a Riverpod StreamProvider, and your UI rebuilds automatically whenever the local database changes - whether from user actions or background sync.

Supabase: Remote Data Source

The remote data source mirrors the local one, but talks to PostgreSQL:

class RemoteProgressDataSource {
  final SupabaseClient _client;

  Future<List<Map<String, dynamic>>> fetchAll(String userId) async {
    return await _client
        .from('user_progress')
        .select()
        .eq('user_id', userId);
  }

  Future<void> upsert(Map<String, dynamic> data) async {
    await _client.from('user_progress').upsert(data);
  }

  Future<List<Map<String, dynamic>>> getChangesSince(DateTime since) async {
    return await _client
        .from('user_progress')
        .select()
        .gte('updated_at', since.toIso8601String());
  }
}

Mappers: The Bridge Between Worlds

Never let Drift types leak into your domain or Supabase types into your UI. Mappers convert between layers:

class ProgressMapper {
  // Drift row -> Domain entity
  static UserProgress fromLocal(ProgressRow row) {
    return UserProgress(
      dayNumber: row.dayNumber,
      isCompleted: row.isCompleted,
      notes: row.notes,
      updatedAt: row.updatedAt,
    );
  }

  // Domain entity -> Drift companion (for inserts/updates)
  static ProgressTableCompanion toLocal(UserProgress entity) {
    return ProgressTableCompanion(
      dayNumber: Value(entity.dayNumber),
      isCompleted: Value(entity.isCompleted),
      notes: Value(entity.notes),
      updatedAt: Value(entity.updatedAt),
      isDirty: const Value(true), // Always dirty on local save
    );
  }

  // Supabase JSON -> Domain entity
  static UserProgress fromRemote(Map<String, dynamic> json) {
    return UserProgress(
      dayNumber: json['day_number'],
      isCompleted: json['is_completed'],
      notes: json['notes'],
      updatedAt: DateTime.parse(json['updated_at']),
    );
  }
}

The Sync Service

The sync service lives in the Data layer and orchestrates push/pull operations:

class ProductSyncService {
  final ProductLocalDataSource _local;
  final ProductRemoteDataSource _remote;
  final Connectivity _connectivity;

  Future<bool> _isOnline() async {
    final result = await _connectivity.checkConnectivity();
    return result.contains(ConnectivityResult.wifi) ||
           result.contains(ConnectivityResult.mobile);
  }

  Future<SyncResult> syncAll() async {
    if (!await _isOnline()) {
      return SyncResult(success: false, error: 'No connection');
    }

    try {
      // Push local changes to remote
      final dirtyRecords = await _local.getDirty();
      for (final record in dirtyRecords) {
        await _remote.upsert(record.toJson());
        await _local.markClean(record.id);
      }

      // Pull remote changes
      final lastSync = await _local.getLastSyncTime();
      final changes = await _remote.getChangesSince(lastSync);

      for (final change in changes) {
        final local = await _local.getById(change['id']);
        final remoteDate = DateTime.parse(change['updated_at']);

        // Last-write-wins conflict resolution
        if (local == null || remoteDate.isAfter(local.updatedAt)) {
          await _local.save(ProgressMapper.fromRemote(change));
        }
      }

      return SyncResult(success: true);
    } catch (e) {
      return SyncResult(success: false, error: e.toString());
    }
  }
}

Wiring It Up with Riverpod

@Riverpod(keepAlive: true)
AppDatabase appDatabase(Ref ref) {
  throw UnimplementedError('Override in ProviderScope');
}

@riverpod
ProductLocalDataSource localDataSource(Ref ref) {
  return DriftProductDataSource(ref.watch(appDatabaseProvider));
}

@riverpod
ProductRemoteDataSource remoteDataSource(Ref ref) {
  return SupabaseProductDataSource(Supabase.instance.client);
}

@riverpod
ProductRepository productRepository(Ref ref) {
  return ProductRepositoryImpl(
    local: ref.watch(localDataSourceProvider),
    remote: ref.watch(remoteDataSourceProvider),
    sync: ref.watch(syncServiceProvider),
  );
}

The Supabase SQL Schema

CREATE TABLE public.user_progress (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  day_number integer NOT NULL,
  is_completed boolean DEFAULT false,
  notes text,
  updated_at timestamptz DEFAULT now(),
  UNIQUE(user_id, day_number)
);

ALTER TABLE public.user_progress ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can manage own progress"
  ON public.user_progress
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- Enable realtime for live updates
ALTER PUBLICATION supabase_realtime ADD TABLE public.user_progress;

Key Principles

1. Local data source knows nothing about remote. They're separate classes.
2. Remote data source knows nothing about local. No mixing.
3. The sync service orchestrates both. It's the only class that talks to both sources.
4. Mappers handle all conversions. Drift rows never reach the domain. Supabase JSON never reaches the UI.
5. isDirty flag tracks what needs syncing. Simple, reliable.
6. Last-write-wins resolves conflicts. Good enough for 90% of apps.

When to Add Supabase

Start with Drift only. Build your entire app offline-first. When you need cloud features:

1. Install supabase_flutter
2. Run your SQL migrations
3. Uncomment the remote data source
4. Switch to hybrid mode in the repository provider
5. Everything else stays the same

The architecture supports this incremental approach by design. Your business logic and UI code never change when you add or remove the remote layer. That's the power of clean separation.