Portfolio Management System (HLD + DB Design)

0) Problem Restatement

Goldman Sachs asked (Superday, with two VPs): design a portfolio management system, both the high-level design and the database design. Investors or advisors see holdings across accounts, transactions (buys, sells, dividends, deposits), current market value, profit and loss (P&L), allocation (by asset class or sector), and performance over time, and can plan rebalancing toward target weights.

1) Core Idea: Transactions Are the Truth

Store every event that changes a portfolio as an immutable transaction. Positions (how many of each instrument we hold) are derived by adding up transactions. This gives a full history, audits, and the ability to recompute anything (e.g., "what did I hold on March 1?").

2) Database Design

CREATE TABLE clients     (client_id BIGINT PRIMARY KEY, name TEXT, base_currency CHAR(3));
CREATE TABLE accounts    (account_id BIGINT PRIMARY KEY, client_id BIGINT REFERENCES clients, type TEXT, currency CHAR(3));
CREATE TABLE instruments (instrument_id BIGINT PRIMARY KEY, symbol TEXT, isin TEXT UNIQUE, asset_class TEXT,
                          sector TEXT, currency CHAR(3));
CREATE TABLE transactions (
  txn_id BIGINT PRIMARY KEY, account_id BIGINT REFERENCES accounts, instrument_id BIGINT NULL,
  type TEXT,                    -- buy, sell, dividend, deposit, withdrawal, fee, split
  quantity NUMERIC(20,6), price NUMERIC(20,6), amount NUMERIC(20,2), currency CHAR(3),
  trade_date DATE, settle_date DATE, created_at TIMESTAMP
);
CREATE TABLE positions (      -- derived, maintained incrementally (and rebuildable)
  account_id BIGINT, instrument_id BIGINT, quantity NUMERIC(20,6), cost_basis NUMERIC(20,2),
  PRIMARY KEY (account_id, instrument_id)
);
CREATE TABLE prices   (instrument_id BIGINT, price_date DATE, close NUMERIC(20,6), PRIMARY KEY (instrument_id, price_date));
CREATE TABLE fx_rates (from_ccy CHAR(3), to_ccy CHAR(3), rate_date DATE, rate NUMERIC(20,10));
CREATE TABLE daily_valuations (account_id BIGINT, val_date DATE, market_value NUMERIC(20,2), net_flows NUMERIC(20,2),
                               PRIMARY KEY (account_id, val_date));
CREATE TABLE targets (account_id BIGINT, asset_class TEXT, target_weight NUMERIC(5,4));
CREATE INDEX ON transactions (account_id, trade_date);

3) Architecture

flowchart LR
    TR["Trades / custodian feeds"] --> TX["Transaction service"]
    TX --> DB[("Transactions + positions")]
    MD["Market data - prices, FX"] --> PR[("Prices DB + cache")]
    EOD["End-of-day valuation job"] --> DB
    EOD --> PR
    EOD --> VAL[("Daily valuations")]
    UI["Advisor / client UI"] --> API["Portfolio API"]
    API --> DB
    API --> PR
    API --> VAL
    API --> REB["Rebalancing engine"]

4) Calculations (plain words)

• Position = sum of the buy quantities − sell quantities (+ split adjustments). The cost basis is tracked per lot or on average.
• Market value = quantity × latest price (× FX rate into the client's base currency).
• Unrealized P&L = market value − cost basis. Realized P&L is recorded on sells.
• Allocation = market value per asset class or sector ÷ the total.
• Performance: use time-weighted return (chain the daily returns, removing the effect of deposits and withdrawals), so adding money doesn't look like "profit". The daily valuations table makes this a simple calculation.
• Corporate actions: a 2-for-1 split doubles the quantity and halves the cost per share (written as a split transaction). Dividends are cash transactions.

5) Rebalancing

Compare current weights with targets. Where the drift exceeds a threshold (e.g., 5%), propose trades (sell overweight, buy underweight), respecting cash, minimum trade sizes and tax considerations. The advisor reviews and approves, and the executed trades flow back in as transactions.

6) Wrap-Up

Keep an immutable transactions ledger as the source of truth, and derive positions (maintained incrementally but rebuildable) with cost basis. Price them with daily prices and FX to get market value, P&L and allocation, and store end-of-day valuations for time-weighted performance. Model corporate actions as transactions, and generate advisor-approved rebalancing proposals from target weights.