pinax-network/substreams-clock-api

Timestamps <> Block numbers conversion for your favorite chains

Substreams Clock API

Block & Timestamps API

REST API

Pathname	Description
GET /chains	Available chains
GET /block	Get block by block_number, block_id or timestamp
GET /trace_calls	Get aggregate of trace_calls for given time range filtered by chain
GET /transaction_traces	Get aggregate of transaction_traces for given time range filtered by chain
GET /uaw	Get daily unique active wallets for given time range filtered by chain
GET /health	Health check
GET /metrics	Prometheus metrics
GET /openapi	OpenAPI v3 JSON

Important note regarding timestamp query parameter

Expects UTC datetime or UNIX-like timestamp for matching the data in the Clickhouse DB. Passing timestamp data with additional timezone information (such as ...T...Z or ±hh) will likely fail the query to match (unless it corresponds to UTC0).

Requirements

• ClickHouse
• Substreams Sink ClickHouse

Quickstart

$ bun install
$ bun dev

Bun Binary Releases

Linux Only

$ wget https://github.com/pinax-network/substreams-clock-api/releases/download/v0.2.0/substreams-clock-api
$ chmod +x ./substreams-clock-api

.env Environment variables

# API Server
PORT=8080
HOSTNAME=localhost

# Clickhouse Database
HOST=http://127.0.0.1:8123
DATABASE=default
USERNAME=default
PASSWORD=
TABLE=blocks
MAX_LIMIT=500

# Logging
VERBOSE=true

Help

$ ./substreams-clock-api -h
Usage: substreams-clock-api [options]

Block & Timestamps API

Options:
  -V, --version            output the version number
  -p, --port <number>      HTTP port on which to attach the API (default: "8080", env: PORT)
  -v, --verbose <boolean>  Enable verbose logging (choices: "true", "false", default: false, env: VERBOSE)
  --hostname <string>      Server listen on HTTP hostname (default: "localhost", env: HOSTNAME)
  --host <string>          Database HTTP hostname (default: "http://localhost:8123", env: HOST)
  --username <string>      Database user (default: "default", env: USERNAME)
  --password <string>      Password associated with the specified username (default: "", env: PASSWORD)
  --database <string>      The database to use inside ClickHouse (default: "default", env: DATABASE)
  --table <string>         Clickhouse table name (default: "blocks", env: TABLE)
  --max-limit <number>     Maximum LIMIT queries (default: 500, env: MAX_LIMIT)
  -h, --help               display help for command

Docker environment

Pull from GitHub Container registry

docker pull ghcr.io/pinax-network/substreams-clock-api:latest

Build from source

docker build -t substreams-clock-api .

Run with .env file

docker run -it --rm --env-file .env ghcr.io/pinax-network/substreams-clock-api

substreams-clock-api's Issues

Add CORS support

Code examples

fetch/OPTIONS.ts

import { CORS_HEADERS } from "./cors.js";

export default async function (req: Request) {
    return new Response('Departed', {headers: CORS_HEADERS});
}

fetch/cors.ts

import { BunFile } from "bun";

export const NotFound = toText('Bad Request', 404);
export const InternalServerError = toText("Internal Server Error", 500);

export const CORS_HEADERS = new Headers({
    "Access-Control-Allow-Origin": "*",
    "Access-Control-Allow-Methods": "GET, POST, OPTIONS",
    "Access-Control-Allow-Headers": "Content-Type, WWW-Authenticate",
});
export const JSON_HEADERS = new Headers({"Content-Type": "application/json"});
export const TEXT_HEADERS = new Headers({"Content-Type": "text/plain"});

export function appendHeaders(...args: Headers[]) {
    const headers = new Headers(CORS_HEADERS); // CORS as default headers
    for (const arg of args) {
        for (const [key, value] of arg.entries()) {
            headers.set(key, value);
        }
    }
    return headers;
};

export function toJSON(body: any, status = 200, headers = new Headers()) {
    const data = typeof body == "string" ? body : JSON.stringify(body, null, 2);
    return new Response(data, { status, headers: appendHeaders(JSON_HEADERS, headers) });
}

export function toText(body: string, status = 200, headers = new Headers()) {
    return new Response(body, { status, headers: appendHeaders(TEXT_HEADERS, headers) });
}

export function toFile(body: BunFile, status = 200, headers = new Headers()) {
    const fileHeaders = new Headers({"Content-Type": body.type});
    return new Response(body, { status, headers: appendHeaders(fileHeaders, headers) });
}

Support for array params `timestamp[]` or `block_number[]`

Provide an array of timestamp[] or block_number[]

That way you can query multiple block ranges within a single query

Support fetching block number results for partial (non-matching) timestamp

Given a partial date or datetime, the API could return a block number following the selection of a predefined behavior (all names subject to change):

• match: exact match, current behavior
• closest: block number from the block with the closest associated timestamp
• first_in_day: block number from the first block of the day of the given date

This selection would be passed as an additional (optional) query parameter search (name subject to change) to the /blocknum route (e.g. /blocknum?timestamp=...&search=...). The default value would be match to retain the current default behavior of searching for the exact match in the DB.

