ChatGPT 5.4: Payment algorithm review: verification integrity, orphaned pending payments, discount drift, and missing recovery paths

[teetangh]

Summary

The mobile payment stack has several state-integrity problems beyond the booking bugs already captured in #50 and #51.

The highest-risk problems are:

• the Razorpay verification route can mark the wrong payment as SUCCEEDED
• checkout persists Payment rows before a gateway order/payment-intent exists
• failed/expired payments do not have a reliable cleanup/recovery path
• discount usage and payment accounting data are not trustworthy

Taken together, these defects can strand orphaned PENDING payments, confirm unpaid bookings, overuse discount codes, and leave the system without a recovery path when webhooks or gateway calls fail.

Scope

Primary mobile files:

• backend/routes/api/checkout/index.dart
• backend/routes/api/checkout/verify.dart
• backend/lib/database/repositories/checkout_repository.dart
• backend/routes/api/webhooks/stripe.dart
• backend/routes/api/webhooks/razorpay.dart
• backend/lib/services/webhook_handlers.dart
• lib/data/datasources/remote/checkout_remote_source.dart

Web parity references:

• /Users/kaustavghosh/Desktop/familiarise_web/app/api/checkout/verify/route.ts
• /Users/kaustavghosh/Desktop/familiarise_web/app/api/payments/discounts/validate/route.ts
• /Users/kaustavghosh/Desktop/familiarise_web/lib/payments/operations/checkout.ts
• /Users/kaustavghosh/Desktop/familiarise_web/scripts/payments/cleanup-abandoned-payments.ts
• /Users/kaustavghosh/Desktop/familiarise_web/app/api/cleanup/reconcile-payment-status/route.ts

Findings

1. Razorpay verify can falsely confirm the wrong payment because it never binds the submitted order ID to the payment being verified

Evidence:

• The verify route loads the payment by internal payment UUID supplied in payment_intent, not by stored gateway order ID.
  • backend/routes/api/checkout/verify.dart:73-79
• It correctly checks that the payment belongs to the authenticated user.
  • backend/routes/api/checkout/verify.dart:92-103
• For Razorpay, it then verifies only that the submitted (razorpay_order_id, razorpay_payment_id, razorpay_signature) tuple is cryptographically valid.
  • backend/routes/api/checkout/verify.dart:133-178
• It never checks that razorpay_order_id equals the payment record’s stored paymentIntent / order ID before marking the payment SUCCEEDED.
  • backend/routes/api/checkout/verify.dart:198-237
• The web verify endpoint is read-only and looks up the payment by actual paymentIntent; it does not let a client-side callback mutate payment state with an unrelated order tuple.
  • /Users/kaustavghosh/Desktop/familiarise_web/app/api/checkout/verify/route.ts:23-80

Impact:

• a user who owns one pending payment and also possesses any valid Razorpay success tuple can attempt to mark a different pending payment as paid
• booking confirmation becomes client-claimable instead of being strictly bound to the gateway object created for that payment row
• this is a payment authorization/integrity bug, not just a UX issue

Proposed fix:

1. Require the submitted razorpay_order_id to equal the stored payment gateway identifier for that payment row before verification proceeds.
2. Fetch the order/payment details from Razorpay and verify:
   • order ID matches stored payment intent
   • amount/currency match the payment row
   • payment is actually captured/paid
3. Prefer webhook-first confirmation and keep verify as a read-only status endpoint where possible.
4. Add tests proving a valid signature tuple for payment B cannot confirm payment A.

2. Checkout creates the database Payment row before creating the real gateway object, which leaves orphaned PENDING payments on gateway failure

Evidence:

• The mobile checkout route computes the amount, then immediately creates the Payment record.
  • backend/routes/api/checkout/index.dart:318-369
• Only after the row is inserted does it call Razorpay/Stripe to create the actual order or payment intent.
  • backend/routes/api/checkout/index.dart:376-524
• CheckoutRepository.createPayment() also pre-populates a synthetic paymentIntent value before any real gateway object exists.
  • backend/lib/database/repositories/checkout_repository.dart:25-53
• If gateway creation fails, the route returns an error response, but the previously-created Payment row is not deleted or marked failed.
  • backend/routes/api/checkout/index.dart:430-447
  • backend/routes/api/checkout/index.dart:506-523
• The web checkout flow persists the payment inside the main transaction after the gateway object exists, not before.
  • /Users/kaustavghosh/Desktop/familiarise_web/lib/payments/operations/checkout.ts:1503-1585

Impact:

• gateway outages or validation failures leave orphaned PENDING payment rows behind
• those rows can confuse dashboard state, expiry logic, support tooling, and later recovery attempts
• a payment record can exist with a fake/generated paymentIntent that never existed at the gateway

Proposed fix:

