# Chapter 7: The local database

> Where on the phone do the fetched messages go? If the app is killed halfway through writing them, are some missing when it opens again?

IM Systems in Depth · https://im.liko.page/en/local-database/

At the end of chapter 6, Ben came out of the subway and his phone caught up by its inbox cursor. What that chapter did not say: **where do the fetched messages go?**

Earlier chapters left two debts: chapter 3's outbox must survive the app being killed, and chapter 6's inbox cursor must survive a restart. This chapter puts them, and the messages themselves, into a local database on the phone, and answers its real question: how to write it so that no message goes missing whenever the app is killed.

## 1. Why the phone keeps a copy

Keeping nothing is possible: each time a chat is opened, ask the server for the last 50 messages. Web clients often do (chapter 26). On a phone it costs this:

- Ben opens the app 20 times a day; say he looks at 3 chats each time: **60 chats opened** a day.
- Each is 50 × 200 bytes = **10 KB**, **600 KB** a day; what he actually receives is 267 × 200 bytes ≈ **53 KB** a day (chapter 6). The same messages are downloaded again and again, **11 times** what arrives.
- Each open waits at least one round trip, 60 × 0.2 = **12 s** a day. In the subway, nothing shows.

The outbox and the cursor must be on disk anyway, so the messages go there too. The work splits like this:

- **The screen reads only the local database**; opening a chat does not touch the network.
- **Everything from the network is written to the database first**: each sync page, each push, each ACK.
- **Sent messages are written first too**, as “sending”, shown at once, and marked “sent” when the ACK comes.

Most phones use SQLite: not a server to connect to but a library inside the app, the whole database one file in the app's folder. iOS and Android both ship it.

## 2. What to store

```sql
CREATE TABLE messages (
  id              INTEGER PRIMARY KEY,
  conversation_id INTEGER NOT NULL,
  seq             INTEGER,               -- the server's seq in the conversation (chapter 5); none yet while sending
  sender_id       INTEGER NOT NULL,
  msg_id          BLOB NOT NULL,         -- the message ID the phone made (chapter 4)
  text            TEXT,
  state           TEXT NOT NULL,         -- 'sending' / 'sent' / 'failed' / 'received'
  UNIQUE (conversation_id, seq),
  UNIQUE (sender_id, msg_id)
);
CREATE TABLE gaps (conversation_id INTEGER, from_seq INTEGER, to_seq INTEGER);  -- a stretch not downloaded yet
CREATE TABLE sync_state (key TEXT PRIMARY KEY, value INTEGER);                  -- the inbox cursor
```

- **The outbox is not another table**: it is the rows with `state = 'sending'`. On restart the app finds them and resends them with their original message IDs (chapter 4).
- **`(conversation_id, seq)`** is the index the chat screen uses: opening a chat asks for “the 50 with the highest seq”, and the database goes straight to the end of that conversation and reads 50 rows, as fast with a million messages stored.
- **`gaps` records holes.** After a long time offline, chapter 6 takes only each conversation's newest message, so a stretch is missing locally, for example a row `(hiking, 120, 140)`. When Ben scrolls up to it, the phone fetches it.
- **The cursor lives in the same database as the messages**, not in the app's settings. Why is the next section.

The phone also keeps each conversation's highest seq, which chapter 5 uses to spot a jump.

**How big it gets.** Ben receives 267 messages a day and sends 40. At 300 bytes each (the message plus indexes; an assumption), that is about **92 KB** a day, **33.6 MB** a year. Text is not what fills a phone; photos and videos are (chapter 8).

## 3. Watch it fail: the cursor gets ahead of the messages

The cursor means “I have everything up to here”. After a restart the phone asks only for what comes after it, and the server can only trust it.

A natural way is to keep the two apart. The cursor is one number, so it goes into the app's settings (iOS's UserDefaults, Android's SharedPreferences: a small file separate from the database) as each reply arrives; the messages are many, so a write queue on a background thread writes them into the database one by one. So when the cursor is saved, the queue often has not finished, and which of the two files reaches the disk first is not fixed either.

