Skip to content

Evenfire is an intelligence layer platform for running LLM agents that take real actions, on your infrastructure

TypeScript
32.2 KB
Raw Blame
import { type DbClient, pool, withTransaction } from '../db.js'

type NotificationDeliveryMedium = 'telegram' | 'slack' | 'teams'

type ClaimedNotificationDeliveryBase = {
  id: string
  medium: NotificationDeliveryMedium
  providerUserId: string
  providerWorkspaceId: string | null
  providerChannelId: string
  attempts: number
  // Figure D multi-bot: the CommunicationChannel ("namespace/name") this
  // verified account is bound to. NULL for legacy/non-wama rows; the delivery
  // worker resolves the per-channel bot from it and skips delivery when absent.
  communicationChannelRef: string | null
}

export type ApprovalRequestedNotificationDelivery = ClaimedNotificationDeliveryBase & {
  eventType: 'approval.requested'
  payload: {
    approvalRequestId: string
    recipeNamespace: string
    recipeName: string
    title: string
    body: string
    actions?: Array<{ id: string; label: string }>
    metadata?: Record<string, unknown> | null
  }
}

export type ApprovalUpdatedNotificationDelivery = ClaimedNotificationDeliveryBase & {
  eventType: 'approval.updated'
  payload: {
    approvalRequestId: string
    recipeNamespace: string
    recipeName: string
    status: 'approved' | 'denied' | 'cancelled' | 'expired' | 'consumed'
  }
}

export type WorkflowRunCompletedNotificationDelivery = ClaimedNotificationDeliveryBase & {
  eventType: 'workflow.run.completed'
  payload: {
    workflowRunId: string
    approvalRequestId: string
    recipeNamespace: string
    recipeName: string
    phase: 'Succeeded' | 'Failed' | 'Canceled'
    providerMedium: NotificationDeliveryMedium
    providerChannelId: string
    providerWorkspaceId?: string | null
    providerThreadId?: string | null
    hasDownloadableItems?: boolean
    completedAt?: string
    message?: string
  }
}

export type PluginWorkloadSdkNotificationDelivery = ClaimedNotificationDeliveryBase & {
  eventType: 'plugin_workload_sdk.notification'
  payload: {
    notificationId: string
    origin: 'plugin_workload_sdk'
    recipeNamespace: string
    recipeName: string
    callerRef: string
    eventType: string
    title: string
    body: string
    data: Record<string, unknown>
    actionRef: { type: string; id: string; urlRef?: string } | null
    deliveryPolicyRef: string | null
  }
}

export type ClaimedNotificationDelivery =
  | ApprovalRequestedNotificationDelivery
  | ApprovalUpdatedNotificationDelivery
  | WorkflowRunCompletedNotificationDelivery
  | PluginWorkloadSdkNotificationDelivery

export type UserBoundNotificationDelivery = ClaimedNotificationDelivery & {
  mcpHostRef: string
}

export type WorkflowApprovalUserBoundNotificationDelivery = (
  | ApprovalRequestedNotificationDelivery
  | ApprovalUpdatedNotificationDelivery
  | WorkflowRunCompletedNotificationDelivery
) & {
  mcpHostRef: string
}

const SUPPORTED_DELIVERY_MEDIA = new Set(['telegram', 'slack', 'teams'])
const MAX_PROVIDER_CHANNEL_IDS = 100

function normalizeMedium(value: unknown): NotificationDeliveryMedium | null {
  const medium = typeof value === 'string' ? value.trim().toLowerCase() : ''
  return SUPPORTED_DELIVERY_MEDIA.has(medium) ? (medium as NotificationDeliveryMedium) : null
}

function normalizeProviderChannelIds(values: unknown): string[] {
  if (!Array.isArray(values)) return []
  const unique = Array.from(
    new Set(
      values
        .map(value => (typeof value === 'string' ? value.trim() : ''))
        .filter(value => value.length > 0)
    )
  )
  if (unique.length > MAX_PROVIDER_CHANNEL_IDS) {
    throw new Error('provider_channel_id_limit_exceeded')
  }
  return unique
}

