Call Logs, SMS, and Messaging App Artifacts

Android stores call history in a SQLite database accessed through the CallLog.Calls content provider. The underlying file is typically at /data/data/com.android.providers.contacts/databases/calllog.db, though manufacturers sometimes relocate it. The calls table is the forensically relevant table: each row represents one call event with columns for the remote number, the normalized number (E.164 format where available), call type, duration in seconds, and a date column containing a Unix epoch timestamp in milliseconds. The type column uses integer codes: 1 for incoming, 2 for outgoing, 3 for missed, 4 for voicemail, 5 for rejected. The geocoded_location column, present in Android 6 and later, records the carrier's best guess at the geographic region associated with the calling number.

iOS stores call history in a separate SQLite file: /private/var/mobile/Library/CallHistoryDB/CallHistory.storedata. Despite the .storedata extension, this is a standard SQLite database used by Core Data. The ZCALLRECORD table holds individual call entries. Key columns are ZADDRESS (the remote number), ZDURATION (floating point seconds), ZDATE (a Core Data timestamp, counted in seconds from 1 January 2001, not 1970), ZDIRECTION (0 for incoming, 1 for outgoing), and ZANSWERED (boolean). The distinct epoch origin means iOS timestamps must be converted differently from Android ones: add 978307200 seconds to convert from Core Data epoch to Unix epoch.

Both platforms also log FaceTime and VoIP calls within the same tables or in closely adjacent ones. On iOS, FaceTime calls appear in ZCALLRECORD with a ZSERVICE_UUID column identifying the call type. On Android, third-party dialers may write to a separate database in their own app container rather than the system CallLog. A complete call log analysis must check both the system call log and any installed third-party dialer application.

On Android, SMS and MMS messages are managed by the Telephony content provider with the underlying database typically at /data/data/com.android.providers.telephony/databases/mmssms.db. The sms table holds individual SMS messages with columns for address (the remote number), body (message text), date (Unix epoch milliseconds), type (1=received, 2=sent, 3=draft, 4=outbox, 5=failed, 6=queued), and read (0 or 1). MMS messages, including group messages and messages with attachments, are stored across three related tables: pdu (the message envelope), part (individual MIME parts such as text, image, or audio), and addr (addressing information for each recipient and sender).

iOS consolidates both SMS and iMessage traffic into a single database at /private/var/mobile/Library/SMS/sms.db. The message table is the primary forensic target. The service column distinguishes the message type: 'SMS' for traditional SMS, 'iMessage' for Apple's end-to-end encrypted messaging protocol. The is_from_me column (0 or 1) identifies direction. The date column uses the Core Data epoch (seconds from 1 January 2001). The text column holds the message body for SMS; iMessage content may also include attachments referenced through the attachment table.

The handle table in sms.db maps a numeric ID to each contact identifier (a phone number or Apple ID email address). The chat table represents individual conversations or group threads. The chat_message_join table is the many-to-many join between chats and messages. Reconstructing a conversation thread requires joining message, chat_message_join, chat, and handle: a query that forensic tools execute automatically, but that an examiner should understand when verifying tool output or writing custom queries.

WhatsApp is the most widely used messaging application globally, and its database schema is well-documented through community reverse engineering and forensic research. On Android, the primary message database is at /data/data/com.whatsapp/databases/msgstore.db. A second database, wa.db, stores the WhatsApp contact list. WhatsApp also maintains a media folder at /data/data/com.whatsapp/files/Media/ where received images, audio, and video are stored, though only metadata (not file content) appears in msgstore.db itself.

The messages table in msgstore.db (renamed to message in more recent schema versions) contains the core message records. Key columns are key_remote_jid (the WhatsApp contact or group identifier, formatted as phone-number@s.whatsapp.net or group-id@g.us), key_from_me (0=received, 1=sent), timestamp (Unix epoch seconds), data (message text for text messages), and status (integer encoding delivery state: 0=error, 1=pending, 4=sent to server, 5=delivered, 13=read). The media_mime_type and media_name columns describe attached files. Group messages are identified by the presence of a remote_resource column naming the individual sender within the group.

WhatsApp schema changes with application updates. A database extracted from WhatsApp version 2.20 may differ structurally from one extracted from version 2.24. Forensic tools maintain schema mappings for known versions, but an examiner working manually should check the sqlite_master table for the actual column names before querying. WhatsApp on iOS stores its equivalent data in the app container under Library/Application Support/WhatsApp/ChatStorage.sqlite, with a different but analogous schema.

