Data Dictionary

This document describes the core persisted data structures used by the Vol Dashboard. It focuses on fields that matter for analytics, sync diagnostics, and operator interpretation.

`snapshots`

Metadata table for snapshot runs.

Field	Meaning
`id`	Internal snapshot ID.
`currency`	Dashboard currency, e.g. `BTC`, `ETH`, `SOL_USDC`.
`snapshot_date`	UTC date captured.
`snapshot_type`	Type such as `gex`, `iv_surface`, `vol_metrics`, `spot`, `oi`.
`record_count`	Number of records captured for that snapshot type.
`created_at`	Insert/update timestamp.

Uniqueness:

(currency, snapshot_date, snapshot_type)

`gex_snapshots`

Per-instrument dealer gamma rows from Amberdata gamma exposure snapshots.

Field	Meaning
`snapshot_id`	FK to `snapshots`.
`timestamp`	Source snapshot timestamp.
`strike`	Option strike.
`expiry`	Option expiry.
`dealer_gamma`	Source dealer net inventory / gamma exposure measure.
`spot`	Source index price where available.

Used for:

Gamma profile evolution.
Magnet levels.
Strike-level gamma concentration.

`gex_hourly`

Hourly aggregate table for GEX and spot history.

Field	Meaning
`currency`	Dashboard currency.
`timestamp`	Hourly timestamp bucket.
`net_gex`	Sum of dealer net inventory across instruments.
`abs_gex`	Sum of absolute dealer net inventory across instruments.
`gex_imbalance`	`net_gex / abs_gex`.
`spot`	Best available spot/index price for the bucket.
`instruments`	Count of instruments included.

Used for:

GEX time series.
Conditional returns.
Gamma regimes.
Probability conditioning.
Realized vol and trade P&L support.

`iv_surface_snapshots`

Persisted implied volatility surface points.

Field	Meaning
`snapshot_id`	FK to `snapshots`.
`timestamp`	Surface observation timestamp.
`dte`	Days to expiry.
`delta`	Option delta bucket, often stored as whole-number delta.
`iv`	Implied volatility.
`forward`	Forward price where source provides it.
`strike`	Strike where source provides it.
`source_underlying_price`	Source underlying/reference price synchronized to the surface row when available.
`source_index_price`	Source index price synchronized to the surface row when available.

Used for:

Risk-neutral density.
Touch probability.
Conditional density.
Smile tracking.
Vol heatmap.

Probability calculations must read one coherent (timestamp, dte) surface at a time. Daily snapshots can contain many hourly surfaces; mixing those rows creates invalid density shapes.

`vol_metrics_snapshots`

Scalar volatility and positioning metrics.

Field	Meaning
`dvol`	Deribit-style 30-day implied volatility index.
`vrp`	Volatility risk premium if source/proxy provides it.
`iv_atm_7d`	7-day ATM implied volatility.
`iv_atm_30d`	30-day ATM implied volatility.
`rv_7d`	7-day realized volatility.
`rv_30d`	30-day realized volatility.
`vol_of_vol`	Volatility of implied volatility metric.
`skew_25d`	25-delta skew.
`put_call_ratio`	Put/call volume ratio.
`total_oi`	Total open interest.
`spot`	Spot/reference price if available.

Used for:

Overview metrics.
Regime stationarity.
Alert signals.
Spot-vol analytics.

`spot_snapshots`

Point-in-time spot/reference price snapshots.

Field	Meaning
`currency` via snapshot	Dashboard currency.
`timestamp`	Observation timestamp.
`price`	Spot/reference price.
`source`	Source label, e.g. `amberdata` or `gex_derived`.

Used for historical context and fallback validation.

`oi_snapshots`

Open interest snapshots by exchange.

Field	Meaning
`timestamp`	Observation timestamp.
`exchange`	Source exchange.
`oi_value`	Open interest value.
`oi_notional`	Notional open interest where available.

