SQLite for Mobile Development: Complete Database Guide

Your app needs to work offline. Not "gracefully degrade" — actually work. That means local storage, and that means SQLite.

The constraints on mobile are different from a server. Limited storage. Migrations running on millions of user devices you cannot SSH into. Concurrent access from the main thread, background sync jobs, and push notification handlers all hitting the same database file. Schema design for a phone is its own discipline, and getting it wrong means jank, data loss, or both.

The Short Version on SQLite

Self-contained. Single file on disk. No server process. ~700KB. Ships on every Android and iOS device already. ACID transactions with rollback. Public domain.

You probably know all that. The interesting parts are what happens after you pick it.

Setting Up SQLite on Android with Room

Raw SQLiteOpenHelper was string-concatenated SQL and runtime crashes. Room fixed that. Compile-time SQL verification, automatic row-to-object mapping, built-in Flow support. Dependencies for your module-level build.gradle.kts:

// build.gradle.kts (Module level)
dependencies {
    val roomVersion = "2.7.0"

    implementation("androidx.room:room-runtime:$roomVersion")
    implementation("androidx.room:room-ktx:$roomVersion")
    ksp("androidx.room:room-compiler:$roomVersion")

    // Optional - testing
    testImplementation("androidx.room:room-testing:$roomVersion")
}

Entity definition:

import androidx.room.*

// Entity represents a table in the database (schema version 2)
@Entity(tableName = "notes", indices = [Index(value = ["tags"])])
data class Note(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,

    @ColumnInfo(name = "title")
    val title: String,

    @ColumnInfo(name = "content")
    val content: String,

    @ColumnInfo(name = "created_at")
    val createdAt: Long = System.currentTimeMillis(),

    @ColumnInfo(name = "updated_at")
    val updatedAt: Long = System.currentTimeMillis(),

    @ColumnInfo(name = "is_synced")
    val isSynced: Boolean = false,

    // Added in version 2 by MIGRATION_1_2 below
    @ColumnInfo(name = "tags", defaultValue = "")
    val tags: String = ""
)

The DAO:

@Dao
interface NoteDao {
    @Query("SELECT * FROM notes ORDER BY updated_at DESC")
    fun getAllNotes(): Flow<List<Note>>

    @Query("SELECT * FROM notes WHERE id = :noteId")
    suspend fun getNoteById(noteId: Long): Note?

    @Query("SELECT * FROM notes WHERE title LIKE '%' || :query || '%' OR content LIKE '%' || :query || '%'")
    fun searchNotes(query: String): Flow<List<Note>>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertNote(note: Note): Long

    @Update
    suspend fun updateNote(note: Note)

    // A list @Update runs every row in one transaction
    @Update
    suspend fun updateNotes(notes: List<Note>)

    @Delete
    suspend fun deleteNote(note: Note)

    @Query("SELECT * FROM notes WHERE is_synced = 0")
    suspend fun getUnsyncedNotes(): List<Note>
}

Database class:

// version 2: the tags column was added after the first release
@Database(entities = [Note::class], version = 2, exportSchema = true)
abstract class AppDatabase : RoomDatabase() {
    abstract fun noteDao(): NoteDao

    companion object {
        @Volatile
        private var INSTANCE: AppDatabase? = null

        fun getDatabase(context: Context): AppDatabase {
            return INSTANCE ?: synchronized(this) {
                val instance = Room.databaseBuilder(
                    context.applicationContext,
                    AppDatabase::class.java,
                    "notes_database"
                )
                    // Upgrades devices that still have the version 1 schema
                    .addMigrations(MIGRATION_1_2)
                    .build()
                INSTANCE = instance
                instance
            }
        }
    }
}

The database is at version 2 because the tags column arrived after the first release; MIGRATION_1_2 is in the migrations section below. exportSchema = true generates a JSON schema file for every database version (point the room.schemaLocation KSP argument at a folder you commit). Set it. Six months from now when a migration breaks on some user's phone running a version you forgot existed, that schema history saves you.

Setting Up SQLite on iOS

Core Data is Apple's official answer here. Object graph management with change tracking, undo support, lazy loading — a lot of machinery. Sometimes too much. But it is the default path and Apple keeps investing in it, so you should know it even if you end up choosing something lighter.

Core Data with SQLite Backend

Persistence stack:

import CoreData

class PersistenceController {
    static let shared = PersistenceController()

    let container: NSPersistentContainer

