Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.allium.so/llms.txt

Use this file to discover all available pages before exploring further.

The hyperliquid.raw.orderbook_snapshots table contains point-in-time snapshots of every resting order on the Hyperliquid order book. New snapshots arrive approximately every 15 minutes; each snapshot is one row per resting order at that moment. The same order will appear in successive snapshots until it is filled or canceled, so you can observe how an individual order’s size, trigger conditions, or status evolve over time.

​
Table Columns

Column NameDescription
snapshot_idUUID identifying a single snapshot. All rows from the same snapshot share this value. Use it to scope a query to one complete book at one point in time.
snapshot_timestampUTC timestamp of the snapshot. Constant across all rows of the same snapshot_id.
max_order_timestampThe latest order_timestamp represented in the snapshot. Useful as a “what’s new” cutoff when combining the snapshot with later orders. Identical for every row in a snapshot.
userThe address of the user who placed the order.
coinA unique identifier for the asset being traded:
• The coin for perpetuals is the standard token symbol, e.g. HYPE
• The coin for spot tokens is an ID representing a pair of tokens based on Hyperliquid’s metadata, e.g. @4 represents token 5/token 0, which corresponds to JEFF/USDC. The metadata is available from the info endpoint of Hyperliquid’s API.
sideB - Buy ・ A - Ask (Sell)
limit_priceThe maximum price the buyer or seller is willing to pay.
sizeThe current size of the order. If the order has been partially filled since it was placed, this is the size of the unfilled remainder.
original_sizeThe size of the order when it was first placed. If the order has not been partially filled, size equals original_size.
order_idOrder ID generated by Hyperliquid (also referred to as oid). Note: order_id is not unique on its own — Hyperliquid reuses the oid space across markets. Use (snapshot_id, coin, order_timestamp, order_id) to identify a unique row.
order_timestampThe time the order was originally placed on Hyperliquid. Unlike snapshot_timestamp, this can be hours or days before the snapshot.
order_typeThe type of order. Refer to the Hyperliquid Docs.
time_in_forceBehaviour for the order. Refer to the Hyperliquid Docs.
trigger_conditionWhat will make this order become active and go into the order book. For example Price below 1560.1. The value can also be Triggered, meaning the trigger condition has already been met.
is_triggerBoolean. Whether this order has a trigger condition (see trigger_condition).
trigger_priceThe price at which the trigger fires, if is_trigger is true.
is_position_tpslBoolean. Whether this order is a take profit or stop loss order.
is_reduce_onlyBoolean. Whether this order reduces the current position, instead of opening a new order in the opposite direction. Refer to the Hyperliquid Docs.
client_order_idCustom order ID provided by the user (also referred to as cloid). Nullable.
childrenJSON variant. Will contain one or two orders that will be added once the parent order is triggered (typically a take profit and/or stop loss).

​
Notes

​
Identifying a unique row

Because the same order persists across snapshots and Hyperliquid reuses the oid space across markets, the minimum unique grain is: 
(snapshot_id, coin, order_timestamp, order_id)
order_id alone is not unique — the same numeric oid may appear for different orders on different coins.

​
Snapshot grouping

All rows that share a snapshot_id belong to the same book at one moment. To get the most recent complete book for a coin: 
WITH latest AS (
  SELECT snapshot_id
  FROM hyperliquid.raw.orderbook_snapshots
  WHERE coin = 'BTC'
  QUALIFY ROW_NUMBER() OVER (ORDER BY snapshot_timestamp DESC) = 1
)
SELECT side, limit_price, size, user, order_timestamp
FROM hyperliquid.raw.orderbook_snapshots
WHERE coin = 'BTC'
  AND snapshot_id = (SELECT snapshot_id FROM latest)
ORDER BY side, limit_price::float DESC;

​
Tracking a single order over time

Because the same order appears in multiple snapshots until it is filled or canceled, you can observe its evolution: 
SELECT
  snapshot_timestamp,
  size,
  limit_price,
  trigger_condition
FROM hyperliquid.raw.orderbook_snapshots
WHERE coin = 'BTC'
  AND user = '0x...'
  AND order_id = '414287276291'
ORDER BY snapshot_timestamp;

​
Freshness via max_order_timestamp

max_order_timestamp is the latest order placement time represented in a snapshot. The gap between snapshot_timestamp and max_order_timestamp is useful for detecting stale or thinly-populated books: 
SELECT
  snapshot_id,
  snapshot_timestamp,
  max_order_timestamp,
  DATEDIFF('second', max_order_timestamp, snapshot_timestamp) AS lag_seconds
