Calendar, Contacts and Reminders Databases on macOS

What it is

Calendar, Contacts and Reminders are three Apple apps backed by Core Data SQLite stores that mirror whatever accounts the user added: iCloud, Exchange, Google (CalDAV/CardDAV), or local "On My Mac" data. They are rarely the first artifact you pull, but they answer classic questions: who is this phone number, was there a meeting with that supplier, when was the "delete the files" task ticked off?

Because they sync, the Mac copy may contain entries created on a phone or by another person (shared calendars, invitations). Always identify the source account before attributing an entry to the user.

Where it lives

The version suffix of the Contacts file (-v22) has been stable for years but is not guaranteed; list the folder rather than hard-coding it.

What it proves

• Contacts: names, organisations, phone numbers, e-mail and postal addresses, notes and social profiles, with creation and modification times. Essential to resolve handles in Messages and Mail.
• Calendar: event titles, locations, start and end times, attendees and organiser, invitation status, alarms, attachments and URLs (video-conference links).
• Reminders: task titles, notes, due dates, completion flag and completion time, list membership, and sometimes location-based triggers.

Key fields

Schemas differ across releases, especially for Calendar. Start with .tables and PRAGMA table_info() rather than a saved query.

Timestamps

Dates are Mac absolute time (add 978307200 for Unix time). Calendar events also store a time zone per event; all-day events use floating dates that do not map to a single UTC instant. Modification times on synced items reflect the last change from any device on the account.

Retention

• Items persist until deleted. Deletions sync, so an entry removed on the phone disappears from the Mac at the next sync.
• Reminders keeps completed tasks unless the user clears them, which gives a useful history of finished work.
• Deleted rows may survive in -wal files, free pages, APFS snapshots and Time Machine.

Collection

sudo ditto "/Users/<user>/Library/Application Support/AddressBook" ./case/AddressBook_<user>
sudo ditto "/Users/<user>/Library/Calendars" ./case/Calendars_<user>
sudo ditto "/Users/<user>/Library/Group Containers/group.com.apple.calendar" ./case/calendar_gc_<user>
sudo ditto "/Users/<user>/Library/Group Containers/group.com.apple.reminders" ./case/reminders_<user>

UAC collects the AddressBook stores, metadata and images. Collect each database with its -wal and -shm files.

Parsing

-- Contacts with phone numbers
SELECT r.ZFIRSTNAME, r.ZLASTNAME, r.ZORGANIZATION, p.ZFULLNUMBER,
       datetime(r.ZCREATIONDATE + 978307200, 'unixepoch') AS created_utc
FROM ZABCDRECORD r JOIN ZABCDPHONENUMBER p ON p.ZOWNER = r.Z_PK;

-- Completed reminders
SELECT ZTITLE, datetime(ZCOMPLETIONDATE + 978307200, 'unixepoch') AS done_utc
FROM ZREMCDREMINDER WHERE ZCOMPLETED = 1 ORDER BY ZCOMPLETIONDATE DESC;

APOLLO's KnowledgeC modules (for example knowledge_calendar_event_title and app activity for Calendar) add usage context on releases where KnowledgeC is populated.

Investigator tips

• Normalise phone numbers (strip spaces, country codes) before joining Contacts to Messages handles.
• A contact created minutes before a call or message exchange can matter; check ZCREATIONDATE.
• Calendar invitations include the organiser's address even when the user declined, which can place a person in a conversation.
• Account identifiers in each store tell you whether data came from a work Exchange account or a personal iCloud account, which affects scope and legal authority.