​

Multi-portfolio engineering design

​

1. Summary and product behavior

​

Plan limits

​

Downgrades

• Creating a portfolio is blocked. Everything else keeps working: linking, imports, uploads, goals, and moves between existing portfolios. The limit counts portfolios, not accounts, so additions do not take the owner further over it, and consolidation moves them later.
• Portfolio management prompts the owner to choose which portfolios to keep (Main always stays) and which kept portfolio receives the rest.
• The webhook schedules a consolidate operation for a fixed grace period later (scheduled_for). If the owner has not chosen by then, every additional portfolio collapses into Main, the one choice that needs no input. An upgrade before the deadline cancels the operation.
• unpaid and paused are recoverable, so they block creation but schedule nothing. Cancellation, when it comes, is a tier change like any other.

​

2. Current architecture and proposed data model

• Plaid Link sends the Clerk ID as client_user_id, and SnapTrade registers one user per Clerk ID (snaptrade_users has UNIQUE (clerk_id)). Both stay as they are.
• Silvia’s duplicate-connection guards for Plaid are keyed on the owner and institution, so a second login at an institution the owner already has is blocked in every portfolio.
• The SnapTrade connection root is snaptrade_institutions, keyed by snaptrade_connection_id. It has no portfolio_id; only snaptrade_accounts does.
• accounts, snaptrade_accounts, and manual_accounts each carry is_hidden, which excludes that one account from totals and snapshots. Hiding is the only way to take one connected account out of the numbers today: the only removal path is /api/disconnect-bank, which removes the whole Item.
• accounts stores Plaid’s persistent_account_id, which stays the same when Plaid issues an existing account a new account_id.
• The brokerage cash-sweep dedup (docs/BROKERAGE-CASH-SWEEP-DEDUP.md) compares sibling accounts on one Item: a depository account is suppressed when an investment account on the same Item already contains its cash.
• snapshots_by_account has no rows from 2025-10-28 through 2026-06-26 because inserts failed. snapshots_by_item and daily_snapshots_summary cover that range.
• A Coinbase connection is a coinbase_accounts row (OAuth tokens, plus wallet_id, which is Coinbase’s own portfolio ID). Coinbase has one account per currency, and the sync upserts each one into crypto_holdings on source_holding_id (Coinbase’s account ID), so every currency keeps one stable row. coinbase_holdings mirrors those rows, keyed by the same account_id.
• A wallets row is one address on one chain. On every sync, app/api/crypto/process-wallet-holdings.ts marks all of that wallet’s crypto_holdings rows inactive and inserts a new row per token, so a token’s row, and its uuid, changes every run. snapshots_by_crypto_holding keys history by that uuid, so one token’s history is spread across many rows. The sync has each token’s contract or mint address (token_address) in memory but stores only the symbol, in crypto_holdings.token, so two tokens with the same symbol on one wallet and chain, such as a spam token named “USDC”, are indistinguishable once written.
• Financial hooks and caches predominantly scope by Clerk ID.
• portfolio_daily_snapshots is date-partitioned and written by the daily snapshot cron, which filters on explicit portfolio_id values and therefore omits legacy unassigned (Main) data. The Main backfill below removes unassigned data altogether.
• Financial SQL views do not consistently expose portfolio assignment.
• Most portfolio foreign keys use ON DELETE SET NULL; asset_ownership, portfolio_links, portfolio_members, and portfolio_daily_snapshots use ON DELETE CASCADE.
• Nothing enforces one default portfolio per owner; is_default has no unique index.
• RLS on portfolios lets clients insert, update, and delete their own rows directly.
• The portfolios SELECT policy already admits accepted portfolio_members, and clients can insert member rows.
• asset_ownership (per-portfolio percentages) and portfolio_links (portfolio-owns-portfolio percentages) exist, but no application code reads them.
• vault_documents and vault_folders carry portfolio_id. Every document belongs to a folder (folder_uuid is required). Phase 1 leaves both columns unwritten (below).

​

Separate ownership, assignment, and provider identity

