Envelope Index Database

Overview

The Envelope Index is the primary SQLite database used by Apple Mail to store structured metadata about all email messages. It provides fast access to sender information, recipients, subjects, timestamps, mailbox locations, read/flagged status, and conversation threading -- without requiring individual EMLX files to be parsed. For most forensic investigations, the Envelope Index alone provides the majority of actionable email evidence.

On macOS 15 Sequoia, the Envelope Index also contains Apple Intelligence categorization data in the message_global_data table.

File Locations

Where {N} is the Mail version number (typically 10 on macOS 10.15+).

Database Schema

messages Table

The central table containing one row per email message.

CREATE TABLE messages (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER NOT NULL DEFAULT 0,
    global_message_id INTEGER NOT NULL,
    remote_id INTEGER,
    document_id TEXT COLLATE BINARY,
    sender INTEGER,                    -- FK to addresses.ROWID
    subject_prefix TEXT COLLATE BINARY,
    subject INTEGER NOT NULL,          -- FK to subjects.ROWID
    summary INTEGER,                   -- FK to summaries.ROWID
    date_sent INTEGER,                 -- Unix timestamp (seconds)
    date_received INTEGER,             -- Unix timestamp (seconds)
    mailbox INTEGER NOT NULL,          -- FK to mailboxes.ROWID
    remote_mailbox INTEGER,
    flags INTEGER NOT NULL DEFAULT 0,  -- Bitmask (see flags section)
    read INTEGER NOT NULL DEFAULT 0,
    flagged INTEGER NOT NULL DEFAULT 0,
    deleted INTEGER NOT NULL DEFAULT 0,
    size INTEGER NOT NULL DEFAULT 0,   -- Message size in bytes
    conversation_id INTEGER NOT NULL DEFAULT 0,
    date_last_viewed INTEGER,          -- Unix timestamp (seconds)
    list_id_hash INTEGER,
    color TEXT COLLATE BINARY,
    type INTEGER,
    automated_conversation INTEGER DEFAULT 0,
    root_status INTEGER DEFAULT -1,
    is_urgent INTEGER NOT NULL DEFAULT 0  -- Apple Intelligence (macOS 15+)
);

addresses Table

Stores unique email address and display name pairs. Referenced by both messages.sender and recipients.address.

CREATE TABLE addresses (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    address TEXT COLLATE NOCASE NOT NULL,   -- Email address
    comment TEXT COLLATE BINARY NOT NULL,    -- Display name
    UNIQUE(address, comment)
);

recipients Table

Links messages to their recipients with type information.

CREATE TABLE recipients (
    ROWID INTEGER PRIMARY KEY,
    message INTEGER NOT NULL,      -- FK to messages.ROWID
    address INTEGER NOT NULL,      -- FK to addresses.ROWID
    type INTEGER,                  -- 0 = To, 1 = Cc, 2 = Bcc
    position INTEGER,              -- Order within the recipient list
    UNIQUE(message, type, position)
);

Recipient type values:

subjects Table

Stores unique subject lines, deduplicated for space efficiency.

CREATE TABLE subjects (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    subject TEXT COLLATE BINARY NOT NULL,
    UNIQUE(subject)
);

mailboxes Table

Represents email folders/mailboxes.

CREATE TABLE mailboxes (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    url TEXT COLLATE BINARY NOT NULL,       -- e.g., 'imap://user@imap.example.com/INBOX'
    total_count INTEGER NOT NULL DEFAULT 0,
    unread_count INTEGER NOT NULL DEFAULT 0,
    deleted_count INTEGER NOT NULL DEFAULT 0,
    UNIQUE(url)
);

The url column encodes the protocol, account, and folder path. Examples:

• imap://john@imap.gmail.com/INBOX
• imap://john@imap.gmail.com/[Gmail]/Sent Mail
• ews://john@outlook.office365.com/Inbox

attachments Table

Records attachment metadata for each message.

CREATE TABLE attachments (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    message INTEGER NOT NULL REFERENCES messages(ROWID) ON DELETE CASCADE,
    attachment_id TEXT COLLATE BINARY,
    name TEXT COLLATE BINARY,              -- Filename
    UNIQUE(message, attachment_id)
);

conversations Table

Groups related messages into conversation threads.

CREATE TABLE conversations (
    conversation_id INTEGER PRIMARY KEY AUTOINCREMENT,
    flags INTEGER NOT NULL DEFAULT 0,
    sync_key TEXT COLLATE BINARY
);

message_global_data Table (macOS 15+)

