Skip to content
Get in touch

Maximizing user retention

The engineering behind an app that never throws its user out: negative ids for objects created offline, an outbox of three columns that collapses pending operations, a settle that is one UPDATE, sessions that last for years and schema migrations that are rehearsed. With code.

By Bogdan Vladoiu, 2026-11-17

In Why applications lose users I argued that users leave apps that stop working without a connection, sign them out without being asked, or lose their data on an update. I ended it with a couple of clues about how I build the opposite and called the rest a trade secret. This is the rest.

Everything here fits in a few hundred lines of code over SQLite. The examples are Kotlin and SQL, written for this article around a notes app: a note has a title and a body, and a note has attachments. db.exec runs one statement, db.transaction { } runs a block atomically. Any driver gives you those two.

The rule the whole design serves: the best way to never lose a user is to never throw him out of the app, under any circumstance.

What "works offline" means

Showing cached data offline is the easy half. The half that keeps users is letting them create, edit and delete offline, and then allowing them to forget about it. Do not just cache data, cache the user's intention. A user who tapped "buy again" in a tunnel made a deal with you. Your job is to remember the deal until the network is back.

That needs three things, and no more:

  • an id for an object the server has never seen,
  • a record of what the server has not been told yet,
  • a way to swap the first for the real thing when the server answers.

Most designs solve this by decorating the schema: is_synced, is_deleted, created_offline, local_id, sync_status, updated_at. Every table grows columns that describe plumbing instead of the product, every query has to remember to filter them, and every new feature has to keep them consistent. I have never needed any of them.

Negative ids

An object created on the device gets a negative id: -1, then -2, then -3. The server issues every positive id. The sign of the id is the origin of the object, so nothing else has to say it.

CREATE TABLE notes (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body  TEXT NOT NULL,
    captured_at INTEGER NOT NULL
) WITHOUT ROWID
fun Db.nextLocalId(table: String): Long =
    minOf(0L, long("SELECT MIN(id) FROM $table") ?: 0L) - 1

There is no sequence table and no counter to persist. The next local id is one below the lowest id in the table, per table.

WITHOUT ROWID is deliberate. With a rowid table, an insert that forgets its id gets one invented by SQLite, a positive one, straight into the space only the server may issue. Without a rowid the same mistake fails loudly on the first run.

What you get for the price of a minus sign:

  • No correlation ids. The object has one id at any moment, in one column.
  • No extra columns. id < 0 answers "has the server seen this" in any query, including joins.
  • The schema models the product and nothing else.

People ask about UUIDs at this point. A random 128-bit key does avoid collisions, and it is the right tool when devices must write to each other with no server in between. In a client-server product you pay for it everywhere else: wider keys in every index and every foreign key, inserts scattered across the index instead of appended, ids nobody can read out loud while debugging. You will not feel it in the client. You will feel it later, in the backend. If a server is the authority, let the server issue the ids and let devices borrow the negative half.

The outbox

The second piece is one service table that records what the server has not been told.

CREATE TABLE outbox (
    object_type INTEGER NOT NULL,
    object_id   INTEGER NOT NULL,
    operation   TEXT NOT NULL,
    PRIMARY KEY (object_type, object_id)
) WITHOUT ROWID

Three columns. operation is C, U or D. There is no payload column, no timestamp, no retry counter. The payload is the row itself, read at the moment it is sent, so what goes out is always the final form. The primary key allows one pending operation per object, and that constraint is the whole trick: the outbox is not a log of everything the user did, it is the shortest message that brings the server up to date.

A new operation meets whatever is already pending for that object:

Pending New Result Why
nothing C, U or D that operation first thing the server has to hear
C U C an offline create absorbs every update after it
U U U updates collapse into one
U D D a delete absorbs the pending update
C D nothing the server never knew the object, so there is nothing to tell
fun Db.outbox(type: Int, id: Long, operation: String) {
    val pending = string(
        "SELECT operation FROM outbox WHERE object_type = ? AND object_id = ?", type, id
    )
    when {
        "D" == operation && "C" == pending -> forget(type, id)
        "U" == operation && "C" == pending -> Unit
        else -> exec("INSERT OR REPLACE INTO outbox VALUES (?, ?, ?)", type, id, operation)
    }
}

fun Db.forget(type: Int, id: Long) =
    exec("DELETE FROM outbox WHERE object_type = ? AND object_id = ?", type, id)

