Contact Change Tracking

Overview

The AddressBook database maintains a forensic audit trail of all contact modifications through three tables: ATRANSACTION records when changes occurred and which application made them, ACHANGE records what was changed (insert, update, or delete), and ZABCDDELETEDRECORDLOG preserves the unique identifiers and deletion dates of removed contacts. Together, these tables provide evidence of who modified contacts, when modifications occurred, and whether contacts were deliberately deleted.

Change tracking is particularly valuable in investigations involving evidence tampering, insider threats, or establishing timelines of relationship changes.

File Locations

Change tracking tables exist in both the main database and per-source databases.

Database Schema

ATRANSACTION Table

Records each transaction (batch of changes) with a timestamp and the application that initiated it.

CREATE TABLE ATRANSACTION (
    Z_PK INTEGER PRIMARY KEY,
    ZTIMESTAMP TIMESTAMP,                   -- Core Data timestamp (seconds since 2001)
    ZBUNDLEID INTEGER,                      -- FK to ATRANSACTIONSTRING (app bundle ID)
    ZCONTEXTNAME VARCHAR,
    ZTRANSACTIONTYPE INTEGER
);

ACHANGE Table

Records individual changes within each transaction. Each row represents one insert, update, or delete operation on a specific entity.

CREATE TABLE ACHANGE (
    Z_PK INTEGER PRIMARY KEY,
    ZTRANSACTIONID INTEGER,                 -- FK to ATRANSACTION.Z_PK
    ZENTITY INTEGER,                        -- Entity type (22 = contact, 19 = group)
    ZCHANGETYPE INTEGER,                    -- 1 = Insert, 2 = Update, 3 = Delete
    ZENTITYPK INTEGER,                      -- Z_PK of the affected record
    ZTOMBSTONEDATA BLOB                     -- Serialized data for deleted records
);

ATRANSACTIONSTRING Table

Lookup table mapping bundle ID foreign keys to actual application identifiers.

CREATE TABLE ATRANSACTIONSTRING (
    Z_PK INTEGER PRIMARY KEY,
    ZVALUE VARCHAR UNIQUE                   -- Bundle ID string
);

Common bundle ID values:

ZABCDDELETEDRECORDLOG Table

Preserves the unique identifiers of deleted contacts for sync coordination.

CREATE TABLE ZABCDDELETEDRECORDLOG (
    Z_PK INTEGER PRIMARY KEY,
    ZUNIQUEID VARCHAR,                      -- Unique ID of the deleted contact
    ZDELETEDRECORDUNIQUEID VARCHAR,         -- Alternative column name (version-dependent)
    ZDELETIONDATE TIMESTAMP                 -- Core Data timestamp (may be NULL)
);

Note: The unique ID column name varies between schema versions. Some versions use ZUNIQUEID, others use ZDELETEDRECORDUNIQUEID. macfor handles both with a COALESCE query.

Key Fields for Analysis

Change Types (ACHANGE.ZCHANGETYPE)

Entity Types (ACHANGE.ZENTITY)

Forensic Queries

-- Recent changes with application attribution
SELECT
    datetime(t.ZTIMESTAMP + 978307200, 'unixepoch') AS change_time_utc,
    CASE c.ZCHANGETYPE
        WHEN 1 THEN 'INSERT'
        WHEN 2 THEN 'UPDATE'
        WHEN 3 THEN 'DELETE'
    END AS change_type,
    CASE c.ZENTITY
        WHEN 22 THEN 'Contact'
        WHEN 19 THEN 'Group'
        WHEN 25 THEN 'Container'
    END AS entity_type,
    c.ZENTITYPK AS affected_record_pk,
    ts.ZVALUE AS application
FROM ATRANSACTION t
JOIN ACHANGE c ON c.ZTRANSACTIONID = t.Z_PK
LEFT JOIN ATRANSACTIONSTRING ts ON t.ZBUNDLEID = ts.Z_PK
ORDER BY t.ZTIMESTAMP DESC;

