An iPhone backup hands you the messages as Apple stores them: a SQLite database with fifteen years of accreted schema, no documentation, and real people's histories riding on your interpretation of it. This chapter is the archaeology, and the one classification bug that taught us the difference between sampling a schema and measuring it.
The shape of sms.db
The core is almost friendly: a message table, a chat table, handle for participants, and join tables tying them together. The hostility is in the details. Timestamps count from Apple's own epoch (January 1, 2001) and changed magnitude across iOS versions, so a correct reader handles seconds and nanoseconds both. The text column is sometimes NULL while the real text lives in attributedBody, a serialized NSKeyedArchiver blob you must unpack to read a message that looks missing. Tapbacks are messages that reference other messages. Edits and unsends leave their traces in a summary column. And a class of rows that look like attachments (.pluginPayloadAttachment files) are actually iMessage's internal rich-link metadata: in one real backup, 38% of the attachment rows. Show those to a user and the app looks broken; every reader of this database independently rediscovers that filter.
Deleted, recovered, and honest about which
Messages the user deleted on the phone can survive in the database for a while (iOS keeps a recoverable window), joined through their own table. We surface them, clearly flagged, in their own view, and every export states its policy about them on the document itself, because a reader of the output must never have to guess. What we cannot do, and say so plainly, is resurrect what was already gone before the backup was taken. The precision habits from the integrity chapter start here: the transcript is our extraction of this schema, and the claims ride on the extraction being humble about edge rows (a message with no recorded content prints as exactly that, never as a silent blank).
The lesson: a sample is not a measurement
The bug that earned this chapter its moral: group-chat system events (renames, joins, someone changing the group photo) are encoded in an undocumented enum column. We classified its values by looking at a sample of rows, shipped, and a real archive promptly produced group-photo-change rows our classifier called something else, rendering nonsense. The fix was trivial. The rule it produced was not: when a schema column is undocumented, you census every distinct value across every available real database before you classify, and the classifier handles the values you did not see anyway. A sample tells you what is common. A census tells you what is true. On other people's data, only the second is acceptable, and our reader now treats unknown values as first-class honest states rather than guesses.
WhatsApp: a second civilization
WhatsApp's ChatStorage.sqlite rides along in the same backup and is its own dig site: different schema, different conventions, quoted replies threading by message reference, reactions in their own structures. Two find-outs worth passing on. First, in group chats, some participants exist only as anonymized identifiers whose display names live in a separate database (a contacts directory beside the chat store), so resolving who said something means a careful cross-database read, done with the store opened immutably so a display feature can never write to evidence. Second, name columns in that world cannot be trusted to be text: we guard every one against binary blobs, because real data eventually contains everything. Read receipts exist in the schema; we deliberately do not surface them for WhatsApp until our census standard is met there too. The same rule, applied forward.
Every message this chapter decodes reaches the user through the pipeline the rest of the series builds: obtained, rendered, exported at scale, and provable.