Portfolio valuation is deceptively complex. On the surface: multiply units by NAV, sum it up. In practice: corporate actions, FX adjustments, accrued income, XIRR calculations, and benchmark comparisons — all for thousands of clients, updated continuously.

The Naive Approach (And Why It Fails)

Version 1 was a nightly batch job. Every night at midnight, recalculate every client's portfolio value and store the result. This worked at 500 clients. At 5,000, it took 4 hours. At 50,000, it wasn't going to work.

The Event-Sourcing Approach

We rebuilt around an event-sourcing model. The source of truth is a transaction ledger — an append-only log of every buy, sell, dividend, and SIP instalment. Portfolio state is derived, not stored.

-- Holdings derived from events (not stored)
CREATE MATERIALIZED VIEW current_holdings AS
SELECT 
  client_id,
  fund_id,
  SUM(CASE WHEN type = 'buy' THEN units ELSE -units END) AS units
FROM transaction_ledger
WHERE date <= CURRENT_DATE
GROUP BY 1, 2
HAVING SUM(...) > 0;

The materialized view is refreshed incrementally when new NAVs arrive (3-4 times per day from AMFI). For real-time client-facing queries, we use a Redis cache populated from the view.

The XIRR Problem

XIRR (Extended Internal Rate of Return) can't be done purely in SQL — it requires an iterative numerical solver. We handle this in Python using a scipy.optimize-based solver that runs on demand, cached aggressively since the inputs (transactions + current NAV) change infrequently.

The best database design is the one that makes wrong states unrepresentable. An event log where you can't have a negative holding is better than a holdings table that requires application code to enforce the same constraint.

After the redesign, portfolio page load dropped from 4.2s to 320ms for 95th percentile clients. The database team also became considerably less anxious about the monthly batch window.