QuestDB Skill

How to Use This Skill

IMPORTANT — MINIMIZE ROUND-TRIPS:

Do NOT explore library source code (cryptofeed, questdb, etc.)
Do NOT check library versions or verify callback signatures
Do NOT read installed package files to "understand the API"
Do NOT verify infrastructure (Docker containers, Grafana health) is running — trust the user's prompt
Do NOT start
```
02_ingest.py
```
separately —
```
03_dashboard.py
```
launches it and verifies data automatically
Do NOT read extra reference files for topics already covered in this skill file
DO read reference files when their topic applies (e.g. enterprise.md for auth, grafana-advanced.md for complex panels)
Do NOT use task tracking (TaskCreate/TaskUpdate) for straightforward builds
Do NOT add
```
sleep
```
commands to wait for data or check background processes (the deploy script handles this)
Do NOT Ctrl+C, restart, or re-launch the ingestion process once
```
03_dashboard.py
```
has started it
Do NOT put VWAP, Bollinger, or RSI in separate timeseries panels — they are refIDs on the OHLC candlestick panel
Do NOT omit or empty
```
fieldConfig.overrides
```
— they put RSI on a right Y-axis (0-100%) and spread on a right axis. Without them, different scales crush the chart flat.
Do NOT set dashboard refresh to
```
"5s"
```
— the default is
```
"250ms"
```
When opening the dashboard URL in the browser, ALWAYS append
```
?refresh=250ms
```
to the URL. Without this, Grafana ignores the JSON refresh setting.
All API details for cryptofeed, QuestDB ingestion, and Grafana are below — use them as-is
Known Python environment issues are already solved in the templates below: uvloop: When creating a NEW venv, pip may pull a uvloop version that crashes on Python 3.10+ (
```
RuntimeError: no current event loop
```
). Uninstall it after pip install in fresh venvs only. Never uninstall uvloop from a user's existing venv — their setup already works. macOS SSL: Homebrew Python lacks system CA certificates. Any outbound HTTPS/WSS connection (exchange WebSockets, API calls) fails without certifi. Always set
```
SSL_CERT_FILE
```
via certifi at the top of scripts that make network connections. These fixes are baked into the pip commands and Python templates below — copy them exactly.

This skill contains ready-to-use SQL, schemas, ingestion code, and Grafana queries. Write the files and run them. A typical pipeline is 3 files (schema setup, ingestion script, dashboard deploy) — write them, execute them, done.

Execution Scenarios

Pick the scenario that matches the user's request. Run the exact commands shown. Each bash code block below is one Bash tool call. Execute immediately - do not deliberate over shell syntax. Blocks at the same step can run as parallel tool calls.

Scenario A — Everything from scratch (Docker + venv + pipeline): Use separate parallel Bash tool calls (one command per call) to parallelize:

bash

docker run -d --name questdb -p 9000:9000 -p 9009:9009 -p 8812:8812 questdb/questdb:latest

bash

docker run -d --name grafana -p 3000:3000 -e GF_INSTALL_PLUGINS=questdb-questdb-datasource -e GF_SECURITY_ADMIN_PASSWORD=admin -e GF_DASHBOARDS_MIN_REFRESH_INTERVAL=250ms grafana/grafana:latest

bash

python3 -m venv .venv

Then sequentially:

bash

.venv/bin/pip install -q cryptofeed questdb 'psycopg[binary]' requests numpy certifi

bash

.venv/bin/pip uninstall uvloop -y 2>/dev/null

Then wait for both services (each in its own Bash call):

bash

curl -sf --retry 30 --retry-delay 1 --retry-all-errors -o /dev/null "http://localhost:9000/exec?query=SELECT+1"

bash

curl -sf --retry 30 --retry-delay 1 --retry-all-errors -o /dev/null http://localhost:3000/api/health

Then configure the datasource:

bash

curl -s -X POST http://localhost:3000/api/datasources -u admin:admin -H "Content-Type: application/json" -d '{"name":"QuestDB","type":"questdb-questdb-datasource","access":"proxy","jsonData":{"server":"host.docker.internal","port":8812,"username":"admin","tlsMode":"disable","timeout":"120","queryTimeout":"60"},"secureJsonData":{"password":"quest"}}'

Datasource fields (QuestDB Grafana plugin uses jsonData, NOT the standard url field):

```
server
```
: hostname only, no port, no protocol (e.g.
```
host.docker.internal
```
)
```
port
```
: integer, separate from server (e.g.
```
8812
```
)
```
tlsMode
```
: must be
```
"disable"
```
for local Docker — omitting it defaults to TLS enabled, which breaks the connection
```
username
```
/
```
password
```
: QuestDB defaults are
```
admin
```
/
```
quest
```
Then write 3 files (schema, ingestion, dashboard) and run them.

Scenario B — Containers running, need venv:

bash

python3 -m venv .venv

bash

.venv/bin/pip install -q cryptofeed questdb 'psycopg[binary]' requests numpy certifi

bash

.venv/bin/pip uninstall uvloop -y 2>/dev/null

Then write 3 files and run them.

Scenario C — User provides existing venv path: Trust the user's venv as-is. Do NOT run pip install or uninstall anything. Write 3 files and run them. Use the user's venv path for all

python

commands.

Scenario D — Everything already running, just need pipeline scripts: Write 3 files and run them. No infrastructure setup needed.

Additional references in the

references/

directory — only read when the user's request goes beyond what this file covers:

```
common-mistakes.md
```
— Wrong patterns → correct QuestDB equivalents (read when writing novel SQL not already templated below)
```
grafana-advanced.md
```
— Read only for Plotly order book depth charts or advanced features not in the dashboard template below
```
indicators.md
```
— Read when user asks for indicators beyond OHLC/VWAP/Bollinger/RSI (MACD, ATR, Stochastic, OBV, Drawdown, Keltner, Donchian, etc.)
```
cookbook.md
```
— Index of official QuestDB cookbook recipes: finance (slippage, markout, IS, ECN scorecard, last-look, indicators, volume/order-flow, risk) and time-series patterns (FILL strategies, session windows, latest-N-per-partition, sparse sensor joins). Read when the user asks for an execution-quality/TCA metric, a named indicator, or a time-series pattern you don't already have inline.
```
sql-grammar.md
```
— Complete index of QuestDB keywords, functions (by category with signatures), data types, and operators. Read when you need to know "does QuestDB have function X?" or want the correct signature for a function. Includes native finance builtins (
```
vwap()
```
,
```
twap()
```
,
```
spread()
```
,
```
mid()
```
,
```
wmid()
```
,
```
l2price()
```
), statistical functions, array/matrix operations, and more.
```
enterprise.md
```
— Read when QuestDB uses authentication, HTTPS, tokens, or ACLs (skip for open source)

Critical Rule

QuestDB is NOT PostgreSQL. It supports the PostgreSQL wire protocol for querying, but has its own SQL extensions for time-series operations. Standard PostgreSQL patterns like

time_bucket()

DISTINCT ON

HAVING

, and

generate_series()

do not exist.

Live Documentation Access

Every QuestDB docs page has a plain-markdown twin at the same URL with

.md

appended. Fetch these with

curl