    init(inMemory: Bool = false) {
        container = NSPersistentContainer(name: "NotesModel")
        if inMemory {
            container.persistentStoreDescriptions.first?.url =
                URL(fileURLWithPath: "/dev/null")
        }

        // Core Data SQLite stores already use WAL by default. SQLite pragmas
        // go under NSSQLitePragmasOption if you want to set one explicitly.
        let description = container.persistentStoreDescriptions.first
        description?.setOption(
            ["journal_mode": "WAL"] as NSDictionary,
            forKey: NSSQLitePragmasOption
        )

        container.loadPersistentStores { description, error in
            if let error = error {
                fatalError("Core Data store failed: \(error)")
            }
        }

        container.viewContext.automaticallyMergesChangesFromParent = true
        container.viewContext.mergePolicy = NSMergeByPropertyObjectTrumpMergePolicy
    }
}

Direct SQLite with GRDB.swift

GRDB.swift is SQLite without the object graph overhead. No relationship management, no undo support. Just tables and queries with a Swift-native API:

import GRDB

// Define your record type
struct Note: Codable, FetchableRecord, PersistableRecord {
    var id: Int64?
    var title: String
    var content: String
    var createdAt: Date
    var updatedAt: Date
    var isSynced: Bool

    mutating func didInsert(_ inserted: InsertionSuccess) {
        id = inserted.rowID
    }
}

// Set up the database
func openDatabase(atPath path: String) throws -> DatabasePool {
    let dbPool = try DatabasePool(path: path)

    try dbPool.write { db in
        try db.create(table: "note", ifNotExists: true) { t in
            t.autoIncrementedPrimaryKey("id")
            t.column("title", .text).notNull()
            t.column("content", .text).notNull()
            t.column("createdAt", .datetime).notNull()
            t.column("updatedAt", .datetime).notNull()
            t.column("isSynced", .boolean).notNull().defaults(to: false)
        }
    }

    return dbPool
}

Type-safe queries, straightforward migrations, and it works well with Swift concurrency. Core Data needs no WAL setup: its SQLite stores use WAL by default, and pragmas only need NSSQLitePragmasOption when you want something else. For apps where Core Data's object graph is overkill — which is most apps — GRDB is the better fit.

Database Schema Design for Mobile

Stop normalizing so aggressively on mobile. Third normal form is for servers with fast disks and no users watching a spinner. On a phone, a JOIN to fetch an author name that never changes is wasted work on every single list scroll.

Design tables around your screens. Author name on the notes table? Yes. Data duplication. Worth it.

-- Normalized approach (server-style)
CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  author_id INTEGER REFERENCES authors(id),
  created_at INTEGER NOT NULL
);

-- Denormalized approach (mobile-friendly)
CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  author_id INTEGER,
  author_name TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  is_synced INTEGER DEFAULT 0,
  sync_version INTEGER DEFAULT 0
);

But not everything should be flattened. If a piece of data changes often and lives in many records, duplication becomes an update nightmare. Roughly second normal form with targeted denormalization for read-heavy screens. That is the sweet spot.

And add sync metadata columns now. is_synced, sync_version, updated_at, deleted_at for soft deletes. Even if you have no sync feature today. Because adding them later means a migration on every user's device, a data backfill, and testing you did not budget for.

CRUD Operations: Both Platforms

Android CRUD with Room and ViewModel

class NoteRepository(
    private val noteDao: NoteDao
) {
    val allNotes: Flow<List<Note>> = noteDao.getAllNotes()

    suspend fun createNote(title: String, content: String): Long {
        val note = Note(
            title = title,
            content = content,
            createdAt = System.currentTimeMillis(),
            updatedAt = System.currentTimeMillis()
        )
        return noteDao.insertNote(note)
    }

    suspend fun updateNote(note: Note) {
        noteDao.updateNote(
            note.copy(updatedAt = System.currentTimeMillis(), isSynced = false)
        )
    }

    suspend fun deleteNote(note: Note) {
        noteDao.deleteNote(note)
    }

    fun searchNotes(query: String): Flow<List<Note>> {
        return noteDao.searchNotes(query)
    }
}

class NoteViewModel(
    private val repository: NoteRepository
) : ViewModel() {
    val notes = repository.allNotes
        .stateIn(viewModelScope, SharingStarted.WhileSubscribed(5000), emptyList())

    fun createNote(title: String, content: String) {
        viewModelScope.launch {
            repository.createNote(title, content)
        }
    }

    fun deleteNote(note: Note) {
        viewModelScope.launch {
            repository.deleteNote(note)
        }
    }
}

