Skip to main content

​
Institution & Account Edge Cases (index)

TL;DR: Plaid and SnapTrade both publish inconsistent shapes of the same data across institutions. This doc is the index of every institution- or account-specific carve-out currently live in the codebase — what it fixes, where it lives, and how it interacts with the others. New special-cases must be added here at the same time as the code. Last audited: 2026-08-10 against prod (institution counts and dollar deltas below are point-in-time and will drift).

​
Table of contents

​
1. Plaid investment value: the 5-branch split

computePlaidInvestmentValue in lib/plaid-investment-value.ts is the sole source of truth for splitting a Plaid investment/retirement account’s balance.current into a (marketValue, cashOnHand) pair. It runs a 5-branch CASE in strict order. The same 5 branches are mirrored in the SQL view — supabase/migrations/20260810_02_account_details_stock_plan_no_phantom_cash.sql — the Plaid cash_component / investment_component CASE for the AI execute_sql tool. The order is LOAD-BEARING — the first branch that matches wins. Where sumNonCashHoldings = SUM(holdings.market_value) − sumCashEquiv, and cash-equivalents are holdings with is_treated_as_cash = true AND securities.type != 'cryptocurrency'.

​
Branch 0 — margin

Plaid reports balance.current on margin accounts as net equity (positions + cash − margin_loan), not the gross asset value. Running such an account through Branch 1 would inflate net worth by the loan amount. Only fires when balance_margin_loan_amount is populated and > 0 — accounts that haven’t re-synced since margin tracking was added fall through, may temporarily inflate, and self-correct on next sync.

​
Branch 0.5 — stock plan (CFO-7276)

Plaid does not enforce a single contract for balance.current on subtype = 'stock plan'. Two shapes coexist:
  • E*TRADE / Morgan Stanley pattern: balance = vested-only; holdings = all granted shares (vested + unvested).
  • Merrill Benefits / some Schwab pattern: balance = total value; holdings empty or thinly populated.
Prod survey 2026-08-10 across 691 stock-plan accounts / 15 institutions: MAX(balance, sumNonCashHoldings) picks the more complete signal per-account (0 users under-reported vs. ~160 by balance-only or ~82 by holdings-only). Cash is 0 unconditionally so this branch cannot re-introduce the phantom-cash pattern this fix eliminates. See Silvia-Web #3276 for the closed prior attempts (CFO-6008 / CFO-6415). Projected prod impact (2026-08-10 prod snapshot, staging-validated, not yet deployed): removes ≈ 8.9M∗∗ofphantomcashacross∗∗88Bucket−Daccounts/88users∗∗,surfaces∗∗≈8.9M** of phantom cash across **88 Bucket-D accounts / 88 users**, surfaces **≈ 6.4M of previously-hidden unvested equity across 72 Bucket-E accounts / 70 users, plus a ≈ 0.05M∗∗rebaseacross85Bucket−Faccounts.Net−worthΔacrossthe691stock−planpopulation:∗∗≈−0.05M** rebase across 85 Bucket-F accounts. Net-worth Δ across the 691 stock-plan population: **≈ −0.9M (Bucket D’s phantom cash removal exceeds Bucket E’s equity surfacing). Every non-stock-plan Plaid account (~38.1K rows) is byte-identical. Replace these numbers with realized deltas after production deployment.

​
Branch 1 — cash-only bug recovery

Several institutions violate Plaid’s investment-contract by sending only the cash sweep (or zero) in balance.current while positions arrive correctly via /investments/holdings/get. Institutions known to hit this branch: Robinhood, Chase, Schwab, Vanguard (some subtypes), LPL Financial (some subtypes). Bucket sizing at the time the branch was written: 414 accounts under 5% ratio (169Mrecovered),319accountsinthe5−50169M recovered), 319 accounts in the 5-50% ratio band (111M recovered). The MAX(balance, sumCashEquiv) cash formula (not sum) is deliberate — Robinhood/Chase/Schwab report the same cash sweep both as balance.current AND as a money-market position (SPAXX/SWVXX/etc.) inside holdings; summing double-counts. See the file docblock (lib/plaid-investment-value.ts lines 358-393) for the concrete production example that pinned the formula.

​
Branch 2 — balance ≤ 0 (Vanguard pattern)

