Portfolio value rollup

Summary

portfolio_value_daily is a derived daily cache of portfolio totals, keyed by (user, scope_kind, scope_id, snapshot_date, base_currency). It holds user-wide totals and per-institution / per-account / per-holding scoped series so detail-page charts read from one table instead of three. It’s rebuildable end-to-end from transactions, observations, and prices. The portfolio-value-rollup job runs in the nightly group, after the price backfill and transfer linking steps before it have finished.

Schema

Column	Meaning
`userId`	uuid → `users.id`.
`scopeKind`	`'user'`, `'institution'`, `'account'`, or `'holding'`.
`scopeId`	The id at that scope. `userId` is the sentinel for `scopeKind='user'` (Postgres composite PKs treat NULL as not-equal-to-NULL, so a non-null sentinel keeps the unique constraint usable).
`snapshotDate`	The date this row is the rollup for.
`baseCurrencyId`	uuid → `tokens.id`. The currency the totals are reported in.
`totalValue`	Decimal string.
`coverageQuality`	`'full' \| 'partial' \| 'estimated' \| 'unknown'`. Drives chart rendering (solid / dashed / gap).
`holdingsWithKnownValue`	Count.
`holdingsTotal`	Count.
`costBasis`	Sum of remaining open lots’ cost in the row’s base currency (FX-converted at purchase time). Nullable for pre-C3 rows.
`realizedPnl`	Cumulative gain/loss from closed positions up to `snapshotDate`.
`unrealizedPnl`	`totalValue - costBasis`.
`computedAt`	When this row’s values last changed. A re-run that reproduces them leaves the row, and this stamp, alone.

Primary key: (userId, scopeKind, scopeId, snapshotDate, baseCurrencyId). Indexes on (userId, snapshotDate desc) and (userId, scopeKind, scopeId, snapshotDate desc) cover the chart reads.

Scopes

Scope	What it rolls up	Used by
`user`	Whole portfolio.	Dashboard headline + main chart.
`institution`	Everything at one institution.	Institution detail page chart.
`account`	Everything in one account.	Account detail page chart.
`holding`	One holding.	Holding detail page chart.

All four scopes for a user are computed in the same backfill pass — no separate per-scope jobs.

How it’s computed

For each (user, scope, date):

For each holding in scope, take its balance on that date from BalanceAtTimeService.balancesFor. It loads each holding’s readings and ledger once, 25 holdings at a time, so no day goes back to the database.
Price each balance in the base currency at the day’s close, from one PriceReader series loaded once per call, for every holding at every day of the chunk that call computes.
Sum the priced balances. Bucket each holding into a coverageQuality based on whether the balance reconstruction had a strong anchor and whether the price was fresh / hub-routed / stale.
Compute costBasis, realizedPnl, unrealizedPnl from the ledger lots up to date.
UPSERT the row.

The job is idempotent per (user, scope, date, base) — re-running just overwrites.

The holding-inclusion rule

A holding counts toward a total when it is shown or was hidden by the closed-position sweep, it is active, and its token is not a scam for its owner. That is the canonical holding-inclusion rule. The scam check is in the query that loads a user’s holdings; the rest is holdingCountsInTotal:

export function holdingCountsInTotal(holding: InclusionHolding): boolean {
  if (holding.isHidden && holding.hiddenBy !== 'auto') return false;
  return holding.isActive;
}

The user, institution and account rows total the holdings that count. The rollup stores holding rows for more than those: every holding that counts, plus the shown holdings that do not count because they are inactive (holdingIsRolledUp). A shown inactive holding keeps a row with nothing in it; a holding its owner hid gets no row. Neither does a shown holding whose token is a scam for its owner: the query that lists a user’s holdings leaves it out, so the rollup never sees it.

export function holdingIsRolledUp(holding: InclusionHolding): boolean {
  return !holding.isHidden || holdingCountsInTotal(holding);
}

The readers that add holding rows up across holdings (the chart series, a day’s total, returns) apply the counting rule to the rows they read. It is duplicated in SQL, in the one inclusion clause (includedHoldingRows) that each of those readers in PortfolioValueDailyRepository filters through, so the chart read path and the dashboard headline always agree. A reader that returns a holding’s own figures applies no rule: findLatestHoldingCostBasis, and findRange asked for a holding scope, return whatever the rollup stored for that holding. Keeping the two definitions in sync is the point — that’s what makes the chart’s latest point reconcile with the headline.

Coverage quality

Bucket	Conditions
`full`	Strong observation/current anchor + a fresh price for every included holding.
`partial`	Some holdings priced, some missing.
`estimated`	Used a stale or hub-routed price for at least one priced holding.
`unknown`	No anchor available for the scope.

The chart UI reads coverageQuality and renders accordingly: a full series is solid, partial is shaded, estimated is dashed, unknown becomes a gap.

Triggering a rebuild

Scheduled. The portfolio-value-rollup job runs nightly, as a step of the nightly group. See the Job catalogue.
On import. A user-initiated import triggers a portfolio-history-backfill user job for that user, scoped to the affected date range.