Enforce strict optimistic locking with bounded retries for transition writes

[HunteRoi]

Summary

Implement strict optimistic locking with bounded retries for all membership state transition writes performed by either the Discord bot or the backend API.

Problem

DataDrop has two behavior entrypoints that can write business state:

• Discord bot runtime
• backend API used by the web app

Both can operate on the same member lifecycle data. Without deterministic concurrency control, race conditions can produce:

• invalid state transitions
• lost updates
• audit inconsistencies
• broken verification or override behavior

The specification already requires strict optimistic locking with retry. This issue implements that requirement.

Why This Matters

This is a platform integrity issue.

It protects:

• state machine correctness
• transition audit consistency
• idempotent behavior under concurrent commands/events
• predictable resolution of bot/API write races

Without this, the rest of the lifecycle rules are unreliable.

Required Behavior

1. Every transition write must validate expected current state.
2. Every transition write must validate expected aggregate version or equivalent concurrency token.
3. On version/state conflict, the writer must re-read the latest record and retry under a bounded retry policy.
4. If retries are exhausted, the write must not silently continue.
5. Exhausted retries must route to the dead-letter path for operator remediation.

Acceptance Criteria

1. Concurrent writes from bot and API do not produce invalid or partially applied lifecycle transitions.
2. A stale writer fails its compare-and-set check.
3. Conflict path retries with a fresh read under bounded retry count.
4. Retry exhaustion creates a dead-letter/remediation artifact.
5. Audit trail still reflects the final accepted transition consistently.

Suggested Implementation Targets

• src/services/PostgresDatabaseService.ts
• prisma/schema.prisma
• API write handlers to be created
• lifecycle transition application layer to be created or refactored

Suggested Technical Direction

Use optimistic locking on the canonical membership aggregate.

At minimum, each write path should include:

• aggregate id (guildId + userId, or equivalent scoped entity)
• expected version
• expected state
• transition payload
• idempotency key if available

Write succeeds only if version and expected state match current persisted values.

Validation

• unit test: stale version write is rejected
• unit test: valid retry succeeds after refresh
• integration test: simultaneous bot/API transition attempts converge safely
• integration test: retry exhaustion creates dead-letter record
• regression test: successful writes still preserve audit trail order

Traceability

• Spec: docs/specs/issue-93-specification.md
• Matrix rule: TRC-026
• Related docs:
  • docs/specs/traceability-matrix.md
  • docs/specs/issue-drafts.md
  • docs/specs/state-machine.md
  • docs/specs/architecture.md

Related Issues

• Umbrella Epic - Feature List #93
• Guard manual implicit verification to VerificationPending with mandatory email check #131
• Implement global one-time verification identity across all DataDrop guilds #128