FROM hyperliquid.raw.orderbook_snapshots
WHERE snapshot_timestamp > DATEADD(hour, -3, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY 2 DESC;

​
Reconstructing a fresher book from hyperliquid.raw.orders and hyperliquid.raw.fills

New snapshots arrive approximately every 15 minutes, so the latest one can be up to ~15 minutes behind real time. To bring it forward, combine three sources:
What changed since the snapshotSource tableHow to apply
Orders removed from the book (filled, canceled, liquidated, rejected, etc.)hyperliquid.raw.ordersDrop the order from the reconstructed book if its latest status_change_timestamp > snapshot_timestamp and the resulting status is terminal.
Orders added to the book after the snapshothyperliquid.raw.ordersInclude any order whose latest status is non-terminal and whose order_timestamp > max_order_timestamp.
Orders partially filled since the snapshot (still resting, but with reduced size)hyperliquid.raw.fillsFor each surviving order, subtract the sum of fill sizes with timestamp > snapshot_timestamp from the snapshot’s size. A partial fill on Hyperliquid does not change order status — the order keeps resting with a smaller size, so it would never appear in the orders table change stream.
max_order_timestamp is the useful lower bound for the “what’s new” filter: any order placed at or before it is already represented in the snapshot, so the orders lookup only needs to consider placements after that point.

​
Terminal vs non-terminal statuses

hyperliquid.raw.orders records every status change for every order. To decide whether an order is still resting, take its latest status (by status_change_timestamp) and check whether that status is terminal. Refer to the Hyperliquid order-status reference for the full taxonomy; broadly:
  • Terminal (order leaves the book): Filled, Canceled, MarginCanceled, Liquidated, SelfTradeCanceled, ReduceOnlyCanceled, VaultWithdrawalCanceled, OpenInterestCapCanceled, DelistedCanceled, SiblingFilledCanceled, ScheduledCancel, and the various *Rejected statuses.
  • Non-terminal (order still resting): Open, Triggered, and trigger-condition modifications.

​
SQL sketch

-- Reconstruct BTC's book as of "now" from the latest snapshot,
-- removing orders that have since terminated, adjusting size for
-- partial fills, and adding orders placed after the snapshot.
WITH latest AS (
  SELECT snapshot_id, snapshot_timestamp, max_order_timestamp
  FROM hyperliquid.raw.orderbook_snapshots
  WHERE coin = 'BTC'
  QUALIFY ROW_NUMBER() OVER (ORDER BY snapshot_timestamp DESC) = 1
),

-- User-maintained list — keep current with Hyperliquid's status taxonomy.
terminal_statuses(status) AS (
  SELECT column1 FROM VALUES
    ('Filled'), ('Canceled'), ('MarginCanceled'), ('Liquidated'),
    ('SelfTradeCanceled'), ('ReduceOnlyCanceled'),
    ('VaultWithdrawalCanceled'), ('OpenInterestCapCanceled'),
    ('DelistedCanceled'), ('SiblingFilledCanceled'), ('ScheduledCancel'),
    ('Rejected'), ('TickRejected'), ('MinTradeNtlRejected'),
    ('PerpMarginRejected'), ('ReduceOnlyRejected'),
    ('BadAloPxRejected'), ('IocCancelRejected'),
    ('BadTriggerPxRejected'), ('MarketOrderNoLiquidityRejected')
),

-- Latest status per order since the snapshot.
order_status_since AS (
  SELECT o.user, o.order_id, o.order_timestamp,
         o.side, o.limit_price, o.size, o.status
  FROM hyperliquid.raw.orders o, latest
  WHERE o.coin = 'BTC'
    AND o.status_change_timestamp > latest.snapshot_timestamp
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY o.user, o.order_id, o.order_timestamp
    ORDER BY o.status_change_timestamp DESC
  ) = 1
),

-- Snapshot rows whose orders are still resting (didn't go terminal).
snapshot_survivors AS (
  SELECT obs.user, obs.order_id, obs.order_timestamp,
         obs.side, obs.limit_price, obs.size
  FROM hyperliquid.raw.orderbook_snapshots obs, latest
  WHERE obs.coin = 'BTC' AND obs.snapshot_id = latest.snapshot_id
    AND NOT EXISTS (
      SELECT 1 FROM order_status_since s
      WHERE s.user = obs.user
        AND s.order_id = obs.order_id
        AND s.order_timestamp = obs.order_timestamp
        AND s.status IN (SELECT status FROM terminal_statuses)
    )
),

-- Net fill size against each surviving order since the snapshot.
fills_since AS (
  SELECT user, order_id, SUM(size::number(38,10)) AS filled_size
  FROM hyperliquid.raw.fills, latest
  WHERE coin = 'BTC' AND timestamp > latest.snapshot_timestamp
  GROUP BY user, order_id
),

-- Snapshot-side resting orders, with size adjusted for partial fills.
adjusted_snapshot AS (
  SELECT s.user, s.order_id, s.order_timestamp, s.side, s.limit_price,
         GREATEST(s.size::number(38,10) - COALESCE(f.filled_size, 0), 0) AS size
  FROM snapshot_survivors s
  LEFT JOIN fills_since f USING (user, order_id)
),

-- Orders that appeared in the book after the snapshot.
new_orders AS (
  SELECT s.user, s.order_id, s.order_timestamp,
         s.side, s.limit_price, s.size
  FROM order_status_since s, latest
  WHERE s.order_timestamp > latest.max_order_timestamp
    AND s.status NOT IN (SELECT status FROM terminal_statuses)
)

SELECT * FROM adjusted_snapshot WHERE size > 0
UNION ALL
SELECT * FROM new_orders;
The terminal_statuses list above is illustrative — keep it in lockstep with the Hyperliquid order-status reference. Missing a status will keep terminated orders in the reconstructed book; over-listing one will drop orders that should still rest.

​
Query tips

  • For efficient date-range queries, filter on snapshot_timestamp::date.