That function is the logic. It is closed: there is no sixth case waiting to be discovered in production, and I have copied it from project to project unchanged. A user who creates a note in a tunnel, edits it eleven times and deletes it has generated zero network requests, and a user who edits a synced note eleven times has generated one.

Same transaction, always

The outbox row is written in the same transaction as the row it describes.

fun Db.save(note: Note) = transaction {
    val created = 0L == note.id
    if (created) note.id = nextLocalId("notes")
    exec(
        "INSERT OR REPLACE INTO notes VALUES (?, ?, ?, ?)",
        note.id, note.title, note.body, note.capturedAt
    )
    outbox(NOTE, note.id, if (created) "C" else "U")
}

If the two writes can be separated by a crash, the outbox stops being a truthful account of what the server has not seen, and from then on every sync is a guess. This one rule is why the pattern survives power loss: after a reboot the queue is exactly what it was, and the user can keep working and sync later.

Deleting a parent

A note has attachments. Deleting the note deletes them, and one D for the note tells the server everything. A pending C for an attachment of that note must not survive, or it would later be sent against a parent the server no longer has.

fun Db.delete(note: Note) = transaction {
    exec("DELETE FROM outbox WHERE object_type = ? AND object_id IN " +
        "(SELECT id FROM attachments WHERE note_id = ?)", ATTACHMENT, note.id)
    exec("DELETE FROM attachments WHERE note_id = ?", note.id)
    exec("DELETE FROM notes WHERE id = ?", note.id)
    outbox(NOTE, note.id, "D")
}

For a note that was created offline, the last line removes its own pending create and the whole thing disappears without a trace, which is correct.

Sending, and the settle

Sending walks the queue and stops at the first failure. Nothing is dropped; what was not sent waits for the next attempt.

fun flush(db: Db, api: Api): Int {
    var sent = 0
    for (intent in db.queue()) {
        when (intent.operation) {
            "C" -> {
                val note = db.note(intent.id)
                val id = api.create(note.title, note.body, note.capturedAt) ?: return sent
                db.settle("notes", NOTE, intent.id, id)
            }
            "U" -> {
                if (!api.update(db.note(intent.id))) return sent
                db.forget(NOTE, intent.id)
            }
            "D" -> {
                if (!api.delete(intent.id)) return sent
                db.forget(NOTE, intent.id)
            }
        }
        sent++
    }
    return sent
}

Three details in there carry most of the weight.

A create is sent as properties only, never with its negative id. The local id is private to the device. Two phones both have a note -1, and the server must never see either. Letting a local id travel is how origin starts to erode and how a local_id column ends up in the backend schema.

The settle is one UPDATE. When the server answers a create with the real id, the device swaps it in and clears the intent, in one transaction:

fun Db.settle(table: String, type: Int, local: Long, settled: Long) = transaction {
    check(local < 0 && settled > 0)
    exec("UPDATE $table SET id = ? WHERE id = ?", settled, local)
    forget(type, local)
}

The check is one of the very few places where I want a crash. A settle with the signs the wrong way round is corruption, and corruption should stop the program, not be handled.

Children follow their parent without any code, because the schema says so:

CREATE TABLE attachments (
    id      INTEGER PRIMARY KEY,
    note_id INTEGER NOT NULL REFERENCES notes(id) ON UPDATE CASCADE,
    path    TEXT NOT NULL
) WITHOUT ROWID

Every foreign key into a table that can be rekeyed is ON UPDATE CASCADE. A plain foreign key rejects the settle. Remember that SQLite enforces foreign keys only when the connection has turned them on, so turn them on in the one place that opens the database.

The order falls out of the data.

fun Db.queue(): List<Intent> = intents(
    "SELECT object_type, object_id, operation FROM outbox ORDER BY object_type ASC, object_id DESC"
)

Number the types so that parents are below children (note 1, attachment 2) and parents are sent first. Within a type, descending order puts -1 before -2, which is the order the user created them in. No sequence column is needed.

A retry must not duplicate

A create carries no id, so consider the worst moment to lose the connection: the server has committed the note and the reply never arrives. The device still holds a pending C and will send it again.

The create therefore carries its own identity: the device, and the moment of capture. The server looks before it inserts.

SELECT id FROM notes WHERE device_id = ? AND captured_at = ?

If a row is there, the server answers with its id as if it had just created it. The device settles and never knows the difference. Updates and deletes need nothing of the kind: sending the final form twice, or deleting twice, ends in the same state.

Nothing waits on a settle

