Recurring Transactions and Cash Flow Projection#

Overview#

RecurringTransaction is a family-scoped ActiveRecord model that represents a detected or manually-created repeating financial pattern. Each record stores the expected amount, currency, day-of-month, next_expected_date, last_occurrence_date, occurrence_count, and a manual flag distinguishing user-created patterns from auto-detected ones 1.

Records can represent either regular transactions (scoped to one account and optionally one merchant) or recurring transfers (with both account_id and destination_account_id set). Manual rows additionally track amount variance via expected_amount_min, expected_amount_max, and expected_amount_avg 2.

Status is either active or inactive. The expected_soon scope returns active rows with next_expected_date within the next month — this is the primary feed for cash flow projection 3.

Pattern Detection#

Auto-detection runs inside RecurringTransaction::Identifier, invoked after every account sync by IdentifyRecurringTransactionsJob. The algorithm:

1. Scans the last 3 months of non-transfer Transaction entries for the family 4.
2. Groups entries by (merchant_id OR name, amount.round(2), currency, account_id) 5.
3. Filters groups to those with ≥ 3 occurrences whose most recent entry falls within the last 45 days 6.
4. Validates day clustering using a circular-distance standard deviation (≤ 5 days). Circular distance handles month-boundary wrapping — e.g., a pattern landing on days 29, 30, 31, 1, 2 is correctly recognized as a single cluster rather than two 7.
5. Computes the expected day via a circular-rotation algorithm: it rotates the 0-indexed day array to find the pivot that minimizes span, then takes the median in rotated space and maps it back 8.

Detected patterns are upserted into recurring_transactions. Manual rows are skipped during auto-detection updates but have their variance recalculated in a separate batch pass .

Transfer-kind transactions are explicitly excluded from auto-detection because they represent only one leg of a Transfer pair; recurring transfers must be created manually via RecurringTransaction.create_from_transfer.

Job Scheduling and Lifecycle#

IdentifyRecurringTransactionsJob is triggered at the end of each account sync and uses two safety mechanisms:

• 30-second debounce — schedule_for writes the current timestamp to Rails cache; when the job runs, it bails if a newer timestamp exists. This collapses bursts of sync completions into a single run 10.
• PostgreSQL advisory lock — prevents concurrent execution of the identifier for the same family; the lock key is a stable MD5-derived bigint from "recurring_transaction_identify:{family_id}" 11.

The job also checks Sync.any_incomplete_for?(family) before running, ensuring it operates on a complete dataset 12.

RecurringTransaction::Cleaner handles retirement:

Inactivation is confirmed by re-running matching_transactions before marking inactive, so a stale last_occurrence_date alone is not sufficient 13.

Amount Variance Tracking#

Manual recurring rows track how much the transaction amount varies over time. On creation via create_from_transaction, the system looks back 6 months for matching entries (same merchant or name, same currency, ±2 days of the expected day of month) and computes expected_amount_min, expected_amount_max, and expected_amount_avg from those historical amounts.

When a new occurrence is recorded via record_occurrence!, the variance fields are updated with an incremental average formula:

A_{n+1} = A_n + (x_{n+1} − A_n) / (n + 1)

This avoids reloading all historical data on each new transaction 14.

Matching logic uses these variance fields to widen the amount filter when looking up historical occurrences: auto-detected rows match by exact amount; manual rows with variance set match any amount in the [expected_amount_min, expected_amount_max] band 15. Day matching always uses a ±2-day window around expected_day_of_month 16.

Cash Flow Projection#

CashFlowWarningGenerator projects the family's combined Depository balance 30 days forward and fires a cash_flow_warning insight when the projected balance dips below $500 17.

Algorithm 18:

1. Sum current balances of all visible same-currency Depository accounts → starting_balance.
2. Fetch upcoming recurring entries via the expected_soon scope, excluding transfers and cross-currency rows. Call projected_entry on each to get an OpenStruct with date and amount.
3. Compute other_daily_spend = max(median_monthly_expense − recurring_expense_total, 0) / 30. Subtracting the recurring total avoids double-counting charges that are already in the median.
4. Iterate days 1–30. Each day: decrement by other_daily_spend, then apply any recurring entries falling on that date.
5. Track the minimum balance seen. If it falls below $500, emit the insight. Negative low point → high priority; low-but-positive → medium.

projected_entry on RecurringTransaction 19 returns an OpenStruct with date = next_expected_date and amount = expected_amount_avg (for manual rows with variance) or amount (fixed amount). It returns nil for inactive rows or rows whose next_expected_date is in the past, so only actionable patterns feed the projection.

The dedup_key is scoped to cash_flow_warning:{month_token}, so the insight can re-fire each month 20. Known limitation: the $500 threshold is dollar/euro-scale and would be meaninglessly low for ¥/₩-denominated families 21.

Key Files#