payfrit-biz/migrations/2026-02-15_payment_hardening_patches.md

Payment Hardening Patches

SUMMARY OF CHANGES

1. createPaymentIntent.cfm

Add idempotency key and expected amount storage:

// After line 132 (httpService.setPassword("")):
// ADD: Idempotency key tied to OrderID (prevents duplicate PIs on client retry)
httpService.addParam(type="header", name="Idempotency-Key", value="pi-order-#orderID#");

// After line 167 (after abort on error):
// ADD: Store expected amount and PaymentIntent ID in order for webhook verification
queryExecute("
    UPDATE Orders
    SET ExpectedAmountCents = :expectedCents,
        StripePaymentIntentID = :piID
    WHERE ID = :orderID
      AND StripePaymentIntentID IS NULL
", {
    expectedCents: totalAmountCents,
    piID: piData.id,
    orderID: orderID
}, { datasource: "payfrit" });

Also add check for existing PaymentIntent at the start:

// After line 81 (qOrder query):
// ADD: Check if order already has a PaymentIntent
if (qOrder.recordCount > 0 && len(trim(qOrder.StripePaymentIntentID ?: "")) > 0) {
    // Return existing PaymentIntent details
    response["OK"] = true;
    response["CLIENT_SECRET"] = ""; // Can't retrieve, client must use existing session
    response["PAYMENT_INTENT_ID"] = qOrder.StripePaymentIntentID;
    response["ERROR"] = "existing_payment_intent";
    response["MESSAGE"] = "Order already has a PaymentIntent. Use existing checkout session.";
    writeOutput(serializeJSON(response));
    abort;
}

---

2. webhook.cfm

Replace entire file with webhook.cfm.hardened (already created)

Key changes:

• Event deduplication via PaymentAudit table
• Amount verification before marking paid
• Atomic transactions with SELECT FOR UPDATE
• Fixed column name (StripePaymentIntentID not OrderStripePaymentIntentID)
• Audit logging for all events

---

3. orders/updateStatus.cfm

Wrap task creation in transaction with order update:

// Replace lines 54-64 and 88-157 with:

<cftransaction action="begin">
  <cftry>
    <!--- Update status --->
    <cfset queryExecute("
      UPDATE Orders
      SET StatusID = ?,
          LastEditedOn = ?
      WHERE ID = ?
    ", [
      { value = NewStatusID, cfsqltype = "cf_sql_integer" },
      { value = now(), cfsqltype = "cf_sql_timestamp" },
      { value = OrderID, cfsqltype = "cf_sql_integer" }
    ], { datasource = "payfrit" })>

    <!--- Create task if transitioning to status 3 --->
    <cfset taskCreated = false>
    <cfif NewStatusID EQ 3 AND oldStatusID NEQ 3>
      <!--- Check for existing task by OrderID --->
      <cfset qExisting = queryExecute("
        SELECT ID FROM Tasks WHERE OrderID = ? LIMIT 1
      ", [ { value = OrderID, cfsqltype = "cf_sql_integer" } ], { datasource = "payfrit" })>

      <cfif qExisting.recordCount EQ 0>
        <!--- (existing task creation logic) --->
        <!--- ... --->
        <cfset taskCreated = true>
      </cfif>
    </cfif>

    <cftransaction action="commit">
    <cfcatch>
      <cftransaction action="rollback">
      <cfrethrow>
    </cfcatch>
  </cftry>
</cftransaction>

---

4. tasks/complete.cfm

Wrap all operations in single transaction:

// After line 109 (end of rating validation):

<cftransaction action="begin">
<cftry>
  <!--- All existing operations from lines 111-365 go inside this transaction --->

  <cftransaction action="commit">
<cfcatch>
  <cftransaction action="rollback">
  <cfset apiAbort({
    "OK": false,
    "ERROR": "transaction_failed",
    "MESSAGE": "Failed to complete task atomically",
    "DETAIL": cfcatch.message
  })>
</cfcatch>
</cftry>
</cftransaction>

---

SQL SCHEMA CHANGES

Run migrations/2026-02-15_payment_hardening.sql on both payfrit_dev and payfrit databases.

Key additions:

• PaymentAudit table with UNIQUE(StripeEventID)
• Orders.ExpectedAmountCents column
• Orders.ReceivedAmountCents column
• UNIQUE constraint on Orders.StripePaymentIntentID
• UNIQUE constraint on WorkPayoutLedgers.StripePaymentIntentID

---

ORDER STATE MACHINE

           ┌─────────────────────────────────────┐
           │                                     │
           ▼                                     │
       ┌───────┐                                 │
       │   0   │  Cart                           │
       │ cart  │                                 │
       └───┬───┘                                 │
           │ submit.cfm                          │
           ▼                                     │
       ┌───────┐                                 │
       │   1   │  Submitted (awaiting payment)   │
       │ subm  │◄────────────────────────────────┤
       └───┬───┘                                 │
           │                                     │
           │ ONLY webhook can transition         │
           │ payment_pending → paid              │
           ▼                                     │
       ┌───────┐     ┌───────┐                   │
       │   2   │────►│   3   │  Final Prep       │
       │in-prog│     │ ready │  (creates Task)   │
       └───────┘     └───┬───┘                   │
                        │                        │
                        ▼                        │
                    ┌───────┐                    │
                    │   4   │  Claimed           │
                    │claimed│                    │
                    └───┬───┘                    │
                        │                        │
                        ▼                        │
                    ┌───────┐                    │
                    │   5   │  Delivered         │
                    │ done  │                    │
                    └───────┘                    │
                                                 │
       ┌───────┐                                 │
       │   6   │  Cancelled ─────────────────────┘
       │cancel │  (can retry)
       └───────┘

       ┌───────┐
       │   7   │  Deleted (abandoned cart)
       │delete │
       └───────┘

PAYMENT STATUS (separate from order status):
- NULL       = not yet paid
- 'paid'     = payment confirmed (ONLY set by webhook)
- 'failed'   = payment failed
- 'refunded' = charge refunded
- 'disputed' = dispute opened

---

INVARIANTS ENFORCED

Invariant	How Enforced
A) Idempotency
PI creation uses OrderID key	Idempotency-Key: pi-order-{OrderID} header
Webhook deduplicates events	UNIQUE(StripeEventID) in PaymentAudit
No duplicate tasks	Check SourceType='dispute' AND SourceID=OrderID before insert
B) Atomicity
Payment update is atomic	<cftransaction> wrapper with rollback
Task creation with order update	Same transaction
C) Amount verification
Expected stored at PI creation	Orders.ExpectedAmountCents = totalAmountCents
Verified in webhook	IF amountReceived != expectedAmount THEN reject
D) State machine
Only webhook sets PaymentStatus='paid'	Webhook handler only, no client endpoint
Client confirmation alone NOT sufficient	Client gets client_secret, webhook confirms
E) Concurrency
Row locking	SELECT ... FOR UPDATE in webhook
Double-update prevention	WHERE PaymentStatus IS NULL OR PaymentStatus NOT IN ('paid','refunded')

