​

Multi-portfolio PR plan

​

Descoped on 2026-10-02

1. Silvia never receives the selector state. She still knows everything else about the owner’s portfolios (IDs, names, types, notes, and which roots each holds) and scopes an answer to any portfolio or combination the user names. Only the selector was trimmed: AI-1 no longer puts selector state in context, and AI-3 no longer labels answers as All when the selector is narrower; instead an answer names its portfolios whenever it used a scope narrower than All. Not planned for a later phase either (§4).
2. The Vault is not portfolio-aware in phase 1. Folders and documents stay owner-level and move to phase 2 (§2, §6).
3. The selector sits on pages, not in the sidebar. It appears at the top of the dashboard, transactions, and the portfolio page (/assets-liabilities), which share one selection. Every other page shows All (§1).

​

How the work is organized

(staging) <- mp/db-stop-portfolio-snapshots <- mp/db-legacy-cleanup <- ... <- mp/db-restrict-and-lockdown

gh stack init --base staging mp/db-stop-portfolio-snapshots
# remove the cron block, commit
gh stack add mp/db-legacy-cleanup
# ...
gh stack submit --auto          # draft PRs, one per branch

​

Workstream dependencies

​

Can start before the schema is settled

​

Rules that apply to every PR

• Migrations. Read docs/DATABASE-VIEW-RLS-SAFETY.md and .agents/skills/database-migration-safety/SKILL.md before writing one. Recreated views declare security_invoker = on. Follow the existing YYYYMMDD_NN_description.sql naming, and split NOT VALID / VALIDATE into separate files as 20260917_03/_04 do.
• Paging. Any read of a per-root x per-day table goes through fetchAllPages (docs/SUPABASE-PAGINATION.md).
• Valuation. Any PR that touches a number on screen checks docs/VALUATION-SURFACES.md for sibling surfaces.
• Logging. Log portfolio IDs, never names or notes. Names are often a person’s name.
• No outbound messages. Deletion, consolidation, and downgrade handling send no email, SMS, or push. That includes “your portfolios were consolidated” notices. Any such message needs Jacob’s explicit sign-off first (AGENTS.md).
• UI. In-app surfaces use components/modulus/ and semantic tokens. Do not edit components/ui/**, app/themes.css, or app/globals.css. Every dynamic() import has a loading fallback.
• Scripts. One-off scripts (backfill, reconciliation, fixtures) live under scripts/multi-portfolio/ and run directly with npx tsx. No package.json entries unless CI invokes them.
• Tests. Each PR adds Vitest coverage for the utilities and hooks it introduces, and lists which “Required verification” items (§5) it covers.
• Silvia skills. A PR that changes user-visible UI notes whether it needs a companion PR in CFO-Silvia/skills (docs/SILVIA-SKILLS-SYNC.md). The bulk of that lands in AI-7.

​

Financial root inventory

​

Production findings (2026-09-24)

• Orphans: decided 2026-09-25: delete them. DB-1 deletes the 66 null-owner roots, the 361 roots with no owner row, and the 248 accounts with no item, together with their child rows.
• Cross-owner children: decide which side is right for the 176 accounts and 32 SnapTrade accounts before any trigger enforces same-owner.
• crypto_holdings parent key: this is a hot table of 3.8M rows. Either resolve on (clerk_id, wallet_address, chain), with duplicate wallets merged first and a unique index added (NULLS NOT DISTINCT for chain), or add an explicit wallet_uuid / Coinbase parent column that the sync writers fill. The first changes no writers; the second makes the trigger a primary-key lookup. The parent is now only needed for a token never seen on the wallet; every other insert looks up its token key, which needs its own index. Either way, test the trigger’s cost on wallet sync inserts, which rewrite every token on every run.
• Coinbase parent key: coinbase_accounts.wallet_id is not unique, and 8 owners have more than one row. Check whether their duplicates share a wallet_id, deactivate the stale ones, and add a partial unique index on (clerk_id, wallet_id) WHERE is_active before DB-7, so a Coinbase holding resolves to exactly one connection. A holding with no active parent lands in Main (§2).
• Wallet token address: the token key uses the contract or mint address, not the symbol (§2). The wallet sync (process-wallet-holdings.ts) already has token_address in memory and must start writing it to a new crypto_holdings.token_address column, with native for a chain’s native coin. That is a one-field change to its insert payload, not a rewrite.
• Wallet sync rewrite (optional): changing process-wallet-holdings.ts to update one row per token in place would remove the need for the token key and stop the row churn. Phase 1 doesn’t require it.
• FKs on clerk_id: check which of them are enforced in production (query below) before relying on them.

SELECT conrelid::regclass AS tbl, conname, convalidated
FROM pg_constraint
WHERE contype = 'f'
  AND confrelid = 'public.all_users'::regclass
  AND conrelid::regclass::text IN ('items', 'accounts', 'snaptrade_institutions', 'snaptrade_accounts',
    'coinbase_accounts', 'wallets', 'crypto_holdings', 'manual_accounts', 'properties', 'vehicles',
    'commercial_properties', 'physical_assets', 'private_investments', 'other_assets',
    'precious_metals_holdings', 'manual_public_holdings', 'vault_folders', 'portfolios')
ORDER BY 1;

​

DB: schema, Main backfill, protections

• Done: #4285.
• Remove the PORTFOLIO DAILY SNAPSHOTS block in app/api/cron/process-daily-snapshot/route.ts. Nothing in the app reads portfolio_daily_snapshots, and the design retires it (§3).
• Must merge before DB-3. Once every owner has a Main, that block would run 11 extra queries per user per day and start writing portfolio_daily_snapshots for everyone.
• App code only, no migration.

• Done: #4288.
• Delete the orphans from the production findings with their child rows: roots with a null clerk_id (§2, “Rows whose clerk_id is null”), roots whose owner has no all_users row, and accounts with no item. Children are deleted explicitly, since production does not enforce every baseline FK.
• Then assert that no connection or standalone root has a null or unknown owner and no account lacks an item. Account rows under a live connection are not deleted here; see the null clerk_id findings for accounts and snaptrade_accounts. vault_folders is asserted but never deleted, because its documents have storage objects.
• Delete the three legacy portfolios rows. Asset references are set to null and asset_ownership, portfolio_links, portfolio_members, and portfolio_daily_snapshots are emptied explicitly, rather than through SET NULL/CASCADE.
• The legacy portfolios and everything referencing them belong to one internal test user, so that part needs no preserving: no export, no mapping to new portfolios, no check of what lands in their Main. The orphans are not all theirs: accounts with no item can have a live owner, and deleting them is the team’s orphan decision above.
• Done when: portfolios is empty in staging and production and no root references a missing portfolio.

• Done: #4433.
• Reverted: #4360 (mp/db-portfolio-metadata) merged with a portfolios.percentage integer column and was reverted in #4431. The redo moves shares to their own table and allows four decimal places.
• The redo is 20260928_03_portfolio_metadata_and_owners.sql. A new name, so a database that applied the reverted file still runs it; the same 20260928_03 slot, so it still sorts before DB-3 to DB-6 (20260928_04 to _06) and the LINK migrations’ Depends on DB-2 (20260928_03 ...) headers stay right. It drops portfolios.percentage IF EXISTS in case the reverted file was applied anywhere.
• DB-3 and the rest of the DB stack were built on #4360’s branch, so they rebase onto this one.
• Add notes and deleted_at (null means live). Repurpose portfolio_type as the portfolio’s type: NOT NULL (DB-1 leaves the table empty), replace its baseline column comment, and drop idx_portfolios_type. Cleanup progress lives on portfolio_operations, not on the portfolio.
• Add portfolio_owners (portfolio_id, owner_clerk_id, percentage), for joint-asset allocation and later sharing, both out of scope for phase 1 (§1 “Joint assets”, §2). A table rather than a column, because a shared portfolio has several owners.
  • Primary key (portfolio_id, owner_clerk_id) and an index on owner_clerk_id. portfolio_id references portfolios ON DELETE RESTRICT (already the DB-11 shape); owner_clerk_id references all_users ON DELETE CASCADE, like portfolios.clerk_id.
  • percentage numeric(7,4) NOT NULL DEFAULT 100, CHECK (percentage > 0 AND percentage <= 100): four decimal places, so a third is 33.3333. Same range as asset_ownership.ownership_percentage, finer than its two decimals.
  • An AFTER INSERT trigger on portfolios writes the owner’s row at 100, so DB-3’s Main backfill and API-4’s create fill it without calling anything. Its function is SECURITY DEFINER (search_path = '', execute revoked from clients), so a client portfolio insert still gets its row until DB-11 removes that path. Nothing else writes the table in phase 1, and nothing reads it.
  • RLS on, SELECT for the row’s own owner_clerk_id, no client write policies. It grants no access; authorization stays on portfolios.clerk_id.
  • The phase 2 rule that one portfolio’s shares sum to at most 100 is cross-row and not enforced here. Phase 1’s single row at 100 satisfies it.
• Checks: portfolio_type in the §1 set (a check constraint, not an enum type); portfolio_type = 'main' iff is_default; Main cannot have deleted_at; name trimmed, non-empty, at most 100 characters; notes at most 1,000; non-Main names are not “main” or “all portfolios” (case-insensitive, trimmed).
• Partial unique indexes: (clerk_id) WHERE is_default, and (clerk_id, lower(btrim(name))) WHERE deleted_at IS NULL.
• Done when: inserting a portfolio yields exactly one portfolio_owners row at 100; 33.3333 is stored as is, 33.33333 is stored as 33.3333, and 0 and 100.0001 are rejected; a client insert, update, or delete on portfolio_owners fails.

• Open: #4361.
• ensure_main_portfolio(clerk_id) inserts Main with ON CONFLICT DO NOTHING and returns its ID.
• Create Main for every existing owner in all_users, including soft-deleted accounts, whose data is still present.
• Done when: every owner has exactly one Main, each with its DB-2 portfolio_owners row; a concurrent-call test returns the same ID.

• Open: #4362.
• app/api/webhooks/clerk/route.ts calls ensure_main_portfolio on user.created.
• Done when: a test user.created event yields a Main row, and a replay is a no-op.

• Open: #4363.
• portfolio_operations, portfolio_audit_events, snaptrade_link_intents, shaped as in the §2 ERD.
• A deletion’s per-connection state lives in portfolio_operations.pending_steps, which stays free-form jsonb: the owner’s choice to disconnect a connection that would otherwise stay (OPS-7), and a disconnect step the re-add restore marks skipped (OPS-10). Neither needs a column.
• RLS: owner can SELECT; writes are service-role only. Audit rows hold IDs only.
• portfolio_audit_events.id is a bigint identity with an index on (clerk_id, id); the owner’s newest ID is the assignment revision (§2).
• record_portfolio_audit_event takes a per-owner pg_advisory_xact_lock and then inserts. Every audit write goes through it, so an owner’s events commit in ID order.
• Partial unique index allowing one pending link intent per owner, and a unique snaptrade_connection_id on snaptrade_link_intents so each intent binds to at most one SnapTrade authorization.

• Open: #4387.
• Drop portfolio_id from items, coinbase_accounts, and wallets; DB-1 left it empty and nothing reads it since DB-0. Connections get no assignment column.
• Add the goal scope to goals (for example scope_kind plus scope_portfolio_ids uuid[]), nullable for now.

• BEFORE INSERT on manual (including manual crypto_holdings) and standalone roots: a null portfolio_id becomes ensure_main_portfolio(NEW.clerk_id). No trigger on vault_folders or vault_documents (out of scope for phase 1, above).
• BEFORE INSERT on accounts, snaptrade_accounts, and Coinbase crypto_holdings: a null portfolio_id follows the sibling rule; a connection whose accounts are all in deleted portfolios raises the LINK-9 error code. A Plaid account whose persistent_account_id matches another account on the same Item takes that account’s portfolio instead. A Coinbase holding’s connection is the active coinbase_accounts row with its owner and wallet_id, through the partial unique index above; none active means Main.
• BEFORE INSERT on wallet crypto_holdings: take the portfolio already held by the token key; only a new token with no link destination follows the sibling rule over the wallet, using the parent key the team picks. A token key whose portfolio is deleted returns null (row skipped), so the wallet’s batch insert still writes its other tokens. Re-adding the wallet restores such a token first (OPS-10), so the skip only applies while the wallet stays connected. Add the token_address column and the token-key index, and have process-wallet-holdings.ts write token_address.
• Every trigger here that reads a connection reads its row FOR SHARE, so it waits for a move holding that row FOR UPDATE (OPS-1) and then reads the committed assignment (§3).
• Postgres fires BEFORE INSERT triggers on an upsert’s conflict path too, and the sync workers upsert existing accounts and snaptrade_accounts rows constantly. Return early when the row’s key already exists, so a balance sync doesn’t pay for the sibling rule and a FOR SHARE connection lock per account (LINK-9).
• Same triggers reject any other destination whose portfolio has deleted_at set, with a distinct error code workers can recognize (used by LINK-9), and a destination owned by someone else.
  • That code is MP003 (PORTFOLIO_DELETED_SQLSTATE in lib/portfolios/errors.ts, reserved by LINK-5), for both the sibling-rule rejection and an explicit deleted destination. LINK-9’s workers acknowledge it.
• Requires the cross-owner rows and the crypto no-parent rows from the production findings to be resolved first. DB-1 removes the accounts with no item.
• Children never get the Main default.
• A manual_public_holdings row inserted with a manual_account_uuid and no portfolio_id takes its account’s portfolio, not Main. LINK-8’s routes already write it; the trigger is the backstop.
• Done when: a wallet sync after a token move keeps the token in its destination, and a sync after that token’s portfolio is deleted writes the wallet’s other tokens.

• No copy triggers. Plaid and SnapTrade accounts and crypto holdings are roots, not children, and the vault_documents copy trigger and folder cascade moved to phase 2 with the rest of the Vault (§6). The branch name predates that and stays.
• BEFORE UPDATE on every root rejects a portfolio_id change unless the transaction-local flag set by the move, consolidation, and deletion functions is on, so a sync upsert cannot move an account.
  • The flag is silvia.portfolio_assignment_change = 'on', set with set_config(..., true). LINK-6’s bind_snaptrade_link_intent already sets it before it moves accounts.
• Exception: on Coinbase crypto_holdings, a clerk_id change resets portfolio_id to the new owner’s Main in the same statement and records an audit event for each owner, taking their locks in clerk_id order, which advances both revisions (§2 “Coinbase owner change resets assignment”). Test a takeover followed by a holdings upsert.
• On accounts and snaptrade_accounts, reject clearing is_hidden while the account’s portfolio is deleted, except inside the restore move (OPS-2).
• Done when: a Plaid, SnapTrade, or Coinbase upsert carrying a different portfolio_id is rejected; a Coinbase takeover followed by a holdings upsert lands the holdings in the new owner’s Main and advances both owners’ revisions; and clearing is_hidden on an account in a deleted portfolio is rejected outside the restore move.

• Preflight: fail if any accounts row has a null clerk_id (production findings). The balance-job skip must already be merged, so no new bare rows appear after the count.
• scripts/multi-portfolio/backfill-main.ts updates null portfolio_id roots to Main in batches, including accounts, snaptrade_accounts, and every crypto_holdings row (inactive wallet rows too, so history resolves). vault_folders and vault_documents are left alone.
• Sets existing aggregate goals to the All scope.
• Idempotent and resumable. Run in staging, then production, before DB-10 merges.

• Two migrations: ADD CONSTRAINT ... CHECK (portfolio_id IS NOT NULL) NOT VALID, then VALIDATE and SET NOT NULL. Goal scope columns too. vault_folders and vault_documents are excluded and stay nullable.
• Done when: “every financial root has a non-null portfolio_id” (§5) holds.

• Change every portfolio FK to ON DELETE RESTRICT, the Vault’s included, since its columns stay.
• Drop client INSERT and DELETE policies on portfolios. A trigger rejects client changes to clerk_id, is_default, deleted_at, and setting or clearing main. portfolio_owners needs nothing here: DB-2 creates it with a RESTRICT FK and no client write policies.
• Remove the portfolio_members path from the portfolios SELECT policy and the client member-insert policy.
• Done when: a direct client insert and a direct client delete both fail (§5).

​

API: scope contract, resolver, management endpoints

• Done: #4281.
• lib/is-production-build.ts replaces ten copies of the same check (NEXT_PUBLIC_VERCEL_ENV, falling back to NODE_ENV), so the rollout flag in API-1 does not add an eleventh. No behavior change.
• Leaves alone the hostname-fallback checks (useExperiment, detail-page and cost-basis flags) and the strict production-only gates (middleware, marketing pixels, DataFast, token meter).

• Done: #4282.
• Rollout flag, checked on both server and client, following the existing per-user flag pattern. Independent of the plan gate (§5 item 6).
• types/portfolio.ts: Portfolio, PortfolioScope, type unions, derived person/entity group. Type display labels live in lib/portfolios/portfolio-types.ts.
• PORTFOLIO_LIMITS: Record<Tier, number> (free 1, pro 2, premium 10). Key by Tier, never display name.

• Done: #4283 (scope.ts) and #4359 (access.ts). The resolver reads deleted_at, so its first caller (SCOPE-2) must not ship before DB-2.
• lib/portfolios/scope.ts: parse, canonicalize (de-dupe, sort), cache-key string, scope label (“All portfolios”, name, “N portfolios”).
• lib/portfolios/access.ts: resolves the authenticated owner plus requested scope to authorized IDs. Rejects empty sets and the whole request on any malformed, foreign, or deleted ID. Missing scope means All (legacy compatibility).
• Done when: tests cover order-independence, cross-user IDs, deleted IDs, and the empty set.

• Done: #4358.
• Returns live portfolios, plus deleted ones whose operation is unfinished, with types, notes, operation status, and assignment_revision (the owner’s newest audit event ID, 0 if none).
• Includes the revision helper (lib/portfolios/revision.ts) from API-6. The revision is read before the list, so the list is never older than the revision it is returned with.
• Written against the DB-2 and DB-5 shapes before they land; merges after both.

• Done: #4366. Its migration calls ensure_main_portfolio and reads deleted_at, so it merges after DB-2 and DB-3.
• Server RPC counts live portfolios and inserts under a per-owner advisory lock. Tier from getActiveSubscription (lib/token-limit.ts).
• Typed errors portfolio_limit_reached (tier and limit) and portfolio_name_taken.
• Done when: concurrent creates cannot exceed the limit; trialing and past_due get the paid limit (§5).

• Done: #4367, stacked on API-4.
• Name and notes on any portfolio, type on additional portfolios only. Main keeps main; nothing else can take it.
• Does not touch ownership shares; phase 1 has no way to change a portfolio_owners row from its 100 default.

• Done, with no PR of its own: the revision helper shipped in API-3 (#4358) and the audit write path in DB-5 (#4363).
• The server helper that reads the owner’s assignment revision landed in API-3; API-7 reuses it. Audit events are written inside the OPS RPCs through record_portfolio_audit_event (DB-5), never from application code, so an event always commits with the change it records.

• Done: #4370, stacked on API-5.
• Extend lib/cache-invalidation.ts and lib/financial-data-cache.ts so server caches key on owner, canonical scope, and revision, and a new revision invalidates every scope for that owner.
• Its one scoped cache, getUnrealizedGainsTaxDetails, is called only by the voice tool, which never receives the selector (§4). It reads All, or the scope the user named once AI-6 passes one; the revision still invalidates it after a move.

​

LINK: destination-bound linking

• Done: #4409, bottom of the LINK stack. Merges after DB-2.
• Validate owner and live portfolio before itemPublicTokenExchange; write the destination as portfolio_id on every account the link inserts.
• /api/relink inserts accounts added in update mode under the sibling rule and returns their IDs, so the app can show the assignment step for them (MGMT-5).

• Done: #4410.
• components/plaid-link-button.tsx / contexts/plaid-link-context.tsx store the destination next to plaid_link_token and send it to connect-bank.
• Done when: “OAuth return after selector changes still uses the original destination” (§5).

• Done: #4411.
• Keep the account-level guard owner-wide. When the match is in another portfolio, the error names it and says to leave the joint account unticked.

• Done: #4412. The pre-Link pickers still block on any match; MGMT-5 should route them through decideDuplicateGuard once they have a destination.
• In PlaidLinkButton, an institution present only in another portfolio shows a confirmation to continue with a different login. Same-portfolio stays blocked.

• Done: #4413. Merges after DB-2 and DB-5.
• POST /api/snaptrade accepts a destination, validates it, cancels any pending intent, stores a new one, and puts its ID in customRedirect.

• Done: #4414. Merges after DB-2, DB-3, and DB-5.
  • No restore needed: a deletion’s disconnect deletes the authorization, and SnapTrade gives a deleted connection new account IDs when it is added again, so a relink inserts new rows. A login whose accounts were only detached keeps its connection, and relinking it returns the same IDs, which stay hidden on the tombstone (#4524).
• app/api/snaptrade/process-connection takes the intent ID, binds the intent to the redirect’s authorizationId, assigns the accounts this link created to its destination (moving them there if a webhook got in first), and marks the intent used. If a webhook bound the intent to a different authorization, completion’s binding wins and that authorization’s accounts move to Main.
• Destination deleted since the intent was created: reject and call removeBrokerageAuthorization on the new connection.
• No restore for SnapTrade. A deletion’s disconnect deletes the authorization, and SnapTrade documents that a deleted connection added again gets new account IDs, so relinking inserts new rows in the intent’s destination. Relinking a login whose accounts were only detached returns the existing connection with the same IDs; those rows are updated in place and stay hidden on the tombstone, restorable through OPS-2 (§3 “SnapTrade needs no restore”).

• Done: #4415. Merges after DB-2 and DB-5.
• handleNewAccountAvailable in app/api/snaptrade/webhook/route.ts and the missing-root recovery in process-snaptrade-holdings-sync use the intent bound to the event’s brokerageAuthorizationId. With none bound, they bind the pending, unbound intent only if Silvia first saw the authorization after the intent was created (its snaptrade_institutions row is being created now, or its created_at is later than the intent’s; both database-clock timestamps); otherwise the sibling rule applies (Main for an empty or new connection). A connection with no local accounts is not enough, since process-connection can leave one behind.
• Done when: webhook-before-completion and closed-tab cases both land in the destination, and an older empty authorization’s webhook during a newer link lands in Main (§5).

• Done: #4416, as one PR. Wallets: add-wallet validates the destination before the wallet exists, and the app sends it again to fetch-wallet-holdings, which writes it only on a chain’s first rows. OPS-10 takes the destination from the same add-wallet request.
• Manual accounts, standalone assets, and wallets accept a concrete destination and write it on the first holdings: wallets through add-wallet and the fetch-wallet-holdings call the app makes right after it. Read scopes (All, multi) are rejected as destinations.
• Coinbase needs nothing here: it links through SnapTrade, so LINK-5 to LINK-7 carry its destination. No client starts the direct Coinbase sign-in any more. Its server-side callback (app/api/auth/[...nextauth]/route.ts) still exists and is left unchanged: it takes no destination, so a legacy sign-in’s new holdings follow the DB-7 sibling rule.
• The wallet destination also feeds the OPS-10 restore when add-wallet reactivates a wallet. Until LINK-8 merges, OPS-10 restores into Main.
• Split by asset family if the diff gets large.

• Done: #4417, top of the LINK stack until LINK-10, which waits on OPS-7.
• Plaid and SnapTrade workers treat the DB-7 deleted-portfolio error as “connection being removed” and acknowledge instead of retrying.
• Workers leave portfolio_id out of every accounts and snaptrade_accounts upsert payload, so the DB-8 update guard never fires on a normal sync.

• Why: a deletion that detaches accounts leaves them hidden, not gone. The Item keeps returning them, so their balances and transactions keep arriving and being stored with no end date (§3 “A detached account keeps syncing”).
• create-link-token-relink already enables update.account_selection_enabled, so the client opens update mode for the Item with the detached accounts named. After update mode, /api/relink already calls accountsGet. It now marks is_active = false on each account in a deleted portfolio that the Item no longer returns. Accounts in live portfolios are left as they are today, so this changes nothing outside deletion.
• An inactive account drops out of “From deleted portfolios” and stops syncing. The connect-bank duplicate guard already filters on is_active, so it no longer blocks the other person linking it from their own login.
• Before it ships, confirm in the sandbox and with the live Chase logins (§5) that unticking an account, including on an OAuth bank’s own consent page, stops Plaid returning it. If it does not at an institution, that institution gets only the disconnect choice (OPS-7).
• Depends on OPS-7, so it goes at the top of the LINK stack or in its own stack once OPS-7 merges.

​

SCOPE: scoped financial reads

• First count duplicate (root ID, date_synced) pairs over each table’s full history, and record the counts in the PR. The production finding covers only the last 30 days, but a unique index covers every row, so a single older duplicate makes the build fail.
• Retention rule: per root and day, keep the newest row (latest created_at, then highest id). That is usually the run the summary last recorded, but nothing guarantees it. daily_snapshots_summary is upserted from the run’s running totals in memory, not from the per-root rows, and the crypto inserts drop their errors (process-daily-snapshot/route.ts). So when a retry’s crypto insert failed, the summary holds the retry’s totals while the newest crypto row is from the earlier run.
• So check before deleting. For every owner-day with duplicates, compare the kept rows’ sum with the summary column each table feeds. Where they agree, delete the other rows. Where they don’t, still keep the newest, and record the owner-day, the series, and the gap. That list goes to SCOPE-7 as known differences, so the reconciliation is not chasing them owner by owner and no row is picked by guessing.
• Deleted rows move to a service-role-only backup table, not straight to deletion, and stay there until SCOPE-7 passes, so any retention choice can be reversed. Delete in batches on snapshots_by_crypto_holding, the largest table.
• Add a unique index on (root ID, date_synced) to each of the five tables, built CONCURRENTLY. A concurrent build that fails leaves an INVALID index behind: drop it, run the dedup again, and rebuild.
• Switch the cron’s inserts on these tables to upserts on that key. Order matters, because process-daily-snapshot is the only writer and uses plain .insert(). An upsert needs the index to exist first. Once the index exists, a retried run’s plain insert fails on the conflict and loses its whole batch, including rows the first attempt never wrote. The crypto inserts don’t check their error, so that loss would be silent. So build the indexes after a day’s run has finished, ship the upsert before the next run, and have the crypto inserts check their errors in the same change.
• Also fixes an existing overcount: use-accounts-daily-series.ts already sums snapshots_by_manual_account.
• Must merge before SCOPE-7.

• Recreate financial views and RPCs with portfolio_id (including account_effective_balances), security_invoker = on.

• Shared helper to read a scope from API requests and run it through the resolver.
• lib/cache-keys.ts: React Query keys include canonical scope and revision.
• Wire format for SCOPE-3 onward (lib/portfolios/scope-params.ts): GET sends ?portfolioIds=<id>,<id>, POST sends { "scope": PortfolioScope }, and All sends nothing, so an All request is byte-identical to a legacy one. Routes call resolvePortfolioScopeFromSearchParams or resolvePortfolioScopeFromBody (lib/portfolios/request-scope.ts) with the session’s Clerk ID and answer a failure with portfolioScopeErrorResponse (400 malformed, empty, or too large; 404 not found; 409 deleted). Hooks take the scope as a parameter, never from the context, pass it through usePortfolioQueryScope, and key with portfolioScopedQueryKey (lib/query-keys.ts). All keeps today’s key; a portfolio set appends { portfolioScope, assignmentRevision } at the end, so existing prefix invalidation still reaches it. A pending scope must use skipToken.

• Each keeps FX, cash-sweep dedup, ownership percentages, liability signs, and crypto valuation unchanged.
• Cash-sweep dedup runs over the Item’s full account list before the scope filter (§4). Callers of detectDuplicateCashSweepAccountIds() fetch sibling accounts outside the scope for detection only.
• Connection cards appear in any scope holding one of their accounts, list only in-scope accounts, name the rest, and show connection health (§3 “Connections that span portfolios”).

• transactions, snaptrade_transactions, and manual_transactions (through manual_account_uuid), scoped before paging.

• A security-invoker SQL function sums per-root snapshot history for roots currently in scope; value logs carried forward for commercial properties, physical assets, and private investments. Unsupported ranges report unavailable. It takes a date range and returns one row per day carrying every series the charts read. lib/portfolios/history.ts calls it. The sum cannot run in the client: a p99 owner has about 123 per-root rows per day, so 1,100 days would be about 135 fetchAllPages pages per series.
• Sums skip crypto rows whose market_value is 0. Which days exist, and any unavailable or coverage check, come from every row. The filter changed no daily total across 180,636 owner-days, but 323 of them held only zero-value crypto rows and would drop out of the chart without the second rule.
• Measured on production (2026-09-30) for the heaviest crypto owner, 357k crypto rows over 343 days. The join to each row’s crypto_holdings row for its portfolio took 5.3 s cold, because the wallet sync gives each token a new row every run. Skipping zero-value rows, 88% of them, brought crypto to 0.15 s warm. SnapTrade’s heaviest history read took 34 ms. No per-portfolio cache is needed.
• Target for a 1,100-day read: p95 under 300 ms warm; the heaviest owner under 1 s warm and 2 s cold. The first read of ALL for the few heaviest crypto owners may miss the cold figure as their history grows. If it does, add a partial covering index on snapshots_by_crypto_holding WHERE market_value <> 0.
• Plaid and SnapTrade history is per account (snapshots_by_account, snaptrade_account_daily_snapshots). For 2025-10-28 through 2026-06-26, where snapshots_by_account has no rows, use snapshots_by_item only when the scope holds every live account on the Item; otherwise report the Item’s range unavailable (§3).
• Service role only (get_portfolio_history(p_clerk_id, ...)): snaptrade_account_daily_snapshots has RLS with no policies and the value logs have insert-only policies, so a function run as authenticated would silently read none of them. lib/portfolios/history.ts passes the owner and scope from resolvePortfolioAccess, as other service-role reads do.
• Businesses count under their own portfolio_id (decided 2026-10-08); one with a null portfolio_id counts under All only.

• scripts/multi-portfolio/reconcile-history.ts compares the All computation with daily_snapshots_summary for every owner. Unexplained differences block SCOPE-8.
• The cron builds the summary from running totals, not from the per-root rows, so some differences are known before the first run. SCOPE-6 must reproduce these rules, not explain them owner by owner:
  • snapshots_by_account and snaptrade_account_daily_snapshots include hidden accounts, which the summary leaves out. Whether an account was hidden on a past day is not stored, so SCOPE-6 leaves out every account hidden today, on every day (decided 2026-10-08). An account unhidden since a day the summary skipped it is a known difference.
  • SnapTrade total_value is in the account’s own currency.
  • Private investments and physical assets have no snapshot table.
  • Commercial property snapshots can be skipped on days the summary still counts them.
  • Owner-days where SCOPE-0’s kept rows disagree with the summary, from the list SCOPE-0 records.

• Charts read SCOPE-6. (Writes to portfolio_daily_snapshots already stopped in DB-0.)
• Each chart requests its selected range rather than a fixed 1,100 days, and a page reads all its series from one call. Today /assets-liabilities issues three separate 1,100-day reads.

• Aggregate goals compute on their stored scope; account goals resolve through the account.
• /goals is not a filtered page, and it is the only place goals are created today (components/goals/create-goal-dialog.tsx). So a new aggregate goal defaults to All, and the create dialog gains a scope picker (All, or any set of live portfolios), behind the rollout flag. A goal created from a filtered page, if one ever offers it, defaults to that page’s selection (§4).
• /goals lists every goal with its scope label.

• Removed (2026-10-02). The Vault is phase 2 (§6).

• Persist on reports, analyses, and saved widgets (app/api/report, app/api/widgets) the explicit scope Silvia used (AI-3), never the selector. Refresh prunes deleted portfolios and says so in the label, and reports a vanished scope instead of falling back to All (§4).
• The transactions CSV export exports what the page has loaded, so SCOPE-5 and SEL-2 cover it.

​

OPS: move, deletion, consolidation

• RPC that takes a set of roots and one destination, validates ownership and live portfolios, locks each affected connection row FOR UPDATE and re-reads its accounts under that lock, updates each root (a wallet token updates every row with its token key, plus legacy address-less rows when its symbol is unique on the wallet) under the DB-8 move flag, and as its last step writes the audit event through record_portfolio_audit_event, which advances the revision. Vault folders and documents are not roots in phase 1 and never move.

• Preview lists the selected accounts, and for each connected account, its sibling accounts with their current portfolios as optional additions. Flags a cash-sweep pair being separated. Move invalidates caches (API-7).
• Restore endpoint: moves a hidden account from a deleted portfolio to a live one and clears is_hidden in one transaction. One of the two moves with a deleted source, with the OPS-10 wallet restore (§3 “Portfolio deletion”).
• Done when: “Moving one account of a connection leaves its sibling accounts, the connection, and provider credentials unchanged, and survives concurrent sync” (§5).

• Open: OPS-3 #4534, OPS-4 #4535, OPS-5 #4536.
• Pure refactors: existing delete routes call the extracted services with no behavior change. SnapTrade removes connections one at a time and never deletes the SnapTrade user. No outbound messages.
• No Vault service: portfolio deletion never touches the Vault, which is not portfolio-aware until phase 2 (§6). The exact-path Storage fix that started in OPS-5 is its own PR, #4580.
• Left as they were, for the PRs that change them: wallet holdings are still deactivated across the whole address (OPS-10 makes it chain-aware), and the Plaid-to-SnapTrade migration keeps its own copy of Item removal.

• Step machine over portfolio_operations.pending_steps: idempotent steps, retry of unfinished ones, repeated requests return the same operation. Cron route to resume pending work.
• Done: #4545, on the OPS-3 to OPS-5 stack. Needs DB-5 applied first. The state changes are SQL functions (20261001_02), each one update on the operation row, so a concurrent write such as OPS-10’s skip is never undone. Failed steps back off from 5 minutes to 6 hours, a step that runs past 25 seconds is recorded as failed, and the 10-minute lease must outlive the longest function that runs operations. POST /api/portfolios/operations/:operationId/retry runs an unfinished operation now.

• Preview every root and goals whose whole scope goes.
• Vault folders and documents are untouched by the preview and by the deletion. The preview says so, so the owner knows deleting a portfolio never deletes a document. The Vault folder S3-purge bug (CFO-6923) predates this work and is not OPS-7’s to fix. For each connection, say whether it is disconnected or stays connected for accounts in other portfolios, and name those accounts.
• Delete sets deleted_at (which frees the name and plan slot), records an audit event, and schedules the operation, which is marked complete when all steps finish.
• Per connection: disconnect (OPS-3 / OPS-4) when every account on it, hidden ones included, is in this portfolio or another deleted one, or when the owner chose to disconnect it; otherwise detach: set is_hidden on its Plaid and SnapTrade accounts in this portfolio, and leave them and any crypto holdings on the tombstone (§3 “Portfolio deletion”).
• A wallet disconnect step takes the wallet’s lock and does nothing if the OPS-10 restore has already marked it skipped, so it never deactivates a wallet the owner re-added while the operation was pending. It does not skip just because the wallet has tokens in a live portfolio: the owner may have chosen to disconnect it. Coinbase needs no such check, since nothing can reactivate a direct Coinbase connection (§3 “Coinbase comes back through SnapTrade”).
• The preview states, for each connection that stays, that its accounts here will be hidden, keep syncing, and stay stored, naming each one (§3 “A detached account keeps syncing”). It offers, per such connection, to disconnect it instead, naming the accounts in other portfolios that would stop syncing. The delete request carries those choices, and the operation stores them when it starts, so a retry does not ask again. A connection the owner chose to disconnect takes the disconnect path above.
• Done: #4546. Needs DB-5 and DB-6 applied first. GET and POST /api/portfolios/:id/deletion. start_portfolio_deletion (20261001_03) sets deleted_at, creates the operation with a plan step holding the owner’s choices, and records the audit event in one transaction. The plan becomes per-connection and per-root steps only after the portfolio is deleted. The wallet step also skips itself when a restore already moved the chain’s holdings out of deleted portfolios, which covers a restore that runs before the plan step has written the wallet step.

• Moves every root from each removed portfolio into the destination through OPS-1, rewrites goal scopes, sets deleted_at without content deletion, records an audit event per portfolio. Vault folders and documents are not touched.

• Accepts the keep set and destination; Main always stays.

• Why: removing a wallet only sets is_active = false, so the rows keep pointing at a deleted portfolio, and re-adding reuses them. Without this, the DB-7 skip hides every token of a re-added wallet, so a wallet that was ever in a deleted portfolio can never come back (§3 “Re-adding a wallet restores its tokens”).
• Wallets only. Coinbase comes back by linking it through SnapTrade, into new rows (§3 “Coinbase comes back through SnapTrade”), and SnapTrade needs no restore (LINK-6).
• RPC restore_detached_crypto(wallet, destination): in one transaction, under the move flag, locks the wallets row FOR UPDATE, creates or reactivates it, moves every crypto_holdings row on the wallet whose portfolio is deleted to the destination (owner, address, and chain, active or not, including legacy rows without token_address, plus the address’s legacy rows with no chain on the first chain restored), and writes the audit event last. Reactivation happens inside the RPC, not before it, so a pending OPS-7 disconnect step cannot take the lock in between and deactivate the wallet just re-added. Rows in live portfolios are untouched. In the same transaction, marks any pending OPS-7 disconnect step for this wallet skipped. The second move with a deleted source, after the OPS-2 restore.
• Replaces the row reactivation in app/api/crypto/add-wallet (both its inactive-row path and its active-row-with-no-active-holdings path), before any holding is written.
• Makes the wallet paths chain-aware (§3): add-wallet checks active holdings on the requested chain, not the whole address, and deleteWallet in hooks/queries/use-crypto-holdings.ts deactivates only its own chain’s holdings (its comment claiming crypto_holdings has no chain is out of date). Otherwise, once one chain of an address has active holdings, re-adding a fully detached chain is refused as a duplicate. Never from background syncs or fetch-wallet-holdings on refresh. Destination from LINK-8, else Main.
• Must merge before ROLL-4: once owners can delete portfolios, this is the only way a wallet’s tokens come back.
• Done when: two wallets with tokens in a deleted portfolio, one detached and one disconnected by the deletion, are removed and added again; their tokens and history appear in the destination, survive the next sync, and tokens in live portfolios stay put. A fully detached chain of an address whose other chain has live tokens can be added again, and a disconnect step whose lock request races the add leaves the wallet active (§5).

​

SEL: page-level selector

• Open: #4356.
• contexts/portfolio-scope-context.tsx and hooks/use-portfolio-scope.ts. localStorage keyed by Clerk user ID, never in the URL. Prunes unknown or deleted IDs before any request; empty falls back to All; checking every portfolio selects All.
• Unchanged by the move onto pages: one stored selection, read by whichever filtered page is open. The provider does not need to wrap SharedChatProvider, since the selector state is never sent to chat (AI-1).

• Rule: the filtered page, or a component on it, reads the selection and passes it to the hooks and requests it makes. A shared hook never reads the context itself, so the same hook used in chat, in a sidebar badge, or on any other page keeps All (§4).
• usePortfolioScope() returns the stored selection on every page, so reading it is not enough to know a page is filtered. Each filtered page opts in explicitly. AddAccountWidget, for example, reads accounts through the shared useAccounts() and also renders inside a chat bubble, so its reads stay on All wherever it renders; only its destination preselection uses the selection (MGMT-4).
• Covers the dashboard, transactions (its CSV export included), and the portfolio page.
• Open question: whether the asset-class pages reached from the portfolio page (/real-estate, /crypto-holdings, /public-investments, /liabilities, and the others the mobile nav groups under “Portfolio”) are filtered too. Until decided, they show All.

• Open: #4526, stacked on SEL-1. PortfolioSelectorMenu with Modulus/Components/Navigation/PortfolioSelector stories, and PortfolioSelector wired to the SEL-1 context. Under All every portfolio is checked, so unchecking one selects the rest; the only selected portfolio cannot be unchecked.
• Modulus multi-select with All as its own option, mounted at the top of the dashboard, transactions, and portfolio pages, all sharing SEL-1’s state. Hidden for Free owners with only Main.
• Works on mobile, since it is part of the page rather than the sidebar.
• #4526 currently mounts it in the desktop sidebar, between the logo and Dashboard. That mount moves to the three pages.

• Removed (2026-10-08). SEL-3’s selector button is the label: it sits beside the title on the three pages and reads “All portfolios”, the portfolio’s name, or “N portfolios” (getPortfolioScopeLabel). SEL-2 captions everything still computed on All.

​

MGMT: management UI and add-data destination

• List with types and notes, plus deleted portfolios whose operation is still running, with progress and retry.

• Group then type, name, notes. Renders portfolio_name_taken inline and portfolio_limit_reached as the upgrade path.

• Main’s type is fixed.

• Required from All or a multi-selection; preselected when the shared selection is exactly one portfolio.
• The same rule applies wherever add-data starts, including pages without the selector, chat (AddAccountWidget in a chat bubble), and onboarding. The shared selection is the owner’s persisted choice, so it still preselects there. The picker always shows the destination before anything is added, so a preselection made on another page is visible and can be changed.

• Plaid (LINK-2), SnapTrade (LINK-5), manual and crypto (LINK-8). Vault uploads get no destination in phase 1.
• Account assignment step for Plaid and SnapTrade: list the new accounts, each with a portfolio picker preset to the destination; changes go through OPS-2 (§3). Shown after a first link and after update mode adds accounts (from the IDs /api/relink returns), preset to where the sibling rule put them.

• Move one or more accounts. The preview offers the connection’s other accounts as optional additions and flags a separated cash-sweep pair. The confirmation says history before the portfolio existed will show.

• Preview, destructive confirmation, progress and retry.
• “Manage hidden accounts” (components/manage-hidden-accounts-dialog.tsx) lists detached accounts under “From deleted portfolios” in every scope; unhiding one asks for a live destination and calls the OPS-2 restore.
• The preview shows the OPS-7 statement for each connection that stays, in plain words: hidden, not deleted, keeps syncing, stays stored. Beside it, a per-connection “Disconnect entirely” choice names what else stops syncing.
• Once the deletion is confirmed, the confirmation step offers “Stop sharing these accounts” for each detached Plaid Item (LINK-10). The same offer stays on each Item under “From deleted portfolios”.
• Copy states facts and choices only. No outbound message is sent about any of this.

​

PLAN: plan limits and downgrades

• app/api/stripe/webhooks/route.ts: an effective tier change leaving the owner over the limit schedules consolidate with scheduled_for. Upgrade cancels it. unpaid and paused schedule nothing. Never moves data.

• At the deadline, recheck the subscription, stand down if within the limit, otherwise collapse into Main. A lookup failure never triggers it.

• components/downgrade-confirm-modal.tsx and before opening the Stripe portal.

• In management, calls OPS-9.

• lib/landing/pricing-plans.ts and the in-app plan grid. Hold until launch, since the landing page is public.

​

AI: Silvia

• IDs, names, types, and notes, marked as user data.
• The selector state is never sent to chat, now or in a later phase.

• Removed (2026-10-08). Silvia resolves references herself from the AI-1 metadata, following the rules in AI-7. Tools take portfolio IDs, and the resolver (API-2) rejects any ID that isn’t the owner’s or is deleted, so a code resolver added no safety.

• The scope comes only from what the user says in the conversation, resolved by Silvia from the AI-1 metadata (rules in AI-7), and defaults to All. Never from the selector.
• Whenever a scope narrower than All was used, the answer says which portfolio or portfolios it covers.

• sql-query-tool.ts, text-to-sql-tool.ts, transaction-sql-tool.ts, transaction-query-tool.ts enforce the explicit scope from AI-3 through the resolver (API-2).
• The query service exposes portfolios and portfolio_id on the roots and on the SCOPE-1 views.
• Rows in deleted portfolios stay out of her results: the resolver rejects deleted IDs, and All resolves to live portfolios only.
• Block calls to SECURITY DEFINER and portfolio RPCs from the SQL tools. The keyword filter on execute_query and execute_text_to_sql_query would let a prompt-injected SELECT public.ensure_main_portfolio('<other id>') through (follow-up from DB-3, #4361).
• Depends on the coordinated query-service change; rollout blocks on it.

• Removed (2026-10-02). The Vault is phase 2 (§6).

• Each takes the explicit scope from AI-3, never the selector.

• Database-schema/view skill: the portfolio tables and columns, so Silvia can answer questions about any named portfolio.
• Navigation skill: the page-level selector and the three pages it filters, portfolio management, the Free/Pro/Max limits, and downgrade behavior.
• UI-component skills: the scope label GenUI widgets show when a narrower scope was used.
• Portfolio reference rules (§4 “Silvia integration”), which replaced AI-2: Main by its type, exact names, a type only when one portfolio has it, and clarification for a partial name, a shared type, or “the portfolios I have selected”. These must not deploy before the rollout flag is on.
• The S3 deploy step. Not part of the gh stack; linked from AI-6.

​

ROLL: fixtures, monitoring, rollout

• scripts/multi-portfolio/seed-staging.ts builds owners across tiers with multiple portfolios and connectors.

• Alerts for assignment mismatches, duplicate-guard rejections, stuck deletion and consolidation operations, and reconciliation errors.

• Playwright flows for the §5 checklist that can run against sandbox providers. Record the two-real-Chase-logins test result manually in the PR.

• Turn on the flag for the first cohort. Scope-aware readers stay deployed if creation is later disabled (§5).