# ClickHouse materializations

A **materialization** is an isolated ClickHouse database that Capless keeps in sync with one or more streams. You define tables with real ClickHouse SQL, connect streams to them, and query them with SQL.

Materializations read streams only through the public API (the [change feed](https://capless.sh/docs/reading.md#changes-the-change-feed)), so they can be dropped and rebuilt from scratch at any time.

## 1. Create a materialization

```sh
H="authorization: Bearer $CAPLESS_API_KEY"

curl -s https://api.capless.sh/v1/materializations -H "$H" -d '{"name":"analytics","type":"clickhouse"}'
# 201 → {"id":"mat_…","name":"analytics","type":"clickhouse","status":"…","config":{"engine":"clickhouse","database":"…"}, …}
export MAT=mat_…
```

This creates an empty database with its own credentials. No tables or sources.

## 2. Create a table

Managed tables must use a MergeTree-family engine and have a `_capless_id String` column: the **lineage column**.

```sh
curl -s https://api.capless.sh/v1/materializations/$MAT/query -H "$H" -d '{"sql":"CREATE TABLE pageviews (_capless_id String, ts DateTime64(3), path String, country String, session String) ENGINE = MergeTree ORDER BY ts"}'
```

## 3. Connect a stream (a source)

```sh
curl -s https://api.capless.sh/v1/materializations/$MAT/sources -H "$H" -d '{
  "stream": "web.events",
  "table": "pageviews",
  "filter": "entry.type == \"pageview\"",
  "map": "{\"ts\": entry.timestamp, \"path\": entry.path, \"country\": has(entry.geo) && has(entry.geo.country) ? entry.geo.country : \"\", \"session\": entry.sessionId}"
}'
```

* The source backfills from the beginning (or from `startCursor`), then follows the stream.
* `map` must return an object; its keys are column names. Without `map`, the record itself is the row.
* One destination row per source entry; `filter` drops entries.

## 4. Query

```sh
curl -s https://api.capless.sh/v1/materializations/$MAT/query -H "$H" -d '{
  "sql": "SELECT path, count() AS views, uniq(session) AS sessions FROM pageviews WHERE ts > now() - INTERVAL {days:UInt32} DAY GROUP BY path ORDER BY views DESC LIMIT 10",
  "parameters": {"days": 7}
}'
```

```json
{
  "columns": [{ "name": "path", "type": "String" }, { "name": "views", "type": "UInt64" }, { "name": "sessions", "type": "UInt64" }],
  "rows": [["/", 1204, 811], ["/pricing", 342, 290]],
  "truncated": false,
  "statistics": { "elapsedMs": 12, "readRows": 5121, "readBytes": 204840 }
}
```

* Real ClickHouse SQL, one statement per request (DDL or query); do not add a `FORMAT` clause.
* Use `{name:Type}` placeholders with `parameters` instead of string concatenation.
* Limits: 30 s, 4 GB memory, `maxRows` returned rows (default 10 000, max 100 000; `truncated: true` when cut).
* 64-bit integers are returned as JSON numbers with exact digits.
* Managed tables cannot be written, truncated, dropped or have columns altered by your SQL (`403 query_forbidden`); Capless owns their contents.

## Status

```sh
curl -s https://api.capless.sh/v1/materializations/$MAT/status -H "$H"
```

`status` is `ready` (no sources), `syncing`, `current` (every source has applied all inserts and all erasures up to the head) or `error`. Per source:

| Field | |
|---|---|
| `status` | `backfilling`, `streaming`, `detached`, `stream_deleted`, `dropped` |
| `entriesThroughCursor` | every insert before it is in the table |
| `erasuresThroughCursor` | every erasure before it has been applied |
| `entriesCurrent` / `deletionCurrent` | insert and deletion freshness, reported separately; `deletionCurrent` is only `true` when no erasure at or after `erasuresThroughCursor` exists |
| `pendingErasures`, `lastError` | |

Inserts do not wait for erasure resolution: entries covered by an erasure are already hidden from the insert feed.

## Guarantees

* **Exactly-once**: the end of each insert page is written ahead before inserting; after a crash the identical range is re-read and re-inserted with the same ClickHouse deduplication token, so a page is applied once. Deletes are idempotent and resumable.
* **One writer per source**: each source is driven by exactly one Durable Object.
* **Lineage**: Capless fills `_capless_id` with the source entry ID, and [erasures](https://capless.sh/docs/erasures.md) delete by it.
* Deleting a stream deletes its rows from every table it fed. Detaching a source (`DELETE …/sources/{id}`) stops inserts but keeps applying future erasures to the rows it wrote until the table is dropped.

## Supported and unsupported patterns

Supported: one destination row per source entry (`map` returns an object, or no `map`), with `filter` dropping entries.

Rejected or reported, never silently wrong:

* `map` returning a list (fan-out) or a scalar → source error `map_must_return_object`.
* Setting `_capless_id` to anything but the entry ID → `lineage_overwritten`.
* Non-MergeTree target tables → `422 unsupported_table_engine`; tables without the lineage column → `422 missing_lineage_column`.
* **Derived data inside ClickHouse** (materialized views, `INSERT … SELECT`, `CREATE TABLE … AS SELECT`, aggregating tables fed from a managed table) is allowed for convenience but is *outside* the erasure guarantees: erasures only delete rows from the managed table. Prefer plain views or query-time aggregation, or rebuild derived tables after erasures.

## Other endpoints

```sh
curl -s https://api.capless.sh/v1/materializations -H "$H"                          # list
curl -s https://api.capless.sh/v1/materializations/$MAT/sources -H "$H"             # sources + checkpoints
curl -s -X DELETE https://api.capless.sh/v1/materializations/$MAT/sources/src_… -H "$H"
curl -s -X DELETE https://api.capless.sh/v1/materializations/$MAT -H "$H"           # drops the database; streams untouched
```

## Isolation

One database and one ClickHouse user per materialization, a settings profile with hard maxima (time, memory, result size), no external table functions (`url`, `file`, `s3`, `remote`, …), and partial revokes on managed tables.