​

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)

• Manual public holdings valuation — lines 683–703 (strict since #2543):
  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

​

3. lib/financial-data-cache.ts — getUnrealizedGainsTaxDetails

​

4. app/api/cron/process-daily-snapshot/route.ts — FROZEN USD history writer

• 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)

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

​

6. DB view holding_details

​

7. DB view account_details — Silvia AI’s Tier-1 ACCOUNTS view

• 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

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)

​

9. hooks/queries/use-holdings.ts and hooks/queries/use-manual-public-holdings.ts

• 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

​

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.

​

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

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.

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