• Shape. The primary key is (portfolio_id, owner_clerk_id), with an index on owner_clerk_id for “portfolios this user holds a share in”. portfolio_id references portfolios with ON DELETE RESTRICT from the start, as every portfolio FK will (below). owner_clerk_id references all_users with ON DELETE CASCADE, matching portfolios.clerk_id.
• Precision. percentage is numeric(7,4), NOT NULL DEFAULT 100, CHECK (percentage > 0 AND percentage <= 100): four decimal places, so 100.0000 fits and a third is 33.3333. That is finer than the two decimal places of asset_ownership.ownership_percentage and the asset dialogs’ 0.01 step, but the range is the same. Postgres rounds a fifth decimal rather than rejecting it (33.33333 is stored as 33.3333), so phase 2’s write path rejects extra precision itself if it wants an error. An owner with no share has no row, so 0 is not a value.
• Phase 1 contents. An AFTER INSERT trigger on portfolios writes one row, the portfolio’s clerk_id at 100, in the same transaction, so every portfolio has its owner row however it was created (ensure_main_portfolio, the create RPC, a script). portfolios.clerk_id cannot change (below), so the row cannot drift from it. Phase 1 adds no other rows and nothing reads the table, so this is a priority call, not a security boundary. The table exists now, filled, so phase 2 starts with no migration and no backfill.
• Not access. A row records an economic share and grants nothing. Authorization stays on portfolios.clerk_id in phase 1 (below). Whether phase 2’s sharing derives access from this table or from portfolio_members is phase 2’s call; either way the membership paths are closed for phase 1.
• Phase 2’s invariant. Shares on one portfolio sum to at most 100, not exactly 100, because a co-owner who isn’t a Silvia user has no row: an owner holding half of a joint portfolio with a spouse who has no account is one row at 50. A sum is a cross-row rule that a check constraint cannot express, so phase 2’s write path enforces it under a per-portfolio lock. Phase 1’s single row at 100 satisfies it.
• RLS. Enabled, with a SELECT policy for rows whose owner_clerk_id is the caller and no client write policies. Writes are service-role only from the start, so the table needs no guard in the portfolio lockdown.

• Plaid accounts (accounts).
• SnapTrade accounts (snaptrade_accounts).
• Manual accounts and standalone assets.
• Crypto holdings (crypto_holdings), whether from a wallet, from Coinbase, or entered manually.

• A connection’s first accounts take the destination the owner chose when linking. Each flow writes that destination onto the accounts it creates (section 3). Plaid’s /api/connect-bank inserts them in the same request. Coinbase links through SnapTrade, so it takes the SnapTrade path; no client starts the direct Coinbase sign-in any more, so its callback carries no destination. SnapTrade’s accounts can arrive by webhook first, so they take the destination from the link intent. A wallet’s first tokens come from the fetch-wallet-holdings call the app makes right after add-wallet, which passes the destination along.
• A later Plaid account arrives only when the owner completes update mode. NEW_ACCOUNTS_AVAILABLE just flags the Item, and create-link-token-relink enables account selection. So the owner is present, and the update-mode flow asks which portfolio each new account goes to (section 3).
• A later SnapTrade account, wallet token, or Coinbase currency arrives in the background, with nobody to ask. It follows the sibling rule. If every account on the connection is in a deleted portfolio, the connection is being removed, and the insert is rejected. Otherwise, if the connection’s accounts in live portfolios all share one portfolio, the new account goes there. Otherwise, it goes to Main. A split connection’s background arrivals therefore go to Main, where the owner can move them.

• Ordered IDs. portfolio_audit_events.id is a bigint identity, and the revision is max(id) for the owner, read through an index on (clerk_id, id).
• Commit order matches ID order. Postgres assigns an identity at insert, not at commit, so two concurrent moves can commit out of ID order. The later ID becomes visible first, a client caches under it, and when the earlier move commits the maximum does not change, so that cache never goes stale. Every audit insert therefore goes through one function, record_portfolio_audit_event, which first takes a transaction-scoped advisory lock keyed by owner. Callers write the event as their last step, after taking their row locks.
• No stepping back. Deleting an owner’s newest event would return the revision to a value caches have already used. Any future pruning keeps each owner’s newest event.

​

Database-level protections

• Foreign keys. Change portfolio foreign keys from SET NULL and CASCADE to RESTRICT. Portfolios are tombstoned, never hard-deleted, so these actions should never fire; if one does, it must fail loudly rather than turn data into Main or drop snapshots.
• One Main per owner. Add a partial unique index on portfolios (clerk_id) WHERE is_default. ensure_main_portfolio relies on it to stay idempotent under concurrent calls.
• Every root has a portfolio. After the backfill, portfolio_id is NOT NULL on every financial root. BEFORE INSERT triggers fill missing values:
  • A manual account, a manual crypto holding, or a standalone asset falls back to the owner’s Main ID.
  • A Plaid or SnapTrade account, or a Coinbase holding, inserted without a portfolio_id follows the sibling rule (above). The exception is a Plaid account whose persistent_account_id matches another account on the same Item. That is Plaid reissuing an existing account under a new account_id, so it keeps the predecessor’s portfolio.
  • A Coinbase holding’s connection is the active coinbase_accounts row with the same owner and wallet_id. wallet_id has no unique constraint today, and several owners have more than one row. So a partial unique index on coinbase_accounts (clerk_id, wallet_id) WHERE is_active makes the parent deterministic, after any stale duplicates are deactivated. A holding with no active parent lands in Main. Reconnecting does not create a second row: the OAuth sign-in callback (app/api/auth/[...nextauth]/route.ts) looks up the existing row by wallet_id and reactivates it, inserting only when none exists. So a reconnect keeps its holdings and their portfolios, and the index never sees two active rows. That includes a holding in a deleted portfolio, which stays there: the callback restores nothing, and no client starts it any more (section 3, “Coinbase comes back through SnapTrade”). The SnapTrade Coinbase migration in app/api/snaptrade/process-connection only deactivates rows. Any future path that replaces a row must deactivate the predecessor first.
  • A wallet holding takes the portfolio already held by its token key, and only a token never seen on that wallet follows the sibling rule. This is what keeps a moved token in place across the sync’s deactivate-and-insert. The lookup needs an index on the token key.
  • Each of these triggers reads its connection row with FOR SHARE, the same row a move locks FOR UPDATE (section 3). So an insert that races a move waits for it and then reads the committed assignment, instead of reading sibling portfolios or a token key that the move is about to change.
  • Child rows (Plaid and SnapTrade holdings) never get the Main default.