1. Reverse the order:
   • create the gateway order/payment intent first
   • persist the payment row only once the gateway object exists
2. If the current order must be preserved, wrap the DB insert in compensation logic:
   • mark failed
   • delete the row
   • delete tentative booking artifacts if the checkout session never became real
3. Add tests for Stripe/Razorpay gateway creation failure ensuring no orphan PENDING payment remains.

3. Failed or expired payments do not have a reliable cleanup path, even though the system records expiresAt

Evidence:

• New payment rows are created with expiresAt = now + 1 hour.
  • backend/lib/database/repositories/checkout_repository.dart:45-48
• The mobile payment failure webhook handler only marks the payment FAILED and sends a notification.
  • backend/lib/services/webhook_handlers.dart:101-156
• It does not release tentative slots, revert booking state, cancel gateway objects, or restore related state.
• I could not find any mobile cleanup/reconciliation route beyond:
  • backend/routes/api/checkout/index.dart
  • backend/routes/api/checkout/verify.dart
  • backend/routes/api/payments/[paymentId]/refunds.dart
  • backend/routes/api/payments/[paymentId]/disputes.dart
  • backend/routes/api/webhooks/stripe.dart
  • backend/routes/api/webhooks/razorpay.dart
• The web repo has explicit abandoned-payment cleanup that:
  • finds expired pending payments
  • re-checks state
  • cancels gateway payment intents/orders
  • marks payments failed
  • cleans tentative slots
  • restores referral credits
  • /Users/kaustavghosh/Desktop/familiarise_web/scripts/payments/cleanup-abandoned-payments.ts:104-260

Impact:

• expired PENDING payments can remain indefinitely
• tentative booking artifacts can drift out of sync with payment state
• support and reporting surfaces accumulate stale financial records with no backend-owned recovery path

Proposed fix:

1. Add a payment cleanup job/route for expired pending payments.
2. Define the cleanup semantics per booking type:
   • fail payment
   • release tentative slots
   • revert or cancel provisional booking state
   • restore any consumed credits/discount counters if needed
3. Add tests for expired payments and failed webhook flows.

4. Webhook processing failures are acknowledged with HTTP 200, but the mobile backend has no replay or reconciliation mechanism

Evidence:

• Stripe webhook processing records the event, and on downstream processing failure it marks the event failed but still returns HTTP 200.
  • backend/routes/api/webhooks/stripe.dart:190-200
• Razorpay does the same.
  • backend/routes/api/webhooks/razorpay.dart:193-203
• Returning 200 prevents the provider from retrying the event.
• I could not find any mobile payment reconciliation or webhook replay route/job.
• The web repo has explicit reconciliation endpoints/jobs for payment status and other post-payment recovery work.
  • /Users/kaustavghosh/Desktop/familiarise_web/app/api/cleanup/reconcile-payment-status/route.ts:1-55

Impact:

• a transient DB or downstream processing error can permanently lose the payment event
• the payment may remain PENDING or partially processed with no automated repair path
• webhook idempotency exists, but webhook recovery does not

Proposed fix:

1. Do not acknowledge failed webhook processing with 200 unless there is a real internal retry/replay system.
2. Add a reconciliation job that compares gateway state with local Payment rows and repairs drift.
3. Provide a safe admin/staff replay path for failed webhook events.
4. Add tests for:
   • webhook handler throws after event recording
   • reconciliation later repairs the payment state

5. Discount-code application is inconsistent across the mobile stack and maxUses is not actually enforced

Evidence:

• The Flutter app calls POST /api/checkout/validate-discount.
  • lib/data/datasources/remote/checkout_remote_source.dart:211-244
• The mobile backend does not expose that route; the payment surface currently consists only of checkout, verify, payment visibility, and webhooks.
• The checkout route does validate a discount code server-side and persists discountCodeId on the payment.
  • backend/routes/api/checkout/index.dart:323-369
• validateDiscountCode() checks maxUses vs currentUses.
  • backend/lib/database/repositories/checkout_repository.dart:173-233
• But I could not find any non-test code in the mobile repo that increments DiscountCode.currentUses after payment creation or success.
• The web repo has both:
  • a real discount validation endpoint
    • /Users/kaustavghosh/Desktop/familiarise_web/app/api/payments/discounts/validate/route.ts:22-119
  • an atomic currentUses increment in the checkout transaction
    • /Users/kaustavghosh/Desktop/familiarise_web/lib/payments/operations/checkout.ts:1595-1601

Impact:

• the mobile UI’s discount validation call path is broken
• maxUses is effectively advisory because successful checkouts do not consume usage count
• discount analytics, abuse prevention, and future code exhaustion behavior are all unreliable

Proposed fix:

