v1.4.1

What's Changed

• Drop base_uri field for NFT Collections by @0237h in #76
• Implement NFT holders endpoint by @DenisCarriere in #77

Full Changelog: v1.4.0...v1.4.1

v1.4.0

New endpoints

• NFT Collections
• NFT Items
• NFT Activities
• NFT Ownerships
• NFT Sales

What's Changed

• Return empty data array instead of 404 by @0237h in #70
• Refactor OHLCV by contract SQL by @0237h in #72
• Remove unnecessary ORDER BY in transfers SQL by @0237h in #73
• Implement ERC721 NFT endpoints by @0237h in #74

Full Changelog: v1.3.8...v1.4.0

v1.3.8

What's Changed

• Add missing security OpenAPI field by @0237h in #69

Full Changelog: v1.3.7...v1.3.8

v1.3.7

What's Changed

• Fix and optimize queries by @YaroShkvorets in #68
• Change to OrderDirection filter by @DenisCarriere in #66

Full Changelog: v1.3.5...v1.3.6

v1.3.5

What's Changed

• add Transaction ID filter by @DenisCarriere in #63
• Optimize swaps and transfers queries by @YaroShkvorets in #64
• Add order by filter by @DenisCarriere in #65

Full Changelog: v1.3.4...v1.3.5

v1.3.4

What's Changed

Reason: query timeout on both swaps & transfers endpoints

• handle LIMIT + OFFSET directly in SQL files
• add startTime & endTime filters to swaps + transfers
• ❌ BREAKING: endpoint route /transfers/:address => /transfers
  • allow to filter by from or to (previously address applied to both)
  • allows for better filter flexibility
  • improves query compute on exact filter (doing OR filters on multiple fields has performance issues)

by @DenisCarriere in #62

Full Changelog: v1.3.3...v1.3.4

v1.3.3

What's Changed

• Return 500 on database execution timeout by @0237h in #60
• Fixes for swap performance issues by @DenisCarriere in #61
  • Add DESC timestamp sorting to initial swaps SELECT
  • Add age default filter (30 days, max 180 days)

Full Changelog: v1.3.2...v1.3.3

v1.3.2

What's Changed

• Fix/order by pools by @DenisCarriere in #59
  • improve Swaps & Transfers SQL performance

Full Changelog: v1.3.1...v1.3.2

v1.3.1

What's Changed

• remove extra historical in endpoint by @DenisCarriere in #57
• Add Unichain stable tokens by @DenisCarriere in #58

Full Changelog: v1.3.0...v1.3.1

v1.3.0

New endpoints

• Swap Events
• Liquidity Pools
• OHLCV by Pool
• OHLCV by Contract (now includes Uniswap V3 data)
• Historical Balances

What's Changed

• Update to v1.11.0 Substreams by @DenisCarriere in #56

Full Changelog: v1.2.4...v1.3.0

Fixes/Improvements