Here is chapter 6's busy evening: 230 new messages in three groups, 50 a page, a page every 0.2 s. Say writing one message takes 0.1 ms and each **commit**, which waits until the data is really on flash, 5 ms. Drag “the app is killed at”.

*[Interactive figure: open the page to use it, https://im.liko.page/en/local-database/]*

- **Cursor first, messages one by one**: a transaction per message, 255 ms a page, slower than the next page arrives, so the queue grows. Killed at 1.0 s: 156 messages on disk but the cursor at 230. After the restart the phone pulls after 230, and the **74** in between the server thinks it already has; they will not be sent again.
- **Cursor first, a page at a time**: a page written in 10 ms; the window is smaller but still there: **4.7%** of the moments in the sync lose a page if the app dies then.
- **One transaction**: a page of messages and the cursor commit together, both or neither. **No moment loses a message.**

The first two ways' figures (74 messages, 4.7%) rest on the 5 ms commit assumption; only the final 0 rests on none.

The missing messages are not gone for good: when that group's next message arrives, its seq jumps and the phone fetches the gap (chapter 5). But if the missing one were Ben's mother's “arriving tomorrow morning”, it would wait for her next message, maybe next week, and nothing tells him meanwhile.

Being killed is common: iOS gives a background app little time, and [Apple's documentation](https://developer.apple.com/documentation/uikit/extending-your-app-s-background-execution-time) says that if you don't end your tasks in time, the system terminates your app; Android kills background processes when memory is short; users swipe apps away, and apps crash. At v1's 2,000,000 syncs a day, if only 1 in 10,000 is cut between the two writes, that is **200 phones** a day missing messages, **20** at 1 in 100,000, with no error and hard to reproduce.

## 4. The fix: a page of messages and the cursor, one transaction

One rule: **the cursor may not be written before what it stands for.** The simplest way is one transaction:

```sql
BEGIN;
INSERT INTO messages (...) VALUES (...), (...), ...    -- this page's 50
  ON CONFLICT (sender_id, msg_id) DO UPDATE SET seq = excluded.seq, state = excluded.state;  -- a repeat lands on its original row
INSERT OR REPLACE INTO sync_state (key, value) VALUES ('inbox_cursor', :next_cursor);
COMMIT;
```

SQLite's [documentation](https://www.sqlite.org/transactional.html) guarantees that a transaction's changes happen completely or not at all, even if the program crashes or the power fails halfway. That is why the cursor must be in the same database: the settings file and the database are two stores, and no transaction spans both.

Killed before the commit, the same page comes again after the restart; a push and a sync may bring the same message. The repeat lands on its original row by the unique key `(sender_id, msg_id)`, the same idea as chapter 4's server dedup, done on the phone; it is also how Ben's own “sending” row becomes “sent” when the message syncs back from his inbox.

The same rule everywhere the phone writes:

- **A push**: if its inbox seq is exactly cursor + 1, the message and the cursor commit together; if it jumps, store only the message and fetch the missing stretch.
- **Sending**: write the “sending” row first, then send; when the ACK comes, the same row gets its seq and becomes “sent”.
- **Filling a gap**: the older messages and the shortened `gaps` row commit together.

## 5. Estimate: how much the phone writes in a big sync

A transaction per page also fixes writing too slowly. Ben back from a week offline (chapter 6) writes **1,869 messages in 38 pages**, a 7.6 s download: a transaction per message is 1,869 commits, about **9.5 s**, slower than the download; one per page is 38 commits, about **0.38 s**. A month offline: **40.9 s** against **1.6 s**. The 5 ms is an assumption; the number of commits, 49 times fewer, is not. SQLite's [FAQ](https://www.sqlite.org/faq.html#q19) says exactly this: it does tens of thousands of `INSERT`s a second but only “a few dozen transactions per second” (a figure for spinning disks; flash is much faster), and wrapping many `INSERT`s in one transaction spreads the commit's cost over all of them.

## 6. Reinstalling and new phones

The local database lives in the app's folder; delete the app or change phones and it is gone. How much comes back depends on how much the server keeps. This book's server keeps the messages, so a new phone does chapter 6's “list first”: the newest message of each of 200 conversations, about **42 KB** in **4 requests**, and older messages as Ben scrolls. How deep a new device's first sync should go is chapter 14's question. Some products' servers delete a message once delivered, such as WhatsApp ([privacy policy](https://www.whatsapp.com/legal/privacy-policy)) and Signal ([terms](https://signal.org/legal/)); history is only on the phone, and losing it means a backup or the old phone: chapter 41.

## 7. The cost

- **Two copies of the truth.** The phone's copy can be wrong: a message the server rejects must be marked failed, a hole from syncing must be recorded and filled when reached, and the conversation list drifts from the server's (chapter 17). The client must be able to repair itself.
- **Storage.** About 34 MB of text a year; in v2 the 500-member hiking group sends 1,000 messages a day, 110 MB a year for that one group. Users need a way to clean up.
- **Upgrades need migrations.** The tables change between versions, and each upgrade must change the old database on every phone in place (chapter 29).
- **Discipline in the code.** Every place that moves a cursor or a state must do it in one transaction with the data it stands for. The rule is easy; keeping everyone to it as the code grows is not.

| Resource | Nothing stored, ask the server each time | Local database, a transaction per page |
|---|---|---|
| Phone requests | one per chat opened, 60 a day | none to open a chat; only syncs and gap fills |
| Phone data | about 600 KB a day for opening chats | only new messages, about 53 KB a day |
| Phone storage | almost none | about 92 KB a day, 34 MB a year |
| Phone disk writes | almost none | 38 commits after a week offline (1,869 one by one) |

## 8. Other answers

- **Store nothing, or only the latest few**: no migrations and no second copy. Fine where the network is good and conversations are few (say, a customer-service window in a browser); chapter 26 covers the web.
- **Messages first, then the cursor, in separate stores**: when the cursor cannot join the transaction, this order loses nothing either; the cost is downloading a page again after a kill.
- **TDLib**: Telegram's open-source client library; its [README](https://github.com/tdlib/td) says it takes care of the network, encryption and local data storage, and its source includes SQLite; the `use_message_database` [parameter](https://core.telegram.org/tdlib/docs/classtd_1_1td__api_1_1set_tdlib_parameters.html) keeps chats and messages between restarts.
- **Signal**: the local database is an encrypted SQLite (SQLCipher, in the [Android](https://github.com/signalapp/Signal-Android/blob/main/gradle/libs.versions.toml) and [desktop](https://github.com/signalapp/Signal-Desktop/blob/main/package.json) dependencies). The server keeps no history, so the phone holds all of it, and encryption is worth it.

## 9. This chapter's decision

*[Interactive figure: open the page to use it, https://im.liko.page/en/local-database/]*

**Decision card**

- Problem: Fetched messages, unsent messages and the sync cursor must all survive the app being killed and restarted; asking the server each time a chat opens costs about 600 KB a day and shows nothing offline. With the cursor and the messages stored apart, an app killed between the two writes leaves the cursor ahead of the messages, and those messages are never fetched again.
- Choice: A local database (SQLite) on the phone: messages, gaps and the cursor all in it, the outbox being the rows marked “sending”. The screen reads only the database; everything from the network is written into it first. Each sync page, push and ACK commits in one transaction with the cursor or state it moves; a repeated write lands on its original row by the unique key (sender, message ID).
- Cost: Two copies of the truth, and a client that must repair itself; about 34 MB of text a year, more with media; a migration on every upgrade; the transaction discipline wherever a cursor moves.
- Revisit when: Where images and files go (chapter 8); how deep a new device's first sync goes (chapter 14); the conversation list in the local database (chapter 17); how much the web stores (chapter 26); one local database for every platform, and migrations (chapter 29); servers that keep no history (chapter 41).
- Other answers: Store nothing or only the latest (web, customer-service windows); messages first, then the cursor (one page downloaded again); TDLib's message database; Signal's encrypted SQLite (SQLCipher).

The messages have a home. Next chapter: a 5 MB photo should not squeeze through the same connection as the text messages.