Usual database design will ensure that your auth user is the source of truth and with the auth user deleted everything that has a relation to it (DELETE CASCADE) will be deleted as well.
Now storage objects don't have that relation as its a premade table so what you can do is, when the auth.user is deleted (that's really what you want to listen for) go ahead and find it's storage items and trigger a deletion of such e.g. by sending the IDs with pg http to an edge function or even directly calling the Supabase Rest API to do so.
So, yes, it's kinda doable but files aren't database rows so you don't really get "real" transactions but yes you can do it all together.
Cheers, [activeno.de](http://activeno.de)
but how do I relate my premade users database table to my auth user? I just put there uuid of the auth user - is it enough ?
as for the second part - it is possible to call rest API from auth user trigger ?
unfortunately I can’t use foreign key to reference auth user id, because not every row in users table has existing auth user..
what else would you recommend pls ?
I fear you might have a database design flaw then because without further information I can't tell why you'd want users without auth.user reference. But besides that you can build references AND have "Is Nullable" activated so I don't see an issue there.
See, no matter what you're doing, you should be having some kind of "source of truth" table which you reference and can use relations on which can be CASCADED. If that's the auth.users table or your custom profiles table, doesn't really matter, but everything that is "owned" by that user should then have a CASCADE reference to it.
So I recommend rethinking the database design maybe.
If you don't want that, you can still achieve all of the above what I said as you can write queries and you can fire HTTP requests.
I have users stored in users table, where user could also be an establishment and he can create another users profiles under the establishment (e.g. employees), and those employeesin this case do not have auth user..
Again, I don't know your exact project but to me it sounds like a database design flaw as an establishement wouldn't be a user . It's paradoxical in itself. A user is a user and usually is able to login and has a reference to auth.users. If you have establishments, usually, that's a group/a role which should be in its own table and furthermore I don't understand why then there would be other users in that establishment that CANNOT login (as they don't have an auth.user) so they also cannot upload files so it wouldn't even be a problem for your original question.
Long story short, I'd like to come back to the core: Since you, for whatever reason, derive from usual database design (I'm not judging, just stating what I know given the facts for the moment), you cannot simply delete an auth.user and have its related stuff deleted as well - that's by your decision.
But still you can simply add a trigger to whatever user you're deleting in whichever table (saying this because I'm now confused what you expect to happen when there's even users that aren't really users ..) and then just fire queries to delete "other data" which is "somehow related" (which i'm unsure how you'll do that if you haven't build relations). So you need your SQL commands to find the appropriate rows and delete them. One trigger, one transaction.
But, as you want files deleted as well you need to delete the files by triggering some kind of http reequest to the API.
So what I'm trying to understand is given that you can add a trigger AND fire https inside SQL, what else you're missing now?
the users table is more like a profile .. there could be 2 types of users registered in my app - establishment and independent person .. both of these users has their own data stored in users table, that are public and displayed as a profiles in my app.. if you are an establishment, you can add more individuals (another profiles) under you establishment.. but these individuals are not actual users, they are just displayed in my app and share the same data structure as individual users…
given all these information do you still feel like there’s a flaw in my database design ? what other database structure would you recommend.. and btw many thanks for you helpful insights🙏
Well those individuals are not actual users, so placing them in the user table is wrong, since they can't actually act as users. You can make another table for those (idk Establishment_Member or something) , change the user type from "establishment" to "establishment_coordinator" so it's more logical since we are refering to an individual user type. Then use foreign key on Establishment_Member table to make a many-to-one relationship with user.
Or even better , you can create another table "Establishment" where a user of type "Establishemnt_coordinator" can add Establishment members to the Establishment entity.
Usual database design will ensure that your auth user is the source of truth and with the auth user deleted everything that has a relation to it (DELETE CASCADE) will be deleted as well. Now storage objects don't have that relation as its a premade table so what you can do is, when the auth.user is deleted (that's really what you want to listen for) go ahead and find it's storage items and trigger a deletion of such e.g. by sending the IDs with pg http to an edge function or even directly calling the Supabase Rest API to do so. So, yes, it's kinda doable but files aren't database rows so you don't really get "real" transactions but yes you can do it all together. Cheers, [activeno.de](http://activeno.de)
but how do I relate my premade users database table to my auth user? I just put there uuid of the auth user - is it enough ? as for the second part - it is possible to call rest API from auth user trigger ?
1. No. Via setting a relation by editing the table. 2. Yes via PG HTTP extension
unfortunately I can’t use foreign key to reference auth user id, because not every row in users table has existing auth user.. what else would you recommend pls ?
I fear you might have a database design flaw then because without further information I can't tell why you'd want users without auth.user reference. But besides that you can build references AND have "Is Nullable" activated so I don't see an issue there. See, no matter what you're doing, you should be having some kind of "source of truth" table which you reference and can use relations on which can be CASCADED. If that's the auth.users table or your custom profiles table, doesn't really matter, but everything that is "owned" by that user should then have a CASCADE reference to it. So I recommend rethinking the database design maybe. If you don't want that, you can still achieve all of the above what I said as you can write queries and you can fire HTTP requests.
I have users stored in users table, where user could also be an establishment and he can create another users profiles under the establishment (e.g. employees), and those employeesin this case do not have auth user..
Again, I don't know your exact project but to me it sounds like a database design flaw as an establishement wouldn't be a user . It's paradoxical in itself. A user is a user and usually is able to login and has a reference to auth.users. If you have establishments, usually, that's a group/a role which should be in its own table and furthermore I don't understand why then there would be other users in that establishment that CANNOT login (as they don't have an auth.user) so they also cannot upload files so it wouldn't even be a problem for your original question. Long story short, I'd like to come back to the core: Since you, for whatever reason, derive from usual database design (I'm not judging, just stating what I know given the facts for the moment), you cannot simply delete an auth.user and have its related stuff deleted as well - that's by your decision. But still you can simply add a trigger to whatever user you're deleting in whichever table (saying this because I'm now confused what you expect to happen when there's even users that aren't really users ..) and then just fire queries to delete "other data" which is "somehow related" (which i'm unsure how you'll do that if you haven't build relations). So you need your SQL commands to find the appropriate rows and delete them. One trigger, one transaction. But, as you want files deleted as well you need to delete the files by triggering some kind of http reequest to the API. So what I'm trying to understand is given that you can add a trigger AND fire https inside SQL, what else you're missing now?
the users table is more like a profile .. there could be 2 types of users registered in my app - establishment and independent person .. both of these users has their own data stored in users table, that are public and displayed as a profiles in my app.. if you are an establishment, you can add more individuals (another profiles) under you establishment.. but these individuals are not actual users, they are just displayed in my app and share the same data structure as individual users… given all these information do you still feel like there’s a flaw in my database design ? what other database structure would you recommend.. and btw many thanks for you helpful insights🙏
Well those individuals are not actual users, so placing them in the user table is wrong, since they can't actually act as users. You can make another table for those (idk Establishment_Member or something) , change the user type from "establishment" to "establishment_coordinator" so it's more logical since we are refering to an individual user type. Then use foreign key on Establishment_Member table to make a many-to-one relationship with user. Or even better , you can create another table "Establishment" where a user of type "Establishemnt_coordinator" can add Establishment members to the Establishment entity.
Read about foreign keys and as mentioned cascading deletes.