• Unique names. Add a partial unique index on portfolios (clerk_id, lower(btrim(name))) WHERE deleted_at IS NULL, so concurrent creates and renames cannot produce a duplicate.
• Server-only portfolio writes. Drop the client INSERT and DELETE policies on portfolios, and stop clients from changing clerk_id, is_default, or deleted_at, or from setting or clearing the main type. Plan-limit enforcement and the deletion workflow both depend on this. portfolio_owners needs no guard here: it never had client write policies (above).
• No assignment copies. Plaid and SnapTrade accounts and crypto holdings are roots, not children, so no connection-level value is copied onto them after insert. The Vault’s folder-to-document copy is phase 2 (section 6).
• Assignment changes only through a move. A BEFORE UPDATE trigger on every root rejects a change to portfolio_id unless it comes from the move, consolidation, or deletion functions, which set a transaction-local flag. Sync workers upsert accounts, snaptrade_accounts, and Coinbase crypto_holdings constantly; this stops an upsert that carries a stale or null portfolio_id from quietly moving an account. A move of a wallet token updates every row with its token key in the same statement.
• Coinbase owner change resets assignment. The Coinbase OAuth sign-in callback (app/api/auth/[...nextauth]/route.ts) finds the coinbase_accounts row by wallet_id alone and writes the signing-in user’s clerk_id. So when a second Silvia user connects a Coinbase account another user already connected, the row changes owner, and the holdings sync then rewrites clerk_id on each of its crypto_holdings rows. Phase 1 keeps that takeover behavior. Left alone, the rows would keep the first owner’s portfolios, and the next holdings upsert would fail the same-owner check and stop the new owner’s sync. Instead, a BEFORE UPDATE trigger on Coinbase crypto_holdings resets assignment whenever clerk_id changes: portfolio_id becomes the new owner’s Main, in the same statement. The connection row carries no assignment, so it needs no reset. This is the one change to portfolio_id outside the move, consolidation, and deletion functions. It records an audit event, with IDs only, for each owner, taking the two owner locks in clerk_id order. That advances both owners’ assignment revisions, so neither owner’s cached views keep the holdings.
• Live portfolios only. The fill, copy, and move paths reject any portfolio_id whose portfolio has deleted_at set. A late webhook that discovers a new account on a connection being removed fails its insert under the sibling rule, since every account on that connection is in a deleted portfolio, even while cleanup is still running. The worker treats that error as “connection being removed” and acknowledges the event instead of retrying. A connection that survives a deletion still has accounts in a live portfolio, so its inserts succeed. Moves out of a deleted portfolio are rejected too, except two restores (section 3): unhiding a hidden, detached Plaid or SnapTrade account, which moves it to a live portfolio and clears is_hidden in one statement, and adding a wallet again, which moves its tokens in deleted portfolios to the add’s destination. Clearing is_hidden any other way on an account in a deleted portfolio is rejected, so today’s unhide call cannot leave a visible account on a tombstone.
• Dormant tables. Phase 1 does not read asset_ownership, portfolio_links, or portfolio_owners. The first two cascade from the legacy cleanup, so they start empty; portfolio_owners holds only each portfolio’s owner row. Per-asset ownership_percentage on properties and manual accounts is unrelated and keeps its current meaning: it is the owner’s share of one asset, while portfolio_owners.percentage is a user’s share of a whole portfolio.

​

Main portfolio

1. Delete the legacy rows (section 2, above), add the one-Main-per-owner index, then create a Main row (name “Main”, type main, is_default = true) for every existing owner.
2. Add a BEFORE INSERT trigger to every top-level financial root that fills a missing portfolio_id with the owner’s Main ID through ensure_main_portfolio(clerk_id), which creates Main with ON CONFLICT DO NOTHING if it is somehow missing. Existing writers need no change for the migration; new code passes its destination explicitly.
3. Backfill null portfolio_id values to Main in batches, on the roots listed above. Existing aggregate goals get the All scope (section 4).
4. Add CHECK (portfolio_id IS NOT NULL) NOT VALID, run VALIDATE, then SET NOT NULL, so Postgres skips a full table scan while holding the lock.
5. Create Main at sign-up, from the Clerk user.created webhook.

​

Provider identity

