Stop idle ClickHouse Cloud dev services every weeknight

Every weeknight at 8pm, sweep my ClickHouse Cloud organization for non-production services that are sitting idle and burning money, and shut them down.

Start by calling List Organizations to discover my organization id, since the rest of the ClickHouse Cloud calls are nested under it. Then call List Services to pull every service in the organization, and Get Service on each one to read its current state, its tags, and its scaling and auto-pause settings. Also call Get Organization Usage Costs for the last seven days, passing from_date and to_date in YYYY-MM-DD form, so you can see what each service actually cost me over that window. Responses come back wrapped in a status, requestId and result envelope, so read the payload from result. Pace the per-service calls, because each API key is capped at ten requests per ten seconds.

Treat a service as a shutdown candidate only when all of these are true: its name or one of its tags matches dev, test, staging, sandbox or qa; it is currently running; and its usage cost over the last seven days is negligible.

Be conservative about what you touch. Never stop a service whose name or tags contain production or prod, even if it also matches one of the non-production patterns. Never stop a service that has an active ingestion pipeline feeding it: call List ClickPipes for each candidate and leave the service running if any pipe is in a running or active state. If a service already has aggressive auto-pause or idle scaling configured, leave it running as well and simply report it, since it will pause itself.

For each service that survives all of those checks, call Update Service State with the stop command. Stopping is asynchronous, so the service will report a stopping state immediately after the call and settle shortly after. Do not block waiting for it. This is low risk because ClickHouse Cloud services wake automatically when a client connects, so a developer who runs a query the next morning just resumes the service.

When the sweep is finished, post one message with Slack Bot to my engineering channel. List every service that was stopped along with its cost over the last seven days, then list every non-production service that was deliberately left running with the reason for each one (tagged production, active ingestion pipe, already auto-pausing, or cost was not negligible), and close with the estimated monthly saving from everything that was stopped. Post the message even on nights when nothing was stopped, so I know the sweep ran.

Then append one row per stopped service to my Google Sheets savings ledger using Append Values, with today's date, the service name, its cost over the last seven days, and a running total. Read the existing ledger rows first with Get Values so the running total continues from the last row rather than restarting from zero.