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:**

```cfm
// 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#");
```

```cfm
// 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:**

```cfm
// 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:**

```cfm
// 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:**

```cfm
// 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)`.