A negative id means "not settled yet", and the rest of the app treats it as a fact about the item, not as a state to wait for. A screen that meets an unsettled note shows it, with a small "waiting to sync" mark. A feature that needs the server's copy, such as sharing a link to the note, skips the item or tells the user it will be available once synced. Nothing blocks, nothing spins, nothing coordinates.

The sync mark deserves an icon and a word, never a colour alone. Green against amber is exactly the pair about one man in twelve cannot tell apart.

The messages are short and they are promises: "Waiting to sync", "This will be sent later". Gmail says "This email will be sent later" and I allow myself to forget about the email. That sentence is the product.

Getting changes back

The other direction is a plain read with one optional argument: since. The server stamps every write with its own clock and returns the stamp with every reply. The client stores the last stamp it saw and repeats it back.

GET /notes?since=1794880000000&limit=200

The server returns what changed after that stamp, oldest first, including tombstones for deleted rows. Without since the same call is an ordinary full read, so there is one endpoint, not a sync protocol. The stamp is always the server's time, never the device's: device clocks are wrong in both directions and users change them.

One stamp per row is enough. "Created at" and "updated at" collapse into it, for the same reason a create and its updates collapse in the outbox.

Applying what comes back has one rule:

fun Db.put(note: Note) {
    if (null != pending(NOTE, note.id)) return
    exec("INSERT OR REPLACE INTO notes VALUES (?, ?, ?, ?)",
        note.id, note.title, note.body, note.capturedAt)
}

What a person did on this device and has not sent yet is not overwritten by what the server last knew. Once the intent is sent, the next pull brings the server's view and the two agree. A tombstone deletes the local row, which is how a delete on one phone reaches the other.

This is where I part ways with the slogan "the database is the source of truth". On a phone with modest storage and a user in front of it, the safer rule is: the last thing the user was shown is the source of truth. If the screen said the note was saved, it is saved, whatever any server thinks for the next few minutes.

Staying signed in

The outbox keeps the data. The session keeps the user. A user who has been thrown out to a login screen may never return, and they are thrown out far more often by token plumbing than by anything they did.

What I build:

  • Two secrets per device. A long-lived one, issued at sign-in, that does nothing except take part in renewing. A short-lived one, replaced every twenty minutes, that authorises calls. Renewing needs both.
  • One session per user and device. Signing in on a new phone does not disturb the old one. A device list lets the user retire one.
  • A reply that distinguishes "expired" from "denied".

The reason for two secrets is that the user signs in once, on a network they trust, and then roams through cafés and airports for a year. What crosses those networks all day is the short-lived secret. Capturing it buys an attacker minutes.

The third point is the one that costs users when it is missing. Every reply from the server opens with one of three words:

Status Meaning What the client does
OK the call was accepted carries on
EXPIRED the short secret has lapsed renews, then repeats the call
DENIED this session no longer exists deletes its secrets, shows sign-in
fun <T> call(request: (String) -> Reply<T>): Reply<T> {
    val held = session.token
    val first = request(held)
    if (EXPIRED == first.status) {
        if (!renew()) return first
        return request(session.token)
    }
    if (DENIED == first.status && held == session.token) session.forget()
    return first
}

EXPIRED is the only status that makes a client renew, and DENIED is the only one that makes it let go. Use one code for both, the usual 401, and the client has to guess. A backend that answers "denied" for a session that has merely lapsed signs every user out each time a session expires, and nothing in any log says why, because from the server's side every answer is correct.

Two more lines in that function matter.

A server that cannot be reached is neither status. No connection, a timeout, a 502 from a proxy: the client keeps its session and tries later. Signing a user out because a train went into a tunnel is the classic way to lose them.

And held == session.token: a client lets go of its session only when the denial answers the secret it still holds. Without that comparison, a slow call made with the old secret comes back DENIED just after a renewal succeeded, and the client throws away the session it renewed a moment ago.

When several calls find the secret expired at once, one renewal must serve all of them. That is simple to design in on day one and painful to add once forty screens each make their own calls, which is why "background token refresh" belongs in the first release and not in a later one.

Opening the app must not depend on the session

Show the user their data first, from disk. Test the session second, in the background. An app that validates a token before it draws its first screen has made its launch as reliable as the network, and has put a spinner where the home screen should be. Users perceive a delay of 400 milliseconds as the app being slow.

The same goes for capture. Writing a note never asks whether a session exists. Only sending needs one.

