View as Markdown

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. Available on the Team plan.

Materializations read streams only through the public API (the change feed), so they can be dropped and rebuilt from scratch at any time.

#1. Create a materialization

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.

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)

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

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}
}'

{
  "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

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:

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

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.