Governed MCP Action Status Check

id: mcp-governed-action-status
namespace: company.team

description: |
  MCP tool. Returns the status of a governed action request. If the approver notification failed
  more than 10 minutes ago, it also re-sends the Slack approval link.

inputs:
  - id: request_id
    type: STRING
    required: true
    validator: '^[A-Za-z0-9_-]{1,64}$'
    description: The request_id returned by governed_action_request.

variables:
  # Placeholders: replace with your own values.
  jdbc_url: jdbc:postgresql://demo-postgres:5432/demo
  jdbc_user: governed_actions
  kestra_url: http://localhost:8080

tasks:
  # Claims the re-send atomically: two status calls at the same time send at most one message, and the
  # next re-send waits another 10 minutes.
  - id: claim_renotify
    type: io.kestra.plugin.jdbc.postgresql.Query
    url: "{{ vars.jdbc_url }}"
    username: "{{ vars.jdbc_user }}"
    password: "{{ secret('PG_PASSWORD') }}"
    fetchType: FETCH_ONE
    sql: |
      UPDATE public.governed_requests SET updated_at = now()
      WHERE request_key = CAST(:key AS text) AND status = 'pending_unnotified'
        AND executor_execution_id IS NOT NULL AND updated_at < now() - interval '10 minutes'
      RETURNING environment, days, executor_execution_id
    parameters:
      key: "{{ inputs.request_id }}"

  - id: renotify_due
    type: io.kestra.plugin.core.flow.If
    condition: "{{ (outputs.claim_renotify.size ?? 0) > 0 }}"
    then:
      # A failed re-send must not fail the status call: the row stays pending_unnotified.
      # allowWarning keeps the execution SUCCESS: a WARNING execution is returned to the agent as an MCP error.
      - id: renotify_approvers
        type: io.kestra.plugin.core.flow.Sequential
        allowFailure: true
        allowWarning: true
        tasks:
          - id: renotify_slack
            type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
            url: "{{ secret('SLACK_WEBHOOK_URL') }}"
            # toJson escapes the whole text, so no input can break the JSON string.
            payload: |
              {
                "text": {{ ('Reminder, approval needed (the first notification failed): ' ~ outputs.claim_renotify.row.environment ~ ' purge of rows older than ' ~ outputs.claim_renotify.row.days ~ ' days (request ' ~ inputs.request_id ~ '). Approve or deny: ' ~ vars.kestra_url ~ '/ui/' ~ flow.tenantId ~ '/executions/' ~ flow.namespace ~ '/mcp-governed-action-executor/' ~ outputs.claim_renotify.row.executor_execution_id) | toJson }}
              }

          - id: record_notified
            type: io.kestra.plugin.jdbc.postgresql.Query
            url: "{{ vars.jdbc_url }}"
            username: "{{ vars.jdbc_user }}"
            password: "{{ secret('PG_PASSWORD') }}"
            sql: |
              UPDATE public.governed_requests SET status = 'pending', updated_at = now()
              WHERE request_key = CAST(:key AS text) AND executor_execution_id = CAST(:exec AS text) AND status = 'pending_unnotified'
            parameters:
              key: "{{ inputs.request_id }}"
              exec: "{{ outputs.claim_renotify.row.executor_execution_id }}"

  - id: read_request
    type: io.kestra.plugin.jdbc.postgresql.Query
    url: "{{ vars.jdbc_url }}"
    username: "{{ vars.jdbc_user }}"
    password: "{{ secret('PG_PASSWORD') }}"
    fetchType: FETCH_ONE
    sql: |
      SELECT status, environment, days, executor_execution_id FROM public.governed_requests WHERE request_key = CAST(:key AS text)
    parameters:
      key: "{{ inputs.request_id }}"

  # Each branch builds the response values: from the row when there is one, otherwise the unknown defaults.
  - id: row_found
    type: io.kestra.plugin.core.flow.If
    condition: "{{ (outputs.read_request.size ?? 0) > 0 }}"
    then:
      - id: values_from_row
        type: io.kestra.plugin.core.output.OutputValues
        values:
          status: "{{ outputs.read_request.row.status }}"
          environment: "{{ outputs.read_request.row.environment }}"
          days: "{{ outputs.read_request.row.days }}"
          approval_execution_id: "{{ outputs.read_request.row.executor_execution_id ?? '' }}"
    else:
      - id: values_unknown
        type: io.kestra.plugin.core.output.OutputValues
        values:
          status: unknown
          environment: ""
          days: "0"
          approval_execution_id: ""

outputs:
  - id: status
    type: STRING
    value: "{{ outputs.values_from_row.values.status ??
      outputs.values_unknown.values.status ?? 'unknown' }}"
  - id: environment
    type: STRING
    value: "{{ outputs.values_from_row.values.environment ??
      outputs.values_unknown.values.environment ?? '' }}"
  - id: days
    type: INT
    value: "{{ outputs.values_from_row.values.days ??
      outputs.values_unknown.values.days ?? 0 }}"
  - id: approval_execution_id
    type: STRING
    value: "{{ outputs.values_from_row.values.approval_execution_id ??
      outputs.values_unknown.values.approval_execution_id ?? '' }}"

triggers:
  - id: mcp
    type: io.kestra.plugin.core.trigger.McpToolTrigger
    toolName: governed_action_status
    title: Check a governed action status
    toolDescription: >-
      Returns the status, environment, days and approval_execution_id of a
      request made with governed_action_request. Status values: claimed
      (accepted, not started yet), pending (production, waiting for a human),
      pending_unnotified (waiting, but the approver notification failed),
      running, executed, rejected, expired (not approved within 1 hour, or the
      executor failed before the purge started), failed, or unknown (no such
      request, with empty environment and approval_execution_id and days 0).
      Call it to follow up a production request. Side effect: if the request is
      pending_unnotified for more than 10 minutes, it re-sends the approval link
      to the approvers and the status becomes pending.
    mcpServer: default
    annotations:
      readOnly: false
      destructive: false
      idempotent: false
      openWorld: true