# Data dictionary

## Coverage

Masscrest covers US-listed single stocks and ETFs. OTC-traded names and index products (SPX, VIX, and similar) are out of scope.

For every listed-option contract in scope, Masscrest tracks buy, sell and net flows across three investor cohorts:

| Cohort | Description |
|---|---|
| Institutional | Long-only, long/short, market-neutral, long-biased, multi-strat and other equity hedge funds; mutual funds, sovereign and endowment funds, family offices and other asset managers; proprietary trading desks, banks and dealers trading on behalf of customers. |
| Retail | Global self-directed retail traders (not investment advisors or private-banking clients). |
| Interdealer / market maker | ~99% of trades take place against a market maker, so this cohort sits opposite to Institutional and Retail flows. Interdealer trades are between dealers, typically to offset greek exposure. |

## What we provide

Three product surfaces sit on top of the same underlying inference layer:

1. **Option flow by contract** — buy, sell, and net trading imbalance for every listed contract, expressed in shares, premium, and greek-adjusted notional (delta, gamma, vega). Query with contract-level filters (`dte`, `abs_delta`, `callput`). Tables: [`option_flows_01_day`](/documentation/dictionary/option-flows-01-day) and [`option_flows_10_min`](/documentation/dictionary/option-flows-10-min).
2. **Option flow by underlying ticker** — the same trading imbalance aggregated to the underlying root symbol × callput. Pre-aggregated at ingest; use when you don't need contract-level filters. Available at the underlying grain (daily + 10-min) and rolled up to sector / industry / ETF / ADR / single-stock / all cohorts (daily only). Tables: [`underlying_option_flows_01_day`](/documentation/dictionary/underlying-option-flows-01-day), [`underlying_option_flows_10_min`](/documentation/dictionary/underlying-option-flows-10-min), and [`grouped_flow_signal_01_day`](/documentation/dictionary/grouped-flow-signal-01-day) (the grouped table carries the raw flow alongside the z-scored signal — see below).
3. **Signal** — proprietary indicator of abnormal net options positioning (delta, gamma, vega exposure) driven by institutional investors on the underlying. Historically correlated with subsequent excess returns; used to surface conviction at the ticker, industry, or sector level. Tables: [`underlying_flow_signal_01_day`](/documentation/dictionary/underlying-flow-signal-01-day) (per underlying) and [`grouped_flow_signal_01_day`](/documentation/dictionary/grouped-flow-signal-01-day) (aggregated to sector / industry / ETF / ADR / single-stock / all).

| Surface | Grain | Daily | 10-min |
|---|---|---|---|
| Option flow by contract | `(date, figi, callput, strike, expiration)` | ✓ | ✓ |
| Option flow by underlying ticker | `(date, figi, callput)` | ✓ | ✓ |
| Option flow — grouped (sector / industry / …) | `(date, group_key, callput)` | ✓ | — |
| Signal — per underlying | `(date, figi, callput)` | ✓ | — |
| Signal — grouped (sector / industry / …) | `(date, group_key, callput)` | ✓ | — |

**Typical uses**

- **Follow institutional flow.** See where hedge funds and asset managers are building or unwinding positions.
- **Spot retail-vs-institutional divergences.** Identify names where the two cohorts sit on opposite sides.
- **Trade the optionality dimension.** Separate directional bets from vega and gamma positioning; know whether a move is premium-driven or forced by dealer hedging.
- **Layer market narrative on price action.** Explain why a name moved when the tape alone doesn't tell you: accumulation ahead of a break, capitulation into a bottom.
- **Control for flow in factor models.** A signal orthogonal to price and volume. Wire it in as a regression control alongside conventional risk factors.

## Delivery & availability

