Crypto Portfolio Risk Management with Streaming SQL

Real-time crypto portfolio risk management means knowing your VaR, current drawdown, and margin exposure the moment prices change — not after the next batch job runs. With RisingWave, a PostgreSQL-compatible streaming database, you can build continuously updated risk views over live price and position data using SQL materialized views, enabling risk systems and portfolio managers to respond to changing conditions in seconds.

Why Portfolio Risk Management Matters in Crypto

Crypto portfolios face risk conditions that make traditional financial risk management approaches look slow. Volatility that takes equity markets months to produce can arrive in crypto within hours. A 20% drawdown in a correlated multi-asset portfolio can trigger margin calls across multiple venues simultaneously.

Key risk metrics that require real-time tracking:

• Value at Risk (VaR): The maximum expected loss at a given confidence level over a time horizon — must be recalculated as positions and prices change
• Drawdown: The decline from a portfolio's peak value — helps assess whether current losses exceed historical norms
• Sharpe Ratio: Risk-adjusted return metric — degrades rapidly when volatility spikes unexpectedly
• Correlation: Cross-asset correlation changes during market stress, invalidating diversification assumptions built on calm-market data
• Margin utilization: Leveraged positions can approach margin call thresholds within minutes of a sharp move

Traditional batch risk systems run overnight or hourly. In crypto, "hourly" is ancient history when markets move this fast.

How Streaming SQL Solves This

RisingWave maintains continuously updated risk metrics by joining live price data with position snapshots. As prices arrive from a market data feed, materialized views recompute portfolio valuations, P&L, and risk metrics — incremental updates mean only affected positions are recalculated on each price tick. The result is a real-time risk dashboard queryable with standard SQL.

Building It Step by Step

Step 1: Connect the Data Source

