Ensure event creation requests are idempotent using transaction IDs

[mujx]

The url path of the endpoint along with the access token of the user were used as the primary key for the transactions table. The path encapsulates all the necessary information (room_id, event_type etc) to identify the correct transaction without the need to store them explicitly.

Storing them explicitly would mean a more complex schema and logic to identify the correct transaction because other endpoints with different identifiers will also use transactions.

[jimmycuadra]

I wanna make sure I'm thinking about how this handles concurrent requests correctly: If two requests come in at the same time, and both get passed the Transaction::find step without finding a record, the first request to write the completed transaction to the DB will succeed, and the second one will fail due to the primary key's uniqueness constraint? And since the transaction record is being created inside the same database transaction as the actual event record, the whole thing will be rolled back and no duplicate event will be created.

A smaller question: Is it worth naming the id column in the transactions table path?

This is great work! Thank you, as always!

[mujx]

Yes, but in order for the database transaction to fail as you described the requests must have the same url path and the same sender (i.e access_token) because the path and the access token are both used as a composite primary key.

I was between those two for the transaction's column. I will change it to path.

Happy to help!