SQL workflows

A workflow evaluates read-only SQL or INSERT INTO ... SELECT on demand or at a fixed interval. For read queries, every returned row is a match; use WHERE or HAVING in the SQL to express the condition. Each execution sends one array containing all returned rows to each sink. An empty result sends nothing. Repeated results and duplicate rows are sent again; consumers own any deduplication or alerting policy.

To insert query results directly into an existing table on each run, use:

INSERT INTO workflow_alerts
SELECT customer_id, count() AS total
FROM events
GROUP BY customer_id
HAVING total > 100

The insert follows the Query API's limits: the target must already exist in the selected user database. URL table functions and INSERT ... VALUES are rejected; SETTINGS clauses are handled by ClickHouse. Insert runs return no matching rows and do not send events to sinks. Logs report written_rows on successful evaluations; query failures are recorded as errors.

The dashboard automatically previews saved read queries. Insert SQL runs only when you select Run query, request an explicit API run, or when the schedule fires; opening a workflow or cancelling an edit does not execute an insert. Run query writes real rows.

Every run executes the insert again, so rows can be inserted repeatedly. A failed or interrupted execution may have written rows before its outcome became known. SQL runs are attempted once per execution; inserts are not exactly-once.

Workflows require an enabled workflow service. The public API reference documents workflow management, logs, and metrics. Organization administrators can create, run, edit, pause, and delete workflows. Organization members can view their configuration, but HTTP URLs and header values are never returned.

Run a workflow explicitly

Start one execution of a saved workflow with no request body:

curl -X POST "$RAWTREE_URL/v1/workflows/$WORKFLOW_ID/runs?organization=$ORGANIZATION&cluster=$CLUSTER" \
  -H "Authorization: Bearer $SESSION_TOKEN" \
  -H "Idempotency-Key: $REQUEST_ID"

Set REQUEST_ID to a fresh UUID for each intended execution. The optional Idempotency-Key accepts 1–128 visible ASCII characters without spaces. The API returns 202 Accepted with id (the execution ID), workflow_id, and source: "manual" after the execution service accepts the run. Acceptance does not mean SQL or sink delivery has completed. Use the runs endpoint to list execution status and the workflow's Logs to inspect SQL and sink delivery events.

Explicit runs work with active, paused, or absent schedules and leave scheduling unchanged. They execute the saved SQL and configured sinks with the same permissions and limits as scheduled runs. Organization admins and admin API keys can start them. The saved revision is pinned when the request is accepted; if it changes before execution, the run fails with workflow_definition_changed.

An explicit run can overlap another manual or scheduled execution of the same workflow. Each run executes independently. Up to two workflow evaluations can run concurrently per cluster; additional runs wait up to five seconds for capacity before failing. Scheduled ticks still skip if an earlier scheduled run is active.

Retry with the same key to recover the same execution, including after a timeout or completion. Keys are scoped to the workflow, and deduplication lasts while Temporal retains that execution's history. A new key, or a request without a key, starts another execution. Run acceptance deduplication does not guarantee exactly-once SQL writes or sink delivery.

List manual and scheduled executions together with GET /v1/workflows/{workflow_id}/runs?organization=...&cluster=.... Pages default to 20 runs and accept limit from 1 to 100. Pass next_cursor as cursor to continue; next_cursor: null marks the end. The page limit does not cap the total number of runs you can retrieve.

Temporal supplies this execution history. Active runs appear first, newest started first, followed by finished runs, newest finished first. Newly started runs and status changes can take a short time to appear. History is available for as long as Temporal retains it; pagination is not a frozen snapshot of runs that are still changing. A completed execution does not mean every asynchronous HTTP sink delivery succeeded; inspect Logs for delivery outcomes.

Configure sinks

Sinks are optional. Open the Sinks tab in the workflow editor to add up to five sinks:

