6 posts tagged with "finops"

We've released a new StackQL provider for Oracle Cloud Infrastructure:

• oci - the OCI control plane across identity, compute, networking, storage, database, containers, security, observability and cost services: identity, compute, network, block_storage, object_storage, database, container_engine, load_balancer, dns, kms, vault, secrets, monitoring, logging, events, functions, resource_manager, streaming, budgets, usage, audit and work_requests (22 services, 452 resources, 1,544 operations)

This completes StackQL's hyperscaler coverage alongside aws, azure and google: the same SQL surface now spans all four clouds for inventory, audit and FinOps queries.

The provider covers the full lifecycle on the tier-1 services: SELECT across the estate, INSERT, UPDATE and DELETE on VCNs, subnets, instances, volumes, buckets, databases and policies, and EXEC for actions such as instance power actions and Autonomous Database start and stop. Columns and WHERE/INSERT keys are snake_case over OCI's camelCase wire format, nested detail objects are JSON columns addressed with json_extract, and SQL LIMIT pushes down to the OCI limit query parameter.

Connect​

OCI API requests are signed with an API key (the draft-cavage HTTP signature scheme). The signing is built into the engine as the oci_signing_v1 auth type, available from stackql v0.12.732, so there is nothing to install beyond stackql itself. The provider reads the same API key credential the OCI CLI and Terraform use:

export OCI_TENANCY=ocid1.tenancy.oc1..aaaa...
export OCI_USER=ocid1.user.oc1..aaaa...
export OCI_FINGERPRINT=aa:bb:cc:...
export OCI_KEY_FILE=~/.oci/oci_api_key.pem
export OCI_REGION=ap-sydney-1
stackql shell

These names are the provider's defaults, so no --auth argument is needed. With none of them set, the standard ~/.oci/config file is read (DEFAULT profile), which is the file the OCI CLI and Terraform already share, so an environment configured for either tool works unchanged. A --auth context can point the *_env_var keys at other names (Terraform's TF_VAR_*, for example) or at a different config file and profile; a runtime context always wins over the defaults. OCI_REGION resolves the regional service endpoints and can be overridden per query in the WHERE clause.

The compartment scope pattern​

Nearly every list operation in OCI is scoped to a compartment, so compartment_id is the universal WHERE key. The tenancy OCID is itself a compartment ID (the root), and the identity.compartments resource enumerates the compartments beneath it, the natural driving table for estate-wide joins:

SELECT id, name, lifecycle_state
FROM oci.identity.compartments
WHERE compartment_id = 'ocid1.tenancy.oc1..aaaa...';

Compute and network estate queries​

Nested details come back as JSON columns, one json_extract away. Instance shape configuration is the usual example:

SELECT
  display_name,
  shape,
  json_extract(shape_config, '$.ocpus') AS ocpus,
  json_extract(shape_config, '$.memoryInGBs') AS memory_gb,
  lifecycle_state
FROM oci.compute.instances
WHERE compartment_id = 'ocid1.compartment.oc1..aaaa...';

The same pattern applies across the network estate. VCNs, subnets, security lists and network security groups are all queryable per compartment and joinable on OCIDs:

SELECT display_name, cidr_block, dns_label, lifecycle_state
FROM oci.network.vcns
WHERE compartment_id = 'ocid1.compartment.oc1..aaaa...';

IAM audit​

Policy statements are a column, so a tenancy-wide policy review is one query:

SELECT name, description, statements
FROM oci.identity.policies
WHERE compartment_id = 'ocid1.tenancy.oc1..aaaa...';

and so is the user review that usually follows it:

SELECT name, email, is_mfa_activated, last_successful_login_time
FROM oci.identity.users
WHERE compartment_id = 'ocid1.tenancy.oc1..aaaa...'
  AND is_mfa_activated = 0;

Provision, mutate and tear down​

Mutations are the usual SQL verbs. Request body attributes use the same snake_case names as the columns:

-- create
INSERT INTO oci.network.vcns (compartment_id, cidr_block, display_name)
SELECT 'ocid1.compartment.oc1..aaaa...', '10.0.0.0/16', 'my-vcn';

-- partial update
UPDATE oci.network.vcns
SET display_name = 'my-vcn-renamed'
WHERE vcn_id = 'ocid1.vcn.oc1.ap-sydney-1.aaaa...';

-- remove it
DELETE FROM oci.network.vcns
WHERE vcn_id = 'ocid1.vcn.oc1.ap-sydney-1.aaaa...';

Actions map to EXEC, with the wire parameter names:

EXEC oci.compute.instances.instance_action
  @instanceId = 'ocid1.instance.oc1.ap-sydney-1.aaaa...',
  @action = 'STOP';

Mutating operations return an opc-work-request-id, and the work_requests service exposes the work request and its log for polling.

