Concurrent platform-link creation can assign duplicate display orders and corrupt link ordering

[Ridanshi]

Summary

Platform link creation determines the next display order using a read-before-write workflow that is not transactionally protected.

Concurrent requests can assign the same display order to multiple links.

Affected Files

• profileService.ts
• schema.prisma

Root Cause

The creation flow calculates the current maximum display order and then inserts a new record separately.

Concurrent requests can observe the same maximum value and both create links using identical display orders.

No transactional enforcement or uniqueness constraint prevents this condition.

Reproduction

1. Create multiple platform-link creation requests simultaneously for the same user.
2. Allow both requests to execute concurrently.
3. Inspect the resulting displayOrder values.
4. Observe duplicate ordering assignments.

Expected Behavior

Each newly created platform link should receive a unique display order.

Actual Behavior

Concurrent requests can generate duplicate display orders.

Why This Is Difficult To Detect

Sequential testing behaves correctly.

The issue only appears under concurrent request execution.

Production Impact

• Unstable link ordering
• Ambiguous reorder behavior
• Inconsistent profile rendering
• Persistent ordering corruption

Suggested Fix

Protect display-order assignment with transactional guarantees or enforce uniqueness and retry behavior.

Severity

High

[Ridanshi]

I investigated this issue and would like to work on it.

The current platform-link creation flow appears vulnerable to concurrent display-order assignment races.

I plan to:

• review the order allocation workflow
• enforce ordering consistency under concurrency
• preserve existing link creation behavior
• add regression tests covering concurrent requests

Please assign this issue to me.

[Harxhit]

@Ridanshi I have assigned this issue to you.

[ShantKhatri]

@Harxhit @Ridanshi I'm not able to understand this issue, can you be specific?

[Ridanshi]

The Problem (Issue #485)
When two users tried to create a platform link at the same time, both requests would read the same max(displayOrder) value and then both try to insert with that same order number causing duplicate displayOrder values and corrupting the display order.

What Changed & Why

1. schema.prisma: DB Constraint (@@unique([userId, displayOrder]))
   Added a unique constraint so the database itself rejects any duplicate (userId, displayOrder) pair. This is the ground-truth enforcement layer.

2. profileService.createPlatformLink: Retry Loop
   Since two concurrent requests can still race to insert before either gets rejected, we now catch P2002 (Prisma's unique constraint violation code), re-read the current max(displayOrder), and retry up to 5 times. This handles the race gracefully instead of crashing.

3. profileService.reorderLinks: Two-Phase Transaction
   When reordering (e.g., swapping positions 2 and 3), a naive update would temporarily set both to position 2, violating the unique constraint mid-transaction. The fix: first shift all affected rows to a temporary high offset, then set the final values — so the constraint is never violated during the swap.

4. Tests

27 new tests in platform-link-ordering.test.ts covering all the new paths (retry logic, concurrent simulation, two-phase reorder)

Updated $transaction mock in profile-cache.test.ts to handle both array and callback forms (Prisma supports both)

Happy to walk through any specific file in the diff if anything is still unclear!