Idempotency is easy if you don't use mutable state in your middleware.

Auth, logging, and atomicity are all isolated concerns that should not affect the domain specific user contract with your API.

How you handle unique keys is going to vary by domain and tolerance-- and its probably not going to be the same in every table.

It's important to design a database schema that can work independently of your middleware layer.