AshPostgres generates duplicate columns in unique index with multitenancy enabled

[gtolarc]

Code of Conduct

• I agree to follow this project's Code of Conduct

AI Policy

• I agree to follow this project's AI Policy, or I agree that AI was not used while creating this issue.

Versions

• Elixir version: 1.18.4
• Ash version: 3.5.32
• AshPostgres version: 2.6.13
• PostgreSQL version: 17

Operating system

MacOS

Current Behavior

Description:

When using multitenancy with strategy: :attribute and defining both an identity and a custom unique index on columns that include the tenant attribute, AshPostgres generates a migration that creates an
index with duplicate columns.

Steps to Reproduce:

1. Define a resource with multitenancy and both an identity and a custom index:

defmodule MyApp.Channel do
use Ash.Resource,
data_layer: AshPostgres.DataLayer

postgres do
  table "channels"
  repo MyApp.Repo
end

multitenancy do
  strategy :attribute
  attribute :project_id
end

identities do
  identity :unique_type_per_project, [:project_id, :type]
end

attributes do
  uuid_primary_key :id
  attribute :type, :atom
end

relationships do
  belongs_to :project, MyApp.Project
end

end

2. Generate migration with mix ash.codegen
3. The generated migration creates an index with duplicate columns:

create index channels_unique_type_per_project_index on channels (project_id, project_id, type)

Expected Behavior:

The index should be created without duplicate columns:
create index channels_unique_type_per_project_index on channels (project_id, type)

Actual Behavior:

When multitenancy is enabled with attribute :project_id, the index is created with project_id duplicated:
create index channels_unique_type_per_project_index on channels (project_id, project_id, type)

This appears to happen because the multitenancy attribute is being added to the index columns even when it's already explicitly included in the identity definition.

Environment:

• Elixir version: 1.18.4
• Ash version: 3.5.32
• AshPostgres version: 2.6.13
• PostgreSQL version: 17

Impact:

1. The incorrect index name prevents Ash from properly handling unique constraint violations
2. Results in Ash.Error.Unknown instead of Ash.Error.Invalid when inserting duplicates
3. Makes it impossible to gracefully handle duplicate key errors in multi-tenant applications

Workaround:

Currently, you can work around this by:

1. Using unique_index_names to map the incorrect index name:
   unique_index_names [
   {[:project_id, :type], "channels_project_id_project_id_type_index", "Custom error message"}
   ]
2. Manually fixing the migration before running it

Additional Context:

This issue specifically occurs when the multitenancy attribute is part of the identity columns. The bug seems to be in the index generation logic that doesn't account for the multitenancy attribute
already being present in the column list.

Reproduction

No response

Expected Behavior

No response