How to use unique_index wisely to grasp our business logic

How do you ensure a unique index when the user tries to get more than one ticket for a paid conference? And the user gets multiple tickets for a free conference?

What are our goals here?

• sent paid conference to Ticket.changeset/1 to ensure the status: :paid, create one.
• sent free conference to Ticket.changeset/1 to ensure the status: :free, create many.

How can we apply these to our business logic:

create table(:tickets) do
  add :conference_id, references(:conferences), null: false
  add :user_id, references(:users), null: false
  add :status, :string, null: false, default: "free"
end

create unique_index(:tickets, [:conference_id, :user_id, :status], where: "status = 'paid'")

Now, how can we test?

iex> Ticket.changeset(%{conference: %{is_paid: false}, user: %{...}, status: :free}) |> Repo.insert()
[debug] QUERY OK
{:ok, 
  %Ticket%{id: 1, status: :free, conference_id: 1, user_id: 1}
}
iex> Ticket.changeset(%{conference: %{is_paid: false}, user: %{...}, status: :free}) |> Repo.insert()
[debug] QUERY OK
{:ok, 
  %Ticket%{id: 2, status: :free, conference_id: 1, user_id: 1}
}
iex> Ticket.changeset(%{conference: %{is_paid: true}, user: %{...}, status: :paid}) |> Repo.insert()
[debug] QUERY OK
{:ok, 
  %Ticket%{id: 3, status: :paid, conference_id: 2, user_id: 1}
}
iex> Ticket.changeset(%{conference: %{is_paid: true}, user: %{...}, status: :paid}) |> Repo.insert()
[debug] QUERY ERROR
** (Ecto.ConstraintError)

Awesome!

Reference

• Ecto.Migration.index/3