FinOps across four clouds​

Cost governance surfaces are first-class resources. Budget versus actual is a SELECT:

SELECT
  display_name,
  amount,
  actual_spend,
  forecasted_spend,
  alert_rule_count
FROM oci.budgets.budgets
WHERE compartment_id = 'ocid1.tenancy.oc1..aaaa...'
ORDER BY actual_spend DESC;

The usage API is a POST, so a cost-by-service summary is an EXEC whose request body is the same JSON the API takes:

EXEC oci.usage.usages.request_summarized_usages
  @@json = '{
    "tenantId": "ocid1.tenancy.oc1..aaaa...",
    "timeUsageStarted": "2026-09-01T00:00:00Z",
    "timeUsageEnded": "2026-10-01T00:00:00Z",
    "granularity": "MONTHLY",
    "groupBy": ["service"]
  }';

and the audit trail is a time-bounded query against audit.events:

SELECT
  event_time,
  event_type,
  json_extract(data, '$.identity.principalName') AS principal,
  json_extract(data, '$.resourceName') AS resource
FROM oci.audit.events
WHERE compartment_id = 'ocid1.tenancy.oc1..aaaa...'
  AND start_time = '2026-10-01T00:00:00Z'
  AND end_time = '2026-10-06T00:00:00Z';

The query this provider exists for is the four-hyperscaler estate in one statement:

SELECT 'oci' AS provider, display_name AS name, shape AS size, lifecycle_state AS state
FROM oci.compute.instances
WHERE compartment_id = 'ocid1.compartment.oc1..aaaa...'
UNION ALL
SELECT 'aws', instance_id, instance_type, json_extract(state, '$.name')
FROM aws.ec2.instances
WHERE region = 'us-east-1'
UNION ALL
SELECT 'azure', name, json_extract(properties, '$.hardwareProfile.vmSize'), json_extract(properties, '$.provisioningState')
FROM azure.compute.virtual_machines
WHERE subscriptionId = '00000000-0000-0000-0000-000000000000' AND resourceGroupName = 'my-rg'
UNION ALL
SELECT 'google', name, machineType, status
FROM google.compute.instances
WHERE project = 'my-project' AND zone = 'us-central1-a';

The same union shape applies to cost data, giving a four-cloud spend picture from one query, in one tool, with one audit log.

Scope​

This release ships the tier-1 services listed above. OCI has dozens more (AI services, integration, analytics, GoldenGate and the rest of the long tail); those are catalogued and will be added per release cycle. Two things are deliberately out of scope: the object storage data plane (object bodies are streaming operations; object listing and metadata are in) and the per-vault KMS crypto and management endpoints, which live on dedicated hosts the provider cannot route to today. One known engine limit: list operations that paginate with OCI's opc-next-page response header currently return the first page, sized by LIMIT up to the service maximum, while body-token lists such as object listing paginate fully; the engine fix is tracked and the provider needs no change when it lands.

Get started​

The provider needs stackql v0.12.732 or later. Pull it from the public registry:

registry pull oci;

Provider docs are at oci-provider.stackql.io. Let us know what you build. Star us on GitHub.

We've released a new StackQL provider for Railway:

• railway - the Railway public API: projects, environments, services, deployments, variables, networking, storage, billing, observability, workspaces, account, templates, integrations, platform and agents (15 services, 151 resources, 396 operations)

The provider reads and writes. Projects, environments, services, variables, domains and volumes can be queried, created, changed and removed, and deployment operations such as redeploy, restart and rollback are available on the resources they act on. The rest of this post is the questions it answers and the tasks it handles.

Connect​

Authentication is a Railway account token or workspace token, read from RAILWAY_TOKEN - the same variable the Terraform Railway provider uses:

export RAILWAY_TOKEN=...
stackql shell

Tokens are created under Account settings -> Tokens. A project token does not work with the provider. Railway limits API requests per hour by plan (100 on Free, 1000 on Hobby, 10000 on Pro), which is worth keeping in mind for queries that cover many projects or environments.

What is deployed where​

The workspaces a token can reach, the projects in one, and the environments of a project:

SELECT id, name, plan
FROM railway.workspaces.workspaces;

SELECT id, name, description, created_at
FROM railway.projects.projects
WHERE workspace_id = '<workspace-id>'
ORDER BY created_at DESC;

SELECT id, name, is_ephemeral
FROM railway.environments.environments
WHERE project_id = '<project-id>';

A service instance is a service as it is configured in one environment. Listing the instances of an environment shows what runs there, from which image or repository, and how the last deployment went:

SELECT service_name, region, num_replicas,
       json_extract(source, '$.image') AS image,
       json_extract(source, '$.repo') AS repo,
       json_extract(latest_deployment, '$.status') AS last_deployment,
       json_extract(latest_deployment, '$.created_at') AS deployed_at
