> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cfosilvia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# BROKERAGE CASH SWEEP DEDUP

# 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`](../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):

| Account | Plaid type | Balance |
| - | - | - |
| WELLSTRADE …2275 | investment | $114,135.42 (includes ~$58K uninvested cash) |
| BROKERAGE CASH …2916 | depository/checking | \$57,992.32 ← **same dollars again** |

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:

| Consumer | Where | Notes |
| - | - | - |
| Client UI (dashboard, accounts, assets) | `hooks/queries/use-accounts.ts` | flags + zeroes the fetched copies |
| AI chat tools / voice / PDF statements | `lib/financial-data-cache.ts#getFinancialMetrics()` / `getAccountDetails()` | choke point for the app-code AI tool path |
| AI `execute_sql` (chat/radar/internal/proactive/simulation) | `public.account_details` view → `account_effective_balances` | the AI writes raw SQL; the *view* bakes in the dedup (see §5). NOT `getFinancialMetrics` |
| Hybrid balance layer | `lib/plaid-investment-balance-override.ts` | zeroes all balance fields, preserves `pre_dedup_balance_current` |
| Daily snapshot cron | `app/api/cron/process-daily-snapshot/route.ts` | detects twice: stored-balance and fresh-Plaid branches |
| Goals (chat + voice) | `app/api/chat/tools.ts`, `incoming-call-webhook-tool/get-goals` | deliberately use `pre_dedup_balance_current` — goals track the REAL brokerage cash |
| Other SQL consumers | `public.account_effective_balances` view | net-worth RPCs; see §5 |

**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:

```sql theme={null}
SELECT account_id, suppressed_usd FROM account_effective_balances
WHERE is_duplicate_cash_sweep ORDER BY suppressed_usd DESC LIMIT 20;
```

## 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).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.