A very common feature in web applications, especially SAAS applications are the concept of teams where a user can have and belong to multiple teams. In this tutorial I want go through how this can be setup in Phoenix and Ecto in combination with Phx Gen Auth.

The idea is that each user has their own personal account. The personal account is not accessible to other users. This ensures that the code works exactly the same regardless of whether we are adding resources to a private account or an account with users.

Once a new user record is created successfully, an action will take place and the user will be granted a personal account.

After that, users can create more accounts and invite others to them. Users can change which account they are viewing in the user interface.

Steps

The Steps to accomplish this is

• Create the accounts table with the option to be personal
• Update user logic to have a personal account created on registration
• Add a members table that acts as a many to many relationship between users and accounts

Create Accounts table and logic

First thing I need to do is to create the context that holds the logic

mix phx.gen.context Accounts Account accounts name created_by_user_id:references:users personal:boolean

Since a user can be deleted and the account might need to keep lingering, I will change reference and add :nilify_all and remove the reference to the user.

add :created_by_user_id, references(:users, on_delete: :nilify_all, type: :binary_id)

Add a partial unique index with Ecto

I want to make sure that a user can only have one personal account. I can solve that with a partial unique constraint. So in the migration, I will add:

create unique_index(:accounts, [:created_by_user_id], name: "accounts_limit_personal_index", where: "personal = true")

And then run mix ecto.migrate to update the database.

Update accounts

I need to update the account schema with the created_by relationship. Note that I specify the foreign key in this relation. Also, while I’m here, since I added the partial unique constraint, I can add the validation for it in the changeset.

Also add in the changeset unique_constraint(:personal, name: :accounts_limit_personal_index) ```

Since I want the user that creates the account stored on the account, I can update it like this and pass in the user:

# lib/demo/accounts.ex
def create_account(user, attrs \\ %{}) do
  %Account{}
  |> Account.changeset(attrs)
  |> Ecto.Changeset.put_assoc(:created_by, user)
  |> Repo.insert()
end

This means that I can keep track on who created the team. Next step is to associate the personal account with the user from the users perspective.

Add personal account to user

• Add personal account relationship to user schema
• Create the personal account when user is created

And in the user schema, I want to define that a user has one personal account

# lib/demo/users/user.ex
has_one :personal_account, Demo.Accounts.Account, foreign_key: :created_by_user_id

Add a members table that acts as a many to many relationship

Im calling everyone signed up to an account members. And I am adding this logic to the existing Account context

mix phx.gen.context Accounts Member members user_id:references:users account_id:references:accounts

Add a unique index because we only want a user to belong to an account once.

create unique_index(:members, [:account_id, :user_id])

Also, before I run the migration. In case a user or account is deleted, I want to remove the member row as well. So, change the ondelete to deleteall

add :user_id, references(:users, on_delete: :delete_all, type: :binary_id)