iOS CRUD with GRDB

class NoteRepository {
    private let dbPool: DatabasePool

    init(dbPool: DatabasePool) {
        self.dbPool = dbPool
    }

    // Create
    func createNote(title: String, content: String) throws -> Note {
        try dbPool.write { db in
            var note = Note(
                id: nil,
                title: title,
                content: content,
                createdAt: Date(),
                updatedAt: Date(),
                isSynced: false
            )
            try note.insert(db)
            return note
        }
    }

    // Read all
    func fetchAllNotes() throws -> [Note] {
        try dbPool.read { db in
            try Note
                .order(Column("updatedAt").desc)
                .fetchAll(db)
        }
    }

    // Update
    func updateNote(_ note: inout Note) throws {
        note.updatedAt = Date()
        note.isSynced = false
        try dbPool.write { db in
            try note.update(db)
        }
    }

    // Delete
    func deleteNote(_ note: Note) throws {
        try dbPool.write { db in
            _ = try note.delete(db)
        }
    }

    // Search
    func searchNotes(query: String) throws -> [Note] {
        try dbPool.read { db in
            let pattern = "%\(query)%"
            return try Note
                .filter(Column("title").like(pattern) || Column("content").like(pattern))
                .fetchAll(db)
        }
    }
}

Migrations and Versioning Strategies

This is the hard part. Not conceptually — adding a column is simple SQL. The hard part is that migrations run on the user's device, on their actual data, across every version of your app they might be upgrading from. You cannot roll back. You cannot SSH in. The migration either works or the app is broken for that user until your next App Store update gets approved, which could be days.

A user on version 1 updates directly to version 5. Can your migration chain handle that? What about the user who somehow still has a database from the beta? These are real scenarios. Room migrations are objects with a start version, end version, and the SQL to execute. Each one must be tested against real-world database files, not just empty schemas:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Add a tags column; NOT NULL DEFAULT '' matches `val tags: String = ""`
        db.execSQL("ALTER TABLE notes ADD COLUMN tags TEXT NOT NULL DEFAULT ''")

        // Same name Room generates for Index(value = ["tags"])
        db.execSQL("CREATE INDEX IF NOT EXISTS index_notes_tags ON notes(tags)")
    }
}

// A later release: ship this together with a Category entity, a categoryId
// field on Note, version = 3 and .addMigrations(MIGRATION_1_2, MIGRATION_2_3)
val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(db: SupportSQLiteDatabase) {
        // Create a new categories table
        db.execSQL("""
            CREATE TABLE categories (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                color TEXT NOT NULL DEFAULT '#000000'
            )
        """)

        // Add category_id to notes
        db.execSQL("ALTER TABLE notes ADD COLUMN category_id INTEGER")
    }
}

Keep database files from every shipped version of your app. Test every migration against them. Clean-schema tests miss the weird data shapes that real users create.

SQLite's ALTER TABLE is limited. No dropping columns before 3.35.0. No renaming before 3.25.0. No changing types ever. The workaround is the four-step dance: create new table, copy data, drop old, rename. Room has @AutoMigration for simple cases. Write complex ones by hand.

Performance Optimization

SQLite is fast. But "fast" and "60fps scrolling while a background sync writes 200 records" are different things. A missing index turns a lookup into a full table scan, and the gap grows with the table: on a large table the indexed query can be many times faster. Measure it on your own data with EXPLAIN QUERY PLAN and a profiler rather than trusting a rule of thumb.

Indexing

Column in a WHERE, ORDER BY, or JOIN? It needs an index. But every index slows writes. Index what you query. Leave the rest alone.

-- Good: index columns used in WHERE and ORDER BY
CREATE INDEX idx_notes_updated ON notes(updated_at);
CREATE INDEX idx_notes_synced ON notes(is_synced) WHERE is_synced = 0;

-- Composite index for queries that filter AND sort
CREATE INDEX idx_notes_category_date ON notes(category_id, updated_at DESC);

-- Use EXPLAIN QUERY PLAN to verify your indexes are being used
EXPLAIN QUERY PLAN SELECT * FROM notes WHERE category_id = 5 ORDER BY updated_at DESC;

That partial index on is_synced — if 95% of rows are synced, a full index wastes space. The partial index covers only unsynced rows. Tiny and fast.

WAL Mode

