Recommended way to get account_id throughout project?

[usebasejump]

Recommended way to get account_id throughout project?

[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.