FROM railway.services.service_instances
WHERE environment_id = '<environment-id>';

An IN list covers several environments in one query, with one request made per environment. Joining to environments puts names on the rows:

SELECT e.name AS environment, s.service_name, s.region, s.num_replicas,
       json_extract(s.latest_deployment, '$.status') AS last_deployment
FROM railway.services.service_instances s
JOIN railway.environments.environments e ON e.id = s.environment_id
WHERE e.project_id = '<project-id>'
  AND s.environment_id IN ('<production-environment-id>', '<staging-environment-id>')
ORDER BY environment, service_name;

The same works across projects, here for the services of two projects in a workspace:

SELECT p.name AS project, s.name AS service, s.created_at
FROM railway.services.services s
JOIN railway.projects.projects p ON p.id = s.project_id
WHERE p.workspace_id = '<workspace-id>'
  AND s.project_id IN ('<project-id>', '<other-project-id>')
ORDER BY project, service;

Deployment health​

Deployment outcomes for a project, then the ones that failed or crashed, with who triggered them:

SELECT status, count(*) AS deployments
FROM railway.deployments.deployments
WHERE project_id = '<project-id>'
GROUP BY status;

SELECT id, service_id, status, created_at,
       json_extract(creator, '$.name') AS deployed_by
FROM railway.deployments.deployments
WHERE project_id = '<project-id>'
  AND status = '{in: [FAILED, CRASHED]}'
ORDER BY created_at DESC;

Services in an environment whose latest deployment is not healthy:

SELECT service_name,
       json_extract(latest_deployment, '$.id') AS deployment_id,
       json_extract(latest_deployment, '$.status') AS status
FROM railway.services.service_instances
WHERE environment_id = '<environment-id>'
  AND json_extract(latest_deployment, '$.status') NOT IN ('SUCCESS', 'SLEEPING');

From there, the build and runtime logs of a deployment are tables. LIMIT sets how many lines are fetched:

SELECT timestamp, severity, message
FROM railway.observability.build_logs
WHERE deployment_id = '<deployment-id>'
LIMIT 100;

SELECT timestamp, severity, message
FROM railway.observability.deployment_logs
WHERE deployment_id = '<deployment-id>'
  AND filter = 'error'
LIMIT 100;

Drift between environments​

Variables are one row per variable, so comparing two environments is a join. Variables a service has in production that are missing in staging:

SELECT prod.name
FROM railway.variables.variables prod
LEFT JOIN railway.variables.variables stg
  ON stg.name = prod.name
WHERE prod.project_id = '<project-id>'
  AND prod.environment_id = '<production-environment-id>'
  AND prod.service_id = '<service-id>'
  AND stg.project_id = '<project-id>'
  AND stg.environment_id = '<staging-environment-id>'
  AND stg.service_id = '<service-id>'
  AND stg.name IS NULL
  AND prod.name NOT LIKE 'RAILWAY_%';

Variables set in both environments with different values:

SELECT prod.name
FROM railway.variables.variables prod
JOIN railway.variables.variables stg
  ON stg.name = prod.name
WHERE prod.project_id = '<project-id>'
  AND prod.environment_id = '<production-environment-id>'
  AND prod.service_id = '<service-id>'
  AND stg.project_id = '<project-id>'
  AND stg.environment_id = '<staging-environment-id>'
  AND stg.service_id = '<service-id>'
  AND prod.value <> stg.value
  AND prod.name NOT LIKE 'RAILWAY_%';

The variables Railway provides itself are prefixed RAILWAY_ and differ between environments by design, so they are left out. Both queries return names only. Selecting value returns the values in clear, so treat that output as a secret.

The same approach compares how a service is configured in each environment:

SELECT prod.service_name,
       prod.num_replicas AS prod_replicas, stg.num_replicas AS staging_replicas,
       prod.region AS prod_region, stg.region AS staging_region,
       prod.start_command AS prod_start_command, stg.start_command AS staging_start_command
FROM railway.services.service_instances prod
JOIN railway.services.service_instances stg
  ON stg.service_id = prod.service_id
WHERE prod.environment_id = '<production-environment-id>'
  AND stg.environment_id = '<staging-environment-id>';

Domains and certificates​

The generated domains of every service in an environment, without going service by service. The custom_domains key of the same column holds the custom ones:

SELECT s.service_name,
       json_extract(d.value, '$.domain') AS domain,
       json_extract(d.value, '$.target_port') AS target_port
FROM railway.services.service_instances s,
     json_each(json_extract(s.domains, '$.service_domains')) d
WHERE s.environment_id = '<environment-id>';

Custom domains carry their verification and certificate state, and the DNS records Railway expects to find:

SELECT domain,
       json_extract(status, '$.verified') AS verified,
       json_extract(status, '$.certificate_status') AS certificate_status,
       json_extract(status, '$.dns_records') AS dns_records