for fast, clean, LLM-friendly reads — no HTML to strip, no scraping required. Prefer an authoritative doc fetch over guessing when you need a keyword, syntax detail, or edge case not covered inline here.

bash

# Any doc page — just append .md to the URL
curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/horizon-join.md"
curl -sH "Accept: text/markdown" "https://questdb.com/docs/cookbook/sql/finance.md"

# Full documentation index for LLMs — lists every page with its .md URL
curl -s "https://questdb.com/docs/llms.txt"

Use

llms.txt

to discover a page when you don't know the exact path. It is the authoritative directory of every doc and cookbook recipe.

SQL Reference

CREATE TABLE

sql

CREATE TABLE IF NOT EXISTS trades (
    ts TIMESTAMP,
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    amount DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);

Key rules:

```
TIMESTAMP(col)
```
designates the time column — required for SAMPLE BY, LATEST ON, ASOF JOIN
```
SYMBOL
```
type for any repeated string (tickers, categories, status codes) — much faster than VARCHAR
```
PARTITION BY DAY|MONTH|YEAR|HOUR
```
— use the most common query granularity
```
WAL
```
enables concurrent writes (required for ILP ingestion)
```
DEDUP UPSERT KEYS(ts, symbol)
```
deduplicates on (timestamp, symbol) — idempotent ingestion

Column types: BOOLEAN, BYTE, SHORT, INT, LONG, FLOAT, DOUBLE, CHAR, VARCHAR, SYMBOL, TIMESTAMP, DATE, LONG256, GEOHASH, UUID, IPv4, DOUBLE[], FLOAT[], INT[], LONG[], SHORT[], UUID[] — plus 2D arrays like

DOUBLE[][]

ALTER TABLE:

sql

ALTER TABLE trades ADD COLUMN exchange SYMBOL;
ALTER TABLE trades DROP COLUMN exchange;
ALTER TABLE trades RENAME COLUMN amount TO qty;
ALTER TABLE trades ALTER COLUMN exchange SET TYPE SYMBOL;

SAMPLE BY (Time-Series Aggregation)

SAMPLE BY is QuestDB's time-bucketing. It replaces

GROUP BY time_bucket()

. Requires a designated timestamp.

sql

SELECT ts, symbol,
    first(price) AS open, max(price) AS high,
    min(price) AS low, last(price) AS close,
    sum(amount) AS volume
FROM trades
WHERE ts > '2025-01-01'
SAMPLE BY 1h;

Key rules:

Valid intervals:
```
1s
```
,
```
5s
```
,
```
1m
```
,
```
15m
```
,
```
1h
```
,
```
1d
```
,
```
1M
```
(month)
```
ALIGN TO CALENDAR
```
aligns buckets to clock boundaries (this is the default, so it can be omitted)
```
FILL(PREV | NULL | LINEAR | value)
```
fills gaps — goes AFTER SAMPLE BY
In Grafana, default to
```
SAMPLE BY 5s
```
unless the user specifies a different bar size
```
first()
```
,
```
last()
```
return first/last values within each time bucket
```
count_distinct(col)
```
instead of
```
COUNT(DISTINCT col)
```

HAVING

— use a subquery:

SELECT * FROM (... SAMPLE BY ...) WHERE volume > 1000

LATEST ON (Last Value Per Group)

Returns the most recent row per group. Replaces

DISTINCT ON

ROW_NUMBER()

sql

SELECT * FROM trades
WHERE ts > dateadd('h', -1, now())
LATEST ON ts PARTITION BY symbol;

Rules:

```
LATEST ON ts
```
must reference the designated timestamp
```
PARTITION BY
```
is required
Add a
```
WHERE
```
time filter for performance

Joins

QuestDB has a family of specialised time-series joins. Pick the one that matches the question — reaching for ASOF JOIN by default when HORIZON or WINDOW would be idiomatic is a common LLM mistake.

Pattern	Use
Point-in-time match: "value at trade time"	ASOF JOIN (or HORIZON JOIN with `LIST (0)` )
Match strictly before trade time	LT JOIN
Interleave two time series chronologically	SPLICE JOIN
Price at trade time AND at offsets ±N (markout, IS decomposition, adverse selection)	HORIZON JOIN
Aggregate quotes within a ±N window around each trade (rolling stats, quote density, pre-/post-trade averages)	WINDOW JOIN
Per-outer-row subquery: top-N per group, dynamic filters, per-row SAMPLE BY / LATEST ON / ASOF	LATERAL JOIN

sql

-- ASOF JOIN: nearest right row with ts ≤ left.ts
SELECT * FROM trades ASOF JOIN quotes ON (symbol);

-- LT JOIN: strictly ts < left.ts
SELECT * FROM trades LT JOIN quotes ON (symbol);

-- SPLICE JOIN: merge two time series interleaved
SELECT * FROM trades SPLICE JOIN quotes ON (symbol);

-- HORIZON JOIN: ASOF at a grid of time offsets, one parallel pass.
-- For each trade, computes metrics at -1m, -30s, 0, 30s, 1m, ...
-- Supports multiple right-hand tables (only the last carries RANGE/LIST/AS).
SELECT h.offset / 1_000_000_000 AS horizon_sec, t.symbol,
       avg((m.best_bid + m.best_ask) / 2) AS avg_mid
FROM fx_trades t
HORIZON JOIN market_data m ON (symbol)
RANGE FROM -1m TO 5m STEP 30s AS h
WHERE t.timestamp IN '$yesterday'
ORDER BY t.symbol, horizon_sec;

-- Or with explicit offsets for implementation shortfall:
--   LIST (0, 1800s) AS h  →  at_fill and at_30m columns (via PIVOT)

-- WINDOW JOIN: aggregate right-table rows within a ±time window around each left row.
-- EXCLUDE PREVAILING omits the last-known value before the window start.
SELECT t.symbol, t.timestamp,
       avg(c.bid_price) AS avg_bid_pm5s,
       count()          AS num_quotes
FROM fx_trades t
WINDOW JOIN core_price c ON (t.symbol = c.symbol)
    RANGE BETWEEN 5 seconds PRECEDING AND 5 seconds FOLLOWING
    EXCLUDE PREVAILING
WHERE t.symbol = 'EURUSD' AND t.timestamp IN '$now-1h..$now';

-- LATERAL JOIN: subquery evaluated per outer row, can reference outer columns.
-- Classic top-N per group — two largest fills for each order:
SELECT o.id, o.desk, t.qty
FROM orders o
JOIN LATERAL (
    SELECT qty FROM fills
    WHERE order_id = o.id
    ORDER BY qty DESC LIMIT 2
) t
ORDER BY o.id, t.qty DESC;

Join rules:

All time-series joins (ASOF, LT, SPLICE, HORIZON, WINDOW) require a designated timestamp on both tables. Timestamps are matched automatically.
```
ON (symbol)
```
matches the key column; both tables must use the same column name. Use
```
ON (t.sym = q.sym)
```
when the names differ.
Standard INNER JOIN and LEFT JOIN also work for non-time-series joins.
HORIZON JOIN: no other join types in the same level, no SAMPLE BY, no window functions inside, WHERE filters left table only. Wrap in a subquery for anything else.
WINDOW JOIN: cannot be combined with GROUP BY, window functions, or other join types. Wrap the join in a CTE then aggregate/join in the outer query.
LATERAL JOIN: only INNER, LEFT, or CROSS variants supported. Requires a parenthesised subquery. Inside the subquery you can reference outer columns and use SAMPLE BY, LATEST ON, ASOF JOIN, window functions, UNION ALL, etc.

