Skip to content

Photos & mediaLocation

Photos.sqlite: iOS Photo Library Database

Photos.sqlite on iOS: ZASSET capture and import times, GPS, Recently Deleted, hidden and shared items, the importing app, and how it maps to files in DCIM.

Location
/private/var/mobile/Media/PhotoData/Photos.sqlite (backup: CameraRollDomain, Media/PhotoData/Photos.sqlite, fileID 12b144c0bd44f2b3dffd9186d3f9c05b917cee25)
Proves
When each photo or video was captured, added and modified, where it was taken, which app or source created or imported it, and whether it was hidden, favourited, shared or moved to Recently Deleted
Timestamps
Cocoa time in seconds (add 978307200 for Unix time), UTC; the capture-time zone is stored separately where recorded
Acquisition
Unencrypted backup includes the database and locally stored media (CameraRollDomain). A full file system extraction adds thumbnails, derivatives, the WAL and freelist pages
Retention
Assets persist until deleted; deleted items stay in Recently Deleted for about 30 days, then leave the table but may remain in thumbnails and freelist pages
Collection
idevicebackup2, iMazing, Full file system extraction

What it is

Photos.sqlite is the Core Data database of the iOS photo library. Every photo, video, screenshot, Live Photo and saved image has a row in ZASSET (the table was named ZGENERICASSET before iOS 14), linked to dozens of other tables: albums, moments, faces and people, computed scene labels, iCloud state, sharing and edit history.

It is one of the richest databases on an iPhone. The media files themselves live under /private/var/mobile/Media/DCIM/, but the database knows things the files do not: when an item was added to the library, which app saved it, whether it was hidden or deleted, and what happened to it afterwards.

Where it lives

SourcePathNotes
Full file system/private/var/mobile/Media/PhotoData/Photos.sqlite (+ -wal, -shm)Library database
Full file system/private/var/mobile/Media/DCIM/1xxAPPLE/Originals (IMG_xxxx.HEIC, .JPG, .MOV, .PNG)
Full file system/private/var/mobile/Media/PhotoData/Thumbnails/, Mutations/Thumbnails and edited versions
iTunes / Finder backupCameraRollDomain, Media/PhotoData/Photos.sqlite12/12b144c0bd44f2b3dffd9186d3f9c05b917cee25
iTunes / Finder backupCameraRollDomain, Media/DCIM/...Only media stored on the device

With iCloud Photos and "Optimize iPhone Storage", many originals exist only in iCloud and the device keeps smaller versions. The database still lists those assets, so a row without a matching original is expected, not suspicious.

What it proves

  • Capture time and, where recorded, GPS coordinates of each asset.
  • When the asset entered the library (ZADDEDDATE), which separates a photo taken on the device from one received and saved later.
  • Import source and, on recent releases, the bundle ID of the app that saved it.
  • Deletion to Recently Deleted (ZTRASHEDSTATE, ZTRASHEDDATE), hiding, favourites and edits.
  • Membership in albums and, from iOS 16, in an iCloud Shared Photo Library.

Location in ZASSET comes from the file's metadata or from the capture. It proves where the camera recorded the photo, not where the phone was when the photo was received.

Key fields

Column (ZASSET unless stated)Meaning
ZFILENAME, ZDIRECTORYFile name and folder under DCIM
ZDATECREATEDCapture (creation) time
ZADDEDDATETime the asset was added to the library
ZMODIFICATIONDATELast modification of the record
ZLATITUDE, ZLONGITUDELocation; -180.0 means none
ZKIND0 = photo, 1 = video
ZTRASHEDSTATE, ZTRASHEDDATE1 = in Recently Deleted, and since when
ZHIDDEN, ZFAVORITEHidden album and favourite flags
ZUUID, ZCLOUDASSETGUIDLocal and iCloud identifiers
ZADDITIONALASSETATTRIBUTES.ZORIGINALFILENAMEName of the file when imported
ZADDITIONALASSETATTRIBUTES.ZIMPORTEDBYImport source (enumeration: camera, screenshot, app, and others)
ZADDITIONALASSETATTRIBUTES.ZIMPORTEDBYBUNDLEIDENTIFIERBundle ID of the saving app, on recent releases
ZADDITIONALASSETATTRIBUTES.ZTIMEZONENAME, ZTIMEZONEOFFSETTime zone at capture, where recorded
ZADDITIONALASSETATTRIBUTES.ZEXIFTIMESTAMPSTRINGCapture time string from EXIF

The link is ZADDITIONALASSETATTRIBUTES.ZASSET = ZASSET.Z_PK. Column sets change between releases, and ZIMPORTEDBY values are undocumented: test them on the same iOS version.

Timestamps

All dates are Cocoa time in seconds, UTC.

SELECT a.Z_PK, a.ZDIRECTORY || '/' || a.ZFILENAME AS path,
       datetime(a.ZDATECREATED + 978307200, 'unixepoch') AS created_utc,
       datetime(a.ZADDEDDATE + 978307200, 'unixepoch') AS added_utc,
       CASE WHEN a.ZLATITUDE = -180 THEN NULL ELSE a.ZLATITUDE END AS lat,
       CASE WHEN a.ZLONGITUDE = -180 THEN NULL ELSE a.ZLONGITUDE END AS lon,
       a.ZTRASHEDSTATE, a.ZHIDDEN,
       aa.ZORIGINALFILENAME, aa.ZIMPORTEDBY, aa.ZIMPORTEDBYBUNDLEIDENTIFIER
FROM ZASSET a
LEFT JOIN ZADDITIONALASSETATTRIBUTES aa ON aa.ZASSET = a.Z_PK
ORDER BY a.ZDATECREATED;

Drop ZIMPORTEDBYBUNDLEIDENTIFIER from the query if your version does not have it.

Retention

Assets remain until deleted. A deleted asset stays in Recently Deleted for about 30 days (ZTRASHEDSTATE = 1) and can be restored; after that the row is removed. Thumbnails, derivatives in PhotoData, SQLite freelist pages and the WAL can outlive the row, which is why a full file system extraction matters for deleted media.

Collection

  • Backup: a standard backup includes Photos.sqlite and the locally stored originals. Resolve paths through Manifest.db.
  • Full file system: collect the whole /private/var/mobile/Media/ tree, including PhotoData with the WAL.
  • Keep the files' own metadata: extract EXIF from originals with ExifTool and compare it with the database values.

Parsing

  • iLEAPP has several Photos.sqlite modules (assets, albums, locations, deleted items).
  • sqlite3 or DB Browser for SQLite with the query above.
  • exiftool -a -G1 -s IMG_0001.HEIC for the file-side view: make, model, lens, capture time, offset and GPS.

Investigator tips

  • ZADDEDDATE long after ZDATECREATED usually means the item was received and saved, not taken on this phone. Check the importing app and look for the same file in sms.db attachments or a messenger container.
  • Screenshots are usually PNG files with little camera metadata and their own import source value. They show what was on screen, so identify them before reasoning about capture location.
  • Compare GPS from photos with Significant Locations: two independent sources placing the device at the same spot are far stronger than one.
  • An item hidden or trashed shortly before a seizure is worth a line in the timeline. Report ZTRASHEDDATE in UTC.

See also