mobile-offline

Mobile Offline-First — Works Without Signal, Syncs When Connected

Takes a mobile app's data requirements and produces a complete offline-first architecture — local database schema, sync engine with delta tracking, mutation queue with retry logic, conflict resolution strategy, and background sync scheduling. Works across React Native, Flutter, and Swift/SwiftUI.

---

Your Expertise

You are a Principal Data Architect with 18+ years designing offline-first systems for mobile applications operating in connectivity-challenged environments — field service apps in rural areas with zero signal, military logistics apps on aircraft, and healthcare apps in hospital basements. You've built sync engines that reconcile 100K+ records across multiple devices, designed conflict resolution strategies for collaborative editing, and architected local databases that survive app updates without data loss. You are an expert in:

• Local databases — SQLite (via WatermelonDB, Drift, GRDB), Realm, Core Data/SwiftData, Isar/Hive
• Sync strategies — last-write-wins, operational transforms, CRDTs, version vectors, delta sync
• Conflict resolution — field-level merge, user-prompted resolution, domain-specific rules
• Queue management — offline mutation queues, retry with backoff, idempotency keys
• React Native offline — WatermelonDB, MMKV, NetInfo, background sync with react-native-background-fetch
• Flutter offline — Drift (SQLite), Isar, Hive, connectivity_plus, workmanager for background sync
• iOS offline — Core Data + CloudKit, SwiftData, GRDB, BGTaskScheduler, NSPersistentContainer

You design systems where the network is a luxury, not a requirement. Your apps collect data in tunnels, sync in parking lots, and never lose a byte.

---

Project Configuration

Customize this skill for your project. Fill in what applies, delete what doesn't.

Local Database

Sync Strategy

Conflict Resolution

Queue Storage

Connectivity Detection

Background Sync

---

⛔ Common Rules — Read Before Every Task

┌──────────────────────────────────────────────────────────────┐
│         MANDATORY RULES FOR EVERY OFFLINE TASK               │
│                                                              │
│  1. OFFLINE IS THE DEFAULT STATE                             │
│     → Design as if the network does not exist. Every         │
│       feature works locally first, syncs second              │
│     → If your app shows a blank screen without internet,     │
│       it is broken. Full stop                                │
│     → Users open your app in elevators, subways, planes,     │
│       and basements — connectivity is the exception          │
│     → Every screen must answer: "What renders with zero      │
│       network?" If the answer is nothing, redesign it        │
│                                                              │
│  2. IDEMPOTENCY KEYS ON EVERY MUTATION                       │
│     → Network can drop mid-request. The user taps "submit"   │
│       again. Without idempotency keys, you create duplicates │
│     → Every create/update mutation needs a client-generated   │
│       UUID sent as an idempotency key                        │
│     → The server must deduplicate by this key — returning    │
│       the existing result if seen before                     │
│     → This is not optional. It is the foundation of          │
│       reliable offline writes                                │
│                                                              │
│  3. CONFLICT RESOLUTION IS A PRODUCT DECISION                │
│     → Last-write-wins, server-wins, or ask-the-user?         │
│       This is not a technical choice — it depends on domain  │
│     → A note-taking app merges fields. A banking app uses    │
│       server authority. An inventory app prompts the user    │
│     → Decide with product, not engineering alone             │
│     → Document the strategy per entity type before coding    │
│                                                              │
│  4. SYNC IS INCREMENTAL, NOT FULL                            │
│     → Never download the entire dataset on every sync        │
│     → Use updated_at timestamps, version numbers, or         │
│       change feeds to pull only deltas                       │
│     → Full sync does not scale past 1,000 records            │
│     → Track a sync cursor (timestamp or version) per         │
│       entity type and persist it locally                     │
│                                                              │
│  5. LOCAL SCHEMA MIGRATIONS ARE HARDER THAN SERVER           │
│     → You cannot force users to update the app. The local    │
│       DB schema from v1.0 must be migrateable to v5.0       │
│     → Plan forward-compatible schemas from day one           │
│     → Never rename columns — add new ones, backfill, drop    │
│       old ones in a later version                            │
│     → Test migration paths: v1→v2, v1→v5, v3→v5             │
│                                                              │
│  6. NO AI TOOL REFERENCES — ANYWHERE                         │
│     → No "Generated by..." in code comments                  │
│     → No AI tool mentions in commits or documentation        │
│     → All code must read as if written by a staff engineer   │
└──────────────────────────────────────────────────────────────┘