• Plaid documents client_user_id as the app’s own user ID, used to search logs in the Dashboard. An Item is one institution login, and Plaid calls several Items at one institution a legitimate use case. Its Chase and PNC invalidation rule applies to a second Item made with the same credentials. Plaid Link API, Duplicate Items, OAuth guide
• SnapTrade de-duplicates a connection only when the same brokerage credentials connect again; a connection is one brokerage login. SnapTrade connections

​

3. Linking, moving, and deleting

​

Linking and ongoing synchronization

• Destination is fixed at the start. Changing the selector mid-flow does not change it. For Plaid, the client keeps it next to plaid_link_token, which already survives the OAuth redirect, and sends it to /api/connect-bank. No server-side record is needed: only that call creates an Item, and the server validates the destination there.
• SnapTrade needs a server-side link intent (NEW), because completion is not the only path that creates the connection. handleNewAccountAvailable in the webhook and the missing-root recovery in process-snaptrade-holdings-sync both insert snaptrade_institutions, and completion is triggered by the browser, so a closed tab skips it. Correlation differs by path:
  • Each intent is bound to exactly one SnapTrade authorization (snaptrade_link_intents.snaptrade_connection_id, unique, set once). An account’s destination comes only from the intent bound to its own authorization, never from whichever intent happens to be pending.
  • Client completion binds exactly. The intent ID rides in the redirect URL the app already supplies (customRedirect), and SnapTrade adds the authorizationId. process-connection binds the intent to that authorization and assigns the accounts that link created to the intent’s destination, moving them there if a webhook created them first. If a webhook had bound the intent to a different authorization, completion’s binding wins, and that other authorization’s accounts move to Main. The success-step picker opens only after this, so no per-account choice has been made yet that it could overwrite.
  • Webhooks and sync recovery know only SnapTrade’s own IDs, including brokerageAuthorizationId. An owner can therefore have one pending SnapTrade intent at a time; starting a new one cancels the previous one. These paths use the intent bound to the event’s authorization if there is one. Otherwise they bind the pending, unbound intent only to an authorization Silvia first saw after the intent was created: one whose snaptrade_institutions row this event is creating, or whose row’s created_at is later than the intent’s. Both timestamps come from the database clock, so no ordering against SnapTrade’s createdDate is involved. That covers the closed-tab case, where completion never runs. The check matters because a connection can exist with no local accounts: process-connection writes snaptrade_institutions before it checks for accounts, and returns 404 when SnapTrade reports none. So an old, empty authorization is never mistaken for the one a newer link is creating. Nothing is ambiguous under this test: an authorization Silvia already knew before the intent existed belongs to an earlier link. An account on an authorization that matches no intent follows the sibling rule (section 2), which puts an empty or new connection’s account in Main.
  • No pending intent (expired or cancelled) lands the connection and its accounts in Main. Holding them unassigned would hide a working connection and add a lifecycle state; in Main they are visible in All and can be moved, and a late client completion carrying its intent ID still reassigns them.
• Destination deleted mid-flow. Completion into a portfolio that has since been deleted is rejected. Plaid rejects before the token exchange; a SnapTrade connection already created is removed with removeBrokerageAuthorization.
• Ongoing sync is unchanged. Webhooks resolve by item_id and snaptrade_user_id as today, and workers never write portfolio_id. An account discovered later lands under the sibling rule (section 2); after update mode the owner then confirms or changes it (above). The Plaid balance jobs (app/api/fetch-balances, app/api/cron/process-stale-balance) upsert every account Plaid returns on account_id, without clerk_id or name, so an account Plaid returned before the owner added it would become a bare row. Account selection should prevent that, but nothing in the code does. So both jobs must skip any account_id they have no stored row for, and any bare rows already in production are counted and fixed before the Main backfill, which cannot give an account with a null clerk_id a Main (PR plan, production findings and DB-9). A Plaid account reissued under a new account_id keeps its predecessor’s portfolio through persistent_account_id (section 2).

​

Duplicate-connection guards

• The client guard in PlaidLinkButton closes Plaid Link as soon as the user picks an institution the owner already has.
• The server guard in /api/connect-bank rejects the token exchange when any incoming account matches a stored account at that institution by name and mask.

​

Moving accounts and assets

• Validates common ownership and live source/destination portfolios. The exceptions are the two restores, whose source is a deleted portfolio: unhiding an account that a portfolio deletion detached, and adding a wallet again (section 3, “Portfolio deletion”).
• Locks each affected connection row FOR UPDATE, then re-reads that connection’s accounts under the lock to confirm the selected accounts are still on it and live.
• Updates each selected account’s portfolio_id. An account discovered after the preview was not selected, so it stays where it landed.
• Records an audit event, which advances the assignment revision.
• Invalidates source, destination, aggregate, and Silvia caches.

• A new account arrives during a move. The sibling rule reads the portfolios of the connection’s other accounts. Without the shared lock, a SnapTrade account arriving while the owner moves both of a connection’s accounts to “Joint” could read their old portfolio and land in Main. With it, the insert waits and reads the committed portfolios. An insert that locked first makes the move wait, and lands under what it read.
• A wallet sync runs during a token move. The sync inserts a fresh row for every token on every run. Without the shared lock, the trigger could copy the token key’s old portfolio onto the fresh row, splitting the token between two portfolios. The move locks the wallet row, so the fresh row reads the committed portfolio.

