[sgarland]

So have a pending table with id, user_id, created_at, and index the latter two as a composite key. SELECT id FROM pending WHERE user_id = ? ORDER BY created_at DESC LIMIT 1.

Preferably delete the row once it's been permanently stored.

Keeping an actual transaction open for that long is asking for contention, and the idea of having data hanging around ephemerally in memory also seems like a terrible idea – what happens if the server fails, the pod dies, etc.?

[tpm]

> So have a pending table

With UUIDs there is absolutely no need for that.

> Keeping an actual transaction open for that long is asking for contention

Yes, which is why this is not an actual db transaction, it's a business transaction as mentioned.

> and the idea of having data hanging around ephemerally

The data is not ephemeral of course. But also the mapping between business transaction and model is not 1:1, so while there is some use for the transaction ID, it can't be used to identify a particular model.

[sgarland]

> With UUIDs there is absolutely no need for that.

Except now (assuming it’s the PK) you’ve added the additional overhead of a UUID PK, which depending on the version and your RDBMS vendor, can be massive. If it’s a non-prime column, I have far fewer issues with them.

> The data is not ephemeral of course.

I may be misunderstanding, but if it isn’t persisted to disk (which generally means a DB), then it should not be seen as durable.

[tpm]

The whole content of the business transaction is persisted as a blob until it's "done" from the business perspective. After that, entities are saved(,updated,deleted) to their respective tables, and the blob is deleted. This gives the users great flexibility during their workflows.

Yes the overhead of UUIDs was something I mentioned already. For us it absolutely makes sense to use them, we don't anticipate to have hundreds of millions of records in our tables.