Examples

Assuming DB contains these rows:

┌──────────────┬─────────────────────┐
│ block_number │      timestamp      │
├──────────────┼─────────────────────┤
│     18379040 │ 2023-10-18T18:53:59 │
│     18378930 │ 2023-10-18T01:10:22 │
│     18378820 │ 2023-10-17T23:59:47 │
│     18378710 │ 2023-10-17T00:12:31 │
└──────────────┴─────────────────────┘

Query	Type	Match	Result
2023-10-18	match	-	[]
2023-10-18	closest	2023-10-17T23:59:47	18378820
2023-10-18	first_in_day	2023-10-18T01:10:22	18378930
2023-10-17 12:00,2023-10-18	closest	[2023-10-17T00:12:31, 2023-10-17T23:59:47]	[18378710, 18378820]
2023-10-17,2023-10-18	first_in_day	[2023-10-17T00:12:31, 2023-10-18T01:10:22]	[18378710, 18378930]

Add Docker setup

• Dockerfile (ex: https://github.com/pinax-network/substreams-sink-websockets/blob/main/Dockerfile)
• GitHub actions to deploy to Docker Container registry (ex: https://github.com/pinax-network/substreams-sink-websockets/blob/main/.github/workflows/gchr.yml)
• Update README with Docker install instructions (Ex: https://github.com/pinax-network/substreams-sink-websockets#docker-environment)

Use Subtivity Substreams as data source

Goal

• Extend substreams-clock-api with Subtivity substreams data source
  https://github.com/pinax-network/substreams-clock-api
  https://github.com/pinax-network/subtivity-substreams
• Offer all the same kind of "aggregates" endpoints we have from Atomic API
• Compute is DAW (Daily Active Wallets) which is the total unique count of uaw (addresses) within a rolling 24 hour period

More

• use Subtivity as source https://github.com/pinax-network/subtivity-substreams
• Include schema.sql from Subtivity
• provide new GET endpoints to include:
  • /trace_calls
    • aggregate functions
  • /uaw
    • daily aggregate
  • /transaction_traces
    • aggregate functions

Health check `/health`

Health checks /health (returns 200 "OK" if everything is good, can be something as simple as ping'ing Clickhouse database to see if it exists)

Update `package.json` version on new releases automatically

In order to maintain consistency with Github releases, the package.json version tag should also be increased and set the same as the latest published version.

Needs a GH Action to automatically bump that number (depending on tag) on release. It shouldn't need a PR to push changes so might need to tweak repo settings to allow this.

Add Bun binary build

• Add GitHub actions for generating Bun binary executables (ex: https://github.com/pinax-network/substreams-sink-websockets/blob/main/.github/workflows/bun.yml)
  • rename files => to API filename

extra `/block` queries params

• Timestamp parsing ("2021-10-19" => UTC Milliseconds)
  • Using native NodeJS new Date()
  • Always default to Z UTC Timezone if not specified
• lessOrEquals, greaterOrEquals, less & greater

  https://clickhouse.com/docs/en/sql-reference/operators#lessorequals-function

  • block number
  • timestamp (ex: less_or_equals_by_timestamp)
• Modulo block number (ex: search by every 1M blocks)

  https://clickhouse.com/docs/en/sql-reference/functions/arithmetic-functions#modulo

OpenAPI for Hono

• support for OpenAPI
• generate OpenAPI Swagger documentation

https://github.com/honojs/middleware/tree/main/packages/zod-openapi

Would be nice if there would be support for Typebox, since we've been using that library, or does Typebox work with Zod?

Prometheus Metrics `/metrics`

Prometheus Metrics /metrics (stored in memory using prom-client, we can track usage with these metrics)

Swagger docs example not using the selected value

It seems that the generated Swagger docs still uses the hardcoded examples set in the code when making interactive queries. Those are defined in the code like the following:

chain: z.enum(supportedChains).openapi({ example: 'EOS' })

The image below shows that the value selected for the chain parameter is not updated in the curl command of the test query.

Zod date parsing for `timestamp` parameter does not work properly when timezone information is missing

Using the z.coerce.date() format for the timestamp query parameter can give unexpected parsed results when the timezone information is missing from the query string.

This results in missing the data originally queried from the DB as the parsed timestamp is no longer the same. The timestamp in the DB follows the format of ISO dates without nanoseconds (e.g. 2023-10-18T16:02:35).

Examples

Query	Parsed	Problem
2023-10-18T16:02:35	2023-10-18T20:02:35.000Z	GMT hours shift
2023-10-18 16:02:35 GMT	2023-10-18T16:02:35.000Z	Nanos still present
2023-10-18T16:02:35Z	2023-10-18T16:02:35.000Z	Nanos still present

Typebox validator for queries

Typebox validator for queries

Reference

https://github.com/honojs/middleware/tree/main/packages/typebox-validator

Add logging capabilities

Log useful debug information such as:

• Query parameters values (after parsing)
• JSON response

Clickhouse MaterialView for daily buckets `SummingMergeTree`

https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/summingmergetree