For the full syntax of HORIZON / WINDOW / LATERAL (including mixed-precision timestamps, dynamic window bounds, and multi-table HORIZON patterns), fetch:

curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/horizon-join.md"

curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/window-join.md"

curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/lateral-join.md"

For ready-made execution analytics queries (slippage, markout, implementation shortfall, last-look, ECN scorecard) — all built on HORIZON JOIN — see

references/cookbook.md

or https://questdb.com/docs/cookbook/sql/finance.md.

UNNEST (Arrays and JSON Arrays to Rows)

QuestDB supports

UNNEST

for expanding arrays - or JSON arrays stored as VARCHAR - into rows. Appears in the

FROM

clause and behaves like a table.

sql

-- Native array: expand the sizes at level 2 of each order book row
SELECT t.symbol, u.vol
FROM market_data t, UNNEST(t.asks[2]) u(vol)
WHERE t.timestamp IN '$now-1m..$now' AND t.symbol = 'EURUSD';

-- WITH ORDINALITY gives a 1-based level index (resets per input row)
SELECT m.symbol, u.vol, u.level
FROM market_data m, UNNEST(m.asks[2]) WITH ORDINALITY u(vol, level)
WHERE m.timestamp IN '$now-1m..$now';

-- JSON array (VARCHAR) with typed columns — great for ingesting API payloads
SELECT u.trade_id, u.price, u.size, u.side, u.time
FROM UNNEST(
    payload_varchar::VARCHAR
    COLUMNS(trade_id LONG, price DOUBLE, size DOUBLE, side VARCHAR, time TIMESTAMP)
) u;

Key rules:

```
UNNEST
```
must be in
```
FROM
```
(or after
```
CROSS JOIN
```
/ comma), not in
```
SELECT
```
Native arrays: currently
```
DOUBLE[]
```
only.
```
UNNEST
```
of a
```
DOUBLE[][]
```
yields
```
DOUBLE[]
```
rows - chain a second
```
UNNEST
```
to fully flatten.
JSON:
```
COLUMNS(name TYPE, ...)
```
extracts typed fields. Supported types: BOOLEAN, SHORT, INT, LONG, DOUBLE, VARCHAR, TIMESTAMP. Invalid JSON / NULL / empty string all produce 0 rows (no error).
```
ordinality
```
is reserved — alias it (
```
u(val, pos)
```
) or quote it.

Full reference:

curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/sql/unnest.md"

Window Functions

sql

-- ROWS frame (count-based)
AVG(close) OVER (ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sma20

-- CUMULATIVE shorthand (QuestDB extension, equivalent to ROWS UNBOUNDED PRECEDING)
SUM(volume) OVER (ORDER BY ts CUMULATIVE) AS running_total

-- PARTITION BY
LAG(close) OVER (PARTITION BY symbol ORDER BY ts) AS prev_close

-- EMA (exponential moving average — QuestDB extension)
avg(price, 'period', 14) OVER (ORDER BY ts) AS ema14

Supported:

ROW_NUMBER()

RANK()

DENSE_RANK()

LAG()

LEAD()

FIRST_VALUE()

AVG()

SUM()

MIN()

MAX()

COUNT()

Important:

stddev_samp()

may not work in window frames. For standard deviation, compute manually:

sqrt(avg(x*x) - avg(x)^2)

— see Bollinger Bands query below.

Materialized Views

Auto-updated, incremental aggregations triggered by new data:

sql

CREATE MATERIALIZED VIEW IF NOT EXISTS candles_5s AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades SAMPLE BY 5s
) PARTITION BY DAY;

Rules:

Source query MUST use SAMPLE BY
Only aggregation functions allowed (no WHERE, no JOIN, no window functions)
Use
```
IF NOT EXISTS
```
to avoid errors on re-run
Cascade views for multi-resolution:
```
trades → 5s → 1m → 1h
```
(see Schema Design below)

Invalidate/rebuild:

ALTER MATERIALIZED VIEW candles_5s INVALIDATE

Views (non-materialized)

QuestDB also supports regular views - virtual tables defined by a SELECT statement, evaluated on every query. Use them for reusable query abstractions that don't need incremental precomputation.

sql

CREATE VIEW IF NOT EXISTS latest_prices AS (
    SELECT * FROM trades
    LATEST ON ts PARTITION BY symbol
);

-- Query it like a table
SELECT * FROM latest_prices WHERE symbol = 'BTC-USDT';

-- Modify or drop
ALTER VIEW latest_prices AS (SELECT ...);   -- redefine
COMPILE VIEW latest_prices;                  -- recompile after schema changes
DROP VIEW latest_prices;

Use a materialized view when you need incremental SAMPLE BY rollups. Use a regular view for everything else (latest-value lookups, filtered subsets, join abstractions).

Timestamp Filtering — TICK Syntax (Preferred)

Always prefer TICK over
dateadd()
/
BETWEEN
for time filters. TICK (Temporal Interval Calendar Kit) is QuestDB's declarative syntax for time intervals. It is more readable, generates optimized interval scans, handles timezones and business days, and expresses complex multi-interval patterns that would require UNION ALL or application-side logic otherwise.

Syntax order:

date [T time] @timezone #dayFilter ;duration

sql

-- Date variables: resolve at query time
WHERE ts IN '$today'                     -- full day (midnight to midnight)
WHERE ts IN '$yesterday'                 -- previous full day
WHERE ts IN '$tomorrow'                  -- next full day

-- Ranges with $now (point-in-time, microsecond precision)
WHERE ts IN '$now - 1h..$now'            -- last hour
WHERE ts IN '$now - 30m..$now'           -- last 30 minutes
WHERE ts IN '$now - 5bd..$now'           -- last 5 business days (skips weekends)

-- Duration suffix: extends forward from a point
WHERE ts IN '$now;1h'                    -- 1 hour starting now (forward)
WHERE ts IN '2025-01-15T09:30;6h30m'     -- NYSE trading session

-- Imprecise dates: month-level and year-level (expand to all days)
WHERE ts IN '[2025-01]'                  -- all of January (no need for [01..31])
WHERE ts IN '[2025]T09:30'              -- all of 2025 at 09:30
WHERE ts IN '2025-02T09:30'             -- bare imprecise (brackets optional alone)

-- Bracket expansion: generates multiple intervals
WHERE ts IN '2025-01-[10..15]'           -- days 10 through 15
WHERE ts IN '2025-01-[5,10..12,20]'      -- specific days + ranges
WHERE ts IN '2025-[01,06]-[10,15]'       -- Cartesian: Jan+Jun x 10th+15th
WHERE ts IN '2025-[01..03]T09:30'        -- month-level range: all days Jan-Mar

-- Day-of-week filters
WHERE ts IN '[2025-01]#workday'          -- weekdays in January
WHERE ts IN '[2025-01]#weekend'          -- weekends in January
WHERE ts IN '[2025-01]#Mon,Wed,Fri'      -- specific days in January