Used for liquidity regime and futures/market-structure views.

`internal_trades`

Alpha-synced trade rows. The production operator workflow uses rows loaded from the Alpha feed only. Earlier development paths supported manual entries, CSV uploads, and mock rows, but those are not part of the active /trades UI.

Base fields:

Field	Meaning
`currency`	Underlying or dashboard currency. For Alpha rows this can be equity underlyings such as `MSTR` or `COIN`.
`trade_date`	Trade execution date/time.
`instrument`	Human-readable instrument.
`direction`	`buy` or `sell`.
`option_type`	`call` or `put`.
`strike`	Option strike.
`expiry`	Option expiry.
`quantity`	Contract count unless `quantity_unit` says otherwise.
`traded_iv`	Trade implied volatility, if known.
`price`	Per-contract price in `price_currency`.
`premium_usd`	Total premium in USD.
`spot_at_trade`	Spot at trade, if known.
`realized_vol`	Computed realized vol after trade, if available.
`pnl`	Computed P&L, if available and trustworthy.
`source`	Production rows should be `alpha_sync`; legacy/manual/test values may exist from earlier phases.
`notes`	Free-form notes.

Alpha metadata fields:

Field	Meaning
`source_trade_id`	Alpha source trade ID. Used for idempotent upsert.
`source_trade_ticket_id`	Source ticket ID, if available.
`source_updated_at`	Source-system update timestamp.
`source_created_at`	Source-system creation timestamp, if available.
`source_portfolio`	Alpha portfolio, e.g. `IMST` or `ICOI`.
`source_portfolio_id`	Source portfolio ID, if available.
`source_security_id`	Source security ID, if available.
`underlying_symbol`	Underlying symbol.
`underlying_type`	Underlying type, if available.
`option_symbol`	Option symbol, if available.
`trade_status`	Source trade status, e.g. `FILLED`.
`recon_status`	Source-provided Alpha reconciliation status.
`contract_multiplier`	Source multiplier.
`price_currency`	Currency of per-contract price.
`quantity_unit`	Quantity unit, e.g. `CONTRACTS`.
`pending_quantity`	Pending quantity, if included.
`fees_usd`	Total fees in USD.
`source_broker`	Source broker, if available.
`source_execution_type`	Source execution type, if available.
`source_created_by`	Source creator, if available.
`is_synthetic`	Synthetic trade flag, if available.

Trade-performance enrichment should resolve Alpha underlyings through back-end/market_proxy.py before joining to dashboard market context. Current production proxy mappings are:

Alpha underlying	Market proxy	Quality state
`MSTR`	`BTC`	`proxy_mapped`
`COIN`	`BTC`	`proxy_mapped`
`BTC`, `ETH`, `SOL`	Same dashboard market key	`available`

Unmapped symbols should remain explicit unmapped states. They must not be treated as BTC by default.

Future derivative-equivalent identity fields are specified in Derivative Identity And Proxy Mapping. That contract separates traded contract identity from market/regime proxy context, so BTC proxy context for MSTR/COIN does not become MSTR/COIN execution marks, liquidity, or P&L authority.

Preparatory future data contracts are specified in Data Ingestion Readiness Plan for durable job and coverage-reason outputs, Regime Model Specification for model provenance/trust outputs, Trade Copilot Evaluation Specification for warning and candidate-evaluation outputs, and SMA Ingestion Requirements for future SMA source fields.

Uniqueness:

(source, source_trade_id)

where source_trade_id IS NOT NULL.

Derived Position Lifecycle API

The position lifecycle view is not a persisted table. It is derived from internal_trades by:

GET /api/internal/trades/positions?source=alpha_sync

Grouping keys:

Key	Meaning
`source_portfolio`	Alpha portfolio. Missing portfolio is flagged.
`underlying_symbol`	Actual traded underlying, e.g. `MSTR` or `COIN`.
`contract_key`	`option_symbol` when available, otherwise `instrument`.
`option_type`	`call` or `put`.
`strike`	Option strike.
`expiry`	Option expiry.

