docs/features/allocations/portfolio-views-design.md
PR #938 introduces
user-defined portfolio grouping across Income, Activity,
Holdings, Performance, and Dashboard views.
The feature direction is valuable, but the current implementation should be
treated as a prototype. It introduces a portfolios table with JSON account
IDs and passes composite MULTI:id,id strings through frontend, services, and
repositories. That approach creates correctness and maintainability issues:
accounts.group should remain account organization metadata instead of
beingThe target model should separate real accounts, account grouping, and saved
portfolio reporting scopes.
In investment software, a portfolio is a collection of financial assets
managed
or evaluated together toward an objective.
A portfolio is not necessarily one account. For example:
In Wealthfolio:
Account is the ledger or custody boundary;Portfolio should be a saved reporting scope over accounts;The target design should follow these principles:
accounts.group as account organization metadata;REAL/floatingA real custody or ledger container.
Examples:
The existing accounts.group field.
Purpose:
Rule:
account_groups table, not a portfolio concept.User-facing saved reporting scope.
Purpose:
Rule:
Not part of this PR.
Wealthfolio already has asset taxonomies/custom groups for asset classification:
Do not mix asset grouping into this account portfolio feature. A future richer
portfolio model could combine an account filter with an optional asset filter,
but
this PR should stay account-scoped.
Keep account grouping on accounts.group for now, and do not migrate it into
portfolio tables.
Normalize saved portfolios into a portfolio table plus membership rows.
CREATE TABLE portfolios (
id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL CHECK (length(trim(name)) > 0),
description TEXT,
sort_order INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
updated_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);
CREATE UNIQUE INDEX idx_portfolios_name_unique
ON portfolios(lower(trim(name)));
CREATE TABLE portfolio_accounts (
id TEXT PRIMARY KEY NOT NULL,
portfolio_id TEXT NOT NULL,
account_id TEXT NOT NULL,
sort_order INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
FOREIGN KEY (portfolio_id)
REFERENCES portfolios(id)
ON DELETE CASCADE,
FOREIGN KEY (account_id)
REFERENCES accounts(id)
ON DELETE CASCADE,
UNIQUE(portfolio_id, account_id)
);
CREATE INDEX idx_portfolio_accounts_portfolio
ON portfolio_accounts(portfolio_id, sort_order);
CREATE INDEX idx_portfolio_accounts_account
ON portfolio_accounts(account_id);
portfolio_accounts.id should be deterministic, for example:
pfm_{portfolio_id}_{account_id}
This gives device sync a stable row identity for membership changes.
![[screenshot 2026-05-11 à 15.30.52.png]]
erDiagram ACCOUNTS { text id PK text name text group "account organization metadata" text currency boolean is_active boolean is_archived }
PORTFOLIOS {
text id PK
text name UK
text description
integer sort_order
text created_at
text updated_at
}
PORTFOLIO_ACCOUNTS {
text id PK "pfm_{portfolio_id}_{account_id}"
text portfolio_id FK
text account_id FK
integer sort_order
text created_at
}
ACCOUNTS ||--o{ PORTFOLIO_ACCOUNTS : "can belong to many"
PORTFOLIOS ||--o{ PORTFOLIO_ACCOUNTS : "has members"
Total Portfolio / All Accounts is intentionally not shown as a row. It is
a
virtual system scope represented by AccountFilter::All, while snapshots or
valuations may still use internal cache IDs for calculated data.
Use a typed account filter everywhere instead of composite string IDs.
Avoid the name AccountScope because the performance module already has
PerformanceScope, which means "flow boundary" rather than "which accounts
are
included".
type AccountFilter =
| { type: "all" }
| { type: "account"; accountId: string }
| { type: "portfolio"; portfolioId: string }
| { type: "adHoc"; accountIds: string[] };
Backend services should resolve an AccountFilter once, validate the result,
and
then pass &[String] account IDs into repositories.
Keep portfolio identity distinct from the resolved account list. A saved
portfolio containing one account is still { type: "portfolio" }, not a plain
account ID. This prevents UI/query behavior from changing just because a
portfolio currently has one member.
Repositories should not parse:
MULTI:id,idPORTFOLIOThis keeps storage code simple, testable, and safe.
Owns portfolio CRUD, membership, and validation:
portfolio unless product explicitly decidescreated_at and updated_at; do not trust client-provided timestamps;Shared service helper:
enum AccountFilter {
All,
Account(String),
Portfolio(String),
AdHoc(Vec<String>),
}
Responsibilities:
All to active/non-archived accounts as appropriate for the caller;Account to one account;Portfolio through portfolio_accounts;AdHoc by validating account IDs;Income, Activity, Holdings, Allocations, and Performance should accept an
account
filter or resolved account IDs.
Avoid adding feature-specific portfolio/filter parsing in repositories.
For temporary compatibility during a staged refactor, frontend wrappers may
translate old route params into an AccountFilter, but core services should
not
grow new string encodings.
For portfolio performance, transfers must be classified relative to the
selected
scope:
member -> member: internal
member -> non-member: external withdrawal
non-member -> member: external contribution
This rule is different from total-portfolio performance when the selected
scope
is a subset of accounts.
This is a real change to TWR calculation. The current flow classifier uses a
metadata flag that describes the whole-portfolio boundary. For saved
portfolios,
the calculation must look up the paired transfer leg by
source_group_id, compare both leg account IDs against the resolved
membership
set, and classify the flow at runtime.
Implementation requirements:
Do not sum DailyAccountValuation.total_value, cash_balance, cost_basis,
or
net_contribution directly across accounts.
Those fields are account-currency values. For mixed-currency portfolios,
direct
summation gives incorrect values and incorrect returns.
Target approach:
daily_account_valuation with explicit base-currency columns:
cash_balance_baseinvestment_market_value_basetotal_value_basecost_basis_basenet_contribution_basefx_rate_to_base = 1.Do not re-derive portfolio valuations from large holdings snapshots at query
time unless the base-value columns are not available yet. Snapshot-derived
calculation is acceptable as a migration/rebuild path, not as the hot query
path.
Holdings and allocation views must also aggregate in base currency. Existing
per-account holdings already carry base-currency value; multi-account
portfolio
views should sum by asset/category using base values, not account-currency
values.
Today Wealthfolio calculation is account-first:
TOTAL scope is rebuilt from individual account snapshots;TOTAL;TOTAL is not a user portfolio record. It is a system calculation cache for
all-account views, quote-sync planning, net worth, and portfolio-level
performance.
PR #938 currently treats
a saved portfolio as a MULTI: account string:
TOTAL aggregation helper;daily_account_valuation;That shape is useful as a prototype, but it should not become the target
calculation model.
Keep persisted calculation artifacts for:
Total Portfolio / All Accounts system scope.Do not precompute and persist snapshots or daily valuations for every saved
user
portfolio by default.
Rationale:
Total Portfolio is different. It is a virtual system scope represented by
AccountFilter::All, but existing TOTAL snapshots and valuations can remain
as internal calculation caches because many core workflows use all-account
totals
and quote-sync state.
Target behavior by view:
accounts.group; do not
materializeThe history chart still needs a daily portfolio series. The target difference
is
where that series comes from:
TOTAL valuation history are persisted calculation artifacts;For AccountFilter::All, keep using the existing TOTAL historical
valuations.
That path is already a system-level cache and should remain fast.
For AccountFilter::Portfolio and AccountFilter::AdHoc, build the chart
series on read:
returns[] series used by the Performance chart.Do not write daily_account_valuation rows with account_id = portfolio_id.
That would make portfolio definitions behave like calculated accounts and
would
create invalidation, sync, and membership-history problems.
Membership semantics for the MVP:
If Wealthfolio later needs "portfolio as managed mandate" semantics, add
membership effective dates, for example valid_from and valid_to, and
compute
history against the membership set effective on each day. Do not add that
complexity to
PR #938 unless the
product explicitly needs time-versioned
portfolios.
For valuation charts, sum base market values. For performance charts, also
classify linked transfers relative to the selected member set so the TWR and
net
contribution series reflect the portfolio boundary.
Read-time portfolio history is feasible if it is built from
daily_account_valuation, not from holdings snapshots.
The existing table is compact and indexed by (account_id, valuation_date).
For a portfolio with M accounts and D daily points, the read path scans
about
M * D valuation rows. That is acceptable for normal local SQLite use because
it avoids:
The target repository method should load member account valuation rows in one
range query and aggregate in Rust with Decimal, for example:
fn get_portfolio_valuation_series(
account_ids: &[String],
start: Option<NaiveDate>,
end: Option<NaiveDate>,
) -> Result<Vec<DailyPortfolioValuation>>;
Do not aggregate decimal text in SQLite with SUM(CAST(... AS REAL)); that is
both mixed-currency unsafe and precision unsafe.
Current-code feasibility:
total_value_base, cash_balance_base, and investment_market_value_baseaccount_currency_value * fx_rate_to_base;cost_basis_base can be derived the same way under the current valuationnet_contribution_base cannot be derived safely fromnet_contribution * fx_rate_to_base, because contribution base values areTherefore the correct target is to persist explicit base-currency valuation
columns during normal account and TOTAL valuation calculation, especially
net_contribution_base.
Factor FX conversion at valuation-write time, not portfolio-read time:
calculate_valuation responsible for producing both account-currencyIf profiling later shows large portfolios are slow, add a local derived cache
keyed by portfolio ID, member-set hash, date range, base currency, and
valuation
version. Keep that cache rebuildable and unsynced; the source of truth remains
account valuations plus portfolio membership.
The synthetic portfolio valuation series should:
If this becomes too slow later, add a local derived cache such as
portfolio_valuation_cache. That cache should be invalidated/rebuilt from
account data and portfolio membership, and should not become the source of
truth
or a synced user model.
Dashboard account groups and saved portfolios should not be treated as the
same
UI layer.
accounts.group is account organization metadata. A saved portfolio is an
analytical shortcut that can overlap with many account groups.
Target behavior:
If product later wants portfolio summaries on Dashboard, model that as a UI or
dashboard-preference layer, not as columns on portfolios.
Do not convert accounts.group directly into portfolios.
accounts.group is account organization metadata. A portfolio is an
analytical
reporting boundary.
Target direction:
accounts.group column for now;If richer account-group metadata becomes necessary later, add a dedicated
account_groups table and an accounts.group_id column. Keep that model
separate from portfolios.
Both portfolio tables must participate in device sync before release.
Required work:
portfolios and portfolio_accounts to sync table lists, withportfolios ordered before portfolio_accounts, and portfolio_accountsaccounts and portfolios;SyncOutboxModel for both DB models;writer.exec_tx and tx.insert / tx.update / tx.delete;If this is omitted, portfolio configuration will be local-only and will not
match Wealthfolio's existing sync behavior for account metadata.
Foreign keys are important here: replay must apply account and portfolio
inserts
before membership inserts. Tests should cover this ordering.
The web adapter must map CRUD commands to actual Axum routes and serialize
payloads.
Expected route shape:
GET /portfolios
GET /portfolios/{id}
POST /portfolios
PUT /portfolios/{id}
DELETE /portfolios/{id}
POST /account-filters/resolve
Avoid Tauri-only assumptions in shared frontend hooks.
PR #938 should move to
the target model before merge. Do not merge the interim
JSON membership / MULTI: string model and plan a later corrective refactor.
Keep the PR focused by reducing the product surface, not by keeping the wrong
model. A good mergeable target is:
portfolios.account_ids with portfolio_accounts;PortfolioNewPortfolioPortfolioAccountAccountFilterMULTI: parsing from frontend, core, and repositories;portfolios and portfolio_accounts;If the performance work is too large, defer portfolio support on the
Performance
page rather than shipping known-wrong mixed-currency or transfer behavior.
Add explicit base-currency columns to daily_account_valuation:
cash_balance_base;investment_market_value_base;total_value_base;cost_basis_base;net_contribution_base.Populate them during normal account and TOTAL valuation calculation. Then
use
those fields to build portfolio valuation series on read.
Keep this separate from portfolio work:
accounts.group;account_groups table later if richer metadata is needed.The new portfolio CRUD commands in
PR #938 are not part of
the public addon SDK
surface today. The existing addon PortfolioAPI exposes holdings, income,
valuation, update, and recalculation APIs. If saved-portfolio CRUD APIs are
added to the addon SDK later, keep old names as aliases for one release or
call
out a hard rename in the changelog.
Frontend persisted state also needs compatibility handling. The Performance
page
stores TrackedItem values in local storage; if portfolio route shapes or
filter
payloads change, add a small migration or defensive cleanup so stale
selections
do not break page load.
portfolios.account_ids values migrate into portfolio_accounts;accounts.group remains on accounts and is not converted into portfolios.portfolio_accounts.All resolves expected accounts;Account resolves one account;Portfolio resolves portfolio members;AdHoc resolves validated IDs;returns[] series needed by
thedaily_account_valuation rows forAccountFilter::All uses the existing virtual TOTAL history cache;File:
crates/storage-sqlite/src/portfolio/valuation/repository.rs
The new SQL aggregation sums account-currency valuation fields directly and
then
marks the synthetic result as base currency with fx_rate_to_base = 1.
DailyAccountValuation stores account-currency values, so a USD+CAD portfolio
will produce numerically wrong returns.
The query also casts decimal strings to REAL before summing, which
introduces
floating-point precision risk for financial values.
Fix by adding explicit base-currency valuation columns and aggregating those
for
portfolio performance using decimal-safe handling. Do not sum account-currency
fields across accounts.
Files:
crates/core/src/portfolio/snapshot/snapshot_service.rs
crates/core/src/portfolio/valuation/valuation_service.rs
apps/frontend/src/adapters/shared/portfolio.ts
PR #938 routes portfolio
performance by resolving a saved portfolio into a
MULTI: account string and then treating it as an account. The current
performance flow classifier understands the whole-portfolio boundary, not an
arbitrary subset of accounts.
For saved portfolios, linked transfer legs must be classified against the
member
set. Member-to-member transfers are internal; member-to-non-member and
non-member-to-member transfers are external flows. Without this, TWR and net
contribution are wrong for any portfolio that contains only one side of a
transfer pair.
Target fix: build portfolio valuation/performance series from resolved member
IDs and linked transfer metadata, not from a MULTI: string masquerading as
an
account ID.
Files:
crates/core/src/portfolio/snapshot/snapshot_service.rs
crates/core/src/portfolio/valuation/valuation_service.rs
apps/tauri/src/listeners.rs
apps/server/src/api/shared.rs
The existing recalculation pipeline persists snapshots and valuations for each
account and for the virtual TOTAL scope. TOTAL is a useful system cache
because all-account views are used widely.
Saved user portfolios should not follow the same default persistence model.
Portfolios can overlap, membership can change, and every account edit would
otherwise fan out into many recalculations.
Target fix: keep account and TOTAL persisted calculation caches, but derive
user portfolio holdings and performance from account-level data on read. Add a
local derived cache later only if profiling proves it is needed.
File:
apps/frontend/src/adapters/web/core.ts
The web adapter registers portfolio commands but does not append IDs or
serialize request bodies for get/create/update/delete/find.
It also maps find_portfolio_by_accounts to /portfolios/find-by-accounts
while the Axum route is /portfolios/match.
Web mode will 404/405 or send empty bodies for the new settings page.
File:
crates/storage-sqlite/src/portfolio/portfolios/repository.rs
Portfolio writes use writer.exec, and the new PortfolioDB is not
registered
as a sync outbox model, app sync table, or sync entity mapping.
Existing accounts.group syncs because it is part of accounts; new
portfolio
rows will remain local to one device.
Files:
crates/storage-sqlite/migrations/2026-04-29-000001_portfolios/up.sql
crates/storage-sqlite/src/portfolio/portfolios/model.rs
crates/core/src/portfolio/portfolios/portfolio_service.rs
The portfolios.account_ids column stores a JSON array. Account deletes
cannot
cascade, duplicate account IDs are not prevented by the database, and invalid
or
stale account IDs can remain in saved portfolios.
The service only checks account_ids.len() >= 2; it does not validate
distinct
IDs or account existence. The DB conversion also uses unwrap_or_default(),
so
corrupt JSON silently becomes an empty portfolio.
Target fix: normalize membership into portfolio_accounts with foreign keys
and
service-level validation. Interim fix: validate distinct existing account IDs
on
create/update and surface JSON corruption as an error.
File:
apps/frontend/src/adapters/shared/portfolios.ts
buildAccountSelection returns the raw account ID when a portfolio contains
one
account. That makes a saved single-account portfolio indistinguishable from
the
account itself and prevents future product support for "named views" over one
account.
Target fix: preserve { type: "portfolio", portfolioId } through the UI and
backend. Do not encode saved portfolios as account-selection strings.
File:
apps/frontend/src/pages/dashboard/accounts-summary.tsx
The grouped Dashboard builds saved portfolio rows first, then removes every
portfolio member from normal account groups. Because portfolios can overlap
and
are analytical views, this can hide accounts from their groups and make
portfolio rows look like part of the account grouping hierarchy.
Target fix: do not render saved portfolio views inside account groups. If
Dashboard portfolio summaries are added later, put that placement in UI
preferences, not the portfolio model. Do not remove accounts from their normal
account groups just because they appear in a portfolio.
File:
apps/frontend/src/pages/settings/portfolios/portfolios-page.tsx
name and selectedIds are initialized from props only once. The key is
placed
on Dialog, not on PortfolioFormDialog, so closing and reopening for
another
portfolio can show or submit stale values.
Reset state in an effect when open or portfolio changes, or key the
component at the parent call site.
This PR is a good feature direction, but I do not think we should merge the
current model as-is.
The target should keep account grouping separate from portfolio reporting
scopes:
accounts.group column for account organization;portfolios represents a saved account reporting scope;portfolio_accounts;portfolios table;AccountFilter, not MULTI: strings;This keeps account groups and portfolio reporting scopes distinct without
forcing exclusive account groups into the same model as overlapping portfolios.
The current PR should be refactored before merge because it has blocking
issues
around mixed-currency performance, web-mode CRUD wiring, and device sync.