Offline-First - Mobile App - Database Sync - Software Development at Program Tom LTD

An offline-first database with automatic sync at startup is a common pattern for mobile and desktop apps. Below is a clear, battle-tested approach that works reliably across platforms.

✅ Best Architecture for Offline-First + Sync on Startup

1. Use a Local Embedded Database

Choose a DB that works well offline:

SQLite (universal, lightweight, battle-tested)
Realm (reactive, built-in sync options in some editions)
PouchDB (JavaScript ecosystem; syncs with CouchDB)
WatermelonDB (React Native optimized; sync adapters)
Core Data (iOS only)

SQLite + your own sync logic is the most flexible, scalable, and platform-agnostic.

✅ 2. Use a Delta-Based Sync Model

Avoid full-table syncs. Instead sync only what changed.

Two main options:

A. Timestamp-Based Sync

Each record has:

updated_at timestamp
deleted_at or a boolean is_deleted

On startup:

Fetch server changes where updated_at > last_sync_time
Apply updates to local DB
Upload local unsent changes
Save new last_sync_time

Pros: simple
Cons: clock drift can cause issues

B. Incremental Revision / Versioning

Each row has:

revision number (monotonic)
local pending_changes

On startup:

Download all changes where revision > last_revision_synced
Merge changes
Push up local changes
Server assigns new revisions to local writes

Pros: reliable, avoids time issues
Cons: requires more server logic

✅ 3. Conflict Resolution Strategy

Conflicts happen if offline changes overlap with server changes.

Choose one:

A. Last Write Wins (LWW)

Simplest; use timestamps or revision numbers.

B. Field-Level Merge

Only merge fields changed by the user.

C. Application-Defined Rules

e.g. “Complete > Incomplete”, “Higher priority always overwrites”.

D. Manual User-Driven Conflict UI

Only for apps where data accuracy is critical.

✅ 4. Sync Flow at App Startup

Here’s the recommended flow:

BOOT → Check local pending changes → Upload them
     → Download remote changes → Merge
     → Mark sync completed → Open app normally

More detailed:

Open local DB
Check pending local mutations (insert/update/delete)
Upload them to the server
Server processes them → returns final states (updated revisions)
Download all server deltas
Apply merges locally
Update sync metadata
App is now up to date

✅ 5. Use a Background Sync Afterwards

Startup sync is mandatory, but you also want:

periodic background syncs
sync on app resume
sync when network becomes available

This keeps everything fresh without making startup slow.

✅ 6. Don’t Block the UI

Best UX approach:

Show the UI immediately with cached offline data
Run sync in the background
Show a small “Syncing…” indicator
Only ask the user to wait if sync is essential (rare)

🔧 Example Tech Stack Patterns

Mobile

React Native → WatermelonDB + custom sync endpoint
Flutter → Drift (SQLite) + your own sync layer
iOS → Core Data + CloudKit or custom backend
Android → Room + WorkManager periodic syncing

Web/Desktop

PouchDB → CouchDB (built-in incremental sync)
IndexedDB → custom API sync

⭐ Recommended “Best Overall” Approach

If you want a general-purpose, scalable, cross-platform solution:

✔ Local database: SQLite

✔ Sync method: Revision-based delta sync

✅Conflict handling: LWW unless domain requires more

✔ Startup behavior: Background sync + immediate UI

This pattern is used by Notion, Obsidian, Spotify, Slack, and many offline-capable apps.

What about Flutter

For Flutter, the best offline-first + startup sync architecture is very well established. Here’s the most reliable, scalable approach developers use in production.

🚀 Best Offline-First Architecture in Flutter

🔧 1. Use a Local Database Layer

Recommended options for Flutter:

⭐ Best overall: `drift` (formerly moor)

Strong type safety
Reactive streams
Good tooling & SQL migrations
Works on mobile, desktop, and web

💡 Other options:

Hive → Fast, great for key-value or documents, but not relational
Isar → Extremely fast, also good for document storage
Realm → Built-in sync but requires MongoDB Realm backend
SQLite → Direct via sqflite (but Drift makes it far better)

🔄 2. Use a Sync Model Designed for Offline Use

Flutter apps typically use either:

A. Revision-Based Sync (Best, Most Reliable)

Each record has:

revision (int or UUID)
updatedAt
deleted flag
optionally a syncStatus (local-only, modified, synced)