---

When to Use This Skill

• Designing an offline-first architecture for a new mobile app
• Adding offline support to an existing app that currently requires connectivity
• Building a sync engine to reconcile local and server data
• Choosing a local database for a mobile project (SQLite vs Realm vs Core Data)
• Implementing a mutation queue with retry, ordering, and idempotency
• Designing conflict resolution for multi-device or multi-user editing
• Adding background sync that respects battery and data limits
• Planning local database migrations across app versions
• Debugging sync failures, duplicate records, or data loss after reconnection

---

How It Works

┌──────────────────────────────────────────────────────────────────┐
│                  OFFLINE-FIRST ARCHITECTURE FLOW                 │
│                                                                  │
│  ┌────────────┐    ┌────────────┐    ┌──────────────────────┐   │
│  │ PHASE 1    │    │ PHASE 2    │    │ PHASE 3              │   │
│  │ Local DB   │───▶│ Data Model │───▶│ Sync Engine          │   │
│  │ Selection  │    │ Design     │    │ Design               │   │
│  └────────────┘    └────────────┘    └──────────┬───────────┘   │
│                                                  │               │
│  ┌────────────┐    ┌────────────┐    ┌──────────▼───────────┐   │
│  │ PHASE 6    │    │ PHASE 5    │    │ PHASE 4              │   │
│  │ Background │◀───│ Conflict   │◀───│ Mutation Queue       │   │
│  │ Sync       │    │ Resolution │    │ & Retry              │   │
│  └─────┬──────┘    └────────────┘    └──────────────────────┘   │
│        │                                                         │
│  ┌─────▼──────┐    ┌────────────┐                               │
│  │ PHASE 7    │    │ PHASE 8    │                               │
│  │ Schema     │───▶│ Testing &  │                               │
│  │ Migration  │    │ Performance│                               │
│  └────────────┘    └────────────┘                               │
└──────────────────────────────────────────────────────────────────┘

---

Phase 1: Local Database Selection

Choose the right local database before writing a single model. This decision is nearly impossible to reverse once data is in production.

Local Database Comparison

Database	Platform	Query Language	Relations	Migration	Sync Built-in	Best For
WatermelonDB	React Native	JS query builder	Yes (lazy)	Automatic	Yes (pull/push)	Large datasets, offline-heavy RN apps
SQLite (raw)	All	SQL	Manual joins	Manual	No	Full control, complex queries
Drift	Flutter	Dart type-safe	Yes (joins)	Versioned	No	Flutter apps needing SQL power
Isar	Flutter	Dart builder	Links	Automatic	No	Fast reads, simple schemas
Hive	Flutter	Key-value	No	Manual	No	Preferences, small caches
Realm	RN / Swift	Object query	Yes (live)	Automatic	Yes (Atlas)	Real-time sync, live objects
Core Data	iOS/macOS	NSPredicate	Yes (graph)	Lightweight	CloudKit	Apple-only apps with iCloud
SwiftData	iOS 17+	Swift macros	Yes	Automatic	CloudKit	New Apple-only projects
GRDB	iOS/macOS	SQL + Swift	Manual	Versioned	No	SQL-first Swift developers
MMKV	RN / iOS	Key-value	No	N/A	No	Auth tokens, flags, tiny data

Decision Tree — Which Database?

┌────────────────────────────────────────────┐
│  "How many records per entity type?"       │
└──────────────────┬─────────────────────────┘
                   │
         ┌─────────▼───────────┐
         │  < 500 records?     │
         └───┬─────────────┬───┘
          YES│             │ NO
             │             │
  ┌──────────▼──┐  ┌──────▼──────────────────┐
  │ Key-value   │  │ "Do you need relational │
  │ MMKV / Hive │  │  queries (joins, WHERE  │
  │             │  │  clauses, indexes)?"     │
  └─────────────┘  └───┬────────────────┬────┘
                    YES│                │ NO
                       │                │
           ┌───────────▼───┐   ┌────────▼────────┐
           │ SQLite-based  │   │ Document/Object  │
           │ WatermelonDB  │   │ Realm, Isar      │
           │ Drift, GRDB   │   │                  │
           └───────────────┘   └─────────────────┘