-- Enriched changes with contact identity
SELECT
    datetime(t.ZTIMESTAMP + 978307200, 'unixepoch') AS change_time_utc,
    CASE c.ZCHANGETYPE
        WHEN 1 THEN 'INSERT'
        WHEN 2 THEN 'UPDATE'
        WHEN 3 THEN 'DELETE'
    END AS change_type,
    r.ZFIRSTNAME || ' ' || r.ZLASTNAME AS contact_name,
    r.ZUNIQUEID,
    ts.ZVALUE AS application
FROM ATRANSACTION t
JOIN ACHANGE c ON c.ZTRANSACTIONID = t.Z_PK
LEFT JOIN ATRANSACTIONSTRING ts ON t.ZBUNDLEID = ts.Z_PK
LEFT JOIN ZABCDRECORD r ON c.ZENTITYPK = r.Z_PK AND c.ZENTITY = 22
WHERE c.ZENTITY = 22
ORDER BY t.ZTIMESTAMP DESC;

-- All deleted contacts
SELECT
    COALESCE(ZUNIQUEID, ZDELETEDRECORDUNIQUEID) AS unique_id,
    datetime(ZDELETIONDATE + 978307200, 'unixepoch') AS deleted_utc
FROM ZABCDDELETEDRECORDLOG
ORDER BY Z_PK DESC;

-- Changes by application (potential unauthorized access)
SELECT
    ts.ZVALUE AS application,
    CASE c.ZCHANGETYPE
        WHEN 1 THEN 'INSERT'
        WHEN 2 THEN 'UPDATE'
        WHEN 3 THEN 'DELETE'
    END AS change_type,
    COUNT(*) AS change_count
FROM ACHANGE c
JOIN ATRANSACTION t ON c.ZTRANSACTIONID = t.Z_PK
JOIN ATRANSACTIONSTRING ts ON t.ZBUNDLEID = ts.Z_PK
GROUP BY ts.ZVALUE, c.ZCHANGETYPE
ORDER BY change_count DESC;

Timestamps

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

SELECT datetime(ZTIMESTAMP + 978307200, 'unixepoch') AS utc FROM ATRANSACTION;

Note: ZDELETIONDATE may be NULL in some schema versions, in which case the deletion time is unknown but the fact of deletion is still recorded.

Analysis Notes

• Application attribution: The ATRANSACTIONSTRING table maps bundle IDs to human-readable application identifiers. An unexpected application modifying contacts (e.g., a third-party app) may indicate unauthorized access or malware.
• Tombstone data: When a contact is deleted (change type 3), the ZTOMBSTONEDATA blob in ACHANGE may contain serialized data from the deleted record. This can potentially be decoded to recover some information about the deleted contact.
• Deleted record log: The ZABCDDELETEDRECORDLOG table persists even after the ACHANGE records may have been pruned. It provides a long-term record of contact deletions.
• Transaction batching: Multiple ACHANGE rows may share the same ATRANSACTION entry, indicating they were part of the same operation (e.g., a sync that modified multiple contacts).
• Change enrichment: For insert and update changes, the ZENTITYPK can be joined back to ZABCDRECORD.Z_PK to identify which contact was affected. For delete changes, the contact record no longer exists in ZABCDRECORD, so the ZTOMBSTONEDATA or ZABCDDELETEDRECORDLOG are the only sources of identity information.
• Pruning: macOS may periodically prune old ATRANSACTION and ACHANGE records to manage database size. The deleted record log typically has longer retention.
• iCloud sync changes: Changes attributed to com.apple.iCloud.contacts represent modifications synced from other devices. The timestamp reflects when the sync occurred locally, not when the original change was made on the remote device.

Version Differences

Tool Support

References

• Mac OSX AddressBook SQLite Schema
• Mac OS X 10.8 AddressBook Forensics
• mac4n6 Forensics Artifacts