Our data model has users. We use an external API (I'll call it Z) to handle payments. We create users in Z and have a mapping table that links our internal IDs to Z IDs. This works fine when there is a 1-to-1 association between "environments."
The problem is that Z provides us one testing environment called "staging." But we have multiple environments, "sandbox", "staging", each dev's local, etc. Ideally we could point the various environments to Z's staging, but then the mapping tables will be wrong in each environment. Each environment has a different user base and the emails could clash and point to the wrong Z IDs. Z provides no delete (or archive) functionality either.
How can we manage those mapping tables in this situation?
This is a common problem. It happens not only when dealing with external systems, but very often internal systems as well.
You really only have two choices. Disallow contact with the external system except from your Staging environment, or allow contact with the external system from multiple environments.
Since you want to do the latter, you have to accept that your mapping tables in each of your environments will not match ID for ID with the external Staging environment. This shouldn't be a problem unless you have some requirement that you have the exact same number of IDs in your mapping table as in the external environment. If this is your case, then you are stuck with option 1.
More likely, there is no real need that every ID in the external environment have a corresponding entry in the same mapping table. In this case, you are really only concerned that every ID in a mapping table have a corresponding ID in the external Staging environment.
You can prevent collisions by creating the ID in the external system before creating it in your mapping table. If the ID is already taken, require the user to pick a different one.