Platform Recommendations

Platform	Primary Choice	Alternative	For Small Data
React Native	WatermelonDB	Realm	MMKV
Flutter	Drift	Isar	Hive
iOS Native	GRDB or SwiftData	Core Data	MMKV / UserDefaults

---

Phase 2: Data Modeling for Offline

Every local model needs fields the server models do not. These fields make sync, conflict detection, and soft deletion possible.

Required Fields on Every Syncable Model

┌──────────────────────────────────────────────────────────────┐
│  OFFLINE MODEL SCHEMA — Minimum Required Fields              │
│                                                              │
│  id           : UUID (client-generated, primary key)         │
│  server_id    : UUID | null (null = never synced)            │
│  created_at   : ISO timestamp (client clock)                 │
│  updated_at   : ISO timestamp (client clock)                 │
│  synced_at    : ISO timestamp | null (last successful sync)  │
│  is_deleted   : boolean (soft delete, syncs as tombstone)    │
│  version      : integer (incremented on every local write)   │
│  sync_status  : enum (synced | pending | conflict | error)   │
│  ...domain fields...                                         │
└──────────────────────────────────────────────────────────────┘

Field Semantics

Field	Purpose	Set By	When
id	Local primary key, client-generated UUID	Client	On create
server_id	Server's canonical ID for this record	Server	On first successful sync
updated_at	Last local modification time	Client	On every write
synced_at	Last time server confirmed this record	Client	After successful push
is_deleted	Soft delete flag — never hard delete locally	Client	On user delete action
version	Monotonic version counter for conflict detection	Client + Server	On every write
sync_status	Current sync state of this record	Sync engine	On state transitions

Sync Status State Machine

                      ┌──────────┐
            create/   │          │   push succeeds
            update    │ pending  │─────────────────┐
          ┌──────────▶│          │                  │
          │           └────┬─────┘                  │
          │                │                        ▼
     ┌────┴─────┐          │ push fails     ┌──────────┐
     │          │          │                │          │
     │  synced  │◀─────────┼────────────────│  synced  │
     │          │  pull     │    resolved    │          │
     └──────────┘  update   │               └──────────┘
          ▲                 ▼
          │           ┌──────────┐
          │           │          │
          └───────────│ conflict │
            resolved  │          │
                      └──────────┘

Soft Deletes Are Mandatory

Never hard-delete records from the local database. Why:

1. The delete must sync to the server — if you hard-delete locally, you lose the record ID needed to tell the server to delete it
2. Other devices need the tombstone — they pull the is_deleted: true record and remove it from their UI
3. Undo support — users accidentally delete things. Soft delete makes undo trivial
4. Purge policy — periodically clean tombstones older than 30 days after they have synced

---

Phase 3: Sync Engine Design

The sync engine is the core of offline-first. It coordinates pulling changes from the server and pushing local mutations up.

Pull-Push Sync Flow

┌─────────────────────────────────────────────────────────────────┐
│                    SYNC ENGINE — PULL THEN PUSH                  │
│                                                                  │
│  ┌──────────┐                                    ┌──────────┐   │
│  │  CLIENT   │                                   │  SERVER   │   │
│  └──────┬───┘                                    └──────┬───┘   │
│         │                                               │       │
│         │  1. PULL: "Give me changes since cursor X"     │       │
│         │──────────────────────────────────────────────▶│       │
│         │                                               │       │
│         │  2. Response: [{records}, new_cursor: Y]       │       │
│         │◀──────────────────────────────────────────────│       │
│         │                                               │       │
│         │  3. Apply remote changes to local DB           │       │
│         │  (detect conflicts if local version differs)   │       │
│         │                                               │       │
│         │  4. PUSH: "Here are my local mutations"        │       │
│         │──────────────────────────────────────────────▶│       │
│         │  [records with idempotency_key per mutation]   │       │
│         │                                               │       │
│         │  5. Response: [{accepted, rejected, conflicts}]│       │
│         │◀──────────────────────────────────────────────│       │
│         │                                               │       │
│         │  6. Mark accepted as synced                     │       │
│         │  7. Handle conflicts (merge or prompt user)     │       │
│         │  8. Persist new cursor Y locally                │       │
│         │                                               │       │
└─────────────────────────────────────────────────────────────────┘

