# Dashboards

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](https://github.com/inseeq/nextjs-starter) or [Python](https://github.com/inseeq/python-starter) 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.

```ts
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:

```ts
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](https://api.inseeq.com/docs/automations.md).
- 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.

| 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 `_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](https://api.inseeq.com/docs/changes.md).
- **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.
