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

# INSTITUTION EDGE CASES

# 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](#1-plaid-investment-value-the-5-branch-split)
* [2. Plaid institution-gated carve-outs](#2-plaid-institution-gated-carve-outs)
  * [2.1 Wells Fargo — brokerage cash-sweep dedup](#21-wells-fargo--brokerage-cash-sweep-dedup)
  * [2.2 Wells Fargo — healthy-branch cash floor](#22-wells-fargo--healthy-branch-cash-floor)
  * [2.3 Plaid loan-sign flip](#23-plaid-loan-sign-flip)
  * [2.4 Capital One — `min_last_updated_datetime`](#24-capital-one--min_last_updated_datetime)
  * [2.5 Robinhood + TastyTrade — Plaid→SnapTrade migration cleanup](#25-robinhood--tastytrade--plaidsnaptrade-migration-cleanup)
  * [2.6 E\*TRADE — balance settles ahead of its own holding marks](#26-etrade--balance-settles-ahead-of-its-own-holding-marks)
* [3. SnapTrade-side carve-outs](#3-snaptrade-side-carve-outs)
  * [3.1 Stablecoins as cash (opt-in)](#31-stablecoins-as-cash-opt-in)
  * [3.2 Type fallback (HSA / CSH / NULL)](#32-type-fallback-hsa--csh--null)
  * [3.3 SnapTrade balance split](#33-snaptrade-balance-split)
* [4. Interaction matrix](#4-interaction-matrix)
* [5. Where these show up (view / cron / frontend / AI)](#5-where-these-show-up-view--cron--frontend--ai)
* [6. Adding a new special case](#6-adding-a-new-special-case)

***

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

`computePlaidInvestmentValue` in [`lib/plaid-investment-value.ts`](../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`](../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.

| # | Branch | Fires when | `cashOnHand` | `marketValue` | Ticket |
| - | - | - | - | - | - |
| 0 | **Margin** | `balance_margin_loan_amount_usd > 0 AND balance > 0` | `MAX(0, balance − sumNonCashHoldings)` | `balance − cashOnHand` | inline |
| 0.5 | **Stock plan** | `LOWER(TRIM(subtype)) = 'stock plan' AND sumNonCashHoldings > 0` | `0` | `MAX(balance, sumNonCashHoldings)` | [CFO-7276](https://linear.app/silvia-financial/issue/CFO-7276) |
| 1 | **Cash-only bug recovery** | `sumNonCashHoldings > 0 AND balance < sumNonCashHoldings × 0.5` | `MAX(0, balance, sumCashEquiv)` | `sumNonCashHoldings` | CFO-3xxx era |
| 2 | **Balance ≤ 0 (Vanguard pattern)** | `balance <= 0` | `sumCashEquiv` | `sumNonCashHoldings` | inline |
| 3 | **Healthy (Plaid contract)** | else | `MIN(MAX(sumCashEquiv, cashFloor), balance)` — see [§2.2](#22-wells-fargo--healthy-branch-cash-floor) | `balance − cashOnHand` | Plaid contract |

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:

| Institution | # accts |
| - | - |
| E\*TRADE Financial | 300 |
| Charles Schwab | 117 |
| E\*TRADE from Morgan Stanley | 114 |
| Merrill Lynch - Benefits | 64 |
| Morgan Stanley - StockPlan Connect / Benefit Access | 35 |
| Principal Financial Group - Participant Logon | 26 |
| Solium Shareworks by Morgan Stanley | 22 |
| Other (Bank of America, Fidelity, Merrill, Northwestern Mutual, Raymond James, EquatePlus, ...) | 13 |

`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](https://github.com/CFO-Silvia/Silvia-Web/pull/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** 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** 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 ($169M 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`](../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`](./BROKERAGE-CASH-SWEEP-DEDUP.md)

**Consumers**:

* Read-time client: [`hooks/queries/use-accounts.ts`](../hooks/queries/use-accounts.ts)
* AI/tool cache: [`lib/financial-data-cache.ts`](../lib/financial-data-cache.ts) (`getFinancialMetrics`, `getAccountDetails`)
* AI `execute_sql`: [`public.account_details`](../supabase/migrations/20260806_01_account_details_universal_components.sql) → `public.account_effective_balances`
* Daily snapshot cron: [`app/api/cron/process-daily-snapshot/route.ts`](../app/api/cron/process-daily-snapshot/route.ts)
* Hybrid balance layer: [`lib/plaid-investment-balance-override.ts`](../lib/plaid-investment-balance-override.ts)
* Goals (chat + voice): deliberately use `pre_dedup_balance_current` — goals track the REAL brokerage cash

**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](#1-plaid-investment-value-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), Lowell Five Bank (3 / $1.17M), Goldman Sachs Private Wealth Management (2 / $0.65M), Nicolet National Bank - Business Online (2 / $0.58M), Unison Bank - Business (1 / $0.29M), Wells Fargo (3 / $0.18M), NexTier Bank (1 / $0.09M), Lyons National Bank (2 / $0.07M), Northwestern Mutual (1 / $0.014M), + 14 smaller institutions (21 rows / $0.005M). **Total: 44 rows / 32 active users / $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). Without flipping, that arrives as `-$250k\` and *reduces* liabilities → 2× net-worth overstatement.

**Full detail** → [`docs/PLAID-LOAN-SIGN-NORMALIZATION.md`](./PLAID-LOAN-SIGN-NORMALIZATION.md)

**Predicate** (canonical, mirrored in JS and SQL): `type = 'loan' AND source_type = 'loan' AND balance < 0` → return `-balance`.

**Where**:

* TS helpers: [`lib/liability-balance.ts`](../lib/liability-balance.ts)
* SQL view: [`supabase/migrations/20260719_01_normalize_plaid_loan_sign_in_account_details.sql`](../supabase/migrations/20260719_01_normalize_plaid_loan_sign_in_account_details.sql)

Applied at every read surface; stored data is never mutated. Shipped in [PR #2890](https://github.com/CFO-Silvia/Silvia-Web/pull/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**:

* Daily cron: [`app/api/cron/process-daily-snapshot/route.ts`](../app/api/cron/process-daily-snapshot/route.ts) around line 505-520
* Balances fetch: [`app/api/fetch-balances/route.ts`](../app/api/fetch-balances/route.ts)

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`](../app/api/snaptrade/process-connection/route.ts):

```ts theme={null}
const PLAID_MIGRATION_MAP: Record<string, string> = {
  'robinhood': 'ins_54',
  'tastytrade': 'ins_116995',
}
```

Robinhood users also get a one-time migration email via [`emails/robinhood-migration-notification.tsx`](../emails/robinhood-migration-notification.tsx) → [`app/api/robinhood/notify-migration/route.ts`](../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` E*TRADE Financial / E*TRADE 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):

| signal | value | basis |
| - | - | - |
| `SUM(institution_value)` | 1,000,000.00 | Thursday's close — the stale marks |
| `balance.current` | 1,005,500.00 | Friday's settled close |
| `SUM(quantity × close_price)` | 1,005,100.00 | Friday's close, security feed |
| repricing delta | +5,100.00 | |
| **shifted anchor (pre-fix)** | **1,010,600.00** | above every honest reading |

For the CFO-7624 reporter this overstated three E\*TRADE accounts by \~$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,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.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**:

* TS: [`lib/plaid-price-freshness.ts`](../lib/plaid-price-freshness.ts) — `adjustPlaidValuationInputs`
* SQL mirror: [`supabase/migrations/20260824_01_plaid_balance_anchor_unshift.sql`](../supabase/migrations/20260824_01_plaid_balance_anchor_unshift.sql) — `account_details.plaid_fresh`
* History repair (manual, off-peak, run once after release): [`sql-queries/cfo-7624-repair-balance-anchor-shift-history.sql`](../sql-queries/cfo-7624-repair-balance-anchor-shift-history.sql) — fixes the inflated 2026-08-20 → 08-23 snapshot rows the chart reads

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

* Frontend: [`hooks/queries/use-accounts.ts`](../hooks/queries/use-accounts.ts) / [`hooks/queries/use-crypto-holdings.ts`](../hooks/queries/use-crypto-holdings.ts)
* Cron: [`app/api/cron/process-daily-snapshot/route.ts`](../app/api/cron/process-daily-snapshot/route.ts) (`processSnapTradeDailySnapshots`)
* View: `public.account_details` SnapTrade branch (`snaptrade_crypto_value`, `snaptrade_cash_adjustment`)

Migrations: [`20260730_01`](../supabase/migrations/20260730_01_stablecoin_symbol_predicates.sql) → [`20260730_02`](../supabase/migrations/20260730_02_account_details_stablecoin_as_cash.sql) → [`20260731_01`](../supabase/migrations/20260731_01_crypto_holding_details_exclude_opt_in_non_snaptrade.sql) → [`20260801_01`](../supabase/migrations/20260801_01_snaptrade_opt_in_stablecoins.sql).

### 3.2 Type fallback (HSA / CSH / NULL)

SnapTrade doesn't always populate `type`. Cron and view resolve type via:

```
resolvedType = COALESCE(
  sa.type,
  CASE WHEN sa.account_type IN ('HSA', 'CSH')
       THEN 'depository'
       ELSE 'investment'
  END
)
```

Then applies [`getEffectiveAccountType`](../lib/account-type-utils.ts) 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:

```
investmentValue = balance − snaptrade_usd_cash − snaptrade_crypto_value − snaptrade_cash_adjustment
cashOnHand      = snaptrade_usd_cash + snaptrade_cash_adjustment  (when effective type is depository or investment)
debt            = -balance                                         (SnapTrade signs debt negative)
```

**Known residual** — SnapTrade partial-sync fallback: [`hooks/queries/use-accounts.ts`](../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`.)

| ← Applies to \ May co-fire with → | Sweep dedup | WF cash floor | Loan-sign flip | Stock-plan | Cash-only bug | Balance ≤ 0 | Margin |
| - | - | - | - | - | - | - | - |
| **Sweep dedup** (§2.1) | — | no\* | no | no | no | no | no |
| **WF cash floor** (§2.2) | (paired) | — | no | **no** ✱ | **no** ✱ | **no** ✱ | **no** ✱ |
| **Loan-sign flip** (§2.3) | no | no | — | no | no | no | no |
| **Stock-plan** (§1 branch 0.5) | no | no | no | — | (blocks) | (blocks) | (blocks) |
| **Cash-only bug** (§1 branch 1) | no | no | no | (blocked by 0.5) | — | no | no |
| **Balance ≤ 0** (§1 branch 2) | no | no | no | (blocked by 0.5) | no | — | no |
| **Margin** (§1 branch 0) | no | no | no | (wins over 0.5) | (wins) | (wins) | — |

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:

| Layer | File | Which rules |
| - | - | - |
| Core Plaid split | [`lib/plaid-investment-value.ts`](../lib/plaid-investment-value.ts) | Branches 0 / 0.5 / 1 / 2 / 3; WF cash floor |
| Wells Fargo sweep detector | [`lib/plaid-brokerage-cash-sweep.ts`](../lib/plaid-brokerage-cash-sweep.ts) | §2.1 |
| Loan sign helper | [`lib/liability-balance.ts`](../lib/liability-balance.ts) | §2.3 |
| Stablecoin classifier | [`lib/stablecoin-utils.ts`](../lib/stablecoin-utils.ts) | §3.1 |
| Effective type | [`lib/account-type-utils.ts`](../lib/account-type-utils.ts) | §3.2 |
| Dashboard totals | [`hooks/queries/use-accounts.ts`](../hooks/queries/use-accounts.ts) | All (consumes helpers above) |
| AI cache | [`lib/financial-data-cache.ts`](../lib/financial-data-cache.ts) | All (consumes helpers above) |
| AI chat/voice hybrid | [`lib/plaid-investment-balance-override.ts`](../lib/plaid-investment-balance-override.ts) | Sweep dedup + Plaid split |
| Daily snapshot cron | [`app/api/cron/process-daily-snapshot/route.ts`](../app/api/cron/process-daily-snapshot/route.ts) | All + Capital One §2.4 |
| SnapTrade migration cleanup | [`app/api/snaptrade/process-connection/route.ts`](../app/api/snaptrade/process-connection/route.ts) | §2.5 |
| AI `execute_sql` — Plaid split | [`supabase/migrations/20260810_02_account_details_stock_plan_no_phantom_cash.sql`](../supabase/migrations/20260810_02_account_details_stock_plan_no_phantom_cash.sql) | Branches 0 / 0.5 / 1 / 2 / 3 + WF cash floor |
| AI `execute_sql` — sweep dedup | [`supabase/migrations/20260707100000_dedup_cash_sweep_in_account_details.sql`](../supabase/migrations/20260707100000_dedup_cash_sweep_in_account_details.sql) + `account_effective_balances` | §2.1 |
| AI `execute_sql` — loan sign | [`supabase/migrations/20260719_01_normalize_plaid_loan_sign_in_account_details.sql`](../supabase/migrations/20260719_01_normalize_plaid_loan_sign_in_account_details.sql) | §2.3 |
| AI `execute_sql` — stablecoins | [`supabase/migrations/20260801_01_snaptrade_opt_in_stablecoins.sql`](../supabase/migrations/20260801_01_snaptrade_opt_in_stablecoins.sql) | §3.1 |
| Skills doc (AI mental model) | [`silvia-database-views-skills/schema/account_details.md`](https://github.com/CFO-Silvia/skills/blob/main/silvia-database-views-skills/schema/account_details.md) (separate repo: [`CFO-Silvia/skills`](https://github.com/CFO-Silvia/skills)) | All |

**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](#5-where-these-show-up-view--cron--frontend--ai).
4. **Update the AI mental model** in [`silvia-database-views-skills/schema/account_details.md`](https://github.com/CFO-Silvia/skills/blob/main/silvia-database-views-skills/schema/account_details.md) (separate repo: [`CFO-Silvia/skills`](https://github.com/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`](./BROKERAGE-CASH-SWEEP-DEDUP.md) §3 for the full "raw storage, read-time logic" principle).


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