​

Connections that span portfolios

• Where a connection appears. In any scope that includes at least one of its live accounts. It lists only the accounts in scope and names the others (“1 more account in Main”).
• Health. A reconnect prompt, sync error, or has_new_accounts_available notice shows in every scope where the connection appears, because fixing it affects every account on it.
• Reconnect and refresh act on the whole connection, as today, and keep each account’s assignment.
• Disconnect removes the whole connection, as today. When it has accounts in portfolios outside the current scope, the confirmation names them and their portfolios.
• Hiding stays per account. A hidden account keeps its portfolio. Hidden-account management lives on /accounts, which has no selector, so it lists every hidden account, with any a portfolio deletion detached in their own group (section 3). Crypto holdings have no hide flag today, and phase 1 doesn’t add one.
• Cash-sweep dedup is decided over the whole Item, never over the accounts in scope (section 4). If a Wells Fargo “BROKERAGE CASH” account and its brokerage account are in different portfolios, the sweep account still reads zero in its own portfolio, because its cash is already counted in the brokerage account’s portfolio. The move preview says so when it separates such a pair.

​

History follows current assignment

​

Portfolio deletion

1. Show a preview of every financial root in the portfolio (section 2), and of goals whose whole scope is being deleted. The preview also says that Vault folders and documents are not part of any portfolio and stay where they are: deleting a portfolio never deletes a document. For each connection that stays connected, the preview says that its accounts here keep syncing, and offers to disconnect it instead. For Plaid, it also says the accounts can be unticked once the deletion is confirmed (below).
2. Require destructive confirmation, including the owner’s choice for each connection that would stay.
3. Set deleted_at, record an audit event, and start the deletion operation. From then on the portfolio is excluded from financial views, its name and plan slot are free, and linking, moves, edits, and sync writes into it are rejected (enforced in the database, section 2).
4. Run each existing account/asset deletion behavior through shared server services.
5. Track provider disconnections and local cleanup independently; retry unfinished work.
6. Mark the operation complete after all required work completes. The portfolio row stays as a tombstone.

• Disconnect when every account on the connection is in this portfolio or already in a deleted one. Hidden accounts count, so a connection with a hidden account in Main is not disconnected. It also applies when the owner chose, in the preview, to disconnect a connection that would otherwise stay. This is today’s whole-connection removal.
• Detach when the connection still has an account in another live portfolio. The connection stays connected for those accounts. What happens to the accounts in this portfolio depends on the kind of account (below). The preview names each connection that stays and the accounts keeping it alive: “Chase stays connected for Chase individual checking in Main.” The owner can turn a detach into a disconnect (below).

• Deletion sets is_hidden on each detached account and leaves it assigned to the deleted portfolio, so its history stays out of every live portfolio’s chart. The provider still returns the account, so sync keeps updating the row, as it does for any hidden account.
• “Manage hidden accounts” lists detached accounts under “From deleted portfolios”, whatever the selection. Unhiding one asks for a live destination portfolio, preset to the portfolio of its connection’s other accounts. This is a move out of a deleted portfolio, one of the two restores the move functions allow (section 2). The account’s history comes with it, as with any move.
• The server duplicate guard keeps matching detached accounts, as it matches hidden accounts today (app/api/connect-bank/route.ts does not filter on is_hidden). Otherwise a second login could link the same joint account and it would count twice once restored. The rejection message points to “Manage hidden accounts”.
• Hiding does not stop the institution sharing the account. Only unticking it in Plaid’s account selection (update mode) does, and the deletion flow offers that (below).

• The statement. For example: “Chase stays connected for Chase individual checking in Main. Spouse checking will be hidden, not deleted. Chase keeps sharing it, and Silvia keeps receiving and storing its balances and transactions, out of every view.” It names every detached account on the connection. It is not a warning shown only at a threshold; it is part of every detach.
• Stop sharing these accounts (Plaid). Once the deletion is confirmed, its confirmation step offers, for each detached Item, Plaid update mode with account selection. This is the existing create-link-token-relink, which already sets update.account_selection_enabled, with the accounts to untick named beside it. /api/relink already calls accountsGet after update mode. It gains one step, which is why the offer comes after confirmation, once the accounts sit in a deleted portfolio: a detached account the Item no longer returns is marked is_active = false, which takes it out of “From deleted portfolios” and ends its syncing. The server duplicate guard already matches only active accounts (app/api/connect-bank/route.ts), so that account can then be linked from the other person’s own login without a clash. An account the Item still returns stays hidden and keeps the offer. The offer also stays under “From deleted portfolios” in “Manage hidden accounts”, so an owner who skipped it can come back to it. At OAuth institutions such as Chase, the account choice happens on the bank’s own consent page. Whether unticking there makes Plaid stop returning the account is confirmed in the sandbox and in the live Chase test (section 5) before this option ships. If it does not, the option is left out for that institution and the preview offers only the disconnect.
• Disconnect the whole connection instead (every provider). For each connection that would stay, the preview offers to disconnect it after all, and names the accounts in other portfolios that stop syncing with it: “Disconnect Chase entirely. Chase individual checking in Main stops syncing too.” This is the only way to stop a SnapTrade or Coinbase connection sharing a detached account, since neither provider can drop one account (above). The choice is stored on the operation when it starts, so a retry does not ask again. The owner can link the login again afterwards and choose which accounts to share.
• What is already stored stays. Neither option deletes what Silvia has already stored for the detached accounts. It stays on the tombstone, out of every view, under today’s retention for a hidden or disconnected account (no new hard-purge policy, above). The preview says that too. Whether deleting a portfolio should also offer to purge that stored data is a retention decision for the team, left open here.

