AddressBook Database

Overview

The AddressBook-v22.abcddb database is the primary storage for contact information on macOS. It uses Apple's Core Data framework with Z-prefixed tables and columns. The main contact data lives in the ZABCDRECORD table (approximately 100 columns per contact), with phone numbers, email addresses, postal addresses, social profiles, URLs, relationships, and notes stored in separate multi-value tables linked by foreign key.

The v22 schema has been stable since macOS 10.7, making it one of the most predictable forensic artifacts to parse. Entity type 22 represents contacts and entity type 19 represents groups.

File Locations

Database Schema

ZABCDRECORD Table (Contact Records)

The primary contact record table. Filter by Z_ENT = 22 for contacts (vs groups or containers).

CREATE TABLE ZABCDRECORD (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,                          -- 22 = Contact, 19 = Group, 25 = Container
    Z_OPT INTEGER,                          -- Optimistic locking counter

    -- Identity
    ZFIRSTNAME VARCHAR,
    ZLASTNAME VARCHAR,
    ZMIDDLENAME VARCHAR,
    ZMAIDENNAME VARCHAR,
    ZNICKNAME VARCHAR,
    ZFIRSTNAMEPHONETIC VARCHAR,
    ZLASTNAMEPHONETIC VARCHAR,
    ZMIDDLENAMEPHONETIC VARCHAR,

    -- Organization
    ZORGANIZATION VARCHAR,
    ZDEPARTMENT VARCHAR,
    ZJOBTITLE VARCHAR,

    -- Timestamps (Core Data seconds since 2001-01-01)
    ZCREATIONDATE TIMESTAMP,
    ZMODIFICATIONDATE TIMESTAMP,
    ZLASTSYNCDATE TIMESTAMP,
    ZBIRTHDAY TIMESTAMP,

    -- Unique Identifiers
    ZUNIQUEID VARCHAR,                      -- Primary unique ID (stable across syncs)
    ZEXTERNALUUID VARCHAR,                  -- External system UUID
    ZEXTERNALIDENTIFIER VARCHAR,            -- External system ID
    ZEXTERNALREPRESENTATION BLOB,           -- Serialized external data

    -- Sync Metadata
    ZSYNCSTATUS INTEGER,
    ZSYNCCOUNT INTEGER,
    ZSYNCANCHOR INTEGER,

    -- Image Data
    ZIMAGEDATA BLOB,                        -- Inline thumbnail
    ZIMAGETYPE INTEGER,                     -- Image format
    ZIMAGEHASH VARCHAR,                     -- Hash for _EXTERNAL_DATA photo
    ZIMAGEREFERENCE VARCHAR,                -- iCloud image URL
    ZCROPRECT VARCHAR,                      -- Image crop rectangle

    -- Display and Linking
    ZDISPLAYFLAGS INTEGER,
    ZPREFERREDFORLINKNAME INTEGER,          -- Preferred for unified display name
    ZPREFERREDFORLINKPHOTO INTEGER,         -- Preferred for unified photo
    ZPERSONLINK INTEGER,                    -- FK to linked contact
    ZLINKID INTEGER,                        -- Link group identifier

    -- Source Tracking
    ZCONTAINER INTEGER,                     -- FK to container (account source)
    ZACCOUNTIDENTIFIER VARCHAR,

    -- Display
    ZSORTINGFIRSTNAME VARCHAR,
    ZSORTINGLASTNAME VARCHAR,
    ZDISPLAYNAME VARCHAR,

    -- Additional (version-dependent)
    ZPHONETICORGANIZATION VARCHAR,
    ZMEMOJIMETADATA BLOB,                   -- macOS 12+
    ZAVATARRECIPEDATA BLOB,                 -- macOS 13+
    ZSENSITIVECONTENTCONFIGURATION INTEGER  -- macOS 13+
);

ZABCDEMAILADDRESS Table

