Skip to main content

​
Brokerage Cash-Sweep Dedup (Wells Fargo double-count)

TL;DR: Wells Fargo / WellsTrade exposes a brokerage’s uninvested cash twice through Plaid — inside the investment account’s balance AND as a standalone depository/checking account named “BROKERAGE CASH …” on the same item. Summing both double-counts the cash. We fix it by suppressing the depository at read time; stored data is never mutated. The canonical detector is lib/plaid-brokerage-cash-sweep.ts. Fleet scope when audited (2026-07-06): 208 sweep accounts / 127 users / ~$1.70M double-counted. Rolled out behind the PostHog flag brokerage-cash-sweep-dedup.

​
1. The problem

Wells Fargo’s “Command Asset Program” holds a brokerage’s uninvested cash in a linked, separately-numbered checking account. Plaid surfaces the same dollars in both places and confirmed this is expected behavior with no structured link field between the two accounts. Example (the original canary): Naively summing inflates cash and net worth by the sweep amount. E*TRADE Bank and some credit unions exhibit the same pattern (not yet allowlisted).

​
2. Detection contract (ALL conditions required)

Implemented in detectDuplicateCashSweepAccountIds() — pure, side-effect-free:
  1. Institution allowlist — Plaid institution_id ∈ BROKERAGE_CASH_SWEEP_INSTITUTION_IDS (currently ins_127991 Wells Fargo only). Gated by the stable ID, never institution name.
  2. Type/subtype — effective type depository + subtype checking.
  3. Name — institution-provided name (official_name ?? name) matches /brokerage\s*cash/i (covers “IRA BROKERAGE CASH”, “BUSINESS …”). Ignores the user’s custom_name so a rename can’t break detection.
  4. Structure — ≥1 active investment sibling on the same item_id.
  5. Coverage guardrail (the lossless proof) — suppress only while SUM(siblings' balance.available) >= depository balance (+$0.01 float epsilon), greedily largest-first. This proves the brokerage’s balance.current already contains the cash, so zeroing the depository cannot lose net worth. A prod scan found “BROKERAGE CASH” accounts whose cash is NOT mirrored — those are left alone.
  6. Margin guard — skip the whole item if any investment sibling carries a margin loan (available can include borrowing power, not real cash).
  7. Scope guards — Plaid-only (manual/SnapTrade excluded), positive depository balance, hidden accounts excluded from candidates AND budget (can only under-suppress, never over-suppress).

​
3. Architecture principle: raw storage, read-time logic

public.accounts always stores raw Plaid balances. Nothing ever writes zeroed balances or flags to it — is_duplicate_cash_sweep / pre_dedup_balance_current are in-memory response decorations, not columns. This means: bank changes behavior → change the detector, no data repair; rollback → flag off, next read shows pre-fix numbers; false positive → the real balance was never destroyed. Where each consumer applies the dedup: Rule for new code: app-side balance math goes through getFinancialMetrics/applyPlaidHybridBalance; new SQL that sums account balances must read account_effective_balances, not accounts.

​
4. The one exception: snapshots (charts)

The nightly cron persists its results into snapshots_by_account, snapshots_by_item, and daily_snapshots_summary — these feed the trend charts (useDailySnapshots). Which columns are deduped vs raw (this distinction matters — see the trap below). Per app/api/cron/process-daily-snapshot/route.ts:
  • Deduped: the derived per-account columns — cash_on_hand, total_assets, net_worth (the sweep contributes 0 via the duplicateSweepIds.has(...) guard, ~line 743) — and all daily_snapshots_summary totals.
  • Raw (NOT deduped): the passthrough per-account columns — balance_current, balance_available (written straight from Plaid, ~line 806), and tax_treatment (copied from the account row). So a suppressed sweep row persists net_worth/cash_on_hand = 0 but balance_current = its full ~$58k, mirroring the raw-storage principle at the account level.
Rule for reading snapshots: aggregate per-account totals from cash_on_hand / net_worth / total_assets (deduped) — never from raw balance_current (would re-introduce the double-count). See §9. Consequences:
  • Flag rollback / false positives do NOT self-heal chart history; the derived rows written while the flag was on keep the deduped values. Repair = backfill script.
  • Historical pre-fix rows had the double-count baked in. The canary user’s history was corrected with scripts/backfill-cash-sweep-reconstruction-canary.sql (staged plan → verify → apply, cutoff at the first deduped cron run). ⚠ Its holdings-sleeve proxy is CANARY-ONLY — fleet validation (2026-07-08) found the sleeve diverges from the depository by six figures/day for other users (Storybook preview quantified it: 32/50 top users mis-corrected ≥20%, 22/50 ≥100%). The fleet correction is scripts/backfill-cash-sweep-reconstruction-fleet.sql, a two-part hybrid:
    • Part A — exact: on days that HAVE a recorded depository row, subtract the depository’s own recorded daily balance, guarded by the live coverage rule applied to recorded history. Always preferred.
    • Part B — flat hole-fill (product decision, Felipe 2026-07-08): the 241-day snapshots_by_account write hole (2025-10-29 → 2026-06-26) has no trustworthy per-day source (Plaid has no historical-balance API). Rather than leave it uncorrected (which shows a cliff where corrected recent history meets the inflated hole), estimate each hole day with the user’s current total double-count (SUM(suppressed_usd) from account_effective_balances), subtracted flat, on daily_snapshots_summary only. Bounded to [WF item created_at, 2026-07-07], disjoint from Part A (only days with no recorded dep row), margin users excluded, and absorb-guarded — days where subtracting the flat amount would drive a column < 0 are skipped and logged (flatfill_absorb_negative), so the method fails toward honest under-correction rather than fabricating a dip. This is a best-effort reconstruction, NOT exact: the sweep balance swung 2–55× across the hole for the ~40% of users with a pre-hole anchor, so absorb-skipped early-hole months stay slightly inflated (jagged partial correction). Interpolation between known endpoints was considered and rejected for simplicity. Backed by the user-facing message: “we reconstruct your past as best we can but can only be certain about the present, which is now fixed.” Every row is backed up; RESTORE reverses the whole run.
  • Every cron run emits a per-user audit trail (see §7) from which exact per-day suppressed amounts can be restored.

​
5. SQL layer: account_effective_balances view

Pure-SQL consumers can’t call the TS detector, so the view (supabase/migrations/20260706100000_…) is its SQL projection: every account plus effective_balance_current_usd (0 when suppressed), is_duplicate_cash_sweep, pre_dedup_balance_current_usd, suppressed_usd. security_invoker — RLS still applies. Consumers (all read the view so detection lives in ONE place):
  • get_user_net_worth_for_clerk (Slack revenue alerts) and get_user_net_worth_export (Silvia-Admin User Wealth leaderboard/CSV) — read effective_balance_current_usd in agg_liquid.
  • account_details — the unified accounts view the AI execute_sql tool reads (Silvia chat / radar / internal / proactive prompts / silvia-simulation, documented in the silvia-database-views-skills skill). Its Plaid branch LEFT JOINs account_effective_balances and zeroes balance / balance_available for a flagged sweep (migration 20260707100000_…). This was the last un-deduped read path — the AI previously summed raw accounts.balance_current through it. account_details was untracked before; that migration also brings it under version control. Depends on this view, so apply 20260706100000 first.
Portfolio scope. account_effective_balances and account_details both end with portfolio_id (20261009_11_financial_views_portfolio_id.sql). Detection reads every active, non-hidden account on the Item before anything looks at that column, so a sweep account and its brokerage sibling in different portfolios still dedup, and filtering the output by portfolio_id gives per-portfolio totals that sum to All. Never filter accounts by portfolio before detection. The view corrects unconditionally (SQL can’t read the PostHog flag) — internal / AI numbers are right-by-default regardless of rollout stage. Documented conservative divergences from the TS detector (view header): prefix-greedy on multi-sweep items (may suppress fewer, never more); COALESCE(usd, native) currency like the pre-existing RPCs. Parity anchor: the canary sweep suppresses exactly $57,992.32 in both the app detector (PostHog event) and the view. If you touch either implementation, re-check that they agree:

​
6. Rollout & rollback

  • Flag: PostHog brokerage-cash-sweep-dedup, targeted by Clerk user_… id. Client gate isCashSweepDedupEnabledClient() (sync, posthog-js); server gate isCashSweepDedupEnabledForServer() (posthog-node, 2s timeout).
  • Fail-safe contract: any flag error/timeout resolves to false = exact pre-fix behavior. The gate must never throw and never slow the cron.
  • Kill switch: flag off → all live reads revert instantly. Snapshot rows written during the on-window need a repair script (§4).

​
7. Observability

  • [CASH_SWEEP_DEDUP] log line in the daily-snapshot cron (Vercel logs): who, which account, how much, why.
  • PostHog event brokerage_cash_sweep_deduped with suppressed_usd_total — fires per user per cron run; doubles as the per-day audit trail for snapshot repair.

​
8. User-facing notice

Affected users see an amber warning icon next to the changed values (dashboard Total Cash card, /assets Cash header, the $0.00 account row) that opens an explanation on hover — components/cash-sweep-correction-notice.tsx, dismissal persisted in all_users.cash_sweep_notice_dismissed via /api/cash-sweep-notice. Visibility gate (shouldShowCashSweepNotice): render only when the account is flagged is_duplicate_cash_sweep and was connected on/before CASH_SWEEP_NOTICE_CONNECTED_BEFORE (2026-07-08 UTC). Accounts connected after that never saw the double-count — the value was deduped from their first render — so the “we corrected…” framing would confuse them and the icon is suppressed (the dedup still applies; only the notice is hidden). An unknown/legacy created_at defaults to showing. Engagement is instrumented via PostHog: cash_sweep_notice_shown / _opened / _email_clicked / _dismissed, each with a surface prop (‘dashboard’ | ‘assets’ | ‘accounts’).

​
9. Known traps for future work

  • Disabled crons app/api/cron/schedule-transactions-summary-emails / send-transactions-summary-email read raw accounts — add dedup before re-enabling.
  • /api/send-daily-email(-v2) trust caller-passed values — callers must source from getFinancialMetrics.
  • Adding an institution (e.g. E*TRADE) requires BOTH the TS allowlist and the view’s IN ('ins_127991') list, plus revisiting the notice copy, which currently says “Wells Fargo” verbatim.
  • The sweep account still emits transactions — they flow through transaction_details untouched (correct: dedup concerns balance stocks, not cash flows; is_internal_transfer identifies offsetting pairs).
  • Tax buckets are deduped, but only in app code. The taxable / tax_deferred / tax_free buckets in getFinancialMetrics (lib/financial-data-cache.ts, ~line 931) apply the same duplicateSweepIds guard as cashOnHand, and unrealized-gains tax reads current_holdings for taxable accounts (a cash sweep has none), so both are correct. There is no SQL tax aggregation today. Trap: a future tax rollup that sums snapshots_by_account.balance_current grouped by tax_treatment would double-count the sweep (that column is raw, §4). Sum cash_on_hand / net_worth instead, or add tax_treatment to account_effective_balances and aggregate the view.

​
History

  • PR #2288 — detector + read-time dedup + snapshot cron + canary flag.
  • PR #2540 — hardened canary backfill script (review fixes).
  • PR #2551 — user-facing correction notice (icon + hover card + dismissal).
  • PR #2563 — account_effective_balances view + net-worth RPC fix (this doc).
  • PR #2596 — fleet backfill: two-part hybrid (exact recorded correction + flat write-hole fill; §4). Sleeve proxy disproven fleet-wide; flat chosen over interpolation by product decision (2026-07-08).