FROM railway.networking.custom_domains
WHERE project_id = '<project-id>'
  AND environment_id = '<environment-id>'
  AND service_id = '<service-id>';

Usage and cost​

Usage for a workspace by measurement, grouped by project, with project names joined in:

SELECT p.name AS project, u.measurement, u.value
FROM railway.billing.usage u
JOIN railway.projects.projects p
  ON p.id = json_extract(u.tags, '$.project_id')
WHERE u.workspace_id = '<workspace-id>'
  AND u.measurements = '[CPU_USAGE, MEMORY_USAGE_GB, NETWORK_TX_GB, DISK_USAGE_GB]'
  AND u.group_by = '[PROJECT_ID]'
  AND p.workspace_id = '<workspace-id>'
ORDER BY project, measurement;

start_date and end_date narrow the window. The projected usage for the current billing period, and the billing position of the workspace:

SELECT project_id, measurement, estimated_value
FROM railway.billing.estimated_usage
WHERE workspace_id = '<workspace-id>'
  AND measurements = '[CPU_USAGE, MEMORY_USAGE_GB]';

SELECT state, current_usage, credit_balance,
       json_extract(billing_period, '$.end') AS period_ends,
       json_extract(usage_limit, '$.hard_limit') AS hard_limit
FROM railway.billing.customers
WHERE workspace_id = '<workspace-id>';

Volumes report their allocated and used size per environment:

SELECT json_extract(volume, '$.name') AS volume, mount_path, region, state,
       size_mb, current_size_mb,
       ROUND(100.0 * current_size_mb / size_mb, 1) AS used_pct
FROM railway.storage.volume_instances
WHERE environment_id = '<environment-id>';

Provisioning​

A project is created with one environment. RETURNING gives back the identifiers the next statements need:

INSERT INTO railway.projects.projects (name, description, workspace_id)
SELECT 'orders', 'order processing', '<workspace-id>'
RETURNING id, name, primary_environment_id;

INSERT INTO railway.environments.environments (project_id, name)
SELECT '<project-id>', 'staging'
RETURNING id, name;

A service from a container image or a repository. A service with a source is deployed when it is created, and a running deployment is billed:

INSERT INTO railway.services.services (project_id, name, source)
SELECT '<project-id>', 'cache', '{"image": "redis:7-alpine"}'
RETURNING id, name;

INSERT INTO railway.services.services (project_id, name, source, branch)
SELECT '<project-id>', 'api', '{"repo": "my-org/orders-api"}', 'main'
RETURNING id, name;

Variables are written one at a time or as a set. Writing a variable that exists replaces its value, and skip_deploys keeps the change from triggering a deployment:

INSERT INTO railway.variables.variables
  (project_id, environment_id, service_id, name, value, skip_deploys)
SELECT '<project-id>', '<environment-id>', '<service-id>', 'LOG_LEVEL', 'info', true;

INSERT INTO railway.variables.variables
  (project_id, environment_id, service_id, variables, skip_deploys)
SELECT '<project-id>', '<environment-id>', '<service-id>',
       '{"SENTRY_DSN": "https://example.ingest.sentry.io/1", "FEATURE_FLAGS": "checkout-v2"}', true;

A generated domain and a volume for the service:

INSERT INTO railway.networking.service_domains (environment_id, service_id)
SELECT '<environment-id>', '<service-id>'
RETURNING id, domain;

INSERT INTO railway.storage.volumes (project_id, environment_id, service_id, mount_path)
SELECT '<project-id>', '<environment-id>', '<service-id>', '/data'
RETURNING id, name;

Day-two operations​

Changing how a service runs in an environment is an UPDATE. Values in SET are written as quoted strings:

UPDATE railway.services.service_instances
SET num_replicas = '2',
    start_command = 'npm run start',
    healthcheck_path = '/healthz'
WHERE service_id = '<service-id>'
  AND environment_id = '<environment-id>';

Deployment operations are EXEC methods. The SHOWRESULTS hint returns the API's answer, including a refusal:

EXEC /*+ SHOWRESULTS */ railway.services.service_instances.redeploy
  @service_id = '<service-id>',
  @environment_id = '<environment-id>';

EXEC /*+ SHOWRESULTS */ railway.deployments.deployments.restart
  @id = '<deployment-id>';

EXEC /*+ SHOWRESULTS */ railway.deployments.deployments.rollback
  @id = '<deployment-id>';

Preview environment cleanup​

Pull request environments accumulate. The ephemeral environments of a project, with the pull request each one belongs to:

SELECT id, name, created_at,
       json_extract(meta, '$.pr_number') AS pr_number,
       json_extract(meta, '$.branch') AS branch
FROM railway.environments.environments
WHERE project_id = '<project-id>'
  AND is_ephemeral = true
