/Tracks

Two-week programme · Day 2 of 14

One transaction, two rows

Study with AI

Part 1Basics

Yesterday we said a save goes to the local database. Now we need the database to remember two things: the new value, and the fact that the server doesn't have it yet.

We keep that second fact in a table called the outbox. Think of the tray on a desk marked to send. Sync later works through that tray.

Words you need today

SQLite
The database built into Android and iOS. It is a single file on the phone.
Transaction
A group of database writes that either all happen or none happen, even if the phone dies midway.
Outbox
A table of changes the server hasn't confirmed yet.
op_id
A unique ID the phone creates for each change, so the server can recognise it if it arrives twice.
Base version
The version of the record the user was looking at when they edited it.
WAL
Write-ahead log, an SQLite mode that lets the screen keep reading while sync is writing.

The story

Priya taps leaking. In one SQLite transaction, the app updates job j-84 and inserts a row into a table called the outbox. The whole thing takes a few milliseconds. The query behind the screen emits again, and valve 3 shows a small clock meaning not yet sent.

At 3:05 pm she pockets the phone, and Android later kills the app to free memory. Nothing is lost. The edit and the record of what must be sent both sit on disk, waiting for something to run them.

Part 2Today's question

How do you save an edit so a crash can never lose it or half-send it?

By the end of this lesson, you should be able to:

  • Write the schema for job and outbox from memory
  • Explain why both rows share a transaction
  • Say what op_id and base_version are for

Part 3In detail

Step by step

  1. Start a transaction. Everything until COMMIT counts as one write.
  2. Update the job row. Valve 3 becomes leaking, and the row is marked pending, meaning not yet sent.
  3. Insert the outbox row. It records what changed, a new op_id, and base version 4.
  4. Commit. Now both rows are on disk together. If the phone died before this line, neither row exists and the user simply sees the old value.
  5. The screen reacts. The screen watches the database, so it redraws and shows a small clock beside valve 3.

Why one transaction matters

Write the job, crash, and never write the outbox row: the edit looks saved but never leaves the phone. Write the outbox row first and crash: the server gets a change the phone doesn't show. One transaction removes both cases, because both rows commit or neither does.

What the outbox row carries

The op_id is generated on the device, and it doubles as the idempotency key: if the same op arrives twice, the server applies it once. The base version records what Priya was looking at when she edited, which is how Day 5 will detect the collision.

Keeping the database fast

WAL mode lets the screen keep reading while sync writes. Short transactions keep the single writer free. Design tables and indexes from the screens' queries, and page with keys rather than OFFSET, so a million rows cost the same as a thousand.

Migrations deserve extra care: a user can skip eleven versions with unsent edits in the outbox, and those edits must survive the upgrade.

Priya's save, as the database sees it

priyas-save-as.sqlSQL
1BEGIN IMMEDIATE;2 3UPDATE job4   SET answers_json = json_set(answers_json, '$.valve_3', 'leaking'),5       sync_state   = 'pending',6       updated_at   = :now7 WHERE id = 'j-84';8 9INSERT INTO outbox (op_id, entity, entity_id, op, payload_json, base_version, status)10VALUES ('0192e0c4-7f3a', 'job', 'j-84', 'update',11        '{"answers.valve_3":"leaking"}', 4, 'pending');12 13COMMIT;
13 linesUTF-8SQL

Remember op 7f3a and base version 4. You will see them again on Days 4, 5 and 6.

The code, explained

This is the real code, and our tests run it. Read each block, then the notes under it.

The rows Priya's edit creates

Two tables hold everything the phone must remember: the job as the user sees it, and the changes the server hasn't confirmed.

LocalDb.ktKotlin
1/** What the supervisor's copy said when the server refused an edit. */2data class Theirs(val field: String, val value: String, val version: Int, val by: String)3 4/** One row of the job table. */5data class Job(6  val id: String,7  val answers: Map<String, String> = emptyMap(),8  val version: Int = 0,9  val syncState: SyncState = SyncState.SYNCED,10  val theirs: Theirs? = null,11  val error: String? = null,12)13 14/** One row of the outbox: a change the server has not confirmed yet. */15data class Op(16  val opId: String,17  val jobId: String,18  val field: String,19  val value: String,20  val baseVersion: Int,21  val by: String = "device",22)
22 linesUTF-8Kotlin
val version: Int
The server version this phone last saw. It becomes the base version of the next edit.
val theirs: Theirs?
Empty until a conflict. Then it holds the other person's value, so the screen can show both.
val opId: String
Created on the device before the first send, so a retry can be recognised.
val baseVersion: Int
What the user was looking at. The server uses it to spot a change made behind their back.

Compiled and tested by npm run test:mobile.

A transaction, all or nothing

SQLite gives you this guarantee. The class below behaves the same way, so the tests can crash mid-write and check that nothing changed.