Daily datasets are keyed on US-equity trading dates and delivered one calendar day later (Friday's data lands Saturday). Real-time notification is available via webhook; a polling fallback is documented below.

### Cadence

- Frequency: T+1 — data for trading day T is delivered on calendar day T+1 (Friday's data lands Saturday). Data only exists for US equity trading days; delivery cadence itself is calendar-day, not trading-day.
- Publish window: 05:00 – 09:00 UTC on the calendar day after the trading date.

### Webhook (recommended)

Register an HTTPS endpoint via `POST /v1/webhooks` (returns HTTP 201) with your API key — no admin gesture required, no ticket to open. When each day's data lands, we push a signed JSON payload. Wire the delivery handler to your flat-files sync job and the day's parquets pull automatically the moment they're published, without polling the manifest.

```json
{
  "event_type": "daily_options_flow.available",
  "trading_date": "2026-07-24",
  "published_at": "2026-07-25T04:37:12Z",
  "workflow_run_id": "aa1cb0c2-5b47-46f1-9c9a-8c9d1c2b0e33"
}
```

Field semantics:

- `trading_date`: the US equity session the data covers, in `YYYY-MM-DD` (UTC-derived, but represents the exchange trading session, not the write time).
- `published_at`: ISO-8601 UTC timestamp of when the notification was dispatched. Data is fully written before this fires, so it is safe to start reading immediately.
- `workflow_run_id`: unique id for the publishing run. Use as your idempotency key: dedupe on this if you receive the same message twice.
- `event_type`: always `daily_options_flow.available` today. See "Supported events" below.

### Verifying the signature

Every request carries two headers:

- `X-Masscrest-Timestamp: <unix-seconds>`
- `X-Masscrest-Signature: sha256=<hex>`

Compute HMAC-SHA256 over `{timestamp}.{raw_body}` using your webhook secret. Reject requests where the timestamp is more than 300 seconds from your clock to block replay attacks.

The `secret` returned at registration is a 64-character hex string. Decode it to bytes before using it as the HMAC key; UTF-8-encoding the hex string produces the wrong signature.

Python:

```python
import hmac, hashlib, time

def verify(secret_hex: str, timestamp: str, raw_body: bytes, received_sig: str) -> bool:
    # Reject stale requests (replay protection).
    if abs(int(time.time()) - int(timestamp)) > 300:
        return False
    key = bytes.fromhex(secret_hex)                                # NOT secret_hex.encode()
    expected = hmac.new(key, f"{timestamp}.".encode() + raw_body, hashlib.sha256).hexdigest()
    # Signature header format: "sha256=<hex>[,sha256=<hex>]" (comma-separated during rotation grace)
    for sig in received_sig.split(","):
        if hmac.compare_digest(f"sha256={expected}", sig.strip()):
            return True
    return False
```

Node.js:

```js
const crypto = require("crypto");

function verify(secretHex, timestamp, rawBody, receivedSig) {
  if (Math.abs(Math.floor(Date.now() / 1000) - Number(timestamp)) > 300) return false;
  const key = Buffer.from(secretHex, "hex");                       // NOT Buffer.from(secretHex)
  const expected = crypto
    .createHmac("sha256", key)
    .update(`${timestamp}.`)
    .update(rawBody)
    .digest("hex");
  // Header may contain two signatures separated by "," during the 24h rotation grace window.
  return receivedSig.split(",").some((sig) =>
    crypto.timingSafeEqual(Buffer.from(`sha256=${expected}`), Buffer.from(sig.trim()))
  );
}
```

### Retry policy

Retry on any non-2xx response or timeout after 10 seconds. One initial delivery plus up to six retries (seven POSTs worst case), spaced 30s, 2m, 10m, 1h, 6h, 24h. Use `workflow_run_id` as your idempotency key. After the sixth failed retry we disable the webhook and email you.

### Supported events

One event type is live today:

| Event type | Fires when |
|---|---|
| `daily_options_flow.available` | The daily options-flow pipeline completes for a session (T+1), between 05:00 and 09:00 UTC on the calendar day after the trading date (Friday's data fires Saturday). |

Additional events will be listed here as we add them. Register only for events you handle; an unrecognized event type is rejected at registration (HTTP 422).

### Managing webhooks

Self-service via your API key — every endpoint below authenticates with the same `X-API-Key` you use for `/v0/*` reads, and each key only sees and manages its own webhooks. Trial accounts may register up to **2 webhooks**; paid accounts are unlimited.

| Method | Path | Purpose |
|---|---|---|
| `POST` | `/v1/webhooks` | Register a URL. Returns `webhook_id` + `secret` (secret shown once). |
| `GET` | `/v1/webhooks` | List your webhooks (secrets redacted). |
| `GET` | `/v1/webhooks/{id}` | Fetch one webhook by id (secret redacted). |
| `DELETE` | `/v1/webhooks/{id}` | Remove a webhook. |
| `POST` | `/v1/webhooks/{id}/rotate-secret` | Generate a new secret. Old secret stays valid for 24h. |
| `POST` | `/v1/webhooks/{id}/send-test` | Fire a synthetic delivery to the URL for integration testing. Add `?with_retries=true` to engage the retry ladder on 5xx for failure-handling validation. |

### Polling (alternative)

Each daily publish lands a manifest file in the [flat files](/documentation#flat-files) area of the customer bucket; poll it programmatically between 05:00 and 11:00 UTC on the day after each session to detect delivery:

```
gs://prod-masscrest/v0/_manifests/year=YYYY/date=YYYY-MM-DD/manifest.json
```

The manifest is written as the very last step of the daily publish, after every parquet and every downstream ingest is in place. Its existence is the delivery-complete signal; once visible it is safe to start reading immediately.

Sample payload:

```json
{
  "workflow_run_id": "aa1cb0c2-5b47-46f1-9c9a-8c9d1c2b0e33",
  "trading_date": "2026-07-31",
  "published_at": "2026-08-03T01:42:22Z",
  "files": [
    { "path": "gs://prod-masscrest/v0/option_flows/01_day/year=2026/date=2026-07-31/000000000000.parquet", "byte_count": 24898886 },
    { "path": "gs://prod-masscrest/v0/underlying_option_flows/01_day/year=2026/date=2026-07-31/000000000000.parquet", "byte_count": 1479423 },
    { "path": "gs://prod-masscrest/v0/underlying_flow_signal/01_day/year=2026/date=2026-07-31/000000000000.parquet", "byte_count": 3512032 },
    "…"
  ]
}
```

## Data corrections

When we detect an error in already-published data, we notify the client's primary technical contact and communicate the fix within 48 hours.

Corrections are written in place, keyed on the same row grain (`date`, `figi`, and `contract_id` where applicable). Re-sync the affected partition to pick up corrected values.

## Trial vs. paid tier

Trial accounts share the paid-tier schemas and endpoints — nothing about the shape of the data changes — but usage is capped so a trial can validate integration and evaluate signal quality without pulling the full historical universe. Paid accounts have none of these limits.

| Surface | Trial | Paid |
|---|---|---|
| **REST API — per-figi endpoints** (`/v0/option_flows/*`, `/v0/underlying_option_flows/01_day`, `/v0/underlying_flow_signals/01_day`) | **25 unique FIGIs / UTC day**, cumulative across every per-figi call. The 26th distinct FIGI in a UTC day returns HTTP 403 with a structured JSON body: `{"error": "trial_figi_cap_exceeded", "cap": 25, "resets_at": "<iso8601>", "touched": [{"figi": "...", "symbol": "..."}, ...], "requested_new": [{"figi": "...", "symbol": "..."}, ...]}`. Cap resets at 00:00 UTC. Must supply an explicit `figi` or `underlying_ticker` / `traded_underlying` filter — a broad `sector` / `industry`-only sweep returns HTTP 400. | Full universe, no per-figi cap. Broad `stock_metadata` filters (sector / industry / is_etf / is_adr) work as documented. |
| **REST API — per-figi snapshots** (`/v0/underlying_option_flows/01_day/snapshot`, `/v0/underlying_flow_signals/01_day/snapshot`) | HTTP 403. A wide snapshot would charge every returned FIGI against the 25-figi cap in a single call, so snapshot endpoints are blocked outright on trial. Use the ranged per-figi endpoints with an explicit `figi` filter, or `/v0/grouped_flow_signal/01_day/snapshot` (not figi-capped) for broad cross-sectional exploration. | Full snapshot access; `stock_metadata` filters (sector / industry / is_etf / is_adr) work as documented. |
| **Data grain** | Aggregate rollups only: per underlying × day, per (underlying, callput) × day, and grouped rollups (sector / industry / ETF / ADR / single-stock). Per-contract data is not available. | Same aggregate rollups plus per-contract data through the flat-files bucket. |
| **REST API — intraday** (`/v0/*/10_min`, including snapshots) | HTTP 403. Daily grain only. | Full intraday access. |
| **REST API — cohort rollups** (`/v0/grouped_flow_signal/01_day(+/snapshot)`) and metadata (`/v0/stock_mapping`) | Not figi-capped. Sector / industry / ETF / ADR / single-stock rollups and metadata browsing are free. | Same. |
| **REST API — universe** | Full — any FIGI Masscrest tracks is queryable (subject to the 25-figi cap). | Full. |
| **REST API — daily history depth** | 730 days. | Full history from 2020-01-02. |
| **REST API — daily call cap** | 100 calls / UTC day (HTTP 429 on the 101st call). | 1,000 calls / UTC day (contact sales for higher). |
| **MCP server** | All tools remain callable. API 403s / 400s (25-figi cap, intraday block, broad-filter block) surface as tool errors — the assistant sees a plain-language message and stops retrying. No MCP-side role gate. | All tools, no cap. |
| **Flat files** | Requires the `programmatic` entitlement (opt-in per account; contact sales). Enabled trials get HMAC keys against **`gs://prod-masscrest-trial`** — an aggregates-only, daily-only, 180-day-lagged subset with fresh reference metadata. See the Flat files section for the exact bucket layout. Trials without `programmatic` see HTTP 403 on any flat-files call. | Full production bucket `gs://prod-masscrest` — contract-level and intraday parquets, no lag. |
| **Webhooks** (`/v1/webhooks`) | Self-service registration with your API key, capped at **2 registered webhooks** per account. | Self-service registration with your API key, unlimited webhooks. |
| **Portfolio saves** | Unlimited. | Unlimited. |

### Trial-to-paid upgrade

On upgrade, the API-tier changes take effect immediately (up to a 5-minute per-instance principal-cache TTL). The Clerk webhook revokes the trial HMAC key at the moment of upgrade, so any local `~/.boto` or gsutil configuration pointing at `gs://prod-masscrest-trial` becomes invalid. Call `POST /v0/flat-files/credentials/rotate` (or the create endpoint) to receive new keys against the production bucket `gs://prod-masscrest` and re-point your pipeline.

---

# option_flows_01_day

Per-contract daily option-flow rows, split by participant class (institutional `_i`, retail `_r`, market-maker `_m`). One row per unique option contract that traded on the session, uniquely identified by `(date, figi, symbol, callput, strikeprice, expirationdate)`.

- Grain: `(date, figi, symbol, callput, strikeprice, expirationdate)`
- History: 2020-01-02 →
- Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

`*_qty` fields are raw traded contract counts (integer). `*_premium` fields are dollar premium (`qty × traded price × adj_shares_deliverable`). `*_delta` / `*_gamma` / `*_vega` fields are the traded contracts as greek-weighted USD notional: dollar notional multiplied by the respective per-contract greek, then scaled by `adj_shares_deliverable`. Delta-weighted notionals are naturally signed by option type (calls +, puts −); `net_* = buy_* − sell_*` and inherits the sign. The `_i + _r + _m ≈ 0` accounting identity holds row-by-row on the notional columns (up to rounding). `floor_*` is exchange-floor / broker-committed volume, held in its own columns because Masscrest does not track these trades and their side (buy vs sell) is not available. Additional per-contract context columns carry the last-observed underlying spot, forward, IV and greeks for the session (useful as an end-of-day mark).

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `date` | Date | trading day (UTC) | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Session date. US equity trading calendar. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24` |
| `figi` | String(12) | identifier | `(date, figi, symbol, callput, strikeprice, expirationdate)` | OpenFIGI composite FIGI for the underlying. Stable across ticker changes; join key to `stock_metadata`, `px_01_day`, `split_factors`. | exchange | n/a | 2020-01-02 | never null | `BBG000MM2P62` |
| `symbol` | String | ticker | `(date, figi, symbol, callput, strikeprice, expirationdate)` | OCC OPRA root of the option (no-dot form: `BRKB`, not `BRK.B`). Match on this key when joining back to raw OCC / OPRA feeds. Use `traded_underlying` for equity-side joins. | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `traded_underlying` | String | ticker | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Point-in-time listed equity ticker for the deliverable underlying on `date`. Dotted dual-class form (`BRK.B`). Join key to `px_01_day`, `stock_metadata`, dividends. | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `callput` | String(1) | option type | `(date, figi, symbol, callput, strikeprice, expirationdate)` | `'C'` (call) or `'P'` (put). | exchange | T+1 | 2020-01-02 | never null | `C` |
| `strikeprice` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Contract strike price, in the deliverable equity's price units. Not split-adjusted at this grain; use `cumulative_split_factor` to convert to a current-share-equivalent strike. | exchange | T+1 | 2020-01-02 | never null | `215.00` |
| `expirationdate` | Date | boundary | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Contract expiration date (US equity calendar). | exchange | T+1 | 2020-01-02 | never null | `2026-08-15` |
| `dte` | Int64 | days | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Days to expiry: `expirationdate − date` (calendar days). `0` on the expiry-day trading session; can be negative on late prints of already-expired contracts (rare). | derived | T+1 | 2020-01-02 | never null | `22` |
| `contract_id` | String | identifier | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Masscrest-built contract identifier that tracks a contract's economic identity across corporate actions (splits, reverse splits, M&A, deliverable adjustments). Standard OCC / OSI symbols mint a new identifier every time the deliverable changes; `contract_id` deliberately does not, so a single value follows the same economic contract through its lifetime. Built as a deterministic string from `(root_symbol, expiration, callput, root-strike × 1000, corporate-action epoch tag)`; root_symbol and root-strike are the OCC-root form pre-action, and the `E<YYYYMMDD>` epoch tag disambiguates recycled OPRA symbols that share the same expiry/strike across pre- and post-action cohorts. Joins to `underlying_option_flows_01_day` (per underlying) via `figi` but is unique at the contract grain. | derived | T+1 | 2020-01-02 | null on trades that pre-date a required OCC memo for a not-yet-mapped corporate-action epoch (rare, <0.01% of rows) | `AAPL  260815C00215000_E00000000` |
| `adj_shares_deliverable` | Float64 | shares per contract | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Shares conversion adjustment for OCC contract adjustments such as reverse split, M&A, and other corporate actions. Standard equity option delivers 100 shares → `adj_shares_deliverable = 100`. Post-adjustment values (fractional deliverables, cash + share basket primary legs) are the OCC-declared per-contract share count. | exchange | T+1 | 2020-01-02 | never null | `100.0` |
| `cumulative_split_factor` | Float64 | ratio | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Cumulative equity split factor in effect on `date` (same source as `split_factors.cum_split_factor`). Divide `strikeprice` by this factor to convert to a current-shares-equivalent strike. `1.0` when no split applies on or after `date`. | derived | T+1 | 2020-01-02 | never null | `1.0` |
| `is_index` | Bool | flag | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Whether the underlying is a cash-settled index (SPX, NDX, RUT, VIX, XSP, MRUT, NANOS, XND). `false` for every single-stock and ETF contract. Currently always `false` in the served surface (Masscrest does not sell index-option data). Retained for schema stability. | exchange | T+1 | 2020-01-02 | never null | `false` |
| `last_underprice` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Underlying spot price observed at the last option trade of the session. Not split-adjusted (contemporaneous mark). | exchange | T+1 | 2020-01-02 | null when the contract has no trades in the session | `224.31` |
| `last_fwd_underprice` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Forward price on the underlying, adjusted for dividends and the risk-free rate. Not adjusted for splits. | derived | T+1 | 2020-01-02 | null when the contract has no trades in the session | `224.68` |
| `last_price` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Last traded option price on the session. Per-contract price (not scaled by `adj_shares_deliverable`). | exchange | T+1 | 2020-01-02 | null when the contract has no trades in the session | `9.42` |
| `last_iv` | Float64 | annualised vol (0.30 = 30%) | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Implied volatility at the last trade of the session, fit against `last_fwd_underprice` and `last_price`. | derived | T+1 | 2020-01-02 | null when the contract has no trades / IV inversion fails | `0.2418` |
| `last_delta` | Float64 | delta per contract | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Contract delta at the last trade of the session. Signed by option type (calls +, puts −). | derived | T+1 | 2020-01-02 | null when the contract has no trades in the session | `0.5620` |
| `last_gamma` | Float64 | gamma per contract | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Contract gamma at the last trade of the session. | derived | T+1 | 2020-01-02 | null when the contract has no trades in the session | `0.0184` |
| `last_vega` | Float64 | vega per contract | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Contract vega at the last trade of the session (dollar P&L per 1 vol point change). | derived | T+1 | 2020-01-02 | null when the contract has no trades in the session | `0.2712` |
| `qty` | Int64 | contracts | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Total unique traded contract volume for the session (each print contributes its quantity once; `m_qty` is not double-counted). | exchange | T+1 | 2020-01-02 | 0 on no-volume rows (present only when other fields exist) | `1_842` |
| `buy_r_qty` / `sell_r_qty` / `net_r_qty` | Int64 | contracts | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net contract count. `net = buy − sell`. | model | T+1 | 2020-01-02 | 0 when no qualifying retail flow | `241` |
| `buy_r_premium` / `sell_r_premium` / `net_r_premium` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Retail dollar premium paid on buys, received on sells, and their net. Signed on `net`. Scaled by `adj_shares_deliverable` (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `312_040.0` |
| `buy_r_delta` / `sell_r_delta` / `net_r_delta` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD notional × delta (deliverable-adjusted). Naturally signed by option type (calls +, puts −); `net = buy − sell`. Positive `net` = net long-delta demand from retail on this contract. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `-38_720.55` |
| `buy_r_gamma` / `sell_r_gamma` / `net_r_gamma` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD-notional-converted gamma (dollar notional multiplied by the per-contract gamma, deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `12_450.30` |
| `buy_r_vega` / `sell_r_vega` / `net_r_vega` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `88_012.40` |
| `buy_i_qty` / `sell_i_qty` / `net_i_qty` | Int64 | contracts | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net contract count. Same convention as retail. | model | T+1 | 2020-01-02 | 0 when no qualifying institutional flow | `987` |
| `buy_i_premium` / `sell_i_premium` / `net_i_premium` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Institutional dollar premium buy / sell / net (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `1_120_338.55` |
| `buy_i_delta` / `sell_i_delta` / `net_i_delta` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD notional × delta (deliverable-adjusted). Naturally signed; positive `net` = net long-delta demand from the institutional class on this contract. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `892_310.00` |
| `buy_i_gamma` / `sell_i_gamma` / `net_i_gamma` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `41_882.70` |
| `buy_i_vega` / `sell_i_vega` / `net_i_vega` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `312_048.80` |
| `buy_m_qty` / `sell_m_qty` / `net_m_qty` | Int64 | contracts | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net contract count. Includes the residual absorption of customer (retail + institutional) net imbalance on the opposite side, so the row-level accounting identity `_i + _r + _m ≈ 0` holds by construction on the notionals. | model | T+1 | 2020-01-02 | 0 when no qualifying market-maker flow | `-1_098` |
| `buy_m_premium` / `sell_m_premium` / `net_m_premium` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker dollar premium buy / sell / net (deliverable-adjusted). Mirror of `_i + _r` on the same row up to floor / rounding. | model | T+1 | 2020-01-02 | 0.0 when no qualifying market-maker flow | `-1_432_378.65` |
| `buy_m_delta` / `sell_m_delta` / `net_m_delta` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD notional × delta (deliverable-adjusted). Mirror of `_i + _r` on the same row. | model | T+1 | 2020-01-02 | 0.0 when no qualifying market-maker flow | `-853_590.00` |
| `buy_m_gamma` / `sell_m_gamma` / `net_m_gamma` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying market-maker flow | `-54_333.00` |
| `buy_m_vega` / `sell_m_vega` / `net_m_vega` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying market-maker flow | `-400_172.00` |
| `floor_qty` | Int64 | contracts | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed contract count (direct-negotiation outcry trades on the exchange floor). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2020-01-02 | 0 when no floor prints | `24` |
| `floor_premium` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed dollar premium (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2020-01-02 | 0.0 when no floor prints | `24_120.0` |
| `floor_delta` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD notional × delta (deliverable-adjusted). No directional classification provided; reported for volume completeness. Naturally signed by option type. | exchange | T+1 | 2020-01-02 | 0.0 when no floor prints | `13_492.20` |
| `floor_gamma` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD-notional-converted gamma (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2020-01-02 | 0.0 when no floor prints | `1_842.10` |
| `floor_vega` | Float64 | USD | `(date, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD-notional-converted vega (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2020-01-02 | 0.0 when no floor prints | `9_820.60` |

---

### Join hints

For per-underlying daily aggregates (rolled up across every contract on the same `(date, figi)`), use `underlying_option_flows_01_day`. For underlying prices and turnover, join to `px_01_day` on `(date, figi)` and self-adjust using `split_factors`. For contract-level metadata (deliverable per contract, corporate-action epochs) the values in this table are already point-in-time; no secondary join is needed.

## Gross-volume accounting

Each trade has a customer side (`buy_{r,i}_qty` / `sell_{r,i}_qty`) and a market-maker counterparty (`buy_m_qty` / `sell_m_qty`, rebuilt to absorb the customer imbalance so the row-level `_i + _r + _m ≈ 0` identity holds on notionals).

**DO**: aggregate on `qty` — one entry per trade.

**DON'T**: sum the cohort legs (`buy_r_qty + sell_r_qty + buy_i_qty + sell_i_qty + buy_m_qty + sell_m_qty + floor_qty`) — inflates volume by ~30-35% because the market-maker leg gets added on top of the customer legs.

---

# option_flows_10_min

10-minute-bucketed sibling of `option_flows_01_day`. Per-contract option-flow rows at a 10-minute cadence: one row per unique option contract that traded within the bucket, keyed on `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)`.

- Grain: `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)`
- History: 2020-01-02 →
- Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

Request window is capped at 30 days per call on the REST endpoint. Omitted dates default to the most recent 30. For multi-year intraday history use flat files at `gs://prod-masscrest/v0/option_flows/10_min/`; the REST cap keeps response sizes bounded.

### Bucket boundary convention

`ten_min_timeframe` is the end of the 10-minute bucket, in New York wall-clock time stored as a naive `DateTime`. Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. First RTH bucket ends at `09:40:00`, last at `16:00:00` (39 buckets per full RTH session). Join to `px_10_min` on `(date, ten_min_timeframe, figi)`; both sides use the same NY-wall-clock convention.

Field semantics match `option_flows_01_day` exactly: the same buy / sell / net triples across the participant classes, greek-weighted USD notionals, the accounting identity `_i + _r + _m ≈ 0` (row-by-row), and the same `last_*` end-of-bucket context columns. The only structural difference is the added `ten_min_timeframe` dimension.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `date` | Date | trading day (UTC) | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Session date. US equity trading calendar. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24` |
| `ten_min_timeframe` | DateTime | NY wall-clock, bucket close | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | End timestamp of the 10-min bucket (naive NY-time). Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24 09:40:00` |
| `figi` | String(12) | identifier | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | OpenFIGI composite FIGI for the underlying. Stable across ticker changes; join key to `stock_metadata`, `px_10_min`, `split_factors`. | exchange | n/a | 2020-01-02 | never null | `BBG000MM2P62` |
| `symbol` | String | ticker | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | OCC OPRA root of the option (no-dot form: `BRKB`, not `BRK.B`). Match on this key when joining back to raw OCC / OPRA feeds. Use `traded_underlying` for equity-side joins. | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `traded_underlying` | String | ticker | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Point-in-time listed equity ticker for the deliverable underlying on `date`. Dotted dual-class form (`BRK.B`). Join key to `px_10_min`, `stock_metadata`, dividends. | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `callput` | String(1) | option type | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | `'C'` (call) or `'P'` (put). | exchange | T+1 | 2020-01-02 | never null | `C` |
| `strikeprice` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Contract strike price, in the deliverable equity's price units. Not split-adjusted at this grain; use `cumulative_split_factor` to convert to a current-share-equivalent strike. | exchange | T+1 | 2020-01-02 | never null | `215.00` |
| `expirationdate` | Date | boundary | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Contract expiration date (US equity calendar). | exchange | T+1 | 2020-01-02 | never null | `2026-08-15` |
| `dte` | Int64 | days | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Days to expiry: `expirationdate − date` (calendar days). `0` on the expiry-day trading session. | derived | T+1 | 2020-01-02 | never null | `22` |
| `contract_id` | String | identifier | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Masscrest-built contract identifier that tracks a contract's economic identity across corporate actions (splits, reverse splits, M&A, deliverable adjustments), unlike standard OCC / OSI symbols which mint a new identifier every time the deliverable changes. Built as a deterministic string from `(root_symbol, expiration, callput, root-strike × 1000, corporate-action epoch tag)`; the trailing `E<YYYYMMDD>` epoch tag disambiguates recycled OPRA symbols that share expiry/strike across pre- and post-action cohorts. Same value across every 10-min bucket of the contract's session and across `option_flows_01_day`. | derived | T+1 | 2020-01-02 | null on trades that pre-date a required OCC memo for a not-yet-mapped corporate-action epoch (rare) | `AAPL  260815C00215000_E00000000` |
| `adj_shares_deliverable` | Float64 | shares per contract | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Shares conversion adjustment for OCC contract adjustments such as reverse split, M&A, and other corporate actions. Standard equity option delivers 100 shares → `adj_shares_deliverable = 100`. | exchange | T+1 | 2020-01-02 | never null | `100.0` |
| `cumulative_split_factor` | Float64 | ratio | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Cumulative equity split factor in effect on `date` (same source as `split_factors.cum_split_factor`). | derived | T+1 | 2020-01-02 | never null | `1.0` |
| `is_index` | Bool | flag | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Whether the underlying is a cash-settled index. Currently always `false` in the served surface. | exchange | T+1 | 2020-01-02 | never null | `false` |
| `last_underprice` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Underlying spot at the last option trade within the bucket. Not split-adjusted. | exchange | T+1 | 2020-01-02 | null when the contract has no trades in the bucket | `224.10` |
| `last_fwd_underprice` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Forward price on the underlying, adjusted for dividends and the risk-free rate. Not adjusted for splits. | derived | T+1 | 2020-01-02 | null when the contract has no trades in the bucket | `224.48` |
| `last_price` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Last traded option price in the bucket. Per-contract price (not scaled by `adj_shares_deliverable`). | exchange | T+1 | 2020-01-02 | null when the contract has no trades in the bucket | `9.38` |
| `last_iv` | Float64 | annualised vol (0.30 = 30%) | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Implied volatility at the last trade of the bucket. | derived | T+1 | 2020-01-02 | null when the contract has no trades / IV inversion fails | `0.2418` |
| `last_delta` | Float64 | delta per contract | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Contract delta at the last trade of the bucket. Signed by option type. | derived | T+1 | 2020-01-02 | null when the contract has no trades in the bucket | `0.5620` |
| `last_gamma` | Float64 | gamma per contract | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Contract gamma at the last trade of the bucket. | derived | T+1 | 2020-01-02 | null when the contract has no trades in the bucket | `0.0184` |
| `last_vega` | Float64 | vega per contract | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Contract vega at the last trade of the bucket. | derived | T+1 | 2020-01-02 | null when the contract has no trades in the bucket | `0.2712` |
| `qty` | Int64 | contracts | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Total unique traded contract volume in the bucket. | exchange | T+1 | 2020-01-02 | 0 on no-volume rows | `184` |
| `buy_r_qty` / `sell_r_qty` / `net_r_qty` | Int64 | contracts | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net contract count within the bucket. | model | T+1 | 2020-01-02 | 0 when no qualifying retail flow | `24` |
| `buy_r_premium` / `sell_r_premium` / `net_r_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Retail dollar premium buy / sell / net for the bucket (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `31_204.0` |
| `buy_r_delta` / `sell_r_delta` / `net_r_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD notional × delta (deliverable-adjusted). Naturally signed by option type; positive `net` = net long-delta demand from retail on this contract in the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `-3_872.55` |
| `buy_r_gamma` / `sell_r_gamma` / `net_r_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `1_245.30` |
| `buy_r_vega` / `sell_r_vega` / `net_r_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Retail buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `8_801.40` |
| `buy_i_qty` / `sell_i_qty` / `net_i_qty` | Int64 | contracts | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net contract count for the bucket. | model | T+1 | 2020-01-02 | 0 when no qualifying institutional flow | `98` |
| `buy_i_premium` / `sell_i_premium` / `net_i_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Institutional dollar premium buy / sell / net (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `112_034.55` |
| `buy_i_delta` / `sell_i_delta` / `net_i_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD notional × delta (deliverable-adjusted). Positive `net` = net long-delta demand from the institutional class on this contract in the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `89_231.00` |
| `buy_i_gamma` / `sell_i_gamma` / `net_i_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `4_188.70` |
| `buy_i_vega` / `sell_i_vega` / `net_i_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Institutional buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `31_204.80` |
| `buy_m_qty` / `sell_m_qty` / `net_m_qty` | Int64 | contracts | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net contract count for the bucket. Includes the residual absorption of customer (retail + institutional) net imbalance on the opposite side. | model | T+1 | 2020-01-02 | 0 when no qualifying market-maker flow | `-108` |
| `buy_m_premium` / `sell_m_premium` / `net_m_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker dollar premium buy / sell / net (deliverable-adjusted). Mirror of `_i + _r` on the same row up to floor / rounding. | model | T+1 | 2020-01-02 | 0.0 when no qualifying market-maker flow | `-143_237.65` |
| `buy_m_delta` / `sell_m_delta` / `net_m_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD notional × delta (deliverable-adjusted). Mirror of `_i + _r` on the same row. | model | T+1 | 2020-01-02 | 0.0 when no qualifying market-maker flow | `-85_359.00` |
| `buy_m_gamma` / `sell_m_gamma` / `net_m_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD-notional-converted gamma (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying market-maker flow | `-5_433.30` |
| `buy_m_vega` / `sell_m_vega` / `net_m_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Market-maker buy / sell / net flow as USD-notional-converted vega (deliverable-adjusted). | model | T+1 | 2020-01-02 | 0.0 when no qualifying market-maker flow | `-40_017.20` |
| `floor_qty` | Int64 | contracts | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed contract count within the bucket (direct-negotiation outcry trades on the exchange floor). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2020-01-02 | 0 when no floor prints | `4` |
| `floor_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed dollar premium within the bucket (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2020-01-02 | 0.0 when no floor prints | `4_120.0` |
| `floor_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD notional × delta within the bucket (deliverable-adjusted). No directional classification provided; reported for volume completeness. Naturally signed. | exchange | T+1 | 2020-01-02 | 0.0 when no floor prints | `2_349.20` |
| `floor_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD-notional-converted gamma within the bucket (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2020-01-02 | 0.0 when no floor prints | `184.10` |
| `floor_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, symbol, callput, strikeprice, expirationdate)` | Total floor-executed USD-notional-converted vega within the bucket (deliverable-adjusted). No directional classification provided; reported for volume completeness. | exchange | T+1 | 2020-01-02 | 0.0 when no floor prints | `982.60` |

---

### Join hints

For per-underlying 10-min aggregates (rolled up across every contract on the same `(date, ten_min_timeframe, figi)`), use `underlying_option_flows_10_min`. For underlying prices and turnover, join to `px_10_min` on `(date, ten_min_timeframe, figi)` and self-adjust using `split_factors`. For daily rollups of the same contracts, sum across `ten_min_timeframe` or use `option_flows_01_day` directly.

## Gross-volume accounting

Each trade has a customer side (`buy_{r,i}_qty` / `sell_{r,i}_qty`) and a market-maker counterparty (`buy_m_qty` / `sell_m_qty`, rebuilt to absorb the customer imbalance so the row-level `_i + _r + _m ≈ 0` identity holds on notionals).

**DO**: aggregate on `qty` — one entry per trade per bucket.

**DON'T**: sum the cohort legs (`buy_r_qty + sell_r_qty + buy_i_qty + sell_i_qty + buy_m_qty + sell_m_qty + floor_qty`) — inflates per bucket volume by ~30-35% because the market-maker leg gets added on top of the customer legs.

---

# underlying_option_flows_01_day

Per-underlying × callput daily aggregation. Same participant-split option-flow columns as `option_flows_01_day`, rolled up across every contract on the same `(date, figi, callput)` grain rather than kept at contract resolution. Every underlying has up to two rows per session: `callput = 'C'` and `callput = 'P'`. Adds context columns (contract count, aggregate share count, session-end underlying mark, 30-day ATM IV).

- Grain: `(date, figi, callput)`
- History: 2020-01-02 →
- Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

Underlying price, turnover, and metadata are not baked into this table. Join to `px_01_day` (adjust locally with `split_factors`) and `stock_metadata` on `figi`.

### Difference vs `option_flows_01_day`

`option_flows_01_day` is per-contract. `underlying_option_flows_01_day` collapses every contract on `(date, figi, callput)` into a single row, preserving the call vs put dimension for cross-sectional or callput-aware research (put-only skew, gross calls-vs-puts turnover, callput-aware Δ). Query with `callput=CP` to have both rows summed back into one at query time; query with `callput=C` or `callput=P` for a single side. No contract-level filters here (no `dte` / `abs_delta` axes); this table is pre-aggregated inside the pipeline.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `date` | Date | trading day (UTC) | `(date, figi, callput)` | Session date. US equity trading calendar. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24` |
| `figi` | String(12) | identifier | `(date, figi, callput)` | OpenFIGI composite FIGI for the underlying. Join key to `stock_metadata`, `px_01_day`, `split_factors`. | exchange | n/a | 2020-01-02 | never null | `BBG000MM2P62` |
| `traded_underlying` | String | ticker | `(date, figi, callput)` | Point-in-time listed equity ticker on `date`. Dotted dual-class form (`BRK.B`). | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `callput` | String(1) | option type | `(date, figi, callput)` | `'C'` (call side) or `'P'` (put side). When queried with `callput=CP` the router sums the two rows and emits the literal `'CP'` in this column. | exchange | T+1 | 2020-01-02 | never null | `C` |
| `n_contracts` | UInt32 | contracts | `(date, figi, callput)` | Distinct option contracts that traded on this underlying × callput on `date`. | exchange | T+1 | 2020-01-02 | 0 on no-volume days | `184` |
| `total_shares` | Float64 | delta-adjusted share equivalents | `(date, figi, callput)` | Sum of all traded contract volume converted to underlying-share equivalents (`qty × adj_shares_deliverable`), regardless of side. | exchange | T+1 | 2020-01-02 | 0.0 on no-volume days | `2_384_100.0` |
| `last_underprice` | Float64 | USD | `(date, figi, callput)` | Underlying spot price observed against the last option trade of the session. Not split-adjusted (contemporaneous mark). | exchange | T+1 | 2020-01-02 | null on no-volume days | `224.31` |
| `last_fwd_underprice` | Float64 | USD | `(date, figi, callput)` | Forward price on the underlying, adjusted for dividends and the risk-free rate. Not adjusted for splits. | derived | T+1 | 2020-01-02 | null on no-volume days | `224.68` |
| `atm_iv_30d` | Float64 | annualised vol (0.30 = 30%) | `(date, figi, callput)` | 30-day at-the-money implied volatility of the underlying, from the last IV surface fit of the session. | derived | T+1 | 2020-01-02 | null on no-volume days | `0.2418` |
| `buy_r_shares` / `sell_r_shares` / `net_r_shares` | Float64 | delta-adjusted share equivalents | `(date, figi, callput)` | Retail buy / sell / net share equivalents for the (underlying, callput) row. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `24_100.0` |
| `buy_r_premium` / `sell_r_premium` / `net_r_premium` | Float64 | USD | `(date, figi, callput)` | Retail dollar premium buy / sell / net. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `312_040.0` |
| `buy_r_delta` / `sell_r_delta` / `net_r_delta` | Float64 | USD | `(date, figi, callput)` | Retail buy / sell / net flow as USD notional × delta for the (underlying, callput) row. Naturally signed (calls +, puts −); positive `net` = net long-delta demand from retail on this call/put side. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-3_872_000.55` |
| `buy_r_gamma` / `sell_r_gamma` / `net_r_gamma` | Float64 | USD | `(date, figi, callput)` | Retail buy / sell / net flow as USD-notional-converted gamma. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `1_245_030.30` |
| `buy_r_vega` / `sell_r_vega` / `net_r_vega` | Float64 | USD | `(date, figi, callput)` | Retail buy / sell / net flow as USD-notional-converted vega. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `8_801_240.40` |
| `buy_i_shares` / `sell_i_shares` / `net_i_shares` | Float64 | delta-adjusted share equivalents | `(date, figi, callput)` | Institutional buy / sell / net share equivalents. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `128_450.0` |
| `buy_i_premium` / `sell_i_premium` / `net_i_premium` | Float64 | USD | `(date, figi, callput)` | Institutional dollar premium buy / sell / net. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `412_034.55` |
| `buy_i_delta` / `sell_i_delta` / `net_i_delta` | Float64 | USD | `(date, figi, callput)` | Institutional buy / sell / net flow as USD notional × delta for the (underlying, callput) row. Positive `net` = net long-delta demand from the institutional class on this call/put side. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `89_231_000.00` |
| `buy_i_gamma` / `sell_i_gamma` / `net_i_gamma` | Float64 | USD | `(date, figi, callput)` | Institutional buy / sell / net flow as USD-notional-converted gamma. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `4_188_270.70` |
| `buy_i_vega` / `sell_i_vega` / `net_i_vega` | Float64 | USD | `(date, figi, callput)` | Institutional buy / sell / net flow as USD-notional-converted vega. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `31_200_480.80` |
| `buy_m_shares` / `sell_m_shares` / `net_m_shares` | Float64 | delta-adjusted share equivalents | `(date, figi, callput)` | Market-maker buy / sell / net share equivalents. `_i + _r + _m = 0` per row. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-130_860.0` |
| `buy_m_premium` / `sell_m_premium` / `net_m_premium` | Float64 | USD | `(date, figi, callput)` | Market-maker dollar premium buy / sell / net. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-824_067.10` |
| `buy_m_delta` / `sell_m_delta` / `net_m_delta` | Float64 | USD | `(date, figi, callput)` | Market-maker buy / sell / net flow as USD notional × delta. Mirror of `_i + _r` on the same row (identity `_i + _r + _m = 0`). | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-85_359_000.00` |
| `buy_m_gamma` / `sell_m_gamma` / `net_m_gamma` | Float64 | USD | `(date, figi, callput)` | Market-maker buy / sell / net flow as USD-notional-converted gamma. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-5_433_300.00` |
| `buy_m_vega` / `sell_m_vega` / `net_m_vega` | Float64 | USD | `(date, figi, callput)` | Market-maker buy / sell / net flow as USD-notional-converted vega. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-40_001_720.00` |

## Gross-volume accounting

Each trade has a customer side (`buy_{r,i}_shares` / `sell_{r,i}_shares`) and a market-maker counterparty (`buy_m_shares` / `sell_m_shares`, rebuilt to absorb the customer imbalance so the row-level `_i + _r + _m ≈ 0` identity holds on notionals). Same convention applies to `_premium` / `_delta` / `_gamma` / `_vega`.

**DO**: aggregate on `total_shares` — one entry per trade.

**DON'T**: sum the cohort legs (`buy_r_shares + sell_r_shares + buy_i_shares + sell_i_shares + buy_m_shares + sell_m_shares`) — inflates volume by ~30-35% because the market-maker leg gets added on top of the customer legs.

---

# underlying_option_flows_10_min

10-minute-bucketed sibling of `underlying_option_flows_01_day`. Per-underlying × callput option-flow aggregation at a 10-minute cadence, with contract-count context and session-end marks.

- Grain: `(date, ten_min_timeframe, figi, callput)`. Up to two rows per underlying per 10-min bucket (`'C'` and `'P'`).
- History: 2020-01-02 →
- Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

Underlying price, turnover, and metadata are not baked into this table. Join to `px_10_min` (adjust locally with `split_factors`) and `stock_metadata` on `figi`.

Request window is capped at 30 days per call on the REST endpoint. For multi-year intraday history use flat files at `gs://prod-masscrest/v0/underlying_option_flows/10_min/`.

### Bucket boundary convention

`ten_min_timeframe` is the end of the 10-minute bucket, NY wall-clock, naive `DateTime`. Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. First RTH bucket: `09:40:00`; last: `16:00:00`. Same convention as `option_flows_10_min` and `px_10_min`; join directly on `(date, ten_min_timeframe, figi)`.

### Difference vs `option_flows_10_min`

`option_flows_10_min` is per-contract. `underlying_option_flows_10_min` collapses every contract on `(date, ten_min_timeframe, figi, callput)` into a single row, keeping the call vs put dimension and adding contract-count / session-mark / IV context columns. This is the intended surface for cross-sectional or callput-aware research.

`buy_m_shares` / `sell_m_shares` are for directional cohort analysis (net market-maker positioning on the row), not for gross volume totals. The same convention applies to the `_premium`, `_delta`, `_gamma`, and `_vega` cohort columns — sum `buy_r + sell_r + buy_i + sell_i + buy_m + sell_m` and you double-count the market-maker leg on every unit.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `date` | Date | trading day (UTC) | `(date, ten_min_timeframe, figi, callput)` | Session date. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24` |
| `ten_min_timeframe` | DateTime | NY wall-clock, bucket close | `(date, ten_min_timeframe, figi, callput)` | End timestamp of the 10-min bucket (naive NY-time). | exchange | T+1 | 2020-01-02 | never null | `2026-07-24 09:40:00` |
| `figi` | String(12) | identifier | `(date, ten_min_timeframe, figi, callput)` | OpenFIGI composite FIGI for the underlying. | exchange | n/a | 2020-01-02 | never null | `BBG000MM2P62` |
| `traded_underlying` | String | ticker | `(date, ten_min_timeframe, figi, callput)` | Point-in-time listed equity ticker on `date`. Dotted dual-class form. | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `callput` | String(1) | option type | `(date, ten_min_timeframe, figi, callput)` | `'C'` or `'P'`. `callput=CP` on the endpoint sums the two rows at query time. | exchange | T+1 | 2020-01-02 | never null | `C` |
| `n_contracts` | UInt32 | contracts | `(date, ten_min_timeframe, figi, callput)` | Distinct option contracts traded on this underlying × callput within the bucket. | exchange | T+1 | 2020-01-02 | 0 on no-volume buckets | `48` |
| `total_shares` | Float64 | delta-adjusted share equivalents | `(date, ten_min_timeframe, figi, callput)` | Sum of all traded volume converted to share equivalents within the bucket. | exchange | T+1 | 2020-01-02 | 0.0 on no-volume buckets | `184_500.0` |
| `last_underprice` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Underlying spot at the last option trade within the bucket. Not split-adjusted. | exchange | T+1 | 2020-01-02 | null on no-volume buckets | `224.10` |
| `last_fwd_underprice` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Forward price on the underlying, adjusted for dividends and the risk-free rate. Not adjusted for splits. | derived | T+1 | 2020-01-02 | null on no-volume buckets | `224.48` |
| `atm_iv_30d` | Float64 | annualised vol | `(date, ten_min_timeframe, figi, callput)` | 30-day at-the-money implied vol at the last option trade of the bucket. | derived | T+1 | 2020-01-02 | null on no-volume buckets | `0.2418` |
| `buy_r_shares` / `sell_r_shares` / `net_r_shares` | Float64 | delta-adjusted share equivalents | `(date, ten_min_timeframe, figi, callput)` | Retail buy / sell / net share equivalents within the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `2_410.0` |
| `buy_r_premium` / `sell_r_premium` / `net_r_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Retail dollar premium buy / sell / net. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `31_204.0` |
| `buy_r_delta` / `sell_r_delta` / `net_r_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Retail buy / sell / net flow as USD notional × delta for the (underlying, callput) row within the bucket. Naturally signed by option type; positive `net` = net long-delta demand from retail on this call/put side in the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-387_200.55` |
| `buy_r_gamma` / `sell_r_gamma` / `net_r_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Retail buy / sell / net flow as USD-notional-converted gamma for the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `124_530.30` |
| `buy_r_vega` / `sell_r_vega` / `net_r_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Retail buy / sell / net flow as USD-notional-converted vega for the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `880_140.40` |
| `buy_i_shares` / `sell_i_shares` / `net_i_shares` | Float64 | delta-adjusted share equivalents | `(date, ten_min_timeframe, figi, callput)` | Institutional buy / sell / net share equivalents. `_i + _r + _m = 0` per row. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `12_845.0` |
| `buy_i_premium` / `sell_i_premium` / `net_i_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Institutional dollar premium buy / sell / net. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `41_203.55` |
| `buy_i_delta` / `sell_i_delta` / `net_i_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Institutional buy / sell / net flow as USD notional × delta for the bucket. Positive `net` = net long-delta demand from the institutional class on this call/put side in the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `8_923_100.00` |
| `buy_i_gamma` / `sell_i_gamma` / `net_i_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Institutional buy / sell / net flow as USD-notional-converted gamma for the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `418_870.70` |
| `buy_i_vega` / `sell_i_vega` / `net_i_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Institutional buy / sell / net flow as USD-notional-converted vega for the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `3_120_080.80` |
| `buy_m_shares` / `sell_m_shares` / `net_m_shares` | Float64 | delta-adjusted share equivalents | `(date, ten_min_timeframe, figi, callput)` | Market-maker buy / sell / net share equivalents. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-13_086.0` |
| `buy_m_premium` / `sell_m_premium` / `net_m_premium` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Market-maker dollar premium buy / sell / net. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-82_406.71` |
| `buy_m_delta` / `sell_m_delta` / `net_m_delta` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Market-maker buy / sell / net flow as USD notional × delta for the bucket. Mirror of `_i + _r` on the same row (identity `_i + _r + _m = 0`). | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-8_535_900.00` |
| `buy_m_gamma` / `sell_m_gamma` / `net_m_gamma` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Market-maker buy / sell / net flow as USD-notional-converted gamma for the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-543_330.00` |
| `buy_m_vega` / `sell_m_vega` / `net_m_vega` | Float64 | USD | `(date, ten_min_timeframe, figi, callput)` | Market-maker buy / sell / net flow as USD-notional-converted vega for the bucket. | model | T+1 | 2020-01-02 | 0.0 when no qualifying volume | `-4_001_720.00` |

## Gross-volume accounting

Each trade has a customer side (`buy_{r,i}_shares` / `sell_{r,i}_shares`) and a market-maker counterparty (`buy_m_shares` / `sell_m_shares`, rebuilt to absorb the customer imbalance so the row-level `_i + _r + _m ≈ 0` identity holds on notionals). Same convention applies to `_premium` / `_delta` / `_gamma` / `_vega`.

**DO**: aggregate on `total_shares` — one entry per trade per bucket.

**DON'T**: sum the cohort legs (`buy_r_shares + sell_r_shares + buy_i_shares + sell_i_shares + buy_m_shares + sell_m_shares`) — inflates per bucket volume by ~30-35% because the market-maker leg gets added on top of the customer legs.

---

# underlying_flow_signal_01_day

Daily flow-signal derivatives computed on the underlying-level nets (`net_i_*`, `net_r_*`, greeks included) from `underlying_option_flows_01_day`. Per-`(date, figi, callput)` grain. All fields are trailing-window transforms (no forward-looking information), so a row at `date = D` is safe to use for a decision at close of `D` (or, more conservatively, at open of `D+1`).

- Grain: `(date, figi, callput)` where `callput ∈ {'C', 'P', 'CP'}`.
- History: 2020-01-02 →
- Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday; same publish window as flows).

### Signal design

This is an in-house Masscrest signal built to flag options activity that is extreme against a stock's own history. Each raw daily net is summed over a 21-trading-day trailing window and then z-scored against its own 2-year rolling mean and standard deviation, scoped to `(figi, callput)`.

- `_21dma` = 21-trading-day rolling SUM of the raw daily net, PARTITION BY `(figi, callput)`. (Historically shipped as a rolling mean; arithmetically equivalent for the z-score, since the sum is just the mean multiplied by 21. The distributed series is now the sum.)
- `z_net_*_21dma` = z-score of `_21dma` against its 2-year (504 trading day) trailing mean and sample standard deviation, computed at query time. Minimum 63 non-null observations in the trailing window required, else null. Rounded to 2 decimals on emit.

The raw rolling μ and σ series are computed on the fly at query time and are not distributed as separate columns — only the resulting z-score ships.

The calculation is deliberately simple. Options activity is not stationary across stocks or over time, and z-scores can reach unrealistic multiples of 3 SD during earnings, M&A, or other catalysts. Masscrest does not cap, winsorise, or otherwise regularise the output: keeping the formula plain avoids overfitting to any specific regime and lets clients replicate the transform end-to-end from `underlying_option_flows_01_day` if they want to. Validation of the single-name spike predicate lives at `/research/systematic-single-stock-signal`; the industry-aggregated version and its long-short performance live at `/research/industry-flow-signal`.

### `callput` semantics

Every row is scoped to one callput bucket:

- `C`: calls only. Raw nets and greeks are direct sums of the call-side contracts.
- `P`: puts only. Raw nets and greeks are direct sums of the put-side contracts.
- `CP`: pre-computed combined view. Nets (`shares` / `premium` / `delta`) are `C − P` because bullish call flow and bearish put flow both express bullish conviction, so the nets combine sign-flipped. Greeks (`gamma` / `vega`) are `C + P` because they are naturally signed by option type (calls +, puts −) and summing captures total book exposure.

Rolling windows PARTITION BY `(figi, callput)`; each callput row has its own independent trailing 21dma and 2y baseline, so `z(C) + z(P) ≠ z(CP)`. Filter to exactly one `callput` per query or triple-count.

### Warm-up

All fields are computed from 2020-01-02 forward. The 21-day rolling sum needs 21 non-null daily nets; the z-score needs ≥ 63 non-null observations in the trailing 504-session (2-year) window and emits NULL until that threshold is met.

Transform chain: daily net → 21-day trailing sum (`_21dma`) → z-score against 2y rolling μ/σ of the 21dma (`z_*`).

### Fire semantics live at the consumer

The signal table does not persist `signal_*` (2σ / 3σ fire) or `hp_*` (22-day holding-period) columns; derive them at query time from the z-scores. Masscrest's canonical spike predicate is:

    WHERE z_net_i_delta_21dma > 2 AND callput = 'CP'    -- 2σ institutional-delta spike
    WHERE z_net_i_delta_21dma > 3 AND callput = 'CP'    -- 3σ institutional-delta spike

Both retail (`net_r_*`) and institutional (`net_i_*`) transforms ship. By convention Masscrest treats large institutional-side deviations as the primary tradable spike; the retail-side z-scores are available for symmetric analysis (crowded-consensus vs smart-money-vs-retail divergence checks, see the `crowding_tag` in the `taxonomy` resource).

### Adding liquidity and stock-return context

This table only carries the signal itself, not stock liquidity or stock returns. If you want to filter for liquid names (e.g. "ignore anything under $10M average daily dollar turnover") or overlay stock returns for validation, join `px_01_day` on `(date, figi)`. Dollar turnover (`turnover`) can be averaged directly; for split-adjusted returns from `close`, also join `split_factors`.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `date` | Date | trading day (UTC) | `(date, figi, callput)` | Session date. US equity trading calendar. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24` |
| `figi` | String(12) | identifier | `(date, figi, callput)` | OpenFIGI composite FIGI for the underlying. Stable across ticker changes; join key to `stock_metadata`, `px_01_day`, `split_factors`. | exchange | n/a | 2020-01-02 | never null | `BBG000MM2P62` |
| `underlying_ticker` | String | ticker | `(date, figi, callput)` | Point-in-time listed equity ticker on `date`. Dotted dual-class form (`BRK.B`). Named `underlying_ticker` here rather than `traded_underlying`; the API SELECT aliases it back to `traded_underlying` on the wire. | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `callput` | LowCardinality(String) | enum | `(date, figi, callput)` | One of `'C'`, `'P'`, `'CP'`. See the `callput` note above for the CP synthesis rule. | derived | T+1 | 2020-01-02 | never null | `CP` |
| `net_i_shares` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Institutional net share-equivalent flow for the callput bucket. Calculated as `sum(net_i_qty × adj_shares_deliverable)`: the underlying-share equivalent of the traded contracts (standard OCC contract = 100 shares, adjusted for corporate actions). On `callput='CP'` rows: `C − P` (bullish-conviction sign convention). | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `18_450.0` |
| `net_r_shares` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Retail net share-equivalent flow. Same formula (`sum(net_r_qty × adj_shares_deliverable)`) and CP-synthesis rule as `net_i_shares`. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `4_120.0` |
| `net_i_premium` | Float64 | USD | `(date, figi, callput)` | Institutional net dollar premium flow. On `CP` rows: `C − P`. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `1_820_038.55` |
| `net_r_premium` | Float64 | USD | `(date, figi, callput)` | Retail net dollar premium flow. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `312_040.0` |
| `net_i_delta` | Float64 | USD | `(date, figi, callput)` | Institutional net flow as USD notional × delta. Naturally signed; positive = net long-delta demand. On `CP` rows: `C − P`. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `12_530_000.00` |
| `net_r_delta` | Float64 | USD | `(date, figi, callput)` | Retail net flow as USD notional × delta. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `-870_000.00` |
| `net_i_gamma` | Float64 | USD × gamma | `(date, figi, callput)` | Institutional net flow as USD notional × gamma. On `CP` rows: `C + P` (gamma is naturally signed by option type). Positive = net gamma demand. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `84_000.0` |
| `net_r_gamma` | Float64 | USD × gamma | `(date, figi, callput)` | Retail net gamma flow. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `12_100.0` |
| `net_i_vega` | Float64 | USD × vega | `(date, figi, callput)` | Institutional net flow as USD notional × vega. On `CP` rows: `C + P`. Positive = net long-vol demand. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `1_400_000.0` |
| `net_r_vega` | Float64 | USD × vega | `(date, figi, callput)` | Retail net vega flow. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `-210_000.0` |
| `net_i_shares_21dma` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Rolling sum of `net_i_shares` over the prior 21 trading days (inclusive of `date`), scoped to this `callput`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `319_284.0` |
| `net_r_shares_21dma` | Float64 | underlying-share equivalents | `(date, figi, callput)` | Rolling 21-day sum of `net_r_shares`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `68_040.0` |
| `net_i_premium_21dma` | Float64 | USD | `(date, figi, callput)` | Rolling 21-day sum of `net_i_premium`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `25_294_500.0` |
| `net_r_premium_21dma` | Float64 | USD | `(date, figi, callput)` | Rolling 21-day sum of `net_r_premium`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `5_040_252.0` |
| `net_i_delta_21dma` | Float64 | USD | `(date, figi, callput)` | Rolling 21-day sum of `net_i_delta`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `206_220_000.0` |
| `net_r_delta_21dma` | Float64 | USD | `(date, figi, callput)` | Rolling 21-day sum of `net_r_delta`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `-13_020_000.0` |
| `net_i_gamma_21dma` | Float64 | USD × gamma | `(date, figi, callput)` | Rolling 21-day sum of `net_i_gamma`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `1_512_000.0` |
| `net_r_gamma_21dma` | Float64 | USD × gamma | `(date, figi, callput)` | Rolling 21-day sum of `net_r_gamma`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `218_400.0` |
| `net_i_vega_21dma` | Float64 | USD × vega | `(date, figi, callput)` | Rolling 21-day sum of `net_i_vega`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `24_780_000.0` |
| `net_r_vega_21dma` | Float64 | USD × vega | `(date, figi, callput)` | Rolling 21-day sum of `net_r_vega`. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | `-3_780_000.0` |
| `z_net_i_shares_21dma` | Float64 | z-score | `(date, figi, callput)` | Z-score of `net_i_shares_21dma` against its trailing 2-year rolling mean and sample standard deviation, computed at query time. Rounded to 2 decimals on emit. | derived | T+1 | 2020-01-02 | null when the trailing 2y window has fewer than 63 non-null observations or its sample stddev is 0 | `1.34` |
| `z_net_r_shares_21dma` | Float64 | z-score | `(date, figi, callput)` | Same construction as `z_net_i_shares_21dma`, applied to the retail 21dma. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `0.42` |
| `z_net_i_premium_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised institutional-premium 21dma vs its 2y baseline. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `1.18` |
| `z_net_r_premium_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised retail-premium 21dma vs its 2y baseline. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `-0.24` |
| `z_net_i_delta_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised institutional-delta 21dma vs its 2y baseline. Masscrest's canonical "spike" predicate is `z > 2` (2σ) or `z > 3` (3σ) with `callput = 'CP'`. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `2.08` |
| `z_net_r_delta_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised retail-delta 21dma vs its 2y baseline. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `-0.55` |
| `z_net_i_gamma_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised institutional-gamma 21dma vs its 2y baseline. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `0.86` |
| `z_net_r_gamma_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised retail-gamma 21dma vs its 2y baseline. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `0.11` |
| `z_net_i_vega_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised institutional-vega 21dma vs its 2y baseline. Masscrest's ≤ −3σ vega tail is historically followed by ~1.6 vol-point IV declines over 21 days (see `research/vega-iv-signal`). | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `-1.42` |
| `z_net_r_vega_21dma` | Float64 | z-score | `(date, figi, callput)` | Standardised retail-vega 21dma vs its 2y baseline. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | `0.30` |

---

### Z-score precision

`z_*` fields are rounded to 2 decimals on emit. When comparing a client-side derived spike predicate (`z > 2`) against the rounded value, expect edge cases on low-vol names where the true unrounded z sits just under 2.00 but the rounded value equals 2.00.

### CP synthesis is pre-computed, not query-time

The `'CP'` rows are materialised at publish time, not derived at query time. Each `(figi, callput)` bucket has its own independent 21dma and 2y baseline trajectory; `z_net_i_delta_21dma` on the `CP` row is derived from `CP`'s own trailing series, not from summing per-side z-scores.

### Deriving fire flags and holding periods

The table does not persist pre-computed fire flags (e.g. "2σ spike today = TRUE") or holding-period booleans. Compute them at query time from the z-scores: `WHERE z_net_i_delta_21dma > 2 AND callput = 'CP'` for the canonical 2σ institutional-delta spike. For a 22-day holding-period rule (fire within the last N days and the same z has not gone negative since), a self-join or a rolling `max(z)` window on the same series is enough.

---

# grouped_flow_signal_01_day

Group-level daily flow-signal derivatives: sector / industry / etf / adr / single_stock / all rollups of the underlying-level nets and greeks aggregated in `underlying_flow_signal_01_day`. Same 50-flow-metric shape across 5 layers × 10 metrics. Per-`(date, group_key, callput)` grain.

- Grain: `(date, group_key, callput)`
- History: 2020-01-02 →
- Timing: T+1 (published shortly after `underlying_flow_signal_01_day` on the option-analytics workflow, typically 06:00-11:00 UTC on the calendar day after the trading date — Friday's data lands Saturday).

## Response-shape rewrite (2026-08-14)

Every row is keyed by a **single `group_key` STRING column**. `group_type` on the REST endpoint takes the same set of canonical values (case-insensitive on input) — a mixed-case sector name (e.g. `"Communication Services"`), a mixed-case industry name (e.g. `"Semiconductors"`), or one of the rollup literals `"etf"` / `"adr"` / `"single_stock"` / `"all"`. The row echoes that value back in `group_key`.

## Group semantics

| Query `group_type` | `group_key` shape (on the row) | Row count per (date, callput) | Notes |
|---|---|---|---|
| `sector` | Canonical mixed-case sector name (e.g. `Technology`, `Financial Services`) | ~11 distinct values | **Excludes ETFs and ADRs.** Figis missing a sector label drop from these rows. |
| `industry` | Canonical mixed-case industry name (e.g. `Semiconductors`, `Software - Application`) | ~150 distinct values (matches the FMP industry taxonomy) | **Excludes ETFs and ADRs.** Same drop-on-missing rule. Every industry-day is emitted (gap-free time series) with `n_figi` populated for downstream filtering — see "Thin-industry handling" below. |
| `etf` | Literal string `etf` | 1 | Aggregate over all figis with `isEtf='true'` in the latest-known phase (live or delisted). |
| `adr` | Literal string `adr` | 1 | Aggregate over all figis with `isAdr='true'` in the latest-known phase (live or delisted). |
| `single_stock` | Literal string `single_stock` | 1 | Everything else (non-ETF, non-ADR). Figis missing from `stock_metadata` are treated as `single_stock`. |
| `all` | Literal string `all` | 1 | Every figi in the flow universe, no filter. |

### Why sector / industry exclude ETFs and ADRs

FMP tags each ETF's `sector` with the issuer's legal-entity sector (typically `Financial Services` for the sponsor, not the underlying-exposure sector). Pooling ETFs into sector rollups inflated `Financial Services` gross flow by 85%+ — meaningless for cohort analysis. Same-shape distortion for ADRs. The 2026-08-11 pipeline change excludes both from `sector` and `industry` aggregations; they get their own top-level `etf` and `adr` branches. As a consequence, `sum(sector) ≠ all` — the residual is precisely the `etf + adr` contribution.

## Valid sector values

The ~11 distinct `group_key` strings emitted when `group_type = 'sector'`. Case-insensitive on input; canonical mixed-case on the wire. As-of 2026-08-14 snapshot; may drift as figis are reclassified or new sectors are added.

| Sector | Description |
|---|---|
| `Basic Materials` | Companies extracting and processing raw commodities used across the economy: metals and mining, chemicals, forestry, and construction materials. Cyclically exposed to global industrial demand. |
| `Communication Services` | Firms that carry information or entertainment to end users: telecom carriers, media conglomerates, interactive-media platforms, publishers, and gaming. Combines legacy telecom with digital-native content. |
| `Consumer Cyclical` | Businesses selling discretionary goods and services whose demand rises and falls with the business cycle: automakers, apparel, restaurants, travel, leisure, and specialty retail. Highly sensitive to consumer confidence. |
| `Consumer Defensive` | Producers of everyday essentials that consumers buy regardless of the economy: packaged food, beverages, tobacco, household staples, and discount retailers. Stable revenue through downturns. |
| `Energy` | The oil, gas, coal and related supply chain: exploration and production, refining, midstream infrastructure, and oilfield services. Tied to commodity prices and global demand cycles. |
| `Financial Services` | Firms that intermediate capital: banks, insurers, asset managers, brokerages, exchanges, and credit-services companies. Rate-sensitive and cycle-exposed. |
| `Healthcare` | Businesses across the health value chain: drug and biotech developers, medical-device makers, diagnostics labs, healthcare providers, health insurers, and pharma distributors. Mix of defensive demand and secular growth. |
| `Industrials` | Capital goods and services that keep the economy moving: aerospace, defence, machinery, transportation, construction, and logistics. Cyclically exposed to capex spending. |
| `Real Estate` | Property-owning and property-servicing companies, dominated by REITs across residential, retail, office, industrial, healthcare, and specialty sub-types. Highly interest-rate-sensitive. |
| `Technology` | Hardware and software firms including semiconductors, application and infrastructure software, IT services, computer hardware, and consumer electronics. Growth-biased, with high cyclicality on the semiconductor side. |
| `Utilities` | Regulated and independent providers of electricity, natural gas, water, and renewable power. Defensive cash flows, bond-like return profile. |

## Valid industry values

The ~150 distinct `group_key` strings emitted when `group_type = 'industry'`. Case-insensitive on input; canonical mixed-case on the wire. As-of 2026-08-14 snapshot; may drift as existing figis are reclassified or new industries are added. For the live authoritative list, call the grouped-flow-signal endpoint with `group_type=industry` and take the distinct `group_key` values from the response.

Every industry with any flow on a given date is emitted (2026-08-28 revision — the time series is gap-free). Every industry-day carries populated raw daily nets AND populated rolling / z fields; `n_figi` is exposed on the row so consumers can filter thin-breadth days at query time. See "Thin-industry handling" below for the z-magnitude comparability caveat on structurally-thin industries such as `Copper`, `Uranium`, `Consulting Services`, `Regulated Water`, `Conglomerates`, `Waste Management`, and `Railroads`.

| Industry | Description |
|---|---|
| `Advertising Agencies` | Agencies that plan, create and place advertising across traditional and digital channels for brand and direct-response clients. |
| `Aerospace & Defense` | Manufacturers of civil aircraft, military platforms, missiles, satellites and defence-electronics systems for governments and commercial airlines. |
| `Agricultural - Machinery` | Producers of tractors, combines, harvesters and other equipment used in commercial farming. |
| `Agricultural Farm Products` | Growers, processors and marketers of grains, oilseeds, produce, meat and other primary agricultural output. |
| `Agricultural Inputs` | Suppliers of seeds, fertilisers, pesticides and other inputs consumed by farms. |
| `Airlines, Airports & Air Services` | Passenger and cargo airlines, airport operators, ground-handling firms and other air-transport service providers. |
| `Aluminum` | Miners, smelters and rolled-product manufacturers of aluminium. |
| `Apparel - Footwear & Accessories` | Designers and makers of shoes, handbags, jewellery, watches and other fashion accessories. |
| `Apparel - Manufacturers` | Companies that design and produce clothing lines under owned or licensed brands. |
| `Apparel - Retail` | Retailers selling clothing and accessories through stores or online, including specialty and off-price chains. |
| `Asset Management` | Firms managing pooled investment vehicles (mutual funds, ETFs, separate accounts) across broad multi-asset strategies. |
| `Asset Management - Bonds` | Managers specialising in fixed-income mutual funds and bond ETFs. |
| `Asset Management - Cryptocurrency` | Managers of cryptocurrency-linked funds, trusts and ETFs. |
| `Asset Management - Global` | Managers of internationally-diversified equity and multi-asset funds. |
| `Asset Management - Income` | Managers focused on dividend-equity and income-oriented fund products. |
| `Asset Management - Leveraged` | Sponsors of leveraged and inverse ETFs. |
| `Auto - Dealerships` | Retailers of new and used vehicles, plus related service, parts and financing operations. |
| `Auto - Manufacturers` | OEMs producing passenger cars, trucks and, increasingly, electric vehicles. |
| `Auto - Parts` | Suppliers of components, systems and modules used in vehicle assembly and aftermarket repair. |
| `Auto - Recreational Vehicles` | Makers of RVs, motorcycles, boats and other personal-use motorised recreational vehicles. |
| `Banks - Diversified` | Large multi-line banks combining consumer, commercial, investment-banking and wealth divisions. |
| `Banks - Regional` | Banks concentrated in a specific geographic footprint, focused on retail deposits and commercial lending. |
| `Beverages - Alcoholic` | Brewers, distillers and vintners producing beer, spirits and packaged alcoholic drinks at scale. |
| `Beverages - Non-Alcoholic` | Producers of soft drinks, bottled water, juices, energy drinks and other non-alcoholic packaged beverages. |
| `Beverages - Wineries & Distilleries` | Craft and specialty wine and spirits producers, generally smaller-scale than the mass-market alcoholic-beverage majors. |
| `Biotechnology` | Companies developing novel therapeutics using biological processes, typically pre-commercial or single-product-focused. |
| `Broadcasting` | Owners of TV and radio stations, local networks and syndicated content distributors. |
| `Business Equipment & Supplies` | Manufacturers of office equipment, printers, copiers and workplace supplies. |
| `Chemicals` | Producers of commodity petrochemicals, plastics, industrial gases and basic chemical intermediates. |
| `Chemicals - Specialty` | Producers of higher-value differentiated chemicals used in coatings, adhesives, catalysts and formulations. |
| `Coal` | Miners and marketers of thermal and metallurgical coal. |
| `Communication Equipment` | Makers of network hardware, routers, switches and telecom infrastructure gear. |
| `Computer Hardware` | Manufacturers of PCs, servers, storage systems and other computing hardware. |
| `Conglomerates` | Multi-industry holding companies spanning several unrelated business lines. |
| `Construction` | General contractors and construction firms building commercial, industrial and infrastructure projects. |
| `Construction Materials` | Producers of cement, aggregates, gypsum, insulation and other bulk building materials. |
| `Consulting Services` | Management, technology, HR and strategy consulting firms selling professional advisory services. |
| `Consumer Electronics` | Makers of smartphones, tablets, audio devices, wearables and other consumer-facing electronic products. |
| `Copper` | Miners and processors of copper ore and refined copper products. |
| `Department Stores` | Multi-category retailers under a single store format spanning apparel, home and accessories. |
| `Discount Stores` | Mass-market retailers competing primarily on price across broad general merchandise. |
| `Diversified Utilities` | Utilities operating across multiple regulated categories (electric, gas, water) rather than a single service. |
| `Drug Manufacturers - General` | Large pharmaceutical companies with broad marketed portfolios and multi-therapeutic pipelines. |
| `Drug Manufacturers - Specialty & Generic` | Pharma companies focused on generic drugs, biosimilars or niche specialty therapeutic areas. |
| `Education & Training Services` | For-profit universities, career schools, tutoring and corporate training providers. |
| `Electrical Equipment & Parts` | Manufacturers of electrical components, motors, transformers, cables and industrial electrical systems. |
| `Electronic Gaming & Multimedia` | Video-game publishers, developers, esports operators and interactive-entertainment firms. |
| `Engineering & Construction` | Firms providing engineering-design and heavy-construction services for large infrastructure projects. |
| `Entertainment` | Film, TV and content-production studios and integrated entertainment companies. |
| `Environmental Services` | Waste-water treatment, environmental consulting and remediation service providers. |
| `Financial - Capital Markets` | Investment banks, broker-dealers and trading firms operating in equity, fixed-income and derivatives markets. |
| `Financial - Conglomerates` | Diversified financial holding companies spanning several finance subsectors. |
| `Financial - Credit Services` | Consumer-finance companies, credit-card networks, payment processors and buy-now-pay-later providers. |
| `Financial - Data & Stock Exchanges` | Exchange operators, index providers, financial-data vendors and market-infrastructure firms. |
| `Financial - Diversified` | Miscellaneous financial-services companies that do not fit the more specific finance subsectors. |
| `Financial - Mortgages` | Mortgage originators, servicers and secondary-market intermediaries. |
| `Food Confectioners` | Producers of chocolate, candy, chewing gum and other confectionery products. |
| `Food Distribution` | Wholesalers distributing food and related goods to restaurants, retailers and institutional customers. |
| `Furnishings, Fixtures & Appliances` | Makers of home furniture, bedding, kitchen appliances and household fixtures. |
| `Gambling, Resorts & Casinos` | Operators of casinos, integrated resorts and online-gambling platforms. |
| `General Transportation` | Diversified transportation companies that do not fit the more specific rail, trucking or air subsectors. |
| `Gold` | Miners and refiners of gold ore and physical gold, plus gold-focused streaming and royalty firms. |
| `Grocery Stores` | Traditional supermarkets and grocery chains selling food and household goods. |
| `Hardware, Equipment & Parts` | General hardware and industrial-equipment manufacturers and distributors. |
| `Home Improvement` | Big-box home-improvement retailers and specialty tool, paint and hardware chains. |
| `Household & Personal Products` | Manufacturers of cleaning products, personal-care items and consumer packaged household goods. |
| `Independent Power Producers` | Non-utility power generators selling electricity into wholesale or contracted markets. |
| `Industrial - Distribution` | Wholesalers of industrial equipment, parts, fasteners and MRO supplies. |
| `Industrial - Infrastructure Operations` | Operators of ports, pipelines, terminals and other industrial-infrastructure assets. |
| `Industrial - Machinery` | Manufacturers of heavy machinery for construction, mining, agriculture and industrial processes. |
| `Industrial - Pollution & Treatment Controls` | Providers of air, water and industrial pollution-control equipment and services. |
| `Industrial - Specialties` | Specialty industrial firms in niche categories that do not fit broader industrial subsectors. |
| `Industrial Materials` | Producers of steel-alternatives, industrial ceramics, composites and other engineered materials. |
| `Information Technology Services` | IT-consulting, systems-integration, outsourcing and managed-services firms. |
| `Insurance - Brokers` | Insurance and reinsurance brokers acting as intermediaries between clients and underwriters. |
| `Insurance - Diversified` | Multi-line insurers writing across life, P&C and other coverage types. |
| `Insurance - Life` | Insurers focused on individual and group life insurance, plus annuity products. |
| `Insurance - Property & Casualty` | Insurers writing property, auto, liability and other short-tail coverage. |
| `Insurance - Reinsurance` | Firms providing insurance to primary insurers to cover concentrated or catastrophic risk. |
| `Insurance - Specialty` | Insurers focused on niche or hard-to-place risks (marine, aviation, cyber, professional liability). |
| `Integrated Freight & Logistics` | Multi-modal freight and logistics operators spanning trucking, rail, air and ocean. |
| `Internet Content & Information` | Digital-content platforms, search engines, social networks and online-information providers. |
| `Investment - Banking & Investment Services` | Full-service investment banks and firms providing M&A, underwriting and advisory services. |
| `Leisure` | Manufacturers of leisure goods, hobby products, toys and personal-recreation equipment. |
| `Luxury Goods` | Producers of high-end fashion, jewellery, watches, leather goods and other luxury consumer categories. |
| `Manufacturing - Metal Fabrication` | Firms fabricating metal parts, structures and assemblies for industrial and consumer use. |
| `Manufacturing - Miscellaneous` | Diversified manufacturers that do not fit more specific industrial subsectors. |
| `Manufacturing - Textiles` | Producers of yarn, fabric and finished textiles for apparel and industrial applications. |
| `Manufacturing - Tools & Accessories` | Makers of power tools, hand tools and related industrial and consumer tool accessories. |
| `Marine Shipping` | Ocean-freight carriers operating tankers, dry-bulk vessels and container ships. |
| `Media & Entertainment` | Diversified media conglomerates spanning television, film, publishing and digital content. |
| `Medical - Care Facilities` | Hospital operators, nursing-home chains and specialty care-facility providers. |
| `Medical - Devices` | Manufacturers of medical implants, surgical tools, monitoring and therapeutic devices. |
| `Medical - Diagnostics & Research` | Clinical-diagnostics labs, life-science research tools and diagnostic-imaging providers. |
| `Medical - Distribution` | Wholesale distributors of drugs, medical supplies and healthcare products. |
| `Medical - Equipment & Services` | Providers of medical equipment plus related installation, maintenance and services. |
| `Medical - Healthcare Information Services` | Health-IT firms providing electronic health records, clinical software and data-analytics platforms. |
| `Medical - Healthcare Plans` | Managed-care organisations and health-insurance plans covering employer, individual and government populations. |
| `Medical - Instruments & Supplies` | Manufacturers of medical instruments, consumables and disposable healthcare supplies. |
| `Medical - Pharmaceuticals` | Broadly-focused pharmaceutical companies not classified under the more specific drug-manufacturer subsectors. |
| `Medical - Specialties` | Specialty medical companies operating in niche healthcare categories. |
| `Oil & Gas Drilling` | Contract drilling firms operating onshore and offshore rigs for oil-and-gas producers. |
| `Oil & Gas Energy` | Diversified oil-and-gas firms that do not fit the more specific upstream, midstream or downstream categories. |
| `Oil & Gas Equipment & Services` | Oilfield-services providers offering drilling, completion and reservoir-management equipment and expertise. |
| `Oil & Gas Exploration & Production` | Upstream producers exploring for and producing crude oil and natural gas. |
| `Oil & Gas Integrated` | Integrated majors operating across upstream, midstream and downstream oil-and-gas businesses. |
| `Oil & Gas Midstream` | Pipelines, storage terminals and processing operators moving oil and gas from wellhead to market. |
| `Oil & Gas Refining & Marketing` | Downstream refiners and fuel-marketing companies converting crude into gasoline, diesel and petrochemical feedstocks. |
| `Other Precious Metals` | Miners of platinum, palladium and other precious metals not classified under gold or silver. |
| `Packaged Foods` | Producers of branded packaged and processed foods sold through retail and foodservice channels. |
| `Packaging & Containers` | Manufacturers of paper, plastic, glass and metal packaging for consumer and industrial products. |
| `Paper, Lumber & Forest Products` | Producers of pulp, paper, lumber and other wood-based industrial and consumer products. |
| `Personal Products & Services` | Personal-care product makers, beauty firms and personal-services businesses. |
| `Publishing` | Publishers of books, newspapers, magazines and other periodical content in print and digital. |
| `REIT - Diversified` | REITs owning property portfolios spanning multiple real-estate categories. |
| `REIT - Healthcare Facilities` | REITs owning hospitals, medical-office buildings, senior housing and other healthcare properties. |
| `REIT - Hotel & Motel` | REITs owning hotel, motel and hospitality properties. |
| `REIT - Industrial` | REITs owning warehouses, logistics facilities and light-industrial properties. |
| `REIT - Mortgage` | Mortgage REITs earning spread on residential and commercial mortgage assets rather than owning property directly. |
| `REIT - Office` | REITs owning office buildings across urban and suburban markets. |
| `REIT - Residential` | REITs owning apartment buildings, single-family rentals and manufactured-home communities. |
| `REIT - Retail` | REITs owning shopping malls, strip centres and standalone retail properties. |
| `REIT - Specialty` | REITs owning niche property types such as data centres, cell towers, self-storage and infrastructure. |
| `Railroads` | Class I and short-line freight-rail operators plus passenger-rail companies. |
| `Real Estate - Development` | Property developers building residential, commercial and mixed-use projects for sale or lease. |
| `Real Estate - Diversified` | Diversified real-estate operating companies not structured as REITs. |
| `Real Estate - Services` | Real-estate brokerages, property-management firms and title-and-appraisal services. |
| `Regulated Electric` | Rate-regulated electric utilities serving retail customers in defined service territories. |
| `Regulated Gas` | Rate-regulated natural-gas distribution utilities. |
| `Regulated Water` | Rate-regulated water and wastewater utilities. |
| `Renewable Utilities` | Utilities and power generators focused on solar, wind, hydro and other renewable-energy assets. |
| `Rental & Leasing Services` | Firms renting equipment, vehicles and other assets to industrial and consumer customers. |
| `Residential Construction` | Homebuilders constructing single-family homes and residential communities for sale. |
| `Restaurants` | Restaurant operators and franchisors across quick-service, casual-dining and fine-dining categories. |
| `Security & Protection Services` | Providers of guarding, cash-in-transit, alarm-monitoring and security-technology services. |
| `Semiconductors` | Designers and manufacturers of integrated circuits, memory, logic chips and related semiconductor equipment. |
| `Shell Companies` | Publicly-listed holding entities without significant operations, often SPACs or blank-check vehicles. |
| `Silver` | Miners and refiners of silver ore and silver products. |
| `Software - Application` | Vendors of packaged and SaaS application software for business, industry-vertical and consumer use. |
| `Software - Infrastructure` | Vendors of infrastructure software including databases, operating systems, security and developer tools. |
| `Software - Services` | Software-enabled services firms delivering platform-hosted business solutions. |
| `Solar` | Manufacturers of solar panels, inverters and installers of solar-power systems. |
| `Specialty Business Services` | Business-services firms in specialty categories (data processing, marketing services, testing, inspection). |
| `Specialty Retail` | Retailers focused on specific product categories such as electronics, sporting goods, jewellery or auto parts. |
| `Staffing & Employment Services` | Temporary-staffing, executive-search and human-capital-management firms. |
| `Steel` | Integrated and mini-mill producers of carbon and specialty steel products. |
| `Technology Distributors` | Distributors of IT hardware, software and networking products to resellers and enterprise buyers. |
| `Telecommunications Services` | Wireless and wireline telecom carriers providing voice and data services to consumers and businesses. |
| `Tobacco` | Cigarette manufacturers plus producers of cigars, smokeless tobacco and next-generation nicotine products. |
| `Travel Lodging` | Hotel and lodging operators and franchisors across the value, mid-scale and luxury segments. |
| `Travel Services` | Online travel agencies, tour operators and travel-booking platforms. |
| `Trucking` | Long-haul and less-than-truckload freight-trucking companies. |
| `Uranium` | Miners of uranium ore and producers of nuclear-fuel feedstock. |
| `Waste Management` | Solid-waste collection, disposal, recycling and hazardous-waste management firms. |

## Signal formula

- `_21dma` = 21-trading-day rolling SUM of the raw group-level net, PARTITION BY `(group_key, callput)`. (Historically shipped as a rolling mean; arithmetically equivalent for the z-score, since the sum is just the mean multiplied by 21. The distributed series is now the sum.)
- `z_net_*_21dma` = z-score of `_21dma` against its 2-year (504 trading day) trailing mean and sample standard deviation, computed at query time. Minimum 63 non-null observations in the trailing window required, else null. Rounded to 2 decimals on emit.

The raw rolling μ and σ series are computed on the fly at query time and are not distributed as separate columns — only the resulting z-score ships.

Every industry-day is emitted with populated rolling / z fields. The 21dma and 2y baseline include ALL days regardless of `n_figi` — thin days contribute their (smaller) raw values to the trailing window, so each industry's z is calibrated against its own historical breadth mix and populates on every day. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully — filter on `n_figi` at query time if comparability matters. See "Thin-industry handling" below.

## Aggregate identities

- `single_stock + etf + adr = all` per (date, callput), 0 residual.
- `sum(sector) ≈ single_stock` per (date, callput); small residual (< 1% of gross flow) attributable to single-stock figis without a sector label.
- Because sector / industry exclude ETFs and ADRs, `sum(sector) ≠ all` — the difference is precisely the `etf + adr` gross flow. Historical rollups produced before 2026-08-11 rolled ETFs+ADRs into sector; those runs have been superseded.
- `sum(industry) ≈ single_stock` per (date, callput). Every industry row (thin or full-breadth) contributes its raw flow to the sum, so this identity holds tighter than it did before 2026-08-28.

## Thin-industry handling

Every group with any flow on a given date is emitted — the time series is gap-free. Every row (thin or full-breadth) carries populated raw daily nets AND populated rolling / z fields:

- `n_figi` is populated with the honest constituent count (visible in the response) — consumers filter thin-breadth days at query time.
- Raw daily nets (`net_i_shares`, `net_r_shares`, …, `net_r_vega`) are populated with the actual group sum.
- Rolling / derived columns (`_21dma`, `z_*`) are populated on every row. The 21dma and 2y baseline include ALL days regardless of `n_figi` — thin days contribute their (smaller) raw values to the trailing window and to the 2y μ/σ used to z-score. Each industry's z-score is therefore calibrated against its own historical breadth mix — a chronically-thin industry's baseline is set to its own thin-day scale, so z fires when flow is unusually large *for that industry*. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully (a +2σ on a 5-FIGI industry is not directly comparable to a +2σ on a 50-FIGI industry). Filter on `n_figi` at query time when magnitude comparability matters.

Sector, etf, adr, single_stock, and all branches always exceed 10 contributing FIGIs so the caveat is a no-op for those branches. Only industries — historically ~46 including `Copper`, `Uranium`, `Consulting Services`, `Regulated Water`, `Conglomerates`, `Waste Management`, `Railroads` — are materially affected. Before 2026-08-28 those industries were dropped entirely and visible as gaps in the served time series; now they emit continuous rows with populated raw flow AND populated (but caveated) z-scores. Live coverage: 11 sectors + ~150 industries + 4 rollups (`all`, `etf`, `adr`, `single_stock`) — every group with flow that day appears in the cross-section.

## Classification is not strictly PIT

The underlying flow data is point-in-time, but each figi's **sector**, **industry**, and **ETF / ADR** classification uses the most recent labelling rather than the label in force at the time. META, for example, rolls up under `Communication Services` for its entire history even though it was classified as `Technology` in 2019. This keeps historical rollups comparable under a single consistent scheme as companies drift between sectors over time.

## `callput` semantics

Every row is scoped to one callput bucket. `C` = calls only, `P` = puts only, `CP` = pre-computed combined view (nets are `C − P` because bullish call flow and bearish put flow both express bullish conviction, so nets combine sign-flipped; greeks are `C + P` because they are naturally signed by option type). Rolling windows partition by `(group_key, callput)`; each callput row has its own independent trailing 21dma and 2y baseline, so `z(C) + z(P) ≠ z(CP)`. Filter to exactly one `callput` per query or triple-count.

## Warmup NULL conventions

- `..._21dma` NULL when a `(group_key, callput)` has < 21 days of history in the trailing 21-row window.
- `z_..._21dma` NULL when the 21dma series has < 63 non-null observations in the trailing 504-session (2-year) window, or the window's sample stddev is 0.
- These are legitimate NULLs; do not fill.

## REST endpoints

- `GET /v0/grouped_flow_signal/01_day`: ranged. Requires `group_type` = a single canonical `group_key` value (sector / industry name or one of the rollup literals `etf` / `adr` / `single_stock` / `all`; case-insensitive on input, fuzzy-suggestion on unknown values). Returns rows for a date range at that one key.
- `GET /v0/grouped_flow_signal/01_day/snapshot`: single-day cross-sectional. All filters optional. `date` defaults to latest. Omit both `category` and `group_type` → every `group_key` with any flow on the day (up to ~168 rows on the default `callput = 'CP'`). `category` (snapshot-only: `industry` / `sector` / `rollup` / `all`) narrows to a whole family in one call. `group_type` (single canonical `group_key` value) narrows to one key. `category` and `group_type` are mutually exclusive.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `date` | Date | trading day (UTC) | `(date, group_key, callput)` | Session date. US equity trading calendar. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24` |
| `group_key` | LowCardinality(String) | text | key | Canonical mixed-case sector or industry name (e.g. `Communication Services`, `Semiconductors`); or the literal `etf` / `adr` / `single_stock` / `all` for the rollup branches. Single key column — the same value you pass in the REST `group_type` query parameter. | derived | T+1 | 2020-01-02 | never null | `Technology` |
| `callput` | LowCardinality(String) | enum | key | One of `C`, `P`, `CP`. See the `callput` note above. | derived | T+1 | 2020-01-02 | never null | `CP` |
| `n_figi` | Nullable(Int64) | count | `(date, group_key, callput)` | Number of distinct FIGIs contributing to this group-day. Every row is emitted with raw nets AND rolling / z fields populated; the 21dma and 2y baseline include all days regardless of `n_figi`, so each industry's z is calibrated against its own historical breadth mix. Consumers filter on `n_figi` at query time when z-magnitude comparability across breadth matters. Sector, etf, adr, single_stock, and all branches always carry n_figi >> 10; only certain industries can dip below. | derived | T+1 | 2020-01-02 | never null in practice | `24` |
| `net_i_shares` | Float64 | delta-adjusted share equivalents | `(date, group_key, callput)` | Institutional net share-equivalent flow, summed across every figi in this grouping. On `CP` rows: `C − P` (bullish-conviction sign convention). Populated on every emitted row (even `n_figi < 10` days). | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `1_820_000.0` |
| `net_r_shares` | Float64 | delta-adjusted share equivalents | `(date, group_key, callput)` | Retail net share-equivalent flow, same CP-synthesis rule. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `412_000.0` |
| `net_i_premium` | Float64 | USD | `(date, group_key, callput)` | Institutional net dollar premium flow. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `182_000_000.0` |
| `net_r_premium` | Float64 | USD | `(date, group_key, callput)` | Retail net dollar premium flow. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `31_000_000.0` |
| `net_i_delta` | Float64 | USD | `(date, group_key, callput)` | Institutional net USD × delta flow. Positive = net long-delta demand across the grouping. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `1_250_000_000.0` |
| `net_r_delta` | Float64 | USD | `(date, group_key, callput)` | Retail net USD × delta flow. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `-87_000_000.0` |
| `net_i_gamma` | Float64 | USD × gamma | `(date, group_key, callput)` | Institutional net USD × gamma. On `CP` rows: `C + P`. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `8_400_000.0` |
| `net_r_gamma` | Float64 | USD × gamma | `(date, group_key, callput)` | Retail net USD × gamma. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `1_210_000.0` |
| `net_i_vega` | Float64 | USD × vega | `(date, group_key, callput)` | Institutional net USD × vega. On `CP` rows: `C + P`. Positive = net long-vol demand across the grouping. | model | T+1 | 2020-01-02 | 0.0 when no qualifying institutional flow | `140_000_000.0` |
| `net_r_vega` | Float64 | USD × vega | `(date, group_key, callput)` | Retail net USD × vega. | model | T+1 | 2020-01-02 | 0.0 when no qualifying retail flow | `-21_000_000.0` |
| `net_*_21dma` | Float64 | (matches base metric) | `(date, group_key, callput)` | 21-trading-day rolling SUM of the corresponding raw net, scoped to this `(group_key, callput)`. Ten of these, one per raw metric. | derived | T+1 | 2020-01-02 | null when the trailing 21-day window has fewer than 21 non-null observations | (varies by metric) |
| `z_net_*_21dma` | Float64 | z-score | `(date, group_key, callput)` | Z-score of the corresponding 21dma against its trailing 2-year (504-session) rolling mean and sample standard deviation, computed at query time. Rounded to 2 decimals on emit. Masscrest's canonical spike predicate for the group-level table is `z_net_i_delta_21dma > 2 AND callput = 'CP'` (or `> 3` for 3σ), same rule as the underlying-level table. Ten of these. | derived | T+1 | 2020-01-02 | null when the trailing 2y window is unwarmed or its sample stddev is 0 | (varies) |

---

### CP synthesis is pre-computed, not query-time

Same rule as `underlying_flow_signal_01_day`: the `'CP'` rows are materialised at publish time, and each `(group_key, callput)` bucket has its own independent 21dma and 2y baseline trajectory.

### Group classification is snapshot-only

Load-bearing, worth repeating: the sector / industry / etf / adr / single_stock labels used to route flow into each row come from each figi's latest-known `stock_metadata` phase (current phase for live names, last-live phase for delisted names) applied across every historical date. Do NOT use this table for PIT backtests where a stock's historical group membership matters; use `underlying_flow_signal_01_day` and roll up client-side.

---

# px_01_day

Daily close (unadjusted) for the covered US single-stock and ETF universe, keyed on FIGI.

**Access:** this table is not directly queryable via the MCP or REST API. Split-adjusted price columns (`adj_close`, `adj_open`, `adj_volume`, `total_turnover`) are returned as LEFT-JOIN columns on `get_underlying_option_flows_daily` (MCP) and `GET /v0/underlying_option_flows/01_day` (REST). Use those endpoints for daily price context — passing `traded_underlying=<TICKER>` and `callput='CP'` returns one row per date with prices attached to the flow payload. For bulk historical pulls, the parquet flat-file surface is at `gs://prod-masscrest/v0/px_data/01_day/`.

- Grain: `(date, figi, symbol)` [1]
- History: 2020-01-02 →
- Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

[1] The underlying storage carries `symbol` to preserve point-in-time ticker context, but every join to Masscrest surfaces should use `figi`.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `date` | Date | trading day (UTC) | `(date, figi)` | Session date. US equity trading calendar. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24` |
| `figi` | String(12) | identifier | `(date, figi)` | OpenFIGI composite FIGI for the underlying. Stable across ticker changes; join key to every other Masscrest table. | exchange | n/a | 2020-01-02 | never null | `BBG000MM2P62` |
| `symbol` | String | ticker | `(date, figi)` | Point-in-time listed ticker on `date`. Dotted dual-class form (`BRK.B`). Preserved for readability; **do not use as a join key** (the same ticker can be recycled across entities over time). | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `open` | Float64 | USD | `(date, figi)` | Session opening print (unadjusted, contemporaneous). No split adjustment applied. | exchange | T+1 | 2020-01-02 | null on non-trading days for the underlying (delistings, halts) | `223.85` |
| `close` | Float64 | USD | `(date, figi)` | Session closing print (unadjusted, contemporaneous). No split adjustment applied. | exchange | T+1 | 2020-01-02 | null on non-trading days for the underlying | `224.31` |
| `volume` | Float64 | shares | `(date, figi)` | Session share volume (unadjusted, contemporaneous). No split adjustment applied (a share count on a pre-split day is a real economic quantity that doesn't rescale post-split). | exchange | T+1 | 2020-01-02 | null on non-trading days | `52_308_400.0` |
| `turnover` | Float64 | USD | `(date, figi)` | Dollar turnover of the underlying on `date` (`sum(price × volume)` across all intraday prints). Dollar amounts are unit-invariant across splits. | exchange | T+1 | 2020-01-02 | null on non-trading days | `11_724_390_012.0` |

---

### Unadjusted semantics

Prices and volume in this table are unadjusted (contemporaneous). To compute split-adjusted series (safe returns across split events, share-count normalisation, historical strike comparisons), join to `split_factors` on `figi` where `date BETWEEN valid_from AND valid_to` and apply `cum_split_factor` per the reconstruction snippet in the flat-files section. The split is deliberate: it lets you reload `px_01_day` and `split_factors` independently, and gives you an explicit knob when you need to invert an adjustment.

---

# px_10_min

10-minute OHLCV bars (unadjusted) for the covered US single-stock and ETF universe, keyed on FIGI. Same underlying universe and FIGI join key as `px_01_day`.

**Access:** this table is not directly queryable via the MCP or REST API. Split-adjusted price columns (`adj_close`, `adj_open`, `adj_volume`, `total_turnover`) are returned as LEFT-JOIN columns on `get_underlying_option_flows_10min` (MCP), `intraday_flow_day` (MCP fat tool for a single symbol × single day), and `GET /v0/underlying_option_flows/10_min` (REST). Use those endpoints for intraday price context — the LEFT-join emits one row per `(date, ten_min_timeframe, figi)` with prices attached to the flow payload. For bulk historical pulls, the parquet flat-file surface is at `gs://prod-masscrest/v0/px_data/10_min/`.

- Grain: `(date, ten_min_timeframe, figi, symbol)`
- History: 2020-01-02 →
- Timing: T+1 (published between 05:00 and 09:00 UTC on the calendar day after the trading date — Friday's data lands Saturday)

### Bucket boundary convention

`ten_min_timeframe` is the end of the 10-minute bucket, in New York wall-clock time stored as a naive `DateTime`. Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. First RTH bucket ends at `09:40:00`, last at `16:00:00` (39 buckets per full session). On-boundary 1-minute bars (e.g. `10:00:00` close) land in the closing bucket, not the opening bucket of the next.

### Coverage

Regular trading hours only (09:30–16:00 ET). No pre-market, no after-hours. Halted sessions produce no rows for the halted intervals.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `ten_min_timeframe` | DateTime | NY wall-clock, bucket close | `(date, ten_min_timeframe, figi)` | End timestamp of the 10-min bucket (naive NY-time). Bucket covers `(ten_min_timeframe − 10 min, ten_min_timeframe]`. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24 09:40:00` |
| `date` | Date | trading day (UTC) | `(date, ten_min_timeframe, figi)` | Session date. US equity trading calendar. | exchange | T+1 | 2020-01-02 | never null | `2026-07-24` |
| `figi` | String(12) | identifier | `(date, ten_min_timeframe, figi)` | OpenFIGI composite FIGI for the underlying. Stable across ticker changes. | exchange | n/a | 2020-01-02 | never null | `BBG000MM2P62` |
| `symbol` | String | ticker | `(date, ten_min_timeframe, figi)` | Point-in-time listed ticker on `date`. Dotted dual-class form. Preserved for readability; do not use as a join key. | exchange | T+1 | 2020-01-02 | never null | `AAPL` |
| `open` | Float64 | USD | `(date, ten_min_timeframe, figi)` | Unadjusted price of the first 1-minute bar in the bucket. Note: the source 1-minute feed carries `open` and `close` only (no high/low), so bar-internal high/low is not available at this grain. | exchange | T+1 | 2020-01-02 | null when the underlying has no trading activity in the bucket | `223.90` |
| `close` | Float64 | USD | `(date, ten_min_timeframe, figi)` | Unadjusted price of the last 1-minute bar in the bucket. | exchange | T+1 | 2020-01-02 | null when the underlying has no trading activity in the bucket | `224.02` |
| `volume` | Float64 | shares | `(date, ten_min_timeframe, figi)` | Underlying share volume within the bucket. Unadjusted (contemporaneous). | exchange | T+1 | 2020-01-02 | null when the underlying has no trading activity | `1_204_812.0` |
| `turnover` | Float64 | USD | `(date, ten_min_timeframe, figi)` | Dollar turnover of the underlying within the bucket. Dollar amounts are unit-invariant across splits. | exchange | T+1 | 2020-01-02 | null when the underlying has no trading activity | `270_881_400.0` |

---

### Unadjusted semantics

Prices and volume in this table are unadjusted (contemporaneous). To compute split-adjusted series, join to `split_factors` on `figi` where `date BETWEEN valid_from AND valid_to` and apply `cum_split_factor` per the reconstruction snippet in the flat-files section. The split is deliberate: it lets you reload `px_10_min` and `split_factors` independently, and gives you an explicit knob when you need to invert an adjustment.

### Ticker-renamed FIGIs

A very small number of FIGIs carry rows under two different `symbol` values within a single day (the point-in-time symbol at the moment of the trade). If you aggregate over a universe by `figi` alone, use `sum(turnover)` for turnover and `argMax(close, turnover)` for a representative close per `(date, ten_min_timeframe, figi)`. Don't collapse via `any(...)` or you'll pick a stale legacy row.

---

# stock_metadata

Point-in-time (PIT) reference table for every underlying Masscrest tracks. Maps FIGI to listed ticker, company name, sector / industry, ETF and ADR flags, and the activity window during which that (ticker, FIGI) pair was live. Historical ticker changes, dual-class shares, and shell-reuse events are all encoded as separate phase rows.

- Grain: `(figi, symbol, phase_start)`, one row per contiguous period during which a FIGI traded under a given symbol.
- History: all open phases plus historical phases as far back as the OpenFIGI record supports (many phases start `1900-01-01` as a "no constraint" sentinel).
- Timing: reference table, rebuilt in full each trading day. Re-sync daily to pick up new phases and any corrections.
- Typical use: join to any flow / price table on `figi` with `date BETWEEN phase_start AND phase_end` to attach a point-in-time symbol, sector, name, ETF flag, etc.

## Known quirks

- For ETFs (`isEtf='true'`, which includes ETCs and ETPs), `sector` and `industry` describe the sponsor legal entity, not the fund's underlying exposure. All BlackRock / iShares sector ETFs show `sector='Financial Services'` because BlackRock is a financial-services firm. Filter `isEtf='true'` out before aggregating flow by sector, otherwise `Financial Services` gross flow inflates by 85%+.
- For ADRs (`isAdr='true'`), `sector` and `industry` describe the underlying foreign company's business. HDB is `Financial Services` (HDFC Bank), BABA is `Consumer Cyclical` (Alibaba). Usually the label you want. Filter on `isAdr` if you need to separate US-domiciled sector totals from foreign ADRs.
- The served rollup `v0.grouped_flow_signal_01_day` already excludes ETFs and ADRs from its sector and industry `group_key`s. ADRs have their own `adr` `group_key` and ETFs their own `etf`. This quirk only matters when joining `v0.stock_metadata` directly.

### Symbol conventions

- Equity tickers use the dotted dual-class convention (`BRK.B`, `RDS.A`), matching every other Masscrest surface. When OPRA options data references the same underlying via its no-dot OCC root (`BRKB`), the equity join still uses the dotted form. Options tables carry both: `traded_symbol` (OCC root) for options joins and `traded_underlying` (dotted equity) for FIGI / equity joins.
- Open phases use `phase_end = 2099-12-31` as the sentinel. Closed phases (renames, delistings, ticker recycles) carry a real end date.
- `terminal_symbol` is the current ticker for the same FIGI as of today. Historical phases point forward at their entity's today-identity; join on it for "all history for whatever is called META today," even for the older FB rows.

### Multi-phase tickers

~1% of tickers have multiple `stock_metadata` rows across time — one per FIGI-era — from corporate reorgs (ISIN flip, e.g. XOM's 2026-07-07 Exxon → ExxonMobil Holdings restructure) or ticker recycles after a delisting (PARA was Paramount, then Banzai after 2026-08-07 under a different FIGI).

- **Current era only**: `WHERE symbol='X' AND phase_end='2099-12-31'`
- **Full history across eras**: `WHERE symbol='X'`, resolve joins with `date BETWEEN phase_start AND phase_end`
- **Isolate one era**: filter by `figi` rather than `symbol`

Flow, price, and split tables are already attributed to the FIGI authoritative on each row's date.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `symbol` | String | ticker | `(figi, symbol, phase_start)` | Point-in-time listed ticker during `[phase_start, phase_end]`. Dotted dual-class form (`BRK.B`). | exchange | n/a | 1900-01-01 (sentinel) | never null | `FB` |
| `figi` | String(12) | identifier | `(figi, symbol, phase_start)` | OpenFIGI composite FIGI for the underlying entity. Stable across ticker changes. | exchange | n/a | n/a | never null | `BBG000NDYB67` |
| `phase_start` | Date | boundary | `(figi, symbol, phase_start)` | First trading date this (symbol, figi) pair was active. `1900-01-01` is a "no constraint" sentinel for entities with unknown pre-history. | exchange | n/a | n/a | never null | `2012-05-18` |
| `phase_end` | Date | boundary | `(figi, symbol, phase_start)` | Last trading date this (symbol, figi) pair was active. `2099-12-31` sentinel means the phase is currently open (still trading under this ticker today). | exchange | n/a | n/a | never null | `2022-06-08` |
| `terminal_symbol` | String | ticker | `(figi, symbol, phase_start)` | Current ticker for the same FIGI. On the FB row you'll see `terminal_symbol = 'META'`; on the META row it's also `'META'`. Use this to fetch the full history of a currently-listed name. | exchange | daily refresh | n/a | never null | `META` |
| `companyName` | String | name | `(figi, symbol, phase_start)` | Registered legal / operating name for the entity during this phase. | exchange | daily refresh | n/a | null when no vendor record is available for the phase | `Meta Platforms, Inc.` |
| `sector` | String | sector | `(figi, symbol, phase_start)` | Sector classification for the entity during this phase. Investor-oriented 11-sector taxonomy (companies grouped by demand-cycle and macro exposure, not by production process). | exchange | daily refresh | n/a | null when unresolved | `Technology` |
| `industry` | String | industry | `(figi, symbol, phase_start)` | Industry classification for the entity during this phase. Investor-oriented 154-industry taxonomy nested under the 11 sectors. | exchange | daily refresh | n/a | null when unresolved | `Software - Application` |
| `isin` | String(12) | identifier | `(figi, symbol, phase_start)` | ISIN for the entity. **Not** a unique join key: roughly 200 ISINs map to two active FIGIs simultaneously (the primary listing and the US OTC F-share composite of the same foreign entity). Join on `figi`, not `isin`. | exchange | daily refresh | n/a | null when the vendor lacks an ISIN for the phase | `US30303M1027` |
| `country` | String | ISO country | `(figi, symbol, phase_start)` | Country of incorporation. | exchange | daily refresh | n/a | null when the vendor lacks a country for the phase | `US` |
| `isEtf` | String | flag (`'true'` / `'false'`) | `(figi, symbol, phase_start)` | Whether the underlying is an ETF or ETP. String-typed for compatibility with the customer-facing endpoints. | exchange | daily refresh | n/a | `'false'` when unresolved (defaults to non-ETF) | `false` |
| `isAdr` | String | flag (`'true'` / `'false'`) | `(figi, symbol, phase_start)` | Whether the underlying is an American Depositary Receipt. | exchange | daily refresh | n/a | `'false'` when unresolved | `false` |
| `isActivelyTrading` | String | flag (`'true'` / `'false'`) | `(figi, symbol, phase_start)` | Whether the entity is actively trading today. Falls back to `'true'` when the phase is still open (`phase_end = 2099-12-31`) and the vendor lacks an explicit record, `'false'` otherwise. | exchange | daily refresh | n/a | never null (default fallback based on `phase_end`) | `true` |

---

# split_factors

Cumulative split-adjustment factors per FIGI, structured as a slowly-changing dimension (SCD2). Delivered so clients can back-adjust unadjusted historical prices (see `px_01_day` / `px_10_min`), or invert a Masscrest split-adjusted price back to the raw contemporaneous print for a specific period.

- Grain: one row per continuous split-factor phase per `figi`, `(figi, valid_from, valid_to)`, non-overlapping intervals covering each FIGI's timeline. `date BETWEEN valid_from AND valid_to` matches exactly one row per `(figi, date)`.
- History: full history for each covered FIGI, back to the earliest known split. Pre-split-history phases use `valid_from = 1900-01-01` as a "no earlier bound" sentinel.
- Timing: reference table, rebuilt in full each trading day whenever a new split lands. Re-sync daily.
- Coverage: currently ~30k FIGIs. Historical splits back to 1962 and forward-looking upcoming ex-dates are both covered.

### How to use the factor

`cum_split_factor` on a date X means: to convert a raw close on date X into a current-shares-equivalent price, divide by this factor. It equals `1.0` on and after the most recent split; `> 1` before a forward split (e.g. `4.0` before a 4-for-1); `< 1` before a reverse split. On or after the latest split within an entity's active period, the factor is `1.0`.

## Fields

| field_name | type | unit | grain | description | provenance | timing_lag | history_start | null_semantics | example |
|---|---|---|---|---|---|---|---|---|---|
| `figi` | String(12) | identifier | `(figi, valid_from, valid_to)` | OpenFIGI composite FIGI for the underlying entity. Join key to `stock_metadata`, `px_01_day`, `px_10_min`. | exchange | n/a | n/a | never null | `BBG000MM2P62` |
| `valid_from` | Date | boundary (inclusive) | `(figi, valid_from, valid_to)` | Start date this cumulative factor applies to. `1900-01-01` for the earliest period. | derived | n/a | 1900-01-01 (sentinel) | never null | `2019-08-30` |
| `valid_to` | Date | boundary (inclusive) | `(figi, valid_from, valid_to)` | Last date this cumulative factor applies to. Open-ended periods (no future split yet) use the phase's `phase_end`; entities in a currently-active phase carry `2099-12-31`. | derived | n/a | n/a | never null | `2020-08-30` |
| `cum_split_factor` | Float64 | ratio | `(figi, valid_from, valid_to)` | Cumulative product of all splits from `valid_from + 1 day` forward within the entity's active phase. Divide a raw close on any date in `[valid_from, valid_to]` by this factor to get a current-shares-equivalent price. `1.0` for the most recent period (no pending split). | derived | n/a | n/a | **null** for extreme reverse-split cases where the true cumulative factor underflows Float64 precision (~1e-15). Treat null as "no valid split adjustment available for this period" and either skip or fail loudly; never coerce to `0.0`. | `4.0` |

---

### Precision

Cumulative factors are stored at 12 decimal places to preserve heavy reverse-split stacks (e.g. UVXY has thirteen reverse splits; its true cum factor at `1900-01-01` is ~6.7e-11). Do NOT round to 6 decimals when consuming; that historically truncated ~200 heavy-reverse-splitter FIGIs to `0.0` and silently zeroed downstream price adjustments. For the handful of penny-stock cases where the true cumulative product underflows Float64 precision entirely (e.g. stacked 1-for-50000 × 1-for-19000 × 1-for-10000 ≈ 3.5e-20), we emit `NULL` rather than `0.0` so consumers can detect and skip rather than silently multiplying prices by zero.

### No-split entities

FIGIs that never split carry a single row spanning the entity's full active phase with `cum_split_factor = 1.0`. Same shape as those that split; no special-case handling needed.

---

# REST API

Every endpoint the Masscrest API exposes, with query URL, Python sample,
and JSON response inline.

## Stock Mapping

```http
GET /v0/stock_mapping
```

Ticker → FIGI / sector / industry reference table (~42k rows).

### Description

Retrieve the `stock_mapping` reference table, which maps every ticker Masscrest tracks to its identifiers and company metadata (FIGI, ISIN, sector, industry, ETF/ADR/fund flags) and includes historical ticker changes via `phase_start` / `phase_end` windows. Filters are optional and combine with `AND` for exact match.

The full table is ~42,000 rows. **Default `limit` is 10 rows** to keep interactive docs responsive; **pass `limit=0` for the full ~42k-row table**. When the default limit clipped the rowset, the response carries `X-Truncated: true` (absent when the full result fit).

### Response headers (shared with every `/v0/*` list endpoint)

- `X-Row-Count: <N>` — number of rows in the response, regardless of `format`.
- `X-Truncated: true` — `stock_mapping` only: default `limit=10` clipped the rowset.

### Error responses

- `400 Bad Request` — unknown query parameter. The response body enumerates the accepted names for this endpoint.

### Use cases

- Mapping point-in-time tickers to stable FIGI identifiers
- Sector / industry classification
- Identifying ETFs, ADRs, and funds
- Tracking ticker-change history

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `terminal_symbol` | string | No | Current / most recent ticker for the company. |
| `figi` | string | No | Composite FIGI identifier. |
| `isin` | string | No | ISIN code. |
| `sector` | string | No | Sector classification. |
| `industry` | string | No | Industry classification. |
| `limit` | integer | No | Maximum number of rows to return. `0` means no cap (full table). Defaults to `10`. |
| `format` | enum: json, parquet | No | Response format. `json` returns a JSON array (default). `parquet` streams a binary Parquet file (~5–10× smaller, faster to parse). |

### Sample response

```json
[
  {
    "symbol": "FB",
    "figi": "BBG000MM2P62",
    "phase_start": "2012-05-18",
    "phase_end": "2022-06-09",
    "terminal_symbol": "META",
    "companyName": "Meta Platforms, Inc.",
    "sector": "Communication Services",
    "industry": "Internet Content & Information",
    "isin": "US30303M1027",
    "country": "US",
    "isEtf": "false",
    "isAdr": "false",
    "isActivelyTrading": "true"
  },
  {
    "symbol": "META",
    "figi": "BBG000MM2P62",
    "phase_start": "2022-06-10",
    "phase_end": "2099-12-31",
    "terminal_symbol": "META",
    "companyName": "Meta Platforms, Inc.",
    "sector": "Communication Services",
    "industry": "Internet Content & Information",
    "isin": "US30303M1027",
    "country": "US",
    "isEtf": "false",
    "isAdr": "false",
    "isActivelyTrading": "true"
  }
]
```

---

## Option Flows: Daily

```http
GET /v0/option_flows/01_day
```

Per-underlying daily aggregated option flow joined with the split-adjusted underlying price.

### Description

Retrieve daily option-flow rows for a single underlying, aggregated per `(date, figi)`: one row per trading day. Every row combines all contracts on that underlying (after any optional filters) into a single set of signed retail / institutional / dealer aggregates, joined to the split-adjusted underlying price on the same day.

Exactly one of `figi` or `traded_underlying` must be supplied. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both, or neither, returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`.

Trial tiers cap daily history at 730 days and count each distinct FIGI touched per UTC day against a 25-figi cap. Paid tiers have no per-figi cap and honour the requested history window. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier) for the full policy.

### Optional filters, applied BEFORE aggregation

`callput`, `dte_min` / `dte_max`, and `abs_delta_min` / `abs_delta_max` narrow the contract set inside the underlying-level GROUP BY. The aggregate reflects only contracts matching the filter. `dte_min <= dte_max` and `abs_delta_min <= abs_delta_max` are cross-validated (400 on mismatch).

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Aggregation

All fields are summed raw across the contracts passing the filters. No call/put sign flip is applied. For a call-vs-put directional view, query `callput=C` and `callput=P` separately and subtract client-side.

- `*_shares`: `sum(qty × adj_shares_deliverable)`. Units are shares (contract count × current OPRA deliverable).
- `*_premium`: `sum(premium)`. Dollar notional.
- `*_delta` / `*_gamma` / `*_vega`: summed raw. Delta is naturally signed by option type on the source contract (calls +, puts −), so `net_i_delta` already carries directional lean.

Prefixes are `_r_` (retail), `_i_` (institutional), `_m_` (dealer). The floor bucket is not exposed via this endpoint.

### Use cases

- Building daily flow-based signals per underlying
- Studying institutional vs retail vs dealer flow around events
- Reconstructing filtered net delta / gamma / vega exposure over time

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. |
| `traded_underlying` | string | No | Stock ticker; resolved to FIGI via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. |
| `callput` | enum: C, P | No | Restrict the contract set to Calls (`C`) or Puts (`P`) BEFORE aggregation. Case-insensitive (`c`, `C`, `p`, `P` all accepted). `CP` is rejected here — this endpoint is contract-grain and every contract is either a call or a put; for a summed C+P view use `/v0/underlying_option_flows/*` with `callput=CP`. |
| `dte_min` | integer | No | Minimum days-to-expiration (inclusive). Applied per contract before aggregation. |
| `dte_max` | integer | No | Maximum days-to-expiration (inclusive). Applied per contract before aggregation. |
| `abs_delta_min` | number | No | Minimum `\|last_delta\|` in `[0.0, 1.0]`. Applied per contract before aggregation. |
| `abs_delta_max` | number | No | Maximum `\|last_delta\|` in `[0.0, 1.0]`. Applied per contract before aggregation. |
| `start_date` | string | No | Earliest trading day to include (inclusive, YYYY-MM-DD). Trial tiers are clamped to a 730-day history window. |
| `end_date` | string | No | Latest trading day to include (inclusive, YYYY-MM-DD). |
| `format` | enum: json, parquet | No | Response format. `json` returns a JSON array (default). `parquet` streams a binary Parquet file (~5–10× smaller). |

### Sample response

```json
[
  {
    "date": "2024-01-02",
    "figi": "BBG000MM2P62",
    "traded_underlying": "META",
    "buy_r_shares": 80700,
    "sell_r_shares": -12000,
    "net_r_shares": 68700,
    "buy_r_premium": 24120080,
    "sell_r_premium": -1892345,
    "net_r_premium": 22227735,
    "buy_i_shares": -320500,
    "sell_i_shares": 452000,
    "net_i_shares": -772500,
    "buy_i_premium": 1441250,
    "sell_i_premium": 10321278,
    "net_i_premium": -8880028,
    "buy_i_delta": 662474262,
    "sell_i_delta": 879245325,
    "net_i_delta": -216771074,
    "buy_m_shares": 240000,
    "sell_m_shares": -451800,
    "net_m_shares": 691800,
    "buy_m_premium": 4823101,
    "sell_m_premium": 20063152,
    "net_m_premium": -15240051,
    "adj_open": 351.10,
    "adj_close": 353.96,
    "adj_volume": 24800000,
    "total_turnover": 4812332101
  }
]
```

---

## Option Flows: 10-minute

```http
GET /v0/option_flows/10_min
```

Per-underlying 10-minute aggregated option flow joined with the intraday split-adjusted underlying price.

### Description

Retrieve 10-minute-bucket option-flow rows for a single underlying, aggregated per `(date, ten_min_timeframe, figi)`: one row per 10-minute bucket. Every row combines all contracts on that underlying (after any optional filters) into a single set of signed retail / institutional / dealer aggregates, joined to the split-adjusted underlying price for the same bucket.

Exactly one of `figi` or `traded_underlying` must be supplied. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both, or neither, returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`.

> **Trial tier: intraday is not available.** All 10-minute endpoints return HTTP 403 for trial accounts. Trials should use the daily variant `/v0/option_flows/01_day`. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier).

> **30-day window cap (paid tier).** Any single call returns at most 30 days of history. If `start_date` / `end_date` are omitted, the response covers the most recent 30 days. Requests spanning more than 30 days return HTTP 400. For multi-year intraday history, use the flat-files bucket `gs://prod-masscrest/v0/option_flows/10_min/`. For longer-horizon signals, use the daily variant `/v0/option_flows/01_day`.

### Optional filters, applied BEFORE aggregation

`callput`, `dte_min` / `dte_max`, and `abs_delta_min` / `abs_delta_max` narrow the contract set inside the bucket-level GROUP BY. The aggregate reflects only contracts matching the filter. `dte_min <= dte_max` and `abs_delta_min <= abs_delta_max` are cross-validated (400 on mismatch).

### Sign convention

Same as the daily variant. See `/v0/option_flows/01_day` for the full sign / prefix / unit notes.

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- Intraday flow surveillance around scheduled releases and open/close auctions
- Filtered 10-minute delta / gamma / vega exposure per underlying

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. |
| `traded_underlying` | string | No | Stock ticker; resolved to FIGI via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. |
| `callput` | enum: C, P | No | Restrict the contract set to Calls (`C`) or Puts (`P`) BEFORE aggregation. Case-insensitive (`c`, `C`, `p`, `P` all accepted). `CP` is rejected here — this endpoint is contract-grain and every contract is either a call or a put; for a summed C+P view use `/v0/underlying_option_flows/*` with `callput=CP`. |
| `dte_min` | integer | No | Minimum days-to-expiration (inclusive). Applied per contract before aggregation. |
| `dte_max` | integer | No | Maximum days-to-expiration (inclusive). Applied per contract before aggregation. |
| `abs_delta_min` | number | No | Minimum `\|last_delta\|` in `[0.0, 1.0]`. Applied per contract before aggregation. |
| `abs_delta_max` | number | No | Maximum `\|last_delta\|` in `[0.0, 1.0]`. Applied per contract before aggregation. |
| `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Combined with `end_date` must span at most 30 days; if both are omitted the response covers the most recent 30 days. |
| `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). Combined with `start_date` must span at most 30 days. |
| `format` | enum: json, parquet | No | Response format. `json` returns a JSON array (default). `parquet` streams a binary Parquet file (~5–10× smaller). |

### Sample response

```json
[
  {
    "date": "2024-01-02",
    "ten_min_timeframe": "2024-01-02 09:40:00",
    "figi": "BBG000MM2P62",
    "traded_underlying": "META",
    "buy_r_shares": 12300,
    "sell_r_shares": -2400,
    "net_r_shares": 9900,
    "buy_r_premium": 694138,
    "sell_r_premium": -128110,
    "net_r_premium": 566028,
    "buy_i_shares": -18000,
    "sell_i_shares": 22000,
    "net_i_shares": -40000,
    "buy_i_premium": 210500,
    "sell_i_premium": 614822,
    "net_i_premium": 404322,
    "buy_i_delta": 12405213,
    "sell_i_delta": 22132814,
    "net_i_delta": -9727601,
    "buy_m_shares": 5000,
    "sell_m_shares": -19100,
    "net_m_shares": 24100,
    "buy_m_premium": 218000,
    "sell_m_premium": 1052300,
    "net_m_premium": -834300,
    "adj_open": 351.10,
    "adj_close": 351.87,
    "adj_volume": 3200000,
    "total_turnover": 1123840120
  }
]
```

---

## Underlying Option Flows: Daily

```http
GET /v0/underlying_option_flows/01_day
```

Per-underlying × callput daily aggregated option flow (pre-aggregated), joined with adjusted underlying price.

### Description

Retrieve daily underlying-level option-flow rows (pre-aggregated across every contract on the same `(date, figi, callput)`), joined to the PIT `stock_metadata` row for `(figi, date)`. Rows are already grouped by `(date, figi, callput)`, so unlike `/v0/option_flows/01_day` no contract-level filters are available. In exchange, cross-underlying scans (see the sibling `/snapshot` endpoint) are significantly cheaper.

Provide at most one of `figi` or `traded_underlying`. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`. The underlying selector may be omitted entirely when at least one `stock_metadata` filter (`is_etf` / `is_adr` / `industry` / `sector`) is provided; the response then covers every figi matching the metadata predicate for the date range.

> **Trial tier.** Must supply an explicit `figi` or `traded_underlying` — a broad `sector` / `industry`-only sweep returns HTTP 400 with the message `Trial tier requires explicit figi or underlying_ticker filter (up to 25 per UTC day). For broad cohort exploration use programmatic flat-files access.` Each distinct FIGI touched counts toward the 25-figi-per-UTC-day cap; the 26th distinct FIGI returns HTTP 403 with the touched list echoed back. Daily history clamped to 730 days. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier).

### `callput`

- `C`: calls-only row per bucket (direct scan).
- `P`: puts-only row per bucket (direct scan).
- `CP`: default; C and P summed at query time. One row per bucket with `callput = "CP"` in the output.

Case-insensitive on input — `c`, `Cp`, `CP`, `p` all accepted; the emitted row always carries the canonical upper-case form.

### Response columns

`date`, `figi`, `traded_underlying`, `callput`, `n_contracts`, `total_shares`, `last_underprice`, `last_fwd_underprice`, `atm_iv_30d`, the 45 flow aggregates (`buy/sell/net × shares/premium/delta/gamma/vega × r/i/m`), `adj_open` / `adj_close` / `adj_volume` / `total_turnover` from the matched underlying, and `industry` / `sector` / `isAdr` / `isEtf` from `stock_metadata` (ranged endpoints intentionally omit `companyName`).

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- Longitudinal per-underlying flow with automatic C+P combination
- Cross-sector or ETF-only flow scans without needing to enumerate figis upfront
- Feeding call-vs-put ratio signals per underlying

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. Optional when a `stock_metadata` filter is supplied. |
| `traded_underlying` | string | No | Stock ticker; resolved via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. Optional when a `stock_metadata` filter is supplied. |
| `callput` | enum: C, P, CP | No | `C`, `P`, or `CP` (default). `CP` sums the C and P rows at query time. |
| `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Trial tiers are clamped to a 730-day window. |
| `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). |
| `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_metadata` row. |
| `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). |
| `industry` | string | No | Filter on `stock_metadata.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). |
| `sector` | string | No | Filter on `stock_metadata.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). |
| `format` | enum: json, parquet | No | `json` (default) or `parquet` (binary, ~5–10× smaller). |

### Sample response

```json
[
  {
    "date": "2026-07-14",
    "figi": "BBG000MM2P62",
    "traded_underlying": "META",
    "callput": "CP",
    "n_contracts": 2838,
    "total_shares": 4132100,
    "last_underprice": 661.08,
    "last_fwd_underprice": 661.55,
    "atm_iv_30d": 0.283,
    "net_i_premium": -5814246,
    "adj_open": 660.10,
    "adj_close": 661.08,
    "adj_volume": 15321000,
    "total_turnover": 10129000000
  }
]
```

---

## Underlying Option Flows: 10-minute

```http
GET /v0/underlying_option_flows/10_min
```

Per-underlying × callput 10-minute aggregated option flow (pre-aggregated), joined with intraday adjusted price.

### Description

Retrieve 10-minute-bucket underlying-level option-flow rows (pre-aggregated across every contract on the same `(date, ten_min_timeframe, figi, callput)`), joined to the PIT `stock_metadata` row for `(figi, date)`. Rows are already grouped by `(date, ten_min_timeframe, figi, callput)`; no contract-level filters are available on this endpoint.

Provide at most one of `figi` or `traded_underlying`. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`. The underlying selector may be omitted entirely when at least one `stock_metadata` filter (`is_etf` / `is_adr` / `industry` / `sector`) is provided; the response then covers every figi matching the metadata predicate for the date range.

> **Trial tier: intraday is not available.** All 10-minute endpoints return HTTP 403 for trial accounts. Trials should use the daily variant `/v0/underlying_option_flows/01_day`. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier).

> **30-day window cap (paid tier).** At most 30 days of history per call. If `start_date` / `end_date` are omitted, the response covers the most recent 30 days. Requests spanning more than 30 days return HTTP 400. For multi-year intraday history use the flat-files bucket `gs://prod-masscrest/v0/option_flows/10_min/`.

### `callput`

Same semantics as the daily variant: `C`, `P`, or `CP` (default). `CP` sums C and P at query time. Case-insensitive on input.

### Response columns

Standard flow-aggregate columns plus `industry` / `sector` / `isAdr` / `isEtf` from `stock_metadata` (ranged endpoints intentionally omit `companyName`).

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- Intraday flow surveillance without per-contract filters
- Sector-restricted intraday sweeps (e.g. all Semiconductors, 30 days)
- Building high-frequency call-vs-put ratio signals per underlying

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. Optional when a `stock_metadata` filter is supplied. |
| `traded_underlying` | string | No | Stock ticker; resolved via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. Optional when a `stock_metadata` filter is supplied. |
| `callput` | enum: C, P, CP | No | `C`, `P`, or `CP` (default). |
| `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Combined with `end_date` must span at most 30 days. |
| `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). |
| `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_metadata` row. |
| `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). |
| `industry` | string | No | Filter on `stock_metadata.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). |
| `sector` | string | No | Filter on `stock_metadata.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). |
| `format` | enum: json, parquet | No | `json` (default) or `parquet` (binary, ~5–10× smaller). |

### Sample response

```json
[
  {
    "date": "2026-07-21",
    "ten_min_timeframe": "2026-07-21 09:40:00",
    "figi": "BBG000MM2P62",
    "traded_underlying": "META",
    "callput": "CP",
    "n_contracts": 411,
    "total_shares": 62100,
    "last_underprice": 665.10,
    "last_fwd_underprice": 665.55,
    "atm_iv_30d": 0.281,
    "net_i_premium": -132400,
    "adj_open": 665.10,
    "adj_close": 664.87,
    "adj_volume": 3200000,
    "total_turnover": 1123840120
  }
]
```

---

## Underlying Option Flows: Daily Snapshot

```http
GET /v0/underlying_option_flows/01_day/snapshot
```

Single-day cross-sectional snapshot of pre-aggregated option flow for every underlying, joined with adjusted price.

### Description

Cross-sectional snapshot: returns one row per `(figi, callput)` (every underlying with flow on the requested `date`, C and P kept as separate rows), joined to the split-adjusted underlying close AND to the PIT `stock_metadata` row for `(figi, date)`.

No underlying selector is required; the response covers the full universe. Optional `stock_metadata`-backed filters (`is_etf` / `is_adr` / `industry` / `sector`) narrow the returned figis to those matching the predicate.

> **Trial tier: snapshot endpoints are not available.** This endpoint returns HTTP 403 for trial accounts (same treatment as intraday). A wide snapshot would charge every returned FIGI against the 25-figi-per-UTC-day cap in a single call, so the snapshot surface is blocked outright on trial. Use the ranged `/v0/underlying_option_flows/01_day` endpoint with an explicit `figi` / `traded_underlying` filter for per-underlying pulls, or `/v0/grouped_flow_signal/01_day/snapshot` (not figi-capped) for broad cross-sectional exploration. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier).

Typical response is ~10,000 rows per trading day (roughly 5,000 underlyings × C+P). Prefer `format=parquet` for anything larger than a browser preview.

### Response columns

Standard flow-aggregate columns plus `companyName` / `industry` / `sector` / `isAdr` / `isEtf` from `stock_metadata`.

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- Cross-sectional cross-underlying ranks / z-scores for a given trading day
- Sector- or ETF-restricted end-of-day dashboards
- Building end-of-day universe-wide dashboards

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `date` | string | Yes | Trading day (YYYY-MM-DD) to snapshot. |
| `figi` | string | No | Optional comma-separated list of composite FIGIs to restrict the snapshot to those symbols (paid tier only — trials are blocked at the router with HTTP 403 before this filter is evaluated). |
| `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_metadata` row. |
| `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). |
| `industry` | string | No | Filter on `stock_metadata.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). |
| `sector` | string | No | Filter on `stock_metadata.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). |
| `format` | enum: json, parquet | No | `json` (default) or `parquet` (recommended for full-universe pulls). |

### Sample response

```json
[
  {
    "date": "2026-07-20",
    "figi": "BBG000B9WM03",
    "traded_underlying": "AB",
    "callput": "C",
    "n_contracts": 118,
    "total_shares": 11800,
    "last_underprice": 37.91,
    "adj_close": 37.91
  }
]
```

---

## Underlying Option Flows: 10-minute Snapshot

```http
GET /v0/underlying_option_flows/10_min/snapshot
```

Single 10-minute-bucket cross-sectional snapshot of pre-aggregated option flow for every underlying.

### Description

Cross-sectional snapshot at a specific 10-minute bucket: returns one row per `(figi, callput)` (every underlying with flow at the requested `(date, ten_min_timeframe)`, C and P kept as separate rows), joined to the split-adjusted underlying price for the same bucket AND to the PIT `stock_metadata` row for `(figi, date)`.

No underlying selector is required. Optional `stock_metadata`-backed filters (`is_etf` / `is_adr` / `industry` / `sector`) narrow the returned figis to those matching the predicate.

> **Trial tier: intraday is not available.** All 10-minute endpoints return HTTP 403 for trial accounts. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier).

The `ten_min_timeframe` param must fall on the requested `date`. Typical response is ~5,000–10,000 rows per bucket. Prefer `format=parquet` for full-universe pulls.

### Response columns

Standard flow-aggregate columns plus `companyName` / `industry` / `sector` / `isAdr` / `isEtf` from `stock_metadata`.

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- Intraday cross-sectional dashboards at 10-minute cadence
- Sector- or ETF-restricted intraday flow ranks
- Bucket-anchored cross-underlying flow ranks around scheduled events

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `date` | string | Yes | Trading day (YYYY-MM-DD) the bucket falls on. |
| `ten_min_timeframe` | string | Yes | Bucket start timestamp (`YYYY-MM-DD HH:MM:SS`). Must fall on `date`. |
| `figi` | string | No | Optional comma-separated list of composite FIGIs to restrict the snapshot to those symbols (paid tier only — trials are blocked at the router with HTTP 403 before this filter is evaluated). |
| `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_metadata` row. |
| `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). |
| `industry` | string | No | Filter on `stock_metadata.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). |
| `sector` | string | No | Filter on `stock_metadata.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). |
| `format` | enum: json, parquet | No | `json` (default) or `parquet` (recommended for full-universe pulls). |

### Sample response

```json
[
  {
    "date": "2026-07-21",
    "ten_min_timeframe": "2026-07-21 09:40:00",
    "figi": "BBG000B9WM03",
    "traded_underlying": "AB",
    "callput": "C",
    "n_contracts": 12,
    "total_shares": 1200,
    "last_underprice": 37.86,
    "adj_close": 37.8605
  }
]
```

---

## Underlying Flow Signals: Daily

```http
GET /v0/underlying_flow_signals/01_day
```

Per-underlying × callput daily flow signals (raw nets, 21-day rolling sums, 2y z-scores across shares / premium / delta / gamma / vega).

### Description

Retrieve daily flow-signal rows from the pre-aggregated `underlying_flow_signal_01_day` table, joined to the PIT `stock_metadata` row for `(figi, date)`. One row per `(date, figi, callput)`.

The source table stores 34 columns per row: 4 keys (`date`, `figi`, `underlying_ticker`, `callput`) plus a 3-layer × 10-metric flow block. The 10 metrics are institutional + retail × shares / premium / delta / gamma / vega. The 3 layers are raw daily nets, `_21dma` (21-trading-day rolling sums), and `z_net_..._21dma` (z-scored against a trailing 2-year rolling mean and sample stddev of the 21dma). The raw μ / σ of the 2-year window are computed at query time and are NOT distributed as separate columns. Rolling windows PARTITION BY `(figi, callput)`; each callput row has its own independent trailing stats, so `z(C) + z(P) ≠ z(CP)`.

### The `callput` dimension

Every source row exists in three variants: `C` (calls only), `P` (puts only), and `CP` (synthetic combined view). `CP` is built at ingest time so consumers do not have to reconstruct call-vs-put semantics themselves:

- Directional nets (shares, premium) → `CP = C − P`. Bullish call flow and bearish put flow both express long-delta demand; the sign-flipped difference is the net directional pressure.
- Naturally-signed greeks (delta, gamma, vega) → `CP = C + P`. Calls carry positive delta, puts negative; the straight sum through-adds correctly.

The default `callput` is `CP`; omitting the filter would triple-count each figi-date across the three variants.

### Deriving fire / holding-period predicates

Fire flags and holding-period flags are not persisted on this table; derive them at query time from the z-score column:

- 2σ institutional-delta fire: `WHERE z_net_i_delta_21dma > 2 AND callput = 'CP'`
- 3σ institutional-delta fire: `WHERE z_net_i_delta_21dma > 3 AND callput = 'CP'`

Holding-period semantics (fire within the last 22 trading days AND same-metric z has not gone negative since) can be reconstructed with a self-lag or a rolling `max(z)` window function. The pipeline does not persist this because the same rule can be expressed on any of the 10 z-columns × any threshold, and pre-materialising that cross-product would add 60+ columns of low-density flags.

### Underlying selector

Provide at most one of `figi` or `traded_underlying`. If `traded_underlying` is used, it is resolved server-side to the corresponding FIGI. Supplying both returns HTTP 400. An unknown `traded_underlying` returns HTTP 404, not an empty `[]`. The underlying selector may be omitted entirely when at least one `stock_metadata` filter (`is_etf` / `is_adr` / `industry` / `sector`) is provided; the response then covers every figi matching the metadata predicate for the date range.

> **Trial tier.** Must supply an explicit `figi` or `traded_underlying` — a broad `sector` / `industry`-only sweep returns HTTP 400. Each distinct FIGI touched counts toward the 25-figi-per-UTC-day cap; the 26th distinct FIGI returns HTTP 403 with the touched list echoed back. Daily history clamped to 730 days. For broad cohort exploration use `/v0/grouped_flow_signal/01_day` (not figi-capped). See [Trial vs. paid tier](/documentation#trial-vs-paid-tier).

### Response columns

`date`, `figi`, `traded_underlying`, `callput`, the 10 raw daily nets, 10 `_21dma` (21-day rolling sums), and 10 z-scores (against the trailing 2-year rolling μ/σ of the 21dma, computed at query time). Plus `industry` / `sector` / `isAdr` / `isEtf` from `stock_metadata` (ranged endpoint intentionally omits `companyName`).

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- Longitudinal per-underlying z-score / 21dma trajectories on a chosen callput
- Screener alerts on institutional 2σ/3σ z-score crossings (`z_net_i_delta_21dma > 2 AND callput = 'CP'`)
- Sector-scoped signal sweeps (e.g. all Technology names with z ≥ 3 in the last week)
- Vega-flow monitoring (`z_net_i_vega_21dma`) for vol-timing signals
- Comparing call-side vs put-side conviction on the same name (`callput = 'C'` vs `callput = 'P'`)

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `figi` | string | No | Composite FIGI of the underlying. Mutually exclusive with `traded_underlying`. Optional when a `stock_metadata` filter is supplied. |
| `traded_underlying` | string | No | Stock ticker; resolved via the `dict_symbol_to_figi` dictionary. Mutually exclusive with `figi`. Optional when a `stock_metadata` filter is supplied. |
| `callput` | enum: C, P, CP | No | `C` (calls only), `P` (puts only), or `CP` (default; synthetic combined view). CP is pre-computed: directional nets are `C − P` (bullish call flow + bearish put flow both express bullish conviction, so nets combine sign-flipped); greeks are `C + P` (naturally signed by option type). **The default is `CP` for a reason: every source row triples up on callput and omitting the filter triple-counts.** Case-insensitive on input (`c`, `Cp`, `CP`, `p` all accepted); the emitted row always carries the canonical upper-case form. |
| `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Trial tiers are clamped to a 730-day window. |
| `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). |
| `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_metadata` row. |
| `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). |
| `industry` | string | No | Filter on `stock_metadata.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). |
| `sector` | string | No | Filter on `stock_metadata.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). |
| `format` | enum: json, parquet | No | `json` (default) or `parquet` (binary, ~5–10× smaller). |

### Sample response

```json
[
  {
    "date": "2026-07-22",
    "figi": "BBG000MM2P62",
    "traded_underlying": "META",
    "callput": "CP",
    "net_i_premium": -5814246.0,
    "net_i_delta": 12530000.0,
    "net_i_gamma": 240000.0,
    "net_i_vega": -820000.0,
    "net_i_premium_21dma": -25416300.0,
    "z_net_i_premium_21dma": -1.59,
    "z_net_i_delta_21dma": 2.08,
    "z_net_i_vega_21dma": -1.34,
    "industry": "Internet Content & Information",
    "sector": "Communication Services",
    "isAdr": "false",
    "isEtf": "false"
  }
]
```

---

## Underlying Flow Signals: Daily Snapshot

```http
GET /v0/underlying_flow_signals/01_day/snapshot
```

Single-day × callput cross-sectional snapshot of daily flow signals for every underlying with a row on `date`.

### Description

Cross-sectional snapshot: returns one row per `(figi, callput)` with a signal-table row on the requested `date`, joined to the PIT `stock_metadata` row for `(figi, date)`.

No underlying selector is required; the response covers the full universe on the requested `callput` (default `CP`). Optional `stock_metadata`-backed filters (`is_etf` / `is_adr` / `industry` / `sector`) narrow the returned figis.

> **Trial tier: snapshot endpoints are not available.** This endpoint returns HTTP 403 for trial accounts (same treatment as intraday). A wide snapshot would charge every returned FIGI against the 25-figi-per-UTC-day cap in a single call, so the snapshot surface is blocked outright on trial. Use the ranged `/v0/underlying_flow_signals/01_day` endpoint with an explicit `figi` / `traded_underlying` filter for per-underlying signal pulls, or `/v0/grouped_flow_signal/01_day/snapshot` (not figi-capped) for broad screening. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier).

Typical unfiltered response is ~4,000–5,000 rows per trading day per callput variant. Adding a z-score filter (e.g. `WHERE z_net_i_delta_21dma > 2`) is left to the client; the endpoint returns the full universe row-set and clients filter downstream. Prefer `format=parquet` for anything larger than a browser preview.

### Callput semantics

Same as the ranged endpoint: `C`, `P`, or `CP`. Default is `CP` when the `callput` parameter is omitted. Case-insensitive on input. See `/v0/underlying_flow_signals/01_day` for the C−P / C+P synthesis convention. Omitting the callput filter would triple-count; the endpoint enforces the default.

### Deriving fire predicates

The endpoint returns the full universe row-set for the requested `date` × `callput`; filter downstream on the emitted z-score columns:

```python
rows = requests.get(...).json()
fires = [r for r in rows if r["z_net_i_delta_21dma"] and r["z_net_i_delta_21dma"] > 2]
```

### Response columns

The 34 signal-table columns (4 keys + 30 flow — 10 raw nets, 10 21-day rolling sums, 10 z-scores against the trailing 2-year μ/σ of the 21dma) plus `companyName` / `industry` / `sector` / `isAdr` / `isEtf` from `stock_metadata`.

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- Daily screener: apply a z-threshold client-side to surface the day's institutional 2σ+ delta names
- Sector-restricted cross-sectional signal maps on a chosen callput
- End-of-day dashboards of active vol-flow (`z_net_i_vega_21dma`) crossings
- Comparing full-universe C-side vs P-side conviction distributions

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `date` | string | Yes | Trading day (YYYY-MM-DD) to snapshot. |
| `callput` | enum: C, P, CP | No | `C` (calls only), `P` (puts only), or `CP` (default; synthetic combined view). CP is pre-computed: directional nets are `C − P` (bullish call flow + bearish put flow both express bullish conviction, so nets combine sign-flipped); greeks are `C + P` (naturally signed by option type). **The default is `CP` for a reason: every source row triples up on callput and omitting the filter triple-counts.** Case-insensitive on input (`c`, `Cp`, `CP`, `p` all accepted); the emitted row always carries the canonical upper-case form. |
| `figi` | string | No | Optional comma-separated list of composite FIGIs to restrict the snapshot to those symbols (paid tier only — trials are blocked at the router with HTTP 403 before this filter is evaluated). |
| `is_etf` | boolean | No | Filter to ETFs (`true`) or non-ETFs (`false`). Applied to the joined `stock_metadata` row. |
| `is_adr` | boolean | No | Filter to ADRs (`true`) or non-ADRs (`false`). |
| `industry` | string | No | Filter on `stock_metadata.industry`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known industry returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown industry: 'semis'. Did you mean 'Semiconductors'?"`). |
| `sector` | string | No | Filter on `stock_metadata.sector`. Case-insensitive on input; the emitted row carries the canonical mixed-case form. A value that doesn't match any known sector returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. `"Unknown sector: 'technolgy'. Did you mean 'Technology'?"`). |
| `format` | enum: json, parquet | No | `json` (default) or `parquet` (recommended for full-universe pulls). |

### Sample response

```json
[
  {
    "date": "2026-07-22",
    "figi": "BBG000B9WM03",
    "traded_underlying": "AB",
    "callput": "CP",
    "net_i_premium": 1240300.0,
    "net_i_delta": 8420000.0,
    "z_net_i_premium_21dma": 2.41,
    "z_net_i_delta_21dma": 2.68,
    "z_net_i_vega_21dma": 0.62,
    "companyName": "AllianceBernstein Holding L.P.",
    "industry": "Asset Management",
    "sector": "Financial Services",
    "isAdr": "false",
    "isEtf": "false"
  }
]
```

---

## Grouped Flow Signal: Daily

```http
GET /v0/grouped_flow_signal/01_day
```

Group-level (sector / industry / etf / adr / single_stock / all) × callput daily flow-signal rollups.

### Description

Retrieve group-level daily flow-signal rows from `grouped_flow_signal_01_day`. One row per `(date, group_key, callput)`. No stock_metadata join; the `group_key` ARE the classification.

The source table sums the `underlying_flow_signal_01_day` raw nets across every figi in the selected bucket, then re-runs the 21-day rolling sum + 2-year z-score cascade on the group-level series. Row shape mirrors `underlying_flow_signal_01_day` (10 raw nets + 10 `_21dma` + 10 z-scores; the raw μ / σ of the trailing 2-year window are computed at query time and are NOT distributed as separate columns). Callput semantics are identical (default `CP`, C−P for nets, C+P for greeks).

### Single-axis contract

Query with **one** `group_type` value — either a canonical mixed-case sector or industry name (e.g. `"Technology"`, `"Software—Application"`) or one of the rollup literals `"etf"` / `"adr"` / `"single_stock"` / `"all"`. There is no paired second parameter — the family (sector vs industry vs rollup) is inferred from the value itself. The response row echoes the same value in the `group_key` STRING column; matching is case-insensitive on input and the wire always carries the canonical mixed-case form.

### Signal formula

- `_21dma` = 21-trading-day rolling SUM of the group-level raw net, scoped to `(group_key, callput)`.
- `z_net_*_21dma` = z-score of `_21dma` against its trailing 2-year (504 trading day) rolling mean and sample standard deviation, computed at query time. Minimum 63 non-null observations in the trailing window required, else null. Rounded to 2dp on emit. The raw μ / σ are not distributed as separate columns.

Every industry-day is emitted with populated rolling-window fields (`21dma`, `z_*`) and `n_figi` exposed on the row. The 21dma and 2y baseline include ALL days regardless of `n_figi` — thin days contribute their (smaller) raw values to the trailing window, so each industry's z is calibrated against its own historical breadth mix and populates on every day. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully — filter on `n_figi` at query time when comparability matters. Sector, etf, adr, single_stock, and all branches always clear the 10-FIGI mark and are effectively unchanged; only industries (Conglomerates, Regulated Water, Uranium, etc.) are materially affected.

### ⚠ Not point-in-time for group classification

The sector, industry, ETF, and ADR labels used to bucket each figi come from the CURRENT `stock_metadata` phase (row with `phase_end = 2099-12-31`), applied uniformly to every historical date. Sector / industry labels churn (10–20 reclassifications per year across the covered universe); clients expect historical rollups to use today's classification for consistency with the current portfolio view.

The signal rolling window itself is per bucket; only the group-membership assignment is applied retroactively.

If you need a fully point-in-time sector rollup, bucket at query time against `stock_metadata` filtered by `date BETWEEN phase_start AND phase_end`, using the `underlying_flow_signal_01_day` table directly.

### Group-type branches

- `sector`: one row per distinct sector name per `(date, callput)`. `group_key` is the mixed-case sector string (~11 distinct values). **ETFs and ADRs are excluded from sector rollups** — they have their own top-level `etf` and `adr` branches, and their FMP-reported "issuer sector" is typically the legal-entity domicile (Financial Services for most ETFs), not the underlying-exposure sector.
- `industry`: one row per distinct industry name per `(date, callput)`. `group_key` is the mixed-case industry string (~150 distinct values matching the FMP taxonomy). Same ETF/ADR exclusion. `n_figi` is exposed on every row so consumers can filter thin-breadth days at query time — see "Thin-industry handling" below.
- `etf`: every figi with `isEtf = 'true'` on the current phase, aggregated. `group_key` = literal `"etf"`.
- `adr`: every figi with `isAdr = 'true'` on the current phase, aggregated. `group_key` = literal `"adr"`.
- `single_stock`: every figi that is neither ETF nor ADR (figis missing from `stock_metadata` default here). `group_key` = literal `"single_stock"`.
- `all`: every figi in the underlying flow table. `group_key` = literal `"all"`.

### Aggregate identities

`single_stock + etf + adr = all` exactly per `(date, callput)`. `sum(sector) ≈ single_stock` per `(date, callput)` within < 1% of gross flow (residual = single-stock figis without a current sector label). Note: because ETFs and ADRs no longer contribute to sector / industry rollups, `sum(sector) ≠ all` — the diff is the `etf + adr` contribution. `sum(industry) ≈ single_stock` too — every industry with any flow now emits a row (raw nets populated), and the honest sum lines up within the same < 1% single-stock-without-industry residual.

### Thin-industry handling

Every industry-day is emitted with populated raw daily nets AND populated rolling / z fields. `n_figi` is exposed on every row so consumers can filter thin-breadth days at query time. The 21dma and 2y baseline include ALL days regardless of `n_figi` — thin days contribute their (smaller) raw values to the trailing window and to the 2y μ/σ used to z-score. Each industry's z-score is therefore calibrated against its own historical breadth mix — a chronically-thin industry's baseline is set to its own thin-day scale, so z fires when flow is unusually large *for that industry*. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully. Structurally-thin industries (historically ~46, including `Copper`, `Uranium`, `Consulting Services`, `Regulated Water`, `Conglomerates`, `Waste Management`, `Railroads`) now emit continuous rows with populated raw flow AND populated z-scores rather than being dropped entirely. Sector, etf, adr, single_stock, and all branches always exceed the threshold and the caveat is a no-op for them.

### Valid `group_key`: sector (~11 total)

Sector matching is case-insensitive on input; the row-emitted `group_key` is always the canonical mixed-case form. For a one-line description of each sector, see the [Data dictionary → grouped_flow_signal_01_day](/documentation#grouped_flow_signal_01_day) section.

`Basic Materials`, `Communication Services`, `Consumer Cyclical`, `Consumer Defensive`, `Energy`, `Financial Services`, `Healthcare`, `Industrials`, `Real Estate`, `Technology`, `Utilities`.

### Valid `group_key`: industry (~150 total)

Case-insensitive on input; canonical mixed-case on the wire. Full enumerated list in the [Data dictionary → grouped_flow_signal_01_day](/documentation#grouped_flow_signal_01_day) section (with a one-line description of each). Passing a `group_type` value that doesn't match any known sector / industry name (or rollup literal) returns HTTP 400 with a fuzzy-match suggestion in the error body (e.g. requesting `"Semi"` returns `"Did you mean 'Semiconductors'?"`).

Roughly 46 named industries are structurally thin (never clear the 10-FIGI mark). They appear in the response every day with raw daily nets AND rolling / z fields populated; the z magnitude on those rows is not directly comparable to full-breadth days (see the thin-industry handling section above — filter on `n_figi` at query time when comparability matters). The sector / industry enumerations may drift as existing figis are reclassified or new labels are added. For the live authoritative list, call the snapshot endpoint with `category=sector` (or `industry`) and take the distinct `group_key` values from the response.

### Response columns

`date`, `group_key`, `callput`, `n_figi`, plus the 30 group-level flow columns (10 raw nets, 10 `_21dma` rolling sums, 10 z-scores; same layer shape as `underlying_flow_signal_01_day`; no metadata columns, none needed).

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- Sector-rotation dashboards on institutional-delta z-score crossings (`group_type=Technology&callput=CP`, one call per sector name)
- ETF vs single_stock vs ADR flow comparisons (`group_type=etf` / `adr` / `single_stock`)
- Sector vol-flow monitoring (`z_net_i_vega_21dma` per sector, one call per name)
- Named-industry timeseries (e.g. `group_type=Semiconductors`) plotted next to price

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `group_type` | string | Yes | A single canonical `group_key` value. Either a mixed-case sector name (e.g. `Technology`, `Financial Services`), a mixed-case industry name (e.g. `Software—Application`, `Semiconductors`), or one of the rollup literals `etf` / `adr` / `single_stock` / `all`. Case-insensitive on input; the row-emitted `group_key` is always the canonical mixed-case form. A value that doesn't match any known name returns HTTP 400 with a fuzzy-match suggestion in the body (e.g. requesting `Semi` returns `Did you mean 'Semiconductors'?`). See the endpoint description for the full enumerated lists (~11 sectors, ~150 industries, plus 4 rollup literals). Time series is gap-free — industries with <10 contributing FIGIs on the day still emit rows with raw nets populated but rolling / z fields NULL. To sweep a whole family in one call, use the snapshot endpoint's `category` parameter instead. |
| `callput` | enum: C, P, CP | No | `C` (calls only), `P` (puts only), or `CP` (default; synthetic combined view). CP is pre-computed: directional nets are `C − P` (bullish call flow + bearish put flow both express bullish conviction, so nets combine sign-flipped); greeks are `C + P` (naturally signed by option type). **The default is `CP` for a reason: every source row triples up on callput and omitting the filter triple-counts.** Case-insensitive on input (`c`, `Cp`, `CP`, `p` all accepted); the emitted row always carries the canonical upper-case form. |
| `start_date` | string | No | Earliest trading day (inclusive, YYYY-MM-DD). Trial tiers are clamped to a 730-day window; this endpoint is not figi-capped, so it is the recommended surface for broad cohort exploration on the trial tier. |
| `end_date` | string | No | Latest trading day (inclusive, YYYY-MM-DD). |
| `format` | enum: json, parquet | No | `json` (default) or `parquet` (recommended for full-history sector sweeps). |

### Sample response

```json
[
  {
    "date": "2026-07-22",
    "group_key": "Technology",
    "callput": "CP",
    "net_i_premium": 182004000.0,
    "net_i_delta": 1253000000.0,
    "net_i_vega": -82000000.0,
    "net_i_premium_21dma": 2529450000.0,
    "z_net_i_premium_21dma": 0.70,
    "z_net_i_delta_21dma": 1.94,
    "z_net_i_vega_21dma": -1.12
  }
]
```

---

## Grouped Flow Signal: Daily Snapshot

```http
GET /v0/grouped_flow_signal/01_day/snapshot
```

Single-day cross-sectional snapshot of group-level daily flow-signal rows. Omit both filters to return every group_key (~168 rows / callput / day across ~11 sectors, ~150 industries, and 4 rollup literals).

### Description

Cross-sectional snapshot of `grouped_flow_signal_01_day` on a single trading day. Every input parameter is optional; with no filters the endpoint returns every `group_key` with any flow on the latest available day (~168 rows on the default `callput = 'CP'`).

- `date` omitted → latest available trading day.
- Neither `category` nor `group_type` set → every `group_key` with flow returned in the same payload (~11 sectors + up to ~150 industries + `etf` + `adr` + `single_stock` + `all`, on the default `callput = 'CP'`). Industry rows with fewer than 10 contributing FIGIs on the requested date carry populated raw nets but NULL rolling / z fields (see "Thin-industry handling" below) — they are NOT dropped.
- `category` set → restrict the payload to a whole family: `industry` returns every industry row (up to ~150), `sector` returns just the ~11 sector rows, `rollup` returns the 4 rollup rows, `all` returns every family. Snapshot-only parameter.
- `group_type` set → restrict to a single `group_key` value (a mixed-case sector / industry name or one of the rollup literals `etf` / `adr` / `single_stock` / `all`). Case-insensitive on input; a value that doesn't match any known name returns HTTP 400 with a fuzzy-match suggestion in the body.
- `category` and `group_type` are mutually exclusive. Pass one or the other, never both.

Response shape is identical to the ranged endpoint: single `group_key` STRING column + `date` + `callput` + 30 flow columns per row (10 raw nets, 10 `_21dma` rolling sums, 10 z-scores against the trailing 2-year μ/σ of the 21dma), no stock_metadata join. See the ranged endpoint for the full sector / industry enumeration, the CP synthesis rule, and the z-score formula.

### ⚠ Not point-in-time for group classification

Same load-bearing caveat as the ranged endpoint: the sector / industry / ETF / ADR labels used to bucket each figi come from the CURRENT `stock_metadata` phase (row with `phase_end = 2099-12-31`), applied uniformly to every historical date. The signal rolling window (21dma + 2y baseline) is per bucket; only the group-membership assignment is applied retroactively.

For strict-PIT sector rollups, use the `underlying_flow_signals/01_day/snapshot` endpoint and bucket client-side against a PIT `stock_metadata` snapshot.

### ETF / ADR exclusion from sector & industry

Sector and industry aggregations EXCLUDE ETFs and ADRs (which get their own top-level `etf` / `adr` rollups). FMP tags ETFs with the issuer's legal-entity sector (typically `Financial Services`), which is meaningless for underlying exposure — so pooling ETFs into sector rollups would inflate Financial Services by 85%+ of gross flow. The exclusion is applied at the underlying-figi filter step, not on the emitted rows.

### Thin-industry handling

Every industry-day is emitted with populated raw daily nets AND populated rolling / z fields (`21dma`, `z_*`). `n_figi` is exposed on every row so consumers can filter thin-breadth days at query time. The 21dma and 2y baseline include ALL days regardless of `n_figi`, so each industry's z is calibrated against its own historical breadth mix and populates on every day. Cross-industry z magnitudes aren't directly comparable when breadth profiles differ meaningfully — filter on `n_figi` at query time when comparability matters. Structurally-thin industries (historically ~46, including `Copper`, `Uranium`, `Consulting Services`, `Regulated Water`, `Conglomerates`, `Waste Management`, `Railroads`) now emit continuous rows with populated raw flow AND populated z-scores rather than being absent. Sector, etf, adr, single_stock, and all branches always exceed the 10-FIGI mark and the caveat is a no-op for them.

### Response headers & errors

Every `/v0/*` list response carries `X-Row-Count: <N>`. Unknown query parameters return `400 Bad Request` with the list of accepted names in the body.

### Use cases

- End-of-day cross-sectional heatmap across every sector, industry, and rollup on the same day (no filters)
- Sector-rotation dashboard: `category=sector` on the latest date, plot z-score by group_key
- ETF vs single_stock vs ADR flow comparison for the latest close (`category=rollup`)
- Screen every industry for institutional 2σ+ delta fires with one call (`category=industry`, filter `z_net_i_delta_21dma > 2` client-side)

### Parameters

| Parameter | Type | Required | Description |
| --- | --- | --- | --- |
| `date` | string | No | Trading day (YYYY-MM-DD) to snapshot. Defaults to the latest available day. |
| `category` | enum: industry, sector, rollup, all | No | Family-level filter. One of `industry` / `sector` / `rollup` / `all`. Restricts the snapshot to the intended grouping family — `industry` returns every industry row with flow on the day (up to ~150), `sector` returns just the ~11 sector rows, `rollup` returns the `etf` / `adr` / `single_stock` / `all` rollup rows, `all` returns every family. Mutually exclusive with `group_type` — pass one or the other, not both. Snapshot-only; not accepted on the ranged endpoint. |
| `group_type` | string | No | Single-key filter — a canonical `group_key` value (mixed-case sector or industry name, or one of the rollup literals `etf` / `adr` / `single_stock` / `all`). Case-insensitive on input; the row-emitted `group_key` is always the canonical mixed-case form. A value that doesn't match any known name returns HTTP 400 with a fuzzy-match suggestion in the body. Mutually exclusive with `category` — use `group_type` when you want a single specific `group_key`, `category` when you want a whole family. |
| `callput` | enum: C, P, CP | No | `C` (calls only), `P` (puts only), or `CP` (default; synthetic combined view). CP is pre-computed: directional nets are `C − P` (bullish call flow + bearish put flow both express bullish conviction, so nets combine sign-flipped); greeks are `C + P` (naturally signed by option type). **The default is `CP` for a reason: every source row triples up on callput and omitting the filter triple-counts.** Case-insensitive on input (`c`, `Cp`, `CP`, `p` all accepted); the emitted row always carries the canonical upper-case form. |
| `format` | enum: json, parquet | No | `json` (default) or `parquet` (binary, ~5–10× smaller). |

### Sample response

```json
[
  {
    "date": "2026-07-22",
    "group_key": "Technology",
    "callput": "CP",
    "net_i_premium": 182004000.0,
    "net_i_delta": 1253000000.0,
    "net_i_vega": -82000000.0,
    "z_net_i_premium_21dma": 0.70,
    "z_net_i_delta_21dma": 1.94,
    "z_net_i_vega_21dma": -1.12
  },
  {
    "date": "2026-07-22",
    "group_key": "all",
    "callput": "CP",
    "net_i_premium": 812000000.0,
    "net_i_delta": 4820000000.0,
    "z_net_i_delta_21dma": 1.42
  }
]
```

---

# Flat files

Read-only HMAC access to the production bucket for systematic pipelines.

**Bucket:** `gs://prod-masscrest/v0`

Flat-files delivery is for clients whose corporate environment cannot
make outbound calls to `api.masscrest.com`. Your pipeline reads Parquet
exports directly from `gs://prod-masscrest/v0/`. The recommended path is a
**warehouse integration**: bind a Google service account minted by
Snowflake, Databricks, BigQuery, or your own GCP runtime and Masscrest
grants read-only IAM against it — no secret material in the loop.
Provision, list, and revoke bindings from the **Warehouse integrations**
card on the Flat files section of the docs page. HMAC key pairs remain
available as a fallback for Python / CLI users (see section 5).

### Trial-tier bucket

Flat-files delivery on the trial tier requires the `programmatic`
entitlement (opt-in per account; contact sales). Enabled trials get HMAC
keys against **`gs://prod-masscrest-trial`** — **not**
`gs://prod-masscrest`. The trial bucket is an aggregates-only,
daily-only mirror lagged 180 days behind current, with fresh reference
metadata. Trials without `programmatic` receive HTTP 403 on any
flat-files call. See [Trial vs. paid tier](/documentation#trial-vs-paid-tier)
on the Data dictionary for the full policy.

Flat-file delivery ships raw parquet partitions to a Masscrest-managed bucket. Two access modes are supported: **warehouse integrations** (Snowflake, Databricks, BigQuery, or any Google-service-account-based reader) that bind a warehouse principal directly to the bucket for federated reads with no key material in the loop, and **HMAC access keys** for Python / CLI users who want to read the same parquet with `gsutil`, `boto3`, `s3fs`, or DuckDB. The warehouse path is the recommended one for production pipelines; HMAC remains the fallback for local scripts and edge runtimes.

Two buckets are provisioned depending on the account tier: `gs://prod-masscrest` for paid accounts (full history, contract-level and intraday) and `gs://prod-masscrest-trial` for trial accounts with the `programmatic` entitlement enabled (aggregates-only, daily-only, 180-day-lagged). Trials without `programmatic` see HTTP 403 on any flat-files call — the entitlement is opt-in per account; contact sales to enable it. The layout, split-adjustment reconstruction, and access conventions below apply verbatim to both buckets; swap `prod-masscrest` for `prod-masscrest-trial` where noted.

## 1. Warehouse integration (recommended)

Bind a warehouse principal (Snowflake external volume, Databricks storage credential, BigQuery cloud-resource connection, or a raw Google service account) directly to the flat-files bucket. Masscrest grants read-only IAM against your principal server-side; the warehouse issues short-lived credentials to itself and streams parquet without any secret material leaving your infrastructure. Provision, list, and revoke bindings from the **Warehouse integrations** card on the Flat files section of the docs page.

### Snowflake external volume

Snowflake creates the external volume first — the associated Google service account is minted on `DESC EXTERNAL VOLUME` — then you paste that service account into the Warehouse integrations card and click **Grant access**. Once the grant flips to `active`, mount the volume as an external stage or Iceberg catalog and read parquet directly.

```sql
CREATE EXTERNAL VOLUME masscrest_flatfiles
  STORAGE_LOCATIONS = (
    (
      NAME = 'masscrest-prod'
      STORAGE_PROVIDER = 'GCS'
      STORAGE_BASE_URL = 'gcs://prod-masscrest/'
    )
  );

DESC EXTERNAL VOLUME masscrest_flatfiles;
-- Copy the STORAGE_GCP_SERVICE_ACCOUNT value (looks like
--   ...@<project>.iam.gserviceaccount.com) into the Warehouse
-- integrations card and click "Grant access".
```

Once the grant is `active`, mount the volume as a stage:

```sql
CREATE STAGE masscrest_daily
  URL = 'gcs://prod-masscrest/v0/underlying_option_flows/01_day/'
  STORAGE_INTEGRATION = masscrest_flatfiles
  DIRECTORY = (ENABLE = TRUE);

SELECT $1:figi::string AS figi, $1:symbol::string AS symbol, $1:date::date
FROM @masscrest_daily/year=2026/date=2026-08-14/
     (FILE_FORMAT => (TYPE = PARQUET))
LIMIT 10;
```

### Databricks storage credential

Databricks on GCP mints a Google service account per storage credential. Paste that email into the Warehouse integrations card and click **Grant access**. Once active, create an external location and query parquet through Unity Catalog.

```sql
-- In your Databricks workspace:
CREATE STORAGE CREDENTIAL masscrest_flatfiles
  WITH DATABRICKS_MANAGED_IDENTITY
  COMMENT 'Masscrest flat-files read-only';

-- Grab the service account email:
DESCRIBE STORAGE CREDENTIAL masscrest_flatfiles;
-- Paste the "Service Account" value into the Warehouse
-- integrations card and click "Grant access".

CREATE EXTERNAL LOCATION masscrest_prod
  URL 'gs://prod-masscrest/'
  WITH (STORAGE CREDENTIAL masscrest_flatfiles);
```

Once the grant is `active`:

```sql
SELECT *
FROM parquet.`gs://prod-masscrest/v0/underlying_option_flows/01_day/year=2026/date=2026-08-14/`
LIMIT 10;
```

### BigQuery cloud-resource connection

BigQuery uses a cloud-resource connection whose delegated service account you paste into the Warehouse integrations card.

```
bq mk --connection \
  --location=us \
  --project_id=<your-gcp-project> \
  --connection_type=CLOUD_RESOURCE \
  masscrest_flatfiles

bq show --format=prettyjson --connection \
  <your-gcp-project>.us.masscrest_flatfiles | jq .cloudResource.serviceAccountId
# Paste that service account into the Warehouse integrations card.
```

Once the grant is `active`, create an external table:

```sql
CREATE OR REPLACE EXTERNAL TABLE `<your-project>.masscrest.underlying_option_flows_01_day`
WITH CONNECTION `us.masscrest_flatfiles`
OPTIONS (
  format = 'PARQUET',
  uris = ['gs://prod-masscrest/v0/underlying_option_flows/01_day/year=*/date=*/*.parquet'],
  hive_partition_uri_prefix = 'gs://prod-masscrest/v0/underlying_option_flows/01_day/'
);
```

### Raw Google service account

For any GCP-native runtime (Cloud Run, GKE, Composer, Dataflow, custom Compute Engine), create a service account, paste its email into the Warehouse integrations card, and read the bucket with the standard `google-cloud-storage` client library once the grant is `active`.

```
gcloud iam service-accounts create masscrest-reader \
  --project=<your-gcp-project> \
  --display-name="Masscrest flat-files reader"

gcloud iam service-accounts list \
  --project=<your-gcp-project> \
  --filter="displayName:Masscrest flat-files reader" \
  --format="value(email)"
# Paste the resulting email into the Warehouse integrations card.
```

### AWS S3 runtimes

GCS does not accept AWS IAM roles as readers, so there is no native principal-delegation path from an AWS runtime. Run `rclone sync` on a schedule from EC2 / ECS / Lambda using an HMAC key pair (see section 5) to mirror flat files into a bucket you own. `rclone` streams parquet directly bucket-to-bucket without staging locally.

```
# rclone.conf snippet
[masscrest]
type = s3
provider = Other
access_key_id = <your HMAC access key>
secret_access_key = <your HMAC secret>
endpoint = https://storage.googleapis.com
region = auto

rclone sync \
  masscrest:prod-masscrest/v0/underlying_option_flows/01_day/year=2026/date=2026-08-14/ \
  s3:your-analytics-bucket/masscrest/underlying_option_flows/01_day/2026-08-14/
```

### Azure ADLS runtimes

GCS does not accept Azure Managed Identities as readers, so there is no native principal-delegation path from an Azure runtime. Use `rclone` from Azure Container Instances or Azure Functions with an HMAC key pair (section 5) to sync into ADLS Gen2 on a daily schedule.

### Grant lifecycle

Each principal binding runs through `pending → active → failed | revoked` server-side. The Warehouse integrations card polls until the grant flips terminal, then displays a `gsutil cat` command against a small verification file so you can prove end-to-end reachability from your warehouse. Grants persist across bucket-tier upgrades: when a trial account upgrades to paid, the principal is automatically re-bound to `gs://prod-masscrest`.

## 2. Bucket layout

All flat files land under `gs://prod-masscrest/v0/`, hive-partitioned by `year=YYYY/date=YYYY-MM-DD/` where `date` is the trading date. New partitions for `date = T-1` land in the 05:00–09:00 UTC window on calendar day `T`. The reference tables (`stock_mapping/`) are full-snapshot reloads: same filename pattern each day, no partition hierarchy. `_manifests/year=YYYY/date=YYYY-MM-DD/manifest.json` is written as the very last step of the daily publish and is the delivery-complete signal for polling clients (see the Data dictionary overview for the payload schema).

```
gs://prod-masscrest/v0/
├── _manifests/
│   └── year=YYYY/date=YYYY-MM-DD/manifest.json    # delivery-complete signal, written last
├── stock_mapping/
│   ├── stock_metadata_*.parquet          # full snapshot, rewritten daily
│   └── split_factors_*.parquet           # full snapshot, rewritten daily
├── px_data/
│   ├── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet
│   └── 10_min/year=YYYY/date=YYYY-MM-DD/*.parquet
├── option_flows/
│   ├── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet
│   └── 10_min/year=YYYY/date=YYYY-MM-DD/*.parquet
├── underlying_option_flows/              # underlying-level rollups
│   ├── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet
│   └── 10_min/year=YYYY/date=YYYY-MM-DD/*.parquet
├── underlying_flow_signal/               # underlying-level flow-signal derivatives
│   └── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet
└── grouped_flow_signal/
    └── data-*.parquet                    # full snapshot, rewritten daily
```

### Trial-tier bucket

Trial accounts with the `programmatic` entitlement land on `gs://prod-masscrest-trial` instead of the production bucket. The tree is a strict subset: aggregate flow tables only, daily grain only, and every partition is 180 days behind the current session.

```
gs://prod-masscrest-trial/v0/
├── stock_mapping/
│   ├── stock_metadata_*.parquet          # fresh; full mirror of production
│   └── split_factors_*.parquet           # fresh; full mirror of production
├── underlying_option_flows/
│   └── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet     # T-180d lag
├── underlying_flow_signal/
│   └── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet     # T-180d lag
├── grouped_flow_signal/
│   └── data-*.parquet                    # fresh; full mirror (not figi-capped)
└── px_data/
    └── 01_day/year=YYYY/date=YYYY-MM-DD/*.parquet     # T-180d lag
```

The trial bucket does **not** carry contract-level `option_flows/*`, 10-minute intraday `*_10_min/*` partitions, or any positioning data. A daily append at 04:00 UTC extends the lagged tables by one partition (`T-180d`). Reference data (`stock_mapping/` and `grouped_flow_signal/`) refreshes on the same daily cadence as the production bucket. On upgrade to a paid account, your trial HMAC key is revoked at the moment of upgrade — call `POST /v0/flat-files/credentials/rotate` to mint a new key against `gs://prod-masscrest`, then re-point your `~/.boto` / gsutil config to the production bucket.

## 3. Split-adjusted price and volume: local reconstruction

`px_data/01_day` and `px_data/10_min` ship raw unadjusted `close` and `volume` per `(date, figi, symbol)`. Split-adjustment is decoupled: `stock_mapping/split_factors_*.parquet` (a full snapshot of `split_factors`) is the authoritative source, refreshed daily. This lets you keep raw prints for audit and derive `adj_close` / `adj_volume` at query time.

### `split_factors` schema

One row per `(figi, valid_from, valid_to)`, non-overlapping intervals covering the FIGI's timeline. The `cum_split_factor` column holds the cumulative product of every split from `valid_from + 1 day` forward within the entity's active phase. To convert a raw close on date `X` into a current-shares-equivalent price, divide by `cum_split_factor`; to convert raw volume, multiply. The most recent interval always carries `1.0`. No-split entities carry a single row spanning their full active phase with `cum_split_factor = 1.0`; same shape, no special case.

### Ticker context

For time series where the ticker has changed (FB → META, TWTR → X, etc.), join on `figi` (stable), never on `symbol` (point-in-time and recyclable). `stock_metadata` provides `(figi, symbol, phase_start, phase_end, terminal_symbol, ...)`; join with `date BETWEEN phase_start AND phase_end` to attach the correct symbol / sector / ETF flag for the session.

### Snippet A: pandas + pyarrow

```python
import pandas as pd
import s3fs
# requires: pip install pandas pyarrow s3fs
# s3fs signs with the HMAC pair against the S3-compatible GCS endpoint.
# Do not use gcsfs or google-cloud-storage here — those libraries
# authenticate via OAuth / ADC and reject HMAC credentials.
fs = s3fs.S3FileSystem(
    key="<your HMAC access key>",
    secret="<your HMAC secret>",
    client_kwargs={"endpoint_url": "https://storage.googleapis.com"},
)

d = "2026-07-24"
px = pd.read_parquet(
    f"s3://prod-masscrest/v0/px_data/01_day/year=2026/date={d}/",
    filesystem=fs,
)
sh_files = ["s3://" + p for p in fs.glob("prod-masscrest/v0/stock_mapping/split_factors_*.parquet")]
md_files = ["s3://" + p for p in fs.glob("prod-masscrest/v0/stock_mapping/stock_metadata_*.parquet")]
sh = pd.read_parquet(sh_files, filesystem=fs,
                     columns=["figi", "valid_from", "valid_to", "cum_split_factor"])
md = pd.read_parquet(md_files, filesystem=fs,
                     columns=["figi", "symbol", "phase_start", "phase_end"])

px["date"] = pd.to_datetime(px["date"]).dt.date
sh["valid_from"] = pd.to_datetime(sh["valid_from"]).dt.date
sh["valid_to"] = pd.to_datetime(sh["valid_to"]).dt.date

merged = px.merge(sh, on="figi", how="left")
in_range = (merged["date"] >= merged["valid_from"]) & (merged["date"] <= merged["valid_to"])
merged = merged[in_range].copy()
merged["adj_close"] = merged["close"] / merged["cum_split_factor"]
merged["adj_volume"] = merged["volume"] * merged["cum_split_factor"]

# Optional: attach point-in-time symbol from stock_metadata.
merged = merged.merge(md, on="figi", how="left", suffixes=("", "_md"))
merged = merged[(merged["date"] >= merged["phase_start"]) & (merged["date"] <= merged["phase_end"])]
out = merged[["figi", "date", "symbol", "close", "volume", "adj_close", "adj_volume"]]
```

### Snippet B: DuckDB

```sql
INSTALL httpfs;
LOAD httpfs;
SET s3_endpoint='storage.googleapis.com';
SET s3_url_style='path';
SET s3_access_key_id='<your HMAC access key>';
SET s3_secret_access_key='<your HMAC secret>';

WITH px AS (
  SELECT date, figi, symbol, close, volume
  FROM read_parquet('s3://prod-masscrest/v0/px_data/01_day/year=2026/date=2026-07-24/*.parquet')
),
sh AS (
  SELECT figi, valid_from, valid_to, cum_split_factor
  FROM read_parquet('s3://prod-masscrest/v0/stock_mapping/split_factors_*.parquet')
),
md AS (
  SELECT figi, symbol AS pit_symbol, phase_start, phase_end
  FROM read_parquet('s3://prod-masscrest/v0/stock_mapping/stock_metadata_*.parquet')
)
SELECT
    px.figi,
    px.date,
    COALESCE(md.pit_symbol, px.symbol) AS symbol,
    px.close,
    px.volume,
    px.close  / sh.cum_split_factor AS adj_close,
    px.volume * sh.cum_split_factor AS adj_volume
FROM px
LEFT JOIN sh USING (figi)
LEFT JOIN md USING (figi)
WHERE px.date BETWEEN sh.valid_from AND sh.valid_to
  AND px.date BETWEEN md.phase_start AND md.phase_end;
```

### Precision

`cum_split_factor` is stored at 12 decimal places to preserve heavy reverse-split stacks (e.g. UVXY's true factor at `1900-01-01` is ~6.7e-11). Do **not** round to 6 decimals; that historically truncated ~200 heavy-reverse-splitter FIGIs to `0.0` and silently zeroed downstream adjustments. Also treat `cum_split_factor IS NULL` as "no valid adjustment for this period"; never coerce to `0.0`, as the extreme tail of penny-stock reverse-split chains underflows Float64 and is emitted as `NULL` by design.

## 4. Applying the same adjustment to flows

`option_flows_01_day` and its 10-minute sibling already carry a split-adjusted underlying price via the joined `adj_close` field; clients using the provided column need no local derivation. To overlay flows on your own price series, or compute ratios like `flow_per_shares_outstanding` where the denominator is your own split-adjusted volume, apply the same `close / cum_split_factor` and `volume * cum_split_factor` logic from Section 3 against the raw px files. Join keys are identical: `figi` for the row, `(valid_from, valid_to)` on `split_factors` for the factor. See the dictionary entries for [`option_flows_01_day`](/documentation#option-flows-01-day) and [`px_01_day`](/documentation#px-01-day) for the full column contracts.

## 5. HMAC access keys (Python / CLI users)

For local scripts, notebooks, and edge runtimes that can't use a warehouse integration, Masscrest also issues HMAC key pairs that authenticate against the GCS S3-compatible endpoint. Provision or rotate the pair from the **HMAC access key** card on the Flat files section of the docs page. This path is a fallback — for production pipelines, prefer the warehouse integration in section 1 (no secret material, shorter blast radius on rotation, native to the SQL engine you're already running).

### S3-compatible access

GCS speaks the S3 protocol at `https://storage.googleapis.com`. HMAC keys authenticate against the bucket the same way native S3 keys do. Bucket name is `prod-masscrest` (drop the `gs://` prefix); every other path segment above is a key prefix. Trial accounts substitute `prod-masscrest-trial` throughout the snippets below — the client library, endpoint URL, and signing flow are identical.

```python
import boto3
from datetime import date, timedelta

s3 = boto3.client(
    "s3",
    endpoint_url="https://storage.googleapis.com",
    aws_access_key_id="<your HMAC access key>",
    aws_secret_access_key="<your HMAC secret>",
    region_name="auto",
)

d = date.today() - timedelta(days=1)
prefix = f"v0/px_data/01_day/year={d.year}/date={d.isoformat()}/"
for obj in s3.list_objects_v2(Bucket="prod-masscrest", Prefix=prefix).get("Contents", []):
    print(obj["Key"], obj["Size"])
```

### From AWS (Lambda, EC2, ECS, Glue)

The same boto3 client works unchanged from any AWS runtime. No GCP setup is required; the Masscrest HMAC key is just another set of S3 credentials, and the `--endpoint-url` override reroutes the request to `storage.googleapis.com`. The recommended pattern is to store your Masscrest HMAC pair in AWS Secrets Manager (or SSM Parameter Store) and fetch it at runtime:

```python
import boto3, json

secrets = boto3.client("secretsmanager", region_name="us-east-1")
creds = json.loads(secrets.get_secret_value(SecretId="masscrest/hmac")["SecretString"])

s3 = boto3.client(
    "s3",
    endpoint_url="https://storage.googleapis.com",
    aws_access_key_id=creds["access_key"],
    aws_secret_access_key=creds["secret"],
    region_name="auto",
)
```

The AWS CLI works with the same override:

```
aws s3 --endpoint-url https://storage.googleapis.com ls s3://prod-masscrest/v0/
aws s3 --endpoint-url https://storage.googleapis.com cp \
    s3://prod-masscrest/v0/px_data/01_day/year=2026/date=2026-07-24/ ./local/ --recursive
```

Common downstream patterns from AWS:

- **Sync into your own S3 for Athena or Redshift Spectrum.** Schedule a small EC2, Lambda, or Glue job that copies each day's partition into a bucket you own, then query the parquet directly. No format translation, no re-shaping needed.
- **External stages in Snowflake or Databricks on AWS.** Both support S3-compatible sources; point the external stage at `s3://prod-masscrest/...` with the `--endpoint-url` override and load daily.
- **Step Functions or Glue Workflows.** Poll `s3://prod-masscrest/v0/_manifests/year=YYYY/date=YYYY-MM-DD/manifest.json` as the "day is ready" signal; the manifest also enumerates every parquet the run wrote (fields: `workflow_run_id`, `trading_date`, `published_at`, `files: [{path, byte_count}]`), so it doubles as the file list for your job.

### Notes

- Credentials are read-only and list-only. They cannot upload, delete, or
  overwrite objects.
- Disabling flat-files access on the account revokes the key server-side
  immediately.
- For high-volume downloads, run your workload in the same region as the
  bucket to avoid egress charges.