`clickhouse` provider for `stackql`

This repository builds and documents the clickhouse provider for StackQL, enabling SQL-based query and provisioning operations against the ClickHouse Cloud API - organizations, services (lifecycle, scaling, settings, passwords), API keys, members and invitations, backups and backup configuration, private endpoints, BYOC infrastructure, usage cost, activities, the ClickStack surface (dashboards, alerts, sources, webhooks), ClickPipes, and Managed Postgres services.

Design Principles

ClickHouse Cloud control plane only. This provider covers the Cloud management API at https://api.clickhouse.cloud. The ClickHouse server HTTP interface (SQL-over-HTTP on any self-managed or Cloud service endpoint) is a distinct surface with disjoint authentication and is reserved as the future sibling provider clickhouse_server - see the roadmap note below.
Organization-scoped server, basic auth. Every service spec is generated on the server template https://api.clickhouse.cloud/v1/organizations/{organization_id}. The organization_id server variable carries x-stackQL-envVar: CLICKHOUSE_ORG_ID (stackql/stackql#707, stackql >= v0.10.601), so StackQL resolves it from the environment and queries need no WHERE organization_id clause; a WHERE value still wins, and with the variable unset organization_id is a required parameter on every method. The two organization-root operations (GET /v1/organizations, GET/PATCH /v1/organizations/{organizationId}) keep their full path on a path-level server override back to the bare API base. Authentication is HTTP Basic with an API key pair: the Key ID is the username (CLICKHOUSE_CLOUD_API_KEY) and the Key Secret is the password (CLICKHOUSE_CLOUD_API_SECRET) - the username_var/password_var construct shared with the confluent, kafka, fivetran and sumologic providers.
snake_case user surface. Columns and WHERE/INSERT keys are snake_case over the camelCase wire (snake_case_aliases: true on the provider config plus request.nativeCasing: camel on every method - the aws and azure provider precedent - snake_case_aliases on the provider config, request.nativeCasing per method). Nested JSON columns keep wire casing inside the blob; EXEC variables and SHOW METHODS required params use wire names.
$.result envelope. Every JSON response wraps its payload as {"result": ..., "requestId": ..., "status": ...}. List and get operations set stackql_object_key to $.result.
Rate limit as a design input. The API allows 10 requests per 10-second window per API key. Test suites run serially with deliberate pacing, and wide multi-service queries should account for the limit.
Lifecycle via PATCH. Service start/stop is PATCH .../state with a command body field, mapped as an EXEC method. Several updates use operation-array semantics (ipAccessList, privateEndpointIds and tags PATCH bodies take add/remove arrays rather than replacement values) - these wire shapes are documented plainly on the affected UPDATE methods.
Deterministic builds. Every pipeline step is a re-runnable script; manual mapping decisions are rules in scripts, never hand-edits to CSVs or specs. Scripts validate and fail without writing on any violation.

Roadmap: the `clickhouse_server` sibling provider

The ClickHouse server HTTP interface - SQL-over-HTTP against any self-managed or Cloud service endpoint - is out of scope here and reserved as the future sibling provider clickhouse_server, following the databricks_account/databricks_workspace split precedent. The design sketch: a statements resource for arbitrary SQL per the snowflake sqlapi precedent, plus typed resources generated by introspecting system.columns per the gitlab/newrelic introspection precedent. One StackQL session will then query both namespaces - the Cloud control plane and the database itself - where today the equivalent Terraform setup requires two separate providers (the Cloud infrastructure provider and the DBops provider).

Prerequisites

Node.js >= 20
A local stackql binary for testing ($STACKQL, ./stackql, or on PATH)
A ClickHouse Cloud API key pair (Key ID and Key Secret) for live smoke tests - create one in the ClickHouse Cloud console under Settings -> API Keys

Install dependencies:

npm install

Makefile

Every step below is wrapped as a make target (GNU make, bash; runs under Linux, WSL and macOS). make help lists them; the two composite targets are:

make all      # deps, full pipeline (fetch/pin, inventory, split, mappings, normalize, generate),
              # offline + integration + meta-route tests, docs generation, website build
make smoke    # live smoke suite against the dev organization (sources .env if present)