Startup Sync Flow:

Read lastSyncedRevision from local database
Upload all unsynced local changes
Fetch all server changes where revision > lastSyncedRevision
Merge them into the local database
Update lastSyncedRevision

B. Timestamp-Based Sync (Simpler but less reliable)

Each record has only updatedAt.

On startup: fetch all rows with updatedAt > lastSyncTime

Downside: clock drift can break sync.

🤝 3. Conflict Resolution

Choose depending on your domain:

🔹 Simple: Last Write Wins (LWW)

Use server timestamp or revision.

🔹 Safer: Field-level merge

Only merge changed fields.

🔹 Advanced: User-resolved conflicts

UI for conflicts (rare, usually for editing apps).

🧠 4. Sync Should NOT Block UI

In Flutter:

💼 5. Recommended Flutter Setup

Here’s a production-grade stack:

Database layer

drift
sqlite3_flutter_libs

Sync layer

Custom sync service
Uses REST or GraphQL with endpoints like:
- /sync/changes?afterRevision=XYZ
- /sync/upload

State management

Riverpod (best)
Bloc (also fine)

Background sync

On startup
On app resume
When network becomes available:
- connectivity_plus
Periodic sync using:
- workmanager (Android)
- background_fetch (iOS & Android)

📦 6. Example Flutter Drift Table (Revision-Based Sync)

class Notes extends Table {
  TextColumn get id => text()();
  TextColumn get title => text()();
  TextColumn get content => text().nullable()();

  IntColumn get revision => integer()();
  BoolColumn get deleted => boolean().withDefault(const Constant(false))();
  DateTimeColumn get updatedAt => dateTime()();

