What iMessage Keeps After You Edit, Unsend, React or Delete: Database Evidence from Nine iPhone Databases on iOS 18 to 26

1 Introduction

Apple Messages lets a sender edit a sent iMessage and take it back after sending [2], react to a single message, reply inline to an earlier one, and recover deleted messages for a period. Each action changes what an examination of the Messages database can show. An edited message may carry its earlier wording; an unsent message may carry nothing but a trace that it existed; a deleted message may remain in the file after it has left the screen. Examiners are asked to say which of these states a conversation was in, and how sure they are.

A motivating example. Suppose an examiner must list the inline replies in a conversation. The message table has a column named reply_to_guid, and the name invites the reading that a filled value marks a reply. In one iOS 26.5.2 database we studied, that column holds a guid on 49,981 of 68,136 rows, including messages that are not replies. Replies identified by the thread originator number 1,360, and the two columns agree on only 56 of them (Listing 1). A reading built on the column name would therefore treat 73 percent of the rows as replies.

row  thread_originator_guid  reply_to_guid
m1   (empty)                 <guid-1>
m2   <guid-2>                <guid-2>
m3   <guid-2>                <guid-3> or empty

m1   not a reply, yet reply_to_guid is set
m2   reply; the two columns are equal
m3   reply; the two columns are not equal

iOS 26.5.2 database:
  reply_to_guid set          49,981 of 68,136 rows
  replies (originator set)    1,360
  columns equal                  56 of 1,360 replies

The problem: readings by analogy. The public guidance we found is mostly practitioner-written. It describes the layout seen at one release, for example which column holds the edit history and that the recall time is kept in date_edited [12], and it is easy to extend by analogy to a column name. Whether such a reading holds on iOS 18 and iOS 26, and on more than one database, is an empirical question that a single hands-on test cannot settle.

Approach. We write each storage model as equalities that must hold if the model is right, run them as checks over every instance in the databases, and report every exception. A check that holds on all instances does not prove what a field means, but a check that fails rejects the model for those databases. We ask three research questions about the Messages database of iPhones running iOS 18 and iOS 26.

• RQ1 (Representation). How are edits, unsent parts, reactions, inline replies and Recently Deleted messages represented, and is the representation consistent across the releases we hold?
• RQ2 (Assumed readings). Which commonly assumed fields or intervals do not behave as assumed?
• RQ3 (Recoverability). What can be said about whether removed content is recoverable from the database alone?

Contributions. This report makes four contributions.

• Nine consistency checks, each an equality that a storage model predicts, and their outcome on nine databases; three of the nine fail (Sections 3.1 and 5).
• Three observations that contradict plausible readings: reply_to_guid does not mark replies, some Recently Deleted rows carry a delete_date more than 30 days before the copy (they outlast the 30-day default only if delete_date records the time of deletion, not tested), and reaction removal rows rarely keep their add row, as the exporter documentation also predicts (Sections 5.3 to 5.5).
• Five rules for reading these records in an examination, derived from the checks (Section 6.2).
• Eight read-only parser artifacts that run the checks over a whole database, built on a shared decoder that returns an empty value instead of raising on unreadable input, with a synthetic database generator and unit tests that make the checks repeatable (Sections 3.2 and 3.4).

Scope. All results describe the nine sms.db databases studied. “Nine” counts databases, not people. The files come from nine different phones, subject to the condition stated in Section 7, and the usage behind each is unknown. The two observations we did not find reported elsewhere, on reply_to_guid and on retention, each rest on a single database, the iOS 26.5.2 one. Most checks replicate published descriptions with counts; Section 6.1 separates what replicates from what is new, and Section 7 states the limits.

2 Background and Related Work

This section describes the database structures that record each action, then places the work among practitioner descriptions, open-source parsers and work on validating forensic tools.

2.1 How sms.db records each action

The Messages database is an SQLite file, sms.db, with one row per message in a message table and join tables to chats and attachments. A message can hold several parts, numbered from 0, and edits and unsends apply to a part. Text typed on recent releases is often not in the text column but in attributedBody, an archived attributed string. A binary property list in message_summary_info holds short-coded keys; those relevant here are ec, the edit-history list (part index to a list of versions, each with a time d and archived text t), otr (original text range per part) and rp (retracted parts). The open-source imessage-exporter project documents ec, otr and rp and the order of the version list, original first and current last [8], [9].

A reaction is a message row of its own whose associated_message_type and associated_message_guid point at the target message; we call a row that adds a reaction an add row and a row that removes one a removal row. An inline reply names the message it answers, its originator, in thread_originator_guid. Recently Deleted messages are tracked in chat_recoverable_message_join and recoverable_message_part; the feature was introduced with iOS 16 [12], and the oldest database we hold is iOS 18.1.1. Figure 1 maps each action to its storage and to the outcome of the check applied to it.