• HTTP sends the event to a webhook. Enter a URL and optional custom headers. The URL and header values are encrypted and write-only. On edit, leave the URL blank and configured header values blank to preserve them. URLs and header values cannot contain {{, %, or ${: these are reserved by the delivery configuration. Percent-encoded URLs are not currently supported.
• Table inserts the returned rows to a RawTree table. Select its database and table; RawTree creates and manages the scoped write-only sink key under the hood.

The equivalent API request is:

Replace placeholder with the sink's bearer token.

curl -X POST "$RAWTREE_URL/v1/workflows?organization=$ORGANIZATION&cluster=$CLUSTER" \
  -H "Authorization: Bearer $SESSION_TOKEN" \
  -H 'Content-Type: application/json' \
  --data-binary @- <<'JSON'
  {
    "name": "high-volume-customers",
    "database": "default",
    "sql": "WITH accurateCastOrNull(customer_id, 'String') AS customer_key SELECT customer_key AS customer_id, count() AS total FROM events WHERE customer_key IS NOT NULL GROUP BY customer_key HAVING total > 100",
    "interval_seconds": 5,
    "enabled": true,
    "sinks": [
      {
        "type": "http",
        "url": "https://example.com/events",
        "headers": {"Authorization": "Bearer placeholder"}
      },
      {
        "type": "table",
        "database": "default",
        "table": "workflow_alerts"
      }
    ]
  }
JSON

Every HTTP or table sink has a stable ID. Omit id when adding a sink; RawTree assigns one. Preserve that ID when editing. PATCH replaces the sink list when sinks is supplied; omitting it preserves the existing list. Send "sinks": [] to remove all sinks.

Event format and delivery

Both sink types receive the plain JSON array returned by the SQL:

[
  {"customer_id": "customer-a", "total": 101},
  {"customer_id": "customer-b", "total": 125}
]

Two matching rows and two sinks produce two requests, each containing that complete array. A table sink inserts both rows in one request.

RawTree hands these results to its managed Vector data plane. Vector uses durable disk buffers and retries retryable HTTP failures. HTTP requests include X-RawTree-Event-Id, X-RawTree-Sink-Id, and an Idempotency-Key of rawtree/event_id/sink_id. Delivery is at-least-once, so receivers should deduplicate using that key.

Each run attempts SQL once. A query or handoff failure is recorded in Logs; the next scheduled run evaluates independently. Once accepted by Vector, sink retries reuse the buffered array without executing SQL again. Delivery IDs are scoped to the execution and sink, so the next execution has a new ID even when its rows are identical.

The API rejects explicit private, loopback, link-local, and metadata addresses unless the operator has allowed the exact origin. Delivery also checks resolved IP addresses and does not follow redirects. Vector owns buffering and retries; RawTree's delivery endpoint performs and observes each sink request. Pausing a workflow stops new scheduled evaluations. Explicit runs remain available.

Inspect a workflow's lifecycle

The editor has four tabs:

Logs become available after saving a workflow. Choose a time range, refresh to see new activity, or load older records. Expand a returned row to inspect its data. Expand correlation IDs to connect an evaluation run, logical match, and delivery attempt. Retries keep the same match event ID but get separate attempt IDs. Each sink attempt includes its type, HTTP status when available, duration, and a safe failure code.

Evaluation started → evaluation result / returned rows
                                      ↓
                              Vector handoff accepted
                                      ↓
                              Sink attempt
                               ↙             ↘
                         Accepted       Failed → retry, if retryable

An accepted Vector handoff is not a successful sink delivery. A successful delivery means the sink returned a 2xx response, not that its application finished processing the event. A timeout leaves delivery uncertain; the receiver may have accepted the event before the timeout.

The same history is available through the scoped API:

curl "$RAWTREE_URL/v1/workflows/$WORKFLOW_ID/logs?organization=$ORGANIZATION&cluster=$CLUSTER&limit=100" \
  -H "Authorization: Bearer $SESSION_TOKEN"

The response contains logs, from, to, and next_cursor. Pass the returned from, to, and next_cursor as cursor for a stable older page. from and to are Unix milliseconds; requests default to the last 24 hours and support ranges up to seven days, including older historical ranges. limit is 1–200.

History is best-effort observability, not an exactly-once audit ledger. Writes may be delayed, duplicated, or dropped during overload or outages. A process crash can leave a started operation without a recorded result. Execution and delivery do not depend on these logs.

Webhook URLs, headers, and receiver response bodies are not included. Returned rows are recorded on workflow.matched events and are visible to members of the owning organization. Payloads larger than 16 KiB are omitted from history with payload_omitted: true; the sink still receives the full event. Each sink event is delivered in a separate request so every attempt has an unambiguous result.