Exemplo de função para implementar idempotência no banco

idempotency.ex

def get_cache_or_insert(idempotency_key, request_hash) do
  Multi.new()
  |> Multi.run(:query, fn _ -> 
    query = from(
      entry in IdempotencyEntry,
      left_join: cache in IdempotencyCache,
      on: cache.entry_key == entry.key,
      select: fragment("CASE WHEN request_hash != ? THEN 'conflict' WHEN cache = NULL THEN 'no_cache' ELSE cache END", request_hash)
    )
        
    Repo.one(query)
  end)
  |> Multi.run(:insert, fn
     %{query: "no_cache"} -> 
       {:error, :no_cache}
      
     %{query: "conflict"} ->
       {:error, :conflict}
    
     %{query: nil} -> 
       %IdempotencyEntry{key: idempotency_key, request_hash: request_hash}
       # Se tiver conflito o insert vai retornar uma struct com o ID nulo porque não conseguiu inserir
       |> Repo.insert(on_conflict: :nothing)
       |> deal_with_conflict()
          
     %{query: cache} ->
        {:ok, cache}
  )
  |> Repo.transaction()
end

defp deal_with_conflict({:ok, %{key: nil}}) do
  IdempotencyCache
  |> Repo.get(idempotency_key)
  |> Map.get(:cache)
  |> case do
    nil -> {:error, :no_cache}
    cache -> {:ok, cache}
  end
end

defp deal_with_conflict({:ok, _entry}), do: {:ok, :inserted}
defp deal_with_conflict(error), do: error