Using UUID in the User table alone while reqular Id's in other table

Questions / Help
1

I’m building a system where the user id being integers or numbers can be a risk i want it to be a uuuid

This is how my user migration is setup

defmodule MySystem.Repo.Migrations.CreateUsersAuthTables do
  use Ecto.Migration

  def change do
    execute "CREATE EXTENSION IF NOT EXISTS citext", ""

    create table(:users, primary_key: false) do
      add :id, :uuid, primary_key: true
      add :first_name, :string
      add :last_name, :string
      add :msisdn, :string
      add :id_number, :string
      add :email, :citext, null: false
      add :hashed_password, :string, null: false
      add :phone_number_verified, :boolean, default: false
      add :confirmed_at, :naive_datetime
      timestamps()
    end

    create unique_index(:users, [:email, :id_number, :msisdn])

    create table(:users_tokens) do
      add :user_id, references(:users, type: :uuid, on_delete: :delete_all), null: false
      add :token, :binary, null: false
      add :context, :string, null: false
      add :sent_to, :string
      timestamps(updated_at: false)
    end

    create index(:users_tokens, [:user_id])
    create unique_index(:users_tokens, [:context, :token])
  end
end

and this is the user schema

defmodule MySystem.Accounts.User do
  use MyStem.Schema
  import Ecto.Changeset

  schema "users" do
    field :first_name, :string
    field :last_name, :string
    field :id_number, :string
    field :email, :string
    field :msisdn, :string
    field :phone_number_verified, :boolean
    field :password, :string, virtual: true, redact: true
    field :password_confirmation, :string, virtual: true, redact: true
    field :hashed_password, :string, redact: true
    field :confirmed_at, :naive_datetime

    timestamps()
  end

using this custom schema

defmodule MySystem.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @primary_key {:id, :binary_id, autogenerate: true}
      @foreign_key_type :binary_id
      @derive {Phoenix.Param, key: :id}
    end
  end
end

then for example here is the user token schema that is using user_id as a foregn key

 @foreign_key_type :binary_id
  schema "users_tokens" do
    field :token, :binary
    field :context, :string
    field :sent_to, :string
    belongs_to :user, Realest8.Accounts.User

    timestamps(updated_at: false)
  end

The user is created but am getting the error form ther as the user token is not the error i get is as follow

[error] GenServer #PID<0.786.0> terminating
** (DBConnection.EncodeError) Postgrex expected an integer in -9223372036854775808..9223372036854775807, got <<167, 251, 185, 13, 152, 40, 73, 84, 151, 208, 177, 113, 75, 186, 52, 23>>. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
    (postgrex 0.17.2) lib/postgrex/type_module.ex:947: Postgrex.DefaultTypes.encode_params/3
    (postgrex 0.17.2) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection 2.5.0) lib/db_connection.ex:1323: DBConnection.encode/5
    (db_connection 2.5.0) lib/db_connection.ex:1423: DBConnection.run_prepare_execute/5
    (db_connection 2.5.0) lib/db_connection.ex:1527: DBConnection.run/6
    (db_connection 2.5.0) lib/db_connection.ex:656: DBConnection.parsed_prepare_execute/5
    (db_connection 2.5.0) lib/db_connection.ex:648: DBConnection.prepare_execute/4
    (postgrex 0.17.2) lib/postgrex.ex:340: Postgrex.query/4
    (ecto_sql 3.10.1) lib/ecto/adapters/sql.ex:955: Ecto.Adapters.SQL.struct/10
    (ecto 3.10.3) lib/ecto/repo/schema.ex:764: Ecto.Repo.Schema.apply/4
    (ecto 3.10.3) lib/ecto/repo/schema.ex:377: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto 3.10.3) lib/ecto/repo/schema.ex:273: Ecto.Repo.Schema.insert!/4

any thoughts?

2

I think you want something like this:

belongs_to :user, Realest8.Accounts.User, type: :binary

3

does this on top of the schema not handle that
@foreign_key_type :binary_id ??

4

You have a typo:

defmodule MySystem.Accounts.User do
  use MyStem.Schema

MyStem → MySystem

But this is probably for the post, not the code.
Try using Ecto.Schema and in schema for token add
@foreign_key_type :binary_id
and
belongs_to(:user, MySystem.Accounts.User)