Data Modeling for Sync

This is Part 2 of the Offline-First Flutter series. In Part 1 we established the law: the local database is the single source of truth. Now we design that database — and here's the twist that trips up everyone building their first sync engine:

An offline-first table is not just your domain data. Every row also carries sync metadata — extra columns whose only job is to let the sync engine reason about it. Skip this metadata and you cannot build correct sync later. Design it well and the rest of the series falls into place.

We'll build the FieldNotes schema in Drift (reactive, type-safe SQLite), and justify every single column. This is the foundation the whole app stands on.

Stack: Drift on SQLite, Flutter 3.38 / Dart 3.12. Drift uses build_runner code generation — exactly the metaprogramming pattern from the Dart Internals series (annotation + part + dart run build_runner).

The shipping-label analogy

Analogy — parcels in a warehouse. Your domain data (a note's title and body) is the contents of a parcel. But a warehouse that ships parcels around the world needs a label on each one: a tracking number, a "last updated" stamp, a "not yet shipped" sticker, and a "return to sender / destroyed" marker.

Sync metadata is that shipping label. The note's content is for the user; the label is for the sync engine. A row without a label can't be tracked, shipped, or reconciled.

Setting up Drift

First, the dependencies (pubspec.yaml):

dependencies:
  drift: ^2.x              # the reactive ORM
  sqlite3_flutter_libs: ^0.5.x  # bundles the SQLite engine
  path_provider: ^2.x
  path: ^1.x

dev_dependencies:
  drift_dev: ^2.x          # the code generator
  build_runner: ^2.x

Drift is reactive (queries return streams that re-emit on change — crucial for Part 4), type-safe (no stringly-typed SQL), and migration-aware (Part 3). It's the most production-ready local DB for sync work in 2026, which is why it's our pick. (Isar, Hive, and sqflite are alternatives; Drift's reactive streams + SQL power fit sync best.)

The Notes table, column by column

Here's the full table. Read it once, then we'll justify the non-obvious half.

import 'package:drift/drift.dart';

class Notes extends Table {
  // ── Domain data (what the user cares about) ──────────────
  TextColumn get id => text()();                  // client-generated UUID (PK)
  TextColumn get title => text().withDefault(const Constant(''))();
  TextColumn get body => text().withDefault(const Constant(''))();

  // ── Sync metadata (what the sync engine cares about) ─────
  DateTimeColumn get updatedAt => dateTime()();   // last local change time
  IntColumn get version => integer().withDefault(const Constant(0))(); // conflict detection
  BoolColumn get isDirty => boolean().withDefault(const Constant(true))();   // has unsynced changes?
  BoolColumn get isDeleted => boolean().withDefault(const Constant(false))(); // tombstone

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

Three domain columns. Four sync columns. That ratio is normal — and necessary.

id — a client-generated UUID, not an auto-int

import 'package:uuid/uuid.dart';
final id = const Uuid().v4(); // e.g. "a1b2c3d4-..." — minted on the CLIENT

Why: offline-first records are created before the server sees them (Part 1). There's no round-trip to allocate a server id, so the client mints the id itself. A UUID is globally unique with no coordination, so two offline devices never collide. This is non-negotiable — auto-increment integer PKs are fundamentally incompatible with offline creation.

updatedAt — the "when" for last-write-wins

A timestamp set on every local modification. It's the basis of the simplest conflict resolution (last-write-wins, Part 8): when the same note changed in two places, the later updatedAt usually wins.

Gotcha (we'll fix it in Part 8): wall-clock timestamps across devices can't be fully trusted — clocks drift. We start with updatedAt for simplicity and upgrade to a version counter / logical clock for robustness.

version — the "how many times" for conflict detection

An integer that increments on every change, often mirrored on the server. It lets the server reject a stale update ("you're editing version 3, but the server already has version 5 — conflict"). It's more reliable than timestamps because it doesn't depend on clocks. We'll lean on it heavily in Part 8.

isDirty — "has unsynced local changes"

The flag that drives the whole push half of sync:

• Set isDirty = true whenever the user creates/edits/deletes locally.
• The sync engine pushes every dirty row to the server (Part 6).
• On success, set isDirty = false ("clean" = matches the server).

isDirty is how the app remembers what it still owes the server. After a week offline, the dirty rows are exactly the backlog to send. It's the simplest form of an outbox (we'll formalize a richer outbox table in Part 6).

isDeleted — the tombstone

A deleted note becomes isDeleted = true (and isDirty = true) — the row stays:

// Deleting is an UPDATE, not a DELETE:
(update(notes)..where((n) => n.id.equals(id)))
    .write(NotesCompanion(isDeleted: const Value(true), isDirty: const Value(true)));

Why a tombstone: a deletion is information that must sync (Part 1). If you physically delete the row, the sync engine forgets the deletion ever happened, can't tell the server, and the note resurrects on the next pull. The tombstone keeps "this was deleted" as syncable data. The row is physically purged only after the delete has been confirmed by the server (Part 7). The UI simply filters out isDeleted rows.

The database class

import 'package:drift/drift.dart';
import 'connection/connection.dart'; // platform-specific opener

part 'app_database.g.dart'; // ← generated by build_runner (metaprogramming!)

@DriftDatabase(tables: [Notes])
class AppDatabase extends _$AppDatabase {
  AppDatabase(super.e);

  @override
  int get schemaVersion => 1; // bump this when the schema changes ([Part 3])
}

Then generate the code:

dart run build_runner build --delete-conflicting-outputs

This @DriftDatabase + part 'app_database.g.dart' + build_runner is precisely the codegen pattern from Dart metaprogramming: an annotation describes intent, and the generator writes the type-safe _$AppDatabase, the Note data class, and NotesCompanion for you.

Reading and writing — a first taste

Drift gives you type-safe queries. The two that matter most for offline-first:

// REACTIVE read: a Stream that re-emits whenever the notes table changes.
// This is the heartbeat of the UI ([Part 4]).
Stream<List<Note>> watchActiveNotes() =>
    (select(notes)..where((n) => n.isDeleted.equals(false))
                  ..orderBy([(n) => OrderingTerm.desc(n.updatedAt)]))
        .watch();

// WRITE: insert-or-update via a Companion (only set fields you mean to change).
Future<void> upsertNote(Note note) =>
    into(notes).insertOnConflictUpdate(note);

The reactive .watch() is why Drift shines for offline-first. When the sync engine writes new data into the local DB in the background, every .watch() stream automatically re-emits, and the UI updates — without the UI knowing sync happened. The local DB being the SSoT plus reactive queries equals "background sync magically updates the screen." We wire this up in Part 4.

A NotesCompanion cheat-sheet

Drift's generated Companion lets you specify only the columns you want to set, leaving others untouched — essential for partial updates that don't clobber sync metadata:

// Create a note (dirty by default, version 0):
NotesCompanion.insert(
  id: const Uuid().v4(),
  title: 'Survey site B',
  body: 'No signal here.',
  updatedAt: DateTime.now().toUtc(),
);

// Edit ONLY the body, bump version + dirty, leave id/title/createdAt alone:
NotesCompanion(
  body: const Value('Updated in the field'),
  updatedAt: Value(DateTime.now().toUtc()),
  version: Value(current.version + 1),
  isDirty: const Value(true),
);

Always store timestamps in UTC (.toUtc()). Devices and servers live in different time zones; UTC is the only sane common reference for updatedAt comparisons in Part 8.

Practice Challenges

Challenge 1 — Add the metadata. A teammate models notes with just id, title, body. List the sync columns you'd add and one sentence on each.

Challenge 2 — Delete correctly. Write the Drift statement to "delete" note x the offline-first way.

(update(notes)..where((n) => n.id.equals('x'))).write(
  const NotesCompanion(isDeleted: Value(true), isDirty: Value(true)),
);

Challenge 3 — UUID vs auto-int. Explain in one sentence why a server auto-increment PK breaks offline creation.

Challenge 4 — Filter the UI. Why does watchActiveNotes() filter isDeleted.equals(false), and what happens to tombstoned rows in the UI?

Challenge 5 — Reactive payoff. The sync engine writes 5 new notes pulled from the server straight into the notes table. What does the UI do, and why didn't we write any UI-update code?

Questions to test yourself

Q1 (basic). What are the two categories of columns in an offline-first table?

Q2 (basic). Why store updatedAt in UTC?

Q3 (intermediate). What does isDirty represent and how does it drive sync?

Q4 (intermediate). Why is delete modeled as a tombstone (isDeleted) instead of removing the row?

Q5 (intermediate). Why does Drift's reactive .watch() matter specifically for offline-first?

Q6 (advanced). Why include both updatedAt and version when either could detect a change?

Wrapping up

• An offline-first row = domain data + sync metadata; the metadata is what makes syncing possible.
• id is a client-generated UUID (offline creation has no server to allocate ids).
• updatedAt (UTC) drives last-write-wins; version gives clock-independent conflict detection.
• isDirty marks unsynced changes and drives the push; isDeleted is a tombstone so deletions sync instead of resurrecting.
• Drift's type-safe schema + reactive .watch() is the foundation: background sync writes propagate to the UI automatically.
• The schema is generated via build_runner — the same codegen pattern as the rest of the ecosystem.

In Part 3 we confront the reality of shipping: your schema will change. We cover migrations and versioning — evolving the local database safely on devices that already hold users' un-synced data.