Signing out is not losing work

When a session ends, for any reason, delete the secrets and keep the rows. Signing out, signing out everywhere and retiring a device from the device list all leave the local data in place, hidden behind an account filter, and the outbox with it. The user may have a week of offline work on that phone with negative ids. Ending a session must not be the moment it is destroyed. Only closing the account removes the data.

A forced update follows the same rule. It stops the app until the update is installed. It signs nobody out and it touches neither the rows nor the queue.

One consequence nobody warns you about: the better your offline mode, the sooner you will be asked for remote wipe. A user who loses a phone that holds everything will want a device panel and a way to remove that phone. Plan it before you are asked in a hurry, and accept that a wipe of a device that is switched off is always best effort.

Migrations: why most apps never get this far

None of the above survives an update that drops the tables. And that is what a great many apps do, because a proper schema migration looked hard, or because the automatic one their library offered failed in a way nobody could reproduce.

I think this is the real reason most apps have no offline mode. Once data on the device matters, every schema change is a change to a database you cannot see, on a device you do not control, in a state you did not test. Teams that have been burned once stop keeping anything on the device that they cannot refetch.

The arithmetic of being burned: a migration that fails for 0.05% of users, on an app with ten million installs, is five thousand people calling a help desk in one week. And there is a nasty ordinary sequence that needs no bug at all: the phone comes back online, the store updates the app before the app has had a chance to send its queue, and the new version starts by deleting the tables.

What I build is small:

val migrations: Array<List<String>> = arrayOf(
    emptyList(),
    listOf("ALTER TABLE notes ADD COLUMN pinned INTEGER NOT NULL DEFAULT 0"),
    listOf(
        "CREATE TABLE tags (id INTEGER PRIMARY KEY, name TEXT NOT NULL) WITHOUT ROWID",
        "ALTER TABLE notes ADD COLUMN tag_id INTEGER REFERENCES tags(id) ON UPDATE CASCADE"
    )
)

fun Db.openAndMigrate(): Boolean = transaction {
    val found = version()
    if (found > migrations.size) return@transaction false
    if (found > 0) for (v in found until migrations.size) migrations[v].forEach { exec(it) }
    schema.forEach { exec(it) }
    version(migrations.size)
    true
}
  • migrations[v] takes the database from version v to v + 1. Plain integers in one list in the code, read top to bottom.
  • The whole upgrade is one transaction. If any step fails, the user still has exactly the file they had.
  • A file newer than the code is refused, not "downgraded".
  • The schema's CREATE … IF NOT EXISTS statements run after the steps, so an upgraded database is identical to a fresh one. That is a property you can test: build one each way and compare.
  • Each statement is its own entry. Do not pack several into one string and hope the driver runs them all.

Then rehearse it. Keep a database file from every version you have shipped, with real rows in it, and open each one with the new build before release. Prefer additive changes, a new column with a default or a new table, so that old rows never need rewriting.

Limits

This design has edges, and you should know them before you choose it.

  • One authority. A server issues ids and decides order. That fits nearly every product app. It does not fit several people editing one document at the same moment with no server between them; that is what CRDTs are for.
  • Conflicts are settled simply. A pending local change wins over a pulled row, and the last update to reach the server wins there. If two people regularly edit the same field of the same record, add a version check on update and decide what the user sees when it fails.
  • A refused operation. The loop above stops at the first failure. That is right for a lost connection and wrong for an operation the server will refuse forever, which would block everything behind it. Decide before you ship what happens to it. Whatever you choose, do not drop it silently: show the user the item that could not be sent.
  • When the queue is sent. At minimum when the app is opened, when the user pulls to refresh, and on a timer while the app is on screen. Because nothing waits on a settle, that is enough for most products. If yours needs the queue sent while the app is closed, schedule it with the platform's own job scheduler, and know that iOS makes no promise about when it will run.

What it adds up to

One sign on an id, one table of three columns, one function of a dozen lines, one UPDATE, two secrets, three words at the front of every reply, and a list of migrations you have rehearsed. No sync framework and no state machine per row.

The users who open your app in flight mode are not doing it out of boredom. They are the ones who rely on it. Everything above exists so that they never find out which parts needed the network.

Keeping users is half of it. How they arrive in the first place is in Maximizing user acquisition.

ComputeShack builds apps that keep working offline, and fixes the ones that do not.

ComputeShack

Small, good solutions.

[email protected]

Privacy policy · © 2026 ComputeShack