Non-negotiable. Default journal mode means readers block writers. WAL mode allows concurrent reads and writes:

-- Enable WAL mode (do this once, it persists)
PRAGMA journal_mode=WAL;

-- Also useful pragmas for mobile
PRAGMA synchronous=NORMAL; -- Slightly less durable but much faster
PRAGMA cache_size=-2000; -- 2MB page cache
PRAGMA temp_store=MEMORY; -- Keep temp tables in memory

Room enables WAL by default (its JournalMode.AUTOMATIC setting picks WAL on devices that are not low-RAM). You might already have it without knowing.

Batch Operations

100 inserts without a transaction: 100 disk flushes. 100 inserts inside a transaction: one flush. Most people miss this entirely.

// Android Room - batch insert with transaction
@Dao
interface NoteDao {
    @Insert
    suspend fun insertAll(notes: List<Note>)

    @Transaction
    suspend fun replaceAllNotes(notes: List<Note>) {
        deleteAll()
        insertAll(notes)
    }

    @Query("DELETE FROM notes")
    suspend fun deleteAll()
}

A sync that writes a few hundred records one by one pays for a few hundred commits, which can show up as a visible hang. Inside one transaction it pays for one. Two extra lines of code.

Handling Concurrent Access and Thread Safety

This will crash your app:

// BAD: This will cause "Cannot access database on the main thread" crash
fun loadNotes() {
    val notes = database.noteDao().getAllNotesSync() // Blocks main thread!
    adapter.submitList(notes)
}

"Cannot access database on the main thread." Every Android developer hits this exactly once.

SQLite allows multiple concurrent readers but one writer, even in WAL mode. On mobile you have the main thread, background sync, push notification handlers — all hitting the database. Room handles most of it (suspend functions run off main, Flow queries observe changes), but you still need the right patterns:

// GOOD: Observe changes reactively
fun observeNotes() {
    viewModelScope.launch {
        database.noteDao().getAllNotes().collect { notes ->
            _uiState.update { it.copy(notes = notes) }
        }
    }
}

On iOS, DatabasePool over DatabaseQueue when you need concurrent reads. WAL mode, multiple readers, single writer. GRDB's ValueObservation gives you reactive change tracking:

// iOS GRDB - observe database changes
let observation = ValueObservation.tracking { db in
    try Note
        .order(Column("updatedAt").desc)
        .fetchAll(db)
}

let cancellable = observation.start(in: dbPool) { error in
    print("Error: \(error)")
} onChange: { notes in
    // Update UI with fresh notes
    self.notes = notes
}

Data Synchronization with Remote Servers

This is one approach. There are others. None of them are simple.

Change-tracking with timestamp-based conflict resolution. Every record has updated_at and is_synced. Modify offline, mark unsynced. Connectivity returns, push then pull.

class SyncManager(
    private val noteDao: NoteDao,
    private val api: NotesApi,
    private val prefs: SharedPreferences
) {
    suspend fun sync() {
        // Step 1: Push local changes to server
        val unsyncedNotes = noteDao.getUnsyncedNotes()
        if (unsyncedNotes.isNotEmpty()) {
            val response = api.pushNotes(unsyncedNotes)
            if (response.isSuccessful) {
                // Mark pushed notes as synced in one transaction, not one write per note
                noteDao.updateNotes(unsyncedNotes.map { it.copy(isSynced = true) })
            }
        }

        // Step 2: Pull remote changes since last sync
        val lastSyncTime = prefs.getLong("last_sync", 0)
        val remoteChanges = api.getChangesSince(lastSyncTime)

        // Step 3: Merge remote changes with local data
        remoteChanges.forEach { remoteNote ->
            val localNote = noteDao.getNoteById(remoteNote.id)

            if (localNote == null) {
                // New remote note, insert it
                noteDao.insertNote(remoteNote.copy(isSynced = true))
            } else if (localNote.isSynced) {
                // No local changes, accept remote version
                noteDao.updateNote(remoteNote.copy(isSynced = true))
            } else {
                // Conflict! Both modified. Last-write-wins strategy:
                if (remoteNote.updatedAt > localNote.updatedAt) {
                    noteDao.updateNote(remoteNote.copy(isSynced = true))
                }
                // Otherwise keep local version, it will push on next sync
            }
        }

        // Step 4: Update last sync timestamp
        prefs.edit().putLong("last_sync", System.currentTimeMillis()).apply()
    }
}

