Spike: Determine approach and effort to fix duplicated IDs for content types with Unique fields

[john-thomas-dotcms]

Parent Issue

No response

Task

Main issue: https://github.com/dotCMS/core/issues/29459

The Landscape

• There are several different approaches to resolve this bug.
  • We need to discuss the approaches and identify the approach to take.
• We don't know for sure what the cause of the bug is.
  • The issue is rare enough that we can't reproduce it, even on the systems of customers that have experienced it repeatedly.
• We do know that the way we currently check for uniqueness is likely to fail at times.
  • When a user creates a new contentlet of a content type with a Unique field, in order to determine if there's a duplicate value for the unique field, we do a query against the ES index.
    • But we know that indexes can become corrupted, and that there can be race conditions where content which has already been saved to the DB has not yet been indexed.

Potential Fixes

So far, the following potential fixes have been proposed:

1. Just change the query from an ES query to a DB query.
   • Pros:
     • Effort: Probably the lowest effort solution.
     • Risk: Likely the lowest risk solution (less likely to impact any other features).
   • Cons:
     • Uncertainty: Since we don't know the cause of the issue, we can't be 100% sure it will fix it.
2. Add a column to the table in the DB and add a DB constraint to ensure uniqueness on that column.
   • Pros:
     • There's some chance it will prevent some issues that option 1 won't.
       • But we don't actually know that it will be any better - or even how likely it is that it's better
   • Cons:
     • Effort: Higher effort than option 1.
     • Risk: Higher risk than option 1.
     • Uncertainty: Since we don't know the cause of the issue, we can't be 100% sure it will fix it.

Proposed Objective

Core Features

Proposed Priority

Priority 2 - Important

Acceptance Criteria

No response

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

No response

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response