function normalizeHostRef(value: unknown): string {
  return typeof value === 'string' ? value.trim() : ''
}

function normalizeProviderWorkspaceId(value: unknown): string | null {
  const normalized = typeof value === 'string' ? value.trim() : ''
  return normalized ? normalized : null
}

function normalizeLimit(value: unknown): number {
  const parsed = typeof value === 'number' ? value : Number(value)
  if (!Number.isFinite(parsed)) return 10
  return Math.max(1, Math.min(50, Math.floor(parsed)))
}

export async function claimUserBoundNotificationDeliveries(params: {
  medium: unknown
  providerWorkspaceId?: unknown
  limit?: unknown
  db?: DbClient
}): Promise<WorkflowApprovalUserBoundNotificationDelivery[]> {
  const medium = normalizeMedium(params.medium)
  if (!medium) {
    throw new Error('unsupported_notification_medium')
  }
  const providerWorkspaceId = normalizeProviderWorkspaceId(params.providerWorkspaceId)
  const limit = normalizeLimit(params.limit)

  const run = async (db: DbClient) => {
    const result = await db.query(
      `WITH bound_accounts AS (
         SELECT wama.user_id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                wama.provider_channel_id,
                wama.communication_channel_ref
           FROM workflow_approval_medium_accounts wama
          WHERE wama.medium = $1
            AND wama.disabled_at IS NULL
            AND wama.provider_channel_id IS NOT NULL
            AND wama.provider_channel_id <> ''
            AND ($3::text IS NULL OR wama.provider_workspace_id = $3)
            AND NOT EXISTS (
                  SELECT 1
                    FROM workflow_approval_medium_accounts preferred
                   WHERE preferred.user_id = wama.user_id
                     AND preferred.disabled_at IS NULL
                     AND (
                          preferred.updated_at > wama.updated_at
                          OR (
                            preferred.updated_at = wama.updated_at
                            AND preferred.created_at > wama.created_at
                          )
                          OR (
                            preferred.updated_at = wama.updated_at
                            AND preferred.created_at = wama.created_at
                            AND preferred.id < wama.id
                          )
                     )
                )
       ),
       candidates AS (
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                wama.provider_channel_id,
                wama.communication_channel_ref,
                COALESCE(
                  NULLIF(war.payload #>> '{metadata,runtimeMcpHostRef}', ''),
                  wati.trigger_caller_key,
                  war.recipe_namespace || '/' || war.recipe_name
                ) AS mcp_host_ref,
                CASE WHEN nd.priority = 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_approval_requests war
             ON war.id::text = nd.payload->>'approvalRequestId'
            AND war.status = 'pending'
            AND (war.expires_at IS NULL OR war.expires_at > NOW())
            AND NULLIF(war.payload #>> '{metadata,workflowTrigger,providerBinding,medium}', '') IS NULL
           LEFT JOIN workflow_approval_trigger_intents wati
             ON wati.approval_request_id = war.id
           JOIN bound_accounts wama
             ON (
                  wama.user_id::text = nd.audience->>'userId'
                  OR EXISTS (
                    SELECT 1
                      FROM team_members tm
                     WHERE tm.team_id::text = nd.audience->>'teamId'
                       AND tm.user_id = wama.user_id
                       AND tm.status = 'active'
                  )
                )
          WHERE nd.event_type = 'approval.requested'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND COALESCE(
                  NULLIF(war.payload #>> '{metadata,runtimeMcpHostRef}', ''),
                  wati.trigger_caller_key,
                  war.recipe_namespace || '/' || war.recipe_name
                ) ~ '^sandbox-recipes/[a-z0-9]([a-z0-9.-]*[a-z0-9])?$'
        UNION ALL
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                wama.provider_channel_id,
                wama.communication_channel_ref,
                COALESCE(
                  NULLIF(war.payload #>> '{metadata,runtimeMcpHostRef}', ''),
                  wati.trigger_caller_key,
                  war.recipe_namespace || '/' || war.recipe_name
                ) AS mcp_host_ref,
                CASE WHEN nd.priority = 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_approval_requests war
             ON war.id::text = nd.payload->>'approvalRequestId'
            AND war.status IN ('approved', 'denied', 'cancelled', 'expired', 'consumed')
            AND NULLIF(war.payload #>> '{metadata,workflowTrigger,providerBinding,medium}', '') IS NULL
           LEFT JOIN workflow_approval_trigger_intents wati
             ON wati.approval_request_id = war.id
           JOIN bound_accounts wama
             ON (
                  wama.user_id::text = nd.audience->>'userId'
                  OR EXISTS (
                    SELECT 1
                      FROM team_members tm
                     WHERE tm.team_id::text = nd.audience->>'teamId'
                       AND tm.user_id = wama.user_id
                       AND tm.status = 'active'
                  )
                )
          WHERE nd.event_type = 'approval.updated'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND nd.payload->>'status' IN ('approved', 'denied', 'cancelled', 'expired', 'consumed')
            AND COALESCE(
                  NULLIF(war.payload #>> '{metadata,runtimeMcpHostRef}', ''),
                  wati.trigger_caller_key,
                  war.recipe_namespace || '/' || war.recipe_name
                ) ~ '^sandbox-recipes/[a-z0-9]([a-z0-9.-]*[a-z0-9])?$'
        UNION ALL
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                wama.provider_channel_id,
                wama.communication_channel_ref,
                COALESCE(wati.trigger_caller_key, wr.recipe_namespace || '/' || wr.recipe_name) AS mcp_host_ref,
                CASE WHEN nd.priority = 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_runs wr
             ON wr.run_id::text = nd.payload->>'workflowRunId'
            AND wr.approval_request_id::text = nd.payload->>'approvalRequestId'
            AND wr.phase = nd.payload->>'phase'
           LEFT JOIN workflow_approval_trigger_intents wati
             ON wati.approval_request_id::text = nd.payload->>'approvalRequestId'
           JOIN bound_accounts wama
             ON (
                  wama.user_id::text = nd.audience->>'userId'
                  OR EXISTS (
                    SELECT 1
                      FROM team_members tm
                     WHERE tm.team_id::text = nd.audience->>'teamId'
                       AND tm.user_id = wama.user_id
                       AND tm.status = 'active'
                  )
                )
          WHERE nd.event_type = 'workflow.run.completed'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND wr.phase IN ('Succeeded', 'Failed', 'Canceled')
            AND NULLIF(nd.payload->>'providerMedium', '') IS NULL
            AND COALESCE(wati.trigger_caller_key, wr.recipe_namespace || '/' || wr.recipe_name) LIKE 'sandbox-recipes/%'
       ),
       locked AS (
         SELECT nd.id,
                c.medium,
                c.provider_user_id,
                c.provider_workspace_id,
                c.provider_channel_id,
                c.communication_channel_ref,
                c.mcp_host_ref
           FROM notification_deliveries nd
           JOIN candidates c ON c.id = nd.id
          ORDER BY c.sort_priority ASC,
                   c.created_at ASC,
                   nd.id ASC
          LIMIT $2
          FOR UPDATE OF nd SKIP LOCKED
       ),
       claimed AS (
         UPDATE notification_deliveries nd
            SET status = 'retrying',
                attempts = nd.attempts + 1,
                next_attempt_at = NOW() + INTERVAL '30 seconds'
           FROM locked c
          WHERE nd.id = c.id
      RETURNING nd.id,
                nd.event_type AS "eventType",
                nd.payload,
                nd.attempts,
                c.medium,
                c.provider_user_id AS "providerUserId",
                c.provider_workspace_id AS "providerWorkspaceId",
                c.provider_channel_id AS "providerChannelId",
                c.communication_channel_ref AS "communicationChannelRef",
                c.mcp_host_ref AS "mcpHostRef"
       )
       SELECT *
         FROM claimed
        ORDER BY attempts ASC`,
      [medium, limit, providerWorkspaceId]
    )
    return result.rows as WorkflowApprovalUserBoundNotificationDelivery[]
  }

  return params.db ? run(params.db) : withTransaction(run)
}

