# Named Pipes

> **Related:** [Access Control](https://wavehouse.dev/access-control.md) · [API Reference](https://wavehouse.dev/api.md) · [Architecture](https://wavehouse.dev/architecture.md) · [Claude Code & AI agents](https://wavehouse.dev/claude-code.md) · [Configuration](https://wavehouse.dev/configuration.md) · [Deployment](https://wavehouse.dev/deployment.md) · [Development](https://wavehouse.dev/development.md) · [Durability & Storage](https://wavehouse.dev/durability.md) · [Getting Started](https://wavehouse.dev/getting-started.md) · [Ingest Pipeline](https://wavehouse.dev/ingest-pipeline.md) · [Behind a reverse proxy](https://wavehouse.dev/reverse-proxy.md) · [TypeScript SDK](https://wavehouse.dev/sdk.md) · [Settings Directory](https://wavehouse.dev/settings-directory.md) · [Why WaveHouse?](https://wavehouse.dev/why-wavehouse.md)
> **Also:** [HTML version](https://wavehouse.dev/pipes) · [Docs index](https://wavehouse.dev/llms.txt)

---
A **named pipe** is a saved SQL query, registered under a name, that callers run by name with parameters — without ever sending raw SQL. They turn an ad-hoc query into a stable, cached, access-controlled endpoint: you write the SQL once as an operator in the settings directory's [`pipes.json`](/settings-directory#pipesjson), expose it at `GET/POST /v1/pipes/{name}`, and clients supply only the declared parameters.

Pipes are the right tool when a query is reusable and shouldn't live in client code — dashboards, reports, public APIs over curated slices of data. They sit on the **cached read path** (the query cache + singleflight, same as structured queries; a [pipe that writes](#pipes-that-write) bypasses both), and authorize through a simple per-pipe allowlist rather than the full [policy engine](/access-control).

## Anatomy of a pipe

A pipe is defined by five fields:

| Field | Type | Required | Description |
| ----- | ---- | -------- | ----------- |
| `name` | string | yes | The pipe's identifier; the last path segment of `/v1/pipes/{name}`. Unique within `pipes.json`. |
| `sql` | string | yes | A SQL template with `{{param}}` placeholders. Sent verbatim to ClickHouse after binding. |
| `parameters` | object[] | no | Formal parameter declarations (see [Parameters](#parameters)). |
| `description` | string | no | Human-readable note shown in the admin listing. |
| `allowed_roles` | string[] | no | Roles permitted to execute the pipe. Empty = admin role only (see [Authorization](#authorizing-a-pipe)). |

```json
{
  "name": "top_pages",
  "sql": "SELECT page, count() AS views FROM clicks WHERE received_timestamp >= {{start_date}} GROUP BY page ORDER BY views DESC LIMIT {{limit}}",
  "parameters": [
    {"name": "start_date", "type": "string", "required": true},
    {"name": "limit", "type": "number", "required": false, "default": 100}
  ],
  "description": "Top pages by view count since a given date",
  "allowed_roles": ["viewer"]
}
```

:::caution[Pipe SQL is operator-authored]
Anyone who can write a pipe can run arbitrary SQL, so pipe definitions live only in the settings directory — a file the platform operator controls — and there is no API that writes them. Callers of a pipe never send SQL — only parameter values. Keep that boundary in mind when you design a pipe: the SQL is your trusted code, the parameters are untrusted input.
:::

## Parameters

Pipes use `{{...}}` placeholders in the SQL template. There are two complementary ways to describe a parameter, and you can mix them freely.

### Declared parameters

The `parameters` array formally declares a parameter with metadata:

| Field | Type | Description |
| ----- | ---- | ----------- |
| `name` | string | Placeholder name; matches `{{name}}` in the SQL. |
| `type` | string | Documents the expected value type — one of `"string"`, `"number"`, `"boolean"`, `"array"` (any other value fails validation). Descriptive — it advertises intent to callers and the SDK; binding itself keys off the runtime value. |
| `required` | bool | If `true`, the caller must supply a value, even if the placeholder doesn't appear in the SQL. A missing required parameter is a `400`. |
| `default` | any | Value used when the caller supplies none. |

### Inline placeholders and defaults

A placeholder can also carry an inline default with `{{name:default}}`, which works with **no** formal declaration at all:

```sql
SELECT * FROM clicks
WHERE received_timestamp >= {{since:2024-01-01}}
LIMIT {{limit:100}}
```

Here `since` defaults to `2024-01-01` and `limit` to `100` when the caller omits them — convenient for quick pipes where you don't need the `required`/`type` metadata.

### How a value is resolved

For each `{{name}}` occurrence, WaveHouse picks the first available of:

1. The value the caller **supplied** (query string or request body).
2. The **declared default** (`parameters[].default`), if a formal parameter exists.
3. The **inline default** (`{{name:default}}`), if one is given.
4. Otherwise → `400 missing required parameter: name`.

Independently, every `required` declared parameter must be supplied regardless of whether it appears in the SQL — so you can require a parameter that gates execution even if it's only referenced in some branches.

### How a value becomes SQL

Bound values are **inlined directly into the SQL string** (not sent as positional driver parameters — that lets a parameter sit anywhere ClickHouse allows a literal, including `LIMIT`). Inlining is type-aware and escaped, and a string brings its own quotes, so **write each placeholder bare, never inside quotes** — `WHERE id = {{id}}`, not `WHERE id = '{{id}}'`:

| Supplied value | Rendered as | Note |
| -------------- | ----------- | ---- |
| string | `'escaped'` | single-quoted; `'` and `\` are escaped |
| numeric string (e.g. `"100"`) | `100` | emitted bare, so `?limit=100` from a query string works as a number |
| number | `42` / `42.5` | whole floats render as integers |
| boolean | `1` / `0` | ClickHouse-style |
| array | `('a', 'b')` | parenthesized list of escaped elements — for `IN` clauses (see below) |
| null | `NULL` | |

Every leaf value is escaped the same way — including each element of an array — so a value bound to a bare placeholder can't break out of its literal and inject SQL, and the SQL *structure* comes only from the operator-authored template. A quoted placeholder gives that up: the value's own quotes close the template's, so in `WHERE id = '{{id}}'` the body `{"id": " OR 1=1 OR id = "}` binds to `WHERE id = '' OR 1=1 OR id = ''`, which matches every row. Values with no safe scalar form are **rejected** with a `400`: a JSON object, and an empty array (which would render as the invalid `IN ()`).

#### Array parameters and `IN` lists

A parameter whose value is a JSON array renders as a ClickHouse list literal — `(v1, v2, …)`, every element escaped — ready for an `IN` clause. Write the placeholder **without** surrounding parentheses; the value brings its own:

```sql
SELECT page, count() AS views FROM clicks WHERE page IN {{pages}} GROUP BY page
```

```bash
curl -X POST http://localhost:8080/v1/pipes/by_pages \
  -H "Content-Type: application/json" \
  -d '{"pages": ["/home", "/pricing", "/docs"]}'
# binds to: … WHERE page IN ('/home', '/pricing', '/docs')
```

Array values must come from a **JSON body** (`POST`) — query-string parameters are always scalar strings, so `GET …?pages=a&pages=b` binds only the first occurrence (`a`) as a string, ignoring the rest.

## Authorizing a pipe

Pipe execution is gated by `allowed_roles` — a plain allowlist, evaluated independently of the table policy:

- The caller's role (a roleless request first resolved to the policy [`default_role`](/access-control#default_role--public-unauthenticated-access)) must appear in `allowed_roles` by **exact** match. There is no `"*"` wildcard, and empty-string entries are ignored.
- The **admin role always passes**, every pipe, by design — admins author pipes and can run arbitrary SQL anyway, so the allowlist is never a boundary against them.
- An **empty or omitted `allowed_roles` authorizes nobody but admin.** Pipes fail closed: a role that isn't listed (or a roleless request with no usable `default_role`) is denied with `403 forbidden`.

To make a pipe public, list the role that `default_role` resolves to. For example, with `default_role: viewer`, a pipe with `"allowed_roles": ["viewer"]` is reachable with no token at all.

This is the *only* authorization check on the execute path — pipes deliberately sit outside the `/v1/ops/*` gate so non-admin callers can run them. The pipe's SQL is not re-checked against the table policy's column or row rules, so **scope the data in the pipe's SQL itself** rather than assuming policy column masking applies. A pipe's `{{param}}` template parameters interpolate values from the request — or a parameter's configured default when the caller omits one ([resolution order](#how-a-value-is-resolved)) — inlined as escaped SQL literals, never token claims; a parameterized predicate is a convenience for the caller, not an authorization boundary.

## Creating and managing pipes

Pipes are defined in the settings directory's `pipes.json` — a `{"pipes": [...]}` list of the definitions above — and the files are the only way to define or change one: edit the file (standalone: on the host; on WaveHouse Cloud the control plane writes it) and the running server re-validates and adopts it on file change, `SIGHUP`, or `POST /v1/ops/settings/reload`, so a create, update, or delete applies without a restart. Validation (`wavehouse validate`, boot, and every reload) rejects an unknown key, a duplicate or empty name, empty SQL, an unknown parameter `type`, and an `allowed_roles` entry not declared in `roles.json`; a rejected reload keeps the previous pipes in effect. See [Settings Directory — `pipes.json`](/settings-directory#pipesjson) for the full rules.

```json
{
  "pipes": [
    {
      "name": "top_pages",
      "sql": "SELECT page, count() AS views FROM clicks WHERE received_timestamp >= {{start_date}} GROUP BY page ORDER BY views DESC LIMIT {{limit}}",
      "parameters": [
        {"name": "start_date", "type": "string", "required": true},
        {"name": "limit", "type": "number", "required": false, "default": 100}
      ],
      "description": "Top pages by view count",
      "allowed_roles": ["viewer"]
    }
  ]
}
```

Two admin-only read endpoints, under `/v1/ops/pipes`, show what is adopted:

| Method | Endpoint | Purpose |
| ------ | -------- | ------- |
| `GET` | `/v1/ops/pipes` | List all pipes. |
| `GET` | `/v1/ops/pipes/{name}` | Get one pipe's definition. |

The [TypeScript SDK](/sdk) exposes them as `client.pipes.list()` and `client.pipes.get(name)`, and the reload as `client.settings.reload()`.

## Executing a pipe

Callers run a pipe at `GET` or `POST /v1/pipes/{name}` — this route is **not** admin-gated; it's governed by the pipe's `allowed_roles`.

- **`GET`** — parameters come from the query string: `GET /v1/pipes/top_pages?start_date=2024-01-01&limit=20`.
- **`POST`** — parameters come from a JSON body, and may be combined with query-string parameters. When the same key appears in both, the **body wins**.

```bash
# GET with query-string params
curl "http://localhost:8080/v1/pipes/top_pages?start_date=2024-01-01&limit=20" \
  -H "Authorization: Bearer $VIEWER_TOKEN"

# POST with a JSON body
curl -X POST http://localhost:8080/v1/pipes/top_pages \
  -H "Authorization: Bearer $VIEWER_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"start_date": "2024-01-01", "limit": 20}'
```

The response is a JSON array of rows. Results flow through the query cache (in-process by default, or a Redis shared by every instance with [`cache.backend: redis`](/configuration#cache)) with singleflight coalescing, so concurrent identical calls hit ClickHouse once; an `X-Cache: HIT` or `X-Cache: MISS` header tells you which path served the response. A [pipe that writes](#pipes-that-write) skips both and answers `X-Cache: BYPASS`.

| Status | Body | Cause |
| ------ | ---- | ----- |
| 404 | `{"error":"pipe not found"}` | No pipe registered under that name |
| 403 | `{"error":"forbidden"}` | Caller's role isn't in `allowed_roles` (and isn't admin) |
| 400 | `{"error":"missing required parameter: x"}` | A required parameter wasn't supplied |
| 400 | `{"error":"parameter \"x\": unsupported parameter type object"}` | A non-scalar value with no SQL form — a JSON object (directly, or nested in an array). An empty array is likewise rejected (`array parameter must not be empty`). |

### Pipes that write

A pipe's SQL may be a write: a statement led by a write verb WaveHouse recognizes — `INSERT`, `UPDATE`, `DELETE`, `ALTER` (so `ALTER … DELETE`), `CREATE`, `DROP`, `TRUNCATE`, `RENAME`, `EXCHANGE`, `REPLACE`, `OPTIMIZE`, `ATTACH`, `DETACH`, `GRANT`, `REVOKE`, `KILL`, `SET`, `USE` or `SYSTEM` — directly, or `INSERT INTO` after a `WITH` list (`WITH … INSERT INTO …`, the only write ClickHouse accepts there). An `EXECUTE AS <user>` prefix is looked through: the statement after it is the one classified. Such a pipe runs on **every** call: it never reads or fills the cache and is never coalesced with an identical call in flight, so ten identical calls are ten writes. The response is `[]` with `X-Cache: BYPASS` and `Cache-Control: no-store`, so an HTTP cache in front of a `GET` does not answer a repeat either. WaveHouse classifies the statement by its leading keyword — a `WITH`-led one by whether it holds `INSERT INTO` outside parentheses — with the same classifier that sends it to ClickHouse as a write, so no pipe property marks it. A statement led by any other keyword runs as a read: one that returns rows (`BACKUP`, `RESTORE`) is cached and coalesced, so a repeat within the TTL does not run; one that returns none (`UNDROP`, `MOVE`) fails the call after it has run, which the SDK may retry — don't put a write led by another verb in a pipe ([#666](https://github.com/Wave-RF/WaveHouse/issues/666)).

A failed write is not retried automatically, because it may have run. It answers with the status and `code` a failed read would ([ClickHouse errors on the query paths](/api#clickhouse-errors-on-the-query-paths)), but always with `retryable: false` and no `Retry-After`, `503 clickhouse.unavailable` included: once the statement is on its way to ClickHouse, WaveHouse cannot tell whether it ran. The [SDK](/sdk/pipes) does not retry such an answer, so check whether the write landed before you send it again. A call refused before anything is sent — the tenant on no ClickHouse pool, `503` with `Retry-After: 30` — cannot have run, and the SDK retries it. The SDK also retries when WaveHouse's own answer never reaches it — a dropped connection, or a `502`/`503`/`504` from a proxy in front of WaveHouse that gave up waiting — so a write can still run twice that way; give a client that runs write pipes [`options.maxRetries`](/sdk#clientconfigdb) `0` if that matters.

`allowed_roles` is a write pipe's only gate: the [policy engine](/access-control)'s insert rules do not apply to it, so any role you list — including a [`default_role`](/access-control#default_role--public-unauthenticated-access) that anonymous callers resolve to — can run the write. The operator fixes the statement and its predicate when authoring the pipe; callers supply only literal values, provided every placeholder is written bare ([how a value becomes SQL](#how-a-value-becomes-sql)).

Two things a write pipe does not do yet: it does not invalidate cached reads of the table it writes — a structured query over that table can serve pre-write rows until its TTL ([#394](https://github.com/Wave-RF/WaveHouse/issues/394)); a read pipe's cached result expires only with its TTL whatever writes the table, ingest included ([#343](https://github.com/Wave-RF/WaveHouse/issues/343)) — and its rows do not reach [`/v1/stream`](/api#get-v1stream--server-sent-events-stream) subscribers, which only the [ingest pipeline](/ingest-pipeline) feeds ([#362](https://github.com/Wave-RF/WaveHouse/issues/362)). For writes that stream subscribers and structured queries should see at once, use [`POST /v1/ingest`](/api#post-v1ingesttabletable--ingest-data).

## End-to-end example

Ship a curated "top pages" endpoint that the public dashboard can call with no token.

1. **Open public access** so roleless requests assume a `viewer` identity — set `default_role` in `policies.json` ([Access Control](/access-control#default_role--public-unauthenticated-access)) and declare the role in `roles.json`. A pipe authorizes through its own `allowed_roles`, independent of any table-level `select` rules, so `default_role` is all the policy needs here:

   ```json title="policies.json"
   { "default_role": "viewer", "tables": {} }
   ```

   ```json title="roles.json"
   { "roles": ["viewer"] }
   ```

2. **Define the pipe** in `pipes.json`, allowing `viewer`; the watcher adopts the edit (or `kill -HUP` the server, or `POST /v1/ops/settings/reload`):

   ```json
   {
     "pipes": [
       {
         "name": "top_pages",
         "sql": "SELECT page, count() AS views FROM clicks WHERE received_timestamp >= {{since:2024-01-01}} GROUP BY page ORDER BY views DESC LIMIT {{limit:50}}",
         "description": "Top pages by view count",
         "allowed_roles": ["viewer"]
       }
     ]
   }
   ```

3. **Call it with no token** — the request resolves to `viewer`, which is allowed:

   ```bash
   curl "http://localhost:8080/v1/pipes/top_pages?limit=10"
   # → [{"page":"/home","views":1422}, {"page":"/pricing","views":910}, ...]
   ```

## See also

- **[Access Control](/access-control)** — roles, `default_role`, and the table-level policy engine that pipe `allowed_roles` builds on.
- **[Settings Directory — `pipes.json`](/settings-directory#pipesjson)** — the file rules and reload triggers.
- **[API Reference](/api)** — the pipe execute and admin endpoints, with full error tables.