-- Timezone-aware (handles DST)
WHERE ts IN '2025-01-15T09:30@America/New_York;6h30m'

-- Combined: workdays at 09:30 New York time for all of January
WHERE ts IN '[2025-01]T09:30@America/New_York#workday;6h30m'

-- Mixed-precision date lists
WHERE ts IN '[2025-01-15, 2025-02]T09:30'  -- one day + full month

-- Time lists: multiple intraday windows
WHERE ts IN '2025-01-15T[09:00,14:30];1h'  -- two 1h windows on the same day

-- Date lists (brackets required for lists)
WHERE ts IN '[$today, $yesterday, 2025-01-15]'

-- ISO week dates
WHERE ts IN '2025-W01-[1..5]T09:00;8h'  -- Mon-Fri of week 1

Key rules:

Date variables are case-insensitive (
```
$TODAY
```
=
```
$today
```
)
```
$today
```
/
```
$yesterday
```
/
```
$tomorrow
```
produce full-day intervals;
```
$now
```
produces a point-in-time (add range or duration to make it useful)
Arithmetic units:
```
y
```
```
M
```
```
w
```
```
d
```
```
bd
```
```
h
```
```
m
```
```
s
```
```
T
```
(ms)
```
u
```
(us)
```
n
```
(ns).
```
bd
```
(business days) is valid in arithmetic only, not in durations. Case-sensitive:
```
M
```
= months,
```
m
```
= minutes,
```
T
```
= milliseconds.
Imprecise dates:
```
YYYY-MM
```
expands to all days in that month,
```
YYYY
```
to all days in that year.
```
'[2025-01]T09:30#workday;6h30m'
```
replaces the verbose
```
'2025-01-[01..31]T09:30#workday;6h30m'
```
. Brackets optional when used alone (
```
'2025-02T09:30'
```
=
```
'[2025-02]T09:30'
```
), required in lists.
Brackets required for: lists (
```
[$today, $yesterday]
```
), ranges with suffixes (
```
[$now - 2h..$now]@America/New_York
```
). Optional for standalone variables, bare ranges (
```
$now - 2h..$now
```
), and bare imprecise dates (
```
2025-01T09:30
```
).
Overlapping intervals from bracket expansion are auto-merged.
Exchange calendars (Enterprise): use
```
#XNYS
```
,
```
#XLON
```
, etc. (ISO 10383 MIC codes) instead of
```
#workday
```
to filter by real exchange trading schedules - holidays, early closes, and lunch breaks are handled automatically.
```
WHERE ts IN '[2025-01]#XNYS'
```
gives only NYSE trading sessions. See:
```
curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/operators/exchange-calendars.md"
```

Full reference:

curl -sH "Accept: text/markdown" "https://questdb.com/docs/query/operators/tick.md"

DECLARE (Variables)

sql

DECLARE @start := '2025-01-01T00:00:00Z', @end := now();
SELECT * FROM trades WHERE ts BETWEEN @start AND @end;

SQL Execution Order

FROM → WHERE → SAMPLE BY → SELECT → LIMIT

(No GROUP BY needed with SAMPLE BY. No HAVING at all.)

Key Functions

Timestamp:

now()

systimestamp()

dateadd('unit', n, ts)

datediff('unit', ts1, ts2)

to_timestamp('str', 'fmt')

timestamp_floor('unit', ts)

timestamp_ceil('unit', ts)

hour(ts)

day_of_week(ts)

week_of_year(ts)

year(ts)

month(ts)

String:

concat('a', 'b')

left(s, n)

right(s, n)

length(s)

starts_with(s, prefix)

lcase(s)

ucase(s)

replace(s, old, new)

split_part(s, delim, idx)

regexp_replace(s, pattern, replacement)

Aggregation:

first(x)

last(x)

count_distinct(x)

sum(x)

avg(x)

min(x)

max(x)

haversine_dist_deg(lat1, lon1, lat2, lon2)

Array:

array_cum_sum(arr)

— cumulative sum of array elements

Data Ingestion

ILP (InfluxDB Line Protocol) — Primary Method

Use ILP for all high-throughput ingestion. Never use INSERT INTO for streaming data.

python

from questdb.ingress import Sender, TimestampNanos
import numpy as np

# Open Source (no auth). For Enterprise: read references/enterprise.md Quick Start
# (admin creates service account + token via REST → ingestion script uses token)
conf = "tcp::addr=localhost:9009;protocol_version=2;"

with Sender.from_conf(conf) as sender:
    sender.row(
        'trades',
        symbols={'symbol': 'BTC-USDT', 'side': 'buy'},
        columns={'price': 42000.50, 'amount': 1.5},
        at=TimestampNanos.now()
    )
    sender.flush()

Key patterns:

```
Sender.from_conf()
```
does NOT connect — use
```
with
```
context manager or call
```
sender.establish()
```
```
symbols={}
```
for SYMBOL columns,
```
columns={}
```
for everything else
Arrays MUST be
```
np.float64
```
numpy arrays, not Python lists
TCP config requires
```
protocol_version=2
```
for array support
HTTP transport:
```
http::addr=localhost:9000;
```
TCP transport:
```
tcp::addr=localhost:9009;
```

2D array ingestion (order books):

python

sender.row(
    'order_book',
    symbols={'symbol': 'BTC-USDT'},
    columns={
        'bids': [
            np.array([41999.0, 41998.5, 41998.0], dtype=np.float64),  # prices [1]
            np.array([2.1, 5.3, 10.0], dtype=np.float64),            # sizes  [2]
        ],
        'asks': [
            np.array([42001.0, 42001.5, 42002.0], dtype=np.float64),
            np.array([1.8, 4.2, 8.5], dtype=np.float64),
        ],
    },
    at=TimestampNanos.now()
)

Other client libraries: Go, Java, Rust, Node.js, C/C++, .NET — fetch

ingestion/clients/{language}

INSERT INTO — For Low Volume Only

sql

INSERT INTO trades (ts, symbol, side, price, amount)
VALUES ('2025-02-09T10:00:00.000000Z', 'BTC-USDT', 'buy', 42000.50, 1.5);

Querying via PG Wire

python

import psycopg as pg
conn = pg.connect("user=admin password=quest host=localhost port=8812 dbname=qdb")

HTTP REST API

Query:

GET http://localhost:9000/exec?query=URL_ENCODED_SQL

URL-encode ALL special characters including parentheses:
```
(
```
=
```
%28
```
,
```
)
```
=
```
%29
```
. Unencoded
```
()
```
in curl commands triggers a security prompt. Example:
```
count_distinct%28symbol%29
```
not
```
count_distinct(symbol)
```
Enterprise: see Quick Start in
```
references/enterprise.md
```
POST is not supported for the exec endpoint - use GET only
Returns JSON:
```
{ "columns": [...], "dataset": [...] }
```

Schema Design

Key principles:

Every time-series table needs a designated timestamp
Use SYMBOL for any repeated string (tickers, categories, status codes)
Partition by the most common query granularity
WAL tables for concurrent write workloads
DEDUP for idempotent ingestion

Financial Market Data Pipeline Schema (Ready to Use)

sql

