ClickHouse Storage Disk Space and Capacity Headroom Sentinel

id: clickhouse-disk-space-and-storage-policy-sentinel
namespace: company.analytics

description: |
  Audits ClickHouse system.disks storage volume utilization and available space
  via JDBC SQL query, detects free space exhaustion breaching safety headroom thresholds,
  opens an automated GitHub DBA incident ticket, and alerts Slack.

inputs:
  - id: clickhouse_url
    type: STRING
    displayName: ClickHouse JDBC URL
    defaults: "jdbc:ch://clickhouse.internal.company.com:8123/default"

  - id: free_space_percent_threshold
    type: FLOAT
    displayName: Minimum Free Space Threshold (%)
    defaults: 15.0

  - id: target_repository
    type: STRING
    displayName: Data Infrastructure Issue Repository
    defaults: my-org/data-platform-operations

triggers:
  - id: periodic_clickhouse_disk_check
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "*/30 * * * *"
    disabled: true

tasks:
  - id: probe_clickhouse_disks
    type: io.kestra.plugin.jdbc.clickhouse.Query
    description: Interrogates ClickHouse system.disks table for volume total_space,
      free_space, and storage paths
    url: "{{ inputs.clickhouse_url }}"
    username: "{{ secret('CLICKHOUSE_USER') }}"
    password: "{{ secret('CLICKHOUSE_PASSWORD') }}"
    sql: |
      SELECT 
        name,
        path,
        free_space,
        total_space,
        round((free_space / total_space) * 100, 2) AS free_pct
      FROM system.disks
      ORDER BY free_pct ASC
      LIMIT 1;
    fetchOne: true

  - id: evaluate_disk_headroom
    type: io.kestra.plugin.core.debug.Return
    description: Evaluates lowest free space percentage against storage safety threshold
    format:
      disk_name: "{{ outputs.probe_clickhouse_disks.row.name ?? 'default' }}"
      path: "{{ outputs.probe_clickhouse_disks.row.path ?? '/' }}"
      free_space_bytes: "{{ outputs.probe_clickhouse_disks.row.free_space ?? 0 }}"
      total_space_bytes: "{{ outputs.probe_clickhouse_disks.row.total_space ?? 0 }}"
      free_pct: "{{ outputs.probe_clickhouse_disks.row.free_pct ?? 100.0 }}"
      threshold_pct: "{{ inputs.free_space_percent_threshold }}"
      is_exhaustion_risk: "{{ (outputs.probe_clickhouse_disks.row.free_pct ?? 100.0)
        <= inputs.free_space_percent_threshold }}"

  - id: branch_on_disk_pressure
    type: io.kestra.plugin.core.flow.If
    description: Branches based on whether ClickHouse disk free space breached the
      safety threshold
    condition: "{{ outputs.evaluate_disk_headroom.value.is_exhaustion_risk == true }}"
    then:
      - id: create_storage_incident_issue
        type: io.kestra.plugin.github.issues.Create
        description: Logs a prioritized GitHub issue documenting ClickHouse disk
          capacity exhaustion
        oauthToken: "{{ secret('GITHUB_TOKEN') }}"
        repository: "{{ inputs.target_repository }}"
        title: "[Data SRE Alert] ClickHouse Disk Free Space Low on Volume: {{
          outputs.evaluate_disk_headroom.value.disk_name }} ({{
          outputs.evaluate_disk_headroom.value.free_pct }}% Free)"
        body: |
          ## 🚨 ClickHouse Storage Volume Capacity Warning

          The database infrastructure sentinel detected storage space exhaustion on ClickHouse cluster:
          - **ClickHouse Endpoint:** `{{ inputs.clickhouse_url }}`
          - **Storage Disk Name:** `{{ outputs.evaluate_disk_headroom.value.disk_name }}`
          - **Volume Mount Path:** `{{ outputs.evaluate_disk_headroom.value.path }}`
          - **Free Space Percentage:** **{{ outputs.evaluate_disk_headroom.value.free_pct }}%**
          - **Warning Threshold:** `{{ inputs.free_space_percent_threshold }}%`

          If free disk space drops below 5%, ClickHouse switches to read-only mode and refuses incoming insert mutations and background table merges.
          *Generated automatically by Kestra ClickHouse Sentinel.*

      - id: alert_data_sre_slack
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Dispatches high-priority alert to the data platform Slack channel
        url: "{{ secret('SLACK_WEBHOOK') }}"
        payload: |
          {{ {
            "text": "🚨 *ClickHouse Disk Space Exhaustion Alert*\n*Volume:* `" ~ outputs.evaluate_disk_headroom.value.disk_name ~ "` (`" ~ outputs.evaluate_disk_headroom.value.path ~ "`)\n*Free Space:* *" ~ outputs.evaluate_disk_headroom.value.free_pct ~ "%* (Threshold: `" ~ inputs.free_space_percent_threshold ~ "%`)\n*Triage Ticket:* <" ~ (outputs.create_storage_incident_issue.issueUrl ?? "#") ~ "|View GitHub Issue>"
          } | toJson }}

    else:
      - id: log_storage_healthy
        type: io.kestra.plugin.core.log.Log
        message: "ClickHouse volume {{ outputs.evaluate_disk_headroom.value.disk_name }}
          has ample free space ({{ outputs.evaluate_disk_headroom.value.free_pct
          }}% > {{ inputs.free_space_percent_threshold }}%)."

  - id: log_audit_complete
    type: io.kestra.plugin.core.log.Log
    description: Final status audit log recording ClickHouse disk storage state
    message: "ClickHouse disk audit complete. Volume: {{
      outputs.evaluate_disk_headroom.value.disk_name }}, Free: {{
      outputs.evaluate_disk_headroom.value.free_pct }}%."

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Alerts Slack when ClickHouse JDBC query or issue creation fails
    url: "{{ secret('SLACK_WEBHOOK') }}"
    payload: |
      {{ {
        "text": "⚠️ *ClickHouse Disk Sentinel Failed* in execution `" ~ execution.id ~ "`. Unable to execute system.disks query."
      } | toJson }}