Photos.sqlite ZINTERNALRESOURCE Table Reference Guide

This reference guide was built as a part of some research and testing I performed looking into the Photos.sqlite ZINTERNALRESOURCE table. During the research, I was able to interpret most of the values I encountered, but I was not able to decode everything. Additional research and testing are required. I will continue to update this reference guide and the queries as new interpretations are discovered.

This reference guide will review some of the Photos.sqlite ZINTERNALRESOURCE table column data and how I interpreted the values from those columns within the query output. This is not an all-encompassing list of all columns, but I will highlight and discuss the columns that I was able to interpret which were used in the research.

Here is a link to my Photos.sqlite queries posted to my GitHub. Locate the repository for the iOS version you are analyzing. If you are attempting to analyze assets that may only have thumbnails or optimized assets being stored on the device internal storage, please use the queries that have “IntResou-iCldPhotos” included in the name of the query. This query will also assist with the analyzing of Live Photos.

Just like most of the tables that I have researched and tested in Photos.sqlite, I don’t believe I was able to generate every value for every column. You might be encountering column data and values within your analysis that I have not listed. If you are using my queries, an unknown value will have the following result in the query output: “Unknown-New-Value!” followed by the raw value. Please feel free to send me the raw value and your theory how the asset was imported into the Local Photo Library, and I would be more than happy to test and see what we can learn. 

The following is a breakdown of how I am listing the Photos.sqlite table names and the associated column names in the query output and within this write-up:

• Photos.sqlite Table: ZINTERNALRESOURCE table = zIntResou
• zIntResou table column name: ZLOCALAVAILABILITY = Local Availability
• Photos.sqlite Table and Column name combined for query output: zIntResou-Local Availability

zIntResou-Asset: Contains the ROW ID that is used to join the ZINTERNALRESOURCE table to the ZASSET table

zIntResou-zPK: Primary key for the ZINTERNALRESOURCE table

zIntResou-Fingerprint: Will indicate a unique identifier for each internal resource asset that was created for a particular original full-sized asset. During research, almost all assets were assigned a zIntResou-Fingerprint, but there are times when some internal resource assets did not have a unique value. Two examples of this would be if an internal resource asset was missing a zIntResou-Cloud Master Date Created or if an asset was in a Cloud Shared Album. During testing, the original full-sized asset zIntResou-Fingerprint matched the zAddAssetAttr-Master Fingerprint and the zCldMast-Cloud Master GUID. These fingerprints or unique IDs can be used to locate related asset data being stored within the store.cloudphotodb database.  

zIntResou-Datastore Class ID: Will indicate the Datastore Class ID for the asset. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value 0: Asset is a Local Photo Library asset or a Cloud Photo Library Asset
• Integer/Value 1: Observed during research but still testing and working on interpretation
• Integer/Value 2: Asset is a Photo Cloud Sharing Asset
• Integer/Value 3: Asset is a Shared with You Syndication Photo Library Asset