Important output fields:

Field	Meaning
`position_id`	Stable hash of the grouping key for frontend drilldowns.
`trade_ids` / `source_trade_ids`	Constituent execution identifiers.
`total_buy_quantity`, `total_sell_quantity`, `net_quantity`	Direction-aware contract aggregation.
`closed_quantity`	Quantity matched against opposite-side executions using FIFO.
`average_open_price`	Remaining open-lot average price weighted by quantity and contract multiplier.
`open_premium_usd`	Price-derived premium of remaining open lots when all open prices are available.
`realized_pnl_before_fees_usd`	Derived same-contract FIFO cashflow before fees; used for internal investigation only until validated against authoritative realized P&L.
`total_gross_premium_usd`	Sum of all execution premiums only when every row has a defensible USD premium.
`known_gross_premium_usd`	Sum of available USD premiums even when premium coverage is partial.
`net_premium_cashflow_usd`	Direction-aware premium cashflow only when premium coverage is complete. Buys are negative, sells are positive.
`known_net_premium_cashflow_usd`	Direction-aware cashflow for available premium rows only.
`total_fees_usd` / `known_fees_usd`	Complete-fee total vs known-fee subset.
`premium_quality`, `fees_quality`	`complete`, `partial`, or `missing`.
`lifecycle_state`	`closed`, `open_long`, or `open_short`.
`lifecycle_quality`	`clean`, `open`, `incomplete_economics`, or `ambiguous`.
`lifecycle_flags`, `missing_fields`	Explicit caveats such as `flipped`, `missing_contract_key`, or `incomplete_price`.

Position lifecycle analytics must use lifecycle_quality and quality-specific totals. Missing premium, price, fee, or close data must not be interpreted as zero.

Derived Opportunity Alignment API

The opportunity alignment view is not persisted. It joins internal_trades to trade_market_context and scores signal consistency through:

GET /api/internal/trades/opportunity-alignment?source=alpha_sync

Per-trade output:

Field	Meaning
`strategy_intent`	Always `unknown` until an explicit strategy-tagging workflow exists.
`assumed_strategy`	Direction-based assumption: buys are `long_vol`, sells are `short_vol`.
`alignment_score`	0-100 signal-consistency score, or null when context is unavailable/unmapped.
`alignment_label`	`aligned`, `partially_aligned`, `mixed`, `weak_alignment`, `misaligned`, or `insufficient_data`.
`drivers`	Supportive signal explanations with numeric impacts.
`detractors`	Contradicting signal explanations with numeric impacts.
`confidence`	0-1 confidence after data-quality, proxy-mapping, stationarity, and signal-coverage penalties.
`confidence_label`	`high`, `medium`, or `low`.
`observed_signal_count` / `missing_signals`	Signal coverage.
`warning`	Explicit reminder that alignment is not realized P&L.

Aggregate output:

Field	Meaning
`by_portfolio`	Alignment summary per Alpha portfolio.
`by_underlying`	Alignment summary per traded underlying.
`by_regime`	Alignment summary per gamma regime.
`by_month`	Alignment summary by execution month.
`by_signal`	Driver and detractor frequency counts.

Do not interpret high alignment as a profitable trade. It only means the execution was consistent with the dashboard's available opportunity signals at the time.

Derived Outcome Attribution API

Outcome attribution is derived from internal_trades, trade_market_context, opportunity alignment, and sourced forward marks:

GET /api/internal/trades/outcomes?source=alpha_sync&horizons=1,7,30

Per-horizon output:

Field	Meaning
`horizon_key`	Horizon label such as `1d`, `7d`, `30d`, or `expiry`.
`target_timestamp`	Intended measurement timestamp.
`mark_timestamp` / `mark_time_delta_minutes`	Source quote timestamp and distance from target, or from expiry for terminal expiry marks.
`mark_price`, `mark_iv`	Fresh sourced forward mark values only.
`mark_quality`	`quoted`, `stale`, or `unavailable`.
`outcome_state`	`unrealized_mark`, `terminal_mark`, `pending`, or `unavailable`.
`entry_premium_usd`	Total entry premium; provided by Alpha or derived from USD price, quantity, and multiplier.
`mark_premium_usd`	`mark_price * quantity * contract_multiplier` when mark is fresh.
`pnl_before_fees_usd`	Direction-aware mark-to-entry P&L before fees.
`pnl_after_fees_usd`	P&L after fees when fee data is available.
`realized_vol_after_trade`	Realized vol over the horizon when enough spot observations exist.
`spot_move_pct`	Spot move from entry to horizon when both spot points are sourced.
`moneyness_at_entry`, `moneyness_at_horizon`	Spot/strike ratios when spot and strike are available.
`missing_fields`	Explicit unavailable inputs.

Aggregate output summarizes horizon outcomes by portfolio, underlying, gamma regime, opportunity-alignment label, and DVol bucket. No missing outcome should be displayed as zero.

`trade_forward_outcome_marks`

Persisted sourced forward marks and horizon P&L for Alpha-synced trades. This table is populated by:

POST /api/internal/trades/outcomes/backfill

Uniqueness:

(source, source_trade_id, horizon_key)

Important fields:

Field	Meaning
`horizon_key` / `horizon_days`	Measurement horizon such as `1d`, `7d`, or `30d`.
`target_timestamp`	Calendar-day UTC target timestamp, calculated as `trade_date + horizon_days`.
`mark_timestamp` / `mark_time_delta_minutes`	Source quote timestamp and distance from target, or from expiry for terminal expiry marks.
`mark_price`, `mark_iv`	Fresh sourced mark values only; stale or missing marks remain null for displayed economics.
`mark_source`	Source label, currently `amberdata_tradfi_level1` for live horizon quotes, `amberdata_tradfi_level1_chunked_recovery` for widened search, `amberdata_tradfi_level1_expiry_quote` for terminal expiry quotes, or adjusted-root fallback labels when explicitly enabled.
`mark_lookup_error`	Lookup error text when the source call failed and the row was persisted as unavailable.
`mark_quality`	`quoted`, `stale`, or `unavailable`.
`outcome_state`	`unrealized_mark`, `terminal_mark`, `pending`, or `unavailable`.
`entry_premium_usd`, `mark_premium_usd`	Entry and horizon mark premium in USD when available.
`pnl_before_fees_usd`, `pnl_after_fees_usd`	Direction-aware P&L, populated only when a fresh sourced mark and entry economics are available.
`missing_fields`	Explicit reasons the row could not produce a sourced P&L.

Pending rows must be re-backfilled after the horizon matures. If a persisted pending row is past its target timestamp, API reads treat it as needing a new mark rather than as valid P&L.

`internal_trade_sync_state`

State table for external trade-feed syncs.

Field	Meaning
`source_name`	Source state label, currently `alpha_trade_feed`.
`scope_key`	Portfolio/pending scope key.
`scope_details`	JSON details of portfolios and pending flag.
`last_successful_cursor`	Cursor from latest successful sync.
`last_successful_watermark`	Latest source update timestamp processed.
`last_successful_sync_at`	Time the dashboard last completed a real sync.
`last_error`	Last sync error, if any.
`last_error_at`	Time of last sync error.
`last_run_summary`	JSON summary of the last run.

Scope key format:

portfolio_names=<sorted portfolio list>|include_pending=<true|false>

`trade_market_context`

Persisted trade-time market/regime context for Alpha-synced trades. This table is populated by the admin backfill endpoint:

POST /api/internal/trades/context/backfill

Uniqueness:

(source, source_trade_id, context_key)

where context_key is the dashboard market proxy such as BTC, or an explicit unmapped:<symbol> key for unmapped underlyings.