Institution sends balance = 0 but holdings carry real value in cash equivalents. Trust holdings as truth — otherwise marketValue = balance − sumCashEquiv silently negates to a very large negative number and gets cancelled by cashOnHand.

​
Branch 3 — healthy

The Plaid contract: balance.current IS the total. Cap cashOnHand at balance to avoid over-reporting when stale-balance meets fresh-MM-holdings.

​
2. Plaid institution-gated carve-outs

​
2.1 Wells Fargo — brokerage cash-sweep dedup

Institution: ins_127991 Wells Fargo (allowlist in lib/plaid-brokerage-cash-sweep.ts) Wells Fargo’s “Command Asset Program” holds a brokerage’s uninvested cash in a linked, separately-numbered depository/checking account named “BROKERAGE CASH …” on the SAME Plaid item as the brokerage. Plaid surfaces the same dollars in both places. We suppress the depository at read-time when a coverage guardrail passes (SUM(sibling investment accounts' balance.available) >= depository balance, greedy largest-first). Full detail → docs/BROKERAGE-CASH-SWEEP-DEDUP.md Consumers: Rollout gate: PostHog flag brokerage-cash-sweep-dedup. Kill switch: flag off → all live reads revert to pre-fix behavior. Snapshot rows written during the on-window need a repair script. Fleet audit (2026-07-06): 208 sweep accounts / 127 users / ~$1.70M double-counted. E*TRADE Bank and some credit unions exhibit the same pattern — not yet allowlisted; allowlisting a new institution requires updates in BOTH the TS allowlist AND the view’s IN ('ins_127991') list AND the notice copy.

​
2.2 Wells Fargo — healthy-branch cash floor

Institution: ins_127991 Wells Fargo (same allowlist as §2.1) Because §2.1 suppresses the depository sweep to $0, the brokerage’s cash needs to be preserved on the investment sibling — otherwise the total cash bucket collapses. In Branch 3 of the 5-branch split, the healthy-branch cash floor is raised to balance_available for Wells Fargo brokerage accounts only when the same Plaid item has a suppressed sweep sibling. Everywhere else cashFloor = 0 (no-op). Where: lib/plaid-investment-value.ts (cashFloor variable in Branch 3) and the identical CASE WHEN i.institution_id = 'ins_127991' AND item_sweep.has_suppressed_sweep in account_details’s Branch 3. Margin caveat (documented in code): We deliberately do NOT gate the floor on margin = 0. Wells Fargo reports no margin data to Plaid at all (100% of WF investment accounts have balance_margin_loan_amount = NULL); gating on margin would disable the floor for every WF account. Residual: a WF account secretly on margin with balance_available including borrowing power is undetectable and bounded — net worth stays correct (cash is capped at balance), only the cash/positions split can over-weight cash.

​
2.3 Plaid loan-sign flip

Institutions (as of 2026-07-19 audit): Port Washington State Bank - Business (8 rows / 1.28M),LowellFiveBank(3/1.28M), Lowell Five Bank (3 / 1.17M), Goldman Sachs Private Wealth Management (2 / 0.65M),NicoletNationalBank−BusinessOnline(2/0.65M), Nicolet National Bank - Business Online (2 / 0.58M), Unison Bank - Business (1 / 0.29M),WellsFargo(3/0.29M), Wells Fargo (3 / 0.18M), NexTier Bank (1 / 0.09M),LyonsNationalBank(2/0.09M), Lyons National Bank (2 / 0.07M), Northwestern Mutual (1 / 0.014M),+14smallerinstitutions(21rows/0.014M), + 14 smaller institutions (21 rows / 0.005M). Total: 44 rows / 32 active users / 4.33Mmis−signeddebt/4.33M mis-signed debt / 8.66M net-worth overstatement. Plaid’s contract says loan balance.current is positive-for-debt. A small class of institutions ships it signed-negative (a -$250k mortgage means the user owes 250k).Withoutflipping,thatarrivesas‘−250k). Without flipping, that arrives as `-250k` and reduces liabilities → 2× net-worth overstatement. Full detail → docs/PLAID-LOAN-SIGN-NORMALIZATION.md Predicate (canonical, mirrored in JS and SQL): type = 'loan' AND source_type = 'loan' AND balance < 0 → return -balance. Where: Applied at every read surface; stored data is never mutated. Shipped in PR #2890.

​
2.4 Capital One — min_last_updated_datetime

Institution: ins_128026 Capital One Plaid’s /accounts/balance/get throws for Capital One items unless the request options include min_last_updated_datetime. We pass the ISO string of 30 days ago whenever the item’s institution_id === 'ins_128026'. On retries for ANY institution, we fall back to the same trick with yesterday’s date (Plaid support-recommended workaround for transient balance staleness). Where: Not itself a valuation bug — a connectivity carve-out. Called out here because it’s an institution-gated code path and belongs to the same maintenance surface.

​
2.5 Robinhood + TastyTrade — Plaid→SnapTrade migration cleanup

Institutions: ins_54 Robinhood, ins_116995 TastyTrade When a user connects Robinhood or TastyTrade via SnapTrade, we deactivate any pre-existing Plaid item for that institution to avoid duplicate accounts. Mapping is a small hardcoded lookup in app/api/snaptrade/process-connection/route.ts:
Robinhood users also get a one-time migration email via emails/robinhood-migration-notification.tsx → app/api/robinhood/notify-migration/route.ts. Why: Robinhood support via Plaid is degraded (frequent OAuth breakage, no options-chain data, no crypto positions). SnapTrade is the strongly preferred integration path for both.

​
2.6 E*TRADE — balance settles ahead of its own holding marks

Institution: ins_129473 ETRADE Financial / ETRADE from Morgan Stanley — but the resulting rule is global, not institution-gated. The CFO-7506 price-freshness fix reprices holdings onto securities.close_price when the broker’s institution_price is stale. Its first version also shifted balance.current by the summed reprice delta, assuming the balance and the per-holding marks are two views of one stale snapshot. E*TRADE broke the assumption in the direction that inflates: its balance.current often settles at the session close before the 01:27 UTC sync while its per-holding marks lag a full session — so the balance already carried the move the delta re-added. Illustration (synthetic values, structure from the CFO-7624 incident; the reporter’s reconciled figures live in the Linear ticket, deliberately not in the repo): For the CFO-7624 reporter this overstated three E*TRADE accounts by ~39kcombined,roughlydoublingFriday′sreportedgain,thenprintedaphantom 39k combined, roughly doubling Friday's reported gain, then printed a phantom ~40k “loss” on a market-closed Saturday when the marks caught up and the delta collapsed. The fix: the balance anchor never shifts. Not a smarter guard — measurement killed every conditional variant:
  • Whether a broker’s balance has settled by sync time is a race, not a property: 66% of the top E*TRADE accounts flip between stale-basis and close-basis across 4 decisive sync nights.
  • A basis-detection guard therefore flips on 31% of fleet-wide day-to-day transitions, each flip injecting a phantom day-change the size of the delta (avg ~5,220on>5,220 on >100k accounts) — the same bug class it was meant to fix.
  • Scored against the true session move (the next night’s caught-up marks), deterministic never-shift beats always-shift on day-change error, 52.1Mvs52.1M vs 68.8M across 10,766 large accounts, and cannot fabricate movement.
Deliberate costs:
  • On stale-balance nights (the Schwab shape) the account header lags its repriced detail rows by one session and self-heals on the next sync.
  • Wells Fargo regresses modestly (its balance genuinely carries sweep cash on the stale basis; 166/262 accounts scored worse). If that warrants a WF-only shift, gate it on the existing BROKERAGE_CASH_SWEEP_INSTITUTION_IDS allowlist — a fixed institution gate cannot flip night-to-night. Open follow-up, not implemented.
Per-holding repricing — the actual CFO-7506 fix for movers, detail rows, and per-position gain/loss — is untouched. Where:

​
3. SnapTrade-side carve-outs

​
3.1 Stablecoins as cash (opt-in)

Landed: 2026-07-30 (PR-2, unifying with manual/wallet/coinbase/plaid). Stablecoin positions (USDC, USDT, DAI, …) are auto-classified as crypto unless the user explicitly opts in via treat_as_cash_override = true. The old asymmetric model — SnapTrade defaulted them to cash and required opt-OUT — is retired. All five sources now use the same opt-in model:
  • treat_as_cash_override = true → cash (user explicitly chose)
  • treat_as_cash_override = null → crypto (default)
  • treat_as_cash_override = false → crypto (redundant with null; kept for backward-compat + audit trail)
Canonical predicate: isPureCryptoPosition(pos) in lib/stablecoin-utils.ts, mirrored in SQL as public.is_pure_crypto_snaptrade_position(type_code, symbol, treat_as_cash_override). The DB predicate doesn’t take cash_equivalent; views apply the cash_equivalent = true guard separately. Where the split is computed: Migrations: 20260730_01 → 20260730_02 → 20260731_01 → 20260801_01.

​
3.2 Type fallback (HSA / CSH / NULL)

SnapTrade doesn’t always populate type. Cron and view resolve type via:
Then applies getEffectiveAccountType semantics:
  • User override (source_type IS NOT NULL AND (resolvedType != source_type OR subtype != source_subtype)) → return resolvedType, no promotion.
  • Depository with any active position → promote to 'investment'.
  • Otherwise → resolvedType.
Where:
  • Cron: app/api/cron/process-daily-snapshot/route.ts (processSnapTradeDailySnapshots, ~line 4031-4038)
  • View: SnapTrade branch in account_details (st_effective LATERAL)

​
3.3 SnapTrade balance split

For a resolved-investment SnapTrade account:
Known residual — SnapTrade partial-sync fallback: hooks/queries/use-accounts.ts lines 1735-1737 falls back to balance_current when cash_balance + adjustment === 0 on a depository. Cron does NOT apply this fallback, so daily snapshots and account_details report $0 for those accounts. The AI query inherits the cron’s behavior. If closed upstream (fallback added to cron), mirror in the view in the same PR.

​
4. Interaction matrix

Which special cases can co-fire on the same account? (account here means one row in accounts or snaptrade_accounts.) Notes:
  • Sweep dedup fires only on depository/checking accounts. Stock-plan / cash-only-bug / balance ≤ 0 / healthy branches only apply to investment/retirement/promoted-depository. Sweep dedup and the investment split can co-fire only on the paired investment sibling in the item — never on the same account.
  • WF cash floor applies only inside Branch 3 (healthy). If Branch 0.5 (stock plan), Branch 1 (cash-only bug), Branch 2 (balance ≤ 0), or Branch 0 (margin) fires first, the floor is skipped by CASE ordering. Verified empirically: 0 WF stock-plan accounts in prod.
  • Loan-sign flip applies only to type = 'loan'. All investment branches apply to type IN ('investment', 'retirement') or depository + has_holdings. Disjoint by design.
  • Stock-plan (Branch 0.5) blocks Branches 1-3 by CASE order — this is the point of the fix.
  • Margin (Branch 0) blocks Branches 0.5-3. Currently no prod stock-plan account has balance_margin_loan_amount > 0, so 0.5-vs-0 is only defensive.

​
5. Where these show up (view / cron / frontend / AI)

Same rule in multiple places must stay in lock-step. Every entry in the table below is a place where at least one of these edge cases lives: Any change to a rule must land in all of its consumers in the same PR. The account_details view exists precisely so pure-SQL callers (AI execute_sql, net-worth RPCs) can’t drift from the app; the AI skill doc exists so the LLM’s mental model can’t drift from the view.

​
6. Adding a new special case

Checklist for landing a new institution-gated or subtype-gated rule:
  1. Add the gate in computePlaidInvestmentValue (or the appropriate helper) with a comment explaining the empirical justification.
  2. Mirror the gate in the account_details view. CREATE OR REPLACE VIEW requires a full re-emit — see the CFO-7276 migration as a template.
  3. Thread any new parameter (like subtype for CFO-7276) through every consumer listed in §5.
  4. Update the AI mental model in silvia-database-views-skills/schema/account_details.md (separate repo: CFO-Silvia/skills) so execute_sql callers can still reason about cash_component / investment_component / debt_component.
  5. Add a regression test using real prod numbers (both the affected pattern AND at least one adjacent pattern that must NOT change).
  6. Prod-verify with an OLD-vs-NEW diff across all rows to confirm the blast radius is exactly what you claim.
  7. Add a row to this doc (§1-§3 by category, plus an entry in §4 interaction matrix and §5 layer table).
Prohibited without discussion:
  • Institution-name matching (institution NAMES change; use institution_id).
  • Regex-matching on the user’s custom_name.
  • Writing a “derived” balance back to accounts. Storage is always raw; all rules live at read-time (see BROKERAGE-CASH-SWEEP-DEDUP.md §3 for the full “raw storage, read-time logic” principle).