zAsset-Cloud Placeholder Kind: Will indicate the cloud placeholder for the asset you might find stored on the device internal storage. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value 0: Indicates if the asset is a local asset or a cloud master asset
• Integer/Value 1: Observed during research but still testing and working on interpretation
• Integer/Value 2: Observed during research but still testing and working on interpretation
• Integer/Value 3: Indicates the only asset being stored on the device is a JPG stored at /private/var/mobile/Media/PhotoData/Thumbnails/V2/* – original full-sized asset is being stored in iCloud
• Integer/Value 4: Indicates the Local Photo Library (LPL) asset is a JPG, but the Cloud Photo Library (CPL) asset is a HEIC, JPG, JPEG, MOV, PNG or other type of asset.
• Integer/Value 5: Indicates the asset has been synced from iCloud Photos to the device
• Integer/Value 6: Observed during research but still testing and working on interpretation
• Integer/Value 7: Indicates the LPL asset is a JPG, but the CPL asset is a MP4
• Integer/Value 8: Indicates the LPL asset is a JPG, but the CPL asset is a Live Photo MOV asset
• Integer/Value 9: Indicates the CPL MP4 asset was saved to the LPL

zIntResou-Local Availability: Will indicate if the Internal Resource Asset is available on the local device. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value -1: Internal Resource Asset is not available on the local device
• Integer/Value 1: Internal Resource is available on the local device
• Integer/Value -32768: Internal Resource is a Shared with You Syndication PL linked asset

zIntResou-Cloud Local State: Will indicate if the Internal Resource Asset has been synced with iCloud Photos. Based on my testing and research, the following is a list of values that I was able to interpret:

• Integer/Value 0: Internal Resource Asset has not been synced with iCloud Photos/Cloud Photo Library, there is no Internal Resource Cloud Master Date Created, indicating this asset doesn’t have cloud master asset 
• Integer/Value 1: Internal Resource Asset is pending upload to iCloud Photos.
  • This value will occur when iCloud Photos is turned ON, but the device has not had a chance to sync with iCloud Photos. During testing this was observed when the device was isolated from networks and I captured new assets. It was also observed when actively monitoring the live system immediately prior to the asset being synced with iCloud Photos. 
  • I also believe, but have not tested, this will also occur when the device has iCloud Photos turned ON, and iCloud storage is full. This would prevent assets from being synced with iCloud Photos, but the system (iOS) would prepare them for syncing when iCloud storage becomes available.
• Integer/Value 2: Internal Resource Asset is a Photo Cloud Sharing Data Asset on the local device 
• Integer/Value 3: Internal Resource Asset has been synced with iCloud Photos/CPL

zIntResou-Remote Availability: Will indicate if the Internal Resource Asset has remote availability. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value 0: Internal Resource Asset has no remote availability
• Integer/Value 1: Internal Resource Asset has remote availability

zIntResou-File ID: Will provide the File ID for the Internal Resource Asset. If an asset has an Internal Resource File ID, there is or was an internal resource asset stored locally on the device. There are a few things I noticed during testing that you should be aware of:

• During testing, I found it was possible to have assets stored within the device internal storage and they would not have a zIntResou-File ID. This occurred routinely with */Thumbnails/V2/* 5005.JPG and 5003.JPG thumbnail assets
• During testing, I learned its possible for an original full-sized internal resource asset to have a file ID even after the that asset has been optimized and imported to iCloud Photos. This was observed while I was forcing the device to initiate the Optimized iPhone Storage process
• During testing, if an asset has a zIntResou-Recipe ID of zero (0) and it had a zIntResou-File ID, the corresponding zIntResou-Data Length and zAddAssetAttr-Original File Size data would match. This can occur even after the asset was optimized and imported to iCloud Photos  

In figure #1, IMG_0008.MOV, is an example of an original full-sized asset, stored within the device internal storage. It has a zIntResou-File ID of 341075 and the zIntResou-Data Length is 6274575, which matches the zAddAssetAttr-Original File Size of 6274575, as seen highlighted in the green. We can see the other internal resource assets being stored on the device and how the data matches the data found in Photos.sqlite.

In figure #1 you will notice there are several other assets found within the device internal storage and the Photos.sqlite database. The assets highlighted in purple from the Photos.sqlite are all related to one primary asset as designated by their ZASSET ZPK integer/value, which is not visible in the screenshot. This is another reminder, if you are using or writing queries for Photos.sqlite and limiting those queries to only one row of data per primary key, you are missing vital data for your analysis.

zIntResou-Version: Will indicate the Internal Resource Asset Version. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value 0: During testing this was the most frequently used value. I have not been able to determine exactly what this value means, but I have indicated this value with the term “Standard”
• Integer/Value 1: Observed during research but still testing and working on interpretation
• Integer/Value 2: Will indicate if the Internal Resource is an Adjustment/Mutation asset
• Integer/Value 3: Will indicate when the Internal Resource Asset does not have a Cloud Master Date Created

zAddAssetAttr-Original File Size: Will indicate the Original Asset File Size in bytes. This is critical data when determining if you are analyzing the original full-sized asset or an optimized asset.

In figure #2, we can see an example of the original file size of an asset, IMG_0329.MP4, within terminal, ArtEx, and Photos.sqlite.

zIntResou-Resource Type: Will indicate the type of Internal Resource asset you are analyzing. Here is a link to Apple developer webpage that might help explain more about asset types. Based on my testing and research, the following is a list of values I was able to interpret:

• 0: Internal Resource is related to a Photo
• Integer/Value 1: Internal Resource is related to a Video
• Integer/Value 3: Internal Resource is related to a Live-Photo
• Integer/Value 5: Internal Resource is related to a HEIC file with Adjustments that were made prior to the capture of the asset. This can occur when the user opens the Apple Camera application and changes are made to the camera settings and filters
• Integer/Value 6: Internal Resource is related to a Screenshot
• Integer/Value 13: Internal Resource is related to a MOV asset
• Integer/Value 14: Indicates the Internal Resource is related to Wallpaper

zIntResou-Datastore Key Data/HEX: I am still working on getting the raw data decoded. In some instances, if the asset is a Photo Cloud Sharing Data Asset, this column might contain a partial file path for where the asset might be stored on the local device.

zIntResou-Datastore Sub-Type: Will indicate the datastore sub-type for the asset you might find on the device internal storage and will provide some insights about the asset size. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value 0: Asset does not have an Internal Resource Cloud Date Created asset
• Integer/Value 1: Asset is the main asset and based on testing will be the same size as the original asset, the zAddAssetAttr-Original File Size will match the zIntResou-Data Length.
• Integer/Value 2: Asset is related to a Photo with Adjustments
• Integer/Value 3: Asset is a JPG Large Thumbnail
• Integer/Value 4: Asset is a JPG Medium Thumbnail
• Integer/Value 5: Asset is a JPG Small Thumbnail
• Integer/Value 6: Asset is a Video Medium Data size
• Integer/Value 7: Asset is a Video Small Data size
• Integer/Value 8: Asset is a MP4 file related to a Photo Cloud Sharing Data asset
• Integer/Value 9: Observed during research but still testing and working on interpretation
• Integer/Value 10: Still testing this value but appears to be related to an asset that was imported by a third-party application that might be visible of the device
• Integer/Value 11: Observed during research but still testing and working on interpretation
• Integer/Value 12: Observed during research but still testing and working on interpretation
• Integer/Value 13: Asset is an Optimized PNG asset synced with Cloud
• Integer/Value 14: Asset is related to a Wallpaper asset
• Integer/Value 15: Asset has Markups and/or Adjustments
• Integer/Value 16: Asset is a Video with Adjustments
• Integer/Value 17: Asset is a RAW Photo. Interpretation by Rhet Turnbull
• Integer/Value 18: Asset is an Optimized Video asset related to a Live Photo synced with Cloud
• Integer/Value 19: Asset is related to a Live Photo with Adjustments
• Integer/Value 20: Observed during research but still testing and working on interpretation
• Integer/Value 21: Observed during research but still testing and working on interpretation
• Integer/Value 22: IMG_<####>.AAE asset associated with Adjustment/Mutation
• Integer/Value 23: Observed during research but still testing and working on interpretation
• Integer/Value 24: Observed during research but still testing and working on interpretation
• Integer/Value 25: Observed during research but still testing and working on interpretation
• Integer/Value 26: Asset is an Optimized MOV asset original size asset is in Cloud
• Integer/Value 27: Observed during research but still testing and working on interpretation
• Integer/Value 28: Asset is a HDR video

zIntResou-Cloud Source Type: Will indicate the Cloud Source Type of an asset. I only encountered a few of these during my testing, but the ones I encountered were due to the asset being a iCloud Link/Cloud Master Moment asset. The values and interpretations observed during testing matched those listed for the zIntResou-Datastore Sub-Type. The only one I observed which was different during testing was:

• Integer/Value 0: Which for zIntResou-Cloud Source Type indicates this is Not Applicable
• See the zIntResou-Datastore Sub-Type for the other values and interpretations

zIntResou-Data Length: Will indicate the internal resource asset data length. In some cases, this will match the zAddAssetAttr-Original File Size for the asset stored within the device internal storage and other times it will not. If you discover you only have a 5005 or 5003 thumbnail asset being stored on the local device storage, the file size for the thumbnail will not match the zIntResou-Data Length.

zIntResou-Recipe ID: Will indicate the internal resource asset type for the asset being stored or was stored on the physical device internal storage. Checking the zIntResou-File ID will allow you to determine if the asset is present on the local device. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value 0:
  • This Internal Resource Recipe ID will indicate if the asset is the original full-sized asset, or it was an original full-sized asset that has been optimized for iPhone storage
  • The zIntResou-Data Length can be compared to the zAddAssetAttr-Original File Size. If they match and you have a zIntResou-File ID for this asset you can expect to find the original full-sized asset within the internal device storage. Routinely during testing, when this was the case, the zAddAssetAttr-Original File Size and the zIntResou-Data Length would also match the metadata file size for the asset when viewed using forensic tools or terminal
  • This Recipe ID can also indicate if the internal resource asset is related to an asset that has been optimized for iPhone storage. If the asset has been optimized for iPhone storage the zIntResou-Data Length can be compared to the zAddAssetAttr-Original File Size and it will not match
  • The file paths for these assets will vary depending on the type of asset

• Integer/Value 65739: internal resource asset is a Large JPG thumbnail. During testing, I only encountered this type of item a few times. Additional testing is required. These internal resource assets can be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Metadata/*

• Integer/Value 65741: internal resource asset is related to a variety of asset types, both Local Photo Library assets, Cloud Photo Library assets, and Photo Cloud Sharing Data assets.
  • The original full-sized asset might not be stored on the device internal storage.
  • Routinely during testing, I observed the following naming schema for this Recipe ID assets:
    • IMG_<####>.JPG
    • IMG_<###>.jpg
    • <GUID>.JPG
    • IMG_<####>.HEIC
  • These assets might also be related to assets with adjustments/mutations.

• /private/var/mobile/Media/PhotoData/Metadata/*
  • /private/var/mobile/Media/PhotoData/CPLAssets/*
  • /private/var/mobile/Media/PhotoData/PhotoCloudSharingData/*
  • /private/var/mobile/Media/PhotoData/Mutations/*
  • /private/var/mobile/Media/PhotoData/UBF/scopes/syndication/*

• Integer/Value 65743: internal resource asset is a thumbnail for an asset that has a zIntResou-Resource Type of Photo. These are typically 5003.JPG or 5005.JPG assets. During my testing, this internal resource asset was never listed with a corresponding zIntResou-File ID. The file size listed in forensic tools did not match the internal resources data length listed in the database. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Thumbnails/V2/*

• Integer/Value 65749: internal resource asset is related to a video asset. Routinely during testing, these assets were LocalVideoKeyFrame.jpg assets. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Thumbnails/VideoKeyFrames/*

• Integer/Value 65938: internal resource asset is related to a photo asset that has an Adjustment/Mutation. Routinely during testing, these internal resource assets were FullSizeRender.jpg or FullSizeRender.heic. Note, if the FullSizedRender.jpg or heic asset is not available you might have an Adjustments.plist. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Mutations/*

• Integer/Value 131072: internal resource asset is related to a video asset that has an Adjustment/Mutation. Routinely during testing, these internal resource assets were FullSizeRender.mov. Note, if the FullSizedRender.mov asset is not available you might have an Adjustments.plist. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Mutations/*

• Integer/Value 131079: internal resource asset is related to a video asset that has an Adjustment/Mutation. Routinely during testing, I observed the following naming schema for this Recipe ID assets, IMG_<####>.medium.MP4 assets. Note, if the IMG_<####>.medium.MP4 asset is not available you might have a IMG_<####>.THM asset that is related. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Mutations/*
  • /private/var/mobile/Media/PhotoData/Metadata/*

• Integer/Value 131081: internal resource asset is a thumbnail for an asset that has a zIntResou-Resource Type of Video. These are typically 5003.JPG or 5005.JPG assets. During my testing, this internal resource asset was never listed with a corresponding Internal Resource File ID. The File Size listed in forensic tools did not match the Internal Resource data length listed in the database. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Thumbnails/V2/*

• Integer/Value 131272: internal resource asset is related to a Live Photo asset that has an Adjustment/Mutation. Routinely during testing, these internal resource assets were FullSizeRender.mov. Note, if the FullSizedRender.mov asset is not available you might have an Adjustments.plist. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Mutations/*

• Integer/Value 131275: internal resource asset is related to a Live Photo asset. Routinely during testing, these internal resource assets were IMG_.medium.MOV assets. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Metadata/*

• Integer/Value 131277: internal resource asset is related to Live Photo assets. These internal resource assets appear to be used for accessing assets that have been synced with iCloud Photos. In some instances, the original full-sized asset might be available via the physical device internal storage and sometimes it was not. This appears to be an indicator if the asset has been removed from the local device internal storge and is being stored in iCloud Photos as the result of Optimize iPhone Storge process. As the other 5003/5005.JPG thumbnails, during my testing, these did not have an Internal Resource File ID. The file size listed in forensic tools did not match the internal resource data length listed in the database. Routinely during testing, the assets with this recipe ID were 5005.JPG that can be found in the following location(s):

• /private/var/mobile/Media/PhotoData/Thumbnails/V2/*

• Integer/Value 131475: internal resource asset is related to a Dolby Vision High Dynamic Range (HDR) video asset. Routinely during testing, these internal resource assets were IMG_.medium-hdr.MOV assets. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Metadata/*

• Integer/Value 327683: This Recipe ID was observed during testing, but further testing is needed. It’s believed that an asset with this Recipe ID is related to a third-party application thumbnail. During testing these were assets related to Google Photos, but again further, testing is required.

• Integer/Value 627687: internal resource asset is related to assets that have been used as wallpaper. These assets will have a corresponding dat file titled “WallpaperComputeResources.dat.” Using Ian Whiffin’s blog post on Property Lists, I was able to view the embedded binary property list data within the dat file. It contains metadata for the asset that was or is set as wallpaper. These were only found in my iOS 16 test device acquisitions. Routinely during testing, these internal resource assets could be found in the following location(s):
  • /private/var/mobile/Media/PhotoData/Caches/Compute/*

zIntResou-Cloud Last Prefetch Date: I couldn’t figure out how to manually trigger and observe the method a timestamp gets populated into this column. Here is an Apple Developer link, which discusses how I believe a date could be populated into this column.

UPDATE: 12/06/2022

After conducting an additional test, I was able to determine how the timestamp listed within zIntResou-Cloud Last Prefetch Date is populated. This occurs when the thumbnail (5003 or 5005) asset is updated on the device and can be viewed in Photo Library/Camera Roll. The following is how I was able to determine how this timestamp is populated:

• Device was being synced and iCloud Photos was ON
• Turned iCloud Photos OFF and this removed all optimized assets from the device which prevented them from being viewed in the Photo Library/Camera Roll
• After a day or so turned iCloud Photos ON and the assets in iCloud Photos began to download back onto the device
• The first time I navigated the Photo Library the thumbnails for the assets that just downloaded were a black box with a white cloud icon in the corner   
• After a few hundreds of a second, the thumbnail would populate with a depiction of the original full-sized asset
• When I reviewed Photos.sqlite zInternal Resource data the assets associated with the zIntResou-Recipe ID “65743-ResouType-Photo_5003-or-5005-JPG_Thumb-65743” had a zIntResou-Cloud Last Prefetch Date

zIntResou-Cloud Prefetch Count: Because I was not able to successfully interpret the timestamp within Cloud Last Prefetch Date, I am unable to determine what produces a prefetch count.

UPDATE: 12/6/2022, please see zIntResou-Cloud Last Prefetch Date information. When these actions occurred, the count would be recorded. During all testing and research, I can not recall a time this count was above one (1), but I believe it’s possible.

zIntResou-Cloud Last On-Demand Download Date: Is a timestamp (UTC-0) that indicates the last time iOS has determined the device user might want an asset to be displayed and stored locally on the device internal storage.

• Based on what I observed during testing, this timestamp can indicate when there MIGHT have been user interaction with the device and the Photo Library
• The timestamp does not necessarily reflect the exact time an asset was interacted with by a user
• If a timestamp exists, it does not indicate that every internal resource asset was downloaded to the internal device storage. A limited number of assets, like a thumbnail or a single reduced sized asset could have been downloaded. It could also indicate when the original full-sized asset was downloaded

Note: Overall I did not observe enough consistent results to confidently state how this timestamp is populated. 

Initial testing and theories about the Cloud Last On-Demand Download Date population:

• I initially believed this timestamp would only be populated when the original full-sized asset was downloaded from iCloud Photos, but after continued testing this was found to be incorrect.
• I also initially believed this timestamp would only be populated when a user was navigating the Photo Library and a user selected/clicked on a specific asset to view or play, but again after further testing this was found to be incorrect
• In earlier testing, I believed these were the steps required to trigger an “On-Demand Download.” But as I already mentioned I was wrong. I have included the steps I took as a device user when I was able to manually trigger an On-Demand Download date for you to reference in case it might help you with an analysis:
  • iCloud Photos was ON and being used
  • The asset was previously optimized for iPhone storage
  • The original full-sized asset was being stored in iCloud Photos
  • A device user launched the photos application
  • A device user browsed the photo library assets
  • A device user clicked on the specific asset, thus viewing it in individual asset view
  • By clicking on the asset and viewing the asset via individual asset view, it initiated the “On-Demand Download” process
  • The full-sized asset was downloaded from iCloud Photos and saved to the device’s internal storage
  • After the user interaction and the On-Demand Download occurred, the Cloud Last On-Demand Download Date was populated in the Photos.sqlite database     

zIntResou-UTI Conformance Hint: Will indicate a hint to inform you of the Uniform Type ID for the internal resource asset. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value 0: Indicates Not Applicable and/or it does not have a conformance hint
• Integer/Value 1: Uniform Type ID is an Image
• Integer/Value 3:Uniform Type ID is a Video

zIntResou-Compact UTI: Will indicate, based on what I observed during testing, a file type you can expect to find related to the internal resource. Based on my testing and research, the following is a list of values I was able to interpret:

• Integer/Value 1: Indicates the asset could be a jpeg or thm asset
• Integer/Value 3: Indicates the asset could be a heic asset
• Integer/Value 6: Indicates the asset could be a png asset
• Integer/Value 7: Observed during research but still testing and working on interpretation
• Integer/Value 23: Indicates the asset could be a jpeg, heic, or quicktime mov asset
• Integer/Value 24: Indicates the asset could be a mpeg4 asset
• Integer/Value 36: Indicates the asset could be a wallpaper asset
• Integer/Value 37: Indicates the asset could be an adjusted/mutated asset

zCldMast-Full Size JPEG Source: Still Testing

Will indicate the original full-sized asset kind that was used as the source for the Cloud Masset JPEG asset. The following is a list of values I have been able to interpret:

• Integer/Value 0: will indicate the source type was a video
• Integer/Value 1: will indicate the source type was a photo

Please review the full write-up for this research which can be found here.

NOTE: I have received some feedback and assistance from Rhet Turnbull on some items that I was not encountered or decoded during my testing. He provided some values and their decoding based on his research. These values will be marked within the query outputs using an “RT” in the decoded value.