Updating Ecto associations on Postgresql

My concern with working with associations in this case is having to update the record, I came up with a solution and would like feedback on it to see if it is correct or even viable.

I’m working on a project where users have inventories, obviously in their inventories they have items, I wanted to keep track of how much of an item an user has, so my schema goes as follows:

User:

  schema "users" do
    ...

    has_one(:inventory, Inventory)

    timestamps()
  end

Inventory:

  schema "inventories" do
    field(:item_quantities, {:array, :map}, default: [])
    belongs_to(:user, User)

    many_to_many(:items, Item, join_through: "inventory_items")

    timestamps()
  end

Item:

  schema "items" do
    ...

    many_to_many(:inventories, Inventory, join_through: "inventory_items")

    timestamps()
  end

The solution I came up with to keep track of the amount of a item an user has is with the field(:item_quantities, {:array, :map} part. With a common module I update this value so it is less error prone when updating the values (adding, removing, etc…) These are the relevant functions on the module:

  @doc """
  Updates 'item_id' on 'inventory' adding 'amount' to it.
  """
  def add_inventory_item_amount(%Inventory{} = inventory, item_id, amount) do
    updated_items =
      Enum.map(inventory.item_quantities, fn item ->
        case item["id"] == item_id do
          true ->
            %{item | "amount" => item["amount"] + amount}

          false ->
            item
        end
      end)

    inventory
    |> Ecto.Changeset.change(%{item_quantities: updated_items})
    |> Repo.update!()
  end

  @doc """
  Updates 'item_id' on 'inventory' removing 'amount' from it.
  """
  def remove_inventory_item_amount(%Inventory{} = inventory, item_id, amount) do
    updated_items =
      Enum.map(inventory.item_quantities, fn item ->
        case item["id"] == item_id do
          true ->
            %{item | "amount" => item["amount"] - amount}

          false ->
            item
        end
      end)

    inventory
    |> Ecto.Changeset.change(%{item_quantities: updated_items})
    |> Repo.update!()
  end

  @doc """
  Adds 'item_amount' to 'inventory' of 'item'. Mostly used to add new items to the inventory.
  """
  def add_item_to_inventory(%Item{} = item, %Inventory{} = inventory, item_amount) do
    params = %{id: item.id, amount: item_amount}

    inv = Repo.preload(inventory, :items)

    inv
    |> Ecto.Changeset.change(%{item_quantities: [params | inv.item_quantities]})
    |> Ecto.Changeset.put_assoc(:items, [item | inv.items])
    |> Repo.update!()
  end

My question is, is this a valid way to do what I want to do or can I work directly with the associations without keeping track on a separate field on the schema?

I would make inventory_items a schema so it can have an amount attribute. Then you’d join through the schema rather than a raw table name e.g. many_to_many(:inventories, Inventory, join_through: InventoryItems). If you do this, I don’t think you actually need an inventories table, unless that will have some other attribute you don’t show (such as an inventory type or name).

Yes, this is what I was looking to do. I’m not discarding having an inventory table as we may have another use for it later on, but you’re right, as for now I don’t think it’d be 100% necessary. Thanks!