Investment Account Data Pipeline#

Investment accounts maintain two parallel materialized datasets that must remain in sync after every sync cycle:

These systems are tightly coupled: balance calculation reads live holdings data to split cash vs. non-cash balances and to compute net_market_flows. When the holdings materialization step is incomplete or stale, both systems diverge.

The full pipeline is orchestrated by Balance::Materializer#materialize_balances, which runs all steps inside a single Balance.transaction. The entry point from the sync side is Account::Syncer#perform_sync.

Holdings System (Returns Tracking)#

Holdings are app-level materialized snapshots of an account's security positions, produced on every sync by Holding::Materializer.

Holding model 1 — keyed by (account_id, security_id, date, currency). Key fields:

• qty, price, amount — position size and valuation
• cost_basis, cost_basis_source, cost_basis_locked — source priority is manual > calculated > provider 2; locked values are never overwritten
• avg_cost — returns the stored cost_basis if present and positive; falls back to a weighted-average calculation from trades 3
• trend — computes unrealized gain/loss as (current_amount) − (qty × avg_cost) 4

Holding::Materializer delegates to one of two calculators 5:

• Holding::ForwardCalculator — manual accounts; accumulates weighted-average cost chronologically 6
• Holding::ReverseCalculator — linked/synced accounts; works backward from the latest provider snapshot 6

After persisting, Holding::Materializer explicitly reloads account.holdings 7 to clear the ActiveRecord association cache before the balance calculator initializes its own SyncCache.

Provider-sourced holdings (account_provider_id present) are preserved and never overwritten by calculated rows 8. Calculated rows that collide with a provider snapshot on the same (date, security, currency) key are cleaned up 9.

Balance System (Trends Tracking)#

Balance rows are keyed by (account_id, date, currency) and store the full daily snapshot needed for charts and return calculations.

Key columns on the balances table:

net_market_flows is calculated in BaseCalculator#market_value_change_on_date as:

net_market_flows = (end_of_day_holdings_value − start_of_day_holdings_value) − (non_cash_inflows − non_cash_outflows)

This isolates price appreciation/depreciation from trade activity.

Balance::SyncCache 10 memoizes holdings values by date in a single pass over account.holdings at the start of balance calculation. This is why the holdings reload in Holding::Materializer is essential — the cache reads holdings exactly once, so any stale association would propagate through the entire balance series.

For investment accounts, end_non_cash_balance is taken directly from holdings_value_for_date() rather than being derived from flows 11, making the holdings table the authoritative source for that column.

Balance::ChartSeriesBuilder queries end_balance, end_cash_balance, and end_non_cash_balance from the balances table via a lateral-join SQL query with FX conversion.

Pipeline Ordering and Atomicity#

Balance::Materializer#materialize_balances runs all steps in a single Balance.transaction in strict order:

1. materialize_holdings → Holding::Materializer (holdings table updated, AR cache reloaded)
2. calculate_balances → ReverseCalculator or ForwardCalculator (reads fresh holdings via SyncCache)
3. persist_balances → bulk upsert keyed on (account_id, date, currency)
4. purge_stale_balances → delete rows outside the computed date window
5. update_account_info → (forward strategy only) write-back to account.balance / account.cash_balance

The transaction guarantees atomicity: a failure in Holding::Materializer rolls back everything, preventing a partial state where balances reflect stale holdings 12.

Incremental forward sync edge case: Balance::ForwardCalculator supports incremental recalculation seeded from the most recent persisted Balance row instead of replaying from the opening anchor. However, it falls back to full recalculation when the prior balance has a non-zero non-cash component — because Holding::Materializer always performs a full recalculation, so the persisted start_non_cash_balance seed would be stale relative to freshly computed holding prices 13. The incremental? flag reflects whether the calculator actually ran incrementally versus falling back.

Divergence Scenarios#

The two systems can diverge when materialization is incomplete or bypassed:

1. Stale SyncCache on interrupted sync
If Holding::Materializer is bypassed without raising (e.g., a code path that skips holdings for a non-investment account type incorrectly classified), Balance::SyncCache will initialize holdings_value_by_date from old holdings data 10. The resulting net_market_flows and end_non_cash_balance on every Balance row will be wrong for that sync cycle.

2. Incremental balance with stale non-cash seed
Without the fallback safeguard in ForwardCalculator, an incremental sync would seed start_non_cash_balance from a persisted balance row that no longer reflects current holding prices, producing incorrect cash/non-cash splits going forward 14.

3. Goals progress basis divergence
Investment-backed goals with progress_basis = contributions compute current value as balance − Σ net_market_flows from the balances table 15. If balance rows have stale or missing net_market_flows, goal progress will misrepresent contributions vs. market gains.

Diagnostic signals:

• If end_non_cash_balance on a balance row doesn't match the sum of holding.amount values for that date, holdings and balances have diverged.
• net_market_flows = 0 consistently across all days on an investment account (with no trades) may indicate that holdings_value_by_date was empty when the balance was calculated.

Key Consumers#

Related files:

• app/models/balance/materializer.rb — pipeline orchestrator
• app/models/holding/materializer.rb — holdings materialization
• app/models/balance/base_calculator.rb — shared balance calculation logic including net_market_flows
• app/models/balance/sync_cache.rb — holdings value lookup used during balance calculation
• app/models/holding.rb — Holding model with cost basis logic
• app/models/investment_statement.rb — investment-level aggregations