make all never bills - the live suites are separate targets (smoke, smoke-service for the billable service lifecycle, smoke-public against the published provider, smoke-cleanup to sweep breadcrumbs). Live credentials are read from the environment or a gitignored .env file:

CLICKHOUSE_CLOUD_API_KEY=...      # Key ID
CLICKHOUSE_CLOUD_API_SECRET=...   # Key Secret
CLICKHOUSE_ORG_ID=...             # organization ID (x-stackQL-envVar target)

0. Download and Pin the Spec

The ClickHouse Cloud API serves its own OpenAPI spec as JSON at the API base, https://api.clickhouse.cloud/v1 (unauthenticated). The spec URL is not versioned, so the snapshot in provider-dev/downloaded/ and the content hash pin in provider-dev/config/spec_pin.json are the record of what was built.

npm run fetch-spec

The script downloads the spec, validates it with @apidevtools/swagger-parser, and verifies the content hash against the pin. If upstream has changed, the script fails without writing; re-run with --update to accept the refresh and review the resulting diff:

npm run fetch-spec -- --update

ClickHouse notes that the API evolves and consumers may need to adjust - the pin-and-diff discipline is the answer to that caveat.

The written snapshot is sanitized deterministically: vendor example values that pattern-match real credentials (the Slack webhook URL examples in the ClickStack webhook schemas) are replaced with an inert placeholder, since they trip secret-scanning push protection in every artifact that embeds them. The pin records the raw upstream hash (drift is always compared against upstream), the sanitized hash of the file on disk, and the redaction count.

1. Endpoint Inventory and Service Split

npm run build-inventory

Builds provider-dev/config/endpoint_inventory.csv from the pinned spec: one row per operation with the response envelope check, request body and array-operation (add/remove) PATCH fields, the vendor's beta tier, pagination-style query parameters, the proposed service from the path rules in provider-dev/config/service_names.json, a draft resource and StackQL verb, and a skip reason where applicable. The script fails without writing if any path lacks a service rule or a mapped operation deviates from the $.result envelope.

Inventory results for the pinned spec (145 operations, spec refreshed 2026-08-17; the previous pin of 2026-07-12 carried 110):

Disposition: 139 mapped; 6 skipped with reason codes (4 prometheus_text_metrics - the Prometheus scrape endpoints return text/plain; 1 non_json_response_pem - the Postgres CA certificate read returns application/x-pem-file; 1 deprecated_superseded - PATCH .../scaling, replaced by PATCH .../replicaScaling)
Envelope: uniform with one reason-coded exception - every mapped JSON response wraps its payload in $.result (20 collection reads as result-array, 96 single reads and writes as result-object); the 23 status-only responses are all DELETE operations returning {status, requestId}, which need no object key. The exception is GET .../prometheus/discovery, a bare JSON array by definition (Prometheus HTTP service discovery format), recorded in ENVELOPE_DEVIATIONS and wrapped by the normalize pass
Pagination: one cursor-paginated surface - the UDF lists (/udfs, .../versions, .../attachments) take cursor/limit and return $.result.pagination.nextCursor; the udfs service carries a document-level x-stackQL-config pagination block (post-process step) so StackQL follows the cursor. Every other collection is bounded and complete; limit/offset windowing parameters on activeBalances, the ClickStack alert/webhook/saved-search lists, Postgres slowQueryPatterns and logs are plain query parameters usable in the WHERE clause (no traversal to configure)
Beta: 78 operations carry the vendor's beta labelling, in two tiers mirrored in the inventory - 53 beta-stable ("API contract is stable": ClickStack, Postgres, backup bucket, ClickPipes schema discovery, quotas) and 25 beta-evolving ("contract may change": clickhouseSettings, scalingSchedule, Postgres prometheus, UDFs, Prometheus discovery, active balances)
Array-operation PATCH semantics: the service PATCH takes add/remove arrays for ipAccessList, privateEndpointIds and tags; the organization PATCH takes them for privateEndpoints (vendor-deprecated in favour of the service-level field). The API key PATCH ipAccessList is plain replacement.

Service Split