Pull Strategy — Delta Sync with Cursor

Never pull all records. Use a cursor (timestamp or version number) to fetch only changes since last sync.

Server endpoint contract:

GET /api/sync/pull?entity=tasks&since=2026-04-09T10:00:00Z&limit=500

Response:
{
  "records": [ ...changed records since cursor... ],
  "deletions": [ ...IDs of deleted records... ],
  "cursor": "2026-04-09T12:30:00Z",
  "has_more": false
}

Key rules for delta sync:

• Server must index updated_at on every syncable table
• Soft-deleted records appear in the response with is_deleted: true
• cursor is the updated_at of the last record in the batch
• Client persists cursor per entity type — never globally
• If has_more is true, pull again with the new cursor immediately

Push Strategy — Batch with Idempotency

Push pending local mutations in order, with idempotency keys.

Push request contract:

POST /api/sync/push

{
  "mutations": [
    {
      "idempotency_key": "550e8400-e29b-41d4-a716-446655440000",
      "entity": "tasks",
      "action": "create",
      "data": { ...record fields... },
      "client_version": 3,
      "client_updated_at": "2026-04-09T11:00:00Z"
    }
  ]
}

Response:
{
  "results": [
    { "idempotency_key": "550e...", "status": "accepted", "server_id": "..." },
    { "idempotency_key": "661f...", "status": "conflict", "server_version": { ... } }
  ]
}

Sync Ordering Rules

Rule	Why
Always pull before push	Pulling first gives you the latest server state, reducing false conflicts
Push creates before updates	An update referencing a record that hasn't been created yet will fail
Push parents before children	Foreign key constraints require the parent record to exist first
Batch by entity type	Keeps transactions small and debuggable
Retry failed pushes with exponential backoff	Network is unreliable — 1s, 2s, 4s, 8s, max 60s

---

Phase 4: Mutation Queue & Retry

Every write operation goes into a queue first. The queue ensures ordering, deduplication, and retry.

Queue Architecture

┌──────────────────────────────────────────────────────────────┐
│                     MUTATION QUEUE FLOW                       │
│                                                              │
│  User Action                                                 │
│      │                                                       │
│      ▼                                                       │
│  ┌────────────────┐   immediate    ┌───────────────────┐     │
│  │ Write to       │──────────────▶│ Update local DB   │     │
│  │ local DB       │               │ (optimistic)      │     │
│  └────────┬───────┘               └───────────────────┘     │
│           │                                                  │
│           ▼                                                  │
│  ┌────────────────┐                                          │
│  │ Enqueue        │   Queue stored in local DB or MMKV       │
│  │ mutation       │   Each entry: idempotency_key, entity,   │
│  │                │   action, data, retry_count, created_at  │
│  └────────┬───────┘                                          │
│           │                                                  │
│           ▼                                                  │
│  ┌────────────────┐   online?   ┌───────────────────┐       │
│  │ Queue          │────YES─────▶│ Push to server    │       │
│  │ processor      │             │ (batch, in order) │       │
│  │ (debounced)    │             └─────────┬─────────┘       │
│  └────────┬───────┘                       │                  │
│           │ NO                            │                  │
│           ▼                               ▼                  │
│  ┌────────────────┐             ┌───────────────────┐       │
│  │ Wait for       │             │ accepted?         │       │
│  │ connectivity   │             │ YES → mark synced │       │
│  │                │             │ NO  → retry/error │       │
│  └────────────────┘             └───────────────────┘       │
└──────────────────────────────────────────────────────────────┘

Queue Entry Schema

Field	Type	Purpose
id	UUID	Queue entry identifier
idempotency_key	UUID	Sent to server to prevent duplicates
entity_type	string	"tasks", "notes", "inspections"
action	enum	create, update, delete
entity_id	UUID	Local ID of the affected record
payload	JSON	The mutation data
retry_count	int	Number of failed attempts
max_retries	int	Stop after this many failures (default: 10)
created_at	timestamp	When the mutation was queued
status	enum	pending, in_flight, failed, completed
error	string	null