Captured fields:

Field	Meaning
`internal_trade_id`	Local `internal_trades.id` if available.
`source` / `source_trade_id`	Source identity, currently Alpha sync rows.
`context_key`	Unique context scope key for the source trade.
`quote_underlying`	Actual traded underlying, e.g. `MSTR` or `COIN`.
`market_proxy`	Dashboard market context key, e.g. `BTC`; null when unmapped.
`mapping_method`	Mapping method such as `direct_market_symbol` or `crypto_equity_proxy`.
`mapping_confidence`	Numeric confidence for the mapping decision.
`mapping_quality`	`available`, `proxy_mapped`, or `unmapped`.
`spot_at_trade`	Nearest valid dashboard spot around trade time.
`spot_source`	Source of the spot, currently `gex_hourly` or `vol_metrics_snapshots`.
`spot_time_delta_minutes`	Absolute time distance between trade and spot observation.
`dvol`, `iv_30d`, `rv_30d`, `vrp`	Nearest volatility context where available.
`dvol_percentile`	Percentile rank of DVol over the configured lookback.
`gamma_regime`, `gex_imbalance`	Nearest GEX context and regime bucket.
`stationarity_status`, `stationarity_blend_weight`	Stationarity confidence context where available.
`smile_rich_cheap`	Intentionally null for Alpha MSTR/COIN trade rows until MSTR/COIN smile data or an approved BTC-proxy smile method exists.
`term_structure_state`	Nearest term-structure context where available.
`context_quality`	Overall quality state: `available`, `proxy_mapped`, `stale`, `unavailable`, or `unmapped`.
`missing_fields`	Explicit list of fields that could not be populated.

Fields such as 24h volume and some open-interest coverage remain missing until later tickets add defensible trade-time sources. Smile is a deliberate not_sourced trade-level state, not a broken source path.

`trade_execution_benchmarks`

Persisted execution-quality benchmarks for Alpha-synced trades. This table is populated by:

POST /api/internal/trades/execution-benchmarks/backfill

Uniqueness:

(source, source_trade_id)

Important fields:

Field	Meaning
`fill_price`	Alpha fill price per contract/source unit.
`benchmark_bid`, `benchmark_ask`, `benchmark_mid`	Fresh sourced quote benchmark values only. These remain null when quote data is missing or stale.
`benchmark_bid_size`, `benchmark_ask_size`	Provider-sourced quote size fields when returned with the traded-contract quote.
`benchmark_mark`	Sourced quote mark or explicitly estimated model/surface mark.
`benchmark_iv`	Sourced quote/model IV associated with the benchmark mark.
`contract_volume_24h`	Traded-contract 24h volume only when the quote provider returns an explicit 24h volume field. Missing is null, not zero.
`contract_open_interest`	Traded-contract open interest only when the quote provider returns an explicit open-interest field. Missing is null, not zero.
`fill_vs_mid`	Direction-aware slippage in price points. Buy: `fill - mid`; sell: `mid - fill`. Positive is worse than mid.
`fill_vs_mid_usd`	`fill_vs_mid * quantity * contract_multiplier`.
`fill_vs_mid_bps`	`fill_vs_mid / benchmark_mid * 10000`.
`spread_capture`	Buy: `(ask - fill) / (ask - bid)`; sell: `(fill - bid) / (ask - bid)`.
`fill_vs_surface_mark`	Direction-aware fill versus benchmark mark.
`benchmark_source`	Source label such as `amberdata_tradfi_level1` or `surface_model`.
`benchmark_quality`	`quoted`, `stale`, `estimated`, or `unavailable`.
`execution_liquidity_quality`	`available`, `partial`, `stale`, `unavailable`, `not_entitled`, or `not_sourced` for traded-contract liquidity coverage.
`missing_fields`	Explicit list of unavailable benchmark fields.

Model or surface estimates must never populate benchmark_bid, benchmark_ask, or benchmark_mid.