How to set up has_many, through: with only unique combinations

I'm trying to set up 2 models (Entities, Users) where each user can work with several entities and each entity can have several users working with them. I want the relationships to be unique, i.e. a user can not work 2 times with the same entity and an entity can't have the same user twice.

I'm using a has_many through relationship and I'm storing extra information like access rights in the "through" class.

class Entity < ApplicationRecord
  has_many :user_entity_roles, dependent: :destroy
  accepts_nested_attributes_for :user_entity_roles
  has_many :users, through: :user_entity_roles, dependent: :restrict_with_error
end

class User < ApplicationRecord
  has_many :user_entity_roles, dependent: :destroy
  has_many :entities, through: :user_entity_roles, dependent: :restrict_with_error
end

class UserEntityRole < ApplicationRecord
  belongs_to :entity, touch: true
  belongs_to :user
end

Now through an error in a form I was building I created a duplicate relationship. Of course I can try to prevent this by not having errors in my forms but I wonder if there is a way to force only unique combinations of Users and Entities?

Yes.

Adding a unique index on entity_id and user_id will enforce uniqueness on the database level:

class AddUniqueCompoundIndexToUserEntityRole < ActiveRecord::Migration[7.0]
  def change
    add_index(:user_entity_roles, [:entity_id, :user_id], unique: true)
  end
end

Note that your database won't let you add this index until you fix the duplicate data - for example by deleting the duplicate rows.

The index will cause the database to reject any duplicate data which will cause a database driver error. To prevent that error and provide better user feedback you want an application level validation:

class UserEntityRole < ApplicationRecord
  belongs_to :entity, touch: true
  belongs_to :user
  validates_uniqueness_of :entity_id, scope: :user_id
end

The validation will catch most of the potential duplicates but doesn't guarentee uniqueness on it's own since it's prone to race conditions.

If you want to trigger this validation when saving an Entity you can use validates_associated:

class Entity < ApplicationRecord
  has_many :user_entity_roles, dependent: :destroy
  accepts_nested_attributes_for :user_entity_roles
  has_many :users, through: :user_entity_roles, dependent: :restrict_with_error
  validates_associated :user_entity_roles
end