A Photos Library must not be located on iCloud Drive. It has never been safe to store the Photos Library there, because the cloud syncing can damage the internal links. Previous system versions allowed us to put our Photos Libraries at risk by storing them on iCloud Drive, but since macOS13 13 iCloud does no longer upload any libraries to iCloud Drive and macOS 14 is even refusing to open a library, while it is on iCloud Drive, to protect our photos.
If you want to store your photos in iCloud and sync them between your devices, use iCloud Photos. It is the only way to keep your photos in iCloud and sync them safely between devices. See Apple's warning here: Move your Photos library to save space on your Mac - Apple Support
"You can't store your library on a storage device used for Time Machine backups. And to avoid possible data loss, don't store your library on a removable storage device like an SD card or USB flash drive, or on a device shared over your network or the internet, including over a cloud-based storage service."
Don't use any other cloud based storage either - Dropbox or Google Drive to store a photos library, if you do not want to lose photos. It is not safe.
When I try to move any kind of photo library (Photos Library, Aperture Library, iPhoto Library) to iCloud Drive and enable to show the cloud status in the Finder, I can see, that iCloud cannot upload them to iCloud, but is keeping them locally on my Mac. There is a crossed out cloud behind the filename, since macOS 13 Monterey. What is new in Sonoma is, that Photos does not even try to open them there.
