# OnchainDivers Examples

> Executable examples for the OnchainDivers Solana, Polymarket, HyperLiquid, and Robinhood Chain indexers

## Bitcoin 5m Up/Down across two order books

This feature example starts from a question that requires all three
[OnchainDivers](https://onchaindivers.com) data surfaces: what did the prediction
market see while the underlying BTC futures book moved during one five-minute
resolution window?

![Reconstructed Bitcoin five-minute Polymarket and HyperLiquid books](/examples/bitcoin-5m-updown.png)

The image above is not checked in and cannot go stale unnoticed. The Python
checker regenerates it from raw data before every successful documentation
build.

The same downloaded raw files also drive the
[simple 300 ms microprice research example](/microprice-research), so the large
HyperLiquid checkpoint is fetched only once per strict build.

### What the script does

`examples/cross_venue/bitcoin_5m_updown.py` performs the complete workflow:

1. It asks `polymarket.raw_event_meta` for the completed
   `btc-updown-5m-<epoch>` event near UTC now minus 24 hours, with a 15-minute
   archive-finalization buffer. The epoch suffix
   defines the exact interval; the script does not mistake metadata creation
   time for market time. Tiny archive-edge files still being finalized are
   skipped in favor of the nearest substantial dedicated capture, and a window
   away from the UTC hour boundary avoids downloading a redundant hourly shard.
2. It asks `polymarket.raw_market_meta` for the Up and Down rows, requires their
   embedded event ID to match, and obtains both CLOB token IDs. No market or
   token ID is hard-coded.
3. It downloads the dedicated `<market-slug>.log.zst` file, extracts every
   explicit best-bid/ask transition during the five minutes, and independently
   replays full snapshots and price-level changes for both outcomes at close.
4. It binary-searches HyperLiquid checkpoint timestamps, downloads the newest
   MessagePack checkpoint before the interval and every required compressed
   hourly diff shard, then loads BTC perpetual action asset `0` at size precision
   `5`.
5. It applies new, update, and remove messages by order ID while maintaining
   aggregated bid and ask levels. Once per second it records best bid, best ask,
   midpoint, spread, live order count, and depth within ten basis points.
6. It renders four synchronized panels: BTC bid/ask, Up/Down probability,
   normalized BTC return versus Up probability, and futures depth/spread.

The adjacent JSON summary records the selected interval, resolved token IDs,
sample counts, final order count, raw byte count, and chart path for automated
checks.

### Run it reproducibly

All dependencies and execution happen inside the same checker image used by the
site build:

```sh
docker build --target checker-base -t onchaindivers-docs:checker-base .
docker run --rm \
  --env-file .env \
  --volume "$PWD/docs/public/examples:/app/docs/public/examples" \
  onchaindivers-docs:checker-base \
  python3 -u examples/cross_venue/bitcoin_5m_updown.py
```

The strict build invokes the same `run()` function after it has scanned both
configured archives. It requires two Polymarket outcomes, the full 300
one-second HyperLiquid samples, valid non-crossed books, and a substantial PNG.
Any metadata drift, archive gap, raw-format change, truncated file, broken replay,
or rendering failure prevents the static site from being built.


## Global fees API

The fees endpoint returns aggregate costs for one Solana mint. Configure its
complete URL privately; examples never contain the service host or port.

```dotenv
FEES_URL=https://fees.example.com/global-fees
```

Run the checked-in client:

```sh
python3 examples/fees/global_fees.py \
  So11111111111111111111111111111111111111112
```

The client appends the `mint` query parameter safely, enforces a timeout, avoids
printing the configured endpoint on failure, and validates these integer fields:

* `total`
* `transaction_fees`, split into `base_fees` and `priority_fees`
* `tips`
* `trading_fees`
* `tx_count` and `success_count`

During a strict documentation build the same call is made against the privately
configured service. The build checks that totals are non-negative and that the
successful transaction count cannot exceed the total transaction count.


## Solana Geyser service

The original Geyser page has been incorporated into the complete
[Solana Nodes and Low-Latency Feeds](/solana-nodes) offering.

Basic shared gRPC remains available for **$200/month**. The new page also covers
the $700 very-fast tier, the $1,200 ultra-low-latency tier, the $3,300 dedicated
custom tier, shreds, and preconfirmation feeds.


## Simple microprice research

This example asks a deliberately small market-microstructure question: does
visible bid/ask imbalance contain information about the midpoint 300
milliseconds later, either on the same venue or across venues?

It uses the same raw files and the same Bitcoin five-minute interval as the
[cross-venue order-book reconstruction](/bitcoin-5m-cross-venue): the dedicated
Polymarket Bitcoin Up/Down capture and the matching HyperLiquid BTC perpetual
checkpoint plus hourly diffs. During the Docker build those large files are
downloaded once, then shared by both analyses.

![Polymarket and HyperLiquid microprice and imbalance heatmaps](/examples/microprice-research.png)

The image is regenerated from a recent historical interval during every strict
documentation build. It is not a static research result checked into the site.

### Microprice and imbalance

For best bid price `P_b`, best ask price `P_a`, displayed bid size `Q_b`, and
displayed ask size `Q_a`, normalized order-book imbalance is:

```text
I = (Q_b - Q_a) / (Q_b + Q_a)
```

`I` lies between `-1` and `+1`. Positive values mean more displayed size rests
at the bid; negative values mean more rests at the ask.

The level-one microprice weights each quote by the size on the opposite side:

```text
microprice = (P_a × Q_b + P_b × Q_a) / (Q_b + Q_a)
           = midpoint + (spread / 2) × I
```

This orientation is intentional. A relatively large bid queue moves the
microprice toward the ask, while a relatively large ask queue moves it toward
the bid. The microprice equals the ordinary midpoint when both displayed sizes
are equal.

For the two-level studies, `Q_b` is the sum of sizes at the best two bid prices
and `Q_a` is the sum at the best two ask prices. This is a simple depth-imbalance
extension: the price in the microprice formula remains level one, while the
signal sees slightly deeper liquidity.

### The four heatmaps

The Polymarket series uses the Bitcoin **Up** outcome. Every predictor state is
paired with the most recent target-venue midpoint observable at time `t` and at
`t + 300 ms`.

1. **Polymarket L1 imbalance → Polymarket move.** The response is the Up-token
   midpoint change in cents after 300 ms.
2. **Polymarket L2 imbalance → HyperLiquid move.** The response is the BTC
   perpetual midpoint return in basis points after 300 ms.
3. **HyperLiquid L2 imbalance → Polymarket move.** The response is the Up-token
   midpoint change in cents after 300 ms.
4. **HyperLiquid L2 imbalance → HyperLiquid move.** The response is the BTC
   perpetual midpoint return in basis points after 300 ms.

Cell color is `log(1 + observations)`, so dense zero-move regions do not hide
sparser tails. The green line is the mean future movement inside each imbalance
bin. Every panel reports its aligned observation count and the raw Pearson
correlation between imbalance and the future move.

### Replay and clock alignment

`examples/cross_venue/microprice_research.py` does not infer size from the
best-bid/ask chart stream. It performs full book reconstruction:

* Polymarket `book` messages replace the complete selected outcome book;
  subsequent `price_change` messages update or remove individual price levels.
  Messages sharing a recorder millisecond are applied atomically. A rare
  incomplete recorder state that remains crossed after grouping is omitted,
  while its mutations are retained for the next complete state.
* HyperLiquid starts from the BTC order-ID checkpoint and applies every new,
  update, and remove diff. Updates sharing a block timestamp are applied before
  the state is sampled, avoiding artificial half-block books.
* Both venues retain event-time states at millisecond precision. Alignment uses
  an as-of lookup: the state at a requested instant is the last state known at
  or before that instant. If no update occurs during 300 ms, the measured move
  is correctly zero.

The script writes a JSON summary beside the image containing the interval,
state counts, horizon, observation counts, correlations, plot ranges, and
output path.

### Run it reproducibly

The standalone command selects and downloads its own recent interval:

```sh
docker build --target checker-base -t onchaindivers-docs:checker-base .
docker run --rm \
  --env-file .env \
  --volume "$PWD/docs/public/examples:/app/docs/public/examples" \
  onchaindivers-docs:checker-base \
  python3 -u examples/cross_venue/microprice_research.py
```

The full documentation checker is more efficient: it downloads the raw market
once and calls `run_downloaded()` for both cross-venue examples before deleting
the temporary inputs.

### Interpretation limits

This page demonstrates a reproducible workflow, not a trading claim. One
five-minute interval is far too small for inference; displayed depth can be
cancelled; venue clocks and network delivery paths differ; and the study does
not model queue position, fees, latency, fills, or market impact. A serious
backtest should repeat the same pipeline over many non-overlapping markets,
retain out-of-sample periods, and include executable-price and cost assumptions.

Data access and additional indexer examples are available from
[OnchainDivers](https://onchaindivers.com).


## Solana Nodes and Low-Latency Feeds

OnchainDivers provides managed Solana data infrastructure for applications that
need anything from a standard filtered gRPC stream to the earliest available
transaction-execution signals. All prices below are monthly in USD.

### Node access

| Service                | Topology  |            Price | Best for                                                                               |
| ---------------------- | --------- | ---------------: | -------------------------------------------------------------------------------------- |
| Basic gRPC             | Shared    |   **$200/month** | Indexers, dashboards, account monitoring, and general real-time transaction feeds      |
| Very-fast gRPC         | Shared    |   **$700/month** | Latency-sensitive production consumers that still want shared infrastructure           |
| Ultra-low-latency gRPC | Shared    | **$1,200/month** | Trading systems that need approximately 1 ms lower delivery latency than the $700 tier |
| Dedicated custom gRPC  | Dedicated | **$3,300/month** | The $1,200 low-latency stack on an isolated node with customer-specific configuration  |

#### Basic shared gRPC — $200/month

The shared gRPC service provides filtered transactions, account updates, slots,
blocks, and block metadata without requiring customers to operate validator-grade
hardware. It is the practical default for indexers and applications that need a
reliable push stream instead of polling JSON-RPC.

#### Very-fast shared gRPC — $700/month

This is the faster shared gRPC tier for latency-sensitive production consumers.
It retains shared economics while using the lower-latency delivery path intended
for trading and time-sensitive ingestion.

#### Ultra-low-latency shared gRPC — $1,200/month

This shared tier reduces delivery latency by approximately **1 ms** compared with
the $700 service. It is intended for systems where a millisecond is materially
important but isolated node capacity is unnecessary.

#### Dedicated custom gRPC — $3,300/month

The dedicated tier runs the $1,200 low-latency stack on an isolated Solana node
with customer-specific configuration. Capacity is not shared with unrelated
customers, making this the right choice for sustained high-volume ingestion and
production trading systems that need predictable resources and custom tuning.

### Earliest-data feeds

| Feed             |            Price | What it exposes                                                                                                           |
| ---------------- | ---------------: | ------------------------------------------------------------------------------------------------------------------------- |
| Shreds           |   **$500/month** | Raw Turbine data and coding packets for reconstructing entries, transactions, and blocks before conventional RPC delivery |
| Preconfirmations | **$1,600/month** | Transaction execution-status signals emitted before shred propagation and standard RPC commitment levels                  |

#### Shreds — $500/month

Solana leaders split block data into network-sized packets called **shreds**.
Data shreds carry block contents, while coding shreds add erasure recovery so a
receiver can reconstruct a block even when some packets are missing. Consuming
the shred stream removes the wait for an RPC node to assemble and expose the
complete block, which is useful for low-latency indexing, market-data pipelines,
and trading infrastructure.

#### What are preconfirmations? — $1,600/month

A **preconfirmation**, or **preconf**, is an early signal emitted when a Solana
leader has executed a transaction and knows its local execution result, but
before that result has been packaged into entries, converted into shreds, and
propagated through Turbine. It can therefore arrive earlier than a shred-derived
stream and earlier than the RPC `processed`, `confirmed`, or `finalized`
commitment levels.

Preconfirmations are valuable when a system needs to react to a likely landing
or failure as soon as the leader knows the outcome. They are not the same as
cluster finality: clients must reconcile the signal with the later shred,
processed, confirmed, or finalized record, and availability depends on leader
coverage. Exact fields, coverage, and delivery semantics are documented during
onboarding.

### Choosing a service

* Start with **basic gRPC** for conventional real-time indexing and monitoring.
* Choose **very-fast gRPC** when latency matters but the ultra-low-latency tier
  is unnecessary.
* Choose **ultra-low-latency gRPC** when approximately 1 ms over the $700 tier
  is material to the strategy.
* Choose **dedicated custom gRPC** for the fastest stack with isolated capacity
  and customer-specific tuning.
* Add **shreds** when you need raw block propagation before RPC assembly.
* Add **preconfirmations** when transaction execution status must arrive before
  shreds and you can correctly reconcile an early, non-final signal.

### Get access

Contact [@inventandchill on Telegram](https://t.me/inventandchill) to discuss
filters, expected throughput, deployment region, authentication, and onboarding.


## Verified Solana examples

These queries are stored under `examples/solana/` and executed against the live
database during every strict docs build. Each scan is constrained by time or by
the newest data window.

### Transfer and funding activity

`token_transfers` and `sol_top_ups` are intentionally separate helper tables.
This query combines their hourly counts without joining individual records. It
is adapted from the workflow introduced in [Two New Solana Tables: token\_transfers and sol\_top\_ups](https://onchaindivers.substack.com/p/two-new-solana-tables-token_transfers).

```sql
WITH
    toStartOfHour(now()) AS end_hour,
    end_hour - INTERVAL 24 HOUR AS start_hour
SELECT
    hour,
    sum(token_transfers) AS token_transfers,
    sum(sol_top_ups) AS sol_top_ups
FROM
(
    SELECT toStartOfHour(block_time) AS hour,
           count() AS token_transfers, 0 AS sol_top_ups
    FROM token_transfers
    PREWHERE block_time >= start_hour AND block_time < end_hour
    GROUP BY hour

    UNION ALL

    SELECT toStartOfHour(block_time) AS hour,
           0 AS token_transfers, count() AS sol_top_ups
    FROM sol_top_ups
    PREWHERE block_time >= start_hour AND block_time < end_hour
    GROUP BY hour
)
GROUP BY hour
ORDER BY hour
```

Source: `examples/solana/transfer_activity.sql`.

### PumpSwap reserve and fee reconciliation

PumpSwap rows expose before/after pool reserves, exact-quote mode, virtual quote
reserves, and protocol, LP, creator, cashback and buyback fee fields. The full
query in `examples/solana/pumpswap_reserve_checks.sql` verifies three identities
for the latest 25 swaps:

* base reserve movement equals `base_token_amount`;
* quote reserve movement equals `quote_token_amount`; and
* `quote_token_amount_without_lp_fee` reconciles with the appropriate fee path
  for buys, sells and exact-quote buys.

It also selects `virtual_quote_reserves`, `coin_creator`,
`coin_creator_fees`, `cash_back_fees`, and `buy_back_fees`, making the accounting
inputs visible beside the checks. This example is based on the field-level
accounting described in [10 New Columns in pumpswap\_all\_swaps](https://onchaindivers.substack.com/p/10-new-columns-in-pumpswap_all_swaps).

Run it directly with the repository verifier:

```sh
python3 scripts/verify_examples.py --strict-live
```

The verifier fails if any returned row has a false reconciliation flag.

### Creator migration rates

The creator example takes the latest timestamp present in
`pumpfun_token_creation`, builds a 30-day window ending there, and joins mints to
`pfamm_migrations`. Anchoring to the dataset rather than the wall clock keeps the
query reproducible for snapshots and retained archives.

The result contains token counts, migrated counts, migration percentage and the
creator's first/last launch time. It is inspired by the cohort construction in
[Pump.fun’s Creator Economy Is a Barbell](https://onchaindivers.substack.com/p/pumpfuns-creator-economy-is-a-barbell).

```sh
# Source query
sed -n '1,220p' examples/solana/creator_migration_rates.sql

# Execute all bounded examples
python3 scripts/verify_examples.py --strict-live
```

Fixed-width mint and creator fields are normalized by removing their null-byte
padding before the join and grouping.


## Connect to the Solana indexer

Connection details are provided with your OnchainDivers credentials. Keep them
in environment variables or a local `.env`; do not embed them in source code.

```dotenv
CLICKHOUSE_HOST=clickhouse.example.com
CLICKHOUSE_PORT=8123
CLICKHOUSE_USERNAME=docs_reader
CLICKHOUSE_PASSWORD=replace-me
```

### Python

```python
import os
import clickhouse_connect

client = clickhouse_connect.get_client(
    host=os.environ["CLICKHOUSE_HOST"],
    port=int(os.environ.get("CLICKHOUSE_PORT", "8123")),
    username=os.environ["CLICKHOUSE_USERNAME"],
    password=os.environ["CLICKHOUSE_PASSWORD"],
)

rows = client.query("SELECT now() AS server_time").named_results()
print(rows)
client.close()
```

Install the client with `pip install clickhouse-connect`.

### Repository client

The examples use `scripts/clickhouse_accessors.py`, which reads the same keys
from `.env`, returns rows as dictionaries, and closes the connection explicitly.

```python
from scripts.clickhouse_accessors import ClickHouseAccessor

client = ClickHouseAccessor(".env")
try:
    rows = client.query("SELECT count() AS rows FROM token_transfers")
    print(rows[0])
finally:
    client.disconnect()
```

Continue with the [verified Solana examples](/solana/examples) or inspect the
[generated table reference](/solana/tables).


## Solana indexer

The Solana ClickHouse database exposes decoded DEX activity and reusable helper
tables. The table reference is generated from the live schema during the build.

### DEX coverage

| Protocol | Published datasets                                                                                                 |
| -------- | ------------------------------------------------------------------------------------------------------------------ |
| Pump.fun | token creation, bonding-curve swaps, v2 swaps, AMM migrations, creator fee distributions and admin creator changes |
| PumpSwap | swaps with reserves, fee components, creator/cashback/buyback fields and virtual quote reserves                    |
| Raydium  | unified swaps, CPMM swaps, Launchpad swaps, token creation and migrations                                          |
| Meteora  | DLMM swaps and dynamic bonding swaps                                                                               |
| Jito     | transaction tips                                                                                                   |

### Helper tables

`token_transfers`, `sol_top_ups`, `solana_blocks`, and `tx_timestamps` make it
possible to analyze transfers, funding paths, block coverage and ingestion
timing without decoding raw transactions again. `max_caps` adds precomputed
token-cap observations.

All monetary values are stored as integer base units unless a column description
says otherwise. Filter on the documented partition/date column before scanning
large swap tables.

* [Connection setup](/solana/getting-started)
* [Reproducible examples](/solana/examples)
* [Reliable Pump.fun creator research](/research/reliable-pumpfun-creators)
* [Unusual Pump.fun activity research](/research/weird-pumpfun-activity)
* [Live table reference](/solana/tables)


## Solana Tables

Tables containing Solana blockchain data including DEX swaps, token trades, and transaction information.

*Last updated: 2026-08-15 23:07 UTC*

import { ClickHouseSqlExample } from '../../components/ClickHouseSqlExample'

### Tables (21)

| Table                                                                          | Rows          | Size       |
| ------------------------------------------------------------------------------ | ------------- | ---------- |
| [jito\_tips](#jito_tips)                                                       | 138,232,839   | 10.42 GB   |
| [max\_caps](#max_caps)                                                         | 9,353,726     | 438.8 MB   |
| [meteora\_dynamic\_bonding\_swaps](#meteora_dynamic_bonding_swaps)             | 151,390,725   | 41.64 GB   |
| [meteora\_swaps](#meteora_swaps)                                               | 75,521,341    | 13.49 GB   |
| [pfamm\_migrations](#pfamm_migrations)                                         | 105,411       | 24.2 MB    |
| [pumpfun\_all\_swaps](#pumpfun_all_swaps)                                      | 2,445,437,524 | 321.80 GB  |
| [pumpfun\_amm\_admin\_set\_coin\_creator](#pumpfun_amm_admin_set_coin_creator) | 750           | 141.2 KB   |
| [pumpfun\_creator\_fee\_distributions](#pumpfun_creator_fee_distributions)     | 1,858,052     | 301.1 MB   |
| [pumpfun\_token\_creation](#pumpfun_token_creation)                            | 16,133,412    | 4.09 GB    |
| [pumpfun\_v2\_swaps](#pumpfun_v2_swaps)                                        | 1,336,702,321 | 183.61 GB  |
| [pumpswap\_all\_swaps](#pumpswap_all_swaps)                                    | 5,063,044,466 | 1653.73 GB |
| [raydium\_all\_swaps](#raydium_all_swaps)                                      | 14,972,070    | 2.10 GB    |
| [raydium\_cpmm\_swaps](#raydium_cpmm_swaps)                                    | 16,040,649    | 3.76 GB    |
| [raydium\_launchpad\_cpmm\_migrations](#raydium_launchpad_cpmm_migrations)     | 3,678         | 4.0 MB     |
| [raydium\_launchpad\_migrations](#raydium_launchpad_migrations)                | 98            | 491.6 KB   |
| [raydium\_launchpad\_swaps](#raydium_launchpad_swaps)                          | 23,025,463    | 3.87 GB    |
| [raydium\_launchpad\_token\_creation](#raydium_launchpad_token_creation)       | 65,568        | 19.9 MB    |
| [sol\_top\_ups](#sol_top_ups)                                                  | 168,835,724   | 27.55 GB   |
| [solana\_blocks](#solana_blocks)                                               | 184,508,670   | 9.45 GB    |
| [token\_transfers](#token_transfers)                                           | 141,849,233   | 21.87 GB   |
| [tx\_timestamps](#tx_timestamps)                                               | 2,881,988,252 | 256.10 GB  |

***

#### jito\_tips

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 138,232,839                          |
| **Size**          | 10.42 GB                             |
| **First Record**  | 2026-08-01                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalDay(14)` |

**Columns:**

| Column           | Type       | Description                                |
| ---------------- | ---------- | ------------------------------------------ |
| `block_time`     | `DateTime` | UTC timestamp when the block was produced  |
| `block_date_utc` | `Date`     | UTC date of the block (for partitioning)   |
| `slot`           | `UInt64`   | Solana slot number (block height)          |
| `tx_idx`         | `UInt32`   | Transaction index within the block         |
| `signature`      | `String`   | Transaction signature (base58 encoded)     |
| `signer`         | `String`   | Wallet address that signed the transaction |
| `sender`         | `String`   | Wallet that sent the tip                   |
| `tip_account`    | `String`   | Jito tip account that received the tip     |
| `amount`         | `UInt64`   | Tip amount in lamports                     |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/2bpta7tMnmdQKd1uDU7r5PuDyvhUja1NKguxBkM8FZhK6uE3EWkuziCL5nJpXGXL5zebH85gF9yX5mFJN5k4SFML)

| Column           | Value                                                |
| ---------------- | ---------------------------------------------------- |
| `block_time`     | `2026-08-15 16:07:06`                                |
| `block_date_utc` | `2026-08-15`                                         |
| `slot`           | `439,525,834`                                        |
| `tx_idx`         | `1,492`                                              |
| `signature`      | `2bpta7tMnmdQKd1uDU7r5PuDyvhUja1NKguxBkM8FZhK6uE...` |
| `signer`         | `eLLnsiBsWvERB34kgiJ4wPhdRzqM17gMG4cHouMqaHz`        |
| `sender`         | `eLLnsiBsWvERB34kgiJ4wPhdRzqM17gMG4cHouMqaHz`        |
| `tip_account`    | `DttWaMuVvTiduZRnguLF7jNxTgiMBZ1hyAumKUiL2KRL`       |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.jito\_tips LIMIT 10
</ClickHouseSqlExample>

***

#### max\_caps

| Statistic | Value     |
| --------- | --------- |
| **Rows**  | 9,353,726 |
| **Size**  | 438.8 MB  |

**Columns:**

| Column          | Type                                    | Description                              |
| --------------- | --------------------------------------- | ---------------------------------------- |
| `token_mint`    | `String`                                | Token mint address                       |
| `max_mcap_sol`  | `SimpleAggregateFunction(max, Float32)` | Maximum market cap reached (in SOL)      |
| `max_mcap_usdc` | `SimpleAggregateFunction(max, Float32)` | Maximum market cap reached (in USDC)     |
| `max_slot`      | `SimpleAggregateFunction(max, UInt32)`  | Slot when maximum market cap was reached |

**Latest Record Sample:**

| Column          | Value                                         |
| --------------- | --------------------------------------------- |
| `token_mint`    | `112BvbiE2nbCcsPCHutzCBJmtSdhY4EXYGKJQyPpump` |
| `max_mcap_sol`  | `134.592`                                     |
| `max_mcap_usdc` | `30780.7`                                     |
| `max_slot`      | `0`                                           |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.max\_caps LIMIT 10
</ClickHouseSqlExample>

***

#### meteora\_dynamic\_bonding\_swaps

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 151,390,725                          |
| **Size**          | 41.64 GB                             |
| **First Record**  | 2025-08-18                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalYear(1)` |

**Columns:**

| Column                     | Type                     | Description                                                    |
| -------------------------- | ------------------------ | -------------------------------------------------------------- |
| `block_time`               | `DateTime`               | UTC timestamp when the block was produced                      |
| `block_date_utc`           | `Date`                   | UTC date of the block (for partitioning)                       |
| `slot`                     | `UInt32`                 | Solana slot number (block height)                              |
| `tx_idx`                   | `UInt16`                 | Transaction index within the block                             |
| `swap_idx`                 | `UInt16`                 | Swap index within the transaction                              |
| `signature`                | `String`                 | Transaction signature (base58 encoded)                         |
| `fee_payer`                | `String`                 | Wallet that paid the transaction fee                           |
| `provided_gas_fee`         | `UInt64`                 | Gas fee provided for the transaction (lamports)                |
| `provided_gas_limit`       | `UInt64`                 | Compute unit limit requested                                   |
| `fee`                      | `UInt64`                 | Transaction fee paid (lamports)                                |
| `consumed_gas`             | `UInt64`                 | Compute units actually consumed                                |
| `pool_id`                  | `String`                 | Pool account address                                           |
| `signer`                   | `String`                 | Wallet address that signed the transaction                     |
| `direction`                | `LowCardinality(String)` | Trade direction (buy/sell)                                     |
| `base_coin`                | `String`                 | Base token mint address                                        |
| `quote_coin`               | `String`                 | Quote token mint address (usually SOL or USDC)                 |
| `base_coin_amount`         | `UInt64`                 | Base token amount (raw units, needs decimal adjustment)        |
| `quote_coin_amount`        | `UInt64`                 | Quote token amount (raw units)                                 |
| `base_coin_slippage`       | `Int64`                  | Slippage on base token (negative = less than expected)         |
| `quote_coin_slippage`      | `Int64`                  | Slippage on quote token (negative = less than expected)        |
| `parent_program`           | `String`                 | Parent program that invoked this instruction (for CPI calls)   |
| `top_level_transfers_json` | `String`                 | JSON array of top-level SOL/token transfers in the transaction |
| `orig_base_coin_amount`    | `UInt64`                 | Original base amount before slippage                           |
| `orig_quote_coin_amount`   | `UInt64`                 | Original quote amount before slippage                          |
| `config`                   | `String`                 | Pool configuration account                                     |
| `trade_direction`          | `UInt64`                 | Numeric trade direction indicator                              |
| `has_referral`             | `UInt8`                  | Whether trade included a referral (1=yes, 0=no)                |
| `swap_amount0`             | `String`                 | First swap amount in the route                                 |
| `swap_amount1`             | `String`                 | Second swap amount in the route                                |
| `swap_mode`                | `UInt64`                 | Swap mode (exact in/out)                                       |
| `input_amount`             | `String`                 | Input amount for the swap                                      |
| `output_amount`            | `String`                 | Output amount from the swap                                    |
| `base_mint`                | `String`                 | Base token mint address                                        |
| `quote_mint`               | `String`                 | Quote token mint address                                       |
| `referral`                 | `String`                 | Referral account address (if any)                              |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/5PW4WGow35mQE3sv9y7KDLGdvBe9rKraPLnvWMMrxeJSCaXuEXJo14FjjGBnBvTiE16S5DoAnvB1NXJis9fiPbix)

| Column             | Value                                                |
| ------------------ | ---------------------------------------------------- |
| `block_time`       | `2026-08-15 16:07:06`                                |
| `block_date_utc`   | `2026-08-15`                                         |
| `slot`             | `439,525,834`                                        |
| `tx_idx`           | `1,431`                                              |
| `swap_idx`         | `7`                                                  |
| `signature`        | `5PW4WGow35mQE3sv9y7KDLGdvBe9rKraPLnvWMMrxeJSCaX...` |
| `fee_payer`        | `5y17d9WiwrRaA3DgHVFrVNyDNzym8wJb7gymhvsMMq9c`       |
| `provided_gas_fee` | `10,100`                                             |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.meteora\_dynamic\_bonding\_swaps LIMIT 10
</ClickHouseSqlExample>

***

#### meteora\_swaps

| Statistic         | Value                            |
| ----------------- | -------------------------------- |
| **Rows**          | 75,521,341                       |
| **Size**          | 13.49 GB                         |
| **First Record**  | 2026-05-17                       |
| **Last Record**   | 2026-08-15                       |
| **Partition Key** | `block_date`                     |
| **TTL**           | `block_date + toIntervalDay(90)` |

**Columns:**

| Column               | Type       | Description                                                                                 |
| -------------------- | ---------- | ------------------------------------------------------------------------------------------- |
| `block_time`         | `DateTime` | UTC timestamp when the block was produced                                                   |
| `block_date`         | `Date`     | UTC date of the block (for partitioning)                                                    |
| `slot`               | `UInt64`   | Solana slot number (block height)                                                           |
| `tx_idx`             | `UInt32`   | Transaction index within the block                                                          |
| `signing_wallet`     | `String`   | Wallet address that signed the transaction                                                  |
| `base_coin`          | `String`   | Base token mint address                                                                     |
| `quote_coin`         | `String`   | Quote token mint address (usually SOL or USDC)                                              |
| `base_coin_amount`   | `UInt64`   | Base token amount (raw units, needs decimal adjustment)                                     |
| `quote_coin_amount`  | `UInt64`   | Quote token amount (raw units)                                                              |
| `start_bin_id`       | `Int32`    | Starting bin ID in the DLMM pool                                                            |
| `end_bin_id`         | `Int32`    | Ending bin ID after the swap                                                                |
| `fee`                | `UInt64`   | Transaction fee paid (lamports)                                                             |
| `protocol_fee`       | `UInt64`   | Protocol fee amount                                                                         |
| `fee_bps_low`        | `UInt64`   | Lower bound of fee in basis points                                                          |
| `fee_bps_high`       | `UInt64`   | Upper bound of fee in basis points                                                          |
| `host_fee`           | `UInt64`   | Host/frontend fee amount                                                                    |
| `signature`          | `String`   | Transaction signature (base58 encoded)                                                      |
| `provided_gas_fee`   | `UInt64`   | Gas fee provided for the transaction (lamports)                                             |
| `provided_gas_limit` | `UInt64`   | Compute unit limit requested                                                                |
| `fee_paid`           | `UInt64`   | Total fee paid                                                                              |
| `consumed_gas`       | `UInt64`   | Compute units actually consumed                                                             |
| `lb_pair`            | `String`   | Liquidity bin pair (pool) address                                                           |
| `from_wallet`        | `String`   | Source wallet for the swap                                                                  |
| `swap_for_y`         | `UInt8`    | Whether swapping for Y token (1=yes, 0=no)                                                  |
| `parent_program`     | `String`   | Parent program that invoked this instruction (for CPI calls) *(populated since 2026-05-17)* |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/2bpta7tMnmdQKd1uDU7r5PuDyvhUja1NKguxBkM8FZhK6uE3EWkuziCL5nJpXGXL5zebH85gF9yX5mFJN5k4SFML)

| Column             | Value                                          |
| ------------------ | ---------------------------------------------- |
| `block_time`       | `2026-08-15 16:07:06`                          |
| `block_date`       | `2026-08-15`                                   |
| `slot`             | `439,525,834`                                  |
| `tx_idx`           | `1,492`                                        |
| `signing_wallet`   | `eLLnsiBsWvERB34kgiJ4wPhdRzqM17gMG4cHouMqaHz`  |
| `base_coin`        | `7vfCXTUXx5WJV5JADk17DUJ4ksgau7utNKj4b963voxs` |
| `quote_coin`       | `So11111111111111111111111111111111111111112`  |
| `base_coin_amount` | `289,697`                                      |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.meteora\_swaps LIMIT 10
</ClickHouseSqlExample>

***

#### pfamm\_migrations

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 105,411                              |
| **Size**          | 24.2 MB                              |
| **First Record**  | 2025-08-15                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalYear(1)` |

**Columns:**

| Column               | Type       | Description                                                                                 |
| -------------------- | ---------- | ------------------------------------------------------------------------------------------- |
| `block_time`         | `DateTime` | UTC timestamp when the block was produced                                                   |
| `block_date_utc`     | `Date`     | UTC date of the block (for partitioning)                                                    |
| `slot`               | `UInt32`   | Solana slot number (block height)                                                           |
| `tx_idx`             | `UInt16`   | Transaction index within the block                                                          |
| `user`               | `String`   | User wallet address                                                                         |
| `mint`               | `String`   | Token mint address                                                                          |
| `mint_amount`        | `UInt64`   | Token amount migrated                                                                       |
| `sol_amount`         | `UInt64`   | SOL amount in the migration                                                                 |
| `pool_migration_fee` | `UInt64`   | Fee paid for pool migration                                                                 |
| `bonding_curve`      | `String`   | Bonding curve account address                                                               |
| `timestamp`          | `UInt32`   | Unix timestamp of migration                                                                 |
| `pool`               | `String`   | Pool account address                                                                        |
| `signature`          | `String`   | Transaction signature (base58 encoded) *(populated since 2025-11-10)*                       |
| `parent_program`     | `String`   | Parent program that invoked this instruction (for CPI calls) *(populated since 2026-01-22)* |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/Pwh3PpFbiD5XTUwVVhUbySfH9X2jV4GwNxQGifkT6pQDP2x1Haydkp9hS9eVQu9iuRvx7swKYMMTLyLYEjbFcpb)

| Column           | Value                                          |
| ---------------- | ---------------------------------------------- |
| `block_time`     | `2026-08-15 16:06:25`                          |
| `block_date_utc` | `2026-08-15`                                   |
| `slot`           | `439,525,737`                                  |
| `tx_idx`         | `1,002`                                        |
| `user`           | `9C4nRvhhVquCKATjDCx5FKvNS9PNgNqgyWy9AcoDjYv5` |
| `mint`           | `BHgec9vNuawtuwQPdPpfzM3Hh5V6zxCQCZpvjHrNpump` |
| `mint_amount`    | `206,900,000,000,000`                          |
| `sol_amount`     | `84,990,360,684`                               |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.pfamm\_migrations LIMIT 10
</ClickHouseSqlExample>

***

#### pumpfun\_all\_swaps

| Statistic        | Value         |
| ---------------- | ------------- |
| **Rows**         | 2,445,437,524 |
| **Size**         | 321.80 GB     |
| **First Record** | 2024-06-11    |
| **Last Record**  | 2026-08-15    |

**Columns:**

| Column                        | Type               | Description                                                                                 |
| ----------------------------- | ------------------ | ------------------------------------------------------------------------------------------- |
| `block_time`                  | `DateTime`         | UTC timestamp when the block was produced                                                   |
| `slot`                        | `UInt32`           | Solana slot number (block height)                                                           |
| `tx_idx`                      | `UInt16`           | Transaction index within the block                                                          |
| `signing_wallet`              | `FixedString(48)`  | Wallet address that signed the transaction                                                  |
| `direction`                   | `String`           | Trade direction (buy/sell)                                                                  |
| `base_coin`                   | `FixedString(48)`  | Base token mint address                                                                     |
| `base_coin_amount`            | `UInt64`           | Base token amount (raw units, needs decimal adjustment)                                     |
| `quote_coin_amount`           | `UInt64`           | Quote token amount (raw units)                                                              |
| `virtual_token_balance_after` | `UInt64`           | Virtual token reserves after trade (bonding curve state)                                    |
| `virtual_sol_balance_after`   | `UInt64`           | Virtual SOL reserves after trade (bonding curve state)                                      |
| `signature`                   | `FixedString(128)` | Transaction signature (base58 encoded)                                                      |
| `provided_gas_fee`            | `UInt64`           | Gas fee provided for the transaction (lamports)                                             |
| `provided_gas_limit`          | `UInt64`           | Compute unit limit requested                                                                |
| `fee`                         | `UInt64`           | Transaction fee paid (lamports)                                                             |
| `consumed_gas`                | `UInt64`           | Compute units actually consumed                                                             |
| `top_level_transfers_json`    | `String`           | JSON array of top-level SOL/token transfers in the transaction                              |
| `is_exact_quote`              | `UInt8`            | Whether quote amount was exact (1=yes, 0=no) *(populated since 2025-12-03)*                 |
| `parent_program`              | `String`           | Parent program that invoked this instruction (for CPI calls) *(populated since 2024-11-02)* |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/b'2eyqdWgrEVPHbBKMcg3PU5SRreH3x8j19dHaM37j6nMjQHgAi9an52U6hud5Y4cMBhKQcVdXjUdJbyWdwifZKYbm\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00')

| Column              | Value                                                |
| ------------------- | ---------------------------------------------------- |
| `block_time`        | `2026-08-15 16:07:06`                                |
| `slot`              | `439,525,834`                                        |
| `tx_idx`            | `1,444`                                              |
| `signing_wallet`    | `b'7iyy4LUYnbMGyTrzQgngtmdtbd2mFZKWtrF4DYEVKpSa\x00` |
| `direction`         | `sell`                                               |
| `base_coin`         | `b'ALfPZafETgry4XEt5aVYfawRecVameyYwvjRQukZpump\x00` |
| `base_coin_amount`  | `104,664,495,307,313`                                |
| `quote_coin_amount` | `14,489,324`                                         |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.pumpfun\_all\_swaps LIMIT 10
</ClickHouseSqlExample>

***

#### pumpfun\_amm\_admin\_set\_coin\_creator

| Statistic         | Value                  |
| ----------------- | ---------------------- |
| **Rows**          | 750                    |
| **Size**          | 141.2 KB               |
| **First Record**  | 2026-03-15             |
| **Last Record**   | 2026-08-15             |
| **Partition Key** | `toYYYYMM(block_time)` |

**Columns:**

| Column                     | Type       | Description                                                    |
| -------------------------- | ---------- | -------------------------------------------------------------- |
| `block_time`               | `DateTime` | UTC timestamp when the block was produced                      |
| `block_date_utc`           | `Date`     | UTC date of the block (for partitioning)                       |
| `slot`                     | `UInt64`   | Solana slot number (block height)                              |
| `tx_idx`                   | `UInt32`   | Transaction index within the block                             |
| `swap_idx`                 | `Int32`    | Swap index within the transaction                              |
| `signature`                | `String`   | Transaction signature (base58 encoded)                         |
| `fee_payer`                | `String`   | Wallet that paid the transaction fee                           |
| `provided_gas_fee`         | `UInt64`   | Gas fee provided for the transaction (lamports)                |
| `provided_gas_limit`       | `UInt64`   | Compute unit limit requested                                   |
| `fee`                      | `UInt64`   | Transaction fee paid (lamports)                                |
| `consumed_gas`             | `UInt64`   | Compute units actually consumed                                |
| `pool`                     | `String`   | Pool account being modified                                    |
| `parent_program`           | `String`   | Parent program that invoked this instruction (for CPI calls)   |
| `top_level_transfers_json` | `String`   | JSON array of top-level SOL/token transfers in the transaction |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/5StYNBLH6pPyBXCZQMrhWbfK1oMazQjuJM3vBVoZqBJeVEUAz1XbP4QofEJqHBwxqsxYv14CyunGYYAjRghFu3qb)

| Column             | Value                                                |
| ------------------ | ---------------------------------------------------- |
| `block_time`       | `2026-08-15 06:41:55`                                |
| `block_date_utc`   | `2026-08-15`                                         |
| `slot`             | `439,444,359`                                        |
| `tx_idx`           | `761`                                                |
| `swap_idx`         | `17`                                                 |
| `signature`        | `5StYNBLH6pPyBXCZQMrhWbfK1oMazQjuJM3vBVoZqBJeVEU...` |
| `fee_payer`        | `2PCy7goCtwLB7sRXMfeqCzTv3HJhd1fr2XNK8Rbdu4pf`       |
| `provided_gas_fee` | `528,002`                                            |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.pumpfun\_amm\_admin\_set\_coin\_creator LIMIT 10
</ClickHouseSqlExample>

***

#### pumpfun\_creator\_fee\_distributions

| Statistic         | Value                    |
| ----------------- | ------------------------ |
| **Rows**          | 1,858,052                |
| **Size**          | 301.1 MB                 |
| **First Record**  | 2026-02-15               |
| **Last Record**   | 2026-08-15               |
| **Partition Key** | `toYYYYMMDD(block_time)` |

**Columns:**

| Column                    | Type       | Description                                                                                 |
| ------------------------- | ---------- | ------------------------------------------------------------------------------------------- |
| `block_time`              | `DateTime` | UTC timestamp when the block was produced                                                   |
| `block_date_utc`          | `Date`     | UTC date of the block (for partitioning)                                                    |
| `slot`                    | `UInt64`   | Solana slot number (block height)                                                           |
| `tx_idx`                  | `UInt32`   | Transaction index within the block                                                          |
| `ix_idx`                  | `UInt32`   | Instruction index within the transaction                                                    |
| `signature`               | `String`   | Transaction signature (base58 encoded)                                                      |
| `fee_payer`               | `String`   | Wallet that paid the transaction fee                                                        |
| `provided_gas_fee`        | `UInt64`   | Gas fee provided for the transaction (lamports)                                             |
| `provided_gas_limit`      | `UInt64`   | Compute unit limit requested                                                                |
| `fee`                     | `UInt64`   | Transaction fee paid (lamports)                                                             |
| `consumed_gas`            | `UInt64`   | Compute units actually consumed                                                             |
| `mint`                    | `String`   | Token mint address                                                                          |
| `receiver`                | `String`   | Creator wallet receiving the fee distribution                                               |
| `amount`                  | `UInt64`   | Fee amount distributed (lamports)                                                           |
| `fee_distribution_method` | `String`   | Method used for fee distribution *(populated since 2026-02-15)*                             |
| `parent_program`          | `String`   | Parent program that invoked this instruction (for CPI calls) *(populated since 2026-05-29)* |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/3MdH19Pzm99AveLrb12Pdwb8UPgM6XsN9AR25tzCuyz6EFAJUrr5hhaKTopmqkVGerdiKG9Bbs2kEFSayBSGPSmC)

| Column             | Value                                                |
| ------------------ | ---------------------------------------------------- |
| `block_time`       | `2026-08-15 16:06:44`                                |
| `block_date_utc`   | `2026-08-15`                                         |
| `slot`             | `439,525,782`                                        |
| `tx_idx`           | `1,548`                                              |
| `ix_idx`           | `0`                                                  |
| `signature`        | `3MdH19Pzm99AveLrb12Pdwb8UPgM6XsN9AR25tzCuyz6EFA...` |
| `fee_payer`        | `Dde9n8s2NPiL9Wd99NftqDtbwRrsPkyxxKDAJeRhCXoJ`       |
| `provided_gas_fee` | `0`                                                  |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.pumpfun\_creator\_fee\_distributions LIMIT 10
</ClickHouseSqlExample>

***

#### pumpfun\_token\_creation

| Statistic        | Value      |
| ---------------- | ---------- |
| **Rows**         | 16,133,412 |
| **Size**         | 4.09 GB    |
| **First Record** | 2024-01-17 |
| **Last Record**  | 2026-08-15 |

**Columns:**

| Column                    | Type               | Description                                                                                 |
| ------------------------- | ------------------ | ------------------------------------------------------------------------------------------- |
| `block_time`              | `DateTime`         | UTC timestamp when the block was produced                                                   |
| `slot`                    | `UInt32`           | Solana slot number (block height)                                                           |
| `tx_idx`                  | `UInt16`           | Transaction index within the block                                                          |
| `creator`                 | `FixedString(48)`  | Wallet that created the token                                                               |
| `name`                    | `FixedString(20)`  | Token name                                                                                  |
| `symbol`                  | `FixedString(16)`  | Token ticker symbol                                                                         |
| `url`                     | `FixedString(256)` | Metadata URI (usually IPFS)                                                                 |
| `mint`                    | `FixedString(48)`  | Token mint address                                                                          |
| `bundle_size`             | `UInt8`            | Number of transactions in the bundle                                                        |
| `gas_used`                | `UInt64`           | Total gas used for token creation                                                           |
| `amount_of_instructions`  | `Int32`            | Number of instructions in the transaction                                                   |
| `amount_of_lookup_reads`  | `Int32`            | Number of address lookup table reads                                                        |
| `amount_of_lookup_writes` | `Int32`            | Number of address lookup table writes                                                       |
| `bundle_structure`        | `String`           | Structure of bundled transactions                                                           |
| `bundled_buys`            | `UInt64`           | Total SOL amount in bundled buys                                                            |
| `bundled_buys_count`      | `UInt64`           | Number of bundled buy transactions                                                          |
| `dev_balance`             | `UInt64`           | Developer's token balance after creation                                                    |
| `creation_ix_index`       | `Int32`            | Index of creation instruction                                                               |
| `curve_address`           | `FixedString(48)`  | Bonding curve account address                                                               |
| `pf_program_index`        | `UInt8`            | Pump.fun program index in transaction                                                       |
| `direct_pf_invocation`    | `UInt8`            | Whether pump.fun was called directly (1=yes, 0=no)                                          |
| `version`                 | `DateTime64(3)`    | Record version timestamp *(populated since 2024-01-17)*                                     |
| `mayhem_mode`             | `UInt8`            | Whether mayhem mode was enabled *(populated since 2025-11-12)*                              |
| `token_program`           | `String`           | Token program used (SPL Token or Token-2022) *(populated since 2024-01-17)*                 |
| `signature`               | `String`           | Transaction signature (base58 encoded) *(populated since 2024-01-17)*                       |
| `parent_program`          | `String`           | Parent program that invoked this instruction (for CPI calls) *(populated since 2026-01-22)* |
| `is_cashback_enabled`     | `UInt8`            | Whether cashback was enabled for creation *(populated since 2026-02-18)*                    |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/2EiHVeJnQH8MBJ4a31AcAY1k1sEoitGjACqGnKWPqUjssn3myayuNnRZTvJEYFmCwF3Zc6ngRghmCJyCFJWDmszT)

| Column       | Value                                                |
| ------------ | ---------------------------------------------------- |
| `block_time` | `2026-08-15 16:07:05`                                |
| `slot`       | `439,525,833`                                        |
| `tx_idx`     | `575`                                                |
| `creator`    | `b'Hv6zhscegkD2kL66jgMZy8dmVFUCfDkzswoHA7UkxZPB\x00` |
| `name`       | `b'100k or RUG?\x00\x00\x00\x00\x00\x00\x00\x00'`    |
| `symbol`     | `b'IOOK/RUG\x00\x00\x00\x00\x00\x00\x00\x00'`        |
| `url`        | `b'https://ipfs.io/ipfs/QmfGUeeFgkwJRam5PqSNMTWHvMg` |
| `mint`       | `b'8Fp71jXaCNWTyWJuSdJKGRNrGNXBh3mfPRvZau36pump\x00` |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.pumpfun\_token\_creation LIMIT 10
</ClickHouseSqlExample>

***

#### pumpfun\_v2\_swaps

| Statistic         | Value                      |
| ----------------- | -------------------------- |
| **Rows**          | 1,336,702,321              |
| **Size**          | 183.61 GB                  |
| **First Record**  | 2024-11-02                 |
| **Last Record**   | 2026-08-15                 |
| **Partition Key** | `(block_date_utc, failed)` |

**Columns:**

| Column                        | Type                     | Description                                                                                                                           |
| ----------------------------- | ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------- |
| `block_time`                  | `DateTime`               | UTC timestamp when the block was produced                                                                                             |
| `block_date_utc`              | `Date`                   | UTC date of the block (for partitioning)                                                                                              |
| `slot`                        | `UInt32`                 | Solana slot number (block height)                                                                                                     |
| `tx_idx`                      | `UInt16`                 | Transaction index within the block                                                                                                    |
| `ix_idx`                      | `Int16`                  | Instruction index within the transaction *(populated since 2024-11-02)*                                                               |
| `signing_wallet`              | `String`                 | Wallet address that signed the transaction                                                                                            |
| `fee_payer`                   | `String`                 | Wallet that paid the transaction fee *(populated since 2024-11-02)*                                                                   |
| `direction`                   | `LowCardinality(String)` | Trade direction (buy/sell)                                                                                                            |
| `base_coin`                   | `String`                 | Base token mint address                                                                                                               |
| `quote_coin`                  | `LowCardinality(String)` | Quote token mint address (usually SOL or USDC)                                                                                        |
| `instruction_type`            | `LowCardinality(String)` | Pump.fun instruction variant: `buy`, `sell`, `buy_v2`, `sell_v2`, `buy_exact_sol_in`, or `buy_exact_quote_in_v2`                      |
| `base_coin_amount`            | `UInt64`                 | Base token amount (raw units, needs decimal adjustment)                                                                               |
| `quote_coin_amount`           | `UInt64`                 | Quote token amount (raw units)                                                                                                        |
| `virtual_token_balance_after` | `UInt64`                 | Virtual token reserves after trade (bonding curve state)                                                                              |
| `virtual_sol_balance_after`   | `UInt64`                 | Virtual SOL reserves after trade (bonding curve state)                                                                                |
| `signature`                   | `String`                 | Transaction signature (base58 encoded)                                                                                                |
| `provided_gas_fee`            | `UInt64`                 | Gas fee provided for the transaction (lamports)                                                                                       |
| `provided_gas_limit`          | `UInt64`                 | Compute unit limit requested                                                                                                          |
| `fee`                         | `UInt64`                 | Transaction fee paid (lamports)                                                                                                       |
| `consumed_gas`                | `UInt64`                 | Compute units actually consumed                                                                                                       |
| `top_level_transfers_json`    | `String`                 | JSON array of top-level SOL/token transfers in the transaction                                                                        |
| `lookup_tables`               | `String`                 | Pipe-delimited account addresses loaded through address lookup tables (writable first, then read-only) *(populated since 2024-11-02)* |
| `parent_program`              | `String`                 | Parent program that invoked this instruction (for CPI calls) *(populated since 2024-11-02)*                                           |
| `failed`                      | `UInt8`                  | Whether the transaction failed (1=yes, 0=no) *(populated since 2024-11-02)*                                                           |
| `pf_program_account_index`    | `Int16`                  | Default: `-1` *(populated since 2024-11-02)*                                                                                          |
| `cu_price_ix_index`           | `Int16`                  | Instruction index of the compute-unit price instruction *(populated since 2024-11-02)*                                                |
| `cu_limit_ix_index`           | `Int16`                  | Instruction index of the compute-unit limit instruction *(populated since 2024-11-02)*                                                |
| `tip_index`                   | `Int16`                  | Default: `-1` *(populated since 2024-11-02)*                                                                                          |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/4hxZsBB1hjJxkYGCJvzfcFDcnQCprNsmnrFgVVCN2xwgRW2qHa1vkkYs7AoYzmDbcAsSiH6wdZmeds7fRYsaeaoU)

| Column           | Value                                          |
| ---------------- | ---------------------------------------------- |
| `block_time`     | `2026-08-15 16:07:06`                          |
| `block_date_utc` | `2026-08-15`                                   |
| `slot`           | `439,525,834`                                  |
| `tx_idx`         | `1,531`                                        |
| `ix_idx`         | `4`                                            |
| `signing_wallet` | `H8UKNvUeaqpVT7DZ8TXJVreSQEXDjTA7fEv8hjD3FzHm` |
| `fee_payer`      | `H8UKNvUeaqpVT7DZ8TXJVreSQEXDjTA7fEv8hjD3FzHm` |
| `direction`      | `buy`                                          |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.pumpfun\_v2\_swaps LIMIT 10
</ClickHouseSqlExample>

***

#### pumpswap\_all\_swaps

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 5,063,044,466                        |
| **Size**          | 1653.73 GB                           |
| **First Record**  | 2025-08-15                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalYear(1)` |

**Columns:**

| Column                              | Type                     | Description                                                                                   |
| ----------------------------------- | ------------------------ | --------------------------------------------------------------------------------------------- |
| `block_time`                        | `DateTime`               | UTC timestamp when the block was produced                                                     |
| `block_date_utc`                    | `Date`                   | UTC date of the block (for partitioning)                                                      |
| `slot`                              | `UInt32`                 | Solana slot number (block height)                                                             |
| `tx_idx`                            | `UInt16`                 | Transaction index within the block                                                            |
| `signing_wallet`                    | `String`                 | Wallet address that signed the transaction                                                    |
| `fee_payer`                         | `String`                 | Wallet that paid the transaction fee                                                          |
| `direction`                         | `LowCardinality(String)` | Trade direction (buy/sell)                                                                    |
| `base_token`                        | `String`                 | Base token mint address                                                                       |
| `quote_token`                       | `String`                 | Quote token mint address                                                                      |
| `base_token_amount`                 | `UInt64`                 | Base token amount (raw units)                                                                 |
| `quote_token_amount`                | `UInt64`                 | Quote token amount (raw units)                                                                |
| `quote_token_amount_limit`          | `UInt64`                 | Maximum quote token amount limit                                                              |
| `quote_token_amount_without_lp_fee` | `UInt64`                 | Quote amount before LP fee deduction                                                          |
| `user_base_token_account`           | `String`                 | User's base token account                                                                     |
| `user_quote_token_account`          | `String`                 | User's quote token account                                                                    |
| `user_base_token_reserves`          | `UInt64`                 | User's base token balance                                                                     |
| `user_quote_token_reserves`         | `UInt64`                 | User's quote token balance                                                                    |
| `pool_base_token_reserves_before`   | `UInt64`                 | Pool base token reserves before swap                                                          |
| `pool_quote_token_reserves_before`  | `UInt64`                 | Pool quote token reserves before swap                                                         |
| `pool_base_token_reserves_after`    | `UInt64`                 | Pool base token reserves after swap                                                           |
| `pool_quote_token_reserves_after`   | `UInt64`                 | Pool quote token reserves after swap                                                          |
| `lp_fee_basis_points`               | `UInt16`                 | LP fee in basis points                                                                        |
| `lp_fee`                            | `UInt64`                 | LP fee amount                                                                                 |
| `protocol_fee_basis_points`         | `UInt16`                 | Protocol fee in basis points                                                                  |
| `protocol_fee`                      | `UInt64`                 | Protocol fee amount                                                                           |
| `signature`                         | `String`                 | Transaction signature (base58 encoded)                                                        |
| `provided_gas_fee`                  | `UInt64`                 | Gas fee provided for the transaction (lamports)                                               |
| `provided_gas_limit`                | `UInt64`                 | Compute unit limit requested                                                                  |
| `fee`                               | `UInt64`                 | Transaction fee paid (lamports)                                                               |
| `consumed_gas`                      | `UInt64`                 | Compute units actually consumed                                                               |
| `pool`                              | `String`                 | Pool account address *(populated since 2025-08-15)*                                           |
| `is_exact_quote`                    | `UInt8`                  | Whether quote amount was exact (1=yes, 0=no) *(populated since 2025-12-03)*                   |
| `parent_program`                    | `String`                 | Parent program that invoked this instruction (for CPI calls) *(populated since 2025-12-13)*   |
| `top_level_transfers_json`          | `String`                 | JSON array of top-level SOL/token transfers in the transaction *(populated since 2026-02-18)* |
| `coin_creator`                      | `String`                 | Default: `''` *(populated since 2026-07-23)*                                                  |
| `coin_creator_fees_basis_points`    | `UInt64`                 | Default: `0` *(populated since 2026-07-23)*                                                   |
| `coin_creator_fees`                 | `UInt64`                 | Default: `0` *(populated since 2026-07-23)*                                                   |
| `cash_back_fees_basis_points`       | `UInt64`                 | Default: `0` *(populated since 2026-07-23)*                                                   |
| `cash_back_fees`                    | `UInt64`                 | Default: `0` *(populated since 2026-07-23)*                                                   |
| `buy_back_fees_basis_points`        | `UInt64`                 | Default: `0` *(populated since 2026-07-23)*                                                   |
| `buy_back_fees`                     | `UInt64`                 | Default: `0` *(populated since 2026-07-23)*                                                   |
| `virtual_quote_reserves`            | `Int128`                 | Default: `0` *(populated since 2026-07-23)*                                                   |
| `can_boost`                         | `UInt8`                  | Default: `0` *(populated since 2026-07-23)*                                                   |
| `base_supply`                       | `UInt64`                 | Default: `0` *(populated since 2026-07-23)*                                                   |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/5R8QsBUj8fR1Bk4Xg7ShfL77QZjMA5eGV8a1RYDXpJhnsjFiBduouPmMJGKWqiWRHxGvaybV9xGvJiY4kymjuNUz)

| Column           | Value                                          |
| ---------------- | ---------------------------------------------- |
| `block_time`     | `2026-08-15 16:07:06`                          |
| `block_date_utc` | `2026-08-15`                                   |
| `slot`           | `439,525,834`                                  |
| `tx_idx`         | `1,509`                                        |
| `signing_wallet` | `6fWKt8JLBYHsQoBchD1nXNyy3tLzCgva3Exxq8K5hXgj` |
| `fee_payer`      | `6fWKt8JLBYHsQoBchD1nXNyy3tLzCgva3Exxq8K5hXgj` |
| `direction`      | `S`                                            |
| `base_token`     | `So11111111111111111111111111111111111111112`  |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.pumpswap\_all\_swaps LIMIT 10
</ClickHouseSqlExample>

***

#### raydium\_all\_swaps

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 14,972,070                           |
| **Size**          | 2.10 GB                              |
| **First Record**  | 2026-05-17                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalDay(90)` |

**Columns:**

| Column                      | Type               | Description                                                                                 |
| --------------------------- | ------------------ | ------------------------------------------------------------------------------------------- |
| `block_time`                | `DateTime`         | UTC timestamp when the block was produced                                                   |
| `block_date_utc`            | `Date`             | UTC date of the block (for partitioning)                                                    |
| `slot`                      | `UInt32`           | Solana slot number (block height)                                                           |
| `tx_idx`                    | `UInt16`           | Transaction index within the block                                                          |
| `signing_wallet`            | `FixedString(48)`  | Wallet address that signed the transaction                                                  |
| `direction`                 | `String`           | Trade direction (buy/sell)                                                                  |
| `base_coin`                 | `FixedString(48)`  | Base token mint address                                                                     |
| `quote_coin`                | `FixedString(48)`  | Quote token mint address (usually SOL or USDC)                                              |
| `base_coin_amount`          | `UInt64`           | Base token amount (raw units, needs decimal adjustment)                                     |
| `quote_coin_amount`         | `UInt64`           | Quote token amount (raw units)                                                              |
| `base_pool_balance_before`  | `UInt64`           | Pool base token balance before swap                                                         |
| `quote_pool_balance_before` | `UInt64`           | Pool quote token balance before swap                                                        |
| `base_pool_balance_after`   | `UInt64`           | Pool base token balance after swap                                                          |
| `quote_pool_balance_after`  | `UInt64`           | Pool quote token balance after swap                                                         |
| `signature`                 | `FixedString(128)` | Transaction signature (base58 encoded)                                                      |
| `serum_market_id`           | `FixedString(48)`  | OpenBook/Serum market ID (for hybrid pools)                                                 |
| `raydium_market_id`         | `FixedString(48)`  | Raydium AMM market ID                                                                       |
| `provided_gas_fee`          | `UInt64`           | Gas fee provided for the transaction (lamports)                                             |
| `provided_gas_limit`        | `UInt64`           | Compute unit limit requested                                                                |
| `fee`                       | `UInt64`           | Transaction fee paid (lamports)                                                             |
| `consumed_gas`              | `UInt64`           | Compute units actually consumed                                                             |
| `parent_program`            | `String`           | Parent program that invoked this instruction (for CPI calls) *(populated since 2026-05-17)* |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/b'3ivKVDVSaYE8ieHmVXoZoM4d6WoG6utJ9rNoeU8C1HXEUxp5X9i3baXPpn21GwjF7iLVxu99TuoumCHumDECrgr2\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00')

| Column           | Value                                                |
| ---------------- | ---------------------------------------------------- |
| `block_time`     | `2026-08-15 16:07:05`                                |
| `block_date_utc` | `2026-08-15`                                         |
| `slot`           | `439,525,833`                                        |
| `tx_idx`         | `1,377`                                              |
| `signing_wallet` | `b'FtvJqfhDTVqh1jsvu6DYWpkELV4A3nHv1cYZMQw9Z8Le\x00` |
| `direction`      | `B`                                                  |
| `base_coin`      | `b'So11111111111111111111111111111111111111112\x00\` |
| `quote_coin`     | `b'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v\x00` |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.raydium\_all\_swaps LIMIT 10
</ClickHouseSqlExample>

***

#### raydium\_cpmm\_swaps

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 16,040,649                           |
| **Size**          | 3.76 GB                              |
| **First Record**  | 2026-05-17                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalDay(90)` |

**Columns:**

| Column                          | Type                     | Description                                                    |
| ------------------------------- | ------------------------ | -------------------------------------------------------------- |
| `block_time`                    | `DateTime`               | UTC timestamp when the block was produced                      |
| `block_date_utc`                | `Date`                   | UTC date of the block (for partitioning)                       |
| `slot`                          | `UInt32`                 | Solana slot number (block height)                              |
| `tx_idx`                        | `UInt16`                 | Transaction index within the block                             |
| `swap_idx`                      | `UInt16`                 | Swap index within the transaction                              |
| `signature`                     | `String`                 | Transaction signature (base58 encoded)                         |
| `fee_payer`                     | `String`                 | Wallet that paid the transaction fee                           |
| `provided_gas_fee`              | `UInt64`                 | Gas fee provided for the transaction (lamports)                |
| `provided_gas_limit`            | `UInt64`                 | Compute unit limit requested                                   |
| `fee`                           | `UInt64`                 | Transaction fee paid (lamports)                                |
| `consumed_gas`                  | `UInt64`                 | Compute units actually consumed                                |
| `pool_id`                       | `String`                 | Pool account address                                           |
| `signer`                        | `String`                 | Wallet address that signed the transaction                     |
| `direction`                     | `LowCardinality(String)` | Trade direction (buy/sell)                                     |
| `base_coin`                     | `String`                 | Base token mint address                                        |
| `quote_coin`                    | `String`                 | Quote token mint address (usually SOL or USDC)                 |
| `base_coin_amount`              | `UInt64`                 | Base token amount (raw units, needs decimal adjustment)        |
| `quote_coin_amount`             | `UInt64`                 | Quote token amount (raw units)                                 |
| `base_coin_slippage`            | `Int64`                  | Slippage on base token                                         |
| `quote_coin_slippage`           | `Int64`                  | Slippage on quote token                                        |
| `parent_program`                | `String`                 | Parent program that invoked this instruction (for CPI calls)   |
| `top_level_transfers_json`      | `String`                 | JSON array of top-level SOL/token transfers in the transaction |
| `input_token_pool_size_before`  | `UInt64`                 | Input token pool size before swap                              |
| `output_token_pool_size_before` | `UInt64`                 | Output token pool size before swap                             |
| `tokens_in`                     | `UInt64`                 | Tokens received by pool                                        |
| `tokens_out`                    | `UInt64`                 | Tokens sent from pool                                          |
| `fee_in_token_in`               | `UInt64`                 | Fee denominated in input token                                 |
| `fee_in_token_out`              | `UInt64`                 | Fee denominated in output token                                |
| `swap_type`                     | `LowCardinality(String)` | Type of swap (BaseIn/BaseOut)                                  |
| `instruction_input1`            | `UInt64`                 | First instruction input parameter                              |
| `instruction_input2`            | `UInt64`                 | Second instruction input parameter                             |
| `input_token_mint`              | `String`                 | Input token mint address                                       |
| `output_token_mint`             | `String`                 | Output token mint address                                      |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/D2TYsbAPkhkgj5Q2tEF7DXVnjCgcb6BP4WRn2sp2DChJpDSfcQuNeFAyfdSQkXjpsx16LbVFUuby6boT7rit7aH)

| Column             | Value                                                |
| ------------------ | ---------------------------------------------------- |
| `block_time`       | `2026-08-15 16:07:06`                                |
| `block_date_utc`   | `2026-08-15`                                         |
| `slot`             | `439,525,834`                                        |
| `tx_idx`           | `1,007`                                              |
| `swap_idx`         | `5`                                                  |
| `signature`        | `D2TYsbAPkhkgj5Q2tEF7DXVnjCgcb6BP4WRn2sp2DChJpDS...` |
| `fee_payer`        | `2DHfSrnyTtpgTMDMbGiWSeRoyUXrDr6nt1j6LMZY2KTr`       |
| `provided_gas_fee` | `2`                                                  |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.raydium\_cpmm\_swaps LIMIT 10
</ClickHouseSqlExample>

***

#### raydium\_launchpad\_cpmm\_migrations

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 3,678                                |
| **Size**          | 4.0 MB                               |
| **First Record**  | 2025-08-15                           |
| **Last Record**   | 2026-08-14                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalYear(1)` |

**Columns:**

| Column                   | Type       | Description                               |
| ------------------------ | ---------- | ----------------------------------------- |
| `signature`              | `String`   | Transaction signature (base58 encoded)    |
| `block_time`             | `DateTime` | UTC timestamp when the block was produced |
| `block_date_utc`         | `Date`     | UTC date of the block (for partitioning)  |
| `slot`                   | `UInt64`   | Solana slot number (block height)         |
| `tx_idx`                 | `UInt32`   | Transaction index within the block        |
| `payer`                  | `String`   | Wallet that paid for the migration        |
| `base_mint`              | `String`   | Base token mint address                   |
| `quote_mint`             | `String`   | Quote token mint address                  |
| `platform_config`        | `String`   | Platform configuration account            |
| `cpswap_program`         | `String`   | CPMM swap program address                 |
| `cpswap_pool`            | `String`   | New CPMM pool address                     |
| `cpswap_authority`       | `String`   | CPMM pool authority                       |
| `cpswap_lp_mint`         | `String`   | CPMM LP token mint                        |
| `cpswap_base_vault`      | `String`   | CPMM base token vault                     |
| `cpswap_quote_vault`     | `String`   | CPMM quote token vault                    |
| `cpswap_config`          | `String`   | CPMM configuration account                |
| `cpswap_create_pool_fee` | `String`   | Fee account for pool creation             |
| `cpswap_observation`     | `String`   | CPMM observation account (for TWAP)       |
| `lock_program`           | `String`   | LP lock program address                   |
| `lock_authority`         | `String`   | LP lock authority                         |
| `lock_lp_vault`          | `String`   | Vault holding locked LP tokens            |
| `authority`              | `String`   | Launchpad authority account               |
| `pool_state`             | `String`   | Launchpad pool state account              |
| `global_config`          | `String`   | Global configuration account              |
| `base_vault`             | `String`   | Launchpad base token vault                |
| `quote_vault`            | `String`   | Launchpad quote token vault               |
| `pool_lp_token`          | `String`   | Pool LP token account                     |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/2kHxUpEbV1ZVjvFaRZZC3UgSQ55VjTiPXxAiZrE2aUCjWsER9czgJT73cZnA1R68eLmzNeK686wiCzHM9MqkHgdz)

| Column           | Value                                                |
| ---------------- | ---------------------------------------------------- |
| `signature`      | `2kHxUpEbV1ZVjvFaRZZC3UgSQ55VjTiPXxAiZrE2aUCjWsE...` |
| `block_time`     | `2026-08-14 22:10:10`                                |
| `block_date_utc` | `2026-08-15`                                         |
| `slot`           | `439,370,475`                                        |
| `tx_idx`         | `1,468`                                              |
| `payer`          | `RAYpQbFNq9i3mu6cKpTKKRwwHFDeK5AuZz8xvxUrCgw`        |
| `base_mint`      | `9x9MKbh5pq8fsTAH6fpSi17UuBgGNAVJPwMYTyXLTRiX`       |
| `quote_mint`     | `So11111111111111111111111111111111111111112`        |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.raydium\_launchpad\_cpmm\_migrations LIMIT 10
</ClickHouseSqlExample>

***

#### raydium\_launchpad\_migrations

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 98                                   |
| **Size**          | 491.6 KB                             |
| **First Record**  | 2025-08-15                           |
| **Last Record**   | 2026-08-14                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalYear(1)` |

**Columns:**

| Column                      | Type       | Description                                    |
| --------------------------- | ---------- | ---------------------------------------------- |
| `signature`                 | `String`   | Transaction signature (base58 encoded)         |
| `block_time`                | `DateTime` | UTC timestamp when the block was produced      |
| `block_date_utc`            | `Date`     | UTC date of the block (for partitioning)       |
| `slot`                      | `UInt64`   | Solana slot number (block height)              |
| `tx_idx`                    | `UInt32`   | Transaction index within the block             |
| `payer`                     | `String`   | Wallet that paid for the migration             |
| `base_coin`                 | `String`   | Base token mint address                        |
| `quote_coin`                | `String`   | Quote token mint address (usually SOL or USDC) |
| `openbook_program`          | `String`   | OpenBook program address                       |
| `serum_market_id`           | `String`   | OpenBook market ID                             |
| `request_queue`             | `String`   | OpenBook request queue                         |
| `event_queue`               | `String`   | OpenBook event queue                           |
| `bids`                      | `String`   | OpenBook bids account                          |
| `asks`                      | `String`   | OpenBook asks account                          |
| `market_vault_signer`       | `String`   | OpenBook vault signer                          |
| `market_base_vault`         | `String`   | OpenBook base token vault                      |
| `market_quote_vault`        | `String`   | OpenBook quote token vault                     |
| `raydium_program`           | `String`   | Raydium AMM program address                    |
| `raydium_market_id`         | `String`   | Raydium AMM market ID                          |
| `amm_authority`             | `String`   | Raydium AMM authority                          |
| `amm_open_orders`           | `String`   | Raydium open orders account                    |
| `lp_mint`                   | `String`   | LP token mint address                          |
| `base_pool_balance_before`  | `String`   | Pool base token balance before migration       |
| `quote_pool_balance_before` | `String`   | Pool quote token balance before migration      |
| `amm_target_orders`         | `String`   | Raydium target orders account                  |
| `amm_config`                | `String`   | Raydium AMM configuration                      |
| `amm_create_fee_dest`       | `String`   | Destination for AMM creation fee               |
| `authority`                 | `String`   | Launchpad authority account                    |
| `pool_state`                | `String`   | Launchpad pool state account                   |
| `global_config`             | `String`   | Global configuration account                   |
| `user_token_coin`           | `String`   | User's base token account                      |
| `user_token_pc`             | `String`   | User's quote token account                     |
| `user_lp_token_account`     | `String`   | User's LP token account                        |
| `token_program`             | `String`   | Token program address                          |
| `associated_token_program`  | `String`   | Associated token program address               |
| `system_program`            | `String`   | System program address                         |
| `rent_program`              | `String`   | Rent sysvar address                            |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/7FvuQT4sdqJPRWT9hmG8WfKagrtfYPWo8iiwrhYoRbfjfhubMrQoJyGVeg9LRgQTHwAdQcMK1jW4S8hb6fusBer)

| Column           | Value                                                |
| ---------------- | ---------------------------------------------------- |
| `signature`      | `7FvuQT4sdqJPRWT9hmG8WfKagrtfYPWo8iiwrhYoRbfjfhu...` |
| `block_time`     | `2026-08-14 13:05:04`                                |
| `block_date_utc` | `2026-08-14`                                         |
| `slot`           | `439,291,807`                                        |
| `tx_idx`         | `1,476`                                              |
| `payer`          | `RAYzrepoBdjSFg7MZj2vy4XBSv2azKRXC72ztUMZMJB`        |
| `base_coin`      | `5cWGJjnLJFLdnp8g7kqqdXf1Q7MLhzVw8HLbrotrench`       |
| `quote_coin`     | `So11111111111111111111111111111111111111112`        |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.raydium\_launchpad\_migrations LIMIT 10
</ClickHouseSqlExample>

***

#### raydium\_launchpad\_swaps

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 23,025,463                           |
| **Size**          | 3.87 GB                              |
| **First Record**  | 2025-08-15                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalYear(1)` |

**Columns:**

| Column                             | Type                     | Description                                     |
| ---------------------------------- | ------------------------ | ----------------------------------------------- |
| `block_time`                       | `DateTime`               | UTC timestamp when the block was produced       |
| `block_date_utc`                   | `Date`                   | UTC date of the block (for partitioning)        |
| `slot`                             | `UInt64`                 | Solana slot number (block height)               |
| `tx_idx`                           | `UInt32`                 | Transaction index within the block              |
| `fee_payer`                        | `String`                 | Wallet that paid the transaction fee            |
| `direction`                        | `LowCardinality(String)` | Trade direction (buy/sell)                      |
| `pool_status`                      | `UInt8`                  | Pool status code                                |
| `pool_state`                       | `String`                 | Pool state account address                      |
| `base_token`                       | `String`                 | Base token mint address                         |
| `quote_token`                      | `String`                 | Quote token mint address                        |
| `total_base_sell`                  | `UInt64`                 | Total base tokens available for sale            |
| `virtual_base`                     | `UInt64`                 | Virtual base token reserves                     |
| `virtual_quote`                    | `UInt64`                 | Virtual quote token reserves                    |
| `pool_base_token_reserves_before`  | `UInt64`                 | Pool base token reserves before swap            |
| `pool_quote_token_reserves_before` | `UInt64`                 | Pool quote token reserves before swap           |
| `pool_base_token_reserves_after`   | `UInt64`                 | Pool base token reserves after swap             |
| `pool_quote_token_reserves_after`  | `UInt64`                 | Pool quote token reserves after swap            |
| `base_token_amount`                | `UInt64`                 | Base token amount (raw units)                   |
| `quote_token_amount`               | `UInt64`                 | Quote token amount (raw units)                  |
| `protocol_fee`                     | `UInt64`                 | Protocol fee amount                             |
| `platform_fee`                     | `UInt64`                 | Platform fee amount                             |
| `share_fee`                        | `UInt64`                 | Share/referral fee amount                       |
| `signature`                        | `String`                 | Transaction signature (base58 encoded)          |
| `provided_gas_fee`                 | `UInt64`                 | Gas fee provided for the transaction (lamports) |
| `provided_gas_limit`               | `UInt64`                 | Compute unit limit requested                    |
| `fee`                              | `UInt64`                 | Transaction fee paid (lamports)                 |
| `consumed_gas`                     | `UInt64`                 | Compute units actually consumed                 |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/47jpfiuk74uXVoPFbkg2ua8qkuW937HbqehWqoyZXSvtNZbpLEQ8sYL1Ly5cCB2mge2hsXEwATvToarr1KJn1bdh)

| Column           | Value                                          |
| ---------------- | ---------------------------------------------- |
| `block_time`     | `2026-08-15 16:04:17`                          |
| `block_date_utc` | `2026-08-15`                                   |
| `slot`           | `439,525,431`                                  |
| `tx_idx`         | `516`                                          |
| `fee_payer`      | `8qWoToS1gd8fqb8qFDBzB1SLMCWu4fYmrncQR8cSshRn` |
| `direction`      | `S`                                            |
| `pool_status`    | `0`                                            |
| `pool_state`     | `4K4sk6hvsziLAnE9ZDiu6MQHGtU7sh8MxHcikamUqpeY` |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.raydium\_launchpad\_swaps LIMIT 10
</ClickHouseSqlExample>

***

#### raydium\_launchpad\_token\_creation

| Statistic        | Value      |
| ---------------- | ---------- |
| **Rows**         | 65,568     |
| **Size**         | 19.9 MB    |
| **First Record** | 2025-04-16 |
| **Last Record**  | 2025-10-15 |

**Columns:**

| Column                       | Type               | Description                                             |
| ---------------------------- | ------------------ | ------------------------------------------------------- |
| `block_time`                 | `DateTime`         | UTC timestamp when the block was produced               |
| `slot`                       | `UInt64`           | Solana slot number (block height)                       |
| `tx_idx`                     | `UInt32`           | Transaction index within the block                      |
| `creator`                    | `FixedString(48)`  | Wallet that created the token                           |
| `name`                       | `FixedString(20)`  | Token name                                              |
| `symbol`                     | `FixedString(16)`  | Token ticker symbol                                     |
| `url`                        | `FixedString(256)` | Metadata URI (usually IPFS)                             |
| `mint`                       | `FixedString(48)`  | Token mint address                                      |
| `bundle_size`                | `UInt8`            | Number of transactions in the bundle                    |
| `gas_used`                   | `UInt64`           | Total gas used for token creation                       |
| `amount_of_instructions`     | `Int32`            | Number of instructions in the transaction               |
| `amount_of_lookup_reads`     | `Int32`            | Number of address lookup table reads                    |
| `amount_of_lookup_writes`    | `Int32`            | Number of address lookup table writes                   |
| `bundle_structure`           | `String`           | Structure of bundled transactions                       |
| `bundled_buys`               | `UInt64`           | Total SOL amount in bundled buys                        |
| `bundled_buys_count`         | `UInt64`           | Number of bundled buy transactions                      |
| `dev_balance`                | `UInt64`           | Developer's token balance after creation                |
| `creation_ix_index`          | `Int32`            | Index of creation instruction                           |
| `pool_state`                 | `FixedString(48)`  | Pool state account address                              |
| `base_vault`                 | `FixedString(48)`  | Base token vault address                                |
| `quote_vault`                | `FixedString(48)`  | Quote token vault address                               |
| `raydium_program_index`      | `UInt8`            | Raydium program index in transaction                    |
| `direct_raydium_invocation`  | `Bool`             | Whether Raydium was called directly                     |
| `decimals`                   | `UInt8`            | Token decimals                                          |
| `cpmm_type`                  | `UInt8`            | CPMM pool type                                          |
| `supply`                     | `UInt64`           | Total token supply                                      |
| `bonding_curve_sell_amount`  | `UInt64`           | Amount available for bonding curve sale                 |
| `bonding_curve_raise_amount` | `UInt64`           | Target raise amount for bonding curve                   |
| `migrate_type`               | `UInt8`            | Migration type after bonding curve                      |
| `version`                    | `DateTime64(3)`    | Record version timestamp *(populated since 2025-04-16)* |

**Latest Record Sample:**

| Column       | Value                                                |
| ------------ | ---------------------------------------------------- |
| `block_time` | `2025-10-15 17:36:42`                                |
| `slot`       | `373,641,138`                                        |
| `tx_idx`     | `714`                                                |
| `creator`    | `b'CrjJT78c3WpSz4kz4x3bawjd8UACGPowMjuz5tKV8NJh\x00` |
| `name`       | `b'Important Coin\x00\x00\x00\x00\x00\x00'`          |
| `symbol`     | `b'IMPORTANT\x00\x00\x00\x00\x00\x00\x00'`           |
| `url`        | `b'https://ipfs.io/ipfs/bafkreif4q7un34ghowsetmgmx6` |
| `mint`       | `b'6JGaQcHA1ZoJ1C3RVVmnbK3S66Xvh7zRLN7piVjsbonk\x00` |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.raydium\_launchpad\_token\_creation LIMIT 10
</ClickHouseSqlExample>

***

#### sol\_top\_ups

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 168,835,724                          |
| **Size**          | 27.55 GB                             |
| **First Record**  | 2026-07-20                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalDay(31)` |

**Columns:**

| Column                     | Type       | Description                                                                                            |
| -------------------------- | ---------- | ------------------------------------------------------------------------------------------------------ |
| `block_time`               | `DateTime` | UTC timestamp when the block was produced                                                              |
| `block_date_utc`           | `Date`     | UTC date of the block (for partitioning)                                                               |
| `slot`                     | `UInt32`   | Solana slot number (block height)                                                                      |
| `tx_idx`                   | `UInt16`   | Transaction index within the block                                                                     |
| `failed`                   | `UInt8`    | Whether the transaction failed (1=yes, 0=no)                                                           |
| `provided_gas_fee`         | `UInt64`   | Gas fee provided for the transaction (lamports)                                                        |
| `provided_gas_limit`       | `UInt64`   | Compute unit limit requested                                                                           |
| `fee`                      | `UInt64`   | Transaction fee paid (lamports)                                                                        |
| `consumed_gas`             | `UInt64`   | Compute units actually consumed                                                                        |
| `cu_price_ix_index`        | `Int16`    | Instruction index of the compute-unit price instruction                                                |
| `cu_limit_ix_index`        | `Int16`    | Instruction index of the compute-unit limit instruction                                                |
| `num_signatures`           | `UInt8`    | Number of signatures on the transaction                                                                |
| `top_level_transfers_json` | `String`   | JSON array of top-level SOL/token transfers in the transaction                                         |
| `transaction_version`      | `Int8`     | Transaction message version (-1=legacy, 0=v0, -2=unknown)                                              |
| `lookup_tables`            | `String`   | Pipe-delimited account addresses loaded through address lookup tables (writable first, then read-only) |
| `signature`                | `String`   | Transaction signature (base58 encoded)                                                                 |
| `fee_payer`                | `String`   | Wallet that paid the transaction fee                                                                   |
| `src_wallet`               | `String`   | Source wallet address                                                                                  |
| `dst_wallet`               | `String`   | Destination wallet address                                                                             |
| `amount`                   | `UInt64`   | Amount of SOL transferred, in lamports                                                                 |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/kPTJ3x7LeAaeoeMQq8xAuFK8GJdrGvznDVFRjLbrAsWXFNvkMLwwPfKdYJq6P1fPoBnYQA48Q82yGquv2d1oMbM)

| Column               | Value                 |
| -------------------- | --------------------- |
| `block_time`         | `2026-08-15 16:07:06` |
| `block_date_utc`     | `2026-08-15`          |
| `slot`               | `439,525,834`         |
| `tx_idx`             | `1,464`               |
| `failed`             | `0`                   |
| `provided_gas_fee`   | `91,634`              |
| `provided_gas_limit` | `64,408`              |
| `fee`                | `20,902`              |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.sol\_top\_ups LIMIT 10
</ClickHouseSqlExample>

***

#### solana\_blocks

| Statistic        | Value       |
| ---------------- | ----------- |
| **Rows**         | 184,508,670 |
| **Size**         | 9.45 GB     |
| **First Record** | 1969-12-31  |
| **Last Record**  | 2026-08-15  |

**Columns:**

| Column                   | Type              | Description                                                          |
| ------------------------ | ----------------- | -------------------------------------------------------------------- |
| `block_time`             | `DateTime`        | UTC timestamp when the block was produced                            |
| `slot`                   | `UInt32`          | Solana slot number (block height)                                    |
| `hash`                   | `FixedString(48)` | Block hash                                                           |
| `validator`              | `FixedString(48)` | Validator identity that produced the block                           |
| `rewards`                | `UInt64`          | Total rewards in the block (lamports) *(populated since 2024-11-02)* |
| `amount_of_transactions` | `UInt64`          | Number of transactions in the block *(populated since 1969-12-31)*   |

**Latest Record Sample:**

| Column                   | Value                                                |
| ------------------------ | ---------------------------------------------------- |
| `block_time`             | `2026-08-15 16:07:06`                                |
| `slot`                   | `439,525,834`                                        |
| `hash`                   | `b'Afr4S9bUssnnEDxGvd6FfdosX8Xsd1kCuZdy8M8F4Aur\x00` |
| `validator`              | `b'EvnRmnMrd69kFdbLMxWkTn1icZ7DCceRhvmb2SJXqDo4\x00` |
| `rewards`                | `24,974,349`                                         |
| `amount_of_transactions` | `1,532`                                              |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.solana\_blocks LIMIT 10
</ClickHouseSqlExample>

***

#### token\_transfers

| Statistic         | Value                                |
| ----------------- | ------------------------------------ |
| **Rows**          | 141,849,233                          |
| **Size**          | 21.87 GB                             |
| **First Record**  | 2026-07-20                           |
| **Last Record**   | 2026-08-15                           |
| **Partition Key** | `block_date_utc`                     |
| **TTL**           | `block_date_utc + toIntervalDay(31)` |

**Columns:**

| Column                     | Type       | Description                                                                                                                                        |
| -------------------------- | ---------- | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| `block_time`               | `DateTime` | UTC timestamp when the block was produced                                                                                                          |
| `block_date_utc`           | `Date`     | UTC date of the block (for partitioning)                                                                                                           |
| `slot`                     | `UInt32`   | Solana slot number (block height)                                                                                                                  |
| `tx_idx`                   | `UInt16`   | Transaction index within the block                                                                                                                 |
| `failed`                   | `UInt8`    | Whether the transaction failed (1=yes, 0=no)                                                                                                       |
| `provided_gas_fee`         | `UInt64`   | Gas fee provided for the transaction (lamports)                                                                                                    |
| `provided_gas_limit`       | `UInt64`   | Compute unit limit requested                                                                                                                       |
| `fee`                      | `UInt64`   | Transaction fee paid (lamports)                                                                                                                    |
| `consumed_gas`             | `UInt64`   | Compute units actually consumed                                                                                                                    |
| `cu_price_ix_index`        | `Int16`    | Instruction index of the compute-unit price instruction                                                                                            |
| `cu_limit_ix_index`        | `Int16`    | Instruction index of the compute-unit limit instruction                                                                                            |
| `num_signatures`           | `UInt8`    | Number of signatures on the transaction                                                                                                            |
| `top_level_transfers_json` | `String`   | JSON array of top-level SOL/token transfers in the transaction                                                                                     |
| `transaction_version`      | `Int8`     | Transaction message version (-1=legacy, 0=v0, -2=unknown)                                                                                          |
| `lookup_tables`            | `String`   | Pipe-delimited account addresses loaded through address lookup tables (writable first, then read-only)                                             |
| `signature`                | `String`   | Transaction signature (base58 encoded)                                                                                                             |
| `fee_payer`                | `String`   | Wallet that paid the transaction fee                                                                                                               |
| `mint`                     | `String`   | Token mint address                                                                                                                                 |
| `src_wallet`               | `String`   | Source wallet address                                                                                                                              |
| `dst_wallet`               | `String`   | Destination wallet address                                                                                                                         |
| `amount`                   | `UInt64`   | Token amount transferred in raw base units (apply the mint's decimals for display)                                                                 |
| `flavour`                  | `UInt8`    | Transfer encoding: add 16 for Token-2022; base values are 0=Transfer, 1=TransferChecked, 2=TransferCheckedWithFee, and 3=SetAuthority owner change |
| `ix_idx`                   | `Int16`    | Instruction index within the transaction                                                                                                           |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/1ABVLJfFi2r9YeidVHiFinne58Cbz35xWBSn2tM5HubvAXAFL5KmBnMbLFVYEgtWaKJx9h1Jm1VvDN5eTr72TKh)

| Column               | Value                 |
| -------------------- | --------------------- |
| `block_time`         | `2026-08-15 16:07:06` |
| `block_date_utc`     | `2026-08-15`          |
| `slot`               | `439,525,834`         |
| `tx_idx`             | `1,476`               |
| `failed`             | `0`                   |
| `provided_gas_fee`   | `4,000`               |
| `provided_gas_limit` | `0`                   |
| `fee`                | `10,600`              |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.token\_transfers LIMIT 10
</ClickHouseSqlExample>

***

#### tx\_timestamps

| Statistic         | Value                                             |
| ----------------- | ------------------------------------------------- |
| **Rows**          | 2,881,988,252                                     |
| **Size**          | 256.10 GB                                         |
| **Partition Key** | `toYYYYMMDD(toDateTime(entry_timestamp))`         |
| **TTL**           | `toDateTime(entry_timestamp) + toIntervalDay(14)` |

**Columns:**

| Column            | Type      | Description                                     |
| ----------------- | --------- | ----------------------------------------------- |
| `slot`            | `UInt64`  | Solana slot number (block height)               |
| `tx_idx`          | `UInt32`  | Transaction index within the block              |
| `entry_timestamp` | `Float64` | Entry timestamp (high-precision Unix timestamp) |
| `signature`       | `String`  | Transaction signature (base58 encoded)          |

**Latest Record Sample:**

[View on Solscan](https://solscan.io/tx/37RhVxMiCVypeaGoWzrePWDKs1VBE46hENPkSMdJvgScXJ4FQYLw8J38eiyiAjUb7AxPcBdij5eg6AYxjqLzbiaC)

| Column            | Value                                                |
| ----------------- | ---------------------------------------------------- |
| `slot`            | `438,983,103`                                        |
| `tx_idx`          | `595`                                                |
| `entry_timestamp` | `1.78661e+09`                                        |
| `signature`       | `37RhVxMiCVypeaGoWzrePWDKs1VBE46hENPkSMdJvgScXJ4...` |

**Example Query:**

<ClickHouseSqlExample database="solana">
  SELECT \* FROM default.tx\_timestamps LIMIT 10
</ClickHouseSqlExample>

***


## DexScrAIner — AI Trading Assistant

An AI-powered assistant that answers questions about on-chain trading data using natural language.

\:::info Beta Access
DexScrAIner is available to indexer subscribers. [Contact us](https://t.me/inventandchill) to try it.
\:::

***

### What It Does

Ask questions in plain English. Get SQL queries and results.

**You ask:**

> "What are the top 10 tokens by volume on Pump.fun today?"

**DexScrAIner:**

1. Generates the SQL query
2. Executes it against our database
3. Returns formatted results
4. Explains what it found

***

### Example Conversations

#### Token Research

> **You:** "Show me tokens launched in the last hour with more than 100 SOL volume"

```sql
SELECT
    c.name,
    c.symbol,
    c.mint,
    count(*) as trades,
    sum(s.quote_coin_amount) / 1e9 as sol_volume
FROM pumpfun_token_creation c
JOIN pumpfun_all_swaps s ON c.mint = s.base_coin
WHERE c.block_time > now() - INTERVAL 1 HOUR
GROUP BY c.name, c.symbol, c.mint
HAVING sol_volume > 100
ORDER BY sol_volume DESC
```

| Name | Symbol | Volume (SOL) |
| ---- | ------ | ------------ |
| ...  | ...    | ...          |

#### Wallet Analysis

> **You:** "Has this wallet traded any newly launched tokens?"
> **\[pastes wallet address]**

DexScrAIner checks the wallet's activity against token launch times and identifies early trading patterns.

#### Market Trends

> **You:** "Compare Raydium vs Pump.fun volume over the last week"

Generates cross-DEX comparison with daily breakdowns.

***

### Capabilities

#### Data Sources

DexScrAIner has access to:

* All Solana DEX swap data
* Token creation events
* Pool migrations
* Jito tips
* Block metadata

#### Query Types

* Aggregations (volume, counts, averages)
* Time-series analysis
* Wallet behavior patterns
* Cross-table joins
* Custom filters

#### Output Formats

* Tables with formatted data
* Summary statistics
* Trend descriptions
* The underlying SQL for verification

***

### How It Works

1. **Natural Language Processing** — Your question is parsed to understand intent
2. **Schema Awareness** — The AI knows all table structures and relationships
3. **Query Generation** — SQL is generated with proper time filters and aggregations
4. **Execution** — Query runs against our ClickHouse database
5. **Interpretation** — Results are summarized in plain language

***

### Limitations

* **No real-time predictions** — It analyzes historical data, not future prices
* **Query complexity** — Very complex multi-step analyses may need refinement
* **Data scope** — Limited to indexed tables (no arbitrary RPC calls)

***

### Use Cases

#### For Traders

* Quick wallet research before following
* Token volume and holder analysis
* Identify trading patterns

#### For Researchers

* Generate queries without knowing SQL
* Explore data interactively
* Validate hypotheses quickly

#### For Builders

* Prototype analytics features
* Test query logic before implementing
* Understand data relationships

***

### Access

DexScrAIner is available to subscribers of our Solana Indexer. Contact [@inventandchill](https://t.me/inventandchill) to get access.


## Services

Beyond data infrastructure, we offer specialized services for blockchain projects and trading operations.

***

### What We Offer

#### Custom Development

Need something specific? We build:

* **Custom Indexers** — Index any on-chain data with your specific requirements
* **Trading Bots** — Execution systems, market making, arbitrage
* **Analytics Dashboards** — Real-time monitoring and visualization
* **Data Pipelines** — ETL from blockchain to your systems

#### Consulting

Strategic guidance from experienced blockchain engineers:

* Architecture review for trading systems
* MEV strategy analysis
* Infrastructure optimization
* Security audits for smart contracts

#### Integration Support

Help connecting to our infrastructure:

* Custom data formats and exports
* API integration assistance
* Query optimization
* Training for your team

***

### Past Projects

We've worked with:

* **Trading firms** building execution infrastructure
* **Analytics companies** creating market intelligence products
* **DeFi protocols** monitoring their on-chain activity
* **Research teams** analyzing market microstructure

***

### Engagement Models

#### Project-Based

Fixed scope and timeline. Good for:

* Building a specific bot or tool
* Creating a custom indexer
* One-time analysis or research

#### Retainer

Ongoing support. Good for:

* Continuous development
* Infrastructure maintenance
* Priority support access

#### Consultation

Hourly or daily. Good for:

* Architecture review
* Strategy sessions
* Knowledge transfer

***

### Technology Stack

We work with:

* **Blockchains**: Solana, Ethereum, L2s
* **Databases**: ClickHouse, PostgreSQL, Redis
* **Languages**: Rust, Python, TypeScript
* **Infrastructure**: AWS, Hetzner, bare metal

***

### Get In Touch

**Telegram**: [@inventandchill](https://t.me/inventandchill)
**Email**: [hello@web3engineering.co.uk](mailto\:hello@web3engineering.co.uk)

Describe your project and we'll discuss the best approach.


## WSOL Exchange

The most efficient way to wrap and unwrap SOL on Solana.

***

### About WSOL Exchange

WSOL Exchange is the most efficient and user-friendly way to wrap and unwrap SOL on the Solana blockchain. Our platform is designed to provide the lowest possible transaction costs with optimized compute unit usage (17,000 CU) and zero priority fees. Whether you're a DeFi user, trader, or developer, WSOL Exchange makes it simple to convert between SOL and WSOL whenever you need to.

Built with security and transparency in mind, all transactions are executed directly through your wallet using standard Solana protocols. We never have access to your funds, and there are no hidden fees or platform charges. Just fast, secure, and cost-effective SOL/WSOL conversions.

\:::tip Try WSOL Exchange
Visit [wsol.exchange](https://wsol.exchange/) to wrap or unwrap SOL instantly.
\:::

***

### Frequently Asked Questions

Everything you need to know about WSOL and how to use WSOL Exchange.

#### What is WSOL?

WSOL (Wrapped SOL) is the SPL token version of Solana's native SOL token. It wraps SOL into the SPL token standard, making it compatible with Solana programs that work exclusively with SPL tokens. WSOL always maintains a 1:1 ratio with SOL, meaning 1 WSOL = 1 SOL.

#### Why do I need to wrap SOL to WSOL?

Many Solana DeFi applications, decentralized exchanges (DEXes), and smart contracts require tokens to follow the SPL token standard. Since SOL is the native blockchain token, it doesn't follow this standard by default. Wrapping SOL to WSOL makes it compatible with these protocols while maintaining the exact same value. Common use cases include providing liquidity on DEXes, participating in DeFi protocols, and interacting with NFT marketplaces.

#### How do I unwrap WSOL back to SOL?

Unwrapping WSOL to SOL is simple on WSOL Exchange. Connect your wallet, select the WSOL to SOL direction, enter the amount you want to unwrap, and confirm the transaction. Your WSOL will be instantly converted back to native SOL at a 1:1 ratio. The process closes your WSOL token account and returns the SOL to your wallet.

#### What are the fees for wrapping/unwrapping?

WSOL Exchange charges no platform fees. You only pay the standard Solana network transaction fee, which is typically around 0.000005 SOL (less than $0.001). Our transactions are optimized with a 17,000 compute unit budget and 0 priority fees to keep costs as low as possible.

#### Is there a minimum amount for wrapping or unwrapping?

When unwrapping WSOL to SOL, if you're leaving any remaining balance in your WSOL account, it must be at least 0.003 SOL (the rent-exempt minimum for a token account). If the remaining amount would be less than 0.003 SOL, you should unwrap your entire WSOL balance instead. For wrapping SOL to WSOL, there is no minimum, but remember to keep some SOL in your wallet for transaction fees.

#### What is the WSOL token address?

The WSOL mint address is `So11111111111111111111111111111111111111112` (also known as `NATIVE_MINT`). This is the same address used across all Solana applications for wrapped SOL.

#### Is WSOL Exchange safe to use?

Yes, WSOL Exchange is completely safe. All transactions are executed directly through your connected wallet, and we never have custody of your funds. The wrapping and unwrapping process uses standard Solana SPL token instructions that are verified and used throughout the Solana ecosystem. The exchange is open source and the smart contracts are transparent.

#### How long does a wrap/unwrap transaction take?

Transactions on WSOL Exchange are typically confirmed within seconds. Once you submit a transaction, it's processed by the Solana network at its normal speed. You'll see your updated balance immediately after the transaction confirms.


## Verified Robinhood Chain examples

Robinhood data uses dedicated read-only credentials because `robinhood` is a
separate ClickHouse database. Configure these values in `.env`:

```dotenv
ROBINHOOD_CLICKHOUSE_URL=clickhouse.example.com:8123
ROBINHOOD_CLICKHOUSE_USER=docs_reader
ROBINHOOD_CLICKHOUSE_PASSWORD=replace-me
```

### Compare recent Uniswap v3 and v4 swaps

The first query normalizes pool identifiers and signed raw token amounts across
both Uniswap versions while preserving their protocol-specific sender and
recipient behavior. Amounts remain strings so 128-bit and 256-bit integers are
not truncated by downstream JSON consumers.

```sql
SELECT
    protocol,
    block_timestamp,
    transaction_hash,
    pool,
    sender,
    recipient,
    amount0_raw,
    amount1_raw,
    tick,
    sqrt_price_x96
FROM
(
    SELECT
        'uniswap_v3' AS protocol,
        block_timestamp,
        transaction_hash,
        pool_address AS pool,
        sender,
        toNullable(recipient) AS recipient,
        toString(amount0) AS amount0_raw,
        toString(amount1) AS amount1_raw,
        tick,
        toString(sqrt_price_x96) AS sqrt_price_x96
    FROM robinhood.uniswap_v3_trades
    PREWHERE block_timestamp >= now() - INTERVAL 1 HOUR
    ORDER BY block_number DESC, transaction_index DESC, log_index DESC
    LIMIT 50

    UNION ALL

    SELECT
        'uniswap_v4' AS protocol,
        block_timestamp,
        transaction_hash,
        pool_id AS pool,
        sender,
        CAST(NULL, 'Nullable(String)') AS recipient,
        toString(amount0) AS amount0_raw,
        toString(amount1) AS amount1_raw,
        tick,
        toString(sqrt_price_x96) AS sqrt_price_x96
    FROM robinhood.uniswap_v4_trades
    PREWHERE block_timestamp >= now() - INTERVAL 1 HOUR
    ORDER BY block_number DESC, transaction_index DESC, log_index DESC
    LIMIT 50
)
ORDER BY block_timestamp DESC
LIMIT 100
```

Source: `examples/robinhood/recent_uniswap_trades.sql`.

### Find the most active pools

The second query scans the latest complete UTC hour rather than a moving partial
hour. It groups both protocols into one result while retaining the pool key
format used by each version.

```sql
WITH pool_activity AS
(
    SELECT
        'uniswap_v3' AS protocol,
        pool_address AS pool,
        count() AS trades,
        uniqExact(transaction_hash) AS transactions,
        min(block_timestamp) AS first_trade,
        max(block_timestamp) AS last_trade
    FROM robinhood.uniswap_v3_trades
    PREWHERE block_timestamp >= toStartOfHour(now()) - INTERVAL 1 HOUR
        AND block_timestamp < toStartOfHour(now())
    GROUP BY pool_address

    UNION ALL

    SELECT
        'uniswap_v4' AS protocol,
        pool_id AS pool,
        count() AS trades,
        uniqExact(transaction_hash) AS transactions,
        min(block_timestamp) AS first_trade,
        max(block_timestamp) AS last_trade
    FROM robinhood.uniswap_v4_trades
    PREWHERE block_timestamp >= toStartOfHour(now()) - INTERVAL 1 HOUR
        AND block_timestamp < toStartOfHour(now())
    GROUP BY pool_id
)
SELECT
    protocol,
    pool,
    trades,
    transactions,
    first_trade,
    last_trade
FROM pool_activity
ORDER BY trades DESC, protocol, pool
LIMIT 50
```

Source: `examples/robinhood/most_active_pools.sql`.

Both source queries execute against the live database during the Docker checker
stage. Empty results or schema/query errors fail the documentation build.


## Robinhood Chain indexer

Robinhood Chain is an Ethereum-compatible Arbitrum Layer 2. OnchainDivers
provides its decoded token and Uniswap activity in a dedicated `robinhood`
ClickHouse database, separate from the Solana, Polymarket and HyperLiquid
datasets.

The network uses ETH for gas and chain ID `4663`. See the
[official Robinhood Chain documentation](https://docs.robinhood.com/chain/)
for network concepts and connection details.

| Table               | Purpose                                                               |
| ------------------- | --------------------------------------------------------------------- |
| `tokens`            | ERC-20 identities, descriptive metadata, creator and creation context |
| `uniswap_v3_pools`  | v3 token pairs, pool fee and tick spacing                             |
| `uniswap_v3_trades` | v3 swap amounts, price, liquidity, tick and transaction context       |
| `uniswap_v4_pools`  | v4 currencies, fee, hooks and initial pool state                      |
| `uniswap_v4_trades` | v4 swap amounts, fee, price, liquidity, tick and transaction context  |

All decoded records retain block, log, transaction, gas and EIP-1559 fee
fields. This makes it possible to reproduce event ordering and relate protocol
activity to its execution cost without querying a second provider.

Credentials are supplied through `.env`; no deployment address or credential
is included in this site.

* [Verified Robinhood Chain examples](/robinhood/examples)
* [Live Robinhood Chain table reference](/robinhood/tables)


## Robinhood Chain Tables

Tables containing Robinhood Chain token metadata and decoded Uniswap v3 and v4 pool and swap events.

*Last updated: 2026-08-15 23:08 UTC*

import { ClickHouseSqlExample } from '../../components/ClickHouseSqlExample'

### Tables (5)

| Table                                     | Rows       | Size     |
| ----------------------------------------- | ---------- | -------- |
| [tokens](#tokens)                         | 51,114     | 24.5 MB  |
| [uniswap\_v3\_pools](#uniswap_v3_pools)   | 443,485    | 157.1 MB |
| [uniswap\_v3\_trades](#uniswap_v3_trades) | 93,792,085 | 25.00 GB |
| [uniswap\_v4\_pools](#uniswap_v4_pools)   | 383,203    | 148.1 MB |
| [uniswap\_v4\_trades](#uniswap_v4_trades) | 36,162,772 | 8.68 GB  |

***

#### tokens

| Statistic        | Value      |
| ---------------- | ---------- |
| **Rows**         | 51,114     |
| **Size**         | 24.5 MB    |
| **First Record** | 2026-07-08 |
| **Last Record**  | 2026-08-15 |

**Columns:**

| Column                     | Type       | Description                                                |
| -------------------------- | ---------- | ---------------------------------------------------------- |
| `token_address`            | `String`   | ERC-20 token contract address                              |
| `name`                     | `String`   | Token name from contract metadata                          |
| `symbol`                   | `String`   | Token ticker symbol from contract metadata                 |
| `decimals`                 | `UInt8`    | Number of decimal places used by the token                 |
| `description`              | `String`   | Human-readable token description when available            |
| `website`                  | `String`   | Project website URL when available                         |
| `image`                    | `String`   | Token image URL when available                             |
| `extra_data`               | `String`   | Additional token metadata serialized as text               |
| `creator`                  | `String`   | Address associated with creation of the token              |
| `event_id`                 | `String`   | Unique identifier for the decoded contract event           |
| `block_number`             | `UInt64`   | EVM block number containing the event                      |
| `log_index`                | `UInt32`   | Event log index within the transaction receipt             |
| `transaction_index`        | `UInt32`   | Transaction index within the block                         |
| `contract_address`         | `String`   | Address of the contract that emitted the event             |
| `block_hash`               | `String`   | Hash of the block containing the event                     |
| `block_timestamp`          | `DateTime` | UTC timestamp of the block containing the event            |
| `gas_used`                 | `UInt64`   | Gas consumed by the transaction                            |
| `gas_limit`                | `UInt64`   | Transaction gas limit                                      |
| `base_fee_per_gas`         | `UInt256`  | Block base fee per gas in wei                              |
| `transaction_hash`         | `String`   | Hash of the transaction containing the event               |
| `transaction_from`         | `String`   | Address that submitted the transaction                     |
| `transaction_to`           | `String`   | Transaction destination address                            |
| `transaction_value`        | `UInt256`  | Native ETH value sent with the transaction, in wei         |
| `transaction_gas`          | `UInt64`   | Gas limit declared by the transaction                      |
| `transaction_nonce`        | `UInt64`   | Sender nonce used by the transaction                       |
| `max_fee_per_gas`          | `UInt256`  | Maximum EIP-1559 fee per gas in wei                        |
| `max_priority_fee_per_gas` | `UInt256`  | Maximum EIP-1559 priority fee per gas in wei               |
| `inserted_at`              | `DateTime` | UTC timestamp when the record was inserted into ClickHouse |

**Example Query:**

<ClickHouseSqlExample database="robinhood">
  SELECT \* FROM robinhood.tokens LIMIT 10
</ClickHouseSqlExample>

***

#### uniswap\_v3\_pools

| Statistic        | Value      |
| ---------------- | ---------- |
| **Rows**         | 443,485    |
| **Size**         | 157.1 MB   |
| **First Record** | 2026-06-18 |
| **Last Record**  | 2026-08-15 |

**Columns:**

| Column                     | Type       | Description                                                |
| -------------------------- | ---------- | ---------------------------------------------------------- |
| `pool_address`             | `String`   | Address of the Uniswap v3 pool contract                    |
| `token0`                   | `String`   | Address of token0 in the pool                              |
| `token1`                   | `String`   | Address of token1 in the pool                              |
| `fee`                      | `UInt32`   | Pool swap fee in hundredths of a basis point               |
| `tick_spacing`             | `Int32`    | Permitted spacing between initialized ticks                |
| `event_id`                 | `String`   | Unique identifier for the decoded contract event           |
| `block_number`             | `UInt64`   | EVM block number containing the event                      |
| `log_index`                | `UInt32`   | Event log index within the transaction receipt             |
| `transaction_index`        | `UInt32`   | Transaction index within the block                         |
| `contract_address`         | `String`   | Address of the contract that emitted the event             |
| `block_hash`               | `String`   | Hash of the block containing the event                     |
| `block_timestamp`          | `DateTime` | UTC timestamp of the block containing the event            |
| `gas_used`                 | `UInt64`   | Gas consumed by the transaction                            |
| `gas_limit`                | `UInt64`   | Transaction gas limit                                      |
| `base_fee_per_gas`         | `UInt256`  | Block base fee per gas in wei                              |
| `transaction_hash`         | `String`   | Hash of the transaction containing the event               |
| `transaction_from`         | `String`   | Address that submitted the transaction                     |
| `transaction_to`           | `String`   | Transaction destination address                            |
| `transaction_value`        | `UInt256`  | Native ETH value sent with the transaction, in wei         |
| `transaction_gas`          | `UInt64`   | Gas limit declared by the transaction                      |
| `transaction_nonce`        | `UInt64`   | Sender nonce used by the transaction                       |
| `max_fee_per_gas`          | `UInt256`  | Maximum EIP-1559 fee per gas in wei                        |
| `max_priority_fee_per_gas` | `UInt256`  | Maximum EIP-1559 priority fee per gas in wei               |
| `inserted_at`              | `DateTime` | UTC timestamp when the record was inserted into ClickHouse |

**Example Query:**

<ClickHouseSqlExample database="robinhood">
  SELECT \* FROM robinhood.uniswap\_v3\_pools LIMIT 10
</ClickHouseSqlExample>

***

#### uniswap\_v3\_trades

| Statistic         | Value                         |
| ----------------- | ----------------------------- |
| **Rows**          | 93,792,085                    |
| **Size**          | 25.00 GB                      |
| **First Record**  | 2026-05-22                    |
| **Last Record**   | 2026-08-15                    |
| **Partition Key** | `toYYYYMMDD(block_timestamp)` |

**Columns:**

| Column                     | Type       | Description                                                    |
| -------------------------- | ---------- | -------------------------------------------------------------- |
| `pool_address`             | `String`   | Address of the Uniswap v3 pool contract                        |
| `sender`                   | `String`   | Address that initiated the swap callback                       |
| `recipient`                | `String`   | Address that received the swap output                          |
| `amount0`                  | `Int256`   | Signed change in the pool's token0 balance, in raw token units |
| `amount1`                  | `Int256`   | Signed change in the pool's token1 balance, in raw token units |
| `sqrt_price_x96`           | `UInt256`  | Pool square-root price after the swap as a Q64.96 integer      |
| `liquidity`                | `UInt128`  | In-range pool liquidity after the swap                         |
| `tick`                     | `Int32`    | Pool tick after the swap                                       |
| `event_id`                 | `String`   | Unique identifier for the decoded contract event               |
| `block_number`             | `UInt64`   | EVM block number containing the event                          |
| `log_index`                | `UInt32`   | Event log index within the transaction receipt                 |
| `transaction_index`        | `UInt32`   | Transaction index within the block                             |
| `contract_address`         | `String`   | Address of the contract that emitted the event                 |
| `block_hash`               | `String`   | Hash of the block containing the event                         |
| `block_timestamp`          | `DateTime` | UTC timestamp of the block containing the event                |
| `gas_used`                 | `UInt64`   | Gas consumed by the transaction                                |
| `gas_limit`                | `UInt64`   | Transaction gas limit                                          |
| `base_fee_per_gas`         | `UInt256`  | Block base fee per gas in wei                                  |
| `transaction_hash`         | `String`   | Hash of the transaction containing the event                   |
| `transaction_from`         | `String`   | Address that submitted the transaction                         |
| `transaction_to`           | `String`   | Transaction destination address                                |
| `transaction_value`        | `UInt256`  | Native ETH value sent with the transaction, in wei             |
| `transaction_gas`          | `UInt64`   | Gas limit declared by the transaction                          |
| `transaction_nonce`        | `UInt64`   | Sender nonce used by the transaction                           |
| `max_fee_per_gas`          | `UInt256`  | Maximum EIP-1559 fee per gas in wei                            |
| `max_priority_fee_per_gas` | `UInt256`  | Maximum EIP-1559 priority fee per gas in wei                   |
| `inserted_at`              | `DateTime` | UTC timestamp when the record was inserted into ClickHouse     |

**Example Query:**

<ClickHouseSqlExample database="robinhood">
  SELECT \* FROM robinhood.uniswap\_v3\_trades LIMIT 10
</ClickHouseSqlExample>

***

#### uniswap\_v4\_pools

| Statistic        | Value      |
| ---------------- | ---------- |
| **Rows**         | 383,203    |
| **Size**         | 148.1 MB   |
| **First Record** | 2026-05-22 |
| **Last Record**  | 2026-08-15 |

**Columns:**

| Column                     | Type       | Description                                                |
| -------------------------- | ---------- | ---------------------------------------------------------- |
| `pool_id`                  | `String`   | Uniswap v4 pool identifier derived from its PoolKey        |
| `currency0`                | `String`   | Address of currency0, or the native-currency sentinel      |
| `currency1`                | `String`   | Address of currency1, or the native-currency sentinel      |
| `fee`                      | `UInt32`   | Pool swap fee in hundredths of a basis point               |
| `tick_spacing`             | `Int32`    | Permitted spacing between initialized ticks                |
| `hooks`                    | `String`   | Address of the hooks contract configured for the pool      |
| `init_sqrt_price_x96`      | `UInt256`  | Initial square-root price encoded as a Q64.96 integer      |
| `init_tick`                | `Int32`    | Pool tick at initialization                                |
| `event_id`                 | `String`   | Unique identifier for the decoded contract event           |
| `block_number`             | `UInt64`   | EVM block number containing the event                      |
| `log_index`                | `UInt32`   | Event log index within the transaction receipt             |
| `transaction_index`        | `UInt32`   | Transaction index within the block                         |
| `contract_address`         | `String`   | Address of the contract that emitted the event             |
| `block_hash`               | `String`   | Hash of the block containing the event                     |
| `block_timestamp`          | `DateTime` | UTC timestamp of the block containing the event            |
| `gas_used`                 | `UInt64`   | Gas consumed by the transaction                            |
| `gas_limit`                | `UInt64`   | Transaction gas limit                                      |
| `base_fee_per_gas`         | `UInt256`  | Block base fee per gas in wei                              |
| `transaction_hash`         | `String`   | Hash of the transaction containing the event               |
| `transaction_from`         | `String`   | Address that submitted the transaction                     |
| `transaction_to`           | `String`   | Transaction destination address                            |
| `transaction_value`        | `UInt256`  | Native ETH value sent with the transaction, in wei         |
| `transaction_gas`          | `UInt64`   | Gas limit declared by the transaction                      |
| `transaction_nonce`        | `UInt64`   | Sender nonce used by the transaction                       |
| `max_fee_per_gas`          | `UInt256`  | Maximum EIP-1559 fee per gas in wei                        |
| `max_priority_fee_per_gas` | `UInt256`  | Maximum EIP-1559 priority fee per gas in wei               |
| `inserted_at`              | `DateTime` | UTC timestamp when the record was inserted into ClickHouse |

**Example Query:**

<ClickHouseSqlExample database="robinhood">
  SELECT \* FROM robinhood.uniswap\_v4\_pools LIMIT 10
</ClickHouseSqlExample>

***

#### uniswap\_v4\_trades

| Statistic         | Value                         |
| ----------------- | ----------------------------- |
| **Rows**          | 36,162,772                    |
| **Size**          | 8.68 GB                       |
| **First Record**  | 2026-05-22                    |
| **Last Record**   | 2026-08-15                    |
| **Partition Key** | `toYYYYMMDD(block_timestamp)` |

**Columns:**

| Column                     | Type       | Description                                                   |
| -------------------------- | ---------- | ------------------------------------------------------------- |
| `pool_id`                  | `String`   | Uniswap v4 pool identifier derived from its PoolKey           |
| `sender`                   | `String`   | Address recorded as the swap sender                           |
| `amount0`                  | `Int128`   | Signed change in the pool's currency0 balance, in raw units   |
| `amount1`                  | `Int128`   | Signed change in the pool's currency1 balance, in raw units   |
| `sqrt_price_x96`           | `UInt256`  | Pool square-root price after the swap as a Q64.96 integer     |
| `liquidity`                | `UInt128`  | In-range pool liquidity after the swap                        |
| `tick`                     | `Int32`    | Pool tick after the swap                                      |
| `fee`                      | `UInt32`   | Swap fee applied to this event in hundredths of a basis point |
| `event_id`                 | `String`   | Unique identifier for the decoded contract event              |
| `block_number`             | `UInt64`   | EVM block number containing the event                         |
| `log_index`                | `UInt32`   | Event log index within the transaction receipt                |
| `transaction_index`        | `UInt32`   | Transaction index within the block                            |
| `contract_address`         | `String`   | Address of the contract that emitted the event                |
| `block_hash`               | `String`   | Hash of the block containing the event                        |
| `block_timestamp`          | `DateTime` | UTC timestamp of the block containing the event               |
| `gas_used`                 | `UInt64`   | Gas consumed by the transaction                               |
| `gas_limit`                | `UInt64`   | Transaction gas limit                                         |
| `base_fee_per_gas`         | `UInt256`  | Block base fee per gas in wei                                 |
| `transaction_hash`         | `String`   | Hash of the transaction containing the event                  |
| `transaction_from`         | `String`   | Address that submitted the transaction                        |
| `transaction_to`           | `String`   | Transaction destination address                               |
| `transaction_value`        | `UInt256`  | Native ETH value sent with the transaction, in wei            |
| `transaction_gas`          | `UInt64`   | Gas limit declared by the transaction                         |
| `transaction_nonce`        | `UInt64`   | Sender nonce used by the transaction                          |
| `max_fee_per_gas`          | `UInt256`  | Maximum EIP-1559 fee per gas in wei                           |
| `max_priority_fee_per_gas` | `UInt256`  | Maximum EIP-1559 priority fee per gas in wei                  |
| `inserted_at`              | `DateTime` | UTC timestamp when the record was inserted into ClickHouse    |

**Example Query:**

<ClickHouseSqlExample database="robinhood">
  SELECT \* FROM robinhood.uniswap\_v4\_trades LIMIT 10
</ClickHouseSqlExample>

***


## Most reliable Pump.fun token creators

A launch qualifies when it has more than five distinct buy transactions in
every slot of at least one eight-consecutive-slot streak within its first 128
post-launch slots. The table ranks creators from the latest 24-hour
data window by qualifying launches, qualification rate, and sustained buy activity.
Only launches with the full 128-slot observation horizon available enter the
table.

*Window end: 2026-08-15T23:07:05Z*

| Rank | Creator                                        | Qualifying launches | All launches |    Rate | Lowest buys in a required slot | Earliest streak offset | Example token              |
| ---: | ---------------------------------------------- | ------------------: | -----------: | ------: | -----------------------------: | ---------------------: | -------------------------- |
|    1 | `AMRsSeU5JpqwQWJGNLMpZzRCZSFEwYQYbMnms3dD4311` |                   2 |            2 | 100.00% |                              6 |                     +1 | `memecoin` `7NG9CY…Gppump` |
|    2 | `4LXAFVZUUTdcqLCmgHq2NZmgLB2arRxp9wuYVa6rnx5C` |                   1 |            1 | 100.00% |                              6 |                    +52 | `snowball` `9arFCw…MCpump` |
|    3 | `95zf8iXyqzYEGmoHyqGocZVERKzAVBboqhjz7JykWsrZ` |                   1 |            1 | 100.00% |                              6 |                    +64 | `John` `7bHZ8M…wgpump`     |
|    4 | `DfDvnGu7b6eyqqp5D1PNnLkqaBZGDWywKqAcL9U1VEFL` |                   1 |            1 | 100.00% |                              6 |                    +44 | `BREAD` `GJoSrk…ZDpump`    |
|    5 | `8zpBrNGRqUqJfAJwTENj68XrKNzVXh9HWC92zxZFF8Na` |                   1 |            1 | 100.00% |                             10 |                     +6 | `Mayhem` `BbNN7Y…Fppump`   |

[View the SQL on GitHub](https://github.com/web3engineering/on-chain-divers/blob/master/examples/research/reliable_pumpfun_creators.sql).

This is a mechanical activity screen, not an endorsement of a creator or token.


## Pump.fun tokens with unusual early activity

This screen compares Pump.fun launches from the latest 24-hour data window.
For each token it counts distinct buys by `parent_program` during slots
`launch_slot + 1` through `launch_slot + 128`, keeps tokens with at least 48
buys, normalizes each token to a distribution, and averages those distributions
with equal weight per token. Launches without a complete 128-slot horizon are
excluded.

*Window end: 2026-08-15T23:07:05Z · Eligible tokens: 4,110*

### Average parent-program distribution

| Parent program                                 | Average token share |
| ---------------------------------------------- | ------------------: |
| `FLASHX8DrLbgeR8FcfNV1F5krxYcYMUdBkrP1EPBtxB9` |             29.253% |
| `&lt;direct&gt;`                               |             27.475% |
| `MAyhSmzXzV1pTf7LsNkrNwkWKTo4ougAJ1PPg47MD4e`  |             22.216% |
| `GMgnVFR8Jb39LoXsEVzb3DvBy3ywCmdmJquHUy1Lrkqb` |              4.462% |
| `6Vo3245eszAb5wuqEMw8mGdbfRUdKbHhDHP5LcaGuTAB` |              4.298% |
| `term9YPb9mzAsABaqN71A4xdbxHmpBNZavpBiQKZzN3`  |              3.661% |
| `JUP6LkbZbjS1jKKwapdHNy74zcZ3tLUZoi5QNyVTaV4`  |              1.057% |
| `FAdo9NCw1ssek6Z6yeWzWjhLVsr8uiCwcWNUnKgzTnHe` |              0.450% |
| `DF1ow4tspfHX9JwWJsAb9epbkA8hmpSEAtxXy1V27QBH` |              0.336% |
| `proVF4pMXVaYqmy4NjniPh4pqKNfMmsihgd4wdkCX3u`  |              0.325% |
| Other (159 programs)                           |              6.468% |

### Five farthest token profiles

Distance is base-2 Jensen–Shannon distance from the average profile. It is
bounded from 0 (identical) to 1 (disjoint). Ties are ordered by mint so the
selection is reproducible.

| Rank | Token                                                                                               | Symbol / name                     | Buys | Distance | Dominant parent-program mix                                                   |
| ---: | --------------------------------------------------------------------------------------------------- | --------------------------------- | ---: | -------: | ----------------------------------------------------------------------------- |
|    1 | [`GN3y6U…Etpump`](https://gmgn.ai/sol/token/GN3y6UM1xj87L7NxHPaZpkHfmDGYQJs5qncNsQEtpump?chain=sol) | `SPNE` / S\&P 500 Ex-Elon         |  200 |   0.9548 | `WH2Ukv3cZvot8Rc3LKVx5rdhfLvHJKpUuRYWhk9Xn2d`: token 10.50% vs average 0.05%  |
|    2 | [`DiecLw…h8pump`](https://gmgn.ai/sol/token/DiecLw7kWwMiAvnxfHdYtT1uqjkwE1GTtSL4hQh8pump?chain=sol) | `billions` / billions must downlo |  163 |   0.9439 | `WH2Ukv3cZvot8Rc3LKVx5rdhfLvHJKpUuRYWhk9Xn2d`: token 11.66% vs average 0.05%  |
|    3 | [`ACHyfz…Ntpump`](https://gmgn.ai/sol/token/ACHyfzqBKdyfA75C71BF4QgFUaeHcXKurAwNtWNtpump?chain=sol) | `Beatrice` / Elon's Dog           |  362 |   0.9315 | `8nWquKoygoBfpgxMBoV1f6vNBTY91h9vn7ECxJQB9BFc`: token 10.50% vs average 0.04% |
|    4 | [`F1bcyG…y6pump`](https://gmgn.ai/sol/token/F1bcyGXyHECmVWopf7d599nVtdo8PEtQEz8mVPy6pump?chain=sol) | `ANUS` / The Quantum Anus         |  369 |   0.9222 | `8nWquKoygoBfpgxMBoV1f6vNBTY91h9vn7ECxJQB9BFc`: token 10.57% vs average 0.04% |
|    5 | [`9o65tF…fHpump`](https://gmgn.ai/sol/token/9o65tFQoNnLPkeoiuc5CweeVDrcjMzynehm8krfHpump?chain=sol) | `Rocketman` / Elon Musk Metacoin  |   62 |   0.9197 | `8nWquKoygoBfpgxMBoV1f6vNBTY91h9vn7ECxJQB9BFc`: token 12.90% vs average 0.04% |

[View the profile SQL on GitHub](https://github.com/web3engineering/on-chain-divers/blob/master/examples/research/pumpfun_parent_program_profiles.sql) and
[the distance calculation](https://github.com/web3engineering/on-chain-divers/blob/master/examples/research/generate_pumpfun_research.py).

Unusual means statistically different in this one feature window; it does not
by itself imply manipulation, fraud, or future performance.


## Verified Polymarket examples

### Recent on-chain fills

The checked-in query uses the current qualified table name and its partitioning
timestamp. It returns both token and USDC amounts, fees, maker attribution and
transaction context.

```sql
SELECT
    block_timestamp,
    transaction_hash,
    wallet,
    asset,
    side,
    amount_token,
    amount_usdc,
    fee,
    is_maker
FROM polymarket.polymarket_order_filled_v3
PREWHERE block_timestamp >= now() - INTERVAL 1 DAY
ORDER BY block_timestamp DESC, block_number DESC, log_index DESC
LIMIT 100
```

Source: `examples/polymarket/recent_fills.sql`.

### Raw order-book captures

Set the archive directory URL privately:

```dotenv
POLYMARKET_ORDERBOOKS=https://captures.example.com/polymarket/
```

#### 1. Scan the filesystem listing

```sh
python3 examples/orderbooks/polymarket.py scan
```

The scanner accepts local directories and HTTP directory listings. It keeps
only `.log` and `.log.zst` captures from the configured origin and rejects an
oversized listing.

#### 2. Download one bounded capture

Choose a name returned by the scanner:

```sh
python3 examples/orderbooks/polymarket.py download \
  MARKET_CAPTURE.log.zst /tmp/market.log.zst
```

The downloader rejects path traversal and files larger than its configured
64 MiB safety limit.

#### 3. Reconstruct one asset

```sh
python3 examples/orderbooks/polymarket.py reconstruct \
  /tmp/market.log.zst CLOB_TOKEN_ID
```

Each raw line is either JSON or `capture_timestamp<TAB>JSON`. A `book` event
replaces both sides of the selected asset's book. Later `price_change` entries
set a price level to the supplied size, or delete it when the size is zero. The
command prints the final best bid, best ask and level counts.

The docs build performs a real scan of the configured archive and an end-to-end
scan, download, decompression and replay against a deterministic capture. This
tests the complete reconstruction path without making the build depend on the
size of an arbitrary production file.

#### Strict 24-hour cross-venue build check

The Docker `checker` stage also computes `UTC now - 24 hours`, finds the nearest
captured Bitcoin five-minute Up/Down event, and resolves its Up and Down CLOB
token IDs by joining the event and market metadata. It then downloads the
special capture named exactly after that market slug, reads its high-resolution
best-bid/ask series, and independently replays both books to market close. The
same five minutes are reconstructed from HyperLiquid raw files and rendered in
the [cross-venue example](/bitcoin-5m-cross-venue).

The same timestamp-bounded operation is available directly for an already
downloaded capture:

```sh
python3 examples/orderbooks/polymarket.py reconstruct-at \
  /tmp/market.log.zst TARGET_UNIX_MILLISECONDS
```


## Polymarket indexer

The Polymarket database combines decoded Polygon fill events with current event
and market metadata.

| Table                        | Purpose                                                                            |
| ---------------------------- | ---------------------------------------------------------------------------------- |
| `polymarket_order_filled_v3` | Maker/taker fill legs, token and USDC amounts, fees, transaction and block context |
| `raw_event_meta`             | Event titles, slugs, dates, categories and source metadata                         |
| `raw_market_meta`            | Market questions, outcomes, token identifiers, status and resolution metadata      |

Historical CLOB captures are separate from ClickHouse. The order-book example
discovers archive files, downloads one with a size cap, decompresses the raw
timestamp-plus-JSON format, anchors on a `book` snapshot, and applies subsequent
`price_change` levels.

OnchainDivers provides access to high-precision Polymarket order books, including
full snapshots, price-level changes, and explicit best-bid/ask transitions for
individual CLOB outcome tokens. Dedicated files for five-minute Bitcoin Up/Down
markets preserve the rapid probability changes around resolution and can be
joined directly to event and market metadata in ClickHouse.

The chart below is regenerated from those raw Polymarket records and the
matching HyperLiquid BTC futures book during every strict documentation build.
It shows how the Up and Down probabilities evolved alongside the underlying
market, with no hard-coded event or CLOB token IDs.

![High-precision Polymarket and HyperLiquid Bitcoin order books](/examples/bitcoin-5m-updown.png)

[See the complete reproducible cross-venue workflow](/bitcoin-5m-cross-venue).

Credentials and archive locations are supplied through `.env`; no deployment
address is included in this site.

* [Verified examples and order-book replay](/polymarket/examples)
* [Live table reference](/polymarket/tables)


## Polymarket Tables

Tables containing Polymarket prediction market data including order books, trades, and market information.

*Last updated: 2026-08-15 23:08 UTC*

import { ClickHouseSqlExample } from '../../components/ClickHouseSqlExample'

### Tables (3)

| Table                                                        | Rows          | Size      |
| ------------------------------------------------------------ | ------------- | --------- |
| [polymarket\_order\_filled\_v3](#polymarket_order_filled_v3) | 1,820,494,742 | 298.59 GB |
| [raw\_event\_meta](#raw_event_meta)                          | 1,864,030     | 10.22 GB  |
| [raw\_market\_meta](#raw_market_meta)                        | 8,204,805     | 9.18 GB   |

***

#### polymarket\_order\_filled\_v3

| Statistic         | Value                         |
| ----------------- | ----------------------------- |
| **Rows**          | 1,820,494,742                 |
| **Size**          | 298.59 GB                     |
| **First Record**  | 2022-11-21                    |
| **Last Record**   | 2026-08-15                    |
| **Partition Key** | `toYYYYMMDD(block_timestamp)` |

**Columns:**

| Column                     | Type             | Description                                  |
| -------------------------- | ---------------- | -------------------------------------------- |
| `event_id`                 | `String`         | Unique event identifier                      |
| `order_hash`               | `String`         | Hash of the order                            |
| `wallet`                   | `String`         | Wallet address that placed the order         |
| `asset`                    | `String`         | Outcome token (condition ID + outcome index) |
| `amount_token`             | `UInt256`        | Token amount filled                          |
| `amount_usdc`              | `UInt256`        | USDC amount filled                           |
| `is_maker`                 | `Bool`           | Whether this was the maker side              |
| `side`                     | `FixedString(1)` | Order side (B=buy, S=sell)                   |
| `fee`                      | `UInt256`        | Fee amount in USDC                           |
| `block_number`             | `UInt64`         | Polygon block number                         |
| `log_index`                | `UInt32`         | Log index within the transaction             |
| `transaction_index`        | `UInt32`         | Transaction index in block                   |
| `contract_address`         | `String`         | CTF Exchange contract address                |
| `block_hash`               | `String`         | Block hash                                   |
| `block_timestamp`          | `DateTime`       | UTC timestamp of the block                   |
| `gas_used`                 | `UInt64`         | Gas used by transaction                      |
| `gas_limit`                | `UInt64`         | Gas limit set for transaction                |
| `base_fee_per_gas`         | `UInt256`        | Base fee per gas (EIP-1559)                  |
| `transaction_hash`         | `String`         | Transaction hash                             |
| `transaction_from`         | `String`         | Transaction sender                           |
| `transaction_to`           | `String`         | Transaction recipient                        |
| `transaction_value`        | `UInt256`        | ETH value transferred                        |
| `transaction_gas`          | `UInt64`         | Gas provided for transaction                 |
| `transaction_nonce`        | `UInt64`         | Transaction nonce                            |
| `max_fee_per_gas`          | `UInt256`        | Maximum fee per gas (EIP-1559)               |
| `max_priority_fee_per_gas` | `UInt256`        | Maximum priority fee (EIP-1559)              |
| `inserted_at`              | `DateTime`       | Record insertion timestamp                   |
| `builder`                  | `String`         | Default: `''`                                |
| `metadata`                 | `String`         | Default: `''`                                |

**Example Query:**

<ClickHouseSqlExample database="polymarket">
  SELECT \* FROM polymarket.polymarket\_order\_filled\_v3 LIMIT 10
</ClickHouseSqlExample>

***

#### raw\_event\_meta

| Statistic | Value     |
| --------- | --------- |
| **Rows**  | 1,864,030 |
| **Size**  | 10.22 GB  |

**Columns:**

| Column                           | Type                      | Description                                               |
| -------------------------------- | ------------------------- | --------------------------------------------------------- |
| `event_id`                       | `String`                  | Unique event identifier                                   |
| `ticker`                         | `Nullable(String)`        | Event ticker symbol (URL-safe)                            |
| `slug`                           | `Nullable(String)`        | URL-friendly event slug                                   |
| `title`                          | `Nullable(String)`        | Event title                                               |
| `subtitle`                       | `Nullable(String)`        | Event subtitle                                            |
| `description`                    | `Nullable(String)`        | Event description / resolution rules text                 |
| `resolution_source`              | `Nullable(String)`        | Source URL/reference used for event resolution            |
| `start_dttm`                     | `Nullable(DateTime64(3))` | Event start datetime                                      |
| `creation_dttm`                  | `Nullable(DateTime64(3))` | Event creation datetime                                   |
| `end_dttm`                       | `Nullable(DateTime64(3))` | Event end datetime                                        |
| `image`                          | `Nullable(String)`        | Event image URL                                           |
| `icon`                           | `Nullable(String)`        | Event icon URL                                            |
| `is_active`                      | `Nullable(Bool)`          | Whether event is active                                   |
| `is_closed`                      | `Nullable(Bool)`          | Whether event is closed                                   |
| `is_archived`                    | `Nullable(Bool)`          | Whether event is archived                                 |
| `is_new`                         | `Nullable(Bool)`          | Whether event is flagged as new                           |
| `is_featured`                    | `Nullable(Bool)`          | Whether event is featured                                 |
| `is_restricted`                  | `Nullable(Bool)`          | Whether event has access restrictions (e.g., geo-blocked) |
| `liquidity`                      | `Nullable(Float64)`       | Total event liquidity (aggregated across markets)         |
| `volume`                         | `Nullable(Float64)`       | Total event trading volume                                |
| `open_interest`                  | `Nullable(Float64)`       | Open interest for the event                               |
| `sort_by`                        | `Nullable(String)`        | Sort key used for event ordering                          |
| `category`                       | `Nullable(String)`        | Event category                                            |
| `subcategory`                    | `Nullable(String)`        | Event subcategory                                         |
| `is_template`                    | `Nullable(Bool)`          | Whether event is a template                               |
| `template_variables`             | `Nullable(String)`        | Template variables for templated events (JSON)            |
| `published_at`                   | `Nullable(String)`        | Publication timestamp                                     |
| `created_by`                     | `Nullable(String)`        | User ID who created the event                             |
| `updated_by`                     | `Nullable(String)`        | User ID who last updated the event                        |
| `created_dttm`                   | `Nullable(DateTime64(3))` | Event record creation datetime                            |
| `updated_dttm`                   | `Nullable(DateTime64(3))` | Event record last update datetime                         |
| `is_comments_enabled`            | `Nullable(Bool)`          | Whether comments are enabled                              |
| `competitive`                    | `Nullable(Float64)`       | Event competitiveness score                               |
| `volume_24hr`                    | `Nullable(Float64)`       | 24-hour event trading volume                              |
| `volume_1wk`                     | `Nullable(Float64)`       | 7-day event trading volume                                |
| `volume_1mo`                     | `Nullable(Float64)`       | 30-day event trading volume                               |
| `volume_1yr`                     | `Nullable(Float64)`       | 1-year event trading volume                               |
| `featured_image`                 | `Nullable(String)`        | Featured image URL                                        |
| `disqus_thread`                  | `Nullable(String)`        | Disqus comment thread ID                                  |
| `parent_event`                   | `Nullable(String)`        | Parent event identifier (for nested events)               |
| `is_enable_order_book`           | `Nullable(Bool)`          | Whether order book is enabled                             |
| `liquidity_amm`                  | `Nullable(Float64)`       | AMM liquidity for the event                               |
| `liquidity_clob`                 | `Nullable(Float64)`       | CLOB liquidity for the event                              |
| `is_neg_risk`                    | `Nullable(Bool)`          | Whether event uses negative risk markets                  |
| `neg_risk_market_id`             | `Nullable(String)`        | Negative risk market identifier                           |
| `neg_risk_fee_bips`              | `Nullable(Int64)`         | Negative risk fee in basis points                         |
| `comment_count`                  | `Nullable(Int64)`         | Number of comments on the event                           |
| `image_optimized`                | `Nullable(String)`        | Optimized image URL                                       |
| `icon_optimized`                 | `Nullable(String)`        | Optimized icon URL                                        |
| `featured_image_optimized`       | `Nullable(String)`        | Optimized featured image URL                              |
| `sub_events`                     | `Nullable(String)`        | JSON array of sub-events                                  |
| `markets`                        | `Nullable(String)`        | JSON array of markets belonging to this event             |
| `series`                         | `Nullable(String)`        | JSON array of series the event belongs to                 |
| `categories`                     | `Nullable(String)`        | JSON array of categories                                  |
| `collections`                    | `Nullable(String)`        | JSON array of collections                                 |
| `tags`                           | `Nullable(String)`        | JSON array of tags                                        |
| `is_cyom`                        | `Nullable(Bool)`          | Whether event is 'create your own market' (CYOM)          |
| `closed_dttm`                    | `Nullable(DateTime64(3))` | Event closing datetime                                    |
| `is_show_all_outcomes`           | `Nullable(Bool)`          | Whether to show all outcomes in UI                        |
| `is_show_market_images`          | `Nullable(Bool)`          | Whether to show market images in UI                       |
| `is_automatically_resolved`      | `Nullable(Bool)`          | Whether event is auto-resolved                            |
| `is_enable_neg_risk`             | `Nullable(Bool)`          | Whether negative risk is enabled                          |
| `is_automatically_active`        | `Nullable(Bool)`          | Whether event is auto-activated                           |
| `event_date`                     | `Nullable(String)`        | Event date string (display format)                        |
| `start_time`                     | `Nullable(DateTime64(3))` | Event start time                                          |
| `event_week`                     | `Nullable(Int64)`         | Week number of event (for recurring events)               |
| `series_slug`                    | `Nullable(String)`        | Slug of the series the event belongs to                   |
| `score`                          | `Nullable(String)`        | Event score (sports events)                               |
| `elapsed`                        | `Nullable(String)`        | Elapsed time string (sports events)                       |
| `period`                         | `Nullable(String)`        | Period indicator (sports events, e.g., Q1, H2)            |
| `is_live`                        | `Nullable(Bool)`          | Whether event is currently live (sports events)           |
| `is_ended`                       | `Nullable(Bool)`          | Whether event has ended (sports events)                   |
| `finished_timestamp`             | `Nullable(DateTime64(3))` | Timestamp the event finished                              |
| `gmp_chart_mode`                 | `Nullable(String)`        | GMP chart display mode                                    |
| `event_creators`                 | `Nullable(String)`        | Event creators metadata                                   |
| `tweet_count`                    | `Nullable(Int64)`         | Number of associated tweets                               |
| `chats`                          | `Nullable(String)`        | Chat metadata                                             |
| `featured_order`                 | `Nullable(Int64)`         | Display order when featured                               |
| `is_estimate_value`              | `Nullable(Bool)`          | Whether value is estimated                                |
| `is_cant_estimate`               | `Nullable(Bool)`          | Whether value cannot be estimated                         |
| `estimated_value`                | `Nullable(String)`        | Estimated value (free-form string)                        |
| `templates`                      | `Nullable(String)`        | Templates associated with the event                       |
| `spreads_main_line`              | `Nullable(Float64)`       | Main spread line (sports betting)                         |
| `totals_main_line`               | `Nullable(Float64)`       | Main totals line (sports betting)                         |
| `carousel_map`                   | `Nullable(String)`        | Carousel display metadata                                 |
| `is_pending_deployment`          | `Nullable(Bool)`          | Whether event is pending deployment                       |
| `is_deploying`                   | `Nullable(Bool)`          | Whether event is currently deploying                      |
| `deploying_timestamp`            | `Nullable(DateTime64(3))` | Deployment timestamp                                      |
| `scheduled_deployment_timestamp` | `Nullable(DateTime64(3))` | Scheduled deployment time                                 |
| `game_status`                    | `Nullable(String)`        | Game status (sports events)                               |
| `raw_json`                       | `String`                  | Raw JSON response from Polymarket API                     |
| `inserted_at`                    | `DateTime`                | Record insertion timestamp                                |

**Example Query:**

<ClickHouseSqlExample database="polymarket">
  SELECT \* FROM polymarket.raw\_event\_meta LIMIT 10
</ClickHouseSqlExample>

***

#### raw\_market\_meta

| Statistic | Value     |
| --------- | --------- |
| **Rows**  | 8,204,805 |
| **Size**  | 9.18 GB   |

**Columns:**

| Column                           | Type                      | Description                                 |
| -------------------------------- | ------------------------- | ------------------------------------------- |
| `market_id`                      | `String`                  | Unique market identifier                    |
| `question`                       | `Nullable(String)`        | Market question text                        |
| `condition_id`                   | `String`                  | Gnosis conditional token condition ID       |
| `slug`                           | `Nullable(String)`        | URL-friendly market slug                    |
| `twitter_card_image`             | `Nullable(String)`        | Twitter card image URL                      |
| `resolution_source`              | `Nullable(String)`        | Source for market resolution                |
| `market_end_dttm`                | `Nullable(DateTime64(3))` | Market end datetime                         |
| `market_start_dttm`              | `Nullable(DateTime64(3))` | Market start datetime                       |
| `category`                       | `Nullable(String)`        | Market category                             |
| `amm_type`                       | `Nullable(String)`        | AMM type used                               |
| `sponsor_name`                   | `Nullable(String)`        | Market sponsor name                         |
| `sponsor_image`                  | `Nullable(String)`        | Sponsor image URL                           |
| `x_axis_value`                   | `Nullable(String)`        | X-axis label for charts                     |
| `y_axis_value`                   | `Nullable(String)`        | Y-axis label for charts                     |
| `denomination_token`             | `Nullable(String)`        | Token used for denomination                 |
| `fee`                            | `Nullable(Float64)`       | Market fee percentage                       |
| `lower_bound`                    | `Nullable(String)`        | Lower bound for scalar markets              |
| `upper_bound`                    | `Nullable(String)`        | Upper bound for scalar markets              |
| `description`                    | `Nullable(String)`        | Market description                          |
| `outcome`                        | `Nullable(String)`        | Outcome name                                |
| `outcome_price`                  | `Nullable(Float64)`       | Current outcome price (0-1)                 |
| `clob_token_id`                  | `String`                  | CLOB token identifier                       |
| `volume`                         | `Nullable(Float64)`       | Total trading volume                        |
| `volume_num`                     | `Nullable(Float64)`       | Numeric trading volume                      |
| `is_active`                      | `Nullable(Bool)`          | Whether market is active                    |
| `market_type`                    | `Nullable(String)`        | Type of market                              |
| `format_type`                    | `Nullable(String)`        | Display format type                         |
| `lower_bound_dttm`               | `Nullable(String)`        | Lower bound datetime for time-based markets |
| `upper_bound_dttm`               | `Nullable(String)`        | Upper bound datetime for time-based markets |
| `is_closed`                      | `Nullable(Bool)`          | Whether market is closed                    |
| `market_maker_address`           | `Nullable(String)`        | Market maker address                        |
| `created_by`                     | `Nullable(Int64)`         | User ID who created the market              |
| `updated_by`                     | `Nullable(Int64)`         | User ID who last updated the market         |
| `created_dttm`                   | `Nullable(DateTime64(3))` | Market creation datetime                    |
| `updated_dttm`                   | `Nullable(DateTime64(3))` | Market last update datetime                 |
| `closed_dttm`                    | `Nullable(DateTime64(3))` | Market close datetime                       |
| `is_wide_format`                 | `Nullable(Bool)`          | Whether market uses wide display format     |
| `is_new`                         | `Nullable(Bool)`          | Whether market is flagged as new            |
| `mailchimp_tag`                  | `Nullable(String)`        | Mailchimp tag for notifications             |
| `is_featured`                    | `Nullable(Bool)`          | Whether market is featured                  |
| `is_archived`                    | `Nullable(Bool)`          | Whether market is archived                  |
| `resolved_by`                    | `Nullable(String)`        | Resolution source or resolver               |
| `is_restricted`                  | `Nullable(Bool)`          | Whether market has access restrictions      |
| `market_group`                   | `Nullable(Int64)`         | Market group identifier                     |
| `group_item_title`               | `Nullable(String)`        | Title within market group                   |
| `group_item_threshold`           | `Nullable(String)`        | Threshold for group item                    |
| `question_id`                    | `Nullable(String)`        | Question identifier                         |
| `uma_end_dttm`                   | `Nullable(String)`        | UMA oracle end datetime                     |
| `is_enable_order_book`           | `Nullable(Bool)`          | Whether order book is enabled               |
| `order_price_min_tick_size`      | `Nullable(Float64)`       | Minimum price tick size                     |
| `order_min_size`                 | `Nullable(Float64)`       | Minimum order size                          |
| `uma_resolution_status`          | `Nullable(String)`        | UMA resolution status                       |
| `curation_order`                 | `Nullable(Int64)`         | Curation display order                      |
| `liquidity_num`                  | `Nullable(Float64)`       | Numeric liquidity value                     |
| `end_date_iso_dt_str`            | `Nullable(String)`        | ISO format end date string                  |
| `start_date_iso_dt_str`          | `Nullable(String)`        | ISO format start date string                |
| `uma_end_date_iso_dt_str`        | `Nullable(String)`        | UMA end date ISO string                     |
| `is_having_reviewed_dates`       | `Nullable(Bool)`          | Whether dates have been reviewed            |
| `is_ready_for_cron`              | `Nullable(Bool)`          | Whether ready for cron processing           |
| `is_comments_enabled`            | `Nullable(Bool)`          | Whether comments are enabled                |
| `volume_24hr`                    | `Nullable(Float64)`       | 24-hour trading volume                      |
| `volume_1wk`                     | `Nullable(Float64)`       | 7-day trading volume                        |
| `volume_1mo`                     | `Nullable(Float64)`       | 30-day trading volume                       |
| `volume_1yr`                     | `Nullable(Float64)`       | 1-year trading volume                       |
| `game_start_time`                | `Nullable(String)`        | Game/event start time                       |
| `seconds_delay`                  | `Nullable(Int64)`         | Delay in seconds                            |
| `disqus_thread`                  | `Nullable(String)`        | Disqus comment thread ID                    |
| `short_outcome`                  | `Nullable(String)`        | Short outcome label                         |
| `team_a_id`                      | `Nullable(String)`        | Team A identifier (sports)                  |
| `team_b_id`                      | `Nullable(String)`        | Team B identifier (sports)                  |
| `uma_bond`                       | `Nullable(String)`        | UMA oracle bond amount                      |
| `uma_reward`                     | `Nullable(String)`        | UMA oracle reward amount                    |
| `is_fpmm_live`                   | `Nullable(Bool)`          | Whether FPMM is live                        |
| `volume_24hr_amm`                | `Nullable(Float64)`       | 24-hour AMM volume                          |
| `volume_1wk_amm`                 | `Nullable(Float64)`       | 7-day AMM volume                            |
| `volume_1mo_amm`                 | `Nullable(Float64)`       | 30-day AMM volume                           |
| `volume_1yr_amm`                 | `Nullable(Float64)`       | 1-year AMM volume                           |
| `volume_24hr_clob`               | `Nullable(Float64)`       | 24-hour CLOB volume                         |
| `volume_1wk_clob`                | `Nullable(Float64)`       | 7-day CLOB volume                           |
| `volume_1mo_clob`                | `Nullable(Float64)`       | 30-day CLOB volume                          |
| `volume_1yr_clob`                | `Nullable(Float64)`       | 1-year CLOB volume                          |
| `volume_amm`                     | `Nullable(Float64)`       | Total AMM volume                            |
| `volume_clob`                    | `Nullable(Float64)`       | Total CLOB volume                           |
| `liquidity`                      | `Nullable(Float64)`       | Total liquidity                             |
| `liquidity_amm`                  | `Nullable(Float64)`       | AMM liquidity                               |
| `liquidity_clob`                 | `Nullable(Float64)`       | CLOB liquidity                              |
| `maker_base_fee`                 | `Nullable(Int64)`         | Maker base fee                              |
| `taker_base_fee`                 | `Nullable(Int64)`         | Taker base fee                              |
| `custom_liveness`                | `Nullable(Int64)`         | Custom liveness period                      |
| `is_accepting_orders`            | `Nullable(Bool)`          | Whether accepting orders                    |
| `is_notifications_enabled`       | `Nullable(Bool)`          | Whether notifications enabled               |
| `score`                          | `Nullable(Int64)`         | Market score/ranking                        |
| `image_optimized`                | `Nullable(String)`        | Optimized image URL                         |
| `icon_optimized`                 | `Nullable(String)`        | Optimized icon URL                          |
| `event`                          | `Nullable(String)`        | Associated event                            |
| `tag`                            | `Nullable(String)`        | Market tag                                  |
| `category_meta`                  | `Nullable(String)`        | Category metadata                           |
| `creator`                        | `Nullable(String)`        | Creator information                         |
| `is_ready`                       | `Nullable(Bool)`          | Whether market is ready                     |
| `is_funded`                      | `Nullable(Bool)`          | Whether market is funded                    |
| `past_slugs`                     | `Nullable(String)`        | Previous URL slugs                          |
| `ready_timestamp`                | `Nullable(DateTime64(3))` | Ready status timestamp                      |
| `funded_timestamp`               | `Nullable(DateTime64(3))` | Funded status timestamp                     |
| `accepting_orders_timestamp`     | `Nullable(DateTime64(3))` | Orders acceptance timestamp                 |
| `competitive`                    | `Nullable(Float64)`       | Competitiveness score                       |
| `rewards_min_size`               | `Nullable(Float64)`       | Minimum size for rewards                    |
| `rewards_max_spread`             | `Nullable(Float64)`       | Maximum spread for rewards                  |
| `spread`                         | `Nullable(Float64)`       | Current bid-ask spread                      |
| `is_automatically_resolved`      | `Nullable(Bool)`          | Whether auto-resolved                       |
| `one_day_price_change`           | `Nullable(Float64)`       | 24-hour price change                        |
| `one_hour_price_change`          | `Nullable(Float64)`       | 1-hour price change                         |
| `one_week_price_change`          | `Nullable(Float64)`       | 7-day price change                          |
| `one_month_price_change`         | `Nullable(Float64)`       | 30-day price change                         |
| `one_year_price_change`          | `Nullable(Float64)`       | 1-year price change                         |
| `last_trade_price`               | `Nullable(Float64)`       | Last trade price                            |
| `best_bid`                       | `Nullable(Float64)`       | Best bid price                              |
| `best_ask`                       | `Nullable(Float64)`       | Best ask price                              |
| `is_automatically_active`        | `Nullable(Bool)`          | Whether auto-activated                      |
| `is_clear_book_on_start`         | `Nullable(Bool)`          | Whether to clear order book on start        |
| `chart_color`                    | `Nullable(String)`        | Chart display color                         |
| `series_color`                   | `Nullable(String)`        | Series display color                        |
| `is_showing_gmp_series`          | `Nullable(Bool)`          | Whether showing GMP series                  |
| `is_showing_gmp_outcome`         | `Nullable(Bool)`          | Whether showing GMP outcome                 |
| `is_manual_activation`           | `Nullable(Bool)`          | Whether manual activation required          |
| `is_neg_risk_other`              | `Nullable(Bool)`          | Negative risk flag                          |
| `game_id`                        | `Nullable(String)`        | Game identifier                             |
| `group_item_range`               | `Nullable(String)`        | Range for group item                        |
| `sports_market_type`             | `Nullable(String)`        | Sports market type                          |
| `line`                           | `Nullable(Float64)`       | Betting line                                |
| `uma_resolution_statuses`        | `Nullable(String)`        | UMA resolution statuses                     |
| `is_pending_deployment`          | `Nullable(Bool)`          | Whether pending deployment                  |
| `is_deploying`                   | `Nullable(Bool)`          | Whether currently deploying                 |
| `deploying_timestamp`            | `Nullable(DateTime64(3))` | Deployment timestamp                        |
| `scheduled_deployment_timestamp` | `Nullable(DateTime64(3))` | Scheduled deployment time                   |
| `is_rfq_enabled`                 | `Nullable(Bool)`          | Whether RFQ enabled                         |
| `event_start_time`               | `Nullable(DateTime64(3))` | Event start datetime                        |
| `image`                          | `Nullable(String)`        | Market image URL                            |
| `icon`                           | `Nullable(String)`        | Market icon URL                             |
| `raw_json`                       | `String`                  | Raw JSON response from API                  |
| `inserted_at`                    | `DateTime`                | Record insertion timestamp                  |

**Example Query:**

<ClickHouseSqlExample database="polymarket">
  SELECT \* FROM polymarket.raw\_market\_meta LIMIT 10
</ClickHouseSqlExample>

***


## Verified HyperLiquid examples

### Largest recent fills

The query reads the current raw fill table and retains wallet, fee, builder,
TWAP and liquidation context through the underlying row.

```sql
SELECT
    utc_fill_dttm,
    fill_id,
    wallet_address,
    coin,
    side,
    price,
    size,
    price * size AS notional,
    closed_pnl,
    fee,
    liquidation_user
FROM hyperliquid.raw_node_fills_by_block
PREWHERE utc_fill_dt >= today() - 1
ORDER BY notional DESC
LIMIT 100
```

Source: `examples/hyperliquid/largest_recent_fills.sql`.

### Raw order-book archive

The example supports a local directory or a configurable HTTP archive listing:

```dotenv
HYPERLIQUID_ORDER_BOOKS=https://captures.example.com/hyperliquid/
```

#### 1. Scan the raw filesystem

```sh
python3 examples/orderbooks/hyperliquid.py scan
```

The recursive listing includes MessagePack ABCI checkpoints under `abci/` and
compressed hourly JSON streams under `book_diffs/`, `order_statuses/`, and
`fills/`.

#### 2. Download a checkpoint and its hourly diffs

Choose paths returned by the scanner. Nested paths are allowed, but traversal
outside the configured archive is rejected.

```sh
python3 examples/orderbooks/hyperliquid.py download \
  abci/CHECKPOINT.rmp /tmp/checkpoint.rmp
python3 examples/orderbooks/hyperliquid.py download \
  book_diffs/YYYYMMDD/HOUR.zst /tmp/book-diffs.zst
```

The same-origin, traversal and 64 MiB bounds used by the Polymarket downloader
also apply here.

#### 3. Reconstruct directly from the raw formats

```sh
python3 examples/orderbooks/hyperliquid.py reconstruct-raw \
  /tmp/checkpoint.rmp BTC ACTION_ASSET SIZE_DECIMALS \
  /tmp/book-diffs.zst
```

`ACTION_ASSET` and `SIZE_DECIMALS` are the historical numeric asset encoding and
size precision from that checkpoint's metadata. The replay reader seeks directly
to the selected spot or perpetual book inside the MessagePack checkpoint rather
than loading the entire checkpoint into memory. It then streams the zstd hourly
envelopes and applies matching `raw_book_diff` records by order ID. Each diff:

* inserts a new order with its order ID, side, price and size;
* changes the remaining size of an existing order; or
* removes the order ID.

Only after replay are live orders aggregated into price levels. This preserves
correctness for partial fills and cancellation of one order among several at the
same price. The output reports timestamp, best bid, best ask and live order
count.

The strict build executes scan, bounded download and reconstruction against both
a deterministic JSONL recovery sequence and a generated MessagePack checkpoint
plus raw hourly diff stream. It also recursively scans the configured live
HyperLiquid archive, so storage-layout drift fails the docs build.

#### Strict 24-hour cross-venue build check

Inside the Docker `checker` stage, the verifier selects a captured Polymarket
Bitcoin five-minute interval near `UTC now - 24 hours` and reads checkpoint
timestamps from the live archive. It downloads the newest ABCI MessagePack
checkpoint at or before that interval plus every required hourly diff file. For
the BTC perpetual book it then:

1. loads the order-level checkpoint state for action asset `0`;
2. ignores diff blocks already represented by the checkpoint;
3. applies raw diffs across the exact market window;
4. samples bid, ask, midpoint, spread and ten-basis-point depth each second; and
5. requires live orders and a non-crossed `best_bid < best_ask` result.

The raw inputs are held in a temporary directory and removed before the checker
layer is committed. The generated chart continues to the static-site builder.
This is intentionally a large, slow integrity check.

```sh
python3 examples/orderbooks/hyperliquid.py reconstruct-raw-at \
  /tmp/checkpoint.rmp BTC 0 5 TARGET_UTC \
  /tmp/book-diffs-1.zst /tmp/book-diffs-2.zst
```


## HyperLiquid indexer

The HyperLiquid database exposes raw block fills together with derived order and
wallet views.

| Table                     | Purpose                                                                                     |
| ------------------------- | ------------------------------------------------------------------------------------------- |
| `raw_node_fills_by_block` | Fill time, wallet, coin, price, size, side, PnL, fee, TWAP, builder and liquidation context |
| `agg_fulfilled_order`     | Fill-level activity aggregated by fulfilled order                                           |
| `view_perpetual_wallet`   | Current perpetual wallet state                                                              |
| `view_wallet_position`    | Current wallet positions                                                                    |

The raw recovery format begins with an order-level `book_snapshot` and continues
with `book_diff` records. Replaying by order ID is important: a remove or partial
update cannot be reconstructed reliably from aggregate price levels alone.

OnchainDivers provides access to high-precision HyperLiquid order books through
raw MessagePack checkpoints and compressed order-level diff streams. This makes
it possible to reconstruct the historical BTC perpetual book—including best
bid, best ask, spread, live order count, and depth near the midpoint—at precise
times rather than relying on coarse candles or aggregated trades.

The chart below replays that BTC futures book once per second over the exact
window of a Polymarket Bitcoin five-minute market. It is generated and checked
inside Docker from the configured archives, so the visualization also serves as
an executable test of checkpoint selection, diff continuity, and book integrity.

![High-precision HyperLiquid and Polymarket Bitcoin order books](/examples/bitcoin-5m-updown.png)

[See the complete reproducible cross-venue workflow](/bitcoin-5m-cross-venue).

Credentials and optional archive locations are supplied through `.env`; no
deployment address is included in this site.

* [Verified examples and order-book replay](/hyperliquid/examples)
* [Live table reference](/hyperliquid/tables)


## HyperLiquid Tables

Tables containing HyperLiquid perpetual exchange data including trades, orders, and funding rates.

*Last updated: 2026-08-15 23:08 UTC*

import { ClickHouseSqlExample } from '../../components/ClickHouseSqlExample'

### Tables (4)

| Table                                                   | Rows          | Size      |
| ------------------------------------------------------- | ------------- | --------- |
| [agg\_fulfilled\_order](#agg_fulfilled_order)           | 0             | 0 B       |
| [raw\_node\_fills\_by\_block](#raw_node_fills_by_block) | 2,936,348,210 | 210.86 GB |
| [view\_perpetual\_wallet](#view_perpetual_wallet)       | 0             | 0 B       |
| [view\_wallet\_position](#view_wallet_position)         | 0             | 0 B       |

***

#### agg\_fulfilled\_order

| Statistic         | Value                                   |
| ----------------- | --------------------------------------- |
| **Rows**          | 0                                       |
| **Size**          | 0 B                                     |
| **Partition Key** | `toYYYYMM(utc_first_fill_dt)`           |
| **TTL**           | `utc_first_fill_dt + toIntervalDay(90)` |

**Columns:**

| Column                | Type                                                 | Description                                   |
| --------------------- | ---------------------------------------------------- | --------------------------------------------- |
| `utc_first_fill_dttm` | `SimpleAggregateFunction(min, DateTime64(3, 'UTC'))` | UTC datetime of the first fill for this order |
| `utc_first_fill_dt`   | `Date`                                               | UTC date of the first fill (for partitioning) |
| `order_id`            | `Int64`                                              | Unique order identifier                       |
| `wallet_address`      | `String`                                             | Wallet address of the trader                  |
| `coin`                | `String`                                             | Trading pair symbol (e.g., BTC, ETH)          |
| `side`                | `String`                                             | Trade side (Buy/Sell)                         |
| `twap_id`             | `Nullable(Int64)`                                    | TWAP order identifier (if part of TWAP)       |
| `size`                | `SimpleAggregateFunction(sum, Float64)`              | Total filled size (in contracts)              |
| `volume`              | `SimpleAggregateFunction(sum, Float64)`              | Total filled volume in USD                    |
| `closed_pnl`          | `SimpleAggregateFunction(sum, Float64)`              | Realized PnL from this order                  |

**Example Query:**

<ClickHouseSqlExample database="hyperliquid">
  SELECT \* FROM hyperliquid.agg\_fulfilled\_order LIMIT 10
</ClickHouseSqlExample>

***

#### raw\_node\_fills\_by\_block

| Statistic         | Value         |
| ----------------- | ------------- |
| **Rows**          | 2,936,348,210 |
| **Size**          | 210.86 GB     |
| **Partition Key** | `utc_fill_dt` |

**Columns:**

| Column                     | Type                   | Description                                      |
| -------------------------- | ---------------------- | ------------------------------------------------ |
| `utc_fill_dttm`            | `DateTime64(3, 'UTC')` | UTC datetime of the fill (millisecond precision) |
| `utc_fill_dt`              | `Date`                 | UTC date of the fill (for partitioning)          |
| `fill_id`                  | `Int64`                | Unique fill identifier                           |
| `fill_hash`                | `String`               | Hash of the fill                                 |
| `fill_type`                | `String`               | Type of fill (trade, liquidation, etc.)          |
| `utc_block_dttm`           | `DateTime64(9, 'UTC')` | UTC datetime of the block (nanosecond precision) |
| `block_id`                 | `Int64`                | HyperLiquid block number                         |
| `block_tx_idx`             | `UInt16`               | Transaction index within the block               |
| `wallet_address`           | `String`               | Wallet address of the trader                     |
| `coin`                     | `String`               | Trading pair symbol (e.g., BTC, ETH)             |
| `price`                    | `Float64`              | Execution price                                  |
| `size`                     | `Float64`              | Fill size (in contracts)                         |
| `side`                     | `String`               | Trade side (Buy/Sell)                            |
| `start_position`           | `Float64`              | Position size before fill                        |
| `closed_pnl`               | `Float64`              | Realized PnL from this fill                      |
| `order_id`                 | `Int64`                | Order identifier                                 |
| `order_crossed_spread_flg` | `Bool`                 | Whether order crossed the spread                 |
| `fee`                      | `Float64`              | Trading fee                                      |
| `fee_token`                | `String`               | Token used for fee payment                       |
| `client_order_id`          | `Nullable(String)`     | Client-provided order ID                         |
| `builder_fee`              | `Nullable(Float64)`    | Builder/API fee                                  |
| `builder`                  | `Nullable(String)`     | Builder/API identifier                           |
| `liquidation_user`         | `Nullable(String)`     | User being liquidated (if liquidation)           |
| `liquidation_mark_px`      | `Nullable(Float64)`    | Mark price at liquidation                        |
| `liquidation_method`       | `Nullable(String)`     | Liquidation method used                          |
| `twap_id`                  | `Nullable(Int64)`      | TWAP order identifier                            |
| `deployer_fee`             | `Nullable(Float64)`    | Deployer fee for spot tokens                     |
| `priority_gas`             | `Nullable(Float64)`    | Priority gas fee paid for inclusion (in USDC)    |

**Example Query:**

<ClickHouseSqlExample database="hyperliquid">
  SELECT \* FROM hyperliquid.raw\_node\_fills\_by\_block LIMIT 10
</ClickHouseSqlExample>

***

#### view\_perpetual\_wallet

| Statistic | Value |
| --------- | ----- |
| **Rows**  | 0     |
| **Size**  | 0 B   |

**Columns:**

| Column                    | Type                                 | Description                                         |
| ------------------------- | ------------------------------------ | --------------------------------------------------- |
| `wallet_address`          | `String`                             | Wallet address of the trader                        |
| `total_pnl`               | `Float64`                            | Total realized profit and loss across all positions |
| `total_volume`            | `Float64`                            | Total trading volume in USD                         |
| `cnt_unique_orders`       | `UInt64`                             | Count of unique orders placed                       |
| `last_utc_order_dt`       | `SimpleAggregateFunction(max, Date)` | UTC date of the most recent order                   |
| `win_count`               | `UInt64`                             | Number of profitable trades                         |
| `total_count`             | `UInt64`                             | Total number of completed trades                    |
| `sum_order_roi`           | `Float64`                            | Sum of return on investment across all orders       |
| `sum_profitable_pnl`      | `Float64`                            | Sum of PnL from profitable trades                   |
| `cnt_profitable_orders`   | `UInt64`                             | Count of profitable orders                          |
| `sum_unprofitable_pnl`    | `Float64`                            | Sum of PnL from unprofitable trades (negative)      |
| `cnt_unprofitable_orders` | `UInt64`                             | Count of unprofitable orders                        |
| `cnt_unique_coins`        | `UInt64`                             | Number of unique trading pairs traded               |
| `cnt_trade_days`          | `UInt64`                             | Number of distinct days with trading activity       |
| `roi_quantiles`           | `Array(Float64)`                     | ROI distribution quantiles for the wallet           |

**Example Query:**

<ClickHouseSqlExample database="hyperliquid">
  SELECT \* FROM hyperliquid.view\_perpetual\_wallet LIMIT 10
</ClickHouseSqlExample>

***

#### view\_wallet\_position

| Statistic | Value |
| --------- | ----- |
| **Rows**  | 0     |
| **Size**  | 0 B   |

**Columns:**

| Column                      | Type                   | Description                                           |
| --------------------------- | ---------------------- | ----------------------------------------------------- |
| `wallet_address`            | `String`               | Wallet address of the trader                          |
| `coin`                      | `String`               | Trading pair symbol (e.g., BTC, ETH)                  |
| `last_position_size`        | `Float64`              | Current position size (positive=long, negative=short) |
| `last_position_update_dttm` | `DateTime64(3, 'UTC')` | UTC datetime of the last position update              |

**Example Query:**

<ClickHouseSqlExample database="hyperliquid">
  SELECT \* FROM hyperliquid.view\_wallet\_position LIMIT 10
</ClickHouseSqlExample>

***