Fixing Square payment captures, idempotency, and refunds

I spent today fixing three critical payment system issues in DeedStack: non-transactional captures that could orphan charges, time-based idempotency keys that let the billing cron double-charge tenants, and a revenue reporting bug that couldn't net refunds even when they existed.

Eight capture paths, not one

The security audit claimed one Square capture path. I found eight across six routes plus the recurring billing cron. Every post-capture write sequence now runs inside a real transaction via runPostCapture(). 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.

I also fixed five broken transactions that used db.pool.query('BEGIN'). That picks a free connection per statement, so BEGIN never bound the writes. They auto-committed and ROLLBACK was a no-op.

The billing cron recharged everyone on retry

The recurring billing cron keyed on recurring_${tenant.id}_${Date.now()}. Any cron re-run inside a billing period re-charged every tenant. Now it keys on next_payment_date.

I replaced all thirteen time-based idempotency keys with sha256 over scope and intent components, truncated to 28 hex characters. Square's documented limit is 45. The loan payment key uses the current next_payment_date, so two clicks collide and dedupe but a genuine second payment in the same month gets a new key once the first commits and advances the date.

Loan terms validation

The financing calculator accepted any downPaymentPercentage. I demonstrated a $15,000 property purchased for $100.50 total with 0.01% down. termMonths was also unbounded. Zero produced monthlyPayment: Infinity and totalAmount: NaN written to the database. 6000 produced a 500-year loan.

I added getPurchaseTermsForTenant on the tenant to global to siteConfig chain, plus assertPurchaseTermsOffered that runs before the Square capture. The calculator also read processingFee from siteConfig instead of tenant settings, so a tenant with its own configured fee had the UI quote one number and the server charge $99.

The client offered 20/25/35/50% down from a hardcoded array when only 20% and 50% were configured tiers. For 25% down the client picked 12% APR and the server picked 8%, a $21.31/mo gap on an option the business never configured.

Active and overdue counts disagreed

The admin dashboard and loans list showed different Active and Overdue counts in both directions. I found thirteen definitions of active and overdue across the codebase, not two. The dashboard baked a seven-day grace period into the overdue flag itself. The loans page treated active and overdue as mutually exclusive and subtracted overdue from active. The All tab counted archived loans that its own filter hides.

I added LOAN_IS_ACTIVE_SQL, LOAN_DAYS_PAST_DUE_SQL, and LOAN_IS_OVERDUE_SQL to server.js as the single source of truth. GET /api/admin/loans now returns is_active, is_overdue, days_past_due, late_fee_applies, past_grace_period, and late_fee_waived. The client renders those flags instead of recomputing them. Active and overdue overlap. A payment due today is not overdue. Grace changes the late fee only.

I also found that /api/admin/reports/export had no tenant filter on any of its eight queries. Portfolio totals, active loans, revenue, tax, HOA and outstanding balances all summed every tenant on the platform into one admin's downloadable PDF. The C-4 audit sweep matched routes taking an :id parameter and missed leaks on endpoints that read all rows without an identifier.

Refunds at both layers

D & D Lozano was refunded $69 through the Square dashboard directly, so their tenant_payments row still reads completed and Financials overstates revenue by $69. The revenue queries filtered payment_type != 'refund', which excluded refund rows rather than subtracting them. A refund would have reduced revenue by exactly zero even if one had been recorded.

I added refund flows at both layers. Master Admin can refund a tenant subscription payment through the platform Square client. Tenant admins can refund a loan payment through the tenant's own client via getSquareClientForTenant. Migration 034 added thirteen refund columns across payments and tenant_payments.

A refund is a new row with a negative amount and refunded_payment_id pointing at the original, never an edit. Around twenty existing SUM() reporting queries then net correctly with no change. The refund locks the payment and re-validates under the lock inside the transaction, so two simultaneous partial refunds cannot overdraw. Throwing after Square has already refunded lands the payment in payment_reconciliation for a human.

Partial refunds are pro-rata across every component. Tax and HOA escrow is refunded because the escrow was funded by the payment being reversed and the tax and HOA reports sum those columns. The dialog warns that already-forwarded escrow leaves the tenant out of pocket.

MRR does not drop on a refund. It is a forward-looking subscribed rate, not cash collected. The refund dialog offers Cancelled rather than assuming it.

Tier pricing consolidated

I found fourteen hardcoded tier prices, four of them wrong. Master Admin Dashboard hardcoded Starter at $49/mo. PricingModal.js advertised $299/$799/$1999 setup fees and $49/mo Starter to customers. Nobody has ever paid those prices. The public pricing page at deedstack.com/pricing is authoritative: Free Trial $0 for 14 days, Starter $69/mo with no setup fee, Pro $299 setup plus $99/mo, Enterprise $999 setup plus $199/mo.

All prices now derive from server/config/pricing.js, which generates the SQL CASE expressions so JS and SQL cannot structurally disagree. client/src/config/pricing.js mirrors it and overlays the server's values at runtime via GET /api/pricing/tiers.

Permanent test tenant

Tenant 7 is now the permanent test tenant with npm run seed:test and npm run reset:test from server/. It has no Square credentials at all. Sandbox keys are one edit from live keys. None cannot be. The seed guard refuses every tenant id except integer 7 and additionally matches company name and subdomain, refuses production square_environment, snapshots row counts for every other tenant, and rolls back if any of them move.

Green Acres isolation is measured, not asserted. An md5 over all 73 tenant-21 loans, payments, properties and users is identical before and after a full test-tenant reset.

The test tenant seeds ten loans covering every UI state: current, overdue-in-grace, overdue-past-grace, late-fee-waived, notice-sent, defaulted, archived, paid-off, escrow with a partial refund, and brand-new. Counts read 10 total, 7 active, 4 overdue, 1 paid off, 1 defaulted, 1 archived with active and overdue overlapping. Every seeded date derives from one instant captured at run start, so a loan seeded 3 days overdue is still 3 days overdue next month.

Verification

Migration 033 and 034 have both been run against production. The payment reconciliation table has zero rows. The refund columns are present.

On 2026-08-17 tenant 21 reads Active 2, Overdue 1 on both /admin/dashboard and /admin/loans with tabs All(2) Active(2) Overdue(1). Robert is 2 days past due within a 7-day grace showing amber and cure $107.08 with no late fee. George is due 2026-09-01.

Master Admin Platform Usage reads 48 properties, 14 active loans and 7 admin users. Every tenant row shows real counts. MRR of $497 is verified correct: 1 Pro at $99 plus 2 Enterprise at $398.

The downPaymentPercentage validation was confirmed against production. 0.01% down returned 400 listing the three offered tiers. A legitimate 20%/36mo passed validation and failed at the Square capture on the fake nonce, which is correct. Only a real browser purchase with a real card remains untested.