  BoolColumn get localModified => boolean().withDefault(const Constant(false))();

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

🔄 7. Startup Sync Pseudocode (Flutter)

Future<void> syncOnStartup() async {
  final localChanges = await db.getLocallyModifiedRows();

  // 1. Push local changes to server
  final serverAcks = await api.uploadChanges(localChanges);
  await db.applyServerAcks(serverAcks);

  // 2. Pull changes from server
  final updates = await api.getChangesAfter(lastRevisionSynced);
  await db.applyRemoteChanges(updates);

  // 3. Update metadata
  lastRevisionSynced = updates.maxRevision;
}

⭐ Summary: Best Approach for Flutter

Component	Best Choice
Local DB	Drift
Sync Model	Revision-based delta sync
Conflict Resolution	LWW or field-level merge
Startup Behavior	Background sync + instant UI
Background Sync	`workmanager`, `background_fetch`

This is what most large-scale Flutter apps (finance, field inventory, CRM, offline mapping, etc.) use.

🏆 Best Choice for Your Requirements: `Drift` (SQLite)

Why?

✔ 1. Full SQL Query Power

Joins
Nested SELECTs
Aggregations
Sorting
Custom SQL
Partial indexes
Views
Triggers
Constraints

If your backend is Postgres, this matters — Drift lets you mirror logic.

✔ 2. Full-Text Search (FTS5)

Flutter apps with search use this extensively.

Match queries
Weighted fields
Prefix search
Ranking by score
Fast indexing

Example:

SELECT *
FROM notes_fts
WHERE notes_fts MATCH 'flutter OR offline'
ORDER BY rank;

FTS-backed tables integrate directly with Drift.

✔ 3. Geospatial Distance Queries via SQLite RTree

SQLite has a built-in R-Tree index:

Fast bounding-box queries
Great for finding items within a radius
Very fast compared to scanning

Example:

SELECT *
FROM places
WHERE id IN (
  SELECT id FROM places_rtree
  WHERE minLat >= :latMin AND maxLat <= :latMax
    AND minLng >= :lngMin AND maxLng <= :lngMax
)

You compute distance in Dart:

distance = Geolocator.distanceBetween(
  userLat, userLng,
  place.lat, place.lng,
);

Then filter/sort locally.

This gives excellent performance.

✔ 4. Supports Related/Sub-Object Queries

You can represent foreign keys and JOIN queries directly:

final results = await db.customSelect(
  'SELECT events.*, locations.* '
  'FROM events '
  'JOIN locations ON events.location_id = locations.id '
  'WHERE locations.city LIKE ?',
  variables: [Variable('%Berlin%')]
).get();

Isar/Hive cannot join across collections without manual client-side filtering (slow).

✔ 5. Complex Filters & Weights

Want a score combining multiple signals?

Example:

SELECT *,
  (title_weight * titleMatchScore) +
  (description_weight * descriptionMatchScore) +
  (flag_weight * flagValue) AS overallScore
FROM search_results
ORDER BY overallScore DESC;

SQLite is the only Flutter DB that can express these algorithms cleanly.

✔ 6. Durable + Battle-Tested

SQLite is used by:

WhatsApp
Signal
Slack
Firefox
Chrome
iOS + Android internally

It is safe, mature, and well-supported.

📍 Why NOT Isar / Hive / ObjectBox for This?

❌ Isar

No JOINs
No FTS
No geospatial indexing
No SQL
No custom queries
Filtering becomes manual (Dart-side), slow on big datasets

– Hive

Great for simple storage, extremely poor for queries
No indexing except keys
No JOINs
No FTS
No R-tree

❌ ObjectBox

Faster than Hive
But limited query power
No FTS
No SQL
No joins
No R-tree

🤔 But SQLite ≠ Postgres, will I lose features?

Postgres has:

PostGIS
GIN/GIST indexes
Ranking & search vectorization
Advanced window functions

SQLite + Drift cannot do everything Postgres does, but for mobile offline apps, you can achieve:

✔ Full-text search
✔ Weighted ranking
✔ Near-geospatial queries via rtree + client distance
✔ Complex filters
✔ Efficient sorting
✔ Advanced SQL

Most apps do not need full PostGIS offline — bounding boxes + haversine distance are typically enough.

🧭 Recommendation

👉 Use Drift + SQLite with:

FTS5 for text search
RTree for spatial search
Drift DAOs for relational queries
A sync layer on top (revision-based or timestamp-based)

This combination gives you:

🟩 The closest thing to “offline Postgres” available in Flutter.

🏆 Is SQLite (server-generated) good for offline-first?

Yes — especially when:

You have complex queries (text search, joins, spatial filtering)
You want the same query semantics offline and online
You want fast startup with a prefilled DB
You want Postgres → SQLite consistency

With the right setup, SQLite can feel like a mini local Postgres.

🧩 Two Main Patterns

1️⃣ Full SQLite snapshot from backend

You ship a ready-made SQLite file:

Created by your backend
Possibly compressed (zip)
Downloaded on first app launch or major version upgrade
Placed into the app’s data directory

⭐ Best for:

Large initial datasets (10k+ rows)
Complex relationships
Heavy search
Geospatial queries (via Rtree)
Users mostly reading data

✔ Advantages:

Fast setup — user downloads 1 file; no need to sync thousands of rows individually
Consistent — DB schema & FTS/Rtree indexes match backend logic
Zero migration pain — schema prebuilt by backend
Full query ability offline — Drift gives you SQL access

❗ Limitations:

You still need a sync mechanism for updates (unless DB is read-only)
Snapshot generation must be automated on server

2️⃣ SQLite snapshot + incremental delta sync

The best hybrid solution:

Backend creates SQLite file periodically (daily, weekly, or version-based)
Client downloads the snapshot
After that → only apply incremental “diffs” / changes from backend

This gives you:

Fast initial load
Full SQL offline
Efficient updates

🏗️ How to Produce a Server-Generated SQLite for Flutter

Your backend (Postgres) process:

Export data from Postgres
Create a SQLite file
Create indexes (including FTS5 + RTree)
Write schema migrations automatically
Compress & store (e.g. S3, Cloudflare R2)
App fetches the new file

This is simple in Python, Go, or even Node.

🔍 Querying Capabilities: Will SQLite lose capability compared to PostgreSQL?

SQLite supports:

✔ Full-text search (FTS5)

Field weighting
Ranking
Prefix search
Stemming (optional)

+ Spatial search (R-tree)

Fast bounding-box region queries
Great for “items near a GPS point”
Combine with client-side haversine

✔ Relational queries

Joins
Nested SELECT
Views
Cross-table filtering

✔ Conditional queries

Flags
Times
Related objects

Top Performance

SQLite is fast, especially on mobile.

❗ What SQLite does not have compared to Postgres?

Most apps don’t need these offline:

Postgres feature	SQLite equivalent
PostGIS	RTree + haversine
GIN/GIST indexes	FTS5 + RTree
Stored procedures	Triggers (limited)
Complex window functions	Some supported, but fewer

SQLite is not Postgres, but for offline-first purposes it covers everything you listed:

distance queries
weighted text search
filtering sub-items
timestamps
flags
complex filters

And it does it fast.

🧭 So is server-generated SQLite a good idea in YOUR case?

Given your requirements:

✔ Full-text search with weighting
✔ Distance filtering
✔ Flags + related objects
✔ Raw SQL-style flexibility
✔ Concern about losing Postgres query power

YES — server-generated SQLite + Drift is an ideal choice.

It gives you:

The same relational model as Postgres
All the query power you need
Offline speeds
Easy synchronization
Fewer client migrations
No query limitations like you’d have in Hive/Isar

🧰 Recommended Architecture

Backend (Postgres) → Produce SQLite snapshot
      ↓
App downloads sqlite snapshot (first launch)
      ↓
Drift opens SQLite normally
      ↓
Incremental sync (revision-based) for updates
      ↓
User performs full complex queries offline

✅ Best Architecture for Offline-First + Sync on Startup

1. Use a Local Embedded Database

✅ 2. Use a Delta-Based Sync Model

A. Timestamp-Based Sync

B. Incremental Revision / Versioning

✅ 3. Conflict Resolution Strategy

A. Last Write Wins (LWW)

B. Field-Level Merge

C. Application-Defined Rules

D. Manual User-Driven Conflict UI

✅ 4. Sync Flow at App Startup

✅ 5. Use a Background Sync Afterwards

✅ 6. Don’t Block the UI

🔧 Example Tech Stack Patterns

Mobile

Web/Desktop

⭐ Recommended “Best Overall” Approach

✔ Local database: SQLite

✔ Sync method: Revision-based delta sync

✅Conflict handling: LWW unless domain requires more

✔ Startup behavior: Background sync + immediate UI

What about Flutter

🚀 Best Offline-First Architecture in Flutter

🔧 1. Use a Local Database Layer

⭐ Best overall: drift (formerly moor)

💡 Other options:

🔄 2. Use a Sync Model Designed for Offline Use

A. Revision-Based Sync (Best, Most Reliable)

Each record has:

Startup Sync Flow:

B. Timestamp-Based Sync (Simpler but less reliable)

🤝 3. Conflict Resolution

🔹 Simple: Last Write Wins (LWW)

🔹 Safer: Field-level merge

🔹 Advanced: User-resolved conflicts

🧠 4. Sync Should NOT Block UI

Recommended:

💼 5. Recommended Flutter Setup

Database layer

Sync layer

State management

Background sync

📦 6. Example Flutter Drift Table (Revision-Based Sync)

🔄 7. Startup Sync Pseudocode (Flutter)

⭐ Summary: Best Approach for Flutter

🏆 Best Choice for Your Requirements: Drift (SQLite)

Why?

✔ 1. Full SQL Query Power

✔ 2. Full-Text Search (FTS5)

✔ 3. Geospatial Distance Queries via SQLite RTree

✔ 4. Supports Related/Sub-Object Queries

✔ 5. Complex Filters & Weights

✔ 6. Durable + Battle-Tested

📍 Why NOT Isar / Hive / ObjectBox for This?

❌ Isar

– Hive

❌ ObjectBox

🤔 But SQLite ≠ Postgres, will I lose features?

🧭 Recommendation

👉 Use Drift + SQLite with:

🟩 The closest thing to “offline Postgres” available in Flutter.

🏆 Is SQLite (server-generated) good for offline-first?

🧩 Two Main Patterns

1️⃣ Full SQLite snapshot from backend

⭐ Best for:

✔ Advantages:

❗ Limitations:

2️⃣ SQLite snapshot + incremental delta sync

🏗️ How to Produce a Server-Generated SQLite for Flutter

Your backend (Postgres) process:

🔍 Querying Capabilities: Will SQLite lose capability compared to PostgreSQL?

✔ Full-text search (FTS5)

+ Spatial search (R-tree)

✔ Relational queries

✔ Conditional queries

Top Performance

❗ What SQLite does not have compared to Postgres?

🧭 So is server-generated SQLite a good idea in YOUR case?

YES — server-generated SQLite + Drift is an ideal choice.

🧰 Recommended Architecture

⭐ Best overall: `drift` (formerly moor)

🏆 Best Choice for Your Requirements: `Drift` (SQLite)