CREATE TABLE IF NOT EXISTS trades (
    ts TIMESTAMP,
    symbol SYMBOL,
    side SYMBOL,
    price DOUBLE,
    amount DOUBLE
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);

CREATE TABLE IF NOT EXISTS orderbook (
    ts TIMESTAMP,
    symbol SYMBOL,
    bid_prices DOUBLE[],
    bid_sizes DOUBLE[],
    ask_prices DOUBLE[],
    ask_sizes DOUBLE[]
) TIMESTAMP(ts) PARTITION BY DAY WAL
DEDUP UPSERT KEYS(ts, symbol);

-- Cascading materialized views: trades → 5s → 1m → 1h
CREATE MATERIALIZED VIEW IF NOT EXISTS candles_5s AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades SAMPLE BY 5s
) PARTITION BY DAY;

CREATE MATERIALIZED VIEW IF NOT EXISTS candles_1m AS (
    SELECT ts, symbol,
        first(open) AS open, max(high) AS high,
        min(low) AS low, last(close) AS close,
        sum(volume) AS volume
    FROM candles_5s SAMPLE BY 1m
) PARTITION BY DAY;

CREATE MATERIALIZED VIEW IF NOT EXISTS candles_1h AS (
    SELECT ts, symbol,
        first(open) AS open, max(high) AS high,
        min(low) AS low, last(close) AS close,
        sum(volume) AS volume
    FROM candles_1m SAMPLE BY 1h
) PARTITION BY MONTH;

The Grafana queries below work with these exact table/view names.

Demo & Sample Data

Infrastructure setup commands are in the Execution Scenarios section above. Pick the scenario that matches, run the exact commands, then proceed here.

Real-Time Crypto Feed (cryptofeed + QuestDB)

Do NOT explore cryptofeed source code or check its version. Everything you need is right here. Copy this ingestion script verbatim. The certifi/SSL fix and all imports are required — do not omit any lines:

python

import os
import certifi
os.environ['SSL_CERT_FILE'] = certifi.where()  # Required: macOS lacks system CA certs for HTTPS/WSS

import numpy as np
from cryptofeed import FeedHandler
from cryptofeed.exchanges import OKX
from cryptofeed.defines import TRADES, L2_BOOK
from questdb.ingress import Sender, TimestampNanos

conf = "tcp::addr=localhost:9009;protocol_version=2;"  # Enterprise: see references/enterprise.md

async def trade_cb(t, receipt_timestamp):
    with Sender.from_conf(conf) as sender:
        sender.row(
            'trades',
            symbols={'symbol': t.symbol, 'side': t.side},
            columns={'price': float(t.price), 'amount': float(t.amount)},
            at=TimestampNanos(int(t.timestamp * 1e9))
        )
        sender.flush()

async def book_cb(book, receipt_timestamp):
    bids = book.book['bid']
    asks = book.book['ask']
    # Sort: bids descending by price, asks ascending
    bid_prices = sorted(bids.keys(), reverse=True)[:25]
    ask_prices = sorted(asks.keys())[:25]
    with Sender.from_conf(conf) as sender:
        sender.row(
            'orderbook',
            symbols={'symbol': book.symbol},
            columns={
                'bid_prices': np.array([float(p) for p in bid_prices], dtype=np.float64),
                'bid_sizes':  np.array([float(bids[p]) for p in bid_prices], dtype=np.float64),
                'ask_prices': np.array([float(p) for p in ask_prices], dtype=np.float64),
                'ask_sizes':  np.array([float(asks[p]) for p in ask_prices], dtype=np.float64),
            },
            at=TimestampNanos(int(book.timestamp * 1e9))
        )
        sender.flush()

SYMBOLS = ['BTC-USDT', 'ETH-USDT', 'SOL-USDT']  # add more as needed

f = FeedHandler()
f.add_feed(OKX(
    symbols=SYMBOLS,
    channels=[TRADES, L2_BOOK],
    callbacks={TRADES: trade_cb, L2_BOOK: book_cb}
))
f.run()

cryptofeed API reference (complete — tested with v2.4.x — do NOT read source code to verify):

t.symbol

t.side

(

'buy'

'sell'

t.price

t.amount

t.timestamp

(float epoch seconds)

t.price
and
t.amount
are
Decimal
types — cast with
float()
before passing to QuestDB Sender

book.book['bid']

and

book.book['ask']

are dicts:

{Decimal(price): Decimal(size), ...}

```
book.symbol
```
,
```
book.timestamp
```
(float epoch seconds)
Exchanges:
```
OKX
```
,
```
Coinbase
```
,
```
Binance
```
,
```
Kraken
```
,
```
Bybit
```
, etc.

Channels:

TRADES

L2_BOOK

L3_BOOK

TICKER

CANDLES

OPEN_INTEREST

FUNDING

LIQUIDATIONS

Symbol format is exchange-native:
```
'BTC-USDT'
```
for OKX,
```
'BTC-USD'
```
for Coinbase
Python compatibility: avoid
```
X | None
```
type hints (requires 3.10+). Use
```
Optional[X]
```
or plain assignment.

Dependencies (fresh venv):

pip install cryptofeed questdb 'psycopg[binary]' requests numpy certifi

then

pip uninstall uvloop -y

(separate Bash calls)

macOS SSL: Always set
```
SSL_CERT_FILE
```
via certifi before any outbound HTTPS/WSS connections (Homebrew Python lacks system CA certs). Set it INSIDE Python code (
```
os.environ['SSL_CERT_FILE'] = certifi.where()
```
), NEVER as a shell-level
```
SSL_CERT_FILE=$(...)
```
prefix - that triggers a security prompt

Performance note: The example above opens a Sender per callback for clarity. For production, use a shared Sender with periodic flush:

python

sender = Sender.from_conf(conf)
sender.establish()

async def trade_cb(t, receipt_timestamp):
    sender.row('trades', symbols={...}, columns={...}, at=...)
    # flush periodically or use auto_flush_interval in conf string

Execution order (mandatory — do not reorder):

Run
```
01_schema.py
```
— create tables and views
Run
```
03_dashboard.py
```
— this script starts ingestion automatically, waits for data, then deploys the dashboard and opens the browser. Do not start
```
02_ingest.py
```
separately.

Do not Ctrl+C, restart, or otherwise touch the ingestion process once it is started.
Definition of done: Dashboard is open in browser AND ingestion process is still running.

Operational notes:

cryptofeed logs to stderr, not stdout. An empty stdout does not mean failure.

End the dashboard deploy script with

open

(macOS) or

xdg-open

(Linux) to launch the browser automatically. Include
?refresh=250ms
in the URL so the dashboard opens with the correct refresh rate:

python

url = f"{GRAFANA_URL}{resp.json()['url']}?refresh=250ms&from=now-5m&to=now"
subprocess.run(["open" if sys.platform == "darwin" else "xdg-open", url])

QuestDB Demo Instance

QuestDB's live demo at

demo.questdb.io

has FX and crypto datasets. Fetch the schema reference:

curl -sH "Accept: text/markdown" "https://questdb.com/docs/cookbook/demo-data-schema/"

Grafana Integration

QuestDB has a dedicated Grafana datasource plugin (

questdb-questdb-datasource

). Connects via PG wire on port 8812.

Datasource API config (the QuestDB plugin uses jsonData, NOT the standard url field):

```
jsonData.server
```
: hostname only — no port, no protocol (e.g.
```
host.docker.internal
```
)
```
jsonData.port
```
: integer, separate from server (e.g.
```
8812
```
)
```
jsonData.tlsMode
```
:
```
"disable"
```
for local Docker — omitting defaults to TLS enabled, which breaks

jsonData.username

secureJsonData.password

: QuestDB defaults

admin

quest

Do NOT use the
```
url
```
field — the QuestDB plugin ignores it

The dashboard deploy template below includes create-or-find datasource logic. Use it.

Key macros:

```
$__timeFilter(ts)
```
— time range from Grafana's time picker
Default SAMPLE BY interval:
```
5s
```
. Only change if the user specifies a different bar size.

Symbol dropdown variable:

SELECT DISTINCT symbol FROM trades

For advanced Grafana patterns (multi-query panels, axis overrides, repeating panels, order book depth charts), see

references/grafana-advanced.md

Ready-to-Use Grafana Queries

Complete, tested SQL for common financial panels. Use directly.

Grafana query pattern rule: Grafana needs a

time

column, so the final SELECT aliases

ts AS time

. Never put OVER() clauses in the same SELECT that aliases
ts AS time
— put all window functions in CTEs where

ts

is still

ts

, then alias only in the final SELECT. All queries below follow this pattern.

OHLC Candlestick (from raw trades):

sql

SELECT ts AS time,
    first(price) AS open, max(price) AS high,
    min(price) AS low, last(price) AS close,
    sum(amount) AS volume
FROM trades
WHERE $__timeFilter(ts) AND symbol = '$symbol'
SAMPLE BY 5s;

OHLC Candlestick (from materialized view — faster for longer ranges):

sql

SELECT ts AS time,
    first(open) AS open, max(high) AS high,
    min(low) AS low, last(close) AS close,
    sum(volume) AS volume
FROM candles_1m
WHERE $__timeFilter(ts) AND symbol = '$symbol'
SAMPLE BY 5m;

VWAP (cumulative volume-weighted average price):

sql

WITH ohlc AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE $__timeFilter(ts) AND symbol = '$symbol'
    SAMPLE BY 5s
),
vwap AS (
    SELECT ts, close,
        sum((high + low + close) / 3 * volume) OVER (ORDER BY ts CUMULATIVE)
        / sum(volume) OVER (ORDER BY ts CUMULATIVE) AS vwap
    FROM ohlc
)
SELECT ts AS time, close, vwap FROM vwap;