ORDER BY created_at;

Removing one is a DELETE. Running the listing again confirms it is gone:

DELETE FROM railway.environments.environments
WHERE id = '<environment-id>';

A join across providers​

Services deployed from a GitHub repository can be matched to the repository itself, for example to find services still deploying from a repository that has been archived or has not been pushed to in some time. This uses the github provider alongside railway:

SELECT s.service_name,
       r.full_name AS repo, r.archived, r.pushed_at
FROM railway.services.service_instances s
JOIN github.repos.repos r
  ON r.full_name = json_extract(s.source, '$.repo')
WHERE s.environment_id = '<environment-id>'
  AND r.org = 'my-org'
ORDER BY r.pushed_at;

Get started​

Pull the provider from the public registry:

registry pull railway;

Provider docs are at railway-provider.stackql.io. Let us know what you build. Star us on GitHub.

We've released an updated StackQL Datadog provider covering the Datadog v1 and v2 REST APIs together: 18 services, 597 resources and 1658 operations, up from 16 services and 575 operations in the previous release.

What's new​

The previous provider was built from the v2 API alone. Datadog's most-used resources - monitors, dashboards, synthetics, SLOs, hosts, log indexes and pipelines - only exist in the v1 API, so this release merges the two specs into one provider. The v2 surface has also grown considerably since the last build. In summary:

• The v1 API: monitors (list, search, create, replace, validate, delete), dashboards and dashboard lists, synthetics tests (API, browser and mobile), locations, private locations and global variables, SLOs and SLO corrections, hosts, host totals and host tags, notebooks, log indexes and pipelines, the Azure, PagerDuty, Slack and webhook integrations, and usage metering.
• New v2 surfaces: cases and case projects, on-call schedules, escalation policies and paging, status pages, incident configuration and responders, feature flags, deployment gates, LLM Observability (projects, datasets, experiments, prompts, annotation queues), Fleet Automation, cloud cost budgets, commitments and tag pipelines, security findings automation, static analysis and SCA, agentless scanning, SIEM historical detections, RUM replay and product analytics, reference tables, org groups and personal access tokens, among others.
• Site from the environment: the provider addresses https://api.{site}, and site is resolved from DD_SITE when it is set (datadoghq.eu, us5.datadoghq.com, ap2.datadoghq.com, ...), the same convention as the Datadog Agent and API clients. Queries carry no site clause; a WHERE site = '...' still wins for one statement.
• Pagination and pushdown: cursor-paginated lists (audit events, container images, spans, RUM events, CI events, security signals and findings) are traversed transparently, and a SQL LIMIT is sent as the API's page-size parameter.
• snake_case surface: columns and WHERE / INSERT keys are snake_case throughout; the few camelCase wire names are aliased.
• Terraform-aligned authentication: DD_API_KEY and DD_APP_KEY, unchanged.

Service highlights​

Authentication​

Export an API key and an application key; set DD_SITE if your organization is not on datadoghq.com:

export DD_API_KEY=...
export DD_APP_KEY=...
export DD_SITE=datadoghq.eu     # optional, defaults to datadoghq.com

Monitors​

Every monitor with its state:

SELECT id, name, type, overall_state, tags
FROM datadog.monitoring.monitors;

Only alerting monitors, using the API's own filter:

SELECT id, name, overall_state
FROM datadog.monitoring.monitors
WHERE group_states = 'alert';

Monitor search, with the same syntax as the Manage Monitors page:

SELECT id, name, status, type
FROM datadog.monitoring.monitor_search_results
WHERE query = 'type:metric status:alert';

Dashboards, SLOs and synthetics​

SELECT id, title, layout_type, author_handle, modified_at
FROM datadog.dashboards.dashboards;

SELECT id, name, type, target_threshold, timeframe
FROM datadog.service_management.slos;

SELECT public_id, name, type, status, locations
FROM datadog.monitoring.synthetics_tests;

Users, roles and keys​

v2 resources return the JSON:API row shape - id, type, attributes, relationships - so attributes are one json_extract away. A user audit:

SELECT id,
       json_extract(attributes, '$.email') AS email,
       json_extract(attributes, '$.status') AS status,
       json_extract(attributes, '$.disabled') AS disabled,
       json_extract(attributes, '$.created_at') AS created_at
FROM datadog.organization.users;

API keys by age, the input to a rotation policy:

SELECT id,
       json_extract(attributes, '$.name') AS name,
       json_extract(attributes, '$.created_at') AS created_at,
       json_extract(attributes, '$.last4') AS last4
FROM datadog.organization.api_keys
ORDER BY created_at;

Infrastructure and logs​

Hosts reporting to Datadog, and the log indexes with their retention:

SELECT host_name, up, is_muted, apps, last_reported_time
FROM datadog.infrastructure.hosts;

