Two-week programme · Day 2 of 14
One transaction, two rows
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
- Start a transaction. Everything until COMMIT counts as one write.
- Update the job row. Valve 3 becomes leaking, and the row is marked pending, meaning not yet sent.
- Insert the outbox row. It records what changed, a new op_id, and base version 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.
- 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
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;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.
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)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.
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}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.
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}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 outbox holds 500 rows and the user edits again. Merge the rows or append?
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
- Build40 minOffline outbox with idempotent sync
Write it yourself before reading the solution. Then ask of each line: what if the process dies here?
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?