Adding a new relationship with multitenancy at the same time generates invalid_foreign_key

[rgraff]

Describe the bug
When adding a belongs_to relationship and using the attribute for multitenancy, if you generate single migrate all at once, it is invalid. If you break it up--add the relationship, generate a migration, add multitenancy, and generate a 2nd migration--then the individual migrations are valid.

To Reproduce

defmodule MyApp.Environments.Account do
  use Ash.Resource, data_layer: AshPostgres.DataLayer, authorizers: [Ash.Policy.Authorizer]

  postgres do
    table "accounts"
    repo MyApp.Repo
  end
  
  attributes do
    uuid_primary_key :id
    attribute :key, :ci_string, allow_nil?: false
  end

  identities do
    identity :key, [:key]
  end
end

Initial migration looks like:

def up do
  create table(:accounts, primary_key: false) do
    add :id, :uuid, null: false, default: fragment("uuid_generate_v4()"), primary_key: true
    add :key, :citext, null: false
    add :name, :text
    add :inserted_at, :utc_datetime_usec, null: false, default: fragment("now()")
    add :updated_at, :utc_datetime_usec, null: false, default: fragment("now()")
  end

  create unique_index(:accounts, [:key], name: "accounts_key_index")
end

Add in a relation...

  relationships do
    belongs_to :environment, MyApp.Tenants.Environment do
      api MyApp.Tenants
      allow_nil? false
    end
  end

Second migration looks good...

def up do
  alter table(:accounts) do
    add :environment_id,
        references(:environments,
          column: :id,
          name: "accounts_environment_id_fkey",
          type: :uuid,
          prefix: "public"
        ),
        null: false
  end
end

Turn on multitenancy...

  multitenancy do
    strategy :attribute
    attribute :environment_id
  end

Third migration looks good...

def up do
  drop_if_exists unique_index(:accounts, [:key], name: "accounts_key_index")

  create unique_index(:accounts, [:environment_id, :key], name: "accounts_key_index")
end

However if you were to generate the migrations all at once, you'd get...

def up do
  create table(:accounts, primary_key: false) do
    add :id, :uuid, null: false, default: fragment("uuid_generate_v4()"), primary_key: true
    add :key, :citext, null: false
    add :name, :text
    add :inserted_at, :utc_datetime_usec, null: false, default: fragment("now()")
    add :updated_at, :utc_datetime_usec, null: false, default: fragment("now()")

    add :environment_id,
        references(:environments,
          column: :id,
          with: [environment_id: :tenant_id],
          match: :full,
          name: "accounts_environment_id_fkey",
          type: :uuid,
          prefix: "public"
        ),
        null: false
  end

  create unique_index(:accounts, [:environment_id, :key], name: "accounts_key_index")
end

And that migration gives you this error:

22:56:57.955 [info] create table accounts
** (Postgrex.Error) ERROR 42830 (invalid_foreign_key) there is no unique constraint matching given keys for referenced table "environments"

The reference for the :environment_id column has a with: [environment_id: :tenant_id], match: :full which you don't get running generating multiple migrations.

Expected behavior
I expect the migration generator to generate a valid migration.

** Runtime

• Elixir version: 1.14.4
• Erlang version: 25
• OS: Ubuntu 12.12
• Ash version: 2.10.2
• Ash Postgres: main (commit: a09dae4)
• Postgres: PostgreSQL 12.12

Additional context
Add any other context about the problem here.

[zachdaniel]

That is pretty strange...I'll leave this one up for grabs and mark it as a good first issue, as I don't have time to fix it. There is a workaround as you've stated.