Add support for sorting direction in custom indexes

[andreh11]

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.

Is your feature request related to a problem? Please describe.

Hi! It's really easy to specifiy custom indexes (and statements) using AshPostgres, thanks! However, I'd also like to specify the sorting direction. I'm building an AuditLog resource, and in case a of compliance query, I'd like the logs to be already sorted by date, for example.

Describe the solution you'd like

We could specify the sorting direction like this:

custom_indexes do
  index desc: :occurred_at
  index [:action, [desc: :occurred_at]]
end

And it would generate the following Ecto migration:

create index(:events, [desc: :occured_at])
create index(:events, [:action, desc: :occured_at])

I guess we'd need to modify the index DSL and the type of fields would become something like atom | String.t | keyword | list(atom | String.t | keyword).

I've never hacked Ash before, but I might try to open a PR for this, if you want.

Describe alternatives you've considered

No response

Additional context

No response

[zachdaniel]

Sounds good to me!

[ThomaseLucas]

I'm an Ash fellow, and I'm going to begin work on this issue!

[ThomaseLucas]

To solve this, I am thinking we need to widen the existing fields argument on custom_indexes index/2. We can simply match Ash’s allowed fields values to Ecto’s index column list format.

I think it would be best to widen fields rather than a new DSL option, because it's a lot less invasive to do so. Another reason I think this is a better choice, is because as I mentioned earlier, it would line up with how Ecto already handles index columns. It would also stay backward compatible, so existing declarations keep working as-is, we are just adding to what fields is able to contain.

Let me know if this solution touches everything it needs to in order to resolve the issue. If you have any ideas on other ways to solve this let me know.

[zachdaniel]

Agreed, we should update fields to be :atom | {:sort_order, :atom} 👍