Signal uses SQLCipher to encrypt its message database. On Android, the encrypted database is at /data/data/org.thoughtcrime.securesms/databases/signal.db. On iOS, it is within the app container. The encryption key is derived from the device's Secret key material. Without the correct decryption key, the database file is cryptographically opaque: there is no practical way to read it through brute force for a properly chosen key.

Decryption is possible in several scenarios. On Android, a physical acquisition of a rooted device can extract the key material from the app's shared preferences file (secret_preferences.xml), which stores the SQLCipher passphrase in plaintext after the key derivation step. Some forensic tools can automate this process against a full physical image. On iOS with a known passcode, forensic tools that can perform a full file system acquisition may be able to extract the database in decrypted form if the keychain entry holding the encryption key is accessible. If none of these routes are available, Signal's contents are effectively inaccessible from the device alone, and investigators may seek the data from Signal's servers or from cloud backups, where Signal's end-to-end encryption may still apply.

When device-side decryption is blocked, cloud sources become relevant. WhatsApp offers Google Drive backups on Android and iCloud backups on iOS that are not end-to-end encrypted by default (though an optional encrypted backup feature exists). Law enforcement agencies in the US, UK, and India can seek these backup copies through legal process served on the cloud provider. The legal framework governing such requests differs: in the US, the Stored Communications Act (18 U.S.C. § 2703) applies; in the UK, the Investigatory Powers Act 2016; in India, the Information Technology Act 2000 and relevant provisions of the Bharatiya Nagarik Suraksha Sanhita 2023.

SQLite does not overwrite data immediately when a row is deleted. Instead, the storage page containing the deleted row is marked as free and placed on the database's free-list. The data on that page remains physically present until SQLite reuses the page for new writes or until a VACUUM command is executed, which compacts the database and removes free pages. In mobile forensics, this means that deleted SMS messages, call log entries, and messaging app records may be recoverable from the free pages of the database file.

Recovery requires a physical acquisition or a full file system acquisition that captures the raw SQLite file, not just an API-level export. Logical acquisitions through content providers or iTunes backup interfaces return only current live records. Tools such as Cellebrite UFED, Oxygen Forensic Detective, and the open-source SQLite Deleted Records Parser can scan database files for residual data in free pages and unallocated space. The recovered fragments often lack column headers, requiring the examiner to infer field boundaries from the known table schema and the SQLite wire format.

Several factors affect recovery success. A device that has sent and received many messages after the deletion is less likely to retain recoverable data because new writes progressively overwrite free pages. A VACUUM operation or a SQLite WAL checkpoint eliminates free page data entirely. Devices that use full-disk encryption at rest (which includes all modern iOS devices and Android devices with file-based encryption enabled) provide no additional barrier to deleted record recovery once the physical image has been decrypted, but the decryption step itself remains a prerequisite.

The admissibility of recovered deleted records requires careful documentation. The examiner must be able to demonstrate that the acquisition preserved the raw database file without modification, that the tool used to parse free pages is validated, and that the recovered fragments are presented with appropriate confidence qualifiers indicating that they are residual data rather than current records. Courts in the US (United States v. Bayer, among others) and the UK have considered recovered deleted digital records, and the evidentiary standard in each jurisdiction applies equally to mobile database artifacts.

A communication timeline is the central analytical product of messaging artifact analysis. It sequences all contacts, whether calls, SMS messages, or app messages, in chronological order, attributing each to a participant and a channel. The timeline reveals patterns that individual artifact views obscure: a suspect who communicated exclusively through Signal except for one unguarded SMS, call clusters preceding a significant event, or a gap in communication that could indicate device power-off or airplane mode.

Building the timeline accurately requires resolving three technical challenges. First, timestamps from different databases must be converted to a common reference frame. Android call logs use milliseconds from Unix epoch; iOS call history uses seconds from Core Data epoch; WhatsApp uses seconds from Unix epoch; some app versions use nanoseconds. Converting all to UTC before sorting is mandatory. Second, contact identifiers must be correlated across databases: the same individual may appear as a phone number in the call log, an Apple ID in iMessage, a WhatsApp JID in msgstore.db, and a display name in the device contacts database. Third, message direction (sent vs. received) must be correctly interpreted from the integer flags specific to each database.

Forensic tools such as Cellebrite Physical Analyzer, Magnet AXIOM, and Oxygen Forensic Detective automate much of this merging. An examiner using these tools should still verify the underlying SQL queries or parsed field mappings for any messages that are pivotal to the investigation, because tool errors in schema mapping are a known failure mode, particularly for newer app versions whose schemas have not yet been added to the tool's parser database. Presenting timeline evidence in court requires the examiner to explain the source database, the extraction method, and the timestamp conversion applied to each entry.