Improve deployment hard limit enforcement to prevent concurrent race conditions

Context

This issue tracks an improvement identified during code review of PR #853.

Related PR: https://github.com/wso2/api-platform/pull/853
Related Comment: https://github.com/wso2/api-platform/pull/853#discussion_r2751556691
Requested by: @dushaniw

Problem

In platform-api/src/internal/repository/api.go (CreateDeploymentWithLimitEnforcement method), the current hard limit enforcement can still allow the limit to be exceeded in the following scenarios:

1. When there are insufficient archived deployments to delete (fewer than needed to make room)
2. When concurrent inserts occur before the transaction completes

The current logic:

• Counts total deployments
• If at/over hard limit, deletes up to 5 oldest archived deployments
• Inserts the new deployment

This doesn't re-check the count after deletion or prevent concurrent inserts from bypassing the limit.

Suggested Improvement

1. Acquire a lock for the API+gateway+org combination (e.g., SELECT ... FOR UPDATE or advisory lock)
2. Calculate exact number of deletions needed: neededDeletes = (count + 1) - hardLimit
3. Fetch and delete exactly that many archived deployments
4. Re-check the count after deletion
5. If there aren't enough archived rows to make room, return a clear limit-reached error instead of proceeding with the insert

Priority

Enhancement - to be addressed in a future PR