export async function claimNotificationDeliveries(params: {
  medium: unknown
  hostRef?: unknown
  providerChannelIds?: unknown
  providerWorkspaceId?: unknown
  limit?: unknown
  db?: DbClient
}): Promise<ClaimedNotificationDelivery[]> {
  const medium = normalizeMedium(params.medium)
  if (!medium) {
    throw new Error('unsupported_notification_medium')
  }
  const providerChannelIds = normalizeProviderChannelIds(params.providerChannelIds)
  if (providerChannelIds.length === 0) {
    throw new Error('provider_channel_id_required')
  }
  const hostRef = normalizeHostRef(params.hostRef)
  if (!hostRef) {
    throw new Error('host_ref_required')
  }
  const providerWorkspaceId = normalizeProviderWorkspaceId(params.providerWorkspaceId)
  if (medium !== 'telegram' && !providerWorkspaceId) {
    throw new Error('provider_workspace_id_required')
  }
  const limit = normalizeLimit(params.limit)

  const run = async (db: DbClient) => {
    const result = await db.query(
      `WITH candidates AS (
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                war.payload #>> '{metadata,workflowTrigger,providerBinding,providerChannelId}' AS provider_channel_id,
                CASE nd.priority WHEN 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_approval_medium_accounts wama
             ON (
                  wama.user_id::text = nd.audience->>'userId'
                  OR EXISTS (
                    SELECT 1
                      FROM team_members tm
                     WHERE tm.team_id::text = nd.audience->>'teamId'
                       AND tm.user_id = wama.user_id
                       AND tm.status = 'active'
                  )
                )
            AND wama.medium = $1
            AND wama.disabled_at IS NULL
            AND ($5::text IS NULL OR wama.provider_workspace_id = $5)
           JOIN workflow_approval_trigger_intents wati
             ON wati.approval_request_id::text = nd.payload->>'approvalRequestId'
            AND wati.trigger_caller_key = $3
           JOIN workflow_approval_requests war
             ON war.id::text = nd.payload->>'approvalRequestId'
            AND war.status = 'pending'
            AND (war.expires_at IS NULL OR war.expires_at > NOW())
          WHERE nd.event_type = 'approval.requested'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND war.payload #>> '{metadata,workflowTrigger,providerBinding,medium}' = $1
            AND war.payload #>> '{metadata,workflowTrigger,providerBinding,providerChannelId}' = ANY($2::text[])
            AND (
                  $1 = 'telegram'
                  OR war.payload #>> '{metadata,workflowTrigger,providerBinding,providerWorkspaceId}' = $5
                )
            AND (
                  $1 = 'telegram'
                  OR wama.provider_channel_id = war.payload #>> '{metadata,workflowTrigger,providerBinding,providerChannelId}'
                )
        UNION ALL
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                CASE WHEN $1 = 'telegram'
                  THEN nd.payload->>'providerChannelId'
                  ELSE wama.provider_channel_id
                END AS provider_channel_id,
                CASE nd.priority WHEN 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_approval_medium_accounts wama
             ON (
                  wama.user_id::text = nd.audience->>'userId'
                  OR EXISTS (
                    SELECT 1
                      FROM team_members tm
                     WHERE tm.team_id::text = nd.audience->>'teamId'
                       AND tm.user_id = wama.user_id
                       AND tm.status = 'active'
                  )
                )
            AND wama.medium = $1
            AND wama.disabled_at IS NULL
            AND ($5::text IS NULL OR wama.provider_workspace_id = $5)
           JOIN workflow_approval_trigger_intents wati
             ON wati.approval_request_id::text = nd.payload->>'approvalRequestId'
            AND wati.trigger_caller_key = $3
           JOIN workflow_runs wr
             ON wr.run_id::text = nd.payload->>'workflowRunId'
            AND wr.approval_request_id::text = nd.payload->>'approvalRequestId'
            AND wr.phase = nd.payload->>'phase'
          WHERE nd.event_type = 'workflow.run.completed'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND wr.phase IN ('Succeeded', 'Failed', 'Canceled')
            AND nd.payload->>'providerMedium' = wama.medium
            AND (
                  NULLIF(nd.payload->>'providerWorkspaceId', '') IS NULL
                  OR wama.provider_workspace_id = nd.payload->>'providerWorkspaceId'
                )
            AND (
                  (
                    $1 = 'telegram'
                    AND nd.payload->>'providerChannelId' = ANY($2::text[])
                  )
                  OR (
                    $1 <> 'telegram'
                    AND nd.payload->>'providerChannelId' = wama.provider_channel_id
                    AND wama.provider_channel_id IS NOT NULL
                    AND wama.provider_channel_id = ANY($2::text[])
                  )
                )
        UNION ALL
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                war.payload #>> '{metadata,workflowTrigger,providerBinding,providerChannelId}' AS provider_channel_id,
                CASE nd.priority WHEN 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_approval_requests war
             ON war.id::text = nd.payload->>'approvalRequestId'
           JOIN workflow_approval_medium_accounts wama
             ON (
                  wama.user_id::text = nd.audience->>'userId'
                  OR EXISTS (
                    SELECT 1
                      FROM team_members tm
                     WHERE tm.team_id::text = nd.audience->>'teamId'
                       AND tm.user_id = wama.user_id
                       AND tm.status = 'active'
                  )
                )
            AND wama.medium = $1
            AND wama.disabled_at IS NULL
            AND ($5::text IS NULL OR wama.provider_workspace_id = $5)
           JOIN workflow_approval_trigger_intents wati
             ON wati.approval_request_id::text = nd.payload->>'approvalRequestId'
            AND wati.trigger_caller_key = $3
          WHERE nd.event_type = 'approval.updated'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND war.status IN ('approved', 'denied', 'cancelled', 'expired', 'consumed')
            AND nd.payload->>'status' IN ('approved', 'denied', 'cancelled', 'expired', 'consumed')
            AND war.payload #>> '{metadata,workflowTrigger,providerBinding,medium}' = $1
            AND war.payload #>> '{metadata,workflowTrigger,providerBinding,providerChannelId}' = ANY($2::text[])
            AND (
                  $1 = 'telegram'
                  OR war.payload #>> '{metadata,workflowTrigger,providerBinding,providerWorkspaceId}' = $5
                )
            AND (
                  $1 = 'telegram'
                  OR wama.provider_channel_id = war.payload #>> '{metadata,workflowTrigger,providerBinding,providerChannelId}'
                )
        UNION ALL
         -- Case 2: workflow-recipe approval WITHOUT a conversational origin
         -- channel (no providerBinding) — e.g. a recipe triggered by schedule
         -- or UI that requests approval mid-run. Routes to the user's preferred
         -- delivery instance (strict) or the most-recently-verified default.
         -- Telegram-only: Slack routing is not fully developed yet, and a
         -- Telegram approval WITHOUT a binding matches no other branch, so this
         -- does not overlap or change the conversational case 1.
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                wama.provider_channel_id AS provider_channel_id,
                CASE nd.priority WHEN 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_approval_medium_accounts wama
             ON (
                  wama.user_id::text = nd.audience->>'userId'
                  OR EXISTS (
                    SELECT 1
                      FROM team_members tm
                     WHERE tm.team_id::text = nd.audience->>'teamId'
                       AND tm.user_id = wama.user_id
                       AND tm.status = 'active'
                  )
                )
            AND wama.medium = $1
            AND wama.disabled_at IS NULL
           JOIN workflow_approval_trigger_intents wati
             ON wati.approval_request_id::text = nd.payload->>'approvalRequestId'
            AND wati.trigger_caller_key = $3
           JOIN workflow_approval_requests war
             ON war.id::text = nd.payload->>'approvalRequestId'
            AND war.status = 'pending'
            AND (war.expires_at IS NULL OR war.expires_at > NOW())
           LEFT JOIN user_notification_preferences unp
             ON unp.user_id = wama.user_id
          WHERE nd.event_type = 'approval.requested'
            AND $1 = 'telegram'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND war.payload #>> '{metadata,workflowTrigger,providerBinding,providerChannelId}' IS NULL
            AND wama.provider_channel_id IS NOT NULL
            AND wama.provider_channel_id = ANY($2::text[])
            AND (
              CASE
                WHEN unp.preferred_account_id IS NOT NULL
                  THEN wama.id = unp.preferred_account_id
                ELSE wama.medium = COALESCE(
                  unp.preferred_medium,
                  (
                    SELECT wama2.medium
                      FROM workflow_approval_medium_accounts wama2
                     WHERE wama2.user_id = wama.user_id
                       AND wama2.disabled_at IS NULL
                     ORDER BY wama2.verified_at DESC
                     LIMIT 1
                  )
                )
              END
            )
        UNION ALL
         -- Case 2 (continued): approval.updated for a non-conversational
         -- workflow-recipe approval. Same Telegram-only, no-binding routing.
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                wama.provider_channel_id AS provider_channel_id,
                CASE nd.priority WHEN 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_approval_medium_accounts wama
             ON (
                  wama.user_id::text = nd.audience->>'userId'
                  OR EXISTS (
                    SELECT 1
                      FROM team_members tm
                     WHERE tm.team_id::text = nd.audience->>'teamId'
                       AND tm.user_id = wama.user_id
                       AND tm.status = 'active'
                  )
                )
            AND wama.medium = $1
            AND wama.disabled_at IS NULL
           JOIN workflow_approval_trigger_intents wati
             ON wati.approval_request_id::text = nd.payload->>'approvalRequestId'
            AND wati.trigger_caller_key = $3
           JOIN workflow_approval_requests war
             ON war.id::text = nd.payload->>'approvalRequestId'
          LEFT JOIN user_notification_preferences unp
             ON unp.user_id = wama.user_id
          WHERE nd.event_type = 'approval.updated'
            AND $1 = 'telegram'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND war.status IN ('approved', 'denied', 'cancelled', 'expired', 'consumed')
            AND nd.payload->>'status' IN ('approved', 'denied', 'cancelled', 'expired', 'consumed')
            AND war.payload #>> '{metadata,workflowTrigger,providerBinding,providerChannelId}' IS NULL
            AND wama.provider_channel_id IS NOT NULL
            AND wama.provider_channel_id = ANY($2::text[])
            AND (
              CASE
                WHEN unp.preferred_account_id IS NOT NULL
                  THEN wama.id = unp.preferred_account_id
                ELSE wama.medium = COALESCE(
                  unp.preferred_medium,
                  (
                    SELECT wama2.medium
                      FROM workflow_approval_medium_accounts wama2
                     WHERE wama2.user_id = wama.user_id
                       AND wama2.disabled_at IS NULL
                     ORDER BY wama2.verified_at DESC
                     LIMIT 1
                  )
                )
              END
            )
        UNION ALL
         -- Plugin Workload SDK notifications: channel fallback after the
         -- desktop grace window. Honors the per-user preferred delivery
         -- instance, and can be disabled entirely via user_notification_preferences.
         SELECT nd.id,
                wama.medium,
                wama.provider_user_id,
                wama.provider_workspace_id,
                wama.provider_channel_id AS provider_channel_id,
                CASE nd.priority WHEN 'high' THEN 0 ELSE 1 END AS sort_priority,
                nd.created_at
           FROM notification_deliveries nd
           JOIN workflow_approval_medium_accounts wama
             ON wama.user_id::text = nd.audience->>'userId'
            AND wama.medium = $1
            AND wama.disabled_at IS NULL
            AND ($5::text IS NULL OR wama.provider_workspace_id = $5)
           LEFT JOIN user_notification_preferences unp
             ON unp.user_id = wama.user_id
          WHERE nd.event_type = 'plugin_workload_sdk.notification'
            AND nd.status IN ('queued', 'retrying')
            AND nd.next_attempt_at <= NOW()
            AND (nd.expires_at IS NULL OR nd.expires_at > NOW())
            AND COALESCE(unp.channel_fallback_enabled, true) = true
            AND wama.provider_channel_id IS NOT NULL
            AND wama.provider_channel_id = ANY($2::text[])
            -- Preference precedence: a preferred instance (account id) wins and
            -- is STRICT — only that account matches; if it is disabled the
            -- disabled_at filter above drops every row (no fallback). Otherwise
            -- fall back to preferred medium, then the most-recently-verified.
            AND (
              CASE
                WHEN unp.preferred_account_id IS NOT NULL
                  THEN wama.id = unp.preferred_account_id
                ELSE wama.medium = COALESCE(
                  unp.preferred_medium,
                  (
                    SELECT wama2.medium
                      FROM workflow_approval_medium_accounts wama2
                     WHERE wama2.user_id = wama.user_id
                       AND wama2.disabled_at IS NULL
                     ORDER BY wama2.verified_at DESC
                     LIMIT 1
                  )
                )
              END
            )
       ),
       locked AS (
         SELECT nd.id,
                c.medium,
                c.provider_user_id,
                c.provider_workspace_id,
                c.provider_channel_id
           FROM notification_deliveries nd
           JOIN candidates c ON c.id = nd.id
          ORDER BY c.sort_priority ASC,
                   c.created_at ASC,
                   nd.id ASC
          LIMIT $4
          FOR UPDATE OF nd SKIP LOCKED
       ),
       claimed AS (
         UPDATE notification_deliveries nd
            SET status = 'retrying',
                attempts = nd.attempts + 1,
                next_attempt_at = NOW() + INTERVAL '30 seconds'
           FROM locked c
          WHERE nd.id = c.id
      RETURNING nd.id,
                nd.event_type AS "eventType",
                nd.payload,
                nd.attempts,
                c.medium,
                c.provider_user_id AS "providerUserId",
                c.provider_workspace_id AS "providerWorkspaceId",
                c.provider_channel_id AS "providerChannelId"
       )
       SELECT *
         FROM claimed
        ORDER BY attempts ASC`,
      [medium, providerChannelIds, hostRef, limit, providerWorkspaceId]
    )
    return result.rows as ClaimedNotificationDelivery[]
  }

  return params.db ? run(params.db) : withTransaction(run)
}

