WhatsApp Desktop — Chat Database

Overview

ChatStorage.sqlite is the primary forensic artifact for WhatsApp Desktop on macOS. It is a standard, unencrypted SQLite database using Apple's Core Data persistence framework. All entity table names carry the ZWA prefix (e.g., ZWAMESSAGE, ZWACHATSESSION). The database is located at:

~/Library/Group Containers/group.net.whatsapp.WhatsApp.shared/ChatStorage.sqlite

The database is accessible to any process running as the user — no Full Disk Access, no root privileges, and no decryption keys are required.

Always collect the WAL and SHM companion files alongside the main database:

ChatStorage.sqlite
ChatStorage.sqlite-wal
ChatStorage.sqlite-shm

Timestamp Format

All timestamp columns use the Apple Core Data epoch: floating-point seconds since January 1, 2001, 00:00:00 UTC.

unix_timestamp = core_data_timestamp + 978307200

In SQLite queries:

datetime(ZMESSAGEDATE + 978307200, 'unixepoch') AS message_time

Zero values in timestamp columns indicate the timestamp was not populated. The ZPIN column on ZWACHATSESSION stores a non-zero value when the session is pinned (the value itself is a timestamp of when pinning occurred, not a boolean).

Table: ZWAMESSAGE

The central table. Every message in every conversation is stored here — text messages, media messages, system events, and missed call notifications.

ZMESSAGETYPE Values

Values 9, 11, 12, and 14 have been observed but are not officially documented. The macfor collector returns unknown(N) for unrecognized values.

ZMESSAGESTATUS Values

For incoming messages, ZMESSAGESTATUS typically reflects how the message was handled locally. For outgoing group messages, it reflects aggregate delivery state — ZWAMESSAGEINFO provides per-recipient detail.

Table: ZWACHATSESSION

One row per conversation thread. Covers 1:1 conversations, group chats, broadcast lists, and the status feed.

ZSESSIONTYPE Values

Table: ZWAMEDIAITEM

Stores metadata for all media attachments. One row per media item, linked to its parent message via ZMESSAGE.

ZMEDIALOCALPATH values are relative to the Group Container directory. To construct the full path, prepend ~/Library/Group Containers/group.net.whatsapp.WhatsApp.shared/.

Location messages (type 5) populate ZLATITUDE and ZLONGITUDE instead of file-related fields.

Table: ZWAGROUPINFO

Group-level metadata. One row per group chat, linked from ZWACHATSESSION.ZGROUPINFO.

Table: ZWAGROUPMEMBER

Group membership. One row per member per group. Group chats are identified by ZCHATSESSION.

The ZISADMIN value 2 (superadmin) typically indicates the original group creator.

Table: ZWAMESSAGEINFO

Per-recipient delivery and read receipts for outgoing messages. Only populated for messages sent by the account owner. In group conversations, there is one row per recipient.

A non-null ZREADDATE is strong evidence that the recipient read the specific message on their device.

Table: ZWAPROFILEPUSHNAME

Maps JIDs to the display name the contact has set for themselves (their "push name"). This is the name the contact chose in their WhatsApp profile, distinct from the name stored in the local address book.

This table is useful when the contact is not in the local address book — the push name provides an identity label from the contact themselves.

Table: Z_PRIMARYKEY

Core Data entity registry. The Z_MAX column stores the highest primary key value ever assigned for each entity type, providing a historical record count even after deletions.

Query example: SELECT Z_NAME, Z_MAX FROM Z_PRIMARYKEY ORDER BY Z_MAX DESC shows all entity types and their maximum historical counts.

Key SQL Queries

All messages in a conversation

-- List all messages in a specific conversation, newest first.
-- Replace the JID placeholder with the target phone number.
SELECT
    m.Z_PK          AS message_id,
    datetime(m.ZMESSAGEDATE + 978307200, 'unixepoch') AS sent_at,
    m.ZFROMJID      AS sender_jid,
    m.ZISFROMME     AS is_from_me,
    m.ZMESSAGETYPE  AS msg_type,
    m.ZMESSAGESTATUS AS status,
    m.ZTEXT         AS body,
    m.ZSTANZAID     AS stanza_id
FROM ZWAMESSAGE m
JOIN ZWACHATSESSION cs ON m.ZCHATSESSION = cs.Z_PK
WHERE cs.ZCONTACTJID = '14155551234@s.whatsapp.net'
ORDER BY m.ZMESSAGEDATE DESC;

All conversations with last message date

-- List all conversation threads, ordered by most recent activity.
SELECT
    cs.ZCONTACTJID                                             AS chat_jid,
    cs.ZPARTNERNAME                                            AS name,
    CASE cs.ZSESSIONTYPE
        WHEN 0 THEN 'private'
        WHEN 1 THEN 'group'
        WHEN 2 THEN 'broadcast'
        WHEN 3 THEN 'status'
        ELSE 'unknown'
    END                                                        AS session_type,
    cs.ZMESSAGECOUNTER                                         AS total_messages,
    cs.ZUNREADCOUNT                                            AS unread,
    datetime(cs.ZLASTMESSAGEDATE + 978307200, 'unixepoch')    AS last_message_at,
    cs.ZARCHIVED                                               AS is_archived
FROM ZWACHATSESSION cs
ORDER BY cs.ZLASTMESSAGEDATE DESC;

Outgoing messages with full delivery/read confirmation

