Google Cloud SQL Database Connection and Health Sentinel

id: gcp-cloud-sql-transaction-id-wraparound-sentinel
namespace: company.cloud

inputs:
  - id: project_id
    type: STRING
    description: Google Cloud Project ID.
  - id: instance_id
    type: STRING
    description: Cloud SQL instance ID to audit.

tasks:
  - id: query_cloud_sql_status
    type: io.kestra.plugin.gcp.cli.GCloudCLI
    projectId: "{{ inputs.project_id }}"
    serviceAccount: "{{ secret('GCP_SERVICE_ACCOUNT') }}"
    commands:
      - |
        gcloud sql instances describe "{{ inputs.instance_id }}" --format=json > /tmp/sql_inst.json

        STATE=$(jq -r '.state // "UNKNOWN"' /tmp/sql_inst.json)

        # Query active connections over last 15 minutes
        START_TIME=$(date -u -v-15M +%Y-%m-%dT%H:%M:%SZ)
        FILTER="metric.type="cloudsql.googleapis.com/database/network/connections" AND resource.labels.database_id="{{ inputs.project_id }}:{{ inputs.instance_id }}""

        gcloud monitoring timeseries list \
          --filter="$FILTER" \
          --interval-start-time="$START_TIME" \
          --format=json > /tmp/sql_conn.json

        PEAK_CONN=$(jq '[.[]?.points[]?.value.int64Value // 0] | max // 0' /tmp/sql_conn.json)

        echo "::set-output name=state::$STATE::"
        echo "::set-output name=peak_conn::$PEAK_CONN::"

  - id: evaluate_sql_health
    type: io.kestra.plugin.core.flow.If
    condition: "{{ outputs.query_cloud_sql_status.vars.state != 'RUNNABLE' }}"
    then:
      - id: notify_dba_team
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        url: "{{ secret('SLACK_WEBHOOK') }}"
        payload: |
          {
            "channel": "#gcp-alerts",
            "attachments": [
              {
                "color": "danger",
                "title": "🚨 Google Cloud SQL Instance Degraded Alert",
                "text": "Cloud SQL instance `{{ inputs.instance_id }}` in project `{{ inputs.project_id }}` is in non-runnable state: *{{ outputs.query_cloud_sql_status.vars.state }}*! Impact: Managed database service is offline, failing over, or undergoing emergency maintenance.",
                "footer": "Kestra Database Sentinel • Google Cloud SQL"
              }
            ]
          }

errors:
  - id: alert_pipeline_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK') }}"
    payload: |
      {
        "channel": "#gcp-alerts",
        "attachments": [
          {
            "color": "danger",
            "title": "⚠️ Google Cloud SQL Database Connection and Health Sentinel Pipeline Failure",
            "text": "Failed to inspect Cloud SQL instance state. Verify instance ID and IAM permissions.",
            "footer": "Kestra Workflow Failure Sentinel"
          }
        ]
      }

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