CREATE TABLE ZABCDEMAILADDRESS (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,
    Z_OPT INTEGER,
    ZOWNER INTEGER,                         -- FK to ZABCDRECORD.Z_PK
    ZISPRIMARY INTEGER,                     -- 1 = primary email
    ZLABEL VARCHAR,                         -- Label (e.g., "_$!<Work>!$_")
    ZADDRESS VARCHAR,                       -- Email address
    ZADDRESSNORMALIZED VARCHAR,             -- Lowercase normalized
    ZORDERINGINDEX INTEGER                  -- Display order
);

ZABCDPHONENUMBER Table

CREATE TABLE ZABCDPHONENUMBER (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,
    Z_OPT INTEGER,
    ZOWNER INTEGER,                         -- FK to ZABCDRECORD.Z_PK
    ZISPRIMARY INTEGER,
    ZLABEL VARCHAR,                         -- Label (e.g., "_$!<Mobile>!$_")
    ZFULLNUMBER VARCHAR,                    -- Full phone number as entered
    ZNUMBER VARCHAR,                        -- Normalized number (digits)
    ZCOUNTRYCODE VARCHAR,                   -- Country code (e.g., "us")
    ZAREACODE VARCHAR,
    ZLOCALNUMBER VARCHAR,
    ZEXTENSION VARCHAR,
    ZORDERINGINDEX INTEGER
);

ZABCDPOSTALADDRESS Table

CREATE TABLE ZABCDPOSTALADDRESS (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,
    Z_OPT INTEGER,
    ZOWNER INTEGER,                         -- FK to ZABCDRECORD.Z_PK
    ZISPRIMARY INTEGER,
    ZLABEL VARCHAR,
    ZSTREET VARCHAR,
    ZSUBLOCALITY VARCHAR,
    ZCITY VARCHAR,
    ZSUBADMINISTRATIVEAREA VARCHAR,
    ZSTATE VARCHAR,
    ZPOSTALCODE VARCHAR,
    ZCOUNTRY VARCHAR,
    ZCOUNTRYCODE VARCHAR,
    ZORDERINGINDEX INTEGER
);

ZABCDSOCIALPROFILE Table

CREATE TABLE ZABCDSOCIALPROFILE (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,
    Z_OPT INTEGER,
    ZOWNER INTEGER,                         -- FK to ZABCDRECORD.Z_PK
    ZSERVICE VARCHAR,                       -- Service name (e.g., "twitter")
    ZSERVICEIDENTIFIER VARCHAR,             -- Service-specific ID
    ZUSERNAME VARCHAR,                      -- Profile username
    ZURL VARCHAR,                           -- Profile URL
    ZORDERINGINDEX INTEGER
);

ZABCDURLADDRESS Table

CREATE TABLE ZABCDURLADDRESS (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,
    Z_OPT INTEGER,
    ZOWNER INTEGER,                         -- FK to ZABCDRECORD.Z_PK
    ZLABEL VARCHAR,
    ZURL VARCHAR,
    ZORDERINGINDEX INTEGER
);

ZABCDRELATEDNAME Table

CREATE TABLE ZABCDRELATEDNAME (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,
    Z_OPT INTEGER,
    ZOWNER INTEGER,                         -- FK to ZABCDRECORD.Z_PK
    ZLABEL VARCHAR,                         -- Relationship type (e.g., "_$!<Spouse>!$_")
    ZNAME VARCHAR,                          -- Related person's name
    ZORDERINGINDEX INTEGER
);

ZABCDNOTE Table

CREATE TABLE ZABCDNOTE (
    Z_PK INTEGER PRIMARY KEY,
    Z_ENT INTEGER,
    Z_OPT INTEGER,
    ZCONTACT INTEGER,                       -- FK to ZABCDRECORD.Z_PK
    ZTEXT VARCHAR,                          -- Plain text content
    ZRICHTEXTDATA BLOB                      -- RTF content (optional)
);

Key Fields for Analysis

Entity Types (Z_ENT)

Always filter by Z_ENT = 22 when querying for contacts.

Label Format

Apple wraps standard labels in a special delimiter format:

Strip the _$!< prefix and >!$_ suffix to get the readable label. Custom labels are stored without the wrapper.

Forensic Queries