Apple Intelligence email categorization data. This table exists only on macOS 15 Sequoia and later.

CREATE TABLE message_global_data (
    ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
    message_id INTEGER,                    -- FK to messages.ROWID
    model_category INTEGER,                -- AI category
    model_subcategory INTEGER,
    model_high_impact INTEGER NOT NULL DEFAULT 0,
    urgent INTEGER,
    generated_summary INTEGER,             -- FK to generated_summaries
    model_analytics TEXT,                  -- JSON with ML confidence scores
    category_model_version INTEGER,
    UNIQUE(message_id)
);

AI category values:

Key Fields for Analysis

Flags Bitmask

The messages.flags column is a bitmask that encodes multiple message states:

-- Decode flags bitmask
SELECT ROWID,
    CASE WHEN flags & 1 THEN 'Yes' ELSE 'No' END AS is_read,
    CASE WHEN flags & 2 THEN 'Yes' ELSE 'No' END AS is_deleted,
    CASE WHEN flags & 4 THEN 'Yes' ELSE 'No' END AS is_answered,
    CASE WHEN flags & 8 THEN 'Yes' ELSE 'No' END AS is_encrypted,
    CASE WHEN flags & 16 THEN 'Yes' ELSE 'No' END AS is_flagged,
    CASE WHEN flags & 64 THEN 'Yes' ELSE 'No' END AS is_draft,
    CASE WHEN flags & 256 THEN 'Yes' ELSE 'No' END AS is_forwarded
FROM messages;

Note that the individual read, flagged, and deleted columns duplicate information from the bitmask for query performance. Both sources should be consistent.

Forensic Queries

-- Full message metadata with sender and subject
SELECT
    m.ROWID,
    datetime(m.date_received, 'unixepoch') AS received_utc,
    datetime(m.date_sent, 'unixepoch') AS sent_utc,
    a.address AS sender_email,
    a.comment AS sender_name,
    s.subject,
    mb.url AS mailbox,
    m.size,
    m.read,
    m.flagged,
    m.conversation_id
FROM messages m
LEFT JOIN addresses a ON m.sender = a.ROWID
LEFT JOIN subjects s ON m.subject = s.ROWID
LEFT JOIN mailboxes mb ON m.mailbox = mb.ROWID
ORDER BY m.date_received DESC;

-- Messages with recipients
SELECT
    m.ROWID,
    s.subject,
    CASE r.type WHEN 0 THEN 'To' WHEN 1 THEN 'Cc' WHEN 2 THEN 'Bcc' END AS recipient_type,
    a.address AS recipient_email,
    a.comment AS recipient_name
FROM messages m
JOIN subjects s ON m.subject = s.ROWID
JOIN recipients r ON r.message = m.ROWID
JOIN addresses a ON r.address = a.ROWID
ORDER BY m.ROWID, r.type, r.position;

-- Apple Intelligence categories (macOS 15+)
SELECT
    m.ROWID,
    s.subject,
    CASE mgd.model_category
        WHEN 0 THEN 'Primary'
        WHEN 1 THEN 'Transactions'
        WHEN 2 THEN 'Updates'
        WHEN 3 THEN 'Promotions'
    END AS ai_category,
    mgd.urgent,
    mgd.model_analytics
FROM messages m
JOIN message_global_data mgd ON mgd.message_id = m.ROWID
JOIN subjects s ON m.subject = s.ROWID
WHERE mgd.model_category IS NOT NULL;

Timestamps

All timestamp columns use standard Unix epoch (seconds since 1970-01-01 00:00:00 UTC).

SELECT datetime(date_received, 'unixepoch') AS received_utc FROM messages;

A value of 0 or NULL indicates the timestamp was not recorded.

Analysis Notes

• Deduplicated tables: The addresses and subjects tables store unique values. Multiple messages from the same sender share a single addresses row. This means counting addresses rows does not give you a message count.
• Mailbox URL parsing: The mailboxes.url column encodes the protocol, account, and folder. Parse it to identify which email account and folder a message belongs to.
• Conversation threading: The conversation_id column links all messages in an email thread. Use this to reconstruct conversation flows.
• Size column: The size column reflects the total message size in bytes, including headers and attachments. This can be useful for identifying large data transfers.
• Apple Intelligence availability: The message_global_data table only exists on macOS 15+. Check for its presence before querying.

Version Differences

Tool Support

References

• Apple Mail Forensics - MailXaminer
• Mac OS X 10.9 Artifacts Location - Forensics Wiki