Recommended way to get account_id throughout project?

[bryan-vh]

Hey all, thanks for building this library! I'm currently doing something like this:

• User signs up with some provider (Google for example)
• We create a team account for that user using the RPC 'create_account' since personal accounts are disabled
• Access the account_id throughout other components that need it?

I'm stuck on step 3 right now. I'm currently debating on creating a bespoke provider to pass this account_id where needed to other client components in my application, but wasn't sure if there was a better way to do this. I'm essentially creating an app that has projects (folders) and documents that are owned by each project. Each project should be owned by only one account and each user should only be able to belong to one team account. Would appreciate any guidance on how to move forward!

[thedevdavid]

I think the simplest solution is either

• a public.profiles table and use a team_id column to reference the basejump.accounts(id) or
• save the account_id into auth.users raw_user_metadata column

I choose something similar to the first option. Since I use personal accounts also, I set it up like auth.users, public.profiles, and basejump.accounts share the same ID, so it makes it easy to cross-reference. Plus I also utilize a slugified, unique username for routing.

I had to overwrite the basejump.run_new_user_setup() function tho. Here's the code. You may be able to get along by changing personal_account to false, use something else for slug & name instead of username, and using a an extra team_id or similar column as a foreign key referencing basejump.accounts(id) on public.profiles, instead of the id.

CREATE TABLE IF NOT EXISTS public.profiles
(
    id uuid not null references auth.users(id) on delete cascade,
    username text not null unique,
......
);

create or replace function basejump.run_new_user_setup()
    returns trigger
    language plpgsql
    security definer
    set search_path = public
as
$$
declare
    first_account_id    uuid;
begin
    -- create the new users's personal account
    insert into basejump.accounts (name, primary_owner_user_id, personal_account, id, slug, created_by, updated_by)
    values (NEW.raw_user_meta_data->>'username', NEW.id, true, NEW.id, NEW.raw_user_meta_data->>'username', NEW.id, NEW.id)
    returning id into first_account_id;

    -- add them to the account_user table so they can act on it
    insert into basejump.account_user (account_id, user_id, account_role)
    values (first_account_id, NEW.id, 'owner');

    -- create a profile for the user
    insert into public.profiles (id, email, username)
    values (NEW.id, NEW.email, NEW.raw_user_meta_data->>'username');

    return NEW;
end;
$$;

-- trigger the function every time a user is created
create or replace trigger on_auth_user_created
    after insert
    on auth.users
    for each row
execute procedure basejump.run_new_user_setup();

Note: There are plenty of improvement options with this solution. But it was "ok enough" for me.