Bollinger Bands (20-period SMA ± 2σ): Uses manual variance — more compatible than

stddev_samp

in window frames:

sql

WITH ohlc AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE $__timeFilter(ts) AND symbol = '$symbol'
    SAMPLE BY 5s
),
stats AS (
    SELECT ts, close,
        AVG(close) OVER (
            ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS sma20,
        AVG(close * close) OVER (
            ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS avg_close_sq
    FROM ohlc
)
SELECT ts AS time, close, sma20,
    sma20 + 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS upper_band,
    sma20 - 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS lower_band
FROM stats;

RSI (14-period, SMA-smoothed): This Grafana version uses SMA via ROWS BETWEEN, proven in production. For standalone EMA-smoothed RSI, see

references/indicators.md

sql

WITH ohlc AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE $__timeFilter(ts) AND symbol = '$symbol'
    SAMPLE BY 5s
),
changes AS (
    SELECT ts, close,
        close - LAG(close) OVER (ORDER BY ts) AS change
    FROM ohlc
),
gains_losses AS (
    SELECT ts, close,
        CASE WHEN change > 0 THEN change ELSE 0 END AS gain,
        CASE WHEN change < 0 THEN ABS(change) ELSE 0 END AS loss
    FROM changes
),
avg_gl AS (
    SELECT ts, close,
        AVG(gain) OVER (ORDER BY ts
            ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_gain,
        AVG(loss) OVER (ORDER BY ts
            ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_loss
    FROM gains_losses
)
SELECT ts AS time,
    CASE WHEN avg_loss = 0 THEN 100
         ELSE 100 - (100 / (1 + avg_gain / NULLIF(avg_loss, 0)))
    END AS rsi
FROM avg_gl;

Note: For EMA-smoothed RSI (standard), replace the AVG...ROWS BETWEEN with

avg(gain, 'period', 14) OVER (ORDER BY ts)

(QuestDB native EMA). For Wilder's smoothing (α=1/N), use

avg(gain, 'period', 27)

Combined VWAP + Bollinger (single panel, multiple series):

sql

WITH ohlc AS (
    SELECT ts, symbol,
        first(price) AS open, max(price) AS high,
        min(price) AS low, last(price) AS close,
        sum(amount) AS volume
    FROM trades
    WHERE $__timeFilter(ts) AND symbol = '$symbol'
    SAMPLE BY 5s
),
indicators AS (
    SELECT ts, close,
        AVG(close) OVER (
            ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS sma20,
        AVG(close * close) OVER (
            ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS avg_close_sq,
        sum((high + low + close) / 3 * volume) OVER (ORDER BY ts CUMULATIVE)
        / sum(volume) OVER (ORDER BY ts CUMULATIVE) AS vwap
    FROM ohlc
)
SELECT ts AS time, close, sma20,
    sma20 + 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS upper_band,
    sma20 - 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS lower_band,
    vwap
FROM indicators;

Dashboard Deployment via API

Complete working deployment script. This dashboard JSON is tested and working — copy the structure exactly for all panels. Do not split or reorganize panels.

Panel layout rule: VWAP, Bollinger Bands, and RSI are ALWAYS overlaid on an OHLC candlestick panel as additional refIDs (B, C, D) with

includeAllFields: true

. The panel type MUST be

candlestick

, not

timeseries

. Creating separate timeseries panels for these indicators is wrong — they go on the candlestick panel. Query pattern: refId A returns OHLC columns (open, high, low, close, volume). refIds B, C, D each return

ts AS time

+ their indicator column(s) ONLY — not OHLC. Grafana overlays them using the shared time column. If an indicator query returns OHLC columns too, the candlestick rendering breaks.

Overrides are mandatory, not cosmetic. RSI (0-100) on the same Y-axis as price (~97,000) makes candlesticks invisible. Spread (~0.01) on the same axis as bid/ask (~97,000) makes the spread line invisible. Copy the

fieldConfig.overrides

arrays from the template exactly — especially RSI's right axis (0-100%) and spread's right axis.

Dashboard defaults (copy exactly):

```
"refresh": "250ms"
```
— NOT
```
"5s"
```
. The 250ms refresh is intentional for real-time data.
```
"liveNow": false
```
```
"time": {"from": "now-5m", "to": "now"}
```

"timepicker": {"refresh_intervals": ["250ms", "500ms", "1s", "5s", "10s", "30s", "1m", "5m", "15m", "30m", "1h", "2h", "1d"]}

Open URL with
```
?refresh=250ms&from=now-5m&to=now
```
appended

CRITICAL: Grafana's
min_refresh_interval
defaults to
5s
. Sub-second refresh intervals (250ms, 500ms, 1s) are blocked server-side unless you set

GF_DASHBOARDS_MIN_REFRESH_INTERVAL=250ms

when starting Grafana. This is already set in the Docker run command in the Execution Scenarios above. Without it, the dashboard JSON

"refresh": "250ms"

and URL

?refresh=250ms

are silently ignored, and the dropdown won't show sub-5s options.

Target structure for every panel query:

json

{
    "refId": "A",
    "datasource": {"uid": "QUESTDB_UID", "type": "questdb-questdb-datasource"},
    "format": 1,
    "rawSql": "SELECT ts AS time, ... WHERE $__timeFilter(ts) ..."
}

CRITICAL:

"format"

MUST be integer

, not string

"table"

. The QuestDB Grafana plugin uses a Go integer enum (

sqlutil.FormatQueryOption

). String values cause

json: cannot unmarshal string into Go struct field Query.format

. Grafana's JSON export shows

"table"

(string) but the API POST requires

(integer).

python

import json, subprocess, sys, os, requests, time

QUESTDB_URL = "http://localhost:9000"
GRAFANA_URL = "http://localhost:3000"
GRAFANA_AUTH = ("admin", "admin")
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))

# --- Start ingestion if not already running ---
already_running = False
try:
    resp = requests.get(f"{QUESTDB_URL}/exec",
        params={"query": "SELECT count() FROM trades WHERE ts > dateadd('s', -10, now())"})
    already_running = resp.json()["dataset"][0][0] > 0
except Exception:
    pass

if already_running:
    print("Ingestion already running — skipping launch")
else:
    ingest_log = open(os.path.join(SCRIPT_DIR, "ingest.log"), "w")
    ingest_proc = subprocess.Popen(
        [sys.executable, "-u", os.path.join(SCRIPT_DIR, "02_ingest.py")],
        stdout=ingest_log, stderr=ingest_log,
        start_new_session=True,  # detach from parent — survives Ctrl+C and script exit
    )
    print(f"Ingestion started: PID {ingest_proc.pid}")

    # --- Gate: wait for data before deploying dashboard ---
    for i in range(15):  # up to 15s
        time.sleep(1)
        if ingest_proc.poll() is not None:
            ingest_log.close()
            print("ERROR: Ingestion process died. Log:")
            print(open(os.path.join(SCRIPT_DIR, "ingest.log")).read())
            sys.exit(1)
        try:
            resp = requests.get(f"{QUESTDB_URL}/exec", params={"query": "SELECT count() FROM trades"})
            count = resp.json()["dataset"][0][0]
            if count > 0:
                print(f"Data gate passed: {count} rows in trades (waited {i+1}s)")
                break
        except Exception:
            pass
    else:
        print("ERROR: No data after 15s. Check ingest.log")
        sys.exit(1)

# --- Create or find QuestDB datasource ---
# QuestDB plugin uses jsonData fields, NOT the standard url field.
# server = hostname only (no port, no protocol), port = integer, tlsMode = "disable"
ds_list = requests.get(f"{GRAFANA_URL}/api/datasources", auth=GRAFANA_AUTH).json()
existing = [d for d in ds_list if d["type"] == "questdb-questdb-datasource"]
if existing:
    questdb_uid = existing[0]["uid"]
else:
    resp = requests.post(f"{GRAFANA_URL}/api/datasources", auth=GRAFANA_AUTH,
        json={
            "name": "QuestDB", "type": "questdb-questdb-datasource", "access": "proxy",
            "jsonData": {
                "server": "host.docker.internal",
                "port": 8812,
                "username": "admin",
                "tlsMode": "disable",
                "timeout": "120",
                "queryTimeout": "60",
            },
            "secureJsonData": {"password": "quest"},
        })
    questdb_uid = resp.json()["datasource"]["uid"]

DS_REF = {"uid": questdb_uid, "type": "questdb-questdb-datasource"}

dashboard = {
    "dashboard": {
        "title": "Crypto Real-Time Market Data",
        "uid": "crypto-realtime",
        "timezone": "browser",
        "refresh": "250ms",  # Default refresh rate — do NOT change to 5s
        "liveNow": False,
        "schemaVersion": 38,
        "time": {"from": "now-5m", "to": "now"},
        "timepicker": {
            "refresh_intervals": ["250ms", "500ms", "1s", "5s", "10s", "30s", "1m", "5m", "15m", "30m", "1h", "2h", "1d"],
        },
        "tags": ["crypto", "questdb", "realtime"],
        "templating": {"list": [{
            "name": "symbol", "type": "query", "label": "Symbol",
            "query": "SELECT DISTINCT symbol FROM trades ORDER BY symbol;",
            "datasource": DS_REF,
            "refresh": 1, "sort": 1,
            "current": {"text": "BTC-USDT", "value": "BTC-USDT"},
        }]},
        "panels": [
            {
                "id": 1, "type": "candlestick",
                "title": "OHLC - $symbol",
                "gridPos": {"h": 10, "w": 24, "x": 0, "y": 0},
                "datasource": DS_REF,
                "fieldConfig": {
                    "defaults": {"custom": {"axisBorderShow": False, "axisPlacement": "auto"}},
                    "overrides": [{"matcher": {"id": "byName", "options": "volume"},
                                   "properties": [{"id": "custom.axisPlacement", "value": "hidden"}]}],
                },
                "options": {
                    "mode": "candles+volume", "includeAllFields": False,
                    "candleStyle": "candles", "colorStrategy": "open-close",
                    "colors": {"up": "green", "down": "red"},
                    "fields": {"open": "open", "high": "high", "low": "low",
                               "close": "close", "volume": "volume"},
                },
                "targets": [{
                    "refId": "A", "datasource": DS_REF, "format": 1,
                    "rawSql": "SELECT ts AS time, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s;",
                }],
            },
            {
                "id": 2, "type": "candlestick",
                "title": "OHLC + Indicators - $symbol",
                "gridPos": {"h": 10, "w": 24, "x": 0, "y": 10},
                "datasource": DS_REF,
                "fieldConfig": {
                    "defaults": {"custom": {"axisBorderShow": False, "axisPlacement": "auto"}},
                    # CRITICAL: these overrides are NOT cosmetic — without them:
                    # - RSI (0-100) shares Y-axis with price (~97000) → candlesticks appear flat
                    # - Volume distorts the price axis
                    # - Bollinger bands have no visual distinction from price lines
                    # Copy ALL overrides below exactly. NEVER use "overrides": []
                    "overrides": [
                        {"matcher": {"id": "byName", "options": "volume"},
                         "properties": [{"id": "custom.axisPlacement", "value": "hidden"}]},
                        {"matcher": {"id": "byName", "options": "vwap"},
                         "properties": [{"id": "color", "value": {"fixedColor": "orange", "mode": "fixed"}},
                                        {"id": "custom.lineWidth", "value": 2}]},
                        {"matcher": {"id": "byName", "options": "sma20"},
                         "properties": [{"id": "color", "value": {"fixedColor": "yellow", "mode": "fixed"}},
                                        {"id": "custom.lineWidth", "value": 2}]},
                        {"matcher": {"id": "byName", "options": "upper_band"},
                         "properties": [{"id": "color", "value": {"fixedColor": "light-blue", "mode": "fixed"}},
                                        {"id": "custom.lineStyle", "value": {"fill": "dash", "dash": [10, 10]}},
                                        {"id": "custom.fillBelowTo", "value": "lower_band"},
                                        {"id": "custom.fillOpacity", "value": 8}]},
                        {"matcher": {"id": "byName", "options": "lower_band"},
                         "properties": [{"id": "color", "value": {"fixedColor": "light-blue", "mode": "fixed"}},
                                        {"id": "custom.lineStyle", "value": {"fill": "dash", "dash": [10, 10]}}]},
                        {"matcher": {"id": "byFrameRefID", "options": "D"},
                         "properties": [{"id": "color", "value": {"fixedColor": "purple", "mode": "fixed"}},
                                        {"id": "custom.axisPlacement", "value": "right"},
                                        {"id": "min", "value": 0}, {"id": "max", "value": 100},
                                        {"id": "unit", "value": "percent"}]},
                    ],
                },
                "options": {
                    "mode": "candles+volume", "includeAllFields": True,
                    "candleStyle": "candles", "colorStrategy": "open-close",
                    "colors": {"up": "green", "down": "red"},
                    "fields": {"open": "open", "high": "high", "low": "low",
                               "close": "close", "volume": "volume"},
                },
                # --- Overlay pattern: refId A = OHLC columns, B/C/D = one indicator each ---
                # A MUST return: open, high, low, close, volume (for the candlestick)
                # B/C/D MUST return: ts AS time + indicator columns ONLY (not OHLC)
                # Grafana overlays B/C/D on A using the shared time column
                "targets": [
                    {   # A: OHLC + volume — the candlestick data
                        "refId": "A", "datasource": DS_REF, "format": 1,
                        "rawSql": "SELECT ts AS time, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s;",
                    },
                    {   # B: VWAP only — overlays on candlestick
                        "refId": "B", "datasource": DS_REF, "format": 1,
                        "rawSql": "WITH ohlc AS (SELECT ts, symbol, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s), vwap AS (SELECT ts, sum((high + low + close) / 3 * volume) OVER (ORDER BY ts CUMULATIVE) / sum(volume) OVER (ORDER BY ts CUMULATIVE) AS vwap FROM ohlc) SELECT ts AS time, vwap FROM vwap;",
                    },
                    {   # C: Bollinger Bands only (sma20, upper_band, lower_band)
                        "refId": "C", "datasource": DS_REF, "format": 1,
                        "rawSql": "WITH ohlc AS (SELECT ts, symbol, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s), stats AS (SELECT ts, close, AVG(close) OVER (ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS sma20, AVG(close * close) OVER (ORDER BY ts ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS avg_close_sq FROM ohlc) SELECT ts AS time, sma20, sma20 + 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS upper_band, sma20 - 2 * sqrt(avg_close_sq - (sma20 * sma20)) AS lower_band FROM stats;",
                    },
                    {   # D: RSI only — right Y-axis 0-100%
                        "refId": "D", "datasource": DS_REF, "format": 1,
                        "rawSql": "WITH ohlc AS (SELECT ts, symbol, first(price) AS open, max(price) AS high, min(price) AS low, last(price) AS close, sum(amount) AS volume FROM trades WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s), changes AS (SELECT ts, close, close - LAG(close) OVER (ORDER BY ts) AS change FROM ohlc), gains_losses AS (SELECT ts, close, CASE WHEN change > 0 THEN change ELSE 0 END AS gain, CASE WHEN change < 0 THEN ABS(change) ELSE 0 END AS loss FROM changes), avg_gl AS (SELECT ts, close, AVG(gain) OVER (ORDER BY ts ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_gain, AVG(loss) OVER (ORDER BY ts ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) AS avg_loss FROM gains_losses) SELECT ts AS time, CASE WHEN avg_loss = 0 THEN 100 ELSE 100 - (100 / (1 + avg_gain / NULLIF(avg_loss, 0))) END AS rsi FROM avg_gl;",
                    },
                ],
            },
            {
                "id": 3, "type": "timeseries",
                "title": "Bid-Ask Spread - $symbol",
                "gridPos": {"h": 6, "w": 24, "x": 0, "y": 20},
                "datasource": DS_REF,
                # CRITICAL: overrides REQUIRED — without them spread (~0.01) is invisible
                # next to bid/ask (~97000). Spread MUST use right Y-axis.
                "fieldConfig": {
                    "defaults": {"custom": {"lineWidth": 1, "fillOpacity": 15, "spanNulls": True, "pointSize": 1}},
                    "overrides": [
                        {"matcher": {"id": "byName", "options": "spread"},
                         "properties": [{"id": "color", "value": {"fixedColor": "red", "mode": "fixed"}},
                                        {"id": "custom.axisPlacement", "value": "right"}]},
                        {"matcher": {"id": "byName", "options": "best_bid"},
                         "properties": [{"id": "color", "value": {"fixedColor": "green", "mode": "fixed"}}]},
                        {"matcher": {"id": "byName", "options": "best_ask"},
                         "properties": [{"id": "color", "value": {"fixedColor": "orange", "mode": "fixed"}}]},
                    ],
                },
                "targets": [{
                    "refId": "A", "datasource": DS_REF, "format": 1,
                    "rawSql": "SELECT ts AS time, avg(ask_prices[1] - bid_prices[1]) AS spread, avg(bid_prices[1]) AS best_bid, avg(ask_prices[1]) AS best_ask FROM orderbook WHERE $__timeFilter(ts) AND symbol = '$symbol' SAMPLE BY 5s;",
                }],
            },
        ],
    },
    "overwrite": True,
}

resp = requests.post(f"{GRAFANA_URL}/api/dashboards/db", auth=GRAFANA_AUTH,
                     headers={"Content-Type": "application/json"}, json=dashboard)
url = f"{GRAFANA_URL}{resp.json().get('url', '')}?refresh=250ms&from=now-5m&to=now"
print(f"Dashboard: {resp.status_code} - {url}")

# Open in browser — data gate already confirmed rows are flowing
subprocess.run(["open" if sys.platform == "darwin" else "xdg-open", url])

Always reference the datasource by UID and type, never by display name. Do NOT add extra

sleep

commands — the data gate loop handles all waiting.

questdb

NPX Install

Tags

SKILL.md Content

QuestDB Skill

How to Use This Skill

Execution Scenarios

Critical Rule

Live Documentation Access

SQL Reference

CREATE TABLE

SAMPLE BY (Time-Series Aggregation)

LATEST ON (Last Value Per Group)

Joins

UNNEST (Arrays and JSON Arrays to Rows)

Window Functions

Materialized Views

Views (non-materialized)

Timestamp Filtering — TICK Syntax (Preferred)

DECLARE (Variables)

SQL Execution Order

Key Functions

Data Ingestion

ILP (InfluxDB Line Protocol) — Primary Method

INSERT INTO — For Low Volume Only

Querying via PG Wire

HTTP REST API

Schema Design

Financial Market Data Pipeline Schema (Ready to Use)

Demo & Sample Data

Real-Time Crypto Feed (cryptofeed + QuestDB)

QuestDB Demo Instance

Grafana Integration

Ready-to-Use Grafana Queries

Dashboard Deployment via API