LocalDb.ktKotlin
1/**2 * Behaves like SQLite for this lesson: a transaction commits whole or not at all,3 * and observers hear about it once, after it commits.4 */5class LocalDb(cursor: Long = 0) {6  private val lock = Any()7  private var committedOutbox: List<Op> = emptyList()8  private var committedCursor = cursor9  private val jobTable = MutableStateFlow<Map<String, Job>>(emptyMap())10 11  /** The query a screen subscribes to. */12  val jobs: StateFlow<Map<String, Job>> = jobTable.asStateFlow()13  val outbox: List<Op> get() = synchronized(lock) { committedOutbox }14  val cursor: Long get() = synchronized(lock) { committedCursor }15 16  fun <T> transaction(block: Tx.() -> T): T = synchronized(lock) {17    val tx = Tx(jobTable.value, committedOutbox, committedCursor)18    val result = tx.block()              // a throw here leaves every table untouched19    committedOutbox = tx.outbox20    committedCursor = tx.cursor21    jobTable.value = tx.jobs             // one emission, however many rows changed22    result23  }24}
24 linesUTF-8Kotlin
val tx = Tx(...)
Works on a scratch copy of the tables, not the real ones.
tx.block()
Runs your writes. If it throws, the function ends here and the real tables never change.
jobTable.value = tx.jobs
Publishes once at the end, so a transaction touching 140 rows redraws the screen once.
val jobs: StateFlow
This is what a screen subscribes to. On Android, a Room or SQLDelight query plays this role.

Compiled and tested by npm run test:mobile.

The save, and the conflict resolution

This is the code behind the tap. It's the only way a screen changes data, and it has no network call in it.

LocalDb.ktKotlin
1/** The only way a screen changes data. Nothing here touches the network. */2class JobRepository(3  private val db: LocalDb,4  private val newOpId: () -> String,5  private val by: String = "device",6  private val requestSync: (Trigger) -> Unit = {},7) {8  fun setAnswer(jobId: String, field: String, value: String) {9    db.transaction {10      val job = jobs.getValue(jobId)11      jobs = jobs + (jobId to job.copy(12        answers = job.answers + (field to value),13        syncState = SyncState.PENDING,14      ))15      outbox = outbox + Op(newOpId(), jobId, field, value, job.version, by)16    }17    requestSync(Trigger.EDIT)            // only after the commit: the edit is already safe18  }19 20  /** The user chose a value in the conflict screen. It is a new edit, based on what they just saw. */21  fun resolve(jobId: String, value: String) {22    db.transaction {23      val job = jobs.getValue(jobId)24      val theirs = checkNotNull(job.theirs) { "$jobId has no conflict" }25      jobs = jobs + (jobId to job.copy(26        answers = job.answers + (theirs.field to value),27        syncState = SyncState.PENDING,28        theirs = null,29      ))30      outbox = outbox + Op(newOpId(), jobId, theirs.field, value, theirs.version, by)31    }32    requestSync(Trigger.EDIT)33  }34}
34 linesUTF-8Kotlin
db.transaction { ... }
The job row and the outbox row commit together. A crash can't leave one without the other.
SyncState.PENDING
Makes the row show its clock. The screen reacts by itself, because it watches this table.
job.version
Copied into the op as its base version: 4 in Priya's case.
requestSync(Trigger.EDIT)
Runs after the commit, so even if scheduling fails, the edit is already safe.
fun resolve
Choosing a value in the conflict screen creates a new edit, based on the version the user just saw. Nothing gets patched in place.

Compiled and tested by npm run test:mobile.

What can go wrong

Crash between the two writes, without a transaction
The edit looks saved but never gets sent. The transaction makes this impossible.
The app updates and the table's shape changes
A migration must keep outbox rows intact, or unsent work is lost.
One slow query blocks the screen
WAL mode and short transactions keep reads and writes from waiting on each other.

Decisions to defend

  • Where do pending writes live? Outbox table in the same database, not an in-memory queue or WorkManager input data. Only the same database lets the data and the pending write commit together.

Part 4Questions and answers

Answer out loud first, then open each one.

Why generate op_id on the device instead of the server?
The device must be able to retry without knowing whether the first attempt arrived. Only an ID that exists before the first send makes a retry recognisable.
The outbox holds 500 rows and the user edits again. Merge the rows or append?
Append by default, because it preserves order and intent. Compact only where it is safe, such as two edits to the same field before either one was sent.

Part 5Going further

The three questions, for today

Where does it live?
In SQLite: the job row and the outbox row, on disk together.
Who runs the next step?
Nothing yet. The row waits for a sync job.
What if it runs twice?
The op_id marks it, so a second send is recognisable.

Practise

Optional reference pages

You don’t need these for the lesson. They hold more detail if you want it later.

The edit is safe, but it is going nowhere, and the app isn't running. Tomorrow: who starts the app again?

← Day 1: A leaking valve, two floors underground

AI Engineer · Day 2 of 14