# API

Insights exposes a programmatic API over NATS request/reply using [NATS micro](https://docs.nats.io/nats-concepts/service_infrastructure/services). The API starts automatically whenever a sink connection is available and provides the same query capabilities that power the web UI.

Any NATS client connected to the same server (or cluster) can issue requests to these endpoints. This enables automation, integration with external tooling, and headless operation without the web UI.

## Service Info

The API registers as a NATS micro service named `insights` with version `0.0.1`. You can discover it using the standard micro service discovery subjects (`$SRV.INFO`, `$SRV.PING`, etc.).

## Subject Hierarchy

All API subjects use the `$INS` prefix and follow a `$INS.{domain}.{action}` convention:

| Domain   | Subject                | Description                                |
| -------- | ---------------------- | ------------------------------------------ |
| `db`     | `$INS.db.query`        | SQL query execution                        |
| `db`     | `$INS.db.query.stream` | Streaming SQL query for large results      |
| `db`     | `$INS.db.explain`      | Validate/plan SQL without executing it     |
| `db`     | `$INS.db.schemas`      | List schemas                               |
| `db`     | `$INS.db.tables`       | List tables and views in a schema          |
| `db`     | `$INS.db.columns`      | List columns of a table or view            |
| `db`     | `$INS.db.macros`       | List macros in a schema                    |
| `db`     | `$INS.db.backup`       | Database backup                            |
| `ops`    | `$INS.ops.info`        | Instance capability and config discovery   |
| `ops`    | `$INS.ops.storage`     | Database storage and connection-pool stats |
| `checks` | `$INS.checks.list`     | List audit checks                          |
| `checks` | `$INS.checks.info`     | Full metadata for one check                |
| `checks` | `$INS.checks.findings` | Run a check and return rows                |

The first token after `$INS` identifies the domain, the second the action. That makes subjects predictable and easy to authorize with NATS subject permissions (for example, grant `$INS.db.>` for all database operations).

## Versioning

The API doesn't currently encode a version in the subject hierarchy. When breaking changes happen, consumers should include an `Insights-Api-Version` header (for example, `Insights-Api-Version: 2`). Requests without the header get current behavior.

## HTTP Gateway

For clients that can't speak NATS (for example, Grafana's Infinity data source), the `insights http` subcommand runs a stateless HTTP-to-NATS bridge. It translates each HTTP request into the matching read-only `$INS.db.*` NATS request and returns the reply. The gateway owns no database and runs no queries itself — the read-only guard and row caps stay enforced by the `$INS.db.*` handlers.

| Method & path      | NATS subject                  |
| ------------------ | ----------------------------- |
| `POST /db/query`   | `$INS.db.query`               |
| `POST /db/explain` | `$INS.db.explain`             |
| `POST /db/schemas` | `$INS.db.schemas`             |
| `POST /db/tables`  | `$INS.db.tables`              |
| `POST /db/columns` | `$INS.db.columns`             |
| `POST /db/macros`  | `$INS.db.macros`              |
| `GET /healthz`     | — (liveness, unauthenticated) |

Every route except `/healthz` requires a bearer token (`Authorization: Bearer <token>`), set with `--auth-token`. The gateway refuses to start without a token unless `--allow-unauthenticated` is given. See the [CLI reference](cli.md) for the full flag set.

## Endpoints

### Query: `$INS.db.query`

Execute arbitrary read-only SQL queries against the DuckDB database.

**Request:**

```json
{
  "sql": "SELECT * FROM hx.server_ident LIMIT 10",
  "params": []
}
```

| Field    | Type   | Required | Description                                                     |
| -------- | ------ | -------- | --------------------------------------------------------------- |
| `sql`    | string | yes      | SQL query to execute. Must be read-only (SELECT, WITH, EXPLAIN) |
| `params` | array  | no       | Positional parameters for parameterized queries                 |

**Response (JSON, default):**

```json
[
  { "name": "server-1", "cluster": "us-east", "version": "2.10.0" },
  { "name": "server-2", "cluster": "us-west", "version": "2.10.0" }
]
```

An array of objects, one per row, keyed by column name.

**Response (CSV):**

Set the `Accept: text/csv` header to receive results as CSV.

This endpoint buffers the whole result before responding, so it caps the row count (`db.query-max-rows`, default `100000`). A result that would exceed the cap is rejected with `413` — add a `LIMIT`, or use the streaming endpoint (`$INS.db.query.stream`) below, which streams the result instead of buffering it.

