Fixing Square payment idempotency and transactional capture

I fixed two audit findings in DeedStack's payment flow. The security audit claimed there was 1 capture path and 7 idempotency key sites. Re-deriving the surface found 8 capture paths across 6 routes plus 1 cron, and 13 idempotency key sites.

Transactional capture

All 8 post-capture write sequences now run inside real transactions via a new runPostCapture() helper that owns a pooled connection. On write failure it rolls back, then records the orphaned charge in a new payment_reconciliation table using a separate pooled connection so the row survives the rollback. It logs the Square payment id and returns it to the caller as a support reference.

I fixed 5 broken transactions that used db.pool.query('BEGIN'). pool.query() picks a free connection per statement, so BEGIN never bound the writes. They auto-committed and ROLLBACK was a no-op. This affected manual payment recording, payment delete, loan import, custom loan create, and loan delete.

Deterministic idempotency keys

I replaced all time-based idempotency keys with sha256 over (scope, intent components). 12 sites converted, the 13th eliminated with a deleted duplicate route. The digest is shortened to 28 hex chars. With 32 the loan_payment scope produced a key of exactly 45 chars, sitting precisely on Square's documented ceiling. 28 gives margin at 41 chars.

The highest-impact bug was in the recurring billing cron. It used recurring_${tenant.id}_${Date.now()}, so any cron re-run inside a billing period re-charged every tenant. Now keyed on next_payment_date.

I found a path where an orphaned capture actively harmed a paying customer. The cron's generic catch marked the tenant past due, wrote a failed payment row, and emailed "payment failed", all after the card was successfully charged.

Reconciliation policy

No auto-refund. With deterministic keys, a retry of the same intent hits Square's dedupe and returns the same payment, so the customer can safely click Pay again and the writes complete with no second charge. Auto-refund is worse: refunds can themselves fail, and refunding a loan payment leaves the borrower past due.

For loan payments the key uses the current next_payment_date. Two clicks collide and dedupe. Once the payment commits next_payment_date advances, so a genuine second payment in the same calendar month derives a different key automatically.

Other fixes

I deleted the duplicate billing loop behind POST /api/billing/process-recurring. No callers anywhere in repo, defective keys, no transaction, and it recomputed next_payment_date from now() so it walked every tenant's billing day forward on each run. Now delegates to processRecurringBillingPayments().

Fixed req.user.userId in POST /api/loans. Undefined everywhere else in the file (req.user.id), so the phone-number update silently matched zero rows.

Added synchronous ref-based double-submit guards to all 5 payment forms.

Loan terms validation

I fixed the downPaymentPercentage hole I had flagged during the transactional work. POST /api/loans accepted any number. 0.01 bought a $15,000 property for $100.50 today ($1.50 down + $99 fee), loan originated at 12% APR, property flipped to pending.

termMonths was also unbounded. 0 produced monthlyPayment: Infinity and totalAmount: NaN written to the loans table. 6000 produced a 500-year loan. paymentDueDay silently mapped anything not strictly === 1 onto the 15th.

I added a purchase-terms resolver (getPurchaseTermsForTenant) on the existing tenant → global → siteConfig chain, plus exact-match validation (assertPurchaseTermsOffered) that runs before the Square capture. Rewrote calculateFinancing to take resolved terms and throw rather than select a tier. Removed both fall-through branches.

Fixed a real money mismatch: calculateFinancing read processingFee and minimumMonthlyPayment from siteConfig, not tenant settings. A tenant that configured its own fee had the UI quote its fee and the server charge siteConfig's $99.

Rewrote the client calculator to render exactly the configured tiers and terms, resolving them with the same precedence as the server.

The UI was wrong

The calculator offered 20/25/35/50% but only 20% and 50% are configured tiers. The two fall-throughs disagreed with each other. For 25% down the client picked the highest tier <= the option (20% tier → 12% APR) while the server picked downPaymentPercent >= 50 (→ 8% APR). The customer was shown $376.95/mo and originated at $355.64/mo, a $21.31/mo gap on an option the business never configured. Same for 35% ($18.50/mo). Neither number was ever the tenant's price.

Files changed

• server/utils/payments.js: new file with idempotencyKey(), billingPeriod(), runPostCapture(), recordOrphanedCapture(), OrphanedCaptureError
• server/config/migrations/033_payment_reconciliation.js: creates payment_reconciliation with unique index on square_payment_id and a partial index on unresolved rows
• server/server.js: 8 capture paths wrapped, 12 idempotency keys converted, 5 transactions repaired, 1 duplicate route deleted, orphan-aware catch blocks, purchase-terms resolution and validation
• client/src/pages/LoanDetail.js, PropertyDetail.js, SignupPayment.js, Admin/AdminBilling.js: ref-based submit locks
• client/src/pages/PropertyDetail.js: hardcoded tier array removed, single quoteFor() replaces four duplicated amortization copies, resolveTier() mirrors the server