[BUG] Atomic Payment Processing with Conflict Resolution

[oomokaro1]

[BUG] Atomic Payment Processing with Conflict Resolution

Priority: High

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

Requirements

1. Transactional Processing

• Wrap the entire processPayment function body in a Drizzle transaction
• Use db.transaction(async (tx) => { ... }) for atomic session update + payment insert
• If either operation fails, both are rolled back

2. Optimistic Locking

• Before updating the session, verify status = 'pending' within the same transaction
• Use SELECT ... FOR UPDATE (pessimistic) or check affected row count (optimistic)
• If the session is no longer pending (another payment got there first), skip this payment and log a warning

3. Idempotency Key

• Add a payment_idempotency_key column to the payments table: UNIQUE(tx_hash, session_id)
• Before inserting, check if a payment with the same idempotency key already exists
• If it exists, skip the insert (idempotent operation)
• Run a Drizzle migration to add the unique constraint

4. Processing Status

• Add a processing status to the sessionStatusEnum
• When a payment is detected: set status to processing first, then to paid after payment record is inserted
• This prevents concurrent payments for the same session from both succeeding

5. Recovery Job

• Implement a periodic job (every 5 minutes) that:
  • Finds sessions stuck in processing status for > 5 minutes
  • Checks Horizon to see if the payment actually confirmed
  • If confirmed: insert the missing payment record, set status to paid
  • If not confirmed: set status back to pending (allow re-detection)
  • Log all recovery actions

6. Duplicate Payment Handling

• If a payment arrives for an already-paid session:
  • Log a warning with the session ID and tx hash
  • Do NOT update the session status (it's already paid)
  • Do NOT insert a duplicate payment record
  • Return gracefully (no error thrown)

7. Testing

• Unit tests: transaction rollback on failure, idempotency check, processing status transitions
• Integration tests: concurrent payment submissions (2 payments for same session simultaneously)
• Test recovery job: stuck processing sessions are recovered correctly
• Test duplicate payment: same tx hash submitted twice is handled gracefully
• Use jest.useFakeTimers() for the recovery job timing

[JuliobaCR]

Hi team,

I'd like to take this one. I went through payment-detector.service.ts and the schema, and the gap matches the description exactly: processPayment does an update then an insert as two independent, non-transactional statements, with no status check before the update and no idempotency guard before the insert.

One thing I found that's worth flagging since it's not explicit in the issue: payments.txHash already has a column-level unique() constraint (not the UNIQUE(tx_hash, session_id) composite the issue asks for). That means a duplicate payment today doesn't silently double-insert — it throws a Postgres unique-violation. But because that throw happens after the session update and before the cursor advances in startPolling's loop, the error propagates up, gets caught by the outer try/catch, and the cursor for that operation never moves — so Horizon keeps returning the same payment on every poll and the monitor effectively livelocks on it instead of just logging-and-skipping. That's the concrete failure mode requirement #6 needs to close.

My plan, mapped to the requirements:

1. Transaction: wrap the status update + payment insert in db.transaction(async (tx) => {...}), using tx for both statements so a failure on either rolls back both.
2. Optimistic locking: do the update as tx.update(checkoutSessions).set({status:'processing'}).where(and(eq(id, session.id), eq(status,'pending'))).returning() and check the returned row count instead of trusting the earlier findFirst read — if it's empty, another payment already claimed this session, log a warning, and return without touching anything else.
3. Idempotency key: add payment_idempotency_key (or just promote the existing pair to a composite unique) via a Drizzle migration — UNIQUE(tx_hash, session_id) — and explicitly SELECT for that key inside the transaction before inserting, so the duplicate path is a clean early return instead of relying on a thrown DB exception.
4. Processing status: add 'processing' to sessionStatusEnum (currently pending | paid | expired | cancelled) via migration, set it in step 2's conditional update, then transition to paid only after the payment insert succeeds inside the same transaction.
5. Recovery job: there's no @nestjs/schedule dependency yet, so I'll add it and use @Interval(5 * 60 * 1000) (or a manual setInterval to match the existing hand-rolled polling style in this service, whichever you prefer) to find sessions stuck in processing past 5 minutes, re-check Horizon via the existing StellarService.getPaymentsForAccount/verifyTransaction, and either complete or revert to pending per the spec.
6. Duplicate handling: with 1–3 in place, an already-paid session fails the conditional update in step 2 and returns early — graceful by construction, no separate special case needed.

Tests: transaction rollback on a forced insert failure, the conditional-update race (two concurrent processPayment calls for the same session, asserting only one wins), idempotency key short-circuiting a re-submitted tx hash, and the recovery job under jest.useFakeTimers() advancing past the 5-minute threshold for both the confirmed and not-confirmed branches.

Estimated time: 2-3 days as scoped, mostly in the recovery job + concurrent test setup.

Looking forward to contributing to OrbitStream.

[grantfox-oss]

🦊 GrantFox — @JuliobaCR has been assigned to this issue as part of the Official Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #9)
2. Your PR will be reviewed by the OrbitStream maintainers

Good luck! Track your progress on GrantFox.

[JuliobaCR]

Thanks for assigning this to me! I'm on it and will start working on it right away.

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Official Campaign campaign!

@JuliobaCR's PR #29 was approved and merged by @oomokaro1.

🏆 @JuliobaCR: You earned 35 FoxPoints for this contribution! Your current tier: Explorer (153 total points). Track your full progress on GrantFox.

👏 Great work, @JuliobaCR! Keep contributing to OrbitStream.