[FEAT] Exactly-Once Payment Processing with Conflict Resolution

[oomokaro1]

[FEAT] Exactly-Once Payment Processing with Conflict Resolution

Priority: High

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

Requirements

1. Serializable Transaction

Wrap the entire processPayment in a Drizzle transaction with the highest isolation level:

await db.transaction(async (tx) => {
  // Lock the session row
  const [session] = await tx.execute(
    sql`SELECT * FROM checkout_sessions WHERE id = ${sessionId} FOR UPDATE`
  );

  if (session.status !== 'pending') {
    return; // Another payment already processed this session
  }

  // Update session
  await tx.update(checkoutSessions).set({ status: 'paid' })...

  // Insert payment
  await tx.insert(payments).values({ ... })...
});

2. Idempotency Check

Before processing, check if a payment with the same tx_hash already exists:

const existing = await db.query.payments.findFirst({
  where: eq(payments.txHash, op.transaction_hash),
});
if (existing) {
  this.logger.warn(`Duplicate payment ${op.transaction_hash} — skipping`);
  return;
}

3. Recovery for Stale Sessions

Implement a periodic job that finds and repairs inconsistent state:

• Sessions stuck in processing for > 5 minutes → check Horizon, recover or rollback
• Sessions marked paid with no payment record → check Horizon, insert missing payment
• Sessions with payment records but still pending → mark as paid

Run every 5 minutes using @nestjs/schedule.

4. Duplicate Payment Handling

If a payment arrives for an already-paid session:

• Log a warning
• Don't update the session
• Don't insert a duplicate payment
• Don't dispatch a duplicate webhook
• Return gracefully

5. Testing

• Concurrent payment submissions (2 payments for same session simultaneously)
• Verify only one succeeds
• Test idempotency: same tx hash submitted twice
• Test recovery job: orphaned sessions are repaired
• Test crash simulation: session stuck in processing

[alexatsejames-alt]

Hi please can you assign these issue to me

[AbelOsaretin]

I'd like to work on this issue.
The current processPayment flow in PaymentDetectorService has no concurrency protection — two simultaneous Horizon events for the same session can both pass the status === 'pending' check, resulting in duplicate payment records, double webhook dispatches, and inconsistent session state. This is a data-integrity issue that can cause merchants to receive duplicate payment.confirmed webhooks and customers to see incorrect payment status.
My approach
I will wrap the entire payment processing path in a Drizzle serializable transaction with SELECT ... FOR UPDATE row locking, add an idempotency guard on tx_hash before the transaction, and implement a periodic recovery job using @nestjs/schedule to repair stale or inconsistent session states. This ensures exactly-once processing even under concurrent Horizon events or process crashes.
Implementation plan

1. Wrap processPayment in a serializable transaction
   • In src/payments/payment-detector.service.ts, wrap the existing processPayment logic inside db.transaction()
   • Use sql\SELECT * FROM checkout_sessions WHERE id = ${sessionId} FOR UPDATE`` to acquire a row lock before reading session status
   • Check session.status !== 'pending' inside the transaction — if already paid/expired/cancelled, return early without side effects
   • Update session status to 'paid' and insert the payment record within the same transaction
   • Ensure webhook dispatch happens after the transaction commits (outside the transaction callback)
2. Add idempotency check on tx_hash
   • Before entering the transaction, query payments table for eq(payments.txHash, op.transaction_hash)
   • If a matching payment exists, log a warning and return immediately — no duplicate processing
   • This catches Horizon redelivery of the same event and prevents re-entry into the transaction
3. Implement session recovery job
   • Create src/payments/payment-recovery.service.ts with @nestjs/schedule @Cron('*/5 * * * *') decorator
   • Stale processing sessions: Query for sessions with status = 'pending' and created_at < now() - interval '5 minutes' — these may be stuck. Check Horizon for the session's memo to determine actual payment status, then mark as paid or expired accordingly
   • Paid sessions with no payment record: Query for status = 'paid' sessions where no corresponding row exists in payments — check Horizon and insert the missing payment record
   • Pending sessions with payment records: Query for sessions still pending but with a matching payment row — mark as paid
   • Log all recovery actions with the session ID and the action taken
4. Ensure duplicate webhook prevention
   • Add a webhookDispatched boolean column to checkout_sessions (or check webhook_deliveries for existing session_id + event = 'payment.confirmed' combination)
   • Before dispatching payment.confirmed, verify no delivery already exists for this session
   • This prevents double webhook delivery if the recovery job or redelivered events trigger duplicate processing
5. Register the recovery module
   • Import ScheduleModule.forRoot() in AppModule to enable @nestjs/schedule
   • Add PaymentRecoveryService to PaymentsModule providers
   • Export the recovery service so it can be tested independently
6. Write comprehensive tests
   • Concurrent payment test: simulate two Horizon events arriving simultaneously for the same session — verify only one payment record is created and session status is 'paid'
   • Idempotency test: submit the same tx_hash twice — verify second attempt is a no-op
   • Recovery test: insert a session stuck in pending with an existing payment record — verify the recovery job marks it as paid
   • Crash simulation test: insert a session with status = 'pending' older than 5 minutes — verify recovery checks Horizon and updates accordingly
   • Duplicate webhook test: verify payment.confirmed webhook is dispatched exactly once per session
     Impact

• Eliminates duplicate payment records and double webhook dispatches caused by concurrent Horizon events or redelivered messages.
• Provides automatic recovery from transient failures (process crashes, network partitions) via the periodic reconciliation job.
• Ensures merchants receive exactly one payment.confirmed webhook per session, preventing incorrect order fulfillment or balance updates.
• Aligns with payment processing best practices: row-level locking, idempotency keys, and reconciliation loops are standard patterns in financial systems.
  I'm ready to start immediately and can have a PR up within a reasonable timeline. Happy to adjust the plan if you have preferences on any of the details.

[grantfox-oss]

🦊 GrantFox — @AbelOsaretin 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 #14)
2. Your PR will be reviewed by the OrbitStream maintainers

Good luck! Track your progress on GrantFox.

[grantfox-oss]

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

@AbelOsaretin's PR #26 was approved and merged by @oomokaro1.

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

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