How can I repair missing referenced photos in Photos.app on MacOS Catalina?
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).