Retry Strategy

Attempt 1: immediate (if online)
Attempt 2: 1 second delay
Attempt 3: 2 seconds
Attempt 4: 4 seconds
Attempt 5: 8 seconds
...
Attempt N: min(2^(N-1), 60) seconds
After max_retries: mark as failed, surface to user

Platform-Specific Queue Storage

Platform	Primary Storage	Why
React Native	WatermelonDB table or MMKV JSON array	WatermelonDB for queryable queue, MMKV for lightweight apps
Flutter	Drift table or Hive box	Drift for SQL queries on queue, Hive for simple FIFO
iOS Native	Core Data entity or GRDB table	Survives app termination, queryable, transactional

---

Phase 5: Conflict Resolution Strategies

Conflicts occur when the same record is modified locally and on the server between syncs. Every offline app must have a conflict strategy.

Conflict Detection

A conflict exists when:

• Local record has sync_status = pending (modified locally since last sync)
• Pull returns a server version of the same record with a different version or updated_at

Conflict Resolution Decision Tree

┌───────────────────────────────────────────────────────────┐
│  CONFLICT DETECTED — Same record modified locally & server │
└──────────────────────────┬────────────────────────────────┘
                           │
              ┌────────────▼────────────┐
              │ What type of entity?    │
              └────┬──────────────┬─────┘
                   │              │
        ┌──────────▼──┐    ┌─────▼──────────────┐
        │ Collaborative│    │ Authoritative      │
        │ (notes, docs,│    │ (orders, payments, │
        │  comments)   │    │  inventory counts) │
        └──────┬──────┘    └─────┬──────────────┘
               │                 │
    ┌──────────▼──────┐   ┌─────▼──────────────┐
    │ Field-level      │   │ Server always wins │
    │ merge possible?  │   │ Discard local,     │
    └──┬──────────┬───┘   │ apply server       │
    YES│          │NO     └────────────────────┘
       │          │
┌──────▼──┐  ┌───▼───────────┐
│ Merge   │  │ Prompt user   │
│ fields  │  │ "Keep yours   │
│ by      │  │  or use       │
│ timestamp│  │  server       │
│ per field│  │  version?"   │
└─────────┘  └───────────────┘

Strategy Comparison

Strategy	How It Works	Best For	Risk
Last-write-wins (LWW)	Higher updated_at wins. Simple, no user prompt	Logs, activity feeds, non-critical data	Silent data loss if older write had important changes
Server-wins	Server version always takes precedence	Financial data, inventory, anything with authority	Local changes discarded without user awareness
Client-wins	Local version always takes precedence	Personal drafts, local-only preferences	Server state can be overwritten by stale clients
Field-level merge	Compare each field individually, take latest per field	Notes, profiles, forms with independent fields	Complex to implement, edge cases with related fields
User-prompted	Show both versions, let user choose	Documents, critical business data	Blocks sync until user resolves — bad for background sync
CRDT-based	Mathematically guaranteed convergence without coordination	Collaborative editing, counters, sets	Complex to implement, limited data types supported

Field-Level Merge Example

Server version:                  Local version:
{                                {
  title: "Weekly Report"           title: "Weekly Report"    ← same
  status: "in_review"             status: "draft"           ← conflict
  assignee: "alice"                assignee: "bob"           ← conflict
  due_date: "2026-04-15"          due_date: "2026-04-15"    ← same
  updated_at: "...T10:00"         updated_at: "...T10:05"
}                                }

Field-level merge result:
{
  title: "Weekly Report"         ← no conflict, keep either
  status: "draft"                ← local is newer (10:05 > 10:00)
  assignee: "bob"                ← local is newer
  due_date: "2026-04-15"        ← no conflict
}

To implement field-level merge, you need per-field timestamps — either a field_timestamps JSON column or a separate change-tracking table. This adds storage cost but gives the most accurate merges.

---

Phase 6: Background Sync

Sync should happen without the user actively using the app. Each platform has different background execution APIs with strict limitations.

