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)
- Evidence area
- 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
Tools
Compare all toolsWhat 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
| Source | Path | Notes |
|---|---|---|
| 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 backup | CameraRollDomain, Media/PhotoData/Photos.sqlite | 12/12b144c0bd44f2b3dffd9186d3f9c05b917cee25 |
| iTunes / Finder backup | CameraRollDomain, 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, ZDIRECTORY | File name and folder under DCIM |
ZDATECREATED | Capture (creation) time |
ZADDEDDATE | Time the asset was added to the library |
ZMODIFICATIONDATE | Last modification of the record |
ZLATITUDE, ZLONGITUDE | Location; -180.0 means none |
ZKIND | 0 = photo, 1 = video |
ZTRASHEDSTATE, ZTRASHEDDATE | 1 = in Recently Deleted, and since when |
ZHIDDEN, ZFAVORITE | Hidden album and favourite flags |
ZUUID, ZCLOUDASSETGUID | Local and iCloud identifiers |
ZADDITIONALASSETATTRIBUTES.ZORIGINALFILENAME | Name of the file when imported |
ZADDITIONALASSETATTRIBUTES.ZIMPORTEDBY | Import source (enumeration: camera, screenshot, app, and others) |
ZADDITIONALASSETATTRIBUTES.ZIMPORTEDBYBUNDLEIDENTIFIER | Bundle ID of the saving app, on recent releases |
ZADDITIONALASSETATTRIBUTES.ZTIMEZONENAME, ZTIMEZONEOFFSET | Time zone at capture, where recorded |
ZADDITIONALASSETATTRIBUTES.ZEXIFTIMESTAMPSTRING | Capture 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.sqliteand the locally stored originals. Resolve paths through Manifest.db. - Full file system: collect the whole
/private/var/mobile/Media/tree, includingPhotoDatawith 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).
sqlite3or DB Browser for SQLite with the query above.exiftool -a -G1 -s IMG_0001.HEICfor the file-side view: make, model, lens, capture time, offset and GPS.
Investigator tips
ZADDEDDATElong afterZDATECREATEDusually 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
ZTRASHEDDATEin UTC.