Problem RestatementProblem
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.
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?").
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);ArchitectureArchitecture
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
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"]Deep Dive — Measuring how the portfolio actually performedDeep dive
A client deposited money twice during the quarter and the portfolio is worth more than it started. How much of that was the manager, and how much was the deposits?
Compare the end value with the start value
(end − start) / start.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
S["Start: $100,000"] --> D["Client deposits $50,000 in February"]
D --> E["End: $156,000"]
E --> CALC["(156,000 - 100,000) / 100,000 = 56%"]
CALC --> WRONG["Reported as 56% return"]
WRONG --> REAL["Actual investment gain: about 4%"]Deposits are counted as gains. Any client who adds money looks like a genius, and any client who withdraws looks like a disaster — the number measures cash flow, not skill.
Subtract the net deposits
(end − start − net_deposits) / start.
The obvious distortion is gone and the arithmetic is still wrong, because when the money arrived matters. $50,000 deposited the day before quarter end was exposed to one day of market movement, not three months; treating it as if it were present throughout misstates the return, and the error grows with the size and lateness of the flow.
Chain the daily returns
Break the period at every cash flow, compute the return of each sub-period, and multiply them together — the time-weighted return:
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
VAL[("Daily valuations table")] --> SUB["Split at each deposit or withdrawal"]
SUB --> R1["Sub-period 1 return"]
SUB --> R2["Sub-period 2 return"]
SUB --> R3["Sub-period 3 return"]
R1 --> CH["Chain: (1+r1)(1+r2)(1+r3) - 1"]
R2 --> CH
R3 --> CH
CH --> TWR["Time-weighted return - the manager's performance"]
FLOW["Cash flows with their dates"] --> IRR["Money-weighted return (IRR) - the client's experience"]- Time-weighted return is independent of cash flows, which is exactly why it is the industry standard for measuring a manager: two managers with identical decisions get identical numbers regardless of what their clients deposited.
- The daily valuations table is what makes it computable. Without a stored value per day, there is nothing to split at a cash flow — which is why that table exists, not merely for charts.
- Money-weighted return (IRR) answers the other question: what the client actually earned given their timing. Both are legitimate and they answer different questions, so a good report shows both and labels them.
Everything else on the page rests on the same principle — transactions are the truth. Positions, cost basis, realised and unrealised P&L are all derived from the transaction log rather than stored and edited, so a corrected trade recomputes the history instead of leaving a balance nobody can reconcile.
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.
Wrap-UpWrap-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.