I'm trying to set up a multi tenant database and am trying to create a new tenant and a user whenever someone signs up. I'm struggling inserting data via a trigger on a new row in auth.users since I'm trying to insert data into two separate tables (tenants and users).

Hello @Nin! This thread has been automatically created from your message in #843999948717555735 a few seconds ago. We have already mentioned the @User so that they can see your message and help you as soon as possible! Want to unsubscribe from this thread? Right-click the thread in Discord (or use the ``...`` menu) and select "Leave Thread" to unsubscribe from future updates. Want to change the title? Use the ``/title`` command! We have solved your problem? Click the button below to archive it.

Can you give more detail on your struggle? Not clear what issue you are having.

I now have 2 separate triggers but the user trigger seems to be failing, I'd like to do it in one function but I don't understand how

Correct

And new also doesn't have raw_user

You can can have one trigger on users extract any data from the row being inserted (basically id,email,raw_user_meta_data and raw_app_meta_data (last two both json) and insert into one or more tables as desired. All are accessed with new.email format, except json you have to use Postres json -> type format.

Thing is, on insertion, there is no tenant_id yet

But, raw_user_meta_data can be changed at any time by the user though.

Yes, I'm passing the tenant_name via raw_user_meta_data

I'll link something in a bit that shows using raw_app_meta_data for multi-tenant

Would it be best to trigger on tenant insertion?

If you can't keep your tentant info in the public profile (public.users in your case I guess)., then you insert into tenant table and use returning to get the id from the tenant table to use in a 2nd insert into your users table.

Can I do that in the same function?

Yes on first part.

Could you give me a pointer how I would go on about that?

I'm going to find the multi-tenant thing first.

Thank you Gary

https://github.com/supabase-community/supabase-custom-claims You should look thru this and see if it applies on your multi-tenant as far as security of setting someones role. You can't just use user_meta_data as user can call that anytime if they know how to code.

Ooh nice then I can add the tenant_id to the jwt

To do the two inserts in a single function you do your tenant insert and add to it returning id as tenant_id then in your next insert into users you have the tenant id. I'm not sure why you need both tables though versus just a public.users table that has all the info, but obviously don't know what you are trying to do.

I have two tables because one tenant should be able to hold multiple users

This from a help issue I worked on this afternoon and is someone's code doing a two insert function and needing id from first insert.

BEGIN
  IF NOT EXISTS(SELECT 1 FROM cart WHERE user_id = auth.uid()) THEN
    INSERT INTO cart (user_id, total) VALUES (auth.uid(), 0)
    RETURNING id INTO new_cart_id;
    INSERT INTO cart_item (cart_id, product_id, user_id, quantity)
    VALUES (
      new_cart_id,
      new_product_id,
      auth.uid(),
      new_quantity
    ) RETURNING * INTO new_cart_item

Ok going to give that a shot

I'll leave you to play around with the new info. Probably should start a new question depending on what you run into.

Will do, thanks Gary!

Thread was archived by @Nin. Anyone can send a message to unarchive it.

Really sorry @garyaustin. Struggling very much with the query. I feel like I'm almost there.

declare tenant_id public.tenants.tenant_id%TYPE;
begin
    insert into public.tenants (tenant_name) values (new.raw_user_meta_data->>'tenant_name');
    returning tenant_id INTO tenant_id;
    INSERT INTO public.users (user_id, tenant_id, user_email)
    VALUES (
      new.id,
      tenant_id,
      new.email
    ) return new;
    end;

This gives me the error:

Failed to create function: failed to update pg.functions with the given ID: syntax error at or near "returning"

Whereas without the declare it doesn't know what

tenant_id

is.

You need to drop the ; after first insert as returning is part of the insert. (Google postgres insert and you will see more info on returning). Also if your primary key on tenant table is really tenant_id versus id, then change use a different name for the INTO name.

It saved!

Thread was archived by @Nin. Anyone can send a message to unarchive it.

Update query

Thread was archived by @Nin. Anyone can send a message to unarchive it.