Menu

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.

Code
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 .sql file and run it by name, as the starters do with queries/. Try it first with the inseeq connector in Claude, then move the same SQL and params here.
  • 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.

TypeScript
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.

Python
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:

Python
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:

Python
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:

TypeScript
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_version is null until the organization's first check. Do not cache then.
  • data_version can 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, reset checkedAt to 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 next data.refreshed.

What every page must show

Read GET /v1/status and show the user what they are looking at.

ShowFromWhy
"Data as of"refreshed_atNumbers without a date get trusted past their age
A warning when the last build failedlast_failure.failed_atThe last good data stays readable, but some tables may be partly updated. Tell the reader the numbers may be mixed
Which sources are behindstale_sources, sources[].is_staleOne 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 _native if 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 in params: current_date() in SQL is a cached query that does not move until data_version does.
  • Recent days can move. Days after final_through_native may still change, and provisional_days says how many. Mark them.
  • Source dates are the source's own day. data_through_native is on that platform's calendar. Read the table's docs before you compare two sources.
  • data_through_native is 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, with final_through_native on 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_status of not_collected marks evidence the client should have but does not. Do not chart it as an empty result. On these rows platform and every date are null, so key on evidence_type and 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, with openssl rand -base64 24 or 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, Secure and SameSite=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 Secure only when serving plain http on localhost: 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

  1. Add INSEEQ_API_KEY, DASHBOARD_PASSWORD and any signing secret in the host's environment settings. The user does this. Never commit them.
  2. Open the deployed page logged out, and check that every page and data route refuses you.
  3. 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_budget means no new queries until resets_at. Cached answers keep working. Show the last answer you have, with its date.
Dashboards · inseeq Data API