[BUG] Atomic Payment Processing + Reconciliation

[oomokaro1]

[BUG] Payment Detection Race Condition + Reconciliation Job

Priority: High

Difficulty: Hard
Estimated Effort: 2-3 days
Relevant Packages: OrbitStream_backend/
Labels: bug, data-integrity, priority:high

Requirements

1. Transactional Processing

Wrap in Drizzle transaction with SELECT ... FOR UPDATE on the session row.

2. Idempotency Check

Check for existing payment with same tx_hash before inserting.

3. Reconciliation Job

Periodic job (every 5 minutes) that:

• Finds sessions with status = 'paid' but no matching payment record
• Checks Horizon to verify if payment actually confirmed
• If confirmed: inserts missing payment record
• If not confirmed: reverts session to pending
• Logs all recovery actions

4. Duplicate Payment Handling

If payment arrives for already-paid session: log warning, skip, return gracefully.

5. Testing

• Concurrent payment submissions
• Recovery job with orphaned sessions
• Duplicate payment handling