Bank Reconciliation Controls

Overview

CommunityPay maintains a daily reconciliation cycle between its general ledger and Stripe's payment processor records. The ReconciliationService performs a structured six-step verification process that matches every transaction, identifies discrepancies, and generates alerts when the payment processor and database diverge.

This is not a monthly close activity. Reconciliation runs daily as an automated Celery task (run_daily_reconciliation), ensuring discrepancies are detected within 24 hours of occurrence.

Six-Step Reconciliation Process

Step 1: Payment Reconciliation

The service retrieves all payments within the reconciliation window from both the CommunityPay database and Stripe's API. For each HOA with a connected Stripe account, it fetches payment intents and matches them against database records by provider_transaction_id.

For each matched payment, the service creates a ReconciliationItem record with one of three outcomes: - Matched: Amount and status align within $0.01 tolerance - Amount Mismatch: Database amount differs from Stripe amount - Status Mismatch: Database status differs from Stripe status

Critical detection: Payments found in Stripe but missing from the database generate high-severity alerts. Payments in the database but missing from Stripe are also flagged. Both represent potential data integrity issues.

Step 2: Refund Reconciliation

Refund records are matched between the database (Refund model) and Stripe's refund API. Amount discrepancies are flagged as reconciliation items requiring action.

Step 3: Transfer Reconciliation

Platform fee transfers are tracked. (Note: Platform fees have been deprecated in favor of subscription pricing; this step now confirms zero fee collection.)

Step 4: Payout Reconciliation

For each connected HOA, Stripe payouts are retrieved and matched against StripePayout records. Failed payouts generate critical-severity alerts that include the failure reason and affected amount.

Step 5: Balance Verification

The service retrieves Stripe's current platform balance (available + pending) and compares it against the expected balance calculated from the database:

Expected = Total Succeeded Payments - Total Completed Refunds - Total Paid Payouts

Discrepancies exceeding $10.00 generate high-severity balance_discrepancy alerts.

Step 6: Alert Generation

All discrepancy items are reviewed. High-value payments (> $100) missing from Stripe are escalated to critical severity. A summary email is sent to administrators when any discrepancies are found.

ReconciliationRun Record

Each execution creates an immutable ReconciliationRun record tracking: - Run type (daily, weekly, manual) - Date range covered - Total payments checked - Total Stripe transactions processed - Matched count and discrepancy count - Total amount processed, fees collected, and amount disbursed - Stripe available and pending balances - Completion status and any error messages

Daily Balance Tracking

The DailyReconciliation service maintains per-HOA daily balance snapshots via the DailyBalance model:

• Opening balance: Previous day's closing balance (chain continuity)
• Payments received: Sum of succeeded payments for the day
• Refunds issued: Sum of completed refunds
• Payouts sent: Sum of paid payouts
• Closing balance: Computed from opening + receipts - refunds - payouts

Each snapshot is verified against Stripe's real-time balance for the connected account. Discrepancies exceeding $1.00 are recorded in the balance_discrepancy field.

Alert Severity Levels

Scheduling