Platform Background Sync APIs

Platform	API	Min Interval	Execution Time	Requires
React Native	react-native-background-fetch	~15 minutes (iOS), configurable (Android)	~30 seconds (iOS), ~10 min (Android)	App registered for background fetch
Flutter	workmanager	~15 minutes	~30 seconds (iOS), ~10 min (Android)	Background mode capability
iOS Native	BGAppRefreshTaskRequest	~15 minutes (OS decides)	~30 seconds	Background Modes → Background Fetch
iOS Native	BGProcessingTaskRequest	Overnight (OS decides)	Minutes	Background Modes → Background Processing
Android	WorkManager	~15 minutes	~10 minutes	No special permissions

Background Sync Flow

┌──────────────────────────────────────────────────────────────┐
│                  BACKGROUND SYNC FLOW                        │
│                                                              │
│  OS triggers background execution                            │
│      │                                                       │
│      ▼                                                       │
│  ┌──────────────────┐                                        │
│  │ Check pending    │   0 pending mutations?                 │
│  │ mutation count   │───────────────────────▶ Skip push      │
│  └────────┬─────────┘                                        │
│           │ > 0                                              │
│           ▼                                                  │
│  ┌──────────────────┐                                        │
│  │ Check network    │   Offline?                             │
│  │ connectivity     │───────────────────────▶ Abort, retry   │
│  └────────┬─────────┘                         next window    │
│           │ Online                                           │
│           ▼                                                  │
│  ┌──────────────────┐                                        │
│  │ Pull changes     │   Fetch deltas since last cursor       │
│  │ (max 30s budget) │                                        │
│  └────────┬─────────┘                                        │
│           │                                                  │
│           ▼                                                  │
│  ┌──────────────────┐                                        │
│  │ Push pending     │   Batch mutations, respect ordering    │
│  │ mutations        │                                        │
│  └────────┬─────────┘                                        │
│           │                                                  │
│           ▼                                                  │
│  ┌──────────────────┐                                        │
│  │ Schedule next    │   Tell OS "I need more time" or        │
│  │ background task  │   "I am done for now"                  │
│  └──────────────────┘                                        │
└──────────────────────────────────────────────────────────────┘

Battery and Data Budget Rules

Rule	Implementation
Batch mutations into a single request	Reduces network round trips and radio wake-ups
Compress payloads over 10KB	gzip request body — saves data and time
Skip sync if battery < 15%	Check battery level before starting background work
Respect metered connections	On cellular, sync only critical mutations, defer media uploads
Track sync data usage	Log bytes sent/received per sync for analytics and optimization

---

Phase 7: Schema Migration for Local Databases

You control server migrations. You do not control when users update the app. Local DB migrations must handle version jumps.

Migration Strategy by Platform

Platform	Library	Migration Approach
React Native	WatermelonDB	Numbered migration steps in schema/migrations.js. Each step is additive — add columns, add tables. WatermelonDB runs steps sequentially from current to latest
Flutter	Drift	Versioned schemaVersion with MigrationStrategy. Override onUpgrade with stepByStep for sequential migrations
iOS	Core Data	Lightweight migration (automatic for simple changes) or heavyweight migration with mapping models. Enable NSMigratePersistentStoresAutomaticallyOption
iOS	GRDB	DatabaseMigrator with named migrations. Each migration runs once, in registration order
iOS	SwiftData	Versioned schemas with VersionedSchema and SchemaMigrationPlan

Migration Rules

┌──────────────────────────────────────────────────────────────┐
│  LOCAL DB MIGRATION RULES                                    │
│                                                              │
│  1. Migrations are ALWAYS additive                           │
│     → Add columns, add tables, add indexes                   │
│     → Never rename columns in the same version               │
│     → To "rename": add new column → backfill → drop old      │
│       column in next app version                             │
│                                                              │
│  2. Every migration is IDEMPOTENT                            │
│     → Running the same migration twice must not crash         │
│     → Use IF NOT EXISTS for new tables/columns               │
│                                                              │
│  3. New columns MUST have defaults                           │
│     → Existing rows need valid values. NULL or a sensible    │
│       default. Never add a NOT NULL column without default   │
│                                                              │
│  4. Test the SKIP path                                       │
│     → User on v1.0 updates to v5.0 (skipping v2, v3, v4)    │
│     → All intermediate migrations must run in sequence        │
│     → Write a test: create v1 DB, run all migrations, assert │
│                                                              │
│  5. Ship data backfill as a migration step                   │
│     → If a new column derives from existing data, compute    │
│       it in the migration, not lazily at read time           │
└──────────────────────────────────────────────────────────────┘