Last-write-wins covers most apps. If you need concurrent edits to the same document, you are looking at operational transformation or CRDTs, and that is a different article entirely.

Encryption with SQLCipher

Health records, financial data, personal messages. If your app stores any of these, encrypt the database at rest. SQLCipher: 256-bit AES on the entire file. On Android, use the current sqlcipher-android library; the older android-database-sqlcipher artifact and its SupportFactory are deprecated and no longer updated.

On Android with Room:

// build.gradle.kts: sqlcipher-android replaces the deprecated
// android-database-sqlcipher (check Maven Central for the latest version)
implementation("net.zetetic:sqlcipher-android:4.19.1@aar")
implementation("androidx.sqlite:sqlite:2.7.0")

// Load the native library once, before any database call
System.loadLibrary("sqlcipher")

// Never hardcode the key; load it from the Android Keystore (see below)
val passphrase: ByteArray = loadDatabaseKey()
val factory = SupportOpenHelperFactory(passphrase)

val db = Room.databaseBuilder(
    context,
    AppDatabase::class.java,
    "encrypted_notes.db"
)
.openHelperFactory(factory)
.build()

Never hardcode the encryption key. Android Keystore or iOS Keychain. If the key is lost, the data is gone — no backdoor. And storing the key in SharedPreferences or UserDefaults defeats the entire point.

iOS with GRDB. usePassphrase only exists in the SQLCipher build of GRDB; the regular package links the system SQLite, which has no encryption:

// Requires the SQLCipher build of GRDB, e.g. in your Podfile:
//   pod 'GRDB.swift/SQLCipher'
//   pod 'SQLCipher', '~> 4.0'
// (and no plain 'GRDB.swift' pod). SwiftPM needs a GRDB fork; see GRDB's README.
var config = Configuration()
config.prepareDatabase { db in
    // Read the key from the Keychain here rather than capturing it earlier
    try db.usePassphrase(try loadDatabaseKey())
}

let dbPool = try DatabasePool(
    path: dbPath,
    configuration: config
)

Testing Database Code

Broken queries surface weeks after release when a specific data shape hits a code path you never tested. Test your database code or pay for it later.

Android: In-Memory Database Tests

class NoteDaoTest {
    private lateinit var database: AppDatabase
    private lateinit var noteDao: NoteDao

    @Before
    fun setup() {
        database = Room.inMemoryDatabaseBuilder(
            ApplicationProvider.getApplicationContext(),
            AppDatabase::class.java
        )
            .allowMainThreadQueries() // Only for testing!
            .build()
        noteDao = database.noteDao()
    }

    @After
    fun teardown() {
        database.close()
    }

    @Test
    fun insertAndRetrieveNote() = runTest {
        val note = Note(title = "Test", content = "Content")
        val id = noteDao.insertNote(note)
        val retrieved = noteDao.getNoteById(id)

        assertNotNull(retrieved)
        assertEquals("Test", retrieved?.title)
    }

    @Test
    fun searchFindsMatchingNotes() = runTest {
        noteDao.insertNote(Note(title = "Kotlin Tips", content = "Great stuff"))
        noteDao.insertNote(Note(title = "Swift Guide", content = "Also great"))
        noteDao.insertNote(Note(title = "Recipes", content = "Kotlin curry"))

        val results = noteDao.searchNotes("Kotlin").first()
        assertEquals(2, results.size)
    }
}

Testing Migrations

Room's MigrationTestHelper. It builds the version 1 database from the exported schema JSON, so exportSchema and the schema folder have to be set up first:

@RunWith(AndroidJUnit4::class)
class MigrationTest {
    @get:Rule
    val helper = MigrationTestHelper(
        InstrumentationRegistry.getInstrumentation(),
        AppDatabase::class.java
    )

    @Test
    fun migrate1To2() {
        // Create database at version 1
        helper.createDatabase("test-db", 1).apply {
            execSQL("INSERT INTO notes (title, content, created_at, updated_at, is_synced) VALUES ('Old Note', 'Content', 0, 0, 0)")
            close()
        }

        // Run migration and validate
        val db = helper.runMigrationsAndValidate("test-db", 2, true, MIGRATION_1_2)

        // Verify the new column exists and old data is preserved
        val cursor = db.query("SELECT tags FROM notes")
        assertTrue(cursor.moveToFirst())
        assertEquals("", cursor.getString(0))
    }
}

Common Pitfalls and Debugging Techniques