2.2 Practitioner and vendor descriptions

The closest prior work is practitioner writing on the iOS 16 changes (Table 1). A vendor blog post describes the same fields we examine: for an unsent message the text and attributedBody values are empty and the change time is in date_edited; for an edited message earlier versions are in the property list under ec; Recently Deleted messages are tracked by chat_recoverable_message_join and persist for 30 days by default [12]. The post reports no counts over several databases and does not examine the reply columns. A vendor post on a commercial tool's handling of iOS 16 sent messages [4], an article on recognising deleted messages from gaps in the row identifiers [10] and an early forum thread on the iOS 16 sms.db changes [5] cover related ground. We read [10] in full; [4] and [5] we confirmed only from their titles and search summaries, and no result here relies on them.

2.3 Open-source parsers

imessage-exporter reads the same database and documents its fields from the source of the format [8]. We used it for field meaning only and copied no code; its licence is GPL-3.0. iLEAPP, with its Android counterpart ALEAPP, is a Python parser suite for forensic extractions [3].

2.4 Validating forensic tools

The case for testing forensic tools and their assumptions, rather than trusting them, is made in the literature on tool reliability [7] and in the broader research agenda of the field [6]. Our checks are a small instance of that practice applied to a data format.

Position of this work. This work differs from the sources in Table 1 in method rather than in subject. We restate the published descriptions, and the readings that column names suggest, as checks; count the instances that could break each; and report the exceptions. Where a check holds, the result is a replication with counts; where it fails, it is an observation on the databases we hold.

3 Method

This section defines the checks, the parser artifacts that run them, the independent recount of their output and the synthetic fixtures.

3.1 Storage models and falsifiable checks

For each action we wrote down a storage model from the format documentation [8], [9] and from inspection of the databases, and derived equalities that must hold if the model is right. For example, the time of the last item in an edit-history list should equal date_edited. We counted the messages for which each equality holds and listed every exception. Table 2 lists the nine checks, C1 to C9. C6 and C7 state plausible readings rather than documented ones: that a removal row leaves its add row, and that reply_to_guid marks replies. C9 tests the 30-day default retention that a vendor post gives for Recently Deleted messages [12]. A check that holds on all instances is only as strong as the number of instances that could have broken it.

3.2 Parser artifacts

The checks run inside eight read-only artifacts (Table 3), modules that each present one kind of record as a table in the tool's report. They share a helper that decodes the archived strings, the binary property lists and the keyed archives. Every decoder returns an empty value on input it cannot read and never raises, so one damaged blob costs one cell and not the artifact. Timestamps are Cocoa times: nanoseconds since 2001-01-01 UTC above a threshold of 10¹⁵, seconds below it, converted to UTC. Values the databases store but we cannot interpret are reported as stored.

3.3 Independent recount

Row counts of every artifact on every re-run database were recomputed with separate code: plain SQL on sms.db with the write-ahead log applied [11], plistlib for the property lists, and a small keyed-archive walker for the link-preview payloads. The first version of the walker was a heuristic and disagreed with the artifact by 16, 4 and 26 rows on three databases; a proper archive reader resolved each difference in favour of the artifact. The recount is therefore a check on both sides, not only on the artifact.

3.4 Synthetic fixtures

A generator writes a fabricated sms.db with reserved documentation phone numbers, example domains, fixed sentences and fixed dates. Twelve unit tests and eight synthetic test cases, one per artifact with recorded baselines, run against it. No fixture contains data from a real database.

4 Evidence and Setup

This section describes the databases, the tools and what counts as agreement.

4.1 Databases

The evidence is nine sms.db files from full file system extractions of iPhones in the Lab's research collection, opened read-only and labelled D1 to D9 by release (Table 4): iOS 18.1.1 (one), iOS 26.2.1 (one), iOS 26.3.1 (three), and iOS 26.5.2, 26.6, 26.6.1 and 26.6.2 (one each). Their usage, language, contacts and message volume are unknown and uneven, as the row counts show.

4.2 Tools and versions

The artifacts were run with the parser suite's command-line tool in zip mode; the code base they were built on and its continuous-integration result are given in Artifact Availability [1]. For field meaning, the artifacts' notes quote pinned permalinks to the imessage-exporter source at commit 4d90fc8d [9].

4.3 What counts as agreement

We did not observe the actions that produced these records, so the stored data are the reference. Agreement takes two forms: the equalities of Section 3.1, which a correct model must satisfy, and the independent recount of row counts, which shows that the parser reports what the file contains. Eight of the nine databases, D2 to D9, were re-run and recounted with the final version of the artifacts. The iOS 18.1.1 database (D1) was used in an earlier run of the same artifacts; its counts, and the figures in the artifact notes derived from it, were not recounted.

