Low Level Design: Investment Portfolio Tracker

Problem Statement

Design an investment portfolio tracker that stores positions, pulls real-time price feeds, calculates gain/loss, alerts on rebalancing drift, and tracks tax lots for cost-basis reporting.

Core Requirements

• Position storage per user and account
• Real-time and end-of-day price feed ingestion
• Unrealized and realized gain/loss calculation
• Portfolio rebalancing alerts when allocation drifts from target
• Tax lot accounting (FIFO, LIFO, specific identification)

Data Model

users
  id            BIGINT PK
  email         VARCHAR(255) UNIQUE NOT NULL

accounts
  id            BIGINT PK
  user_id       BIGINT FK -> users.id
  name          VARCHAR(100)
  account_type  ENUM('taxable','ira','roth','401k')
  currency      VARCHAR(3) DEFAULT 'USD'

positions
  id            BIGINT PK
  account_id    BIGINT FK -> accounts.id
  symbol        VARCHAR(20) NOT NULL
  quantity      DECIMAL(18,6) NOT NULL
  updated_at    TIMESTAMP
  UNIQUE (account_id, symbol)

tax_lots
  id            BIGINT PK
  position_id   BIGINT FK -> positions.id
  quantity      DECIMAL(18,6) NOT NULL
  cost_basis    DECIMAL(18,6) NOT NULL     -- per share
  acquired_at   TIMESTAMP
  lot_method    ENUM('fifo','lifo','specific')

transactions
  id            BIGINT PK
  account_id    BIGINT FK -> accounts.id
  symbol        VARCHAR(20) NOT NULL
  type          ENUM('buy','sell','dividend','split','transfer')
  quantity      DECIMAL(18,6)
  price         DECIMAL(18,6)
  fee           DECIMAL(10,4) DEFAULT 0
  transacted_at TIMESTAMP

prices
  symbol        VARCHAR(20) NOT NULL
  price         DECIMAL(18,6) NOT NULL
  source        VARCHAR(50)
  recorded_at   TIMESTAMP
  PRIMARY KEY (symbol, recorded_at)

targets
  id            BIGINT PK
  account_id    BIGINT FK -> accounts.id
  symbol        VARCHAR(20) NOT NULL
  target_pct    DECIMAL(7,4) NOT NULL
  tolerance_pct DECIMAL(7,4) DEFAULT 5.0

API Design

GET    /accounts/{accountId}/positions        Current positions with live value
GET    /accounts/{accountId}/performance      Total gain/loss summary
GET    /accounts/{accountId}/tax-lots         All open lots with unrealized gain
POST   /accounts/{accountId}/transactions     Record a buy or sell
GET    /accounts/{accountId}/rebalance        Drift from target allocations
PUT    /accounts/{accountId}/targets          Set or update target allocations
GET    /prices/{symbol}                       Latest and historical prices

Position and Value Calculation

-- Current market value per position
SELECT
  p.symbol,
  p.quantity,
  pr.price AS current_price,
  p.quantity * pr.price AS market_value
FROM positions p
JOIN LATERAL (
  SELECT price FROM prices
  WHERE symbol = p.symbol
  ORDER BY recorded_at DESC LIMIT 1
) pr ON true
WHERE p.account_id = :account_id;

-- Unrealized gain/loss per tax lot
SELECT
  tl.quantity,
  tl.cost_basis,
  pr.price AS current_price,
  (pr.price - tl.cost_basis) * tl.quantity AS unrealized_gain
FROM tax_lots tl
JOIN positions p ON tl.position_id = p.id
JOIN LATERAL (...) pr ON true
WHERE p.account_id = :account_id;

Price Feed Ingestion

A price ingestor service subscribes to market data providers (e.g., Polygon, IEX Cloud) via WebSocket for real-time quotes during market hours and via REST for end-of-day closes. Prices are written to the prices table and also cached in Redis with a short TTL for fast reads during portfolio valuation.

Redis key: price:{symbol}
Value:     {"price": 182.45, "ts": 1713360000}
TTL:       15 seconds during market hours, 3600 seconds after close

Tax Lot Accounting

Each buy transaction creates a new tax lot. When shares are sold the service selects which lots to close based on the user's chosen method:

• FIFO: consume oldest lots first
• LIFO: consume newest lots first
• Specific identification: user nominates lot IDs at sale time

Realized gain equals (sale price minus cost basis) times quantity sold. Lots are partially consumed when a sale quantity falls within a single lot.

-- FIFO lot selection for a sell of :sell_qty shares
SELECT id, quantity, cost_basis
FROM tax_lots
WHERE position_id = :position_id
ORDER BY acquired_at ASC
-- application iterates rows, deducting sell_qty until exhausted

Rebalancing Alerts

A scheduled job computes current allocation percentages and compares them against target percentages. When any position drifts beyond tolerance_pct, an alert is generated.

total_value = SUM(position.quantity * current_price) for all positions

for each position:
  current_pct = (position.quantity * current_price) / total_value * 100
  drift = ABS(current_pct - target.target_pct)
  if drift > target.tolerance_pct:
    create alert(account_id, symbol, current_pct, target.target_pct, drift)
    emit BUY or SELL suggestion to reach target_pct

Performance Calculation

Time-weighted return (TWR) is the standard for portfolio performance because it neutralizes the impact of cash flows. The service snapshots portfolio value daily after close and uses the chain-linking formula across sub-periods. For simpler needs, a simple return on cost basis is also exposed.

Scaling Considerations

• Pre-compute and cache portfolio snapshots nightly for historical performance charts
• Price reads go through Redis; prices table is used for history and audit only
• Partition prices table by month — it grows unboundedly
• Fan-out rebalancing checks as background jobs keyed by account_id to avoid one slow account blocking others
• Use event sourcing for transactions so the full lot history can always be reconstructed

Interview Tips

• Lead with the tax lot model — most candidates miss it and it immediately differentiates the design
• Clarify real-time vs. end-of-day requirements early; real-time needs WebSocket feeds and Redis, EOD is much simpler
• Explain why TWR is preferred over simple return for performance measurement — it shows domain knowledge
• Discuss account types (taxable vs. IRA) as they affect which tax lot methods are relevant

See also: Coinbase Interview Guide

See also: Stripe Interview Guide 2026: Process, Bug Bash Round, and Payment Systems

See also: Databricks Interview Guide 2026: Spark Internals, Delta Lake, and Lakehouse Architecture