**Errors:**

| Code  | Condition                                                                       |
| ----- | ------------------------------------------------------------------------------- |
| `400` | Invalid JSON, missing SQL, or non-read-only query                               |
| `413` | Result exceeds `db.query-max-rows`; add a `LIMIT` or use `$INS.db.query.stream` |
| `500` | Internal server error                                                           |

**Example using `nats` CLI:**

```sh
nats req '$INS.db.query' '{"sql": "SELECT count(*) as n FROM hx.server_ident"}'
```

### Streaming Query: `$INS.db.query.stream`

Streams a large read-only result instead of buffering it. The result is delivered as ordered [Apache Arrow](https://arrow.apache.org/) chunks so neither the server nor the client holds the whole result in memory — the path to use for large exports. The same read-only rules as `$INS.db.query` apply, and it is not subject to the `db.query-max-rows` buffering cap (a large safety limit, `db.query-stream.max-rows`, default `1000000`, still aborts a runaway unscoped scan).

The `insights query` command uses this endpoint automatically, falling back to `$INS.db.query` when no streaming responder is available.

### Explain & Schema Discovery: `$INS.db.explain` / `.schemas` / `.tables` / `.columns` / `.macros`

These read-only endpoints back the `insights db` subcommands and the matching HTTP gateway routes. Discovery reads catalog metadata only — no data is scanned — and is scoped to the allowlisted `hx`, `main`, and `audit` schemas.

- **`$INS.db.explain`** — plan a read-only query without executing it. Request `{ "sql": "…", "analyze": false }`; set `"analyze": true` to run `EXPLAIN ANALYZE` (which executes the query, still read-only-guarded) and add per-operator runtime timing. Returns the DuckDB plan text.
- **`$INS.db.schemas`** — list schemas with descriptions and object counts. Empty request (`{}`).
- **`$INS.db.tables`** — list tables and views. Request `{ "schema": "hx" }`; omit `schema` to list across all allowlisted schemas.
- **`$INS.db.columns`** — list columns of a table or view. Request `{ "schema": "hx", "table": "servers" }`.
- **`$INS.db.macros`** — list macros with signatures. Request `{ "schema": "audit" }`; omit `schema` for all.

### Checks List: `$INS.checks.list`

Returns all audit checks grouped by category, including configurable parameters.

**Request:** Empty payload.

**Response:**

```json
[
  {
    "label": "Server Health",
    "checks": [
      {
        "code": "SERVER_003",
        "name": "High CPU Usage",
        "description": "Server CPU usage exceeds threshold",
        "scope": "server",
        "optimization": false,
        "params": [
          {
            "name": "cpu_percent",
            "default": 90.0,
            "resolved": 90.0,
            "description": "CPU usage threshold percentage"
          }
        ]
      }
    ]
  }
]
```

### Checks Info: `$INS.checks.info`

Return full metadata for a single check, including its description, severity, scope, and configurable parameters with default and resolved (currently in-effect) values.

**Request:**

```json
{ "code": "SERVER_003" }
```

| Field  | Type   | Required | Description                     |
| ------ | ------ | -------- | ------------------------------- |
| `code` | string | yes      | Check code (e.g., `SERVER_003`) |

**Response:**

```json
{
  "code": "SERVER_003",
  "name": "High CPU Usage",
  "description": "Server CPU usage exceeds threshold",
  "scope": "server",
  "severity": "warning",
  "category": "Performance",
  "optimization": false,
  "params": [
    {
      "name": "cpu_percent",
      "default": 90.0,
      "resolved": 90.0,
      "description": "CPU usage threshold percentage"
    }
  ]
}
```

**Errors:**

| Code  | Condition             |
| ----- | --------------------- |
| `400` | Missing check code    |
| `404` | Unknown check code    |
| `500` | Internal server error |

### Checks Findings: `$INS.checks.findings`

Execute a specific check by code and return matching results.

**Request:**

```json
{
  "code": "SERVER_003",
  "time": {
    "duration": "1h"
  },
  "page": 1
}
```

| Field  | Type   | Required | Description                     |
| ------ | ------ | -------- | ------------------------------- |
| `code` | string | yes      | Check code (e.g., `SERVER_003`) |
| `time` | object | no       | Time parameters with `duration` |
| `page` | int    | no       | Page number for pagination      |

**Response (JSON, default):**

```json
{
  "rows": [
    {
      "code": "SERVER_003",
      "severity": "warning",
      "entity": "server-1",
      "entity_pk": "NABC123",
      "check_name": "High CPU Usage",
      "cpu_percent": 92.3
    }
  ],
  "page_info": {
    "page": 1,
    "total_pages": 1,
    "total_rows": 2
  }
}
```

**Response (CSV):** Set `Accept: text/csv` header.

**Errors:**

| Code  | Condition                              |
| ----- | -------------------------------------- |
| `400` | Missing check code or invalid duration |
| `404` | Unknown check code                     |
| `500` | Internal server error                  |

### Backup: `$INS.db.backup`

Create a transactionally consistent DuckDB backup. The backup captures all tables in the `hx` schema, pinned to a consistent epoch to avoid partial data from concurrent indexing.

**Request:**

```json
{
  "start_epoch": "2024-01-01T00:00:00Z",
  "end_epoch": "2024-01-02T00:00:00Z",
  "upload": true,
  "retain": false
}
```

| Field         | Type              | Required | Description                                                                    |
| ------------- | ----------------- | -------- | ------------------------------------------------------------------------------ |
| `start_epoch` | string (RFC 3339) | no       | Start of epoch range to include. Omit for full backup                          |
| `end_epoch`   | string (RFC 3339) | no       | End of epoch range to include. Omit for full backup                            |
| `upload`      | bool              | no       | Upload the backup to a NATS object store (`insights-backups` bucket)           |
| `retain`      | bool              | no       | When `upload` is true, keep the local file after uploading. Default removes it |

All fields are optional. With an empty payload `{}`, a full backup is created locally.

**Response:**

```json
{
  "filename": "insights-backup-20240115-093045.db",
  "size": 52428800,
  "duration": "1.234s",
  "tables": 24,
  "object_name": "insights-backup-20240115-093045.db",
  "bucket": "insights-backups"
}
```

| Field         | Description                                              |
| ------------- | -------------------------------------------------------- |
| `filename`    | Local filename (empty when uploaded without `retain`)    |
| `size`        | Backup file size in bytes                                |
| `duration`    | Time taken to create the backup                          |
| `tables`      | Number of tables backed up                               |
| `object_name` | Object store key (present when `upload` is true)         |
| `bucket`      | Object store bucket name (present when `upload` is true) |

Only one backup can run at a time. A concurrent request returns a `409` error.

**Errors:**

| Code  | Condition                  |
| ----- | -------------------------- |
| `400` | Invalid request JSON       |
| `409` | Backup already in progress |
| `500` | Internal server error      |

The object store TTL is controlled by the [`backup.object-store-ttl`](configuration.md#backup-backup) configuration option.

### Instance Info: `$INS.ops.info`

Report the backend instance's capabilities and configuration. A web-only tier uses this to discover the settings of the backend it connects to, rather than reading its own local config. This is a request/reply endpoint with an empty request payload.

**Response:**

```json
{
  "geo": { "enabled": true },
  "realtime": { "enabled": false },
  "scraper": { "interval": 60000000000 },
  "db": { "retention": 2764800000000000 },
  "license": {}
}
```

| Field              | Description                                      |
| ------------------ | ------------------------------------------------ |
| `geo.enabled`      | Whether IP geolocation enrichment is configured  |
| `realtime.enabled` | Whether the realtime advisory feed is available  |
| `scraper.interval` | Configured scrape cadence                        |
| `db.retention`     | Data-retention duration (`0` disables retention) |
| `license`          | Parsed license claims (omitted when unlicensed)  |

`scraper.interval` and `db.retention` are durations serialized as integer nanoseconds — `60000000000` is `1m`, `2764800000000000` is `768h`.

The related `$INS.ops.storage` subject reports the database owner's storage figures (file and WAL size) and connection-pool statistics.

## Error Handling

All endpoints return errors using the NATS micro error format:

- **Code**. A string error code (HTTP-style: `400`, `404`, `500`, and so on).
- **Description**. A human-readable error message.
- **Data**. Optional additional context (JSON bytes).

Internal errors get masked with a generic `500` / `"internal server error"` response, so implementation details don't leak.

## Content Negotiation

The `db.query` and `checks.findings` endpoints support content negotiation via the NATS message `Accept` header:

- **`application/json`** (default). Results as a JSON array/object.
- **`text/csv`**. Results as CSV with a header row.