Amazon RDS Connection Spike and Headroom Sentinel

id: aws-rds-connection-spike-and-deadlock-sentinel
namespace: company.database

description: |
  Audits Amazon RDS database instances for connection pool exhaustion spikes
  and deadlock contention via CloudWatch metrics, logs automated GitHub DBA
  investigation tickets, and dispatches high-priority Slack notifications to on-call DBAs.

inputs:
  - id: aws_region
    type: STRING
    displayName: AWS Region
    defaults: us-east-1

  - id: db_instance_identifier
    type: STRING
    displayName: RDS DB Instance Identifier
    defaults: production-postgres-cluster

  - id: connection_threshold
    type: INT
    displayName: Database Connection Threshold
    defaults: 250

  - id: github_repository
    type: STRING
    displayName: GitHub Target Infrastructure Repository
    defaults: my-org/database-operations

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

tasks:
  - id: probe_rds_metrics
    type: io.kestra.plugin.aws.cli.AwsCLI
    description: Queries Amazon CloudWatch for RDS DatabaseConnections metrics over
      the last 15 minutes
    accessKeyId: "{{ secret('AWS_ACCESS_KEY_ID') }}"
    secretKeyId: "{{ secret('AWS_SECRET_ACCESS_KEY') }}"
    region: "{{ inputs.aws_region }}"
    commands:
      - aws cloudwatch get-metric-data --metric-data-queries
        '[{"Id":"m1","MetricStat":{"Metric":{"Namespace":"AWS/RDS","MetricName":"DatabaseConnections","Dimensions":[{"Name":"DBInstanceIdentifier","Value":"{{
        inputs.db_instance_identifier
        }}"}]},"Period":300,"Stat":"Maximum"},"ReturnData":true}]' --start-time
        $(date -u -v-15M +%Y-%m-%dT%H:%M:%SZ) --end-time $(date -u
        +%Y-%m-%dT%H:%M:%SZ) --output json | tr -d '\n' | xargs -0 -I {} echo
        '::{"outputs":{}}::'

  - id: evaluate_connection_headroom
    type: io.kestra.plugin.core.debug.Return
    description: Evaluates maximum connection spikes against the designated threshold
    format:
      max_connections: "{{
        outputs.probe_rds_metrics.vars.MetricDataResults[0].Values[0] ?? 0 }}"
      threshold: "{{ inputs.connection_threshold }}"
      is_exhausted: "{{ (outputs.probe_rds_metrics.vars.MetricDataResults[0].Values[0]
        ?? 0) >= inputs.connection_threshold }}"

  - id: branch_on_connection_spike
    type: io.kestra.plugin.core.flow.If
    description: Branches based on whether database connections exceeded safe
      headroom limits
    condition: "{{ outputs.evaluate_connection_headroom.value.is_exhausted == true }}"
    then:
      - id: create_dba_incident_issue
        type: io.kestra.plugin.github.issues.Create
        description: Creates a GitHub tracking issue for database connection saturation
        oauthToken: "{{ secret('GITHUB_TOKEN') }}"
        repository: "{{ inputs.github_repository }}"
        title: "[DBA Alert] Connection Spike Detected on RDS Instance: {{
          inputs.db_instance_identifier }}"
        body: |
          ## 🚨 RDS Connection Saturation Warning

          - **Database Identifier:** `{{ inputs.db_instance_identifier }}`
          - **AWS Region:** `{{ inputs.aws_region }}`
          - **Peak Connections (Last 15m):** `{{ outputs.evaluate_connection_headroom.value.max_connections }}`
          - **Safe Threshold:** `{{ inputs.connection_threshold }}`

          Please inspect active backend connection pools and potential hung transactions in `pg_stat_activity` / `SHOW PROCESSLIST`.
          *Generated automatically by Kestra RDS Sentinel.*

      - id: alert_dba_slack
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Alerts on-call DBAs on Slack with connection spike metrics and
          issue link
        url: "{{ secret('SLACK_WEBHOOK') }}"
        payload: |
          {{ {
            "text": "🚨 *AWS RDS Database Connection Spike Alert*\n*Instance:* `" ~ inputs.db_instance_identifier ~ "` (" ~ inputs.aws_region ~ ")\n*Max Connections:* *" ~ outputs.evaluate_connection_headroom.value.max_connections ~ "* (Threshold: `" ~ inputs.connection_threshold ~ "`)\n*DBA Ticket:* <" ~ (outputs.create_dba_incident_issue.issueUrl ?? "#") ~ "|View GitHub Issue>"
          } | toJson }}

    else:
      - id: log_database_healthy
        type: io.kestra.plugin.core.log.Log
        message: "RDS instance {{ inputs.db_instance_identifier }} connections within
          safe headroom (Peak: {{
          outputs.evaluate_connection_headroom.value.max_connections }} <= {{
          inputs.connection_threshold }})."

  - id: log_audit_complete
    type: io.kestra.plugin.core.log.Log
    description: Audit trail summarizing the RDS connection health evaluation
    message: "RDS health check completed for {{ inputs.db_instance_identifier }}.
      Peak connections: {{
      outputs.evaluate_connection_headroom.value.max_connections }}."

errors:
  - id: alert_rds_flow_error
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Alerts Slack when metric queries or issue creation fails
    url: "{{ secret('SLACK_WEBHOOK') }}"
    payload: |
      {{ {
        "text": "⚠️ *AWS RDS Sentinel Flow Error* in execution `" ~ execution.id ~ "`. Unable to query CloudWatch metrics for `" ~ inputs.db_instance_identifier ~ "`."
      } | toJson }}