Database locked errors. Multiple connections, one holding a write lock too long. Single database instance. Wrap long operations in transactions.

Leaked cursors. Raw SQLite requires manual cursor closing. Skip it and you leak memory until the app crashes. Room handles this. One more reason to use it.

Storing large blobs. Store paths, not pixels. SQLite handles blobs up to 2GB, sure. Performance tanks and the database file balloons. Images go on disk. The path goes in the database.

String-concatenated queries. SQL injection is real on mobile too. Parameterized queries. Room enforces this at compile time.

Main-thread queries. Dropped frames. Visible jank. Room throws an exception on Android. On iOS you have to be disciplined about dispatching to a background queue yourself.

Debugging Tools

Android Studio's Database Inspector: browse tables, run queries, modify data live on a connected device. On iOS, pull the database file from ~/Library/Developer/CoreSimulator/Devices/ and open it with the SQLite CLI.

SQL logging in debug builds:

// Enable SQL query logging in debug builds
if (BuildConfig.DEBUG) {
    val db = Room.databaseBuilder(context, AppDatabase::class.java, "notes.db")
        .setQueryCallback({ sqlQuery, bindArgs ->
            Log.d("RoomSQL", "Query: $sqlQuery Args: $bindArgs")
        }, Executors.newSingleThreadExecutor())
        .build()
}

Case Study: Building an Offline-First Notes App

Requirements: create and edit text notes, sync across devices, work fully offline. Simple on paper.

Architecture Decisions

Room on Android, GRDB on iOS. Notes table plus a sync_queue table for pending operations:

CREATE TABLE sync_queue (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  entity_type TEXT NOT NULL,
  entity_id INTEGER NOT NULL,
  operation TEXT NOT NULL, -- 'INSERT', 'UPDATE', 'DELETE'
  payload TEXT, -- JSON of the changed data
  created_at INTEGER NOT NULL,
  retry_count INTEGER DEFAULT 0,
  last_error TEXT
);

Why a queue instead of the simpler is_synced flag? Deletes. A deleted row cannot be marked unsynced because it is gone. The queue preserves intent until the server acknowledges it.

The Sync Flow

Every modification writes to the notes table and enqueues a sync operation. Same transaction. WorkManager processes the queue when connectivity returns. The worker takes a DAO and an API client, so it is built through Hilt; your Application also needs to provide a HiltWorkerFactory to WorkManager:

// WorkManager's default factory can only call a (Context, WorkerParameters)
// constructor. Extra dependencies need Hilt (shown here) or a custom WorkerFactory.
@HiltWorker
class SyncWorker @AssistedInject constructor(
    @Assisted context: Context,
    @Assisted params: WorkerParameters,
    private val syncDao: SyncQueueDao,
    private val api: NotesApi
) : CoroutineWorker(context, params) {

    override suspend fun doWork(): Result {
        val pendingOps = syncDao.getPendingOperations()

        for (op in pendingOps) {
            try {
                when (op.operation) {
                    "INSERT", "UPDATE" -> api.upsertNote(op.payload)
                    "DELETE" -> api.deleteNote(op.entityId)
                }
                syncDao.deleteOperation(op.id)
            } catch (e: Exception) {
                syncDao.incrementRetryCount(op.id, e.message)
                if (op.retryCount >= 5) {
                    // Move to dead letter queue or notify user
                    syncDao.markAsFailed(op.id)
                }
            }
        }

        return Result.success()
    }
}

Conflict Resolution

Last-write-wins with one addition: when both client and server modified the same note, the server returns both versions. The app shows a simple diff. User picks. Not collaborative editing, but it covers the common case of one person editing on two devices.

Results

This case study is an illustrative design, not a measured deployment, so there are no benchmark numbers to report. The decisions that matter for size and speed: keep only text and metadata in SQLite and images on the file system, which keeps the database file small, and send only queued changes, which keeps each sync proportional to what changed. Measure sync time and database size in your own beta.

Design the offline experience first. Build the app against local data. Treat sync as a separate layer you add on top. The teams that build network-first and bolt offline on later are the ones still debugging sync bugs six months after launch.

Sync is the hard part and it always will be.

What I Would Do Differently Next Time

Add sync metadata columns and a sync queue table from day one. Even if the first version has no sync. Adding them later means a migration, a backfill, and testing you will wish you had budgeted for. And test every migration against database files pulled from real devices. Not clean test databases. Real ones, with the weird data your users actually created.