SELECT name, num_retention_days, daily_limit
FROM datadog.logs.indexes;

Audit log​

The audit event list is cursor-paginated and takes the time window as a query parameter:

SELECT json_extract(attributes, '$.timestamp') AS timestamp,
       json_extract(attributes, '$.attributes.evt.name') AS event,
       json_extract(attributes, '$.attributes.usr.email') AS actor
FROM datadog.organization.audit_logs
WHERE "filter[from]" = 'now-1d';

Security monitoring rules​

Which detection rules are enabled, and who last changed them:

SELECT id, name, type, is_enabled, is_default, updated_at, update_author_id
FROM datadog.security.monitoring_rules
WHERE is_default = false;

Provisioning​

Mutations use the same SQL grammar. v1 resources take their fields as columns; v2 resources take the JSON:API data document. A monitor end to end - validate the definition, create it, replace it (the v1 monitor API updates with PUT), delete it:

EXEC datadog.monitoring.monitors.validate_monitor
  @type = 'metric alert',
  @query = 'avg(last_5m):avg:system.cpu.user{env:prod} by {host} > 90',
  @name = 'High CPU on prod hosts';

INSERT INTO datadog.monitoring.monitors (name, type, query, message, tags)
SELECT 'High CPU on prod hosts',
       'metric alert',
       'avg(last_5m):avg:system.cpu.user{env:prod} by {host} > 90',
       'CPU above 90% on {{host.name}} @slack-ops',
       '["team:web", "managed-by:stackql"]';

REPLACE datadog.monitoring.monitors
SET name = 'High CPU on prod hosts', type = 'metric alert',
    query = 'avg(last_5m):avg:system.cpu.user{env:prod} by {host} > 95'
WHERE monitor_id = 12345678;

DELETE FROM datadog.monitoring.monitors
WHERE monitor_id = 12345678;

A downtime for a release window, and a role:

INSERT INTO datadog.service_management.downtimes (data)
SELECT '{"type": "downtime",
         "attributes": {"message": "release window", "scope": "env:prod",
                        "monitor_identifier": {"monitor_tags": ["team:web"]},
                        "schedule": {"start": "2026-09-01T22:00:00Z", "end": "2026-09-01T23:00:00Z"}}}';

INSERT INTO datadog.organization.roles (data)
SELECT '{"type": "roles", "attributes": {"name": "read-only-auditors"}}';

UPDATE datadog.organization.roles
SET data = '{"id": "<role-id>", "type": "roles", "attributes": {"name": "auditors"}}'
WHERE role_id = '<role-id>';

Get started​

Pull the provider from the public registry:

registry pull datadog;

Provider docs are at datadog-provider.stackql.io. Let us know what you build. Star us on GitHub.

We've released a new StackQL provider for ClickHouse Cloud:

• clickhouse - the ClickHouse Cloud control plane (api.clickhouse.cloud): organizations, services (lifecycle, scaling, settings, passwords, private endpoints), API keys, members and invitations, organization roles, backups and backup configuration, usage cost and quotas, activities, ClickPipes, the ClickStack surface (dashboards, alerts, sources, webhooks, saved searches), user-defined functions and Managed Postgres (10 services, 44 resources, 139 operations)

The provider covers the Cloud management API only. The ClickHouse server HTTP interface - SQL against a service endpoint and the system.* tables - is a separate surface with separate authentication and is reserved as a future sibling provider, clickhouse_server. What takes two Terraform providers today (the Cloud infrastructure provider and the DBops provider) will be two namespaces in one StackQL session.

Organization scope from the environment​

Every resource except organizations is scoped to an organization. The organization ID is a server variable that StackQL resolves from CLICKHOUSE_ORG_ID when it is set, so queries carry no organization clause:

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

A WHERE organization_id = '...' still takes precedence when you need to address another organization in the same session. Columns and WHERE/INSERT keys are snake_case (created_at, ip_access_list, service_id); the provider maps them to the API's camelCase on the wire.

Estate inventory​

State, footprint and scaling configuration for every service in one result set:

SELECT name, state, provider, region,
       num_replicas,
       min_replica_memory_gb, max_replica_memory_gb,
       json_extract(current_scaling, '$.effectiveAutoscalingMode') AS scaling_mode,
       idle_scaling, idle_timeout_minutes, clickhouse_version
FROM clickhouse.services.services
ORDER BY provider, region, name;

Organization quotas report usage against limits, which is the quickest answer to "how much room is left":

SELECT quota_code, name, value AS quota_limit, usage
FROM clickhouse.organizations.quotas;

Usage cost by day and entity​

The usage cost endpoint returns one row per entity per day in ClickHouse Credits, with the compute, storage, backup and data-transfer components in a metrics object:

SELECT date, entity_type, entity_name, total_chc,
       json_extract(metrics, '$.computeCHC') AS compute_chc,
       json_extract(metrics, '$.storageCHC') AS storage_chc,
       json_extract(metrics, '$.backupCHC')  AS backup_chc
FROM clickhouse.organizations.usage_costs
WHERE from_date = '2026-08-01' AND to_date = '2026-08-31'
ORDER BY date, entity_name;

Joined to the services list, the same data answers which stopped or idle services still carried cost in the window:

SELECT s.name, s.state, SUM(c.total_chc) AS chc_in_window
FROM clickhouse.services.services s
JOIN clickhouse.organizations.usage_costs c
  ON c.service_id = s.id
WHERE c.from_date = '2026-08-01' AND c.to_date = '2026-08-31'
  AND s.state IN ('stopped', 'idle')
GROUP BY s.name, s.state
ORDER BY chc_in_window DESC;

Scaffolding​

Provisioning is the usual SQL verbs. A service is an INSERT; the state command (start, stop, awake) is an EXEC; access-list changes are an UPDATE whose PATCH body takes add/remove arrays, passed through as written:

INSERT INTO clickhouse.services.services
  (name, provider, region, min_replica_memory_gb, max_replica_memory_gb, num_replicas, idle_scaling, idle_timeout_minutes)
SELECT 'analytics-dev', 'aws', 'us-east-1', 8, 8, 1, true, 15;

UPDATE clickhouse.services.services
SET ip_access_list = '{"add": [{"source": "203.0.113.0/24", "description": "office"}],
                       "remove": [{"source": "0.0.0.0/0", "description": "Anywhere"}]}'
WHERE service_id = '<service-uuid>';

EXEC clickhouse.services.services.update_state
  @serviceId = '<service-uuid>',
  @command = 'stop';

Organizations that use Custom Roles assign API key roles by ID, so the role lookup and the key creation are one statement, and the generated secret comes back with RETURNING:

INSERT INTO clickhouse.keys.keys (name, assigned_role_ids, state)
SELECT 'finops-reader', '["' || id || '"]', 'enabled'
FROM clickhouse.roles.roles
WHERE name = 'Organization API Reader'
RETURNING json_extract(result, '$.key.id')    AS id,
          json_extract(result, '$.keyId')     AS key_id,
          json_extract(result, '$.keySecret') AS key_secret;

Backup configuration is queryable across the estate, and settable per service:

SELECT s.name, b.backup_period_in_hours, b.backup_retention_period_in_hours, b.backup_start_time
FROM clickhouse.services.services s
JOIN clickhouse.backups.backup_configurations b
  ON b.service_id = s.id;

Observability as code​

ClickStack dashboards, alerts, sources, webhooks and saved searches are resources with full CRUD, so a dashboard definition lives in version control and is applied with an INSERT:

INSERT INTO clickhouse.clickstack.dashboards (service_id, name, tiles, tags)
SELECT '<service-uuid>', 'Service Overview',
       '[{"name": "Error rate", "x": 0, "y": 0, "w": 6, "h": 3,
          "config": {"displayType": "line",
                     "select": [{"aggFn": "count", "where": "SeverityText = ''ERROR''"}]}}]',
       '["production"]';

and audited with a SELECT:

SELECT d.name, json_array_length(d.tiles) AS tiles, d.tags
FROM clickhouse.clickstack.dashboards d
WHERE d.service_id = '<service-uuid>';

Alerts and webhooks follow the same pattern (clickhouse.clickstack.alerts, clickhouse.clickstack.webhooks), and the organization activity log is a table for audit questions:

SELECT created_at, type, actor_type, actor_details
FROM clickhouse.organizations.activities
ORDER BY created_at DESC;

The data platform estate in one query​

Cross-provider joins are ordinary SQL, so ClickHouse Cloud services sit alongside Snowflake warehouses and Databricks clusters in one inventory:

SELECT 'clickhouse' AS platform, name, state, region
FROM clickhouse.services.services
UNION ALL
SELECT 'snowflake', name, state, NULL
FROM snowflake.warehouses.warehouses
UNION ALL
SELECT 'databricks', cluster_name, state, NULL
FROM databricks_workspace.compute.clusters
WHERE deployment_name = '<workspace>';

Authentication​

Create an API key pair in the ClickHouse Cloud console (Settings -> API Keys) with the role the queries need - Organization API Reader / Service API Reader for inventory and cost queries, Service API Admin for provisioning - and export three variables:

export CLICKHOUSE_CLOUD_API_KEY=...      # Key ID
export CLICKHOUSE_CLOUD_API_SECRET=...   # Key Secret
export CLICKHOUSE_ORG_ID=...             # organization ID

The API allows a fixed window of requests per key (documented as 10 per 10 seconds); a 429 is the signal to pace wide scans.