-- Live price feed from market data aggregator
CREATE SOURCE price_feed (
    exchange        VARCHAR,
    trading_pair    VARCHAR,
    asset           VARCHAR,
    price_usd       NUMERIC,
    bid             NUMERIC,
    ask             NUMERIC,
    event_time      TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'crypto.market.prices',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Portfolio positions from trading system (CDC from PostgreSQL)
CREATE SOURCE portfolio_positions (
    account_id      VARCHAR,
    asset           VARCHAR,
    quantity        NUMERIC,      -- positive = long, negative = short
    avg_entry_price NUMERIC,
    margin_used_usd NUMERIC,
    leverage        NUMERIC,
    position_time   TIMESTAMPTZ,
    op              VARCHAR       -- 'insert', 'update', 'delete'
) WITH (
    connector = 'postgres-cdc',
    hostname = 'postgres',
    port = '5432',
    username = 'replicator',
    password = 'secret',
    database.name = 'trading_db',
    schema.name = 'public',
    table.name = 'positions'
);

Step 2: Build the Core Materialized View

-- Current best price per asset (latest across all exchanges)
CREATE MATERIALIZED VIEW latest_prices AS
SELECT DISTINCT ON (asset)
    asset,
    price_usd,
    bid,
    ask,
    event_time
FROM price_feed
ORDER BY asset, event_time DESC;

-- Live portfolio valuation with unrealized P&L
CREATE MATERIALIZED VIEW portfolio_pnl AS
SELECT
    p.account_id,
    p.asset,
    p.quantity,
    p.avg_entry_price,
    lp.price_usd                                    AS current_price,
    p.quantity * lp.price_usd                       AS market_value_usd,
    p.quantity * (lp.price_usd - p.avg_entry_price) AS unrealized_pnl,
    (lp.price_usd - p.avg_entry_price)
        / NULLIF(p.avg_entry_price, 0) * 100        AS pnl_pct,
    p.margin_used_usd,
    p.leverage,
    lp.event_time                                   AS price_as_of
FROM portfolio_positions p
JOIN latest_prices lp ON p.asset = lp.asset;

-- Account-level risk summary
CREATE MATERIALIZED VIEW account_risk_summary AS
SELECT
    account_id,
    SUM(market_value_usd) FILTER (WHERE quantity > 0) AS long_exposure_usd,
    SUM(ABS(market_value_usd)) FILTER (WHERE quantity < 0) AS short_exposure_usd,
    SUM(market_value_usd)                              AS net_exposure_usd,
    SUM(unrealized_pnl)                                AS total_unrealized_pnl,
    SUM(margin_used_usd)                               AS total_margin_used,
    SUM(ABS(market_value_usd))
        / NULLIF(SUM(margin_used_usd), 0)              AS leverage_ratio,
    COUNT(DISTINCT asset)                              AS position_count,
    MAX(price_as_of)                                   AS last_updated
FROM portfolio_pnl
GROUP BY account_id;

-- Rolling 24-hour return volatility (for VaR approximation)
CREATE MATERIALIZED VIEW asset_volatility_24h AS
SELECT
    asset,
    window_start,
    window_end,
    STDDEV(price_usd)                        AS price_stddev,
    AVG(price_usd)                           AS avg_price,
    STDDEV(price_usd) / NULLIF(AVG(price_usd), 0) AS coefficient_of_variation,
    MAX(price_usd) - MIN(price_usd)          AS price_range,
    COUNT(*)                                 AS sample_count
FROM TUMBLE(price_feed, event_time, INTERVAL '1 HOUR')
GROUP BY asset, window_start, window_end;

Step 3: Add Alerts and Detection Logic

-- Margin utilization alerts: positions consuming >80% of available margin
CREATE MATERIALIZED VIEW margin_alerts AS
SELECT
    rs.account_id,
    rs.leverage_ratio,
    rs.total_margin_used,
    rs.net_exposure_usd,
    rs.total_unrealized_pnl,
    rs.last_updated,
    CASE
        WHEN rs.leverage_ratio >= 10 THEN 'MARGIN_CALL_RISK'
        WHEN rs.leverage_ratio >= 8  THEN 'HIGH_LEVERAGE'
        ELSE                              'ELEVATED_LEVERAGE'
    END AS alert_level
FROM account_risk_summary rs
WHERE rs.leverage_ratio >= 6;

-- Large drawdown alert: position down more than 20%
CREATE MATERIALIZED VIEW drawdown_alerts AS
SELECT
    account_id,
    asset,
    quantity,
    avg_entry_price,
    current_price,
    market_value_usd,
    unrealized_pnl,
    pnl_pct,
    price_as_of
FROM portfolio_pnl
WHERE pnl_pct < -20;

-- Sink risk alerts to Kafka
CREATE SINK risk_alerts_sink AS
SELECT account_id, leverage_ratio, alert_level, last_updated, 'MARGIN' AS risk_type
FROM margin_alerts
UNION ALL
SELECT account_id, pnl_pct AS leverage_ratio, 'DRAWDOWN' AS alert_level,
       price_as_of AS last_updated, 'DRAWDOWN' AS risk_type
FROM drawdown_alerts
WITH (
    connector = 'kafka',
    topic = 'portfolio.alerts.risk',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Step 4: Query for Real-Time Insights

-- Full portfolio snapshot for an account
SELECT
    asset,
    quantity,
    ROUND(avg_entry_price, 4)   AS entry_price,
    ROUND(current_price, 4)     AS current_price,
    ROUND(market_value_usd, 2)  AS market_value_usd,
    ROUND(unrealized_pnl, 2)    AS unrealized_pnl,
    ROUND(pnl_pct, 2)           AS pnl_pct,
    price_as_of
FROM portfolio_pnl
WHERE account_id = 'ACC_001'
ORDER BY ABS(market_value_usd) DESC;

-- Risk summary across all accounts
SELECT
    account_id,
    ROUND(net_exposure_usd, 0)      AS net_exposure,
    ROUND(total_unrealized_pnl, 0)  AS unrealized_pnl,
    ROUND(leverage_ratio, 2)         AS leverage,
    position_count,
    last_updated
FROM account_risk_summary
ORDER BY leverage_ratio DESC
LIMIT 20;

Comparison: Batch vs Streaming

FAQ

Q: How accurate is the VaR calculation using this streaming approach?

The materialized view approach supports historical simulation VaR by maintaining rolling price return distributions. The asset_volatility_24h view provides inputs for a parametric VaR model (assuming normal distribution). For more sophisticated historical simulation VaR, maintain a rolling window of hourly price returns per asset and apply the desired confidence level percentile. The accuracy depends on the return distribution assumption and window length — the streaming approach ensures the inputs are always current.

Q: How does the position source handle leverage and derivatives?

The postgres-cdc source reads positions exactly as stored in your trading system. If positions include futures contracts, options, or perpetuals, extend the schema with fields for notional value, delta, and expiry. The risk summary views then aggregate by notional exposure rather than market value for derivatives. The pattern is the same — the streaming join with live prices handles the continuous revaluation.

Q: Can correlation between assets be tracked in real time?

Yes. Build a materialized view that computes rolling hourly returns for each asset, then join assets pairwise to compute correlation coefficients over a rolling window. This is computationally intensive but tractable for portfolios up to a few hundred assets. For larger portfolios, compute correlation daily and use the streaming layer for position valuation and margin monitoring where sub-second latency matters most.

Key Takeaways

• CDC from PostgreSQL feeds position data while Kafka delivers live prices — both sources join seamlessly in RisingWave
• The portfolio_pnl view continuously reprices every position as new price ticks arrive
• Account-level risk summaries aggregate leverage, exposure, and P&L without scheduled batch jobs
• Margin and drawdown alert materialized views trigger Kafka sink events within milliseconds of threshold breaches
• The full risk stack uses standard SQL — the same queries work against historical data and live streams

Ready to try this? Get started with RisingWave. Join our Slack community.