export async function markUserBoundNotificationDeliverySent(
  id: string,
  db: DbClient = pool
): Promise<boolean> {
  const result = await db.query(
    `UPDATE notification_deliveries
        SET status = 'sent'
      WHERE id = $1
        AND event_type IN ('approval.requested', 'approval.updated', 'workflow.run.completed')
        AND status IN ('queued', 'retrying')
      RETURNING id`,
    [id]
  )
  return (result.rowCount ?? 0) > 0
}

export async function markUserBoundNotificationDeliveryFailed(
  id: string,
  db: DbClient = pool
): Promise<boolean> {
  const result = await db.query(
    `UPDATE notification_deliveries
        SET status = CASE WHEN attempts >= 5 THEN 'failed' ELSE 'retrying' END,
            next_attempt_at = NOW() + INTERVAL '60 seconds'
      WHERE id = $1
        AND event_type IN ('approval.requested', 'approval.updated', 'workflow.run.completed')
        AND status IN ('queued', 'retrying')
      RETURNING id`,
    [id]
  )
  return (result.rowCount ?? 0) > 0
}

// Figure D multi-bot: terminal outcome when no bot credential can be resolved
// for the delivery's CommunicationChannel (Secret/CC absent). NOT `sent` (the
// message never left) and NOT `retrying`/`failed` (retrying is pointless — the
// channel simply has no bot configured). A distinct `skipped_no_bot` status
// makes suppressed deliveries auditable (vs being indistinguishable from a
// provider failure). The worker also emits a metric + structured log.
export async function markUserBoundNotificationDeliverySkippedNoBot(
  id: string,
  db: DbClient = pool
): Promise<boolean> {
  const result = await db.query(
    `UPDATE notification_deliveries
        SET status = 'skipped_no_bot'
      WHERE id = $1
        AND event_type IN ('approval.requested', 'approval.updated', 'workflow.run.completed')
        AND status IN ('queued', 'retrying')
      RETURNING id`,
    [id]
  )
  return (result.rowCount ?? 0) > 0
}

export {
  acknowledgeNotificationDelivery,
  failNotificationDelivery,
  resolvePendingWorkflowApprovalDelivery,
  type ResolvePendingWorkflowApprovalDeliveryResult,
} from './notificationDeliveryTerminalService.js'