Why Region IDs Break OpenAPI Integrations and How to Design Resilient Solutions

Preface

I recently joined a new project that requires syncing partner data such as enterprises, orders, contracts, and logistics to our platform via OpenAPI, so we can provide financial services. Because our team and the partner's team are in different cities, we held multiple video meetings and discovered a seemingly minor enterprise registration region id field that halted progress.

1. Region Issue

Our enterprise table stores a mandatory region id selected by the user during registration; the backend later resolves the id to a region name for display. The partner's system has its own region table with different primary keys, so they cannot directly provide our region id.

We first considered having the partner pull all our region data and persist it locally, then translate their region names to our ids before calling the enterprise upload API. The partner rejected this because it added unnecessary work.

1.2 Name‑Based Lookup

We then tried calling our region query API by name to obtain the id. This raised two problems: incomplete names (e.g., "成都" vs. "成都市") requiring fuzzy matching that could hurt performance, and ambiguous names (e.g., "北京市" matching both province‑level and city‑level records).

1.3 National‑Standard Code Lookup

Another colleague suggested using the national standard code (GB/T 2260) to query the region id. Due to time pressure we adopted this approach without full evaluation.

1.4 Passing the Standard Code Directly

The partner later proposed sending the region national code instead of the id, arguing that the code is unique nationwide and would guarantee consistency.

2. A New Concern

Region data evolves: city names change (e.g., "襄樊" to "襄阳") and administrative adjustments alter codes. Our region table was initialized two years ago and never updated, while the partner updates theirs regularly, leading to mismatched data and failed lookups.

2.1 Synchronized Updates

Running a synchronized job on both sides would solve the mismatch, but it creates tight coupling and failure‑propagation risks, especially when multiple partners are involved.

2.2 One‑Side Authority

Designating one side as the source of truth also fails if the other side cannot reach the region API during an upload, causing inconsistency.

3. Other Solutions Explored

3.1 Store Snapshots

We considered storing partner data snapshots in MongoDB as JSON while keeping our core data in MySQL. However, the product team noted that auditors only review MySQL tables, making this approach infeasible.

3.2 Manual Updates

If a region is missing during an upload, we could alert a human to manually add or modify the region data, but this is unreliable outside business hours.

3.3 Provide an Update API

We thought about exposing an API for the partner to add or modify regions before uploading enterprise data, but security concerns prevented us from allowing external writes to our base data.

4. Final Solution

We added two optional fields to the enterprise upload API: region national code and region name. The partner now sends both. Our backend attempts to resolve the id via the national code; if that fails, it stores the provided region name. The region id field remains optional.

5. Summary

5.1 Design APIs from the User Perspective

Choose universally accepted parameters (e.g., national codes) rather than custom IDs to reduce integration friction.

5.2 Build Flexible Technical Solutions

In distributed systems, strict strong consistency often leads to failure; a tolerant, asynchronous approach is more robust.

5.3 No Perfect Solution, Only the Right Fit

Our final design does not guarantee a region id for every case, but having the name as a fallback satisfies business needs.

5.4 Communicate Effectively

When stuck on a technical issue, pause the discussion, move on, and revisit later with fresh ideas.

5.5 Technology Serves Business

Technical decisions must align with business constraints; otherwise they become theoretical exercises.