5 Results

The edit-history and unsend models held, with explained exceptions, and three plausible readings failed. Table 5 answers each research question and Table 6 gives the outcome of every check; the subsections give the evidence, one action at a time.

5.1 Edit history

The edit-history layout matched the published description on every instance we hold [8], [9]. The nine databases hold 466 edited messages, each with one ec entry. Of these lists, 452 hold two versions and 14 hold three, so a list is not limited to a single edit, and none holds more than three (Figure 2). On the 463 messages edited at part 0, the time of the first stored version equals the message date (C1). The time of the last stored version equals date_edited on all 466, to within 2 ms (C3), and the last version's text equals the current text in attributedBody on 463 of 466 (C2).

The three exceptions to C2 are edits to a part other than 0 of a message with several parts. There attributedBody holds the whole message and not the one part, so the exceptions are consistent with the model once part structure is taken into account. We read this result as a replication with counts, not as a new finding.

5.2 Unsent messages

An unsent part keeps its row and loses its wording. In seven of the nine databases, 184 messages had a retracted-part entry (rp) covering 192 parts. All 184 had an empty text (C4), and none had attributedBody unless another part of the message was kept. The only trace of the removed wording in these columns is the stored length of the original text range (otr, value le), which gives a length and not the text.

For these 184 messages, date_edited lay between 2.8 and 118.7 s after the message date. This is consistent with a brief unsend window, which Apple's user guide describes for the feature [2]. We could not retrieve the body of that page; search summaries of it give two minutes, which would contain the observed maximum of 118.7 s, but we have not checked the interval against Apple's own text. date_retracted was 0 on every row of all nine databases, so on these releases the rp entry, not the retraction column, marks an unsent part. This agrees with the vendor observation that the column does not appear to be used [12].

5.3 Reactions

The documented mapping of reaction names was not contradicted. Reaction names (Loved, Liked, Disliked, Laughed at, Emphasized, Questioned) follow the numeric associated_message_type mapping documented in imessage-exporter [9], and we cross-checked it against the system-written text of the same rows (C5). Of 29,574 rows of types 2000 to 2005, 28,076 (94.9 percent) start with the verb that matches the type, none starts with a verb that belongs to another type, and 1,498 start with something else, such as empty text or another language. The cross-check cannot establish meaning for those 1,498 rows.

A removal row rarely has its add row (C6). Stored types 3000 to 3007 are read as the removal of the reaction numbered 1000 lower, as documented in the same project [9]. Of 76 removal rows, 9 have an add row of the matching type for the same target still in the table; for the other 67 the add row is not in the database. The documented behaviour is that the add row is removed when a reaction is removed, so a missing add row is the common state and says nothing about whether a reaction was ever added.

5.4 Inline replies

reply_to_guid does not mark replies on iOS 26.5.2 (C7). In the iOS 26.5.2 database (D6) the column holds a 36-character guid on 49,981 of 68,136 rows (73.4 percent), including messages that are not replies, and it equals thread_originator_guid in only 56 of the 1,360 replies there (4.1 percent; Figure 3). The artifact therefore identifies replies by thread_originator_guid. We did not determine what reply_to_guid does record.

Across the nine databases there are 6,035 such replies, and for 6,017 the originator row was still in the database. The third number of thread_originator_part equals the length of the originator's text in 4,913 of those 6,017 (81.6 percent), so we report that field as stored and do not interpret it.

5.5 Recently Deleted

Recently Deleted rows keep their wording, and some have a delete_date more than 30 days before the copy. Five of the nine databases hold 204 rows in chat_recoverable_message_join. The message rows stay in the message table with their wording in attributedBody, and none of the 204 has a row in chat_message_join, which separates them from messages shown in a conversation (C8).

In D6 the earliest and latest delete_date are 60 days apart, so, if delete_date records the time of deletion (not tested), the oldest of those rows was at least 60 days old when the database was copied, and the rows cannot be assumed to fall within the 30 days that a vendor post gives as the default retention (C9) [12]. Two databases hold 11 part rows in recoverable_message_part, where the rest of the message is still in its conversation; in D6 their delete_date values are one to three years before the extraction. The database therefore retains rows beyond the interval the feature advertises. Whether the operating system would still offer them to the user, we did not test. A message removed from Recently Deleted leaves no row, so an empty result is not evidence that nothing was deleted.

5.6 Link previews and app messages

The artifacts decoded 5,413 link previews and 447 app messages, the latter in seven of the nine databases, from keyed archives. The title and summary of a preview come from the linked page and were stored by Messages; they are not text typed by a person, and a preview does not show that the link was opened. These two artifacts carry no consistency check beyond row counts.

5.7 Independent recount

