My understanding is that only the storage layer should care about that, and only if it's a relational store. The concept of looking things up by "id" will obvio

My understanding is that only the storage layer should care about that, and only if it's a relational store. The concept of looking things up by "id" will obviously make it to the application layer but the application layer "id" isn't directly linked to the PK column of a particular SQL table, even if it's going to be the most common use case.