• Destination. The one the add already carries: add-wallet receives it and passes it on to fetch-wallet-holdings. A legacy request without a destination restores into Main, like any legacy addition (section 4).
• Only a user-driven add restores. Background syncs and fetch-wallet-holdings on refresh do not. So while the wallet stays added, a detached token stays out of every view, as before, until the owner adds that chain again.
• A deletion still running. If a deletion operation has not yet run its disconnect step for the wallet, the restore marks that step skipped, in the same transaction and under the same connection lock the step takes. Otherwise a slow deletion could deactivate a connection the owner has just added back. The step does not decide this itself by checking for holdings in live portfolios, because the owner may have chosen to disconnect a connection that has them (“Disconnect the whole connection instead”, above).

​

4. Read interfaces, authorization, and Silvia

​

Shared scope contract

type PortfolioScope =
  | { kind: 'all' }
  | { kind: 'portfolios'; portfolioIds: string[] }

​

Plan-limit enforcement

• Resolve the tier with getActiveSubscription, the same resolver token gating uses, so trialing and past_due keep their paid tier and unpaid, paused, and canceled resolve to Free.
• Count Main plus live additional portfolios, and insert, in one transaction that holds a per-owner lock (for example an advisory lock keyed by owner), so concurrent requests cannot exceed the limit.
• Reject with a typed portfolio_limit_reached error carrying the current tier and limit, which the UI uses to show the upgrade path.

​

Financial views and caches

​

Authorization and future roles

​

Silvia integration

• Unqualified requests use All portfolios.
• Silvia narrows scope only when the user names portfolios in the conversation (“work with Spouse’s portfolio”, “compare Main and the LLC”). The narrowed scope applies to that operation only.
• She cannot answer “the portfolios I have selected”, because she has no access to the selection. She asks which portfolios the user means.
• Whenever an answer used a scope narrower than All, it says which portfolio or portfolios it covers.
• “Main” or “my main portfolio” resolves through the main type, whatever Main is currently named.
• A name resolves to exactly one portfolio; names are unique per owner (section 1). A type reference (“my child’s portfolio”, “the revocable trust”) resolves when exactly one portfolio has that type; one that can still match several, or a partial name, requires clarification.
• Financial writes require one concrete authorized target.
• Responses and generated artifacts identify their portfolio scope.
• Portfolio creation through Silvia, if offered, uses the same server-side plan check. At the limit, Silvia explains the plan limit and the upgrade path instead of filing a support ticket.

​

5. Delivery and acceptance criteria

​

Delivery sequence

1. Schema and compatibility: verify deployed state; add metadata, deleted_at, portfolio_owners, ownership constraints, and the main type; run the Main backfill and set NOT NULL; apply the database-level protections (foreign keys, default-portfolio index, server-only portfolio writes, membership paths removed).
2. Provider support: destination-bound linking, the cross-portfolio duplicate-guard confirmation, reconnects, workers, and webhooks.
3. Financial scope: queries, caches, valuation views, snapshots, exports, read-time history, and the All reconciliation against daily_snapshots_summary.
4. Management UI and plan gating: the multi-select page-level selector on the dashboard, transactions, and portfolio pages, create/edit, account-level moves, the account assignment step after linking and update mode, retryable deletion, server-side plan limits, upgrade prompts, the downgrade warning in components/downgrade-confirm-modal.tsx and before opening the Stripe portal, the over-limit keep-and-destination prompt, the scheduled consolidate operation, and the Pro/Max feature bullets in lib/landing/pricing-plans.ts and the in-app plan grid.
5. Silvia: portfolio metadata in context, enforced tool scope taken from the conversation, the query-service change, and coordinated skills deployment.
6. Rollout: staging fixtures and provider sandbox checks, followed by a feature-flagged cohort. The rollout flag and the plan gate are independent: the flag decides whether the feature exists for a user, then the plan decides how many portfolios they may create.

​

Required verification

