SQL queries

/query runs read-only DuckDB SQL directly against the options snapshot. It’s the escape hatch for anything the chain and screener shapes can’t express: GROUP BY aggregations (gamma exposure, put/call ratios, OI distributions), window functions, joins between the tables, or ad-hoc analytics.

Calling it

// Total gamma exposure by strike for SPY
const gex = await cvApi.query(`
  SELECT strike_price,
         SUM(gamma * open_interest * shares_per_contract *
             CASE WHEN contract_type = 'call' THEN 1 ELSE -1 END) AS net_gamma
  FROM options_snapshots
  WHERE underlying_ticker = 'SPY'
  GROUP BY strike_price
  ORDER BY strike_price
`);

curl -X POST -H "Authorization: Bearer cv_live_..." \
  -H "Content-Type: application/json" \
  -d '{ "sql": "SELECT underlying_ticker, COUNT(*) AS contracts FROM options_snapshots GROUP BY 1 ORDER BY 2 DESC LIMIT 20" }' \
  https://tap.convexvalue.com/api/data/query

From a coding agent, the MCP tool is query_sql — see the MCP reference.

Request shape

Rows come back as JSON objects keyed by column name, with row_count, truncated, and elapsed_ms alongside:

{
  "rows": [
    { "underlying_ticker": "SPY", "contracts": 12488 },
    { "underlying_ticker": "SPX", "contracts": 11020 }
  ],
  "row_count": 2,
  "truncated": false,
  "elapsed_ms": 132
}

Tables

options_snapshots

One row per option contract — the entire live snapshot. Use the documented supported fields: text columns (underlying_ticker, ticker, contract_type, expiration_date, …), numeric columns (FMV, Greeks, session aggregates), and timestamps. Raw storage columns are not a promise of product availability: cvforge does not provide option bid/ask quotes, NBBO, or last trades. The table is indexed by underlying_ticker, by ticker, and by (underlying_ticker, expiration_date, strike_price, contract_type) — filter on those for the fastest queries.

option_symbols

Rules & limits

• SELECT / WITH only. DDL, DML, PRAGMA, and multiple statements are rejected with 400.
• 10,000-row ceiling. max_rows above the server cap is clamped; aggregate server-side rather than paginating raw contracts.
• The database is a snapshot. It’s swapped atomically about once a minute; there is no history in these tables — for time series use historical bars.
• DuckDB’s SQL dialect applies — date functions, window functions, QUALIFY, and list aggregates all work.

Recipes

SELECT underlying_ticker,
       SUM(CASE WHEN contract_type = 'put'  THEN day_volume ELSE 0 END) /
       NULLIF(SUM(CASE WHEN contract_type = 'call' THEN day_volume ELSE 0 END), 0) AS pc_ratio,
       SUM(day_volume) AS total_volume
FROM options_snapshots
GROUP BY underlying_ticker
HAVING total_volume > 10000
ORDER BY total_volume DESC
LIMIT 25

SELECT expiration_date,
       AVG(implied_volatility) FILTER (WHERE ABS(delta) BETWEEN 0.45 AND 0.55) AS atm_iv
FROM options_snapshots
WHERE underlying_ticker = 'AAPL'
GROUP BY expiration_date
ORDER BY expiration_date