-- Find outgoing messages where a specific recipient confirmed they read the message.
SELECT
    m.Z_PK AS message_id,
    datetime(m.ZMESSAGEDATE + 978307200, 'unixepoch') AS sent_at,
    cs.ZCONTACTJID AS chat_jid,
    m.ZTEXT AS body,
    mi.ZRECEIPTJID AS read_by,
    datetime(mi.ZREADDATE + 978307200, 'unixepoch') AS read_at,
    datetime(mi.ZRECEIVEDDATE + 978307200, 'unixepoch') AS received_at
FROM ZWAMESSAGE m
JOIN ZWACHATSESSION cs ON m.ZCHATSESSION = cs.Z_PK
JOIN ZWAMESSAGEINFO mi ON mi.ZMESSAGE = m.Z_PK
WHERE m.ZISFROMME = 1
  AND mi.ZREADDATE IS NOT NULL
  AND mi.ZREADDATE > 0
ORDER BY m.ZMESSAGEDATE DESC;

Messages with media attachments (geolocation)

-- Find all location messages with latitude/longitude coordinates.
SELECT
    m.Z_PK AS message_id,
    datetime(m.ZMESSAGEDATE + 978307200, 'unixepoch') AS sent_at,
    cs.ZCONTACTJID AS chat_jid,
    m.ZFROMJID AS sender_jid,
    med.ZLATITUDE AS latitude,
    med.ZLONGITUDE AS longitude,
    m.ZTEXT AS location_label
FROM ZWAMESSAGE m
JOIN ZWACHATSESSION cs ON m.ZCHATSESSION = cs.Z_PK
JOIN ZWAMEDIAITEM med ON med.ZMESSAGE = m.Z_PK
WHERE m.ZMESSAGETYPE = 5
  AND med.ZLATITUDE IS NOT NULL
ORDER BY m.ZMESSAGEDATE DESC;

Forwarded messages

-- Find all forwarded messages, which may indicate information dissemination.
SELECT
    m.Z_PK AS message_id,
    datetime(m.ZMESSAGEDATE + 978307200, 'unixepoch') AS sent_at,
    cs.ZCONTACTJID AS chat_jid,
    m.ZFROMJID AS sender_jid,
    m.ZISFROMME AS is_from_me,
    m.ZFORWARDCOUNT AS forward_count,
    m.ZTEXT AS body
FROM ZWAMESSAGE m
JOIN ZWACHATSESSION cs ON m.ZCHATSESSION = cs.Z_PK
WHERE m.ZISFORWARDED = 1
ORDER BY m.ZMESSAGEDATE DESC;

Group membership and admin roles

-- List all members of a group chat with their admin roles.
SELECT
    cs.ZPARTNERNAME AS group_name,
    cs.ZCONTACTJID AS group_jid,
    gi.ZCREATORJID AS creator_jid,
    datetime(gi.ZCREATIONDATE + 978307200, 'unixepoch') AS group_created_at,
    gm.ZMEMBERJID AS member_jid,
    CASE gm.ZISADMIN
        WHEN 0 THEN 'member'
        WHEN 1 THEN 'admin'
        WHEN 2 THEN 'superadmin'
        ELSE 'unknown'
    END AS role,
    gm.ZCONTACTNAME AS display_name
FROM ZWAGROUPMEMBER gm
JOIN ZWACHATSESSION cs ON gm.ZCHATSESSION = cs.Z_PK
LEFT JOIN ZWAGROUPINFO gi ON cs.ZGROUPINFO = gi.Z_PK
WHERE cs.ZCONTACTJID = '120363123456789012@g.us'
ORDER BY gm.ZISADMIN DESC, gm.ZMEMBERJID;

Starred messages

-- Retrieve all starred (bookmarked) messages across all conversations.
SELECT
    m.Z_PK AS message_id,
    datetime(m.ZMESSAGEDATE + 978307200, 'unixepoch') AS sent_at,
    cs.ZCONTACTJID AS chat_jid,
    cs.ZPARTNERNAME AS chat_name,
    m.ZFROMJID AS sender_jid,
    m.ZMESSAGETYPE AS msg_type,
    m.ZTEXT AS body
FROM ZWAMESSAGE m
JOIN ZWACHATSESSION cs ON m.ZCHATSESSION = cs.Z_PK
WHERE m.ZSTARRED = 1
ORDER BY m.ZMESSAGEDATE DESC;

Historical record count from Core Data

-- Show entity types and their maximum historical primary key values.
-- Z_MAX is a lower bound on total records ever created.
SELECT
    Z_NAME AS entity,
    Z_MAX AS max_pk_ever_assigned
FROM Z_PRIMARYKEY
ORDER BY Z_MAX DESC;

Media files by MIME type

-- List all media attachments by MIME type with local file paths.
SELECT
    med.ZFILEMIMETYPE AS mime_type,
    count(*) AS count,
    sum(med.ZFILESIZE) AS total_bytes
FROM ZWAMEDIAITEM med
WHERE med.ZFILEMIMETYPE IS NOT NULL
GROUP BY med.ZFILEMIMETYPE
ORDER BY count DESC;

macfor Collection

macfor collects the following artifacts from ChatStorage.sqlite via the messaging.whatsapp plugin:

The raw ChatStorage.sqlite, ChatStorage.sqlite-wal, and ChatStorage.sqlite-shm files are also preserved in the evidence container.

Collection command:

macfor-pro collect --plugin messaging.whatsapp --output ./evidence.zip

Skip message bodies (metadata-only mode):

macfor-pro collect --plugin messaging.whatsapp --option metadata_only=true --output ./evidence.zip

References

• WhatsApp Desktop — Overview
• iCloud Backup
• Deleted Message Recovery
• Apple Core Data Programming Guide