Is transactionId unique across Production and Sandbox environments for DB design?

Question

Created 1w

Replies 1

Boosts 0

Participants 1

Hi, I am designing a database schema to store App Store transaction data for our backend system, and I have a question regarding the uniqueness of transactionId.

According to the documentation (apple.com), transactionId is a unique identifier for a transaction. However, it is not explicitly clear whether this uniqueness is guaranteed across different environments.Could you please clarify the following points? Is transactionId guaranteed to be unique across both the Production and Sandbox environments? (i.e., Is there any possibility that the exact same transactionId is generated in both environments?) For database design, is it safe to use transactionId alone as a Primary Key? Or is it strongly recommended to use a composite key consisting of both environment and transactionId?

Any insights or best practices from Apple engineers or the community would be highly appreciated.Thank you.

Answer 1

kamesenin OP

2d

Based on my research, I have arrived at the following conclusion; is my understanding correct?

[Regarding potential transactionId collisions between Apple's Production and Sandbox environments]

Uniqueness across environments: Global uniqueness across Production and Sandbox environments is not guaranteed.

Suitability as a single primary key: Using transactionId alone as a primary key (PK) is not recommended.

Recommended schema design: A composite key of (environment, transactionId), or a surrogate key combined with a composite unique constraint.

Scope of uniqueness: According to Apple's specifications, transactionId is guaranteed to be unique within each specific environment (Production or Sandbox). However, since the Production and Sandbox environments operate as independent systems, the architectural possibility of the same numeric string being assigned—however rare—cannot be ruled out.

Database design best practice: Adopting a composite key: In your table design, include an environment column (Production/Sandbox) and configure a composite primary key (or composite unique index) based on (environment, transactionId).

Separating Tables and Databases by Environment: Mixing production data with test or review data in the same tables can lead to errors in sales aggregation, KPI analysis, and user access management. To ensure safety, it is best practice—wherever possible—to logically or physically separate data storage locations for production and sandbox environments, or to design systems that mandate the inclusion of an environment = 'Production' condition in all views and extraction queries.