Let's say I have the following database schema:
thumbnailKey stores the key to a file stored in s3.
Now I want to delete all the posts of a user when deleting that user and use ON DELETE CASCADE for this. How can I also delete all thumbnails from s3 now? My first approach would be to make two queries, one for fetching all the posts (--> use the data to delete images from s3) and another one to delete the user. But if, for example, the database query fails, I deleted all thumbnails without deleting the posts.
Another approach I came up with is creating a separate s3 bucket for each user, storing the post's thumbnails in there, and deleting the entire bucket when deleting a user. But this doesn't seem right to me, I rather want to store all the files in one bucket.
Is there a better solution to my problem? Maybe even some best-practices?
If it helps: I use the Prisma ORM with Nest.js and PostgresQL and plan to use MinIO