All 64 counts, eight artifacts on each of the eight re-run databases, equal the independent recount. The artifacts also passed the project's own checks on output quality; the first pass listed 45 distinct findings of empty, constant or identical columns (for example no named group chat, a single iMessage line, or a link that is also the message text), and each is now named in the artifact notes. The recount verifies row counts only: the consistency statistics in Table 6 were computed during artifact development and were not each re-derived by a second method.

6 Discussion

The results matter less for the layouts they confirm than for the readings they rule out. This section separates the two, states what they mean for an examination, and draws one lesson for parser testing.

6.1 What replicates and what is new

The edit-history layout, the use of rp for unsent parts and the Recently Deleted tables were already described [8], [9], [12]; checks C1 to C5 and C8 replicate those descriptions with counts on more databases. The observations we did not find reported elsewhere are the failures of C7 and C9 and the three exceptions to C2 explained by multi-part messages. C6 fails as the exporter documentation predicts, since the add row is removed with the reaction [9]; our contribution there is the count. The new observations are warnings against assuming the opposite, not measured prevalences. The artifacts and tests are engineering that makes the checks repeatable.

6.2 Consequences for examination

• An edit-history list is a record of what Messages kept, and the number of versions may not equal the number of edits a person made. We observed at most three versions.
• An unsent message is visible as a row with no text and a retracted-part entry; its stored original length is the only trace of the wording in these columns.
• A reaction removal without an add row is expected. The absence of an add row should not be reported as evidence that no reaction occurred.
• Replies should be identified by the thread originator, not by reply_to_guid, on the releases we examined.
• A Recently Deleted row indicates a deletion consistent with the stored time; it does not show who deleted the message, and it does not imply the row is recent. An empty table is not evidence of no deletion.

6.3 Testing a parser against its own model is weak

Tool-testing literature argues for tests that expose assumptions [7]. Our equalities are cheap to state and to run, and three of the nine failed. The recount also exposed a defect in the first version of our own recount walker. Together these suggest that a parser's own tests, written from the same model as the parser, are weak evidence against a wrong model.

7 Threats to Validity

Internal validity. Counts were produced by the parser under study and, for row counts, re-derived with separate code; agreement shows that the parser reports what the file holds, not that the model matches what the operating system meant. The consistency statistics were not independently re-derived. The D1 (iOS 18.1.1) counts were not recounted in the final run.

Construct validity. A row is a record the device stored, not proof of a user action, and it does not show who edited, unsent, reacted or deleted, or from which device. Times are as stored and converted to UTC. The 73 percent for reply_to_guid and the 60-day span are single-database statistics and depend on how the device's user used Messages.

External validity. The sample is nine databases of unknown usage, mostly from one family of releases (iOS 26). We have no iOS 16 or 17 data, none from iPad or Mac, and none acquired by backup or logical methods; results are stated per class and are not generalised. We did not record which extraction each file came from. A comparison of hashed device identifiers found that the full file system extractions of the Lab’s collection that match the nine releases (one each for six releases, six for iOS 26.3.1) are all different phones, so the nine files come from nine phones provided that they were taken from these extractions.

Conclusion validity. No statistical inference is made; percentages describe the sample only. A model that survives every check is not proven; a failed check rejects the model for these databases.

Limitations. From the stored records alone we cannot say how a given action maps to a given row, how many edits a person made, whether Recently Deleted rows are still offered to the user, or what the operating system does on a synced second device. Our confidence is therefore moderate for the edit-history and unsend layouts, and low for retention and for how often each feature is used, because nine databases of unknown usage are not a sample.

Reproducibility. The fixtures are synthetic and released with the code. Anyone can run the released artifacts on a comparable sms.db or on the generated database; the real-data results can be checked only with comparable databases.

8 Conclusion and Future Work

Column names and single-release descriptions invite readings of the Messages database that may not hold. Stated as checks and run over nine iPhone sms.db files, the edit-history and unsend layouts behave as published, with exceptions we can explain, while one plausible reading does not hold (reply_to_guid is not a reply marker) and another fails if delete_date records the time of deletion, which was not tested: Recently Deleted rows with a delete_date more than 30 days before the copy outlast the 30-day default. A third, that a reaction removal leaves its add row behind, fails as the exporter documentation predicts. Stating a storage model as equalities and counting the exceptions is a test that any parser of this database can run, open-source parsers included. What remains open is whether the two readings that failed on one database fail more widely, and how the earlier releases iOS 16 and 17 store these actions.

Future work, in order of importance: (1) databases from iOS 16 and 17, and from iPad and Mac, to test the claims on earlier releases and other devices; (2) a test of C7 and C9 on more databases, since each now rests on one; (3) a larger and better described corpus, so that the fractions in Figure 3 can be reported with an interval and a stated population.