DuckDB OLAP & Vectorized Window Functions

🦆 DuckDB OLAP & Vectorized Window Functions

Kryptofolio delegates heavy computational workloads — tax lot FIFO matching, time-weighted returns, and institutional risk metrics — to a local instance of DuckDB (fiscal.duckdb).

DuckDB operates as an in-process, columnar OLAP engine utilizing vectorized SIMD instructions, allowing complex multi-year queries across hundreds of thousands of rows to execute in single-digit milliseconds.

---

📊 Core Analytical Views Architecture

The analytical engine structures portfolio calculations into four foundational SQL views:

flowchart TD
    Ledger["ledger.spot_transactions (SQLite)"] --> Balances["v_daily_running_balances"]
    Prices["historical_prices (*.parquet)"] --> Balances

    Balances --> Val["v_portfolio_daily_valuation\n(Materialized into m_portfolio_daily_valuation)"]
    Val --> Vol["v_portfolio_returns_volatility\n(30d Annualized Volatility)"]
    Val --> Drawdown["v_portfolio_ath_drawdown\n(Rolling ATH & Drawdown Curve)"]
    Vol --> AlphaBeta["v_portfolio_alpha_beta\n(Benchmark Covariance vs BTC)"]

---

🔬 Mathematical Formulas & View Definitions

1. Daily Valuation (v_portfolio_daily_valuation)

Computes running cumulative balances for each asset across all accounts, projecting historical prices using ASOF join logic:

SELECT
    b.date,
    b.asset_id,
    b.cumulative_qty,
    p.price_eur,
    (b.cumulative_qty * p.price_eur) AS valuation_eur
FROM v_daily_running_balances b
ASOF JOIN historical_prices p
    ON b.asset_id = p.asset_id AND b.date >= p.price_timestamp;

2. 30-Day Annualized Volatility (v_portfolio_returns_volatility)

Calculates daily log returns of the entire portfolio and applies standard financial volatility scaling:

$$\text{Daily Return}t = \frac{\text{Value}t - \text{Value}{t-1}}{\text{Value}{t-1}}$$

$$\text{Volatility}{30d} = \sigma(\text{Daily Returns}{t-29 \dots t}) \times \sqrt{365}$$

In DuckDB SQL, this is computed over a sliding 30-day window:

stddev(daily_return) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) * sqrt(365) AS annualized_volatility_30d

3. Rolling ATH & Percentage Drawdown (v_portfolio_ath_drawdown)

Tracks the historical peak valuation of the portfolio and calculates the drawdown percentage:

$$\text{Rolling ATH}_t = \max(\text{Valuation}_0 \dots \text{Valuation}_t)$$

$$\text{Drawdown}_{%} = \frac{\text{Valuation}_t - \text{Rolling ATH}_t}{\text{Rolling ATH}_t}$$

4. Risk Alpha ($\alpha$) & Beta ($\beta$) (v_portfolio_alpha_beta)

Measures portfolio sensitivity and excess returns relative to the broader crypto benchmark (Bitcoin):

$$\beta = \frac{\text{Cov}(R_{\text{portfolio}}, R_{\text{btc}})}{\text{Var}(R_{\text{btc}})}$$

$$\alpha = \bar{R}{\text{portfolio}} - \beta \cdot \bar{R}{\text{btc}}$$

---

🧵 DuckDB Connection Pooling

DuckDB’s Node.js binding provides high throughput but requires disciplined concurrency management.

Kryptofolio wraps the engine in an internal pool (DUCKDB_POOL_SIZE, default 4):

• Multiple incoming HTTP read requests borrow pooled connection handles from a single DuckDBInstance.
• Analytical queries run concurrently across distinct worker threads without locking the SQLite database.