Let’s say you have a one-to-many relationship between Day and Todo: i.e., one day has many todos. The order of the todos of a day is important. The todos must be retrieved from the database ordered in a specific way, that was determined when the todos were inserted into the database. Additionally, todos can be deleted, inserted and moved.

I have a similar setup in my application, at the moment. I rely on the order of certain items when they are retrieved from the database and I update their relative position whenever necessary. Currently, I update the positions of the items manually. When an items is, for example, deleted, all the items down the list shift their position with -1.

I was wondering if there is a better way of doing this. Possibly a solution that is built into Postgres or Ecto, for example. I am hoping to reduce the risk of problems with data integrity. I have been looking for a built-in solution, but have not been able to find confirmation that is does or does not exist.

Do you happen to know a better way?

Ty.