Photos.sqlite: macOS Photos Library Metadata

What it is

A Photos library is a macOS package (a folder that Finder shows as one file) with the .photoslibrary extension. Inside, database/Photos.sqlite is a Core Data SQLite database with one row per photo or video, plus albums, people, moments, import sessions and edit history. The original media files sit in the originals folder of the same package. The database records metadata the files alone do not show: when an item was added to the library, whether it is hidden or in Recently Deleted, the app that imported it, and iCloud sync identifiers.

Where it lives

Asset table names by macOS version (from osxphotos):

The library is protected by TCC (Photos privacy). Terminal or the collector needs Full Disk Access to copy it on a live system. Search for other libraries on a mounted image, e.g. find /Volumes/<image> -name '*.photoslibrary' -type d.

What it proves

• Capture date and time (ZDATECREATED) and the time zone in effect (ZTIMEZONEOFFSET, ZTIMEZONENAME).
• GPS position (ZLATITUDE, ZLONGITUDE) and cached reverse geocoding (ZREVERSELOCATIONDATA).
• When the item was added to this library (ZADDEDDATE) and last modified (ZMODIFICATIONDATE).
• The name the file had before import (ZORIGINALFILENAME) and the importing app (ZIMPORTEDBYBUNDLEIDENTIFIER, ZIMPORTEDBYDISPLAYNAME).
• User actions: favourite (ZFAVORITE), hidden (ZHIDDEN), trashed (ZTRASHEDSTATE, ZTRASHEDDATE).
• iCloud Photos identity (ZCLOUDASSETGUID).

It does not prove the user of this Mac took the photo: with iCloud Photos, items captured on an iPhone appear in the Mac library. Location can be missing, stripped or edited.

Key fields

Camera make, model and full EXIF remain in the original file: read them with exiftool.

Timestamps

ZDATECREATED, ZADDEDDATE, ZMODIFICATIONDATE and ZTRASHEDDATE are Mac absolute time, seconds since 2001-01-01 UTC. ZTIMEZONEOFFSET and ZTIMEZONENAME record the time zone at capture.

SELECT datetime(ZDATECREATED + 978307200, 'unixepoch') AS created_utc,
       datetime(ZADDEDDATE   + 978307200, 'unixepoch') AS added_utc
FROM ZASSET LIMIT 5;

EXIF DateTimeOriginal inside the file is local time without a zone unless offset tags are present; do not compare it directly with the UTC database values.

Retention

Items stay until deleted. Deleted items move to Recently Deleted (ZTRASHEDSTATE = 1) and, per Apple, are permanently deleted after 30 days, or sooner if the user empties the album. With iCloud Photos, deletions sync to the user's other devices. Rows and files for purged assets may survive in WAL files, SQLite free pages, APFS snapshots and Time Machine backups.

Collection

# Terminal with Full Disk Access; quit Photos first
ditto "$HOME/Pictures/Photos Library.photoslibrary/database" case/photos/database
shasum -a 256 case/photos/database/Photos.sqlite*

• UAC's photos artifact collects Photos.sqlite* from the default library for each user; add originals/ if you need the media.
• For full libraries, image the Data volume; originals can be very large, and with "Optimize Mac Storage" many originals may only exist in iCloud.

Parsing

• osxphotos handles every Photos database version (macOS and Linux for query and export):

# point --library at a copied .photoslibrary package (or its Photos.sqlite)
osxphotos query --library "case/photos/Photos Library.photoslibrary" --json > photos.json

• exiftool reads embedded EXIF and GPS from the files in originals/.
• SQL on macOS 11+ (use ZGENERICASSET on 10.15):

SELECT a.ZUUID, aa.ZORIGINALFILENAME, a.ZDIRECTORY || '/' || a.ZFILENAME AS stored_as,
       datetime(a.ZDATECREATED + 978307200, 'unixepoch') AS created_utc,
       datetime(a.ZADDEDDATE   + 978307200, 'unixepoch') AS added_utc,
       a.ZLATITUDE, a.ZLONGITUDE, a.ZTRASHEDSTATE,
       datetime(a.ZTRASHEDDATE + 978307200, 'unixepoch') AS trashed_utc,
       a.ZHIDDEN, aa.ZIMPORTEDBYBUNDLEIDENTIFIER
FROM ZASSET a
LEFT JOIN ZADDITIONALASSETATTRIBUTES aa ON aa.ZASSET = a.Z_PK
ORDER BY a.ZDATECREATED;

Investigator tips

• A large gap between ZDATECREATED and ZADDEDDATE means the item came from elsewhere (import, sync, another device); check ZIMPORTEDBYBUNDLEIDENTIFIER and import sessions.
• ZHIDDEN = 1 and ZTRASHEDSTATE = 1 are deliberate user actions worth reporting, with ZTRASHEDDATE as the time.
• ZORIGINALFILENAME often preserves the camera or sender's naming, which helps link a photo to a Messages attachment or a camera card seen in USB device logs.
• Check ZCLOUDASSETGUID and your iCloud evidence before attributing capture to this Mac; see iCloud Drive.
• Validate column names with .schema ZASSET: Apple changes the Photos schema with most major releases (osxphotos tracks at least eight model versions).
• Look for additional .photoslibrary packages outside ~/Pictures; a second library on an external drive is easy to miss.