The split is recorded as ordered path rules in provider-dev/config/service_names.json (first match wins), shared by the inventory and the split step. The vendor's tags are too coarse to split on - Organization spans organizations, BYOC and private endpoints, and Service spans the whole service surface.

Service	Surface	Mapped ops
`clickstack`	dashboards (incl. validate), alerts, sources, webhooks, roles, saved searches	32
`services`	services, state, replica scaling, password, private endpoints, query endpoint, scaling schedule, upgrade window, ClickHouse settings	24
`clickpipes`	ClickPipes, settings, scaling, state, CDC scaling, schema discovery, reverse private endpoints	17
`postgres`	Managed Postgres services, config, metrics, slow query patterns, logs	16
`organizations`	organizations, activities, usage cost, active balances, quotas, Prometheus scrape targets, org private endpoint config, BYOC infrastructure	15
`udfs`	user-defined functions, versions, service attachments, upload URLs	12
`backups`	backups, backup configuration, backup bucket	8
`members`	members, invitations	8
`roles`	organization RBAC roles	5
`keys`	API keys	5

Decisions taken from the inventory: roles, clickpipes and (from the 2026-08-17 refresh) udfs are dedicated services (surfaces not in the original candidate list; roles are referenced by both keys and members, so neither absorbs them); there is no network service (the only org-level private endpoint resource is a single deprecated GET, folded into organizations); BYOC infrastructure folds into organizations (three write-only operations, no reads); ClickStack is a dedicated service with unprefixed resource names (clickhouse.clickstack.dashboards).

2. Split into Service Specs

npm run split -- --provider-name clickhouse --overwrite

Splits the pinned spec into per-service OpenAPI specs in provider-dev/source/ using the path rules in provider-dev/config/service_names.json. A path with no rule fails the run without writing. After the split every service is rebased onto the organization-scoped server template in provider-dev/config/servers.json (the single source of truth for the template, shared with the Makefile): paths lose the /v1/organizations/{organizationId} prefix and the organizationId path parameter, and the {organization_id} server variable (snake_case like the rest of the surface - server variables sit outside the nativeCasing reverse lookup) carries x-stackQL-envVar: CLICKHOUSE_ORG_ID. The two organization-root paths are the exception - they keep their full path and are pinned back to the API base in the post-process step (the normalize step strips path-level servers, so it cannot happen earlier). Pass --services to split a subset:

npm run split -- --provider-name clickhouse --services services,keys,organizations --overwrite

3. Generate Mappings

npm run generate-mappings -- --provider-name clickhouse --input-dir provider-dev/source --output-dir provider-dev/config
npm run map-operations

generate-mappings (provider-utils analyze) writes the skeleton provider-dev/config/all_services.csv; map-operations populates stackql_resource_name, stackql_method_name, stackql_verb and stackql_object_key deterministically. Manual mapping decisions are rules in provider-dev/scripts/map_operations.mjs, never CSV edits. The script validates before writing: every operation mapped or reason-coded, method names unique per resource, and unique required-parameter signatures per SQL verb - it fails without writing on any violation.

Mapping conventions:

Operation pattern	StackQL verb	Resource / method
GET collection (`$.result` array)	`SELECT`	`<resource>.list`, objectKey `$.result`
GET single / singleton config	`SELECT`	`<resource>.get`, objectKey `$.result`
POST create	`INSERT`	`<resource>.create`
PATCH / PUT update	`UPDATE`	`<resource>.update` - the service `PATCH` takes `add`/`remove` arrays for `ipAccessList`, `privateEndpointIds` and `tags`
DELETE	`DELETE`	`<resource>.delete`
PATCH `.../state`, `.../password`	`EXEC`	`services.update_state` (with `@command`), `services.update_password`
Prometheus text endpoints, PEM certificate read, deprecated `.../scaling`	skipped	reason-coded in the inventory

Mapping results (all ten services, 145 operations): 60 SELECT, 22 INSERT, 25 UPDATE, 23 DELETE, 9 EXEC, 6 skipped. 44 resources:

services: services, replica_scalings, scaling_schedules, upgrade_windows, clickhouse_settings, clickhouse_settings_schemas, private_endpoints, private_endpoint_configs, service_query_endpoints
organizations: organizations, activities, usage_costs, active_balances, quotas, prometheus_scrape_targets, private_endpoint_configs, byoc_infrastructures
keys: keys; roles: roles; members: members, invitations
backups: backups, backup_configurations, backup_buckets
clickpipes: clickpipes, settings, scalings, cdc_scalings, reverse_private_endpoints
clickstack: dashboards, alerts, sources, webhooks, roles, saved_searches
postgres: services, configs, metrics, slow_query_patterns, logs
udfs: functions, versions, attachments, upload_urls

organizations.usage_costs.list projects $.result.costs (the per-entity cost rows) rather than $.result (a wrapper carrying grandTotalCHC plus the array) so FinOps queries are row-oriented; active_balances.list ($.result.prepaidBalances) and the three UDF lists ($.result.items) follow the same rule. dashboards.validate is the one POST action mapped as EXEC outside the state/password/restore family.

4. Normalize the Service Specs

node provider-dev/scripts/pre_normalize.mjs
npm run normalize -- --api-dir provider-dev/source

pre_normalize.mjs applies the ClickHouse-specific adjustments before the generic provider-utils pass: the vendor spec is OpenAPI 3.1.2 and uses type arrays (type: [string, "null"] for nullable scalars, type: [string, integer] for dual-typed ids - 270 occurrences) and numeric exclusiveMinimum (25); StackQL's OpenAPI loader (any-sdk on kin-openapi v0.88, Type string, ExclusiveMin bool) cannot unmarshal either, so type arrays are lowered to their first non-null member with nullable: true recorded, and numeric exclusive bounds become minimum plus the boolean flag. The openapi version string is set to 3.1.1 because the docgen dereferencer (@apidevtools/swagger-parser v12) rejects 3.1.2 by string match. The generic pass then flattens allOf and renames oneOf/anyOf (112, 67 and 7 occurrences, mostly ClickPipes and ClickStack polymorphism), wraps the one bare-array response, so deeply nested objects (tile configs, scaling blocks, ipAccessList) land as JSON-blob columns addressed with json_extract.

5. Generate the Provider

make generate

which runs:

rm -rf provider-dev/openapi/*
npm run generate-provider -- \
  --provider-name clickhouse \
  --input-dir provider-dev/source \
  --output-dir provider-dev/openapi/src/clickhouse \
  --config-path provider-dev/config/all_services.csv \
  --servers "$(tr -d '\n' < provider-dev/config/servers.json)" \
  --provider-config '{"auth": {"type": "basic", "username_var": "CLICKHOUSE_CLOUD_API_KEY", "password_var": "CLICKHOUSE_CLOUD_API_SECRET"}, "snake_case_aliases": true}' \
  --naive-req-body-translate \
  --overwrite
node provider-dev/scripts/post_process.mjs

No pagination config is passed on the command line - every list endpoint outside the UDF surface returns the complete bounded collection (confirmed by the inventory); the UDF cursor pagination is added to udfs.yaml by the post-process step. --naive-req-body-translate exposes top-level request body properties as columns, so INSERT INTO clickhouse.keys.keys (name, assigned_role_ids, state) ... and EXEC clickhouse.services.services.update_state @serviceId = '...', @command = 'stop' render the wire bodies as written (snake INSERT columns resolve to the camelCase body attributes through request.nativeCasing: camel). post_process.mjs pins the two organization-root path items in organizations.yaml to https://api.clickhouse.cloud with a path-level servers override (any-sdk resolves servers operation -> path item -> document), adds the UDF cursor pagination config (cursor query token / $.result.pagination.nextCursor) to udfs.yaml, sets request.nativeCasing: camel on all 139 methods, and validates that every other path is org-relative.

Server parameters

The only server variable is organization_id. With CLICKHOUSE_ORG_ID exported it is resolved automatically:

SELECT name, state, provider, region FROM clickhouse.services.services;

A WHERE organization_id = '...' value takes precedence over the environment (one session, several organizations); with the variable unset the parameter is required and listed by SHOW METHODS. SELECT id, name FROM clickhouse.organizations.organizations needs neither.

Authentication

Provider config: {"auth": {"type": "basic", "username_var": "CLICKHOUSE_CLOUD_API_KEY", "password_var": "CLICKHOUSE_CLOUD_API_SECRET"}} - the plaintext Key ID / Key Secret pair, base64-encoded by StackQL at request time; the established registry pattern (confluent, kafka, fivetran, sumologic, github). Different variable names can be passed at runtime with --auth='{"clickhouse": {"type": "basic", "username_var": "...", "password_var": "..."}}'.

6. Test the Provider

Four layers, in order. Every regeneration is followed by the first three before commit (make test); the fourth is live.

Validate offline

make test-offline          # node tests/offline_validation.mjs

SHOW SERVICES / SHOW RESOURCES / SHOW METHODS and DESCRIBE EXTENDED against the local file registry - asserts the ten services and 44 resources, the UDF pagination projection, the bare-array wrap on prometheus_scrape_targets, the verb mapping on services.services, that organization_id is required only when CLICKHOUSE_ORG_ID is unset, the snake_case column aliases, that usage_costs projects the cost rows, and that the ClickStack dashboard PUT requires name and tiles (full replacement).

Meta-route test suite

make test-meta             # npm run start-server / test-meta-routes -- clickhouse / stop-server

Walks every service, resource and method over a local wire server: 10 services, 44 resources, 139 methods, 60 selectable, no failures.

Integration tests (mock ClickHouse Cloud API - no organization required)

make test-integration      # add -- --verbose for per-query output

Runs the provider against an in-process mock of the ClickHouse Cloud API (tests/integration/mock_clickhouse_server.mjs) serving redacted live wire shapes - the $.result envelope, status-only DELETE responses, the {requestId, error, status} error envelope and the rate-limit headers - and enforcing basic auth. The runner materialises a test copy of the registry with the server URLs pointed at the mock (server variables and the x-stackQL-envVar extension preserved) and asserts 42 row-level checks: $.result unwrapping for list and single reads, $.result.costs on usage_costs, the basic-auth header, CLICKHOUSE_ORG_ID resolution vs a WHERE organization_id override vs the unset failure mode, snake_case WHERE/INSERT keys resolving to the camelCase wire, the organization-root paths on their path-level server, a full API key INSERT / UPDATE / DELETE lifecycle, the EXEC state command wire body ({"command": "stop"}), an ipAccessList add/remove array-patch UPDATE, a ClickStack dashboard round trip, and the 404 error envelope.

Smoke tests (live)

make smoke                 # reads + API key lifecycle
make smoke-service         # additionally the billable service create / stop / delete lifecycle
make smoke-public          # against the published provider (post-publish verification)
make smoke-cleanup         # sweep stackql-smoke-* keys and services

tests/smoke_test.py (pystackql) runs against a dedicated dev organization: read smokes (organizations, the services estate inventory, members, invitations, keys, roles, quotas, activities, usage cost, backups) and a disposable write lifecycle - an API key INSERT (using assigned_role_ids from roles.roles; organizations migrated to Custom Roles reject the legacy roles field), SELECT, UPDATE (state and ip_access_list replacement) and DELETE. --with-service adds a smallest-footprint service (1 replica x 8 GB, idle after 5 minutes) created, patched, stopped with EXEC update_state @command = 'stop', and deleted within the run. Everything is named stackql-smoke-<stamp>; the run sweeps breadcrumbs first, so a failed run cannot leave a billable service behind past the next run. Statements are paced at 1.2 s (under 10 per 10 s); a 429 fails the run. The harness upgrades pystackql's managed stackql binary to >= v0.10.601 (the x-stackQL-envVar release) when it is older. Never run this against a production organization.

UAT

Test the provider locally:

set -a; source .env; set +a
REG_ROOT="$(pwd)/provider-dev/openapi"
REG="{\"url\":\"file://${REG_ROOT}\",\"localDocRoot\":\"${REG_ROOT}\",\"verifyConfig\":{\"nopVerify\":true}}"
stackql --registry="${REG}" shell

CI

.github/workflows/build-and-test.yml: pin check + build + generation-drift check, offline validation, integration tests, meta-route tests and docs generation on every push and PR; the secret-gated live smoke suite (reads + key lifecycle, never the service lifecycle) on pushes; and a weekly spec-drift job that fetches the served spec, compares it with the pin, and opens a spec-drift issue when it moves. The web workflows build and deploy the microsite from main.

7. Publish the Provider

To publish, push the clickhouse dir to providers/src in a feature branch of the stackql-provider-registry and follow the registry release flow. Pull and verify from the dev registry:

export DEV_REG="{ \"url\": \"https://registry-dev.stackql.app/providers\" }"
stackql --registry="${DEV_REG}" shell

registry pull clickhouse;

then make smoke-public.

8. Generate Web Docs

The doc microsite (website/) is Docusaurus 3.10 and follows the shared architecture used by the other provider microsites: navbar/footer/theme/plugin configuration lives in stackql/docusaurus-config, vendored into .shared-config/ at build time. Site-local files are limited to the provider identity (website/provider.js: providerName = 'clickhouse', providerTitle = 'ClickHouse Cloud'), thin wrappers, the shared components/theme under src/, and static assets including static/CNAME (clickhouse-provider.stackql.io).

make docs        # generate-docs --snake-case-aliases (provider-utils >= 0.7.8) + website/scripts/sanitize-docs.mjs
make website     # yarn install && yarn build (vendors the shared config; needs GitHub access)
make website-start

headerContent1.txt / headerContent2.txt in provider-dev/docgen/provider-data/ supply the landing page: installation, scope and the clickhouse_server roadmap, API key creation with role guidance, the env var convention, organization scope, the rate limit, beta labelling and the example queries (estate inventory, usage cost by day and entity, idle-service detection, keys by age, member audit, backup coverage, lifecycle EXEC, ipAccessList array patch, ClickStack dashboards as code, and the cross-provider data platform estate query). sanitize-docs.mjs escapes MDX-hostile description text and applies two clickhouse-specific rewrites: every generated organization_id example is annotated "required unless CLICKHOUSE_ORG_ID is set", and organizations.list (which addresses the bare API base) loses the parameter that docgen inferred from the document-level server.

To publish, select GitHub Actions as the Pages source and create the DNS record (the served hostname is pinned by website/static/CNAME):

Source Domain	Record Type	Target
clickhouse-provider.stackql.io	CNAME	stackql.github.io.

License

MIT License - see LICENSE.

Contributing

Contributions are welcome. Please open an issue or pull request.

Name		Name	Last commit message	Last commit date
.github/workflows		.github/workflows
bin		bin
provider-dev		provider-dev
tests		tests
website		website
.gitattributes		.gitattributes
.gitignore		.gitignore
.npmrc		.npmrc
CLAUDE.md		CLAUDE.md
LICENSE		LICENSE
Makefile		Makefile
NOTES.md		NOTES.md
README.md		README.md
package-lock.json		package-lock.json
package.json		package.json

Navigation Menu

Uh oh!

Repository files navigation

`clickhouse` provider for `stackql`

Design Principles

Roadmap: the `clickhouse_server` sibling provider

Prerequisites

Makefile

0. Download and Pin the Spec

1. Endpoint Inventory and Service Split

Service Split

2. Split into Service Specs

3. Generate Mappings

4. Normalize the Service Specs

5. Generate the Provider

Server parameters

Authentication

6. Test the Provider

Validate offline

Meta-route test suite

Integration tests (mock ClickHouse Cloud API - no organization required)

Smoke tests (live)

UAT

CI

7. Publish the Provider

8. Generate Web Docs

License

Contributing

About

Releases

Packages

Contributors

Languages

Uh oh!

Latest commit

History

Folders and files

Repository files navigation

clickhouse provider for stackql

Design Principles

Roadmap: the clickhouse_server sibling provider

Prerequisites

Makefile

0. Download and Pin the Spec

1. Endpoint Inventory and Service Split

Service Split

2. Split into Service Specs

3. Generate Mappings

4. Normalize the Service Specs

5. Generate the Provider

Server parameters

Authentication

6. Test the Provider

Validate offline

Meta-route test suite

Integration tests (mock ClickHouse Cloud API - no organization required)

Smoke tests (live)

UAT

CI

7. Publish the Provider

8. Generate Web Docs

License

Contributing

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

`clickhouse` provider for `stackql`

Roadmap: the `clickhouse_server` sibling provider