Well, I'll give another take.

If you do not have two separate forms of identifier AND you have a "public" API (including basically any client apps, frontend JS, or anything found in query params) then you are making compliance with European regulators a massive headache when it comes to erasure of PII, since shared identifiers must be destroyed one way or another. Trying to merely delete the records is complicated by the fact that you need legal holds to comply with various federal laws.

Between the work involved in using two identifiers, one for joins and one for external lookups, versus the work involved in manually coding up all sorts of erasure work arounds, something I was in charge of in the past, I would strongly consider just using two IDs.

If your ORM gets in the way, just modify the ORM. This is easier than you'd think. For example, in Django just make a helpers module with something like this:

    class OurModel(Model):
        def get(...):

And have some sort of programatic way (lint, etc) of ensuring that your models.py doesn't use the stock class. It's simpler. It ruffles some feathers at first, but if your framework is getting in the way of a real use case just change the framework and don't worry about it.