Virtual Portfolio Design
User-owned reporting overlays that aggregate real portfolios and books across the book tree

Virtual portfolios do not appear in the system portfolio tree used by real portfolios and system report definitions. They are always root nodes, displayed in a separate "My Virtual Portfolios" section of the portfolio explorer. This separation ensures that:

• System report definitions (which aggregate by real portfolio hierarchy) are not confused by user-defined views.
• The system tree remains a clean representation of organisational ownership.
• Real portfolios cannot have virtual portfolios as children — this is structurally impossible since virtual portfolios live in a separate context.

Virtual portfolios have no parent_portfolio_id in the system tree. Grouping of virtual portfolios (e.g. "My FX views", "My Risk views") may be added later as a separate organisational layer within the virtual portfolio section; it is not part of this design.

A virtual portfolio's members may be any combination of:

• Real portfolios (including all books beneath them, resolved recursively)
• Real books (directly)

Virtual portfolios may not be members of another virtual portfolio. This avoids recursive resolution complexity. The constraint is enforced at write time.

Two junction tables to maintain clean FK semantics:

-- Virtual portfolio → real portfolio membership
create table ores_trading_vp_portfolio_members_tbl (
    virtual_portfolio_id  uuid  not null
        references ores_trading_portfolios_tbl(id),
    portfolio_id          uuid  not null
        references ores_trading_portfolios_tbl(id),
    primary key (virtual_portfolio_id, portfolio_id),
    constraint vp_portfolio_members_no_virtual_member check (
        (select not is_virtual from ores_trading_portfolios_tbl
         where id = portfolio_id)
    ),
    constraint vp_portfolio_members_owner_is_virtual check (
        (select is_virtual from ores_trading_portfolios_tbl
         where id = virtual_portfolio_id)
    )
);

-- Virtual portfolio → book membership
create table ores_trading_vp_book_members_tbl (
    virtual_portfolio_id  uuid  not null
        references ores_trading_portfolios_tbl(id),
    book_id               uuid  not null
        references ores_trading_books_tbl(id),
    primary key (virtual_portfolio_id, book_id),
    constraint vp_book_members_owner_is_virtual check (
        (select is_virtual from ores_trading_portfolios_tbl
         where id = virtual_portfolio_id)
    )
);

Virtual portfolios are owned by the user who created them. The owner may grant other users read or manage access.

create table ores_trading_vp_permissions_tbl (
    virtual_portfolio_id  uuid         not null
        references ores_trading_portfolios_tbl(id),
    grantee_user_id       text         not null,
    permission            text         not null
        check (permission in ('read', 'manage')),
    granted_by            text         not null,
    granted_at            timestamptz  not null default now(),
    primary key (virtual_portfolio_id, grantee_user_id)
);

Access rules:

• Owner (owner_user_id): full read + manage + delete + share.
• manage grantee: can edit members and grant read to others; cannot delete or grant manage.
• read grantee: can see the virtual portfolio and use it as a workspace scope; cannot edit members.
• Users with no row in the permissions table cannot see the virtual portfolio at all (enforced by RLS on the portfolios table for virtual rows).

-- Step 1: collect direct book members
select book_id from ores_trading_vp_book_members_tbl
where virtual_portfolio_id = :vp_id

union

-- Step 2: collect books under portfolio members (recursive)
with recursive descendants(portfolio_id) as (
    select pm.portfolio_id
    from ores_trading_vp_portfolio_members_tbl pm
    where pm.virtual_portfolio_id = :vp_id

    union all

    select p.id
    from ores_trading_portfolios_tbl p
    join descendants d on p.parent_portfolio_id = d.portfolio_id
)
select b.id as book_id
from ores_trading_books_tbl b
join descendants d on b.parent_portfolio_id = d.portfolio_id;
-- RLS on ores_trading_books_tbl silently excludes books the user cannot see.

The result is the effective book set. For workspace scope, this query replaces the real-portfolio recursive descent used for non-virtual portfolios.

The PortfolioBookTreeMdiWindow shows two distinct sections:

▼ PORTFOLIOS
  ▶ Firm
      ▶ Division A
          ▶ EUR Rates Desk
              ■ EUR Book 1
              ■ EUR Book 2
          ▶ FX Desk
              ...

▼ MY VIRTUAL PORTFOLIOS
  ▼ EUR + FX Combined    [2 portfolios, 5 books]  ⚠ 1 member not visible
      ▶ EUR Rates Desk   (real portfolio)
      ▶ FX Desk          (real portfolio)
      ■ Special Book     (direct book member)
  ▼ My Overnight Hedges  [3 books]
      ■ ...

• Virtual portfolio nodes use a distinct icon (briefcase_20_regular).
• ⚠ N member(s) not visible badge when effective membership < defined membership (cross-party books the user lacks access to).
• Selecting a virtual portfolio node filters the trade table to its effective books, identical in behaviour to selecting a real portfolio node.

Accessible via right-click on any virtual portfolio node or "New Virtual Portfolio" action.

• Name field.
• Members panel: searchable picker showing both real portfolios (with full path Firm / Division / Desk / Portfolio) and books. Selected members listed with remove button.
• Sharing panel: list of grantees with permission level; add/remove grants.
• Effective membership count shown live as members are added/removed.

When setting a workspace's scope_portfolio_id, the picker shows:

• Real portfolios (labelled with full path)
• User's virtual portfolios (labelled "[VP] name")

Both in a unified searchable list, clearly distinguished.

1. Add is_virtual and owner_user_id to portfolios table.
2. Add no-virtual-parent constraint.
3. Create ores_trading_vp_portfolio_members_tbl and ores_trading_vp_book_members_tbl.
4. Create ores_trading_vp_permissions_tbl.
5. Add RLS policy on portfolios table: virtual rows visible only to owner and permission grantees.
6. Add full SQL machinery: triggers, NOTIFY.
7. Run recreate_database.sh; validate schemas.

1. Extend portfolio domain type with is_virtual, owner_user_id fields.
2. New domain type virtual_portfolio_members (portfolio IDs + book IDs).
3. NATS endpoints:
   • portfolio.v1.virtual.create
   • portfolio.v1.virtual.update-members
   • portfolio.v1.virtual.update-permissions
   • portfolio.v1.virtual.delete
   • portfolio.v1.virtual.list — list visible virtual portfolios for caller
   • portfolio.v1.virtual.resolve — expand to effective book IDs
4. Extend portfolio.v1.list to include virtual portfolios (flagged).

1. PortfolioBookTreeMdiWindow: separate virtual portfolio section; distinct icon; "N member(s) not visible" badge; book/portfolio leaves.
2. Virtual portfolio editor dialog (members + sharing).
3. Trade table filtering for virtual portfolio node selection.
4. Workspace scope picker: unified real + virtual list.