> ## 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. # VALUATION SURFACES # Valuation Surfaces Map > **Last updated: 2026-07-08** (base branch: `staging`, post-PR #2543) > > **Status: the FX/minor-unit fix has LANDED on every surface.** The currency > fix (`codex/holding-currency-valuation`, PR #2543, merged 2026-07-07) plus the > FX net-worth-RPC migrations and the manual-snapshot restatement are on > `staging`. Every section below was re-verified against the merged tree on > 2026-07-08. > > **Read this before touching any holding/portfolio valuation logic** — FX rates, > currency semantics, minor units (GBX/pence), price sources, or net-worth math. > This file is the central inventory of *every* place that computes, stores, or > displays a money value for holdings and net worth. A change to how a holding is > priced/valued/converted must be applied consistently to **all** of the surfaces > below, or numbers will silently disagree between the dashboard, the chart, the > daily snapshot, the Claude tools, the CSV export, and the admin RPCs. > > **Display currency is NOT a valuation surface.** Showing amounts in EUR/GBP/BTC > (`docs/DISPLAY-CURRENCY.md`) converts USD at print time only; nothing below > changes, and nothing below should ever store a display-currency value. > > **Keep this file updated when you add a new valuation surface.** If you add a > read/write/display of a money value, add it to the numbered list. *** ## TL;DR mental model * **Manual public holdings** (`manual_public_holdings`) are now valued through the **strict path** on every surface: `current_price` comes from `tracked_tickers_latest` in **native feed units** (e.g. GBX pence for LSE tickers) and is converted via `manualHoldingUsd`/`priceToUsd` (TS) or the equivalent minor-unit × FX SQL (views/RPCs). A currency with no FX rate is **excluded from totals and reported — never counted as USD**. Any *new* surface that reads manual holdings must use the same strict path; raw `quantity × current_price` reintroduces the 100× GBX bug. * **Plaid / SnapTrade holdings** are *already* converted to USD at the sync/view layer. They carry `*_usd` columns and account/holding-level `currency_code`. Read surfaces prefer `market_value_usd` and fall back to the native value converted from the row's own currency (`storedHoldingUsd` in `lib/currency.ts`, which leaves out a currency with no rate). **Do not double-convert them.** * **History is frozen.** The daily-snapshot cron writes USD values into snapshot tables that are **never recomputed on read**. A forward-only valuation fix restates *today* but not *yesterday*, creating a visible cliff in the net-worth chart (see Pitfalls). *** ## The surfaces ### 1. `lib/financial-data-cache.ts` — `getFinancialMetrics` (the central hub) Function starts at **line 112**. This is the primary live net-worth / metrics engine consumed by the dashboard, chat tools, PDF export, and proactive prompts. * **Manual public holdings valuation** — lines **683–703** (strict since #2543): ```ts theme={null} const { marketValueUsd, supported } = manualHoldingUsd( holding.quantity, holding.tracked_tickers_latest?.current_price, holding.cost_basis, holding.tracked_tickers_latest?.currency_name, exchangeRates, ) if (!supported) { reportUnsupportedHoldingCurrency(...); return } // excluded, never USD manualPublicHoldingsValue += marketValueUsd ?? 0 ``` * **`netWorth` aggregation** — line **1027** (sums all asset buckets minus debts). * **Plaid crypto is counted exactly once, through `marketValue`.** The `accounts` query does NOT exclude `subtype = 'crypto exchange'` (Kraken, River, Gemini, Binance US) — their hybrid balance enters `marketValue` and the `(totalValue - plaidValue)` telescoping cancels the same tokens out of the crypto leg. Excluding those accounts while keeping the subtraction dropped the whole exchange from net worth (CFO-7103). `getAccountDetails` and `getInvestmentDetails` DO exclude them on purpose: they are account listings, and the exchange tokens are itemised by `getCryptoHoldingsDetails`. * **`portfolioValue`, `totalCryptoValue`, `totalCash` are disjoint, but `netWorth` is authoritative.** `portfolioValue = marketValue - plaidCryptoInsideMarketValue + manual + snaptradePositions` removes Plaid crypto (opt-in went to `totalCash`, the rest to `totalCryptoValue`). `plaidCryptoInsideMarketValue` is `plaidValue` capped PER ACCOUNT at that account's own valuation (`lib/plaid-crypto-netting.ts`): exchange balances are often weeks staler than the nightly-repriced coins, and an uncapped subtraction would push the account's investments slice negative. When the cap binds, the three buckets no longer partition the liquid side exactly: the investments slice floors at zero, the crypto bucket keeps the fresh figure, and the bucket sum OVERSTATES the liquid side of `netWorth` by the stale delta. `netWorth` cancels the crypto leg in full and is unaffected, so consumers must quote `netWorth` and treat the buckets as a breakdown, never as addends. Mirrors `useNetWorth().breakdown.investmentsForAssetsBreakdown` on the client (CFO-7886 / CFO-7423 / CFO-7203 / CFO-7796 double-counts). * Downstream consumers that inherit this valuation (do not re-implement, but re-verify when the shape changes): * `app/api/chat/tools.ts` — `getFinancialMetrics` tool (line \~110) **and goals progress** at lines \~447–457 (`getFinancialMetrics(...).netWorth`). * `app/api/chat/tools/pdf-export-tool.ts` (line \~44) — PFS/PDF export. * `app/api/send-daily-email/route.ts` — receives `netWorth` as a POST param (computed upstream; not its own surface). ### 2. `lib/financial-data-cache.ts` — `getInvestmentDetails` Function at line **1347**. Manual holdings valuation at lines **1432–1440** (strict since #2543): `manualHoldingUsd(...)` returning `{ marketValueUsd, costBasisUsd, supported }`, with unsupported currencies excluded. Connected (Plaid) holdings above it prefer `holding.market_value_usd ?? holding.market_value` — both branches are now USD-consistent. ### 3. `lib/financial-data-cache.ts` — `getUnrealizedGainsTaxDetails` Function at line **2595**; taxable-gains estimates. Manual holdings selected at line **\~2655** and valued at lines **2763–2770** (strict since #2543): `manualHoldingUsd(...)` → `marketValueUsd` against `costBasisUsd` (cost basis is user-entered USD, passed through unconverted). Unsupported currencies are excluded from the gains estimate. The old raw-native-price-vs-USD-cost-basis asymmetry with the Plaid branch is gone. ### 4. `app/api/cron/process-daily-snapshot/route.ts` — FROZEN USD history writer Writes end-of-day USD history that is **never recomputed on read**: * `snapshots_by_manual_public_holding` — per-holding rows. Manual valuation at line **2228** (strict since #2543): `manualHoldingUsd(...)`, unsupported currencies skipped and reported to Sentry; inserted at line \~2262. * `daily_snapshots_summary` — the summary row upserted at line **2675**. * `portfolio_daily_snapshots` (partitioned) is no longer written. Existing rows are left in place and nothing reads them. History charts still read `daily_snapshots_summary`. Per-portfolio history is planned as a read-time sum over the per-root tables (`docs/multi-portfolio/MULTI-PORTFOLIO-ENG-DESIGN.md` §3) and is not built yet. * Uses `convertToUsd` for Plaid/SnapTrade major-currency amounts and `manualHoldingUsd` for manual holdings. * **History was restated** for the pre-fix period via `20260707090000_restate_manual_holding_snapshots_data.sql` (CFO-5666), so the chart-cliff risk for *this* fix is handled — but the pitfall still applies to any future valuation change. > `app/api/cron/daily-snapshots/route.ts` (singular) is **legacy/dead** — the > entire body is commented out and the live `GET` returns 405. Not a surface. > The active snapshot writer is `process-daily-snapshot` above. > **Portfolio scope on the SQL views (#5 to #7).** Since > `20261009_11_financial_views_portfolio_id.sql` every financial view ends with > `portfolio_id`: the root's own column, or the account's for holdings and > transactions. A scoped read filters the view's output; it never filters the > inputs. That matters for the cash-sweep dedup, which is decided over the whole > Item (`docs/BROKERAGE-CASH-SWEEP-DEDUP.md` §5). A migration that replaces one > of these views must keep `portfolio_id` on every branch, which > `lib/migrations/__tests__/financial-views-portfolio-id.test.ts` checks. ### 5. DB view `public_holding_details` (lives in the database) Change it via a **new migration** (latest def: `supabase/migrations/20261009_11_financial_views_portfolio_id.sql`). The **manual branch** now converts (strict since #2543): ```sql theme={null} mph.quantity * t.current_price * conv.usd_factor AS market_value -- usd_factor = (0.01 if minor unit: GBX/GBp/Zac/ILA) / exchange_rates.rate; -- NULL (fail-safe, excluded) when the currency has no FX rate ``` The **Plaid / SnapTrade branches convert** via `COALESCE(x_usd, x / er.rate)` joined on `exchange_rates`. This view feeds `holding_details` (#6), the holdings UI, and the Claude DB tools (referenced in `app/api/claude/config/system-prompt.ts`, e.g. lines 55, 322). ### 6. DB view `holding_details` Cross-asset-class rollup: `UNION` of `public_holding_details` + `crypto_holding_details` + `private_investments`. Any change to #5 flows through here. Referenced as the Tier-2 view in `app/api/claude/config/system-prompt.ts` (line \~325). ### 7. DB view `account_details` — Silvia AI's Tier-1 ACCOUNTS view Unified account-level rollup (`accounts` (Plaid) ∪ `manual_accounts` ∪ SnapTrade). Change via **migration** (latest def: `supabase/migrations/20261009_11_financial_views_portfolio_id.sql`). Not a *holdings* surface — it never reads `manual_public_holdings` — but it is a **money-value surface**: the Silvia AI DB tools are explicitly told to prefer it for accounts / balances / cash (`app/api/claude/config/system-prompt.ts` lines 55 and \~322, Tier 1), so any change to balance-USD or cash-sweep semantics must land here or the AI's numbers diverge from the dashboard. * **Plaid branch** — converts to USD: `COALESCE(a.balance_current_usd, a.balance_current / NULLIF(er.rate, 0))` (same pattern for `balance_available` and `market_value`) joined on `exchange_rates`. * **SnapTrade branch** — converts balances the same way and **computes** a holdings value: `SUM(COALESCE(p.price_usd, p.price) * p.units)`, plus `snaptrade_usd_cash` and cash-equivalent adjustments. * **Manual-accounts branch** — raw `ma.balance` (user-entered, treated as USD; `currency_code` is NULL). * **Cash-sweep dedup** — flags `is_duplicate_cash_sweep`, zeroes the duplicate balance, preserves `pre_dedup_balance`. **Keep in lockstep** with the net-worth RPCs (#8) and the `account_effective_balances` view (`20260706100000_create_account_effective_balances_view.sql`). * **Plaid crypto-exchange accounts are INCLUDED** (since `20260904_01_account_details_include_plaid_crypto_exchange.sql`, PR #3718). The old `subtype <> 'crypto exchange'` exclusion, combined with the net-worth recipe subtracting all Plaid crypto from `SUM(investment_component)`, dropped the whole exchange from the AI net worth (CFO-7103). The Plaid branch now exposes `plaid_crypto_value` (USD value of the account's non-opt-in crypto rows, same predicate as the plaid branch of `crypto_holding_details`), the mirror of `snaptrade_crypto_value`, so `balance - plaid_crypto_value` is the non-crypto slice. ### 8. DB RPCs `get_user_net_worth_for_clerk` and `get_user_net_worth_export` SQL net worth (admin/alert path). Change via **migration** (latest defs: `supabase/migrations/20260707120000_get_user_net_worth_for_clerk_fx_over_cash_dedup.sql` and the paired `…120100_…export…` migration). The `agg_public` CTE now FX-converts (strict since CFO-5666): minor units (GBX/GBp/Zac/ILA) × 0.01, ÷ `exchange_rates.rate`, currencies with no rate **excluded**, and the **per-holding under-\$1B sanity cap** retained: ```sql theme={null} SELECT SUM(CASE WHEN usd.val < 1000000000 THEN usd.val ELSE 0 END) AS val -- usd.val = quantity × COALESCE(NULLIF(current_price,0), prev_close) -- × (0.01 if minor unit) / er.rate (row dropped if no rate) ``` Consumed by `lib/slack-revenue.ts` (line \~2124), which states it mirrors `get_user_net_worth_export` exactly — **keep the two RPCs in lockstep.** ### 9. `hooks/queries/use-holdings.ts` and `hooks/queries/use-manual-public-holdings.ts` Client-side holding values. * `use-holdings.ts` — connected holdings convert via `h.market_value_usd ?? convertToUsd(...)` (line \~149); **manual holdings now convert via `manualHoldingUsd(...)`** (line \~247, strict since #2543), carrying `marketValueUsd` / `marketValueMajor` / `supported` so the UI can flag unsupported currencies instead of miscounting them. * `use-manual-public-holdings.ts` still returns **raw rows** from `manual_public_holdings` joined to `tracked_tickers_latest` (`current_price` + `currency_name`, lines \~57–60) — conversion happens in the consumers (`use-holdings.ts` above). Beware direct readers like `app/(app)/public-investments/_components/public-investments-helpers.ts` (line 66: `Number(holding.market_value || 0)`), which display whatever pre-computed value they're handed. ### 10. `hooks/queries/use-daily-snapshots.ts` — net-worth CHART Reads **frozen** `daily_snapshots_summary` (line 55) for the chart's `net_worth` series. Because this is stored history, correcting live valuation **without restating history** produces a step-cliff between the last frozen day and the newly-corrected live day. Restate history or gate the fix by date. (For the CFO-5666 FX fix this was handled by `20260707090000_restate_manual_holding_snapshots_data.sql`.) ### 11. `lib/currency.ts` — conversion primitives * `fetchExchangeRates(supabase)` — loads `exchange_rates` into a map (`rate` = **foreign units per 1 USD**). * `convertToUsd(amount, code, rates)` — **lenient**: on unknown currency it logs `"treating as USD"` and returns the amount unchanged (legacy fallback). * `getExchangeRateInfo(code, rates)` — rate lookup, null for USD/unknown. * **`priceToUsd` and `normalizeCurrency` LANDED on `staging` 2026-07-07** (PR #2543, `codex/holding-currency-valuation`), together with the FX net-worth-RPC migrations (`20260707120000/…120100`) and the manual-snapshot restatement (`20260707090000_restate_manual_holding_snapshots_data.sql`). They are the strict path: * normalize minor units: GBX/GBp → GBP ÷ 100, Zac → ZAR ÷ 100, ILA → ILS ÷ 100; * **fail-safe**: an unsupported currency returns `{ usd: null, supported: false }` → **exclude from totals, flag in UI, report to Sentry — NEVER default to USD** (unlike the lenient `convertToUsd`). * **`manualHoldingUsd(quantity, currentPrice, costBasis, currencyName, rates)`** (line \~179) — the manual-holding wrapper around `priceToUsd`; returns `{ marketValueUsd, marketValueMajor, costBasisUsd, supported, majorCode }`. This is what surfaces #1–#4 and #9 call. Verified 2026-07-08: the strict path is wired into all TS surfaces; the SQL surfaces (#5, #7, #8) implement the equivalent minor-unit × FX logic inline. ### 12. Data tables (the price/FX source of truth) * `exchange_rates` — live rates; `rate` = **foreign units per 1 USD** (e.g. CAD 1.3676 ⇒ 1 USD = 1.3676 CAD). To go native→USD, **divide** by rate. * `exchange_rates_historical` — daily FX history (table created 2026-03-05). No application-code consumers found on `staging`; used by DB-side USD refresh. * `tracked_tickers_latest` — latest prices in **NATIVE FEED UNITS** (incl. GBX pence for LSE). `current_price` is the raw feed price; `currency_name` = the feed currency (populated 2026-07 via Finnhub `/stock/symbol`). Written by the price crons (`fetch-real-time-stock-prices`, `fetch-fund-prices`, `fetch-bond-prices`, `sync-tracked-securities`) and `lib/market-data/provider.ts` / `lib/market-data/tracked-ticker-metadata.ts`. `currency_name` resolution is backfilled by `app/api/cron/backfill-tracked-ticker-currencies/route.ts`. ### 13. Additional surfaces verified * **CSV export** — `lib/utils.ts` `exportToCSV` (line 46) emits `'Value': holding.market_value` (line 133). Displays a **pre-computed** value; it does no math but will surface whatever upstream valuation produced. Whatever fix you apply upstream flows here automatically — verify the column. * **Holdings table** — `components/holdings-table.tsx` renders `market_value` and a native-currency badge (`market_value_native` + `native_currency_code`, lines \~382–388) for connected holdings; formatting only, no re-valuation. * **Radar simulation** — `app/api/radar/silvia-simulation/route.ts` only does a `count` (`head: true`) on `manual_public_holdings` (line 208). **Not a valuation surface** (presence check only). * **Holdings import** — `app/api/investments/manual/route.ts` and `app/api/process-holdings-import/route.ts` write `manual_public_holdings` rows (quantity, cost\_basis, symbol → `tracked_tickers_latest`). Write path, not a valuation surface, but the source of the quantities every surface above reads. Surfaces intentionally **omitted** because they compute no money value on `staging`: `app/api/cron/daily-snapshots/route.ts` (dead 405 stub), `app/api/email/generate-financial-statements/route.ts` (body commented out). *** ## Pitfalls * **GBX pence vs GBP pounds is a 100× error.** `tracked_tickers_latest.current_price` is stored in native feed units; LSE feeds quote pence (GBX). Multiplying `quantity × current_price` without dividing by 100 overstates UK holdings 100×. * **Never default an unknown currency to USD.** The lenient `convertToUsd` does exactly that ("treating as USD"); the strict `priceToUsd` fail-safe excludes + flags + reports to Sentry instead. Silent USD-default corrupts totals. * **A NULL `currency_name` is valued as USD, so the label is load-bearing.** `priceToUsd` treats a blank label as USD (only an *unknown* non-blank code is excluded). Anything that can NULL a label silently re-prices that ticker. The 2026-08-11 batched upserts in `fetch-real-time-stock-prices` did exactly that until CFO-8286: PostgREST NULLs every column a row in a multi-row write does not carry, so labels churned (≈1,520 unlabelled at once) and hand-fixes like FAIR.L reverted. Batch only rows with the same columns (`lib/market-data/group-rows-by-column-set.ts`). Finnhub's exchange listing labels almost every foreign ticker; the daily `check-ticker-currencies` cron raises a Sentry issue (with the fix SQL) for any held foreign ticker still blank, so it can be labelled by hand. * **Frozen snapshots mean forward-only fixes cliff the chart.** Snapshot tables (`daily_snapshots_summary`, `snapshots_by_manual_public_holding`, `portfolio_daily_snapshots`) are never recomputed on read. Fix live valuation and history diverge, producing a visible step in the net-worth chart. * **Live-vs-snapshot skew creates phantom 1-day changes** (see CFO-6403). Compare live values against a snapshot captured at a different instant and you get a fake gain/loss. Compare like-for-like capture moments. * **Manual-holding `cost_basis` is user-entered in USD** per the Add Investment UI ("\$" placeholder, Total / Per-Share toggle). **Do not FX-convert it** — it is already USD. Only `current_price` needs FX/minor-unit handling. * **Plaid / SnapTrade already convert** at the sync/view layer; their `currency_code` is account/holding-level and they carry `*_usd` columns. Read the `*_usd` value and **do not double-convert**. *** ## Checklist for agents changing valuation logic If you change how a holding is priced, valued, or currency-converted, you must touch or explicitly verify **each** numbered surface above: 1. `getFinancialMetrics` manual valuation (lines 683–703) + `netWorth` (line 1027). 2. `getInvestmentDetails` manual valuation (lines 1432–1440). 3. `getUnrealizedGainsTaxDetails` manual valuation (lines 2763–2770). 4. `process-daily-snapshot` snapshot writers (lines 2228, 2675, \~3010) — and decide whether history must be **restated** (see chart-cliff pitfall). 5. DB view `public_holding_details` (new migration). 6. DB view `holding_details` (new migration; flows from #5). 7. DB view `account_details` (new migration; Silvia AI's Tier-1 accounts view — keep balance-USD + cash-sweep-dedup semantics in lockstep with the RPCs and `account_effective_balances`). 8. RPCs `get_user_net_worth_for_clerk` **and** `get_user_net_worth_export` (migrations; keep in lockstep). 9. `use-holdings.ts` + `use-manual-public-holdings.ts` (and their consuming components / helpers). 10. `use-daily-snapshots.ts` chart series. 11. `lib/currency.ts` (`priceToUsd`/`normalizeCurrency` — strict path, landed 2026-07-07 via PR #2543). 12. Confirm the data-source semantics: `exchange_rates` (foreign-per-USD), `tracked_tickers_latest` (native feed units + `currency_name`). 13. Downstream displays: CSV export (`lib/utils.ts`), holdings table, PDF export, goals progress, daily email. Then: * **Run** `lib/__tests__/currency.test.ts` (`npm test` / Vitest) and extend it if you touch `lib/currency.ts`. * **Compare against the `impact_by_account` baseline** (DB) before/after to confirm per-account totals only move where you intend. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.