-- All contacts with phone numbers
SELECT
    r.Z_PK,
    r.ZFIRSTNAME,
    r.ZLASTNAME,
    r.ZORGANIZATION,
    datetime(r.ZCREATIONDATE + 978307200, 'unixepoch') AS created_utc,
    p.ZFULLNUMBER AS phone,
    p.ZLABEL AS phone_label
FROM ZABCDRECORD r
LEFT JOIN ZABCDPHONENUMBER p ON p.ZOWNER = r.Z_PK
WHERE r.Z_ENT = 22
ORDER BY r.ZLASTNAME, r.ZFIRSTNAME;

-- All contacts with email addresses
SELECT
    r.ZFIRSTNAME,
    r.ZLASTNAME,
    e.ZADDRESS AS email,
    e.ZLABEL AS email_label
FROM ZABCDRECORD r
JOIN ZABCDEMAILADDRESS e ON e.ZOWNER = r.Z_PK
WHERE r.Z_ENT = 22
ORDER BY r.ZLASTNAME, r.ZFIRSTNAME;

-- Recently modified contacts
SELECT
    r.ZFIRSTNAME,
    r.ZLASTNAME,
    datetime(r.ZMODIFICATIONDATE + 978307200, 'unixepoch') AS modified_utc,
    datetime(r.ZCREATIONDATE + 978307200, 'unixepoch') AS created_utc
FROM ZABCDRECORD r
WHERE r.Z_ENT = 22
ORDER BY r.ZMODIFICATIONDATE DESC
LIMIT 50;

-- Contacts with relationships
SELECT
    r.ZFIRSTNAME || ' ' || r.ZLASTNAME AS contact,
    rn.ZLABEL AS relationship,
    rn.ZNAME AS related_person
FROM ZABCDRECORD r
JOIN ZABCDRELATEDNAME rn ON rn.ZOWNER = r.Z_PK
WHERE r.Z_ENT = 22;

-- Contact notes (potentially sensitive)
SELECT
    r.ZFIRSTNAME || ' ' || r.ZLASTNAME AS contact,
    n.ZTEXT AS note
FROM ZABCDRECORD r
JOIN ZABCDNOTE n ON n.ZCONTACT = r.Z_PK
WHERE r.Z_ENT = 22 AND n.ZTEXT IS NOT NULL;

Phone Number Normalization

For cross-referencing with Messages handles and FaceTime addresses, phone numbers must be normalized:

1. Strip all non-digit characters except a leading +.
2. Handle country code prefixes (e.g., +1 for US/Canada).
3. Compare the last 10 digits for US numbers, or use E.164 format for international.

The ZNUMBER column contains a pre-normalized version, but ZFULLNUMBER preserves the original formatting entered by the user.

Timestamps

All timestamps use Core Data epoch (seconds since 2001-01-01 00:00:00 UTC):

SELECT datetime(ZCREATIONDATE + 978307200, 'unixepoch') AS created_utc
FROM ZABCDRECORD WHERE Z_ENT = 22;

Analysis Notes

• Multi-value joins: Each multi-value field type (phones, emails, etc.) is in its own table, joined to ZABCDRECORD via the ZOWNER foreign key. A single contact can have multiple entries in each table.
• Unified contacts: macOS may link contacts from different sources (iCloud, Exchange, local) into a unified view. The ZPERSONLINK and ZLINKID columns manage this linking. Two contacts with the same ZLINKID represent the same person from different sources.
• Photo resolution: Contact photos may be stored inline (ZIMAGEDATA blob for thumbnails) or as external JPEG files in the _EXTERNAL_DATA directory (referenced by ZIMAGEHASH).
• Notes sensitivity: The ZABCDNOTE.ZTEXT field may contain free-text information that is highly sensitive. Collection options allow skipping notes.
• Ordering: The ZORDERINGINDEX column on multi-value tables determines the display order in the Contacts UI. The first entry (index 0) may not be the primary one -- check ZISPRIMARY instead.
• Empty records: Some contacts may have only an organization name and no personal name, or vice versa. Always check both identity and organization fields.

Version Differences

Tool Support

References

• Mac OSX AddressBook SQLite Schema
• ABCDDB File Format
• kacos2000 AddressBook Queries