---

TEST CHECKLIST

Idempotency Tests

• Replay webhook: Send same payment_intent.succeeded event twice

  • Expected: Second call returns {"OK":true,"SKIPPED":"duplicate_event"}
  • Verify: PaymentAudit has only 1 row with that StripeEventID
  • Verify: Order not double-updated

• Client retry: Call createPaymentIntent twice for same OrderID

  • Expected: Second call returns {"ERROR":"existing_payment_intent"}
  • Verify: Only 1 PaymentIntent in Stripe dashboard

• Concurrent webhooks: Fire 2 webhook calls simultaneously (use curl in parallel)

  • Expected: One succeeds, one returns duplicate
  • Verify: Order has correct state

Amount Verification Tests

• Correct amount: Normal payment flow

  • Verify: Order.ReceivedAmountCents matches ExpectedAmountCents

• Amount mismatch: Manually craft webhook with wrong amount

  • Expected: Webhook returns error, order NOT marked paid
  • Verify: PaymentAudit.ProcessingResult = 'error'

Refund Tests

• Issue refund via Stripe dashboard
  • Expected: Order.PaymentStatus = 'refunded'
  • Expected: Order.RefundAmount populated
  • Expected: Order.RefundedOn populated

Dispute Tests

• Simulate dispute
  • Expected: Order.PaymentStatus = 'disputed'
  • Expected: Task created with Title = 'Payment Dispute'
  • Verify: Only 1 task created (replay webhook to confirm)

Transaction Tests

• Partial failure: Inject error after order update but before task creation
  • Expected: Both rollback, no partial state

---

RISK SUMMARY

CRITICAL RISKS (MUST FIX IMMEDIATELY)

1. Column name mismatch (refund/dispute handlers use wrong column)

   • Impact: Refunds and disputes silently fail to update orders
   • Fix: Replace OrderStripePaymentIntentID → StripePaymentIntentID

2. No idempotency on PI creation

   • Impact: Client retry creates duplicate charges
   • Fix: Add Idempotency-Key header

3. No webhook event deduplication

   • Impact: Replayed webhook double-processes payment
   • Fix: PaymentAudit table with UNIQUE(StripeEventID)

HIGH RISKS

4. No transaction wrappers

   • Impact: Partial writes on failure
   • Fix: Wrap related operations in <cftransaction>

5. No amount verification

   • Impact: Any amount accepted as "paid"
   • Fix: Store ExpectedAmountCents, verify in webhook

6. No unique constraint on StripePaymentIntentID

   • Impact: Same PI could be linked to multiple orders (data corruption)
   • Fix: Add UNIQUE index

MEDIUM RISKS

7. No SELECT FOR UPDATE

   • Impact: Race condition on concurrent webhook delivery
   • Fix: Add FOR UPDATE to order lookup in webhook

8. No audit trail

   • Impact: Cannot debug disputes or investigate issues
   • Fix: PaymentAudit table

---

DEPLOYMENT ORDER

1. Run SQL migration on payfrit_dev first, verify
2. Deploy webhook.cfm.hardened → rename to webhook.cfm
3. Apply patches to createPaymentIntent.cfm
4. Apply patches to updateStatus.cfm and complete.cfm
5. Test with test Stripe keys
6. Run SQL migration on production (payfrit)
7. Deploy to production

---

PAYFRIT $2.32 PAYOUT VERIFICATION

Based on the fee structure in createPaymentIntent.cfm:

• Customer fee: 5% of subtotal
• Business fee: 5% of subtotal
• Payfrit receives: 10% total (application_fee_amount)

If Payfrit received $2.32 in application fee:

• That represents 10% of a ~$23.20 subtotal
• Or the order total (with fees) was approximately $27-28

This aligns with the code's totalPlatformFeeCents = round((payfritCustomerFee + payfritBusinessFee) * 100).