How can I repair missing referenced photos in Photos.app on MacOS Catalina? A while ago I moved many photos from my Mac to external storage. However, I forgot to consolidate the library before performing this change. And now I can't remember the original folder structure I set up in the first place.

In my library I now have a set of photos which I can't repair the link to. No Finder window pops up, which means that the folder they were originally residing in, no longer exists.

Is there a way to reveal the file name and folder location of these "lost" referenced files so I can repair my Photos database?

Note: since the internal storage structure of the Photos Library changed dramatically in Catalina, I'm explicitly referring to Catalina in this question as I suppose it might affect the answer.
I couldn't find anything in Automator so I decided to explore a copy of the SQLite database stored in the Photos Library.photoslibrary bundle managed by Photos.

The instructions apply to macOS Catalina and may not work for earlier/later macOS versions.

⚠️ Warning: Never edit this database as it is managed by Photos.app.

I used the DB Browser for SQLite app for the job.

First copy the database to a working directory (assuming ~/PhotosDBCopy/):

Open a Terminal and type:

$ mkdir ~/PhotosDBCopy/
$ cp -pr ~/Pictures/Photos\ Library.photoslibrary/database/ ~/PhotosDBCopy/


Now launch the DB Browser for SQLite app and open the following database: ~/PhotosDBCopy/Photos.sqlite

The referenced images are listed in the ZFILESYSTEMBOOKMARK table. The original file paths are stored in the ZPATHRELATIVETOVOLUME column.

Close Photos.app.

Make sure you place the originals on your filesystem at the location given in the ZPATHRELATIVETOVOLUME column.

Once all photos have been placed in their initial folders (prior to moving them elsewhere), you can re-open Photos.app. The red line across the 'referenced' overlay icon will be gone for the referenced images, which means that Photos.app found the referenced originals on the filesystem.

You can now consolidate your Photos database. Upon completion, it is normally safe to delete again the recovered originals as they have now been copied into the Photos database (no more 'referenced" overlay icon on the photos).