• Existing Main accounts connect, refresh, and reconnect without re-registration.
• One Clerk account links separate spouse credentials through both providers under the same provider identity, and every connection stays healthy. Test with two real Chase logins, one of which can see a joint account; Plaid’s sandbox mock banks do not reproduce Chase’s OAuth behavior.
• A second login at an institution already connected in another portfolio links successfully after the different-login confirmation; the same institution in the destination portfolio is still blocked, and an account already connected anywhere under the owner is still rejected.
• Cross-user portfolio IDs fail under APIs, RLS, workers, and AI tools.
• Every portfolio, each backfilled Main included, has exactly one portfolio_owners row: its owner at 100. A client cannot insert, update, or delete one.
• OAuth return after selector changes still uses the original destination.
• A SnapTrade connection whose webhook arrives before process-connection, or whose tab is closed before the redirect, still lands in the intended destination.
• A webhook for an older SnapTrade authorization that has no local accounts, arriving while a newer link’s intent is pending, does not take that intent’s destination. It lands in Main, and the newer link’s own authorization still lands in its destination.
• One Chase login with an individual and a joint checking account, the individual account in Main and the joint account in “Joint”: each portfolio shows only its account, All shows both once, and the connection and its health status appear in both portfolios.
• Moving one account of a connection leaves its sibling accounts, the connection, and provider credentials unchanged, and survives concurrent sync. A sync upsert never changes an account’s portfolio_id.
• One wallet token moved to another portfolio stays there after the next wallet sync inserts fresh rows, the wallet’s other tokens stay put, and its history before the move appears under the destination. A Coinbase currency account moved on its own stays there across Coinbase syncs.
• Two tokens with the same symbol on one wallet and chain (for example a spam “USDC” beside the real one) move independently, and each keeps its own history.
• A wallet sync that runs while one of its tokens is being moved leaves every fresh row for that token in the destination. A SnapTrade account discovered while its connection’s accounts are being moved lands under the sibling rule as of the committed move, never a stale read.
• A Coinbase holding for an owner with several coinbase_accounts rows resolves to the one active row with its wallet_id.
• When a second owner connects a Coinbase account another owner already had, the connection and, on the next sync, its holdings land in the new owner’s Main, and that owner’s Coinbase sync keeps running. The first owner’s views drop the holdings.
• Deleting a portfolio that holds one token of a wallet whose other tokens are elsewhere keeps the wallet syncing: later syncs skip the deleted token and write the others.
• Moved history appears under the destination; aggregate values remain unchanged.
• For a split Plaid Item, a scope holding only some of its accounts shows 2025-10-28 through 2026-06-26 as unavailable for that Item, while All and scopes holding the whole Item show that range from snapshots_by_item.
• A cash-sweep account in a different portfolio from its brokerage account stays suppressed, and All still equals the sum of the portfolios.
• Deleting a portfolio disconnects a connection only when none of its live accounts, hidden ones included, remain in another portfolio, or when the owner chose to disconnect it in the preview. A connection that stays keeps its other accounts, its detached accounts are hidden and stay out of every view after later syncs, and the owner’s SnapTrade user stays in place.
• The deletion preview states, for every connection that stays, which of its accounts will be hidden but keep syncing and stay stored, and offers to disconnect that connection instead, naming the accounts in other portfolios that would stop syncing with it. Choosing to disconnect removes the connection, and a retried operation does not ask again.
• After a deletion detaches Plaid accounts, the owner can untick them in update mode. Each account the Item no longer returns is marked inactive, drops out of “From deleted portfolios”, stops syncing, and can be linked from another login without a duplicate-guard rejection. An account still returned stays hidden and keeps the offer. Tested in the sandbox and with the live Chase logins.
• A detached account appears in “Manage hidden accounts” under “From deleted portfolios”; unhiding it asks for a live portfolio, and its balance and history then appear there. Clearing is_hidden directly on it is rejected, and linking it again through another login is still blocked by the duplicate guard.
• A detached Coinbase currency account or wallet token stays out of every view while its connection stays connected, including after later syncs.
• A wallet with tokens in a deleted portfolio, whether the deletion detached or disconnected it, can be removed and added again. Those tokens then appear in the destination chosen for the add, with their history, and later syncs keep them there. Its tokens in live portfolios stay where they were. A background sync restores nothing.
• A Coinbase connection that a deletion detached or disconnected comes back by linking Coinbase through SnapTrade: its accounts land in that link’s destination, the direct coinbase_accounts rows are deactivated, and the old holdings stay out of every view.
• A deletion whose disconnect step runs after the owner has added the wallet back leaves it connected, including when the step’s lock request arrives while the add is still running.
• For an address tracked on two chains, where one chain’s tokens are all detached and the other chain has live tokens, the detached chain can be added again and restores into the destination while the other chain’s holdings stay where they were. Removing one chain of an address leaves the other chain’s holdings active.
• A SnapTrade brokerage login disconnected by a portfolio deletion and linked again gets new account IDs, which land in the new link’s destination. Linking again a login whose accounts the deletion only detached leaves those accounts hidden in the deleted portfolio, restorable from “Manage hidden accounts”.
• Partial provider deletion failures retry safely; a late webhook’s insert into a deleted portfolio is rejected and acknowledged, not retried.
• A connection’s first accounts land in the destination chosen when linking, for Plaid, SnapTrade (including webhook-first and closed-tab), Coinbase, and wallets. A later background account lands in the portfolio its connection’s live accounts share, or Main if they are split; one on a connection being removed is rejected and acknowledged. A Plaid account reissued under a new account_id keeps its predecessor’s portfolio.
• After Plaid update mode adds an account to an existing connection, the app lists it with a portfolio picker preset to where the sibling rule put it, and the owner’s choice sticks.
• No Plaid balance job inserts an accounts row for an account the owner never added.
• All and every multi-portfolio selection equal the sum of their portfolios, for current totals and history, using the same valuation semantics.
• The same set of portfolios selected in a different order produces the same cache key and results.
• A persisted selection containing a deleted portfolio drops it before any request; an emptied selection falls back to All.
• The selection survives a new tab and a browser restart on the same device, is not shared with another device on the same login, and never appears in a URL.
• The selector appears only on the dashboard, transactions, and portfolio pages, on desktop and mobile. A selection made on one of them carries over to the other two. Every other page, and every surface on it, shows All, including shared hooks such as sidebar badges while a filtered page shows a narrower selection.
• Adding data from a page without the selector preselects the destination when the shared selection is exactly one portfolio, and shows it before anything is added.
• A request whose set includes any unauthorized, malformed, or deleted ID fails as a whole.
• Joint-account duplicates are surfaced for review without automatic merging.
• Charts and transactions remain accurate beyond 1,000 history rows.
• CSV-imported transactions on a manual account appear only in scopes that include the account’s portfolio, move with the account, and are counted once in All. A CSV import into a manual account lands in that account’s portfolio regardless of the current selection.
• The Vault ignores portfolios: it lists every folder and document whatever the selection, uploads ask for no portfolio, and deleting or consolidating a portfolio leaves every folder and document in place. The deletion preview says so.
• Existing net_worth, total_cash, and total_debt goals keep their progress after other data moves out of Main; an account-tracking goal follows its account; a goal’s progress uses its own scope in every view. A goal created on /goals defaults to All.
• Rapid selection changes never show cached data from another scope.
• Silvia handles aggregate, named, multi-portfolio, ambiguous, and deleted portfolio requests correctly, and never returns rows in a deleted portfolio.
• An unqualified Silvia request covers All portfolios whatever the selector shows, and a request naming portfolios uses exactly those. Her answers never change with the selection; asked about “the portfolios I have selected”, she asks which portfolios the user means. An answer that used a scope narrower than All names the portfolios it covers.
• Desktop/mobile and light/dark flows use existing semantic tokens and accessible controls.
• Free cannot create a portfolio; Pro stops at 2 portfolios in total and Max at 10, and the next attempt returns portfolio_limit_reached. Main counts toward the limit.
• Concurrent creation requests cannot exceed the limit, and a direct client insert into portfolios is rejected by RLS.
• trialing and past_due subscriptions get their paid limit; unpaid, paused, and canceled get Free.
• A tier drop from any source (in-app, Stripe portal, cancellation) leaves an owner over the limit able to add data but not create portfolios, and prompts for a keep-and-destination choice. Consolidating moves every financial root, including wallets and exchange connections, into the destination, and All is unchanged.
• With no choice by the deadline, every additional portfolio collapses into Main; an upgrade before the deadline cancels it; unpaid and paused schedule nothing.
• Consolidation never deletes financial data, runs once under repeated webhooks, and resumes after a partial failure. A subscription-lookup failure never triggers it.
• A direct client delete of a portfolio fails, and no data is reassigned to Main.
• After the backfill, every owner has exactly one Main, every financial root has a non-null portfolio_id, the legacy rows are gone, and today’s readers behave identically. An insert without portfolio_id lands in Main, and a new sign-up gets Main.
• Main’s name and notes, and any portfolio’s name and notes, can be edited. Main’s main type cannot be removed or given to another portfolio, and no other portfolio can be named “Main”. After Main is renamed, “my main portfolio” still resolves to it in Silvia.
• Logs and audit events contain no portfolio names or notes.

​

6. Phase 2

• A BEFORE INSERT fill trigger on vault_folders, falling back to the owner’s Main.
• The document copy trigger: a document takes portfolio_id from its folder on insert, an update of the folder’s portfolio_id cascades to its documents in the same statement, and a direct document write that differs from the folder is rejected.
• A backfill of every folder, including those created during phase 1, to Main, with documents following through the cascade.
• NOT NULL on both columns, and both tables added to the move-only update guard.
• Listing and search scoped through the folder’s portfolio, including chunk search, which filters through each chunk’s document. Documents received by email land in Main.
• An upload destination: uploading from All or a multi-portfolio selection requires choosing a folder, and the document takes its portfolio.
• Deletion and consolidation: the deletion preview lists folders and documents, deletion runs the Vault deletion service on them, and consolidation and moves carry folders with their documents.
• Silvia’s document tools taking the explicit scope from the conversation, like her other tools (section 4).