Get started​

Pull the provider from the public registry:

registry pull clickhouse

Provider docs are at clickhouse-provider.stackql.io. Let us know what you build. Star us on GitHub.

We've released an update to the StackQL providers for the OpenAI platform:

• openai - the platform API surface available to standard API keys: models, files, fine-tuning, batches, vector stores, assistants, evals, conversations, uploads, containers, and skills (11 services, 26 resources, 97 operations)
• openai_admin [new] - the organization and administration API surface: usage and cost reporting, projects, organization users and invites, groups and roles, admin API keys, audit logs, and certificates (10 services, 29 resources, 81 operations)

Both providers expose a SQL-first surface: authentication is handled automatically, push down support using the LIMIT clause and built in pagination handling.

The openai provider is a ground-up rebuild of the previous provider, generated from the vendor's published OpenAPI specification. Some resources have been renamed and the organization/admin surface has moved to openai_admin - the previous provider version remains available in the registry for pinning, and the full disposition is documented at openai-provider.stackql.io.

Async jobs as SQL​

Fine-tuning jobs, batches, vector store file batches and uploads follow the same pattern: INSERT creates the job, SELECT polls it, EXEC cancels it. For example:

SELECT id, status, model, fine_tuned_model, trained_tokens
FROM openai.fine_tuning.jobs;

SELECT id, status, endpoint, request_counts
FROM openai.batches.batches
LIMIT 10;

Vector stores are a full CRUD surface with file membership:

SELECT id, name, status, usage_bytes, file_counts
FROM openai.vector_stores.vector_stores;

Inference endpoints (chat/completions, responses, embeddings, images, audio) are deliberately out of scope - the provider covers the control plane; use the vendor SDKs for invocation.

The admin provider - your OpenAI org as data​

The openai_admin provider presents the organization management surface as SQL.

Usage and cost are bucketed time series: one row per time bucket, with the per-group breakdown in the results JSON column, fanned out with JSON_EACH. Token usage by project and model over a 30-day window:

SELECT
  json_extract(r.value, '$.project_id')    AS project_id,
  json_extract(r.value, '$.model')         AS model,
  strftime('%Y-%m-%d', u.start_time, 'unixepoch') AS usage_date,
  json_extract(r.value, '$.input_tokens')  AS input_tokens,
  json_extract(r.value, '$.output_tokens') AS output_tokens
FROM openai_admin.usage.completions u, json_each(u.results) r
WHERE u.start_time = 1781481600
  AND u.bucket_width = '1d'
  AND u.limit = 31
  AND u.group_by = 'project_id'
ORDER BY usage_date, project_id;

Daily spend in USD by project:

SELECT
  strftime('%Y-%m-%d', c.start_time, 'unixepoch') AS cost_date,
  json_extract(r.value, '$.project_id')   AS project_id,
  json_extract(r.value, '$.amount.value') AS amount_usd
FROM openai_admin.costs.costs c, json_each(c.results) r
WHERE c.start_time = 1781481600
  AND c.limit = 180
  AND c.group_by = 'project_id'
ORDER BY cost_date, amount_usd DESC;

Usage is broken out per capability - completions, embeddings, moderations, images, audio, vector stores and code interpreter sessions - and can be grouped by project_id, api_key_id or model.

Governance and auditing​

Projects and their child resources (users, service accounts, API keys, rate limits) are queryable and writable:

SELECT p.name AS project, p.status, sa.name AS service_account, sa.role
FROM openai_admin.projects.projects p
JOIN openai_admin.projects.service_accounts sa ON sa.project_id = p.id
WHERE p.status = 'active';

Admin key hygiene and audit logs work the same way:

SELECT name, created_at, last_used_at, owner
FROM openai_admin.admin_api_keys.admin_api_keys
ORDER BY created_at;

SELECT id, type, effective_at, actor, project
FROM openai_admin.audit_logs.audit_logs
WHERE "effective_at[gt]" = 1750000000
  AND "event_types[]" = 'project.created';

Because it's all SQL, you can join usage to projects, materialize daily cost snapshots into a database, or point a BI tool at StackQL's Postgres wire protocol server and build an org-wide OpenAI spend dashboard without writing a line of integration code.

Authentication​

The two providers use different key types, which are disjoint by design:

# openai - standard API key
export OPENAI_API_KEY=sk-...

# openai_admin - org-scoped admin key (created by organization owners)
export OPENAI_ADMIN_KEY=sk-admin-...

Admin keys are available to organization accounts only and can be provisioned by organization owners in the platform console. A standard key cannot call the admin endpoints and vice versa.

Get started​

Pull the providers from the public registry:

registry pull openai
registry pull openai_admin

Provider docs are at openai-provider.stackql.io and openai-admin-provider.stackql.io. Let us know what you build. Star us on GitHub.