Migration script places unique index below relationships they're needed for

[florid-sojourner]

Describe the bug
Migration produces unique indexes too late in the script for relationships that depend on them.

To Reproduce
I've got a recource that is a many-to-many table. I also need it to contain items as a linked list. So I created a unique constraint on the id for the foreign key. The framework generates a migration, but creates the line to add the unique constraint AFTER the alter table statement that creates the foreign key to self. So the migration fails unless I move the create unique index statement above the alter table.

defmodule MyProj.TemplateManager.TemplatePhase do
use Ash.Resource,
datalayer: AshPostgres.DataLayer

postgres do
  table "templatephase"
  repo MyProj.Repo
end

attributes do
  uuid_primary_key :id
  attribute :name, :string do
    allow_nil? false
  end
end

identities do
  identity :id, [:id]
end

relationships do
  belongs_to :template, MyProj.TemplateManager.Template, primary_key?: true, allow_nil?: false
  belongs_to :phase, MyProj.TemplateManager.Phase, primary_key?: true, allow_nil?: false
  belongs_to :template_phase, __MODULE do
    source_attribute :follows
    destination_attribute :id
    attribute_writable? true
    allow_nil? true
  end
end

actions do
  defaults [:create, :read, :update]
end

end

Which produces the migration file with the relevant parts in this order:

alter table(:template_phase) do
  modify :phase_id,
         references(:phase,
           column: :id,
           name: "template_phase_phase_id_fkey",
           type: :uuid,
           prefix: "public"
         )

  modify :follows,
         references(:template_phase,
           column: :id,
           name: "template_phase_follows_fkey",
           type: :uuid,
           prefix: "public"
         )
end

create unique_index(:template_phase, [:id], name: "template_phase_id_index")

Expected behavior
If I move the bottom line to the top of this exerpt, the migration succeeds and I get this DB structure (otherwise the migrate command fails because the unique constraint has yet to be applied):

Table "public.template_phase"
Column | Type | Collation | Nullable | Default
-------------+------+-----------+----------+--------------------
id | uuid | | not null | uuid_generate_v4()
name | text | | not null |
template_id | uuid | | not null |
phase_id | uuid | | not null |
follows | uuid | | |
Indexes:
"template_phase_pkey" PRIMARY KEY, btree (id, template_id, phase_id)
"template_phase_id_index" UNIQUE, btree (id)
Foreign-key constraints:
"template_phase_follows_fkey" FOREIGN KEY (follows) REFERENCES template_phase(id)
"template_phase_phase_id_fkey" FOREIGN KEY (phase_id) REFERENCES phase(id)
"template_phase_template_id_fkey" FOREIGN KEY (template_id) REFERENCES template(id)
Referenced by:
TABLE "template_phase" CONSTRAINT "template_phase_follows_fkey" FOREIGN KEY (follows) REFERENCES template_phase(id) (edited)
Jump
general

** Runtime

• Elixir version: 1.16.2
• Erlang version: 26
• OS: Ubuntu 22
• Ash version: 2.20.1
• Ash Postgres: 1.5.17

Additional context
Add any other context about the problem here.

[zachdaniel]

Thanks for the report! I don't have much time to look into this issue specifically, and since there is a reasonable workaround, it's not super high priority. I'm going to mark it as a good first issue, so if anyone is inclined to take a stab at it that would be great :)