• Refactored existing routes to match v1.11.0 Substreams
• update contracts uses SimpleAggregateFunction(anyLast, Nullable(String))
• add value field (amount / pow(10, decimals)

New OHLC/Historical endpoints

• /historical/balances/by_address/evm/{address}
• /ohlc/by_pool/evm?pool={pool}&token={token}&factory={factory}

Balances/Transfers endpoints

• /transfers/by_address/evm/{address} (originally /transfers/evm/{address})
• /transfers/by_contract/evm/{contract}

DEX endpoints

• /pools/evm?pool={pool}&protocol={protocol}&token={token},...
• /swaps/evm?token={token}?protocol={protocol},...

SQL swaps

-- Swaps for Uniswap V2 & V3 --
CREATE TABLE IF NOT EXISTS swaps (
   -- block --
   block_num            UInt32,
   block_hash           FixedString(66),
   timestamp            DateTime(0, 'UTC'),

   -- ordering --
   ordinal              UInt64, -- log.ordinal
   `index`              UInt64, -- relative index
   global_sequence      UInt64, -- latest global sequence (block_num << 32 + index)

   -- transaction --
   transaction_id       FixedString(66),

   -- call --
   caller               FixedString(42) COMMENT 'caller address', -- call.caller

   -- swaps --
   pool                 FixedString(42) COMMENT 'pool address', -- log.address
   sender               FixedString(42) COMMENT 'sender address',
   recipient            FixedString(42) COMMENT 'recipient address',
   amount0              Int256 COMMENT 'token0 amount',
   amount1              Int256 COMMENT 'token1 amount',
   price                Float64 COMMENT 'computed price for token0',
   protocol             LowCardinality(String) COMMENT 'protocol name', -- 'uniswap_v2' or 'uniswap_v3'
)
ENGINE = ReplacingMergeTree(global_sequence)
PRIMARY KEY (timestamp, block_num, `index`)
ORDER BY (timestamp, block_num, `index`);

SQL pools

-- Pools Created for Uniswap V2 & V3 --
CREATE TABLE IF NOT EXISTS pools (
   -- block --
   block_num            UInt32,
   block_hash           FixedString(66),
   timestamp            DateTime(0, 'UTC'),

   -- ordering --
   global_sequence      UInt64, -- latest global sequence (block_num << 32 + index)

   -- transaction --
   transaction_id       FixedString(66),

   -- swaps --
   factory              FixedString(42) COMMENT 'factory address', -- log.address
   pool                 FixedString(42) COMMENT 'pool address',
   token0               FixedString(42) COMMENT 'token0 address',
   token1               FixedString(42) COMMENT 'token1 address',
   fee                  UInt32 COMMENT 'pool fee (e.g., 3000 represents 0.30%)',
   protocol             LowCardinality(String) COMMENT 'protocol name', -- 'uniswap_v2' or 'uniswap_v3'
)
ENGINE = ReplacingMergeTree(global_sequence)
PRIMARY KEY (factory, pool)
ORDER BY (factory, pool);

SQL contracts

CREATE TABLE IF NOT EXISTS contracts  (
   -- block --
   block_num            SimpleAggregateFunction(max, UInt32) COMMENT 'block number',
   timestamp            SimpleAggregateFunction(max, DateTime(0, 'UTC')),

   -- ordering --
   global_sequence      UInt64, -- latest global sequence (block_num << 32 + index)

   -- contract --
   address              FixedString(42) COMMENT 'ERC-20 contract address',
   name                 SimpleAggregateFunction(anyLast, Nullable(String)) COMMENT 'ERC-20 contract name (typically 3-8 characters)',
   symbol               SimpleAggregateFunction(anyLast, Nullable(String)) COMMENT 'ERC-20 contract symbol (typically 3-4 characters)',
   decimals             SimpleAggregateFunction(anyLast, Nullable(UInt8)) COMMENT 'ERC-20 contract decimals (18 by default)'
)
ENGINE = AggregatingMergeTree
ORDER BY address;

SQL historical_balances

CREATE TABLE IF NOT EXISTS historical_balances (
   -- block --
   block_num            SimpleAggregateFunction(min, UInt32),
   timestamp            DateTime(0, 'UTC') COMMENT 'the start of the aggregate window',

   -- balance change --
   contract             FixedString(42) COMMENT 'contract address',
   address              FixedString(42) COMMENT 'wallet address',

   -- balance --
   open           AggregateFunction(argMin, UInt256, UInt64),
   high           SimpleAggregateFunction(max, UInt256),
   low            SimpleAggregateFunction(min, UInt256),
   close          AggregateFunction(argMax, UInt256, UInt64),
   uaw            AggregateFunction(uniq, FixedString(42)) COMMENT 'unique wallet addresses that changed balance in the window',
   transactions   AggregateFunction(sum, UInt8) COMMENT 'number of transactions that changed balance in the window'
)
ENGINE = AggregatingMergeTree
PRIMARY KEY (address, contract, timestamp)
ORDER BY (address, contract, timestamp);

SQL ohlc_prices

-- OHLC prices including Uniswap V2 & V3 --
CREATE TABLE IF NOT EXISTS ohlc_prices (
   -- block --
   block_num            SimpleAggregateFunction(min, UInt32),
   timestamp            DateTime(0, 'UTC') COMMENT 'beginning of the bar',

   -- pool --
   pool                 LowCardinality(FixedString(42)) COMMENT 'pool address',

   -- swaps --
   open0                AggregateFunction(argMin, Float64, UInt64),
   high0                AggregateFunction(quantileDeterministic, Float64, UInt64),
   low0                 AggregateFunction(quantileDeterministic, Float64, UInt64),
   close0               AggregateFunction(argMax, Float64, UInt64),

   -- volume --

   -- “Gross” or “volume” signals a total quantity traded with no regard to direction. --
   gross_volume0        SimpleAggregateFunction(sum, UInt256) COMMENT 'gross volume of token0 in the window',
   gross_volume1        SimpleAggregateFunction(sum, UInt256) COMMENT 'gross volume of token1 in the window',

   -- “Net” plus “flow” tells you it’s a directional figure that can be positive or negative. --
   net_flow0            SimpleAggregateFunction(sum, Int256) COMMENT 'net flow of token0 in the window',
   net_flow1            SimpleAggregateFunction(sum, Int256) COMMENT 'net flow of token1 in the window',

   -- universal --
   uaw                  AggregateFunction(uniq, FixedString(42)) COMMENT 'unique wallet addresses in the window',
   transactions         SimpleAggregateFunction(sum, UInt64) COMMENT 'number of transactions in the window'
)
ENGINE = AggregatingMergeTree
PRIMARY KEY (pool, timestamp)
ORDER BY (pool, timestamp);