1. Implement a real discount validation endpoint or align the Flutter client to the existing API.
2. Increment DiscountCode.currentUses atomically when a payment session is successfully created or when payment succeeds, depending on the intended business rule.
3. Define reversal semantics for failed/expired/refunded payments.
4. Add tests covering:
   • validation endpoint contract
   • max-use exhaustion
   • concurrent checkouts on the last available discount use

6. Payment.originalAmount is overwritten with the discounted net amount, so the system loses the gross pre-discount price

Evidence:

• Checkout starts from plan price, then subtracts the applied discount before creating the payment.
  • backend/routes/api/checkout/index.dart:318-340
• createPayment() receives that already-discounted amount and stores:
  • amount = amount
  • originalAmount = amount
  • backend/lib/database/repositories/checkout_repository.dart:29-35
• The web checkout flow explicitly preserves gross and net values separately.
  • /Users/kaustavghosh/Desktop/familiarise_web/lib/payments/operations/checkout.ts:332-415
  • /Users/kaustavghosh/Desktop/familiarise_web/lib/payments/operations/checkout.ts:1569-1578
• The web payout/earnings code then relies on originalAmount as the gross consultant-facing amount.
  • /Users/kaustavghosh/Desktop/familiarise_web/lib/payments/payouts/earnings-service.ts:64-65

Impact:

• the mobile backend cannot reliably distinguish:
  • gross plan price
  • discount amount
  • net collected amount
• earnings, refunds, invoice generation, and analytics become harder or impossible to calculate correctly from local payment data
• this is a financial-data modeling bug, not just a UI reporting issue

Proposed fix:

1. Preserve gross originalAmount before applying discounts.
2. Store discount amount explicitly or derive it from gross minus net.
3. Add tests confirming:
   • undiscounted payments keep amount == originalAmount
   • discounted payments keep amount < originalAmount
   • refund/earnings logic can reconstruct gross vs net correctly

7. Stripe verify is status-only, and without reconciliation a missed webhook can leave payments stuck in PENDING

Evidence:

• For Stripe, the verify route simply returns the current DB status; it does not query Stripe or reconcile drift.
  • backend/routes/api/checkout/verify.dart:119-131
• Payment rows have expiration metadata, but there is no mobile reconciliation/cleanup route for stuck Stripe payments.
• The web repo has a dedicated payment-status reconciliation path.
  • /Users/kaustavghosh/Desktop/familiarise_web/app/api/cleanup/reconcile-payment-status/route.ts:1-55

Impact:

• if Stripe succeeds but the webhook is delayed or lost, the mobile verify endpoint cannot repair the local state
• the user sees a perpetually pending payment even when the gateway may already know the outcome
• this increases support burden and encourages unsafe manual intervention

Proposed fix:

1. Keep verify read-only for the normal path, but add server-side reconciliation against Stripe for stale PENDING payments.
2. Introduce a recovery job that periodically checks aged PENDING payments.
3. Add tests for delayed webhook vs reconciliation behavior.

Recommended fix plan

1. Split payment flow into three explicit phases:
   • gateway object creation
   • transactional DB persistence
   • asynchronous post-payment side effects
2. Make verification endpoints safe by default:
   • client verify should not be able to promote a payment to SUCCEEDED unless the gateway object is bound to the payment record and gateway state is re-validated
3. Add a recovery layer:
   • abandoned payment cleanup
   • payment status reconciliation
   • webhook replay / failed-event reprocessing
4. Fix financial data modeling:
   • preserve originalAmount
   • track discount consumption and reversal rules explicitly
5. Align mobile API contracts with frontend usage for discount validation and payment status checks.

Acceptance criteria

• a Razorpay callback cannot confirm any payment other than the one bound to that order
• checkout does not leave orphan PENDING payments when gateway order/payment-intent creation fails
• expired or failed payments are cleaned up deterministically
• failed webhook processing can be retried or reconciled automatically
• discount validation endpoint and mobile client agree on the same route contract
• discount maxUses is enforced by real counter mutation
• Payment.originalAmount preserves gross value while amount preserves net collected value
• stale Stripe PENDING payments can be reconciled without manual DB edits

Regression test matrix

• Razorpay verify rejects a valid signature tuple when the submitted order ID does not match the stored payment intent
• Razorpay verify accepts only the matching order/payment/signature for the correct payment
• Stripe order/payment-intent creation failure leaves no orphan local payment row
• Razorpay order creation failure leaves no orphan local payment row
• expired pending payments are marked failed and their tentative booking artifacts are cleaned
• webhook processing failure is recoverable through replay or reconciliation
• discount validation route works for the Flutter client contract
• discount code usage increments and max-use exhaustion is enforced
• discounted payments persist both gross and net amounts correctly
• stale Stripe PENDING payments transition correctly after reconciliation