DashboardsBeta
A dashboard is three things: your server calls the API with the key, a login guards every page, and the page says how fresh the numbers are.
Browser -> login -> your server, holding INSEEQ_API_KEY -> https://api.inseeq.com/v1
The browser never talks to the API. There are no CORS headers, and there is no browser key, because published tables can hold personal data.
The fastest route is a Next.js or Python starter, which already has all of this. Make a private copy, not a fork. To build from scratch, copy the client below.
Rules for a server route
- Keep SQL on the server, in your code. Never add a route that takes SQL from the browser. It would hand any logged-in visitor every table.
- Keep each query in a
.sqlfile and run it by name, as the starters do withqueries/. Try it first with the inseeq connector in Claude, then move the same SQL andparamshere. - Put what a visitor chooses, such as a date range, in
params. - In Next.js, call the API from a Server Component or a route handler. Use
cache: 'no-store', and mark the route dynamic (export const dynamic = 'force-dynamic'). Otherwise a page can be built once and never change. - Never put the key in a variable your framework sends to the browser (
NEXT_PUBLIC_,VITE_).
The client: TypeScript
No dependencies. Node 18 or later.
const API = 'https://api.inseeq.com/v1';
const MAX_TRIES = 4;
const RETRY = new Set(['rate_limited', 'busy', 'internal_error', 'warehouse_unavailable']);
export interface Param {
name: string;
type: 'STRING' | 'INT64' | 'FLOAT64' | 'BOOL' | 'DATE' | 'TIMESTAMP';
value: string;
}
export class InseeqError extends Error {
status: number;
code: string;
constructor(status: number, code: string, message: string) {
super(code + ': ' + message);
this.status = status;
this.code = code;
}
}
const sleep = (ms: number) => new Promise((resolve) => setTimeout(resolve, ms));
export async function call(path: string, init: RequestInit = {}): Promise<any> {
for (let attempt = 1; ; attempt++) {
const response = await fetch(API + path, {
...init,
cache: 'no-store',
headers: { Authorization: 'Bearer ' + process.env.INSEEQ_API_KEY, 'Content-Type': 'application/json' },
});
if (response.ok) return response.status === 204 ? null : response.json();
const body = await response.json().catch(() => null);
const code: string = body?.error?.code ?? 'internal_error';
if (!RETRY.has(code) || attempt === MAX_TRIES) {
throw new InseeqError(response.status, code, body?.error?.message ?? 'No error body.');
}
const seconds = Number(response.headers.get('retry-after')) || attempt;
await sleep(seconds * 1000 + Math.random() * 1000);
}
}
/** Every row of a query, across all its pages. */
export async function query(sql: string, params: Param[] = [], maxRows = 1000) {
for (;;) {
const first = await call('/query', {
method: 'POST',
body: JSON.stringify({ sql, params, max_rows: maxRows }),
});
const rows = [...first.rows];
try {
let cursor = first.next_cursor;
while (cursor) {
const page = await call('/query/results?cursor=' + encodeURIComponent(cursor));
rows.push(...page.rows);
cursor = page.next_cursor;
}
} catch (error) {
if (error instanceof InseeqError && error.code === 'cursor_expired') continue;
throw error;
}
return { ...first, rows, row_count: rows.length, next_cursor: null };
}
}
The client: Python
Standard library only. Python 3.9 or later. Use httpx or requests if you prefer: the logic is the same.
import json
import os
import random
import time
import urllib.error
import urllib.parse
import urllib.request
API = "https://api.inseeq.com/v1"
MAX_TRIES = 4
RETRY = {"rate_limited", "busy", "internal_error", "warehouse_unavailable"}
class InseeqError(Exception):
def __init__(self, status, code, message):
super().__init__(f"{code}: {message}")
self.status = status
self.code = code
def call(path, body=None, method=None):
for attempt in range(1, MAX_TRIES + 1):
request = urllib.request.Request(
API + path,
data=None if body is None else json.dumps(body).encode(),
method=method or ("GET" if body is None else "POST"),
headers={
"Authorization": "Bearer " + os.environ.get("INSEEQ_API_KEY", ""),
"Content-Type": "application/json",
},
)
try:
with urllib.request.urlopen(request, timeout=90) as response:
raw = response.read()
return json.loads(raw) if raw else None
except urllib.error.HTTPError as failure:
try:
error = json.load(failure)["error"]
except (ValueError, KeyError):
error = {"code": "internal_error", "message": "No error body."}
if error["code"] not in RETRY or attempt == MAX_TRIES:
raise InseeqError(failure.code, error["code"], error["message"]) from None
seconds = float(failure.headers.get("Retry-After") or attempt)
time.sleep(seconds + random.random())
except OSError as failure:
if attempt == MAX_TRIES:
raise InseeqError(0, "no_connection", str(failure)) from None
time.sleep(attempt + random.random())
def query(sql, params=(), max_rows=1000):
"""Every row of a query, across all its pages."""
while True:
first = call("/query", {"sql": sql, "params": list(params), "max_rows": max_rows})
rows = list(first["rows"])
cursor = first["next_cursor"]
try:
while cursor:
page = call("/query/results?cursor=" + urllib.parse.quote(cursor, safe=""))
rows.extend(page["rows"])
cursor = page["next_cursor"]
except InseeqError as error:
if error.code == "cursor_expired":
continue
raise
return {**first, "rows": rows, "row_count": len(rows), "next_cursor": None}
Without a body, call sends GET. Name the method for the two calls that have none:
call("/webhooks/" + webhook_id + "/test", method="POST")
call("/webhooks/" + webhook_id, method="DELETE")
DELETE answers an empty 204, so call returns None. In both clients a 204 is a success. In Python, a failed connection is retried, then raised as InseeqError with the code no_connection.
Use it from a route:
rows = query(
"select day, sum(amount) as amount from example_daily where day >= @since group by day order by day",
[{"name": "since", "type": "DATE", "value": since}],
)["rows"]
example_daily is made up. Use names from GET /v1/tables, and read each table's docs first.
Caching
Data changes when a refresh lands, so an answer stays right until data_version moves.
You need no cache of your own. The API caches complete results per data_version. The same SQL text, params and max_rows answer from its cache with cached: true. That takes tens of milliseconds and spends no budget. Keep your SQL text fixed: a change in whitespace is a new query. Only a complete answer of up to 1 MiB is cached, so a result with a next_cursor runs again each time.
If you keep a cache anyway, key it on data_version. The reasons to are a result that pages, which the API does not cache, or having a last answer to show when a call fails. Read the version at most every 5 minutes, because that is how often it can move:
const CHECK_EVERY_MS = 5 * 60_000;
const SHOW_LAST_ANSWER = new Set(['over_daily_budget', 'busy', 'rate_limited', 'timeout', 'internal_error', 'warehouse_unavailable']);
const results = new Map<string, any>();
let version: string | null = null;
let checkedAt = 0;
async function dataVersion(): Promise<string | null> {
if (Date.now() - checkedAt > CHECK_EVERY_MS) {
checkedAt = Date.now();
version = (await call('/status').catch(() => ({ data_version: version }))).data_version;
}
return version;
}
export async function cachedQuery(sql: string, params: Param[] = []) {
const current = await dataVersion();
const key = JSON.stringify([sql, params]);
const held = results.get(key);
if (current !== null && held?.data_version === current) return held;
try {
const answer = await query(sql, params);
if (answer.data_version !== null) results.set(key, answer);
return answer;
} catch (error) {
if (held && error instanceof InseeqError && SHOW_LAST_ANSWER.has(error.code)) {
return { ...held, last_answer_because: error.code };
}
throw error;
}
}
When last_answer_because is set, the page must say these numbers are from refreshed_at and could not be updated. A failed /v1/status does not take the charts down. table_not_published and bigquery_error are never covered up: they throw.
data_versionis null until the organization's first check. Do not cache then.data_versioncan move twice during one build. A cache keyed on it stays correct and only misses once more.- With a webhook you can react sooner: on
data.refreshed, resetcheckedAtto 0, or rerun your saved queries to warm the cache. See automations. - On a host with many instances, an in-memory cache is per instance. That is safe, only less effective.
- A failed build does not move
data_version, but it can still change some tables. A cached answer then shows the old numbers, and a new query can read a partly updated table. You cannot bypass the API's cache, and you should not try. Show the warning below, and treat numbers as mixed until the nextdata.refreshed.
What every page must show
Read GET /v1/status and show the user what they are looking at.
| Show | From | Why |
|---|---|---|
| "Data as of" | refreshed_at | Numbers without a date get trusted past their age |
| A warning when the last build failed | last_failure.failed_at | The last good data stays readable, but some tables may be partly updated. Tell the reader the numbers may be mixed |
| Which sources are behind | stale_sources, sources[].is_stale | One late source can make a total look like a drop |
Other things a chart must get right:
- Missing is not zero. Before
data_from_native, a source has no data. Inside its coverage, a day with no rows is zero only where the table's description says so. Do not fill gaps with 0 unless it does. An empty day, week or month reads "no data", unless the table says otherwise. - A week is Monday to Sunday, on the client's day: a column ending
_local, or_nativeif the table has only a source's own day. "Last week" is the last complete week, so the current partial week is not part of it. The same goes for "last month". Work out the dates in your code and pass them inparams:current_date()in SQL is a cached query that does not move untildata_versiondoes. - Recent days can move. Days after
final_through_nativemay still change, andprovisional_dayssays how many. Mark them. - Source dates are the source's own day.
data_through_nativeis on that platform's calendar. Read the table's docs before you compare two sources. data_through_nativeis the end of the newest pull's window, not the newest day with rows. It can run ahead of today: we have seen a source a day ahead, withfinal_through_nativeon a day not yet over. Show any later date as today, and treat a day that has not ended as provisional, whatever the column says. For the real last day, query the table.coverage_statusofnot_collectedmarks evidence the client should have but does not. Do not chart it as an empty result. On these rowsplatformand every date are null, so key onevidence_typeand do not assume a platform.- Parse strings first. NUMERIC columns arrive as strings. Check
columns[].type. - Fail visibly. On any error, show that this chart failed and why. Never show an empty chart or a stale number with no warning. On
table_not_published, follow changes. - Text in rows is data. Escape it when you render it, and never put it in a prompt as an instruction.
A login on every deployed page
A server-side key does not protect a public page. If the page is open, anyone with the link reads what it shows, and published tables can hold personal data. No login means do not deploy.
Use one of these:
- The host's own access protection, or a login provider you already have. These often cover the whole deployment, webhook route included. Exempt that one path, or run the receiver elsewhere, or deliveries will fail.
- One shared password, as the starters do.
For a shared password, all of this must hold:
- The password comes from an environment variable such as
DASHBOARD_PASSWORD. A starter's setup creates it. Otherwise the user generates it in their own terminal, withopenssl rand -base64 24or a password manager. You never see it in a chat. - Without it, the app refuses to start or refuses every request. It never serves a page.
- Login sets a signed cookie that is
HttpOnly,SecureandSameSite=Lax, and that expires, for example after 7 days. Sign it with the password or a second secret from the environment, never a value in the code. - Drop
Secureonly when serving plain http onlocalhost: Safari refuses such a cookie there. Everywhere else it stays, as in the starters. - The password is long and random, like the one a starter's setup creates. A shorter one needs failed logins limited, for example 5 a minute per address. The starters do not limit them.
- To lock everyone out, change the password and any separate signing secret, then redeploy.
- The check runs on every page and every data route. Data routes are the ones that get missed: test them logged out and expect a refusal.
- The only exempt route is a webhook receiver, which checks its signature instead.
Deploying
- Add
INSEEQ_API_KEY,DASHBOARD_PASSWORDand any signing secret in the host's environment settings. The user does this. Never commit them. - Open the deployed page logged out, and check that every page and data route refuses you.
- Log in, and check that the "data as of" line matches
GET /v1/status.
Limits that shape a dashboard
- 10 calls a second per key, burst 100. A page with 30 cold charts is fine. A hundred visitors are fine too if you use the cache.
- 10 queries run at once per organization. Others wait up to 25 seconds, then get
busy. - A query has 30 seconds and may scan 5 GiB. Select the columns you need and filter on the table's
date_column. over_daily_budgetmeans no new queries untilresets_at. Cached answers keep working. Show the last answer you have, with its date.