I am attempting to fulfill a test where a user can create a prescription if and only if the user has a role of “Prescription Approval Role” with a permission of “approve_scripts”. In order to fulfill the test, I have the following factories setup using ex_machina for elixir:

      def user_factory do
        %User{
          id: Ecto.UUID.generate(),
          emr_id: Ecto.UUID.generate(),
          username: sequence(:user_username, &"user-#{&1})"),
          name_first: sequence(:user_name_first, &"user-#{&1})"),
          name_middle: sequence(:user_name_middle, &"user-#{&1})"),
          name_last: sequence(:user_name_last, &"user-#{&1})"),
          roles: [build(:role)],
          type: "practice"
        }
      end

      def permission_factory do
        %Permission{
          key: "approve_scripts",
          label: "Prescription Approval Permission",
          description: "Permission to approve scripts"
        }
      end

      def role_factory do
        %Role{
          label: "Prescription Approval Role",
          description: "Role needed to approve prescriptions",
          permissions: [build(:permission)]
        }
      end

Here is the schema that corresponds and maps to my factories:

schema "users" do
    field :emr_id, :string
    field :username, :string
    field :name_first, :string
    field :name_middle, :string
    field :name_last, :string
    field :type, Ecto.Enum, values: @user_types

    many_to_many :roles, Role, join_through: "users_roles", on_replace: :delete

    has_many :permissions, through: [:roles, :permissions]

    timestamps()
  end

   schema "roles" do
    field :label, :string
    field :description, :string

    many_to_many(:permissions, Permission,
      join_through: "roles_permissions",
      join_keys: [role_id: :id, permission_key: :key],
      on_replace: :delete
    )

    many_to_many(:users, User, join_through: "users_roles")

    timestamps()
  end
   
  schema "permissions" do
    field :label, :string
    field :description, :string

    timestamps()
  end

When I create any test that utilizes insert(:user) I receive the following error:

    ** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "inserted_at" of relation "roles_permissions" violates not-null constraint

         table: roles_permissions
         column: inserted_at

     Failing row contains (a63ceb35-3e7f-428d-8328-05e05a3fe6bb, approve_scripts, null, null).

The issue is simple to me. Basically, the underlying join table of roles_permissions doesn’t have an inserted_at or updated_at field. However, I don’t know what needs to be done to fix this. I would think that those fields would be inserted automatically so it doesn’t make sense why they would be null. Am I needing to create a roles_permissions file and table? Any help or guidance would be appreciated thank you.

EDIT

This is the current database schema in elixir code:

create table(:users) do
      add :emr_id, :citext
      add :username, :citext, null: false
      add :name_first, :text, null: false
      add :name_middle, :text
      add :name_last, :text, null: false
      add :type, :user_type, null: false
      timestamps()
    end

    create table("roles") do
      add :label, :text, null: false
      add :description, :text, null: false
      timestamps()
    end

    create table("permissions", primary_key: false) do
      add :key, :text, primary_key: true
      add :label, :text, null: false
      add :description, :text, null: false
      timestamps()
    end

    create table("roles_permissions", primary_key: false) do
      add :role_id, references(:roles), primary_key: true
      add :permission_key, references(:permissions, column: :key, type: :text), primary_key: true
      timestamps()
    end

    create table("users_roles", primary_key: false) do
      add :user_id, references(:users), primary_key: true
      add :role_id, references(:roles), primary_key: true
      timestamps()
    end
  end