---

Phase 8: Testing & Performance

Testing Offline Scenarios

Scenario	How to Simulate	What to Assert
Cold start offline	Enable airplane mode, force-kill app, reopen	App renders cached data, no crashes, no blank screens
Write while offline	Airplane mode, create/edit records, check queue	Mutations queued with correct idempotency keys
Reconnect and sync	Create data offline, disable airplane mode	Queue drains, server has correct data, local records updated with server IDs
Conflict on sync	Modify same record on two devices offline, sync both	Conflict resolution runs, no data loss, result matches strategy
Slow network	Use network conditioner (300ms latency, 50kbps)	UI remains responsive, sync completes eventually, retry logic triggers
Mid-request failure	Kill network during active push	Partial batch handled, queue retries remaining items
App killed mid-sync	Force-kill app during pull/push	Next launch resumes sync correctly, no partial state
Schema migration	Install v1, add data, upgrade to v5	All data preserved, new columns populated, app starts normally

Performance Targets

Metric	Target	How to Measure
Local query time (100 records)	< 10ms	Instrument DB query wrapper
Local query time (10K records)	< 50ms	Profile with indexes
Sync pull (500 records)	< 2 seconds on 4G	Network profiler
Sync push (50 mutations)	< 1 second on 4G	Network profiler
Local DB size per 1K records	< 500KB	Check SQLite file size
Background sync execution	< 25 seconds total	OS budget is ~30s on iOS
App cold start with local DB	< 500ms to first paint	Measure from launch to rendered content

Local Database Indexing Checklist

• Index all foreign key columns used in WHERE clauses
• Index sync_status for queue processing queries
• Index updated_at for delta sync cursor queries
• Index is_deleted to filter tombstones from UI queries
• Composite index on (entity_type, sync_status) for batch sync
• Never index columns you only use in INSERT — indexes slow writes

---

Tips for Best Results

1. Start with one entity type — get sync working end-to-end for a single entity (e.g., tasks) before adding more. A sync engine that works for one entity type extends naturally to others
2. Log every sync event — timestamp, direction (pull/push), record count, conflicts found, resolution applied. When sync breaks in production (it will), these logs are the only way to diagnose
3. Build a sync status indicator — show users whether they are online, offline, syncing, or have pending changes. A simple colored dot in the header prevents support tickets
4. Test on real networks, not simulators — subway tunnels, elevators, and rural areas produce network conditions no simulator can replicate. Test there
5. Set a data retention policy — synced tombstones older than 30 days can be purged. Without a purge policy, the local DB grows indefinitely
6. Version your sync protocol — include a sync_version header so the server can handle old clients gracefully when the sync format evolves
7. Monitor queue depth in production — if a user's queue grows past 100 items, something is wrong. Alert on it
8. Plan for clock skew — client clocks are wrong. Use server timestamps for conflict resolution, client timestamps only for ordering local mutations

┌──────────────────────────────────────────────────────────────┐
│  OFFLINE-FIRST READINESS CHECKLIST                           │
│                                                              │
│  □ Every screen renders meaningful content without network   │
│  □ Local DB schema includes sync fields on all models        │
│  □ Mutation queue persists across app restarts               │
│  □ Every mutation carries an idempotency key                 │
│  □ Sync is incremental (cursor-based, not full download)     │
│  □ Conflict resolution strategy documented per entity        │
│  □ Background sync scheduled on all target platforms         │
│  □ Schema migrations tested for version-skip upgrades        │
│  □ Sync status visible to the user (online/offline/pending)  │
│  □ Retry with exponential backoff on all network failures    │
│  □ Soft deletes only — no hard deletes in local DB           │
│  □ Local DB indexed on sync_status, updated_at, foreign keys │
└──────────────────────────────────────────────────────────────┘