Skip to content

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

TypeScript
94.1 KB
Raw Blame
import { createHash, randomUUID } from 'node:crypto'
import { type DbClient, type DbTransactionClient, pool, withTransaction } from '../db.js'
import type { AdministrativeEventSubmitterPrincipalV1 } from '../middleware/tracingSubmitterAuth.js'
import { stableStringify } from '../utils/stableStringify.js'
import {
  type LlmProviderAttemptRow,
  isLinkedCodexInFlightWithoutUsage,
  linkedCodexExactUsage,
  loadLlmProviderAttemptBySdkAttemptId,
} from './llmProviderAttemptStore.js'
import {
  insertSpendOutcomeFloor,
  isOauthBrokerProvider,
  spendFloor,
} from './pluginWorkloadSdkSpendLedger.js'
import {
  type ControlApiPermissionChange,
  appendControlApiPermissionEventsInTransaction,
} from './tracing/controlApiPermissionEvents.js'

// ─── Plugin Workload SDK — DB layer ──────────────────────────────────────
// Extracted from db.ts (per plan: avoid growing the migration monolith).
// Owns the three SDK tables:
//   - plugin_workload_sdk_grants          per-recipe capability grants
//   - plugin_workload_sdk_invocations     invocation audit trail
//   - plugin_workload_sdk_quota_counters  historical-only counters (issue #348);
//                                         admin read path only, no writers

export const PLUGIN_WORKLOAD_SDK_FAMILIES = ['promptBridge', 'clientNotifications'] as const
export type PluginWorkloadSdkFamily = (typeof PLUGIN_WORKLOAD_SDK_FAMILIES)[number]

// ─── Grant-update NOTIFY (issue #375, P3) ────────────────────────────────
// CROSS-SERVICE CONTRACT: a transactional `pg_notify` fired inside the
// grant-mutation transactions (upsert / delete / revoke) so the Workflow
// Recipes Controller re-reconciles the affected recipe immediately (~1–5s)
// instead of waiting for its ≤30s level-triggered watchdog. The WRC LISTEN side
// lives in `workflow-recipes/src/reconciler/grantUpdateListener.ts` — the
// channel name and payload shape below MUST stay in sync with it.
//
// This is an app-level NOTIFY (not a DDL trigger) so the payload stays typed and
// the change is fully revertible. Being inside the transaction, it is delivered
// on COMMIT and discarded on ROLLBACK. Semantics are best-effort: a dropped
// notification degrades to the existing polling backstop, never worse.
export const PLUGIN_WORKLOAD_SDK_GRANT_UPDATE_CHANNEL = 'plugin_workload_sdk_grant_update'

export interface GrantUpdateNotifyInput {
  recipeNamespace: string
  recipeName: string
  /** Omitted for a whole-recipe revoke that spans multiple families. */
  capabilityFamily?: string
}

/**
 * Build the JSON payload for a grant-update NOTIFY. Pure and exported so the
 * exact wire shape can be unit-tested without a live Postgres.
 */
export function buildGrantUpdateNotifyPayload(input: GrantUpdateNotifyInput): string {
  return JSON.stringify({
    recipeNamespace: input.recipeNamespace,
    recipeName: input.recipeName,
    ...(input.capabilityFamily ? { capabilityFamily: input.capabilityFamily } : {}),
  })
}

/**
 * Emit the grant-update NOTIFY on the shared channel. MUST be called with the
 * in-transaction `db` client so the signal is delivered on COMMIT (and dropped
 * on ROLLBACK), never as a separate connection.
 *
 * A `pg_notify` inside the transaction would roll the whole grant mutation back
 * if it threw — but the only failure mode is a payload over Postgres' 8000-byte
 * NOTIFY limit, and this payload is just a recipe namespace/name (+ optional
 * short family), which cannot approach that bound. So the mutation is never at
 * risk from the notify, and the transactional coupling is intentional.
 */
async function notifyGrantUpdate(
  // issue #375 M3: REQUIRES the branded transaction session — `pool` no longer
  // structurally satisfies this parameter, so the db→pool refactor that breaks
  // COMMIT/ROLLBACK coupling fails to COMPILE instead of merely failing a test.
  db: DbTransactionClient,
  input: GrantUpdateNotifyInput
): Promise<void> {
  await db.query('SELECT pg_notify($1, $2)', [
    PLUGIN_WORKLOAD_SDK_GRANT_UPDATE_CHANNEL,
    buildGrantUpdateNotifyPayload(input),
  ])
}

export const PLUGIN_WORKLOAD_SDK_INVOCATION_STATUSES = [
  'in_progress',
  'complete',
  'failed',
  'provider_unavailable',
  'accepted',
  'delivered',
] as const
export type PluginWorkloadSdkInvocationStatus =
  (typeof PLUGIN_WORKLOAD_SDK_INVOCATION_STATUSES)[number]

/** Structured authorization error codes (spec §15). */
export const PLUGIN_WORKLOAD_SDK_ERROR_CODES = [
  'capability_not_declared',
  'caller_not_allowed',
  'scope_denied',
  'target_not_allowed',
  'ambiguous_model',
  'event_type_not_allowed',
  'quota_exceeded',
  'provider_policy_denied',
  'provider_unavailable',
  'payload_too_large',
  'idempotency_conflict',
  'recipe_binding_mismatch',
  'protocol_mismatch',
] as const
export type PluginWorkloadSdkErrorCode = (typeof PLUGIN_WORKLOAD_SDK_ERROR_CODES)[number]

/**
 * v1 allowlisted purpose enum for promptBridge requests (spec §9). A purpose
 * outside this list is rejected at input validation, before authorization.
 */
export const PLUGIN_WORKLOAD_SDK_PURPOSES = [
  'summarization',
  'classification',
  'extraction',
  'generation',
  'translation',
  'question_answering',
  'analysis',
] as const
export type PluginWorkloadSdkPurpose = (typeof PLUGIN_WORKLOAD_SDK_PURPOSES)[number]

// ─── Input validation defaults (plan §2.7) ──────────────────────────────
export const DEFAULT_MAX_REQUEST_CONTENT_BYTES = 128 * 1024
export const DEFAULT_MAX_TITLE_BYTES = 256
export const DEFAULT_MAX_BODY_BYTES = 4096
export const DEFAULT_IDEMPOTENCY_KEY_PATTERN = '^[a-zA-Z0-9_-]{1,128}$'
export const MAX_IDEMPOTENCY_KEY_LENGTH = 128
export const MAX_TARGET_REF_LENGTH = 256
export const MAX_USER_REF_LENGTH = 256
/** Max length for a single admin allowlist entry. */
export const MAX_ALLOWLIST_ENTRY_LENGTH = 256
export const MAX_ALLOWLIST_ITEMS = 64
/**
 * Maximum number of provider targets an operator may persist in one grant.
 * This is deliberately separate from the execution budget below: a policy may
 * contain a catalogue larger than a single invocation's ordered suffix.
 */
export const MAX_PROMPT_BRIDGE_TARGETS_PER_GRANT = MAX_ALLOWLIST_ITEMS
/** Hard cap for physical provider attempts in one logical invocation. */
export const MAX_PROMPT_BRIDGE_PROVIDER_ATTEMPTS = 4
/** Default lease is the prompt timeout plus a bounded recovery grace period. */
export const DEFAULT_PROMPT_BRIDGE_ATTEMPT_LEASE_SECONDS = 180
const MIN_PROMPT_BRIDGE_ATTEMPT_LEASE_SECONDS = 30
const MAX_PROMPT_BRIDGE_ATTEMPT_LEASE_SECONDS = 3600

/** One operator-controlled lease value is shared by issuance and stale sweep. */
export function getPromptBridgeAttemptLeaseSeconds(): number {
  const raw = Number(process.env.PLUGIN_WORKLOAD_SDK_ATTEMPT_LEASE_SECONDS)
  if (!Number.isInteger(raw)) return DEFAULT_PROMPT_BRIDGE_ATTEMPT_LEASE_SECONDS
  return Math.min(
    Math.max(raw, MIN_PROMPT_BRIDGE_ATTEMPT_LEASE_SECONDS),
    MAX_PROMPT_BRIDGE_ATTEMPT_LEASE_SECONDS
  )
}
/** Idempotency keys are reusable after this window (OQ-5). */
const IDEMPOTENCY_TTL_HOURS = 24

export interface PluginWorkloadSdkQuotaLimits {
  /** Deprecated (issue #348) — accepted and validated on the wire, never persisted. */
  maxRequestsPerRun?: number
  /** Deprecated (issue #348) — accepted and validated on the wire, never persisted. */
  maxNotificationsPerRun?: number
  maxInvocationsPerMinute?: number
  maxNotificationsPerMinute?: number
  /**
   * Per-grant output ceiling. PERSISTED and enforced: it lands as `max_tokens`
   * on API-key providers; codex-subscription cannot bind it on the ChatGPT
   * wire and is bounded there by the contract limit instead. See
   * `AuthorizedPromptBridge.maxOutputTokens`.
   *
   * This said "never persisted" after the per-grant ceiling had already been
   * reactivated. A reader trusting that comment would drop the persistence or the read
   * and hand the regression straight back.
   */
  maxOutputTokens?: number
}

/**
 * Resolved model policy record (plan §2.2): a modelPolicyRef is never a
 * black box — it must resolve to a concrete record. Stored per-grant in
 * model_policies JSONB keyed by policy ref.
 */
export interface PluginWorkloadSdkModelPolicy {
  provider: string
  model: string
  temperature?: number
  maxCostUsd?: number
}

/** A single operator-authorized promptBridge attempt target. */
export interface PluginWorkloadSdkPromptTarget {
  targetRef: string
  provider: string
  model: string
  /**
   * Secret data-key identity only; never its value. Empty for oauth-broker
   * providers (Codex): no static Secret key exists for a brokered grant.
   */
  credentialSlot: string
  /**
   * Codex subscription grant (connection key) this target executes against.
   * Required for oauth-broker providers, absent for API-key providers. The
   * grant is only *chosen* here — it must already exist and be connected.
   * Control-api stamps the matching `clerum.io/codex-connection-ref`
   * annotation; authorize attests that annotation and fail-closes if this
   * field disagrees.
   */
  connectionRef?: string
}

/**
 * Non-secret snapshot of the originally authorized ordered suffix. It is the
 * authority for JIT fallback-ticket issuance; a request body never supplies
 * this list.
 */
export interface PluginWorkloadSdkPromptAuthorization {
  policyRevision: number
  policyHash: string
  authorizedTargetRefs: string[]
}

export interface PluginWorkloadSdkGrant {
  id: string
  recipeNamespace: string
  recipeName: string
  capabilityFamily: PluginWorkloadSdkFamily
  /**
   * Explicit provider bound to a promptBridge grant (R1). NULL for grants
   * written before the provider column existed and for clientNotifications
   * grants. A NULL value is legacy/unreviewed state; neither the API nor the
   * Control UI may infer a routable provider from a flat model list.
   */
  provider: string | null
  allowedModels: string[]
  allowedEventTypes: string[]
  allowedTargetRefs: string[]
  allowedUserRefs: string[]
  allowedCallers: string[]
  quotaLimits: PluginWorkloadSdkQuotaLimits
  modelPolicies: Record<string, PluginWorkloadSdkModelPolicy>
  /** Empty/null means a legacy grant and is deliberately not routable. */
  promptTargets: PluginWorkloadSdkPromptTarget[]
  defaultTargetRef: string | null
  policyState: 'active' | 'legacy_unreviewed' | 'revoking' | 'disabled'
  policyRevision: number
  /** Durable proof that an operator explicitly saved the current policy. */
  policyReviewProvenancePresent?: boolean
  /** Kill-switch epoch. Non-null while a recipe is revoking or disabled. */
  revocationId: string | null
  createdAt: string
  updatedAt: string
}

/**
 * Single readiness predicate for the provider-free notification capability.
 * An active grant without a usable recipient allowlist cannot deliver a
 * notification, so it must never be advertised as validated or admitted by
 * the authorizer.
 */
export function isClientNotificationsPolicyReady(
  grant: Pick<
    PluginWorkloadSdkGrant,
    'policyState' | 'allowedCallers' | 'allowedEventTypes' | 'allowedTargetRefs' | 'allowedUserRefs'
  >
): boolean {
  return (
    grant.policyState === 'active' &&
    grant.allowedCallers.length > 0 &&
    grant.allowedEventTypes.length > 0 &&
    grant.allowedTargetRefs.length + grant.allowedUserRefs.length > 0
  )
}

const CONTROL_PLANE_USER_UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i

/**
 * Resolve the destination half of the notification contract against the same
 * tables used by delivery. A non-empty JSON allowlist is not sufficient:
 * deleted users and disabled medium identities must not make a recipe appear
 * validated when every send would remain queued or be dropped. A transaction
 * client may be supplied so an admin upsert and its readiness check share one
 * database boundary.
 */
export async function hasUsableClientNotificationRecipients(
  grant: Pick<PluginWorkloadSdkGrant, 'allowedTargetRefs' | 'allowedUserRefs'>,
  db: Pick<DbClient, 'query'> = pool
): Promise<boolean> {
  const userRefs = grant.allowedUserRefs.filter(ref => CONTROL_PLANE_USER_UUID_RE.test(ref))
  if (userRefs.length > 0) {
    const users = await db.query(
      `SELECT id
         FROM users
        WHERE id = ANY($1::uuid[])
          AND email IS NOT NULL
          AND btrim(email) <> ''
        LIMIT 1`,
      [userRefs]
    )
    if (users.rows.length > 0) return true
  }

  const targetRefs = grant.allowedTargetRefs.filter(ref => ref.trim().length > 0)
  if (targetRefs.length === 0) return false

  // Opaque targetRef delivery is backed by verified, non-disabled medium
  // identities; arbitrary strings are intentionally not treated as usable.
  const mediumAccounts = await db.query(
    `SELECT 1
       FROM workflow_approval_medium_accounts
      WHERE provider_user_id = ANY($1::text[])
        AND disabled_at IS NULL
      LIMIT 1`,
    [targetRefs]
  )
  return mediumAccounts.rows.length > 0
}

export interface PluginWorkloadSdkInvocationRecord {
  id: string
  recipeNamespace: string
  recipeName: string
  callerRef: string
  correlationId: string | null
  method: PluginWorkloadSdkFamily
  /** Model name (promptBridge) or event type (clientNotifications). */
  detail: string
  purpose: string | null
  idempotencyKeyHash: string
  payloadHash: string
  status: PluginWorkloadSdkInvocationStatus
  quotaConsumed: boolean
  authorizationDecision: string
  contractVersion: 1 | 2
  promptAuthorization: PluginWorkloadSdkPromptAuthorization | null
  /** Monotonic physical-attempt generation; increments on failed replay. */
  attemptGeneration: number
  leaseExpiresAt: string | null
  createdAt: string
  updatedAt: string
  completedAt: string | null
}

// Schema lives in pluginWorkloadSdkSchema.ts (type-only db dependency) so
// db.ts can register migration 0034_plugin_workload_sdk without a runtime
// import cycle.
export { applyPluginWorkloadSdkSchema } from './pluginWorkloadSdkSchema.js'

// ─── Hashing helpers ─────────────────────────────────────────────────────

/** SHA-256 of the raw idempotency key — never store the raw key (spec §16). */
export function hashIdempotencyKey(idempotencyKey: string): string {
  return createHash('sha256').update(idempotencyKey).digest('hex')
}

/**
 * Deterministic payload hash for idempotency conflict detection. Uses
 * stableStringify (lexicographic key order at every depth) so semantically
 * equivalent JSON with different key ordering produces the same hash —
 * consistent with RFC 8785 JCS for the JSON subset we accept.
 */
export function hashPayload(payload: unknown): string {
  return createHash('sha256').update(stableStringify(payload)).digest('hex')
}

/** Stable fingerprint of the complete ordered policy; contains no secret values. */
export function hashPromptTargetPolicy(
  grant: Pick<PluginWorkloadSdkGrant, 'policyRevision' | 'defaultTargetRef' | 'promptTargets'>
): string {
  return createHash('sha256')
    .update(
      stableStringify({
        revision: grant.policyRevision,
        defaultTargetRef: grant.defaultTargetRef,
        targets: grant.promptTargets,
      })
    )
    .digest('hex')
}

// ─── Row mapping ─────────────────────────────────────────────────────────

function stringArray(value: unknown): string[] {
  return Array.isArray(value) ? value.filter((v): v is string => typeof v === 'string') : []
}

function promptTargets(value: unknown): PluginWorkloadSdkPromptTarget[] {
  if (!Array.isArray(value)) return []
  return value.flatMap(raw => {
    if (typeof raw !== 'object' || raw === null) return []
    const target = raw as Record<string, unknown>
    if (
      typeof target.targetRef !== 'string' ||
      typeof target.provider !== 'string' ||
      typeof target.model !== 'string' ||
      typeof target.credentialSlot !== 'string' ||
      (target.connectionRef !== undefined && typeof target.connectionRef !== 'string')
    ) {
      return []
    }
    return [
      {
        targetRef: target.targetRef,
        provider: target.provider,
        model: target.model,
        credentialSlot: target.credentialSlot,
        ...(typeof target.connectionRef === 'string' && target.connectionRef
          ? { connectionRef: target.connectionRef }
          : {}),
      },
    ]
  })
}

export function mapGrantRow(row: Record<string, unknown>): PluginWorkloadSdkGrant {
  const family = row.capability_family
  if (!PLUGIN_WORKLOAD_SDK_FAMILIES.includes(family as PluginWorkloadSdkFamily)) {
    throw new Error(`unknown capability_family from db: ${String(family)}`)
  }
  const quotaLimitsRaw = row.quota_limits
  const quotaLimits: PluginWorkloadSdkQuotaLimits =
    typeof quotaLimitsRaw === 'object' && quotaLimitsRaw !== null
      ? (quotaLimitsRaw as PluginWorkloadSdkQuotaLimits)
      : {}
  const modelPoliciesRaw = row.model_policies
  const modelPolicies: Record<string, PluginWorkloadSdkModelPolicy> =
    typeof modelPoliciesRaw === 'object' && modelPoliciesRaw !== null
      ? (modelPoliciesRaw as Record<string, PluginWorkloadSdkModelPolicy>)
      : {}
  return {
    id: String(row.id),
    recipeNamespace: String(row.recipe_namespace),
    recipeName: String(row.recipe_name),
    capabilityFamily: family as PluginWorkloadSdkFamily,
    provider: row.provider == null ? null : String(row.provider),
    allowedModels: stringArray(row.allowed_models),
    allowedEventTypes: stringArray(row.allowed_event_types),
    allowedTargetRefs: stringArray(row.allowed_target_refs),
    allowedUserRefs: stringArray(row.allowed_user_refs),
    allowedCallers: stringArray(row.allowed_callers),
    quotaLimits,
    modelPolicies,
    promptTargets: promptTargets(row.prompt_targets),
    defaultTargetRef: row.default_target_ref == null ? null : String(row.default_target_ref),
    policyState:
      row.policy_state === 'legacy_unreviewed' ||
      row.policy_state === 'revoking' ||
      row.policy_state === 'disabled'
        ? (row.policy_state as 'legacy_unreviewed' | 'revoking' | 'disabled')
        : 'active',
    policyRevision:
      typeof row.policy_revision === 'number' && Number.isInteger(row.policy_revision)
        ? row.policy_revision
        : 0,
    policyReviewProvenancePresent:
      row.policy_reviewed_at != null &&
      typeof row.policy_reviewed_by === 'string' &&
      row.policy_reviewed_by.trim().length > 0,
    revocationId: row.revocation_id == null ? null : String(row.revocation_id),
    createdAt: new Date(row.created_at as string).toISOString(),
    updatedAt: new Date(row.updated_at as string).toISOString(),
  }
}

function mapInvocationRow(row: Record<string, unknown>): PluginWorkloadSdkInvocationRecord {
  return {
    id: String(row.id),
    recipeNamespace: String(row.recipe_namespace),
    recipeName: String(row.recipe_name),
    callerRef: String(row.caller_ref),
    correlationId: row.correlation_id === null ? null : String(row.correlation_id),
    method: row.method as PluginWorkloadSdkFamily,
    detail: String(row.detail),
    purpose: row.purpose === null ? null : String(row.purpose),
    idempotencyKeyHash: String(row.idempotency_key_hash),
    payloadHash: String(row.payload_hash),
    status: row.status as PluginWorkloadSdkInvocationStatus,
    quotaConsumed: row.quota_consumed === true,
    authorizationDecision: String(row.authorization_decision),
    contractVersion: row.contract_version === 1 ? 1 : 2,
    promptAuthorization: promptAuthorization(row.prompt_authorization),
    attemptGeneration:
      typeof row.attempt_generation === 'number' && Number.isInteger(row.attempt_generation)
        ? Math.max(row.attempt_generation, 1)
        : 1,
    leaseExpiresAt:
      row.lease_expires_at == null ? null : new Date(row.lease_expires_at as string).toISOString(),
    createdAt: new Date(row.created_at as string).toISOString(),
    updatedAt: new Date((row.updated_at ?? row.created_at) as string).toISOString(),
    completedAt:
      row.completed_at === null ? null : new Date(row.completed_at as string).toISOString(),
  }
}

function promptAuthorization(value: unknown): PluginWorkloadSdkPromptAuthorization | null {
  if (typeof value !== 'object' || value === null || Array.isArray(value)) return null
  const record = value as Record<string, unknown>
  if (
    typeof record.policyRevision !== 'number' ||
    !Number.isInteger(record.policyRevision) ||
    record.policyRevision < 1 ||
    typeof record.policyHash !== 'string' ||
    !/^[a-f0-9]{64}$/.test(record.policyHash) ||
    !Array.isArray(record.authorizedTargetRefs) ||
    record.authorizedTargetRefs.length === 0 ||
    !record.authorizedTargetRefs.every(ref => typeof ref === 'string' && ref.length > 0)
  ) {
    return null
  }
  return {
    policyRevision: record.policyRevision,
    policyHash: record.policyHash,
    authorizedTargetRefs: [...record.authorizedTargetRefs],
  }
}

// ─── Grants ──────────────────────────────────────────────────────────────

export async function findGrant(
  recipeNamespace: string,
  recipeName: string,
  capabilityFamily: PluginWorkloadSdkFamily
): Promise<PluginWorkloadSdkGrant | null> {
  const result = await pool.query(
    `SELECT * FROM plugin_workload_sdk_grants
     WHERE recipe_namespace = $1 AND recipe_name = $2 AND capability_family = $3`,
    [recipeNamespace, recipeName, capabilityFamily]
  )
  const row = result.rows[0] as Record<string, unknown> | undefined
  return row ? mapGrantRow(row) : null
}

export interface UpsertGrantParams {
  recipeNamespace: string
  recipeName: string
  capabilityFamily: PluginWorkloadSdkFamily
  /**
   * Explicit provider for promptBridge grants (R1). The route requires it for
   * promptBridge and omits it for clientNotifications. Because the upsert is a
   * full-column overwrite, a promptBridge update MUST always carry the provider
   * or it would be wiped to NULL.
   */
  provider?: string
  allowedModels?: string[]
  allowedEventTypes?: string[]
  allowedTargetRefs?: string[]
  allowedUserRefs?: string[]
  allowedCallers?: string[]
  quotaLimits?: PluginWorkloadSdkQuotaLimits
  modelPolicies?: Record<string, PluginWorkloadSdkModelPolicy>
  promptTargets?: PluginWorkloadSdkPromptTarget[]
  defaultTargetRef?: string
}

export async function upsertGrant(
  params: UpsertGrantParams,
  operatorSub: string,
  // When the caller already runs inside a carrier transaction (the grant
  // write-gate, which holds the per-model advisory locks — R1-H3 fase 2), the
  // upsert MUST reuse that same transaction so the recipe lock is taken AFTER
  // the model locks (global order: `llm-model:*` before `plugin_workload_sdk:*`)
  // and the enabled-ness revalidation + the write commit atomically. Every other
  // caller passes no `db` and gets its own transaction, exactly as before.
  // The carrier MUST be a real transaction session (branded, issue #375 M3):
  // it holds the advisory locks and `notifyGrantUpdate` refuses `pool`.
  db?: DbTransactionClient
): Promise<PluginWorkloadSdkGrant> {
  if (db) return upsertGrantInTransaction(params, operatorSub, db)
  return withTransaction(inner => upsertGrantInTransaction(params, operatorSub, inner))
}

async function upsertGrantInTransaction(
  params: UpsertGrantParams,
  operatorSub: string,
  db: DbTransactionClient
): Promise<PluginWorkloadSdkGrant> {
  {
    // All capability families for a recipe share one lock. A family-scoped
    // lock permits an SDK-only revoke to race an upsert for the other family.
    const recipeLock = `plugin_workload_sdk:${params.recipeNamespace}/${params.recipeName}`
    await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [recipeLock])
    const previous = await db.query(
      `SELECT allowed_user_refs, policy_state, revocation_id
         FROM plugin_workload_sdk_grants
        WHERE recipe_namespace = $1 AND recipe_name = $2 AND capability_family = $3
        FOR UPDATE`,
      [params.recipeNamespace, params.recipeName, params.capabilityFamily]
    )
    const previousRow = previous.rows[0] as { allowed_user_refs?: unknown } | undefined
    const recipeState = await db.query(
      `SELECT policy_state, revocation_id
         FROM plugin_workload_sdk_grants
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND policy_state IN ('revoking', 'disabled')
        ORDER BY updated_at DESC
        LIMIT 1
        FOR UPDATE`,
      [params.recipeNamespace, params.recipeName]
    )
    if (recipeState.rows.length > 0) {
      throw new Error(
        `Plugin Workload SDK policy for ${params.recipeNamespace}/${params.recipeName} is ${String((recipeState.rows[0] as { policy_state: unknown }).policy_state)} and cannot be reactivated`
      )
    }
    const previousUserRefs = Array.isArray(previousRow?.allowed_user_refs)
      ? previousRow.allowed_user_refs.map(String)
      : []
    if (
      params.capabilityFamily === 'clientNotifications' &&
      !(await hasUsableClientNotificationRecipients(
        {
          allowedTargetRefs: params.allowedTargetRefs ?? [],
          allowedUserRefs: params.allowedUserRefs ?? [],
        },
        db
      ))
    ) {
      throw new Error(
        'clientNotifications policy requires at least one existing user or verified notification target'
      )
    }
    const result = await db.query(
      `INSERT INTO plugin_workload_sdk_grants
         (recipe_namespace, recipe_name, capability_family, provider, allowed_models,
         allowed_event_types, allowed_target_refs, allowed_user_refs, allowed_callers,
         quota_limits, model_policies, prompt_targets, default_target_ref,
         policy_reviewed_at, policy_reviewed_by)
       VALUES ($1, $2, $3, $4, $5::jsonb, $6::jsonb, $7::jsonb, $8::jsonb, $9::jsonb, $10::jsonb, $11::jsonb, $12::jsonb, $13, now(), $14)
       ON CONFLICT (recipe_namespace, recipe_name, capability_family)
       DO UPDATE SET
         provider = EXCLUDED.provider,
         allowed_models = EXCLUDED.allowed_models,
         allowed_event_types = EXCLUDED.allowed_event_types,
         allowed_target_refs = EXCLUDED.allowed_target_refs,
         allowed_user_refs = EXCLUDED.allowed_user_refs,
         allowed_callers = EXCLUDED.allowed_callers,
         quota_limits = EXCLUDED.quota_limits,
         model_policies = EXCLUDED.model_policies,
         prompt_targets = EXCLUDED.prompt_targets,
         default_target_ref = EXCLUDED.default_target_ref,
         -- A stale upsert must never undo a kill-switch. The recipe-wide
         -- lock plus the guard above makes this branch defensive for rows
         -- created by older binaries that did not know revocation_id.
         policy_state = CASE
           WHEN plugin_workload_sdk_grants.policy_state IN ('revoking', 'disabled')
           THEN plugin_workload_sdk_grants.policy_state
           ELSE 'active'
         END,
         revocation_id = CASE
           WHEN plugin_workload_sdk_grants.policy_state IN ('revoking', 'disabled')
           THEN plugin_workload_sdk_grants.revocation_id
           ELSE NULL
         END,
         policy_revision = plugin_workload_sdk_grants.policy_revision + 1,
         policy_reviewed_at = now(),
         policy_reviewed_by = EXCLUDED.policy_reviewed_by,
         updated_at = now()
       RETURNING *`,
      [
        params.recipeNamespace,
        params.recipeName,
        params.capabilityFamily,
        params.provider ?? null,
        JSON.stringify(params.allowedModels ?? []),
        JSON.stringify(params.allowedEventTypes ?? []),
        JSON.stringify(params.allowedTargetRefs ?? []),
        JSON.stringify(params.allowedUserRefs ?? []),
        JSON.stringify(params.allowedCallers ?? []),
        JSON.stringify(params.quotaLimits ?? {}),
        JSON.stringify(params.modelPolicies ?? {}),
        JSON.stringify(params.promptTargets ?? []),
        params.defaultTargetRef ?? null,
        operatorSub,
      ]
    )
    const grant = mapGrantRow(result.rows[0] as Record<string, unknown>)
    const currentUserRefs = grant.allowedUserRefs
    const addedUserRefs = currentUserRefs.filter(ref => !previousUserRefs.includes(ref))
    const removedUserRefs = previousUserRefs.filter(ref => !currentUserRefs.includes(ref))
    const referencedUsers = [...new Set([...addedUserRefs, ...removedUserRefs])]
    const resolvedUsers = new Set<string>()
    if (referencedUsers.length > 0) {
      const users = await db.query(`SELECT id::text AS id FROM users WHERE id = ANY($1::uuid[])`, [
        referencedUsers,
      ])
      for (const row of users.rows) resolvedUsers.add(String((row as { id: string }).id))
    }
    const resourceRef = `plugin_workload_sdk:${params.recipeNamespace}/${params.recipeName}:${params.capabilityFamily}`
    const changes: ControlApiPermissionChange[] = [
      {
        action: 'grant' as const,
        resourceClass: 'plugin_workload_sdk_access',
        resourceRef,
        subject: { kind: 'service' as const, id: params.capabilityFamily },
        namespace: params.recipeNamespace,
        sourceAuditRef: `plugin_workload_sdk_grants:${grant.id}`,
        status: 'configured',
      },
      ...addedUserRefs
        .filter(userRef => resolvedUsers.has(userRef))
        .map(userRef => ({
          action: 'grant' as const,
          resourceClass: 'plugin_workload_sdk_access',
          resourceRef,
          subject: { kind: 'user' as const, id: userRef },
          namespace: params.recipeNamespace,
          sourceAuditRef: `plugin_workload_sdk_grants:${grant.id}`,
        })),
      ...removedUserRefs
        .filter(userRef => resolvedUsers.has(userRef))
        .map(userRef => ({
          action: 'revoke' as const,
          resourceClass: 'plugin_workload_sdk_access',
          resourceRef,
          subject: { kind: 'user' as const, id: userRef },
          namespace: params.recipeNamespace,
          sourceAuditRef: `plugin_workload_sdk_grants:${grant.id}`,
        })),
    ]
    await appendControlApiPermissionEventsInTransaction(db, { operatorSub, changes })
    // issue #375 (P3): nudge the WRC to re-reconcile now that the grant changed.
    await notifyGrantUpdate(db, {
      recipeNamespace: params.recipeNamespace,
      recipeName: params.recipeName,
      capabilityFamily: params.capabilityFamily,
    })
    return grant
  }
}

export async function listGrants(
  filter?: {
    recipeNamespace?: string
    recipeName?: string
  },
  // Accepts a transaction client so the grant write-gate can read the stored
  // grant (Pieza D no-worsening context) on the SAME connection that holds the
  // model advisory locks — no extra pool checkout under the lock (adenda A3).
  // Defaults to the global pool for every other (unlocked) caller.
  db: Pick<DbClient, 'query'> = pool
): Promise<PluginWorkloadSdkGrant[]> {
  const clauses: string[] = []
  const values: unknown[] = []
  if (filter?.recipeNamespace) {
    values.push(filter.recipeNamespace)
    clauses.push(`recipe_namespace = $${values.length}`)
  }
  if (filter?.recipeName) {
    values.push(filter.recipeName)
    clauses.push(`recipe_name = $${values.length}`)
  }
  const where = clauses.length > 0 ? `WHERE ${clauses.join(' AND ')}` : ''
  const result = await db.query(
    `SELECT * FROM plugin_workload_sdk_grants ${where}
     ORDER BY recipe_namespace, recipe_name, capability_family`,
    values
  )
  return (result.rows as Record<string, unknown>[]).map(mapGrantRow)
}

/**
 * Grants whose `allowed_models` list names `model` — the 4th source of the
 * LLM-model impact enumeration (Fase 3, `llmModelImpact.ts`).
 *
 * MATCH BY MODEL NAME ONLY, deliberately NOT by `(provider, model)`. The
 * `allowed_models` column is a provider-LESS flat model-name list and is NOT
 * enforced to hold only models of the grant's `provider` column: the write-gate
 * (`routes/admin/pluginWorkloadSdk.ts`) validates `prompt_targets` per-provider,
 * but `allowed_models` is parsed from the request body free-form and passed
 * straight through — and since `prompt_targets` can span multiple providers, the
 * mirrored `allowed_models` set can too. Filtering by `provider` would therefore
 * UNDER-report references, the unsafe direction for a safety gate that gates a
 * destructive disable/delete. Matching by model name may over-report a same-named
 * model under a different provider (extra operator friction, never silent
 * breakage) — the fail-safe trade-off. This is exercised by the realPostgres
 * integration test `db.listGrantsReferencingModel.realPostgres.integration`.
 *
 * No `policy_state` and no `provider IS NOT NULL` filter: a grant is surfaced
 * whenever it names the model, including legacy NULL-provider and
 * `revoking`/`disabled` rows (fail-loud — never hide a dangling reference).
 *
 * `allowed_models @> to_jsonb($1::text)` is jsonb array-contains-scalar: for a
 * text `model`, `to_jsonb('m'::text)` is the JSON string `"m"`, and a jsonb
 * array `@>` a scalar is true when the array contains that element.
 */
export async function listGrantsReferencingModel(
  model: string,
  db: DbClient = pool
): Promise<PluginWorkloadSdkGrant[]> {
  const result = await db.query(
    `SELECT * FROM plugin_workload_sdk_grants
      WHERE allowed_models @> to_jsonb($1::text)
      ORDER BY recipe_namespace, recipe_name, capability_family`,
    [model]
  )
  return (result.rows as Record<string, unknown>[]).map(mapGrantRow)
}

/**
 * Read-only migration gate for promptBridge rows written before the ordered
 * target policy existed. Legacy rows are intentionally never inferred or
 * rewritten here; operators get only non-secret identifiers, shape flags, and
 * reasons that must be resolved by an explicit policy upsert.
 */
export interface PluginWorkloadSdkLegacyGrantInventoryItem {
  id: string
  recipeNamespace: string
  recipeName: string
  policyState: string
  policyRevision: number
  providerPresent: boolean
  policyReviewProvenancePresent: boolean
  promptTargetsCount: number
  defaultTargetRefPresent: boolean
  reasons: string[]
}

export interface PluginWorkloadSdkLegacyGrantInventory {
  totalPromptBridgeGrants: number
  legacyPromptBridgeGrants: number
  activationReady: boolean
  items: PluginWorkloadSdkLegacyGrantInventoryItem[]
}

export async function getPluginWorkloadSdkLegacyGrantInventory(filter?: {
  recipeNamespace?: string
  recipeName?: string
}): Promise<PluginWorkloadSdkLegacyGrantInventory> {
  const clauses = ["capability_family = 'promptBridge'"]
  const values: unknown[] = []
  if (filter?.recipeNamespace) {
    values.push(filter.recipeNamespace)
    clauses.push(`recipe_namespace = $${values.length}`)
  }
  if (filter?.recipeName) {
    values.push(filter.recipeName)
    clauses.push(`recipe_name = $${values.length}`)
  }
  const result = await pool.query(
    `SELECT id, recipe_namespace, recipe_name, policy_state, policy_revision,
            provider, prompt_targets, default_target_ref, policy_reviewed_at,
            policy_reviewed_by
       FROM plugin_workload_sdk_grants
      WHERE ${clauses.join(' AND ')}
      ORDER BY recipe_namespace, recipe_name`,
    values
  )

  const items = (result.rows as Record<string, unknown>[]).flatMap(row => {
    const rawTargets = Array.isArray(row.prompt_targets) ? row.prompt_targets : []
    const targets = promptTargets(row.prompt_targets)
    const policyState = String(row.policy_state ?? '')
    const policyRevision =
      typeof row.policy_revision === 'number' && Number.isInteger(row.policy_revision)
        ? row.policy_revision
        : Number(row.policy_revision ?? 0)
    const providerPresent = typeof row.provider === 'string' && row.provider.trim().length > 0
    const policyReviewProvenancePresent =
      row.policy_reviewed_at != null &&
      typeof row.policy_reviewed_by === 'string' &&
      row.policy_reviewed_by.trim().length > 0
    const defaultTargetRefPresent =
      typeof row.default_target_ref === 'string' && row.default_target_ref.trim().length > 0
    const reasons: string[] = []
    if (policyState === 'legacy_unreviewed') reasons.push('legacy_policy_state')
    if (!policyReviewProvenancePresent) reasons.push('missing_operator_review_provenance')
    if (!providerPresent) reasons.push('missing_provider')
    if (rawTargets.length !== targets.length) reasons.push('invalid_prompt_targets')
    if (targets.length === 0) reasons.push('empty_prompt_targets')
    if (!defaultTargetRefPresent) reasons.push('missing_default_target')
    if (policyRevision < 1) reasons.push('invalid_policy_revision')
    if (defaultTargetRefPresent && targets[0]?.targetRef !== String(row.default_target_ref)) {
      reasons.push('default_target_not_first')
    }
    const targetRefs = targets.map(target => target.targetRef)
    if (new Set(targetRefs).size !== targetRefs.length) reasons.push('duplicate_target_refs')
    if (reasons.length === 0) return []
    return [
      {
        id: String(row.id),
        recipeNamespace: String(row.recipe_namespace),
        recipeName: String(row.recipe_name),
        policyState,
        policyRevision: Number.isFinite(policyRevision) ? policyRevision : 0,
        providerPresent,
        policyReviewProvenancePresent,
        promptTargetsCount: targets.length,
        defaultTargetRefPresent,
        reasons,
      },
    ]
  })
  return {
    totalPromptBridgeGrants: result.rows.length,
    legacyPromptBridgeGrants: items.length,
    activationReady: items.length === 0,
    items,
  }
}

export type PluginWorkloadSdkRevocationState = 'missing' | 'revoking' | 'disabled' | 'conflict'

export interface PluginWorkloadSdkRevocationReceipt {
  state: PluginWorkloadSdkRevocationState
  revocationId?: string
  revoked: number
  fencedInvocations: number
  disabled?: number
}

export interface PluginWorkloadSdkRevocationActor {
  operatorSub: string
  internalPrincipal: AdministrativeEventSubmitterPrincipalV1
}

/**
 * Fence every SDK capability for a recipe before its runtime resources are
 * torn down. This is deliberately recipe-scoped and transactional: the
 * recipe-wide advisory lock is the linearization point shared with upserts,
 * authorization, JIT issuance, and ticket redemption. A receipt epoch is
 * returned so only the reconcile that fenced this exact policy can finalize it.
 */
export async function revokePluginWorkloadSdkForRecipe(
  recipeNamespace: string,
  recipeName: string,
  actor: PluginWorkloadSdkRevocationActor
): Promise<PluginWorkloadSdkRevocationReceipt> {
  return withTransaction(async db => {
    const recipeLock = `plugin_workload_sdk:${recipeNamespace}/${recipeName}`
    await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [recipeLock])
    const grants = await db.query(
      `SELECT id, capability_family, policy_state, revocation_id
         FROM plugin_workload_sdk_grants
        WHERE recipe_namespace = $1 AND recipe_name = $2
        FOR UPDATE`,
      [recipeNamespace, recipeName]
    )
    if (grants.rows.length === 0) {
      return { state: 'missing', revoked: 0, fencedInvocations: 0 }
    }

    const revocationIds = [
      ...new Set(
        grants.rows
          .map(row => (row as { revocation_id?: unknown }).revocation_id)
          .filter((value): value is string => typeof value === 'string' && value.length > 0)
          .map(String)
      ),
    ]
    if (revocationIds.length > 1) {
      return {
        state: 'conflict',
        revoked: 0,
        fencedInvocations: 0,
      }
    }
    const currentRevocationId = revocationIds[0] ?? randomUUID()
    const hasActivePolicy = grants.rows.some(row =>
      ['active', 'legacy_unreviewed'].includes(
        String((row as { policy_state: unknown }).policy_state)
      )
    )
    const hasRevokingPolicy = grants.rows.some(
      row => String((row as { policy_state: unknown }).policy_state) === 'revoking'
    )

    const revoked = await db.query(
      `UPDATE plugin_workload_sdk_grants
          SET policy_state = 'revoking',
              policy_revision = policy_revision + 1,
              revocation_id = COALESCE(revocation_id, $3::uuid),
              updated_at = now()
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND policy_state IN ('active', 'legacy_unreviewed')`,
      [recipeNamespace, recipeName, currentRevocationId]
    )

    const fenced = await db.query(
      `UPDATE plugin_workload_sdk_invocations
          SET status = 'failed',
              authorization_decision = 'revoked',
              updated_at = now(),
              lease_expires_at = NULL,
              completed_at = COALESCE(completed_at, now())
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND status = 'in_progress'
        RETURNING id`,
      [recipeNamespace, recipeName]
    )
    await db.query(
      `UPDATE plugin_workload_sdk_invocation_attempts
          SET status = 'failed',
              lease_expires_at = NULL,
              completed_at = COALESCE(completed_at, now())
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND status = 'in_progress'`,
      [recipeNamespace, recipeName]
    )
    await db.query(
      `UPDATE plugin_workload_sdk_provider_attempts
          SET status = 'failed',
              lease_expires_at = NULL,
              completed_at = COALESCE(completed_at, now())
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND status IN ('reserved', 'in_progress')`,
      [recipeNamespace, recipeName]
    )

    const changes: ControlApiPermissionChange[] = grants.rows
      .filter(row =>
        ['active', 'legacy_unreviewed'].includes(
          String((row as { policy_state: unknown }).policy_state)
        )
      )
      .map(row => ({
        action: 'revoke' as const,
        resourceClass: 'plugin_workload_sdk_access',
        resourceRef: `plugin_workload_sdk:${recipeNamespace}/${recipeName}:${String((row as { capability_family: unknown }).capability_family)}`,
        subject: {
          kind: 'service' as const,
          id: String((row as { capability_family: unknown }).capability_family),
        },
        namespace: recipeNamespace,
        sourceAuditRef: `plugin_workload_sdk_grants:${String((row as { id: unknown }).id)}`,
        status: 'revoking',
      }))
    if (changes.length > 0) {
      await appendControlApiPermissionEventsInTransaction(db, {
        operatorSub: actor.operatorSub,
        internalPrincipal: actor.internalPrincipal,
        changes,
      })
    }
    // issue #375 (P3): a whole-recipe revoke spans every family, so omit the
    // capabilityFamily — the WRC only needs the recipe coordinates to re-reconcile.
    // issue #375 (B2): notify ONLY on an actual transition (rows moved to
    // 'revoking', or permission changes emitted). A no-op revoke — every grant
    // already revoking/disabled — must stay silent, otherwise the WRC would
    // force-reconcile the still-cached recipe, which can re-enter revoke and
    // NOTIFY again: a reconcile+pg_notify spin. Matches upsert/delete "notify
    // only on a real mutation" semantics.
    if ((revoked.rowCount ?? 0) > 0 || changes.length > 0) {
      await notifyGrantUpdate(db, { recipeNamespace, recipeName })
    }
    return {
      state:
        hasActivePolicy || hasRevokingPolicy || (revoked.rowCount ?? 0) > 0
          ? 'revoking'
          : 'disabled',
      revocationId: currentRevocationId,
      revoked: revoked.rowCount ?? 0,
      fencedInvocations: fenced.rowCount ?? 0,
    }
  })
}

/**
 * Complete a previously fenced revocation only after the caller has proved
 * that the recipe-bound SDK endpoint and credentials are gone. The state
 * transition is intentionally impossible from `active`, preventing callers
 * from declaring a kill-switch success without the teardown boundary.
 *
 * issue #375 (B2 counterpart): finalize (`revoking`→`disabled`) deliberately
 * emits NO grant-update pg_notify — it is terminal cleanup reachable only after
 * teardown is proven, its only caller/consumer is the WRC itself, and the
 * visible `revoke` transition already notified.
 */
export async function finalizePluginWorkloadSdkRevocation(
  recipeNamespace: string,
  recipeName: string,
  expectedRevocationId: string,
  actor: PluginWorkloadSdkRevocationActor
): Promise<PluginWorkloadSdkRevocationReceipt> {
  return withTransaction(async db => {
    const recipeLock = `plugin_workload_sdk:${recipeNamespace}/${recipeName}`
    await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [recipeLock])
    const grants = await db.query(
      `SELECT id, capability_family, policy_state, revocation_id
         FROM plugin_workload_sdk_grants
        WHERE recipe_namespace = $1 AND recipe_name = $2
        FOR UPDATE`,
      [recipeNamespace, recipeName]
    )
    if (grants.rows.length === 0) {
      return { state: 'missing', revoked: 0, fencedInvocations: 0, disabled: 0 }
    }
    const hasActive = grants.rows.some(row =>
      ['active', 'legacy_unreviewed'].includes(
        String((row as { policy_state: unknown }).policy_state)
      )
    )
    const hasWrongEpoch = grants.rows.some(row => {
      const state = String((row as { policy_state: unknown }).policy_state)
      return (
        (state === 'revoking' || state === 'disabled') &&
        String((row as { revocation_id?: unknown }).revocation_id ?? '') !== expectedRevocationId
      )
    })
    if (hasActive || hasWrongEpoch) {
      return {
        state: 'conflict',
        revocationId: expectedRevocationId,
        revoked: 0,
        fencedInvocations: 0,
      }
    }
    const result = await db.query(
      `UPDATE plugin_workload_sdk_grants
          SET policy_state = 'disabled',
              updated_at = now()
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND policy_state = 'revoking'
          AND revocation_id = $3::uuid
        RETURNING id, capability_family`,
      [recipeNamespace, recipeName, expectedRevocationId]
    )
    const disabledRows =
      result.rows.length > 0
        ? result.rows
        : grants.rows.filter(
            row => String((row as { policy_state: unknown }).policy_state) === 'disabled'
          )
    if (disabledRows.length === 0) {
      return {
        state: 'conflict',
        revocationId: expectedRevocationId,
        revoked: 0,
        fencedInvocations: 0,
      }
    }
    const changes: ControlApiPermissionChange[] = result.rows.map(row => ({
      action: 'revoke' as const,
      resourceClass: 'plugin_workload_sdk_access',
      resourceRef: `plugin_workload_sdk:${recipeNamespace}/${recipeName}:${String((row as { capability_family: unknown }).capability_family)}`,
      subject: {
        kind: 'service' as const,
        id: String((row as { capability_family: unknown }).capability_family),
      },
      namespace: recipeNamespace,
      sourceAuditRef: `plugin_workload_sdk_grants:${String((row as { id: unknown }).id)}`,
      status: 'disabled',
    }))
    if (changes.length > 0) {
      await appendControlApiPermissionEventsInTransaction(db, {
        operatorSub: actor.operatorSub,
        internalPrincipal: actor.internalPrincipal,
        changes,
      })
    }
    return {
      state: 'disabled',
      revocationId: expectedRevocationId,
      revoked: 0,
      fencedInvocations: 0,
      disabled: result.rowCount ?? 0,
    }
  })
}

/**
 * Delete a grant by UUID, scoped to its recipe binding. The recipe_namespace
 * + recipe_name predicates prevent a UUID alone from deleting a grant that
 * belongs to a different recipe than the caller intended (defense in depth on
 * top of the admin auth gate).
 */
export async function deleteGrant(
  id: string,
  recipeNamespace: string,
  recipeName: string,
  operatorSub: string
): Promise<boolean> {
  return withTransaction(async db => {
    await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [
      `plugin_workload_sdk:${recipeNamespace}/${recipeName}`,
    ])
    const result = await db.query(
      `DELETE FROM plugin_workload_sdk_grants
       WHERE id = $1 AND recipe_namespace = $2 AND recipe_name = $3
       RETURNING *`,
      [id, recipeNamespace, recipeName]
    )
    if ((result.rowCount ?? 0) === 0) return false
    const grant = mapGrantRow(result.rows[0] as Record<string, unknown>)
    const resolvedUsers = new Set<string>()
    if (grant.allowedUserRefs.length > 0) {
      const users = await db.query(`SELECT id::text AS id FROM users WHERE id = ANY($1::uuid[])`, [
        grant.allowedUserRefs,
      ])
      for (const row of users.rows) resolvedUsers.add(String((row as { id: string }).id))
    }
    const resourceRef = `plugin_workload_sdk:${grant.recipeNamespace}/${grant.recipeName}:${grant.capabilityFamily}`
    const changes: ControlApiPermissionChange[] = [
      {
        action: 'revoke',
        resourceClass: 'plugin_workload_sdk_access',
        resourceRef,
        subject: { kind: 'service', id: grant.capabilityFamily },
        namespace: grant.recipeNamespace,
        sourceAuditRef: `plugin_workload_sdk_grants:${grant.id}`,
      },
      ...grant.allowedUserRefs
        .filter(userRef => resolvedUsers.has(userRef))
        .map(userRef => ({
          action: 'revoke' as const,
          resourceClass: 'plugin_workload_sdk_access',
          resourceRef,
          subject: { kind: 'user' as const, id: userRef },
          namespace: grant.recipeNamespace,
          sourceAuditRef: `plugin_workload_sdk_grants:${grant.id}`,
        })),
    ]
    await appendControlApiPermissionEventsInTransaction(db, { operatorSub, changes })
    // issue #375 (P3): nudge the WRC to re-reconcile after the grant is removed
    // so the capability projection transitions back to awaiting_policy/degraded.
    await notifyGrantUpdate(db, {
      recipeNamespace: grant.recipeNamespace,
      recipeName: grant.recipeName,
      capabilityFamily: grant.capabilityFamily,
    })
    return true
  })
}

// ─── Recipients (clientNotifications picker) ─────────────────────────────

/**
 * A grant's allowed userRef resolved for the picker: `userRef` is the opaque
 * UUID the notify call targets (the option value), `displayName` is the user's
 * email — the human handle shown in the dropdown.
 */
export interface PluginWorkloadSdkRecipientProfile {
  userRef: string
  displayName: string
}

/** Control-plane user UUID shape — allowedUserRefs are enforced to this at grant creation. */
const RECIPIENT_UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i

/**
 * Resolve a grant's allowedUserRefs (control-plane user UUIDs) to a display
 * handle for the clientNotifications recipient picker. EvenFire users are
 * identified by email — it is the unique, human-readable handle — so the picker
 * shows the email. The opaque UUID is the option VALUE the notify call needs but
 * is never surfaced as a label: a ref that no longer resolves to a user (e.g. a
 * deleted account) is dropped, not shown as a bare UUID. Input order is
 * preserved; non-UUID refs are skipped defensively (grant creation enforces UUIDs).
 */
export async function resolveRecipientProfiles(
  userRefs: string[]
): Promise<PluginWorkloadSdkRecipientProfile[]> {
  const refs = userRefs.filter(ref => RECIPIENT_UUID_RE.test(ref))
  if (refs.length === 0) return []
  const result = await pool.query(
    `SELECT u.id, u.email FROM users u WHERE u.id = ANY($1::uuid[])`,
    [refs]
  )
  const emailById = new Map<string, string>()
  for (const row of result.rows as Array<{ id: string; email: string | null }>) {
    if (row.email) emailById.set(String(row.id), String(row.email))
  }
  // Preserve input order; drop refs that no longer resolve (never show a UUID).
  return refs
    .filter(ref => emailById.has(ref))
    .map(ref => ({ userRef: ref, displayName: emailById.get(ref) as string }))
}

// ─── Invocations ─────────────────────────────────────────────────────────

/**
 * Immutable identity for one physical provider attempt. The parent invocation
 * is the idempotent logical request; this row is the receipt/fencing record
 * used by tickets, usage ingestion, and late-result rejection.
 */
export interface PluginWorkloadSdkAttemptReceipt {
  invocationId: string
  recipeNamespace: string
  recipeName: string
  attemptGeneration: number
  method: PluginWorkloadSdkFamily
  targetRefs: string[]
  policyRevision: number | null
  policyHash: string | null
  status: PluginWorkloadSdkInvocationStatus
  startedAt: string
  leaseExpiresAt: string | null
  completedAt: string | null
}

function mapAttemptReceiptRow(row: Record<string, unknown>): PluginWorkloadSdkAttemptReceipt {
  return {
    invocationId: String(row.invocation_id),
    recipeNamespace: String(row.recipe_namespace),
    recipeName: String(row.recipe_name),
    attemptGeneration: Number(row.attempt_generation),
    method: row.method as PluginWorkloadSdkFamily,
    targetRefs: stringArray(row.target_refs),
    policyRevision: row.policy_revision == null ? null : Number(row.policy_revision),
    policyHash: row.policy_hash == null ? null : String(row.policy_hash),
    status: row.status as PluginWorkloadSdkInvocationStatus,
    startedAt: new Date(row.started_at as string).toISOString(),
    leaseExpiresAt:
      row.lease_expires_at == null ? null : new Date(row.lease_expires_at as string).toISOString(),
    completedAt:
      row.completed_at == null ? null : new Date(row.completed_at as string).toISOString(),
  }
}

export async function getPluginWorkloadSdkAttemptReceipt(
  invocationId: string,
  attemptGeneration: number,
  db: DbClient = pool
): Promise<PluginWorkloadSdkAttemptReceipt | null> {
  const result = await db.query(
    `SELECT * FROM plugin_workload_sdk_invocation_attempts
      WHERE invocation_id = $1 AND attempt_generation = $2`,
    [invocationId, attemptGeneration]
  )
  const row = result.rows[0] as Record<string, unknown> | undefined
  return row ? mapAttemptReceiptRow(row) : null
}

export type PluginWorkloadSdkProviderAttemptStatus =
  | 'reserved'
  | 'in_progress'
  | 'complete'
  | 'failed'
  | 'provider_unavailable'
  | 'skipped'

export interface PluginWorkloadSdkProviderAttempt {
  id: string
  invocationId: string
  recipeNamespace: string
  recipeName: string
  attemptGeneration: number
  attemptIndex: number
  targetRef: string
  provider: string
  model: string
  credentialSlot: string
  status: PluginWorkloadSdkProviderAttemptStatus
  credentialJti: string | null
  startedAt: string
  leaseExpiresAt: string | null
  completedAt: string | null
  usageRequestId: string | null
}

function mapProviderAttemptRow(row: Record<string, unknown>): PluginWorkloadSdkProviderAttempt {
  return {
    id: String(row.id),
    invocationId: String(row.invocation_id),
    recipeNamespace: String(row.recipe_namespace),
    recipeName: String(row.recipe_name),
    attemptGeneration: Number(row.attempt_generation),
    attemptIndex: Number(row.attempt_index),
    targetRef: String(row.target_ref),
    provider: String(row.provider),
    model: String(row.model),
    credentialSlot: String(row.credential_slot),
    status: row.status as PluginWorkloadSdkProviderAttemptStatus,
    credentialJti: row.credential_jti == null ? null : String(row.credential_jti),
    startedAt: new Date(row.started_at as string).toISOString(),
    leaseExpiresAt:
      row.lease_expires_at == null ? null : new Date(row.lease_expires_at as string).toISOString(),
    completedAt:
      row.completed_at == null ? null : new Date(row.completed_at as string).toISOString(),
    usageRequestId: row.usage_request_id == null ? null : String(row.usage_request_id),
  }
}

/** Reserve one physical target before issuing a JIT credential ticket. */
export async function reservePluginWorkloadSdkProviderAttempt(input: {
  invocationId: string
  recipeNamespace: string
  recipeName: string
  attemptGeneration: number
  target: PluginWorkloadSdkPromptTarget
}): Promise<PluginWorkloadSdkProviderAttempt | null> {
  return withTransaction(async db => {
    // Revoke and every capability-creation path use this same recipe lock.
    // Acquire it before the invocation row lock to keep one global order and
    // avoid a revoke↔attempt deadlock.
    const recipeLock = `plugin_workload_sdk:${input.recipeNamespace}/${input.recipeName}`
    await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [recipeLock])
    const invocationResult = await db.query(
      `SELECT id, method, status, attempt_generation, lease_expires_at, prompt_authorization
         FROM plugin_workload_sdk_invocations
        WHERE id = $1 AND recipe_namespace = $2 AND recipe_name = $3
        FOR UPDATE`,
      [input.invocationId, input.recipeNamespace, input.recipeName]
    )
    const invocation = invocationResult.rows[0] as Record<string, unknown> | undefined
    const authorization = promptAuthorization(invocation?.prompt_authorization)
    if (
      !invocation ||
      invocation.method !== 'promptBridge' ||
      invocation.status !== 'in_progress' ||
      Number(invocation.attempt_generation) !== input.attemptGeneration ||
      !invocation.lease_expires_at ||
      new Date(invocation.lease_expires_at as string).getTime() <= Date.now() ||
      !authorization?.authorizedTargetRefs.includes(input.target.targetRef)
    ) {
      return null
    }
    const grantResult = await db.query(
      `SELECT policy_state, policy_revision, prompt_targets, default_target_ref
         FROM plugin_workload_sdk_grants
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND capability_family = 'promptBridge'
        FOR UPDATE`,
      [input.recipeNamespace, input.recipeName]
    )
    const grant = grantResult.rows[0] as Record<string, unknown> | undefined
    const currentTargets = promptTargets(grant?.prompt_targets)
    const currentRevision = Number(grant?.policy_revision)
    const currentHash = hashPromptTargetPolicy({
      policyRevision: currentRevision,
      defaultTargetRef: grant?.default_target_ref == null ? null : String(grant.default_target_ref),
      promptTargets: currentTargets,
    })
    const currentTarget = currentTargets.find(target => target.targetRef === input.target.targetRef)
    if (
      !grant ||
      grant.policy_state !== 'active' ||
      currentRevision !== authorization?.policyRevision ||
      currentHash !== authorization?.policyHash ||
      !currentTarget ||
      currentTarget.provider !== input.target.provider ||
      currentTarget.model !== input.target.model ||
      currentTarget.credentialSlot !== input.target.credentialSlot ||
      (currentTarget.connectionRef ?? '') !== (input.target.connectionRef ?? '')
    ) {
      return null
    }
    const attemptsResult = await db.query(
      `SELECT attempt_index, target_ref, status
         FROM plugin_workload_sdk_provider_attempts
        WHERE invocation_id = $1 AND attempt_generation = $2
        ORDER BY attempt_index ASC
        FOR UPDATE`,
      [input.invocationId, input.attemptGeneration]
    )
    const attempts = attemptsResult.rows as Array<{
      attempt_index?: unknown
      target_ref?: unknown
      status?: unknown
    }>
    const attemptIndex = attempts.length + 1
    const expectedTargetRef = authorization.authorizedTargetRefs[attemptIndex - 1]
    if (
      attemptIndex > MAX_PROMPT_BRIDGE_PROVIDER_ATTEMPTS ||
      !expectedTargetRef ||
      expectedTargetRef !== input.target.targetRef
    ) {
      return null
    }
    if (attemptIndex > 1) {
      const previousAttempt = attempts[attempts.length - 1]
      if (
        !previousAttempt ||
        !['failed', 'provider_unavailable', 'skipped'].includes(String(previousAttempt.status))
      ) {
        return null
      }
    }
    const result = await db.query(
      `INSERT INTO plugin_workload_sdk_provider_attempts
         (invocation_id, recipe_namespace, recipe_name, attempt_generation, attempt_index,
          target_ref, provider, model, credential_slot, status, lease_expires_at)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, 'reserved', $10)
       RETURNING *`,
      [
        input.invocationId,
        input.recipeNamespace,
        input.recipeName,
        input.attemptGeneration,
        attemptIndex,
        input.target.targetRef,
        input.target.provider,
        input.target.model,
        input.target.credentialSlot,
        invocation.lease_expires_at,
      ]
    )
    const row = result.rows[0] as Record<string, unknown> | undefined
    return row ? mapProviderAttemptRow(row) : null
  })
}

export async function getPluginWorkloadSdkProviderAttempt(
  id: string,
  db: DbClient = pool
): Promise<PluginWorkloadSdkProviderAttempt | null> {
  const result = await db.query(
    `SELECT * FROM plugin_workload_sdk_provider_attempts WHERE id = $1`,
    [id]
  )
  const row = result.rows[0] as Record<string, unknown> | undefined
  return row ? mapProviderAttemptRow(row) : null
}

export async function lockPluginWorkloadSdkRecipe(
  db: DbClient,
  recipeNamespace: string,
  recipeName: string
): Promise<void> {
  const recipeLock = `plugin_workload_sdk:${recipeNamespace}/${recipeName}`
  await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [recipeLock])
}

export async function getPluginWorkloadSdkProviderAttemptForUpdate(
  id: string,
  db: DbClient
): Promise<PluginWorkloadSdkProviderAttempt | null> {
  const result = await db.query(
    `SELECT * FROM plugin_workload_sdk_provider_attempts WHERE id = $1 FOR UPDATE`,
    [id]
  )
  const row = result.rows[0] as Record<string, unknown> | undefined
  return row ? mapProviderAttemptRow(row) : null
}

export async function pluginWorkloadSdkSpendOutcomeExists(
  providerAttemptId: string,
  db: DbClient
): Promise<boolean> {
  const result = await db.query(
    `SELECT 1 FROM plugin_workload_sdk_spend_outcomes WHERE provider_attempt_id = $1`,
    [providerAttemptId]
  )
  return result.rows.length > 0
}

/**
 * JTI-free reserved → in_progress for oauth-broker authorize-link. Does not
 * write credential_jti. The secret-ticket path still promotes through
 * registerPluginWorkloadSdkCredentialTicketJti. Provider is a bind parameter
 * so Codex and Grok cannot promote each other's reserved rows.
 */
export async function promoteReservedOauthBrokerProviderAttempt(
  input: {
    id: string
    invocationId: string
    recipeNamespace: string
    recipeName: string
    attemptGeneration: number
    attemptIndex: number
    model: string
    targetRef: string
    provider: 'codex-subscription' | 'grok-subscription'
  },
  db: DbClient
): Promise<boolean> {
  const result = await db.query(
    `UPDATE plugin_workload_sdk_provider_attempts
        SET status = 'in_progress'
      WHERE id = $1
        AND invocation_id = $2
        AND recipe_namespace = $3
        AND recipe_name = $4
        AND attempt_generation = $5
        AND attempt_index = $6
        AND provider = $9
        AND model = $7
        AND target_ref = $8
        AND status = 'reserved'
        AND credential_jti IS NULL`,
    [
      input.id,
      input.invocationId,
      input.recipeNamespace,
      input.recipeName,
      input.attemptGeneration,
      input.attemptIndex,
      input.model,
      input.targetRef,
      input.provider,
    ]
  )
  return (result.rowCount ?? 0) === 1
}

export async function markPluginWorkloadSdkProviderAttemptStatus(input: {
  id: string
  invocationId: string
  recipeNamespace: string
  recipeName: string
  attemptGeneration: number
  status: Exclude<PluginWorkloadSdkProviderAttemptStatus, 'reserved' | 'in_progress'>
}): Promise<boolean> {
  // Terminal reports are idempotent: a committed response whose HTTP reply
  // was lost must be safe to repeat. A different terminal outcome still
  // returns false, so a late failure can never downgrade a completed attempt.
  const result = await pool.query(
    `UPDATE plugin_workload_sdk_provider_attempts
        SET status = $6,
            completed_at = now(),
            lease_expires_at = NULL
      WHERE id = $1
        AND invocation_id = $2
        AND recipe_namespace = $3
        AND recipe_name = $4
        AND attempt_generation = $5
        AND (
          status = $6
          OR (
            status IN ('reserved', 'in_progress')
            AND EXISTS (
              SELECT 1
                FROM plugin_workload_sdk_invocations inv
               WHERE inv.id = $2
                 AND inv.status = 'in_progress'
                 AND inv.attempt_generation = $5
                 AND inv.lease_expires_at > now()
            )
          )
        )`,
    [
      input.id,
      input.invocationId,
      input.recipeNamespace,
      input.recipeName,
      input.attemptGeneration,
      input.status,
    ]
  )
  return (result.rowCount ?? 0) === 1
}

async function insertAttemptReceipt(
  db: DbClient,
  invocation: PluginWorkloadSdkInvocationRecord
): Promise<void> {
  const authorization = invocation.promptAuthorization
  await db.query(
    `INSERT INTO plugin_workload_sdk_invocation_attempts
       (invocation_id, recipe_namespace, recipe_name, attempt_generation, method, target_refs, policy_revision,
        policy_hash, status, lease_expires_at)
     VALUES ($1, $2, $3, $4, $5, $6::jsonb, $7, $8, $9, $10)
     ON CONFLICT (invocation_id, attempt_generation) DO NOTHING`,
    [
      invocation.id,
      invocation.recipeNamespace,
      invocation.recipeName,
      invocation.attemptGeneration,
      invocation.method,
      JSON.stringify(authorization?.authorizedTargetRefs ?? []),
      authorization?.policyRevision ?? null,
      authorization?.policyHash ?? null,
      invocation.status,
      invocation.leaseExpiresAt,
    ]
  )
}

export interface InsertInvocationParams {
  recipeNamespace: string
  recipeName: string
  callerRef: string
  correlationId?: string
  method: PluginWorkloadSdkFamily
  detail: string
  purpose?: string
  idempotencyKeyHash: string
  payloadHash: string
  status: PluginWorkloadSdkInvocationStatus
  authorizationDecision: string
  promptAuthorization?: PluginWorkloadSdkPromptAuthorization
  contractVersion?: 1 | 2
  /** Lease for the first physical attempt; terminal transitions clear it. */
  attemptLeaseSeconds?: number
  /**
   * States that may create/replay an invocation. The check runs under the
   * recipe-wide advisory lock so revoke and authorization have one ordering.
   */
  requiredGrantStates?: readonly ('active' | 'legacy_unreviewed')[]
}

export type InsertInvocationResult =
  | { kind: 'inserted'; invocation: PluginWorkloadSdkInvocationRecord }
  | { kind: 'replay'; invocation: PluginWorkloadSdkInvocationRecord }
  | { kind: 'conflict'; invocation: PluginWorkloadSdkInvocationRecord }
  // Lost the ON CONFLICT race AND the winning row vanished (pruned between the
  // INSERT and the fallback SELECT). A structured signal — NOT an opaque throw
  // — so the caller maps it to a deterministic idempotency_conflict (409-style)
  // deny instead of a 500. The caller can safely retry with a fresh key.
  | { kind: 'race_unresolved' }
  | { kind: 'policy_revoked' }

/**
 * Insert an invocation with idempotency semantics: same key + same payload
 * hash → replay (existing record returned); same key + different payload
 * hash → conflict (spec §15 idempotency_conflict).
 */
export async function insertInvocation(
  params: InsertInvocationParams
): Promise<InsertInvocationResult> {
  return withTransaction(async db => {
    const recipeLock = `plugin_workload_sdk:${params.recipeNamespace}/${params.recipeName}`
    await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [recipeLock])
    if (params.requiredGrantStates) {
      const grant = await db.query(
        `SELECT policy_state
           FROM plugin_workload_sdk_grants
          WHERE recipe_namespace = $1
            AND recipe_name = $2
            AND capability_family = $3
          FOR UPDATE`,
        [params.recipeNamespace, params.recipeName, params.method]
      )
      const state = String(
        (grant.rows[0] as { policy_state?: unknown } | undefined)?.policy_state ?? ''
      )
      if (!params.requiredGrantStates.includes(state as 'active' | 'legacy_unreviewed')) {
        return { kind: 'policy_revoked' }
      }
    }
    const inserted = await db.query(
      `INSERT INTO plugin_workload_sdk_invocations
       (recipe_namespace, recipe_name, caller_ref, correlation_id, method, detail, purpose,
        idempotency_key_hash, payload_hash, status, authorization_decision, prompt_authorization,
        contract_version, attempt_generation, lease_expires_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, 1,
        now() + interval '1 second' * $14)
     ON CONFLICT (recipe_namespace, recipe_name, method, idempotency_key_hash) DO NOTHING
     RETURNING *`,
      [
        params.recipeNamespace,
        params.recipeName,
        params.callerRef,
        params.correlationId ?? null,
        params.method,
        params.detail,
        params.purpose ?? null,
        params.idempotencyKeyHash,
        params.payloadHash,
        params.status,
        params.authorizationDecision,
        params.promptAuthorization ? JSON.stringify(params.promptAuthorization) : null,
        params.contractVersion ?? 2,
        params.attemptLeaseSeconds ?? getPromptBridgeAttemptLeaseSeconds(),
      ]
    )
    const insertedRow = inserted.rows[0] as Record<string, unknown> | undefined
    if (insertedRow) {
      const invocation = mapInvocationRow(insertedRow)
      await insertAttemptReceipt(db, invocation)
      return { kind: 'inserted', invocation }
    }

    const existing = await db.query(
      `SELECT * FROM plugin_workload_sdk_invocations
     WHERE recipe_namespace = $1 AND recipe_name = $2 AND method = $3 AND idempotency_key_hash = $4`,
      [params.recipeNamespace, params.recipeName, params.method, params.idempotencyKeyHash]
    )
    const existingRow = existing.rows[0] as Record<string, unknown> | undefined
    if (!existingRow) {
      // Lost the race AND the winner vanished (pruned between statements) —
      // surface a structured signal so the caller returns a deterministic
      // idempotency_conflict (NOT a 500) and the workload retries with a fresh key.
      return { kind: 'race_unresolved' }
    }
    const invocation = mapInvocationRow(existingRow)
    return invocation.payloadHash === params.payloadHash
      ? { kind: 'replay', invocation }
      : { kind: 'conflict', invocation }
  })
}

export async function getInvocationById(
  id: string,
  db: DbClient = pool
): Promise<PluginWorkloadSdkInvocationRecord | null> {
  const result = await db.query(`SELECT * FROM plugin_workload_sdk_invocations WHERE id = $1`, [id])
  const row = result.rows[0] as Record<string, unknown> | undefined
  return row ? mapInvocationRow(row) : null
}

export async function registerPluginWorkloadSdkCredentialTicketJti(input: {
  jti: string
  recipeNamespace: string
  recipeName: string
  invocationId: string
  targetRef: string
  attemptGeneration: number
  providerAttemptId: string
  expiresAt: Date
}): Promise<boolean> {
  return withTransaction(async db => {
    const recipeLock = `plugin_workload_sdk:${input.recipeNamespace}/${input.recipeName}`
    await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [recipeLock])
    const grant = await db.query(
      `SELECT policy_state
         FROM plugin_workload_sdk_grants
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND capability_family = 'promptBridge'
        FOR UPDATE`,
      [input.recipeNamespace, input.recipeName]
    )
    if (
      String((grant.rows[0] as { policy_state?: unknown } | undefined)?.policy_state ?? '') !==
      'active'
    ) {
      return false
    }
    const invocation = await db.query(
      `SELECT id, status, attempt_generation, lease_expires_at
         FROM plugin_workload_sdk_invocations
        WHERE id = $1
          AND recipe_namespace = $2
          AND recipe_name = $3
        FOR UPDATE`,
      [input.invocationId, input.recipeNamespace, input.recipeName]
    )
    const invocationRow = invocation.rows[0] as Record<string, unknown> | undefined
    if (
      !invocationRow ||
      invocationRow.status !== 'in_progress' ||
      Number(invocationRow.attempt_generation) !== input.attemptGeneration ||
      !invocationRow.lease_expires_at ||
      new Date(invocationRow.lease_expires_at as string).getTime() <= Date.now()
    ) {
      return false
    }
    const providerAttempt = await db.query(
      `SELECT id
         FROM plugin_workload_sdk_provider_attempts
        WHERE id = $1
          AND invocation_id = $2
          AND recipe_namespace = $3
          AND recipe_name = $4
          AND attempt_generation = $5
          AND target_ref = $6
          AND status = 'reserved'
        FOR UPDATE`,
      [
        input.providerAttemptId,
        input.invocationId,
        input.recipeNamespace,
        input.recipeName,
        input.attemptGeneration,
        input.targetRef,
      ]
    )
    if (providerAttempt.rows.length !== 1) return false
    // Opportunistic cleanup bounds the one-shot registry without retaining any
    // credentials or ticket bodies.
    await db.query(
      `DELETE FROM plugin_workload_sdk_credential_ticket_jtis WHERE expires_at <= now()`
    )
    const result = await db.query(
      `INSERT INTO plugin_workload_sdk_credential_ticket_jtis
         (jti, recipe_namespace, recipe_name, invocation_id, target_ref, attempt_generation,
          provider_attempt_id, expires_at)
       SELECT $1, $2, $3, $4, $5, $6, $7, $8
        WHERE EXISTS (
          SELECT 1 FROM plugin_workload_sdk_provider_attempts
           WHERE id = $7
             AND invocation_id = $4
             AND recipe_namespace = $2
             AND recipe_name = $3
             AND attempt_generation = $6
             AND target_ref = $5
             AND status = 'reserved'
        )
       ON CONFLICT (jti) DO NOTHING`,
      [
        input.jti,
        input.recipeNamespace,
        input.recipeName,
        input.invocationId,
        input.targetRef,
        input.attemptGeneration,
        input.providerAttemptId,
        input.expiresAt,
      ]
    )
    if ((result.rowCount ?? 0) !== 1) return false
    const promoted = await db.query(
      `UPDATE plugin_workload_sdk_provider_attempts
          SET credential_jti = $2, status = 'in_progress'
        WHERE id = $1 AND status = 'reserved'`,
      [input.providerAttemptId, input.jti]
    )
    if ((promoted.rowCount ?? 0) === 1) return true
    // The JTI insert and the reservation promotion are one state transition.
    // Remove the orphaned registry row before committing a false result so a
    // race cannot leave a redeemable ticket with no physical attempt owner.
    await db.query(`DELETE FROM plugin_workload_sdk_credential_ticket_jtis WHERE jti = $1`, [
      input.jti,
    ])
    return false
  })
}

/** Atomically consumes a ticket identity after all binding/policy checks pass. */
export async function redeemPluginWorkloadSdkCredentialTicketJti(input: {
  jti: string
  recipeNamespace: string
  recipeName: string
  invocationId: string
  targetRef: string
  attemptGeneration: number
  providerAttemptId: string
}): Promise<boolean> {
  return withTransaction(async db => {
    const recipeLock = `plugin_workload_sdk:${input.recipeNamespace}/${input.recipeName}`
    await db.query(`SELECT pg_advisory_xact_lock(hashtext($1)::bigint)`, [recipeLock])
    const grant = await db.query(
      `SELECT policy_state
         FROM plugin_workload_sdk_grants
        WHERE recipe_namespace = $1
          AND recipe_name = $2
          AND capability_family = 'promptBridge'
        FOR UPDATE`,
      [input.recipeNamespace, input.recipeName]
    )
    if (
      String((grant.rows[0] as { policy_state?: unknown } | undefined)?.policy_state ?? '') !==
      'active'
    ) {
      return false
    }
    const result = await db.query(
      `UPDATE plugin_workload_sdk_credential_ticket_jtis AS ticket
          SET redeemed_at = now()
        WHERE ticket.jti = $1
          AND ticket.recipe_namespace = $2
          AND ticket.recipe_name = $3
          AND ticket.invocation_id = $4
          AND ticket.target_ref = $5
          AND ticket.attempt_generation = $6
          AND ticket.provider_attempt_id = $7
          AND ticket.redeemed_at IS NULL
          AND ticket.expires_at > now()
          AND EXISTS (
            SELECT 1
              FROM plugin_workload_sdk_invocations invocation
             WHERE invocation.id = ticket.invocation_id
               AND invocation.recipe_namespace = ticket.recipe_namespace
               AND invocation.recipe_name = ticket.recipe_name
               AND invocation.method = 'promptBridge'
               AND invocation.status = 'in_progress'
               AND invocation.attempt_generation = ticket.attempt_generation
               AND invocation.lease_expires_at > now()
          )
          AND EXISTS (
            SELECT 1
              FROM plugin_workload_sdk_invocation_attempts receipt
             WHERE receipt.invocation_id = ticket.invocation_id
               AND receipt.recipe_namespace = ticket.recipe_namespace
               AND receipt.recipe_name = ticket.recipe_name
               AND receipt.attempt_generation = ticket.attempt_generation
               AND receipt.method = 'promptBridge'
               AND receipt.status = 'in_progress'
               AND receipt.lease_expires_at > now()
          )
          AND EXISTS (
            SELECT 1
              FROM plugin_workload_sdk_provider_attempts attempt
             WHERE attempt.id = ticket.provider_attempt_id
               AND attempt.invocation_id = ticket.invocation_id
               AND attempt.recipe_namespace = ticket.recipe_namespace
               AND attempt.recipe_name = ticket.recipe_name
               AND attempt.attempt_generation = ticket.attempt_generation
               AND attempt.target_ref = ticket.target_ref
               AND attempt.status = 'in_progress'
          )`,
      [
        input.jti,
        input.recipeNamespace,
        input.recipeName,
        input.invocationId,
        input.targetRef,
        input.attemptGeneration,
        input.providerAttemptId,
      ]
    )
    return (result.rowCount ?? 0) === 1
  })
}

export async function updateInvocationStatus(
  id: string,
  status: PluginWorkloadSdkInvocationStatus,
  opts: {
    completed?: boolean
    recipeNamespace?: string
    recipeName?: string
    expectedAttemptGeneration?: number
    leaseSeconds?: number
    /**
     * Optimistic-concurrency guard: when set, the UPDATE only applies if the
     * row is currently in this status. Used to transition accepted → delivered
     * exactly once without clobbering a terminal 'failed'.
     */
    expectedCurrentStatus?: PluginWorkloadSdkInvocationStatus
  } = {}
): Promise<boolean> {
  // Every in-progress invocation must carry a lease. Without this guard an
  // older rolling-update writer can create a v2 row that the stale sweep can
  // never identify, leaving the idempotency key permanently wedged.
  // The terminal branch below also makes identical completion reports
  // idempotent while preserving the expected-status CAS for new transitions.
  const leaseSeconds = opts.leaseSeconds
  if (
    status === 'in_progress' &&
    (typeof leaseSeconds !== 'number' || !Number.isInteger(leaseSeconds) || leaseSeconds <= 0)
  ) {
    return false
  }
  const values: unknown[] = [id, status, opts.completed ?? false, leaseSeconds ?? 0]
  let bindingClause = ''
  if (opts.recipeNamespace !== undefined && opts.recipeName !== undefined) {
    values.push(opts.recipeNamespace, opts.recipeName)
    bindingClause = ` AND recipe_namespace = $${values.length - 1} AND recipe_name = $${values.length}`
  }
  let expectedStatusPlaceholder = ''
  if (opts.expectedCurrentStatus !== undefined) {
    values.push(opts.expectedCurrentStatus)
    expectedStatusPlaceholder = `$${values.length}`
  }
  let generationGuardClause = ''
  if (opts.expectedAttemptGeneration !== undefined) {
    values.push(opts.expectedAttemptGeneration)
    generationGuardClause = ` AND attempt_generation = $${values.length}`
  }
  return withTransaction(async db => {
    const result = await db.query(
      `UPDATE plugin_workload_sdk_invocations
       SET status = $2,
           updated_at = now(),
           lease_expires_at = CASE
             WHEN $2 = 'in_progress' AND $4 > 0
               THEN now() + interval '1 second' * $4
             ELSE NULL
           END,
           completed_at = CASE WHEN $3 THEN now() ELSE NULL END
       WHERE id = $1${bindingClause}
         AND (
           ${expectedStatusPlaceholder ? `status = ${expectedStatusPlaceholder}` : 'TRUE'}
           OR (status = $2 AND $2 NOT IN ('in_progress', 'accepted'))
         )${generationGuardClause}`,
      values
    )
    if ((result.rowCount ?? 0) === 0) return false
    const generation =
      opts.expectedAttemptGeneration ??
      Number(
        (
          (
            await db.query(
              `SELECT attempt_generation FROM plugin_workload_sdk_invocations WHERE id = $1`,
              [id]
            )
          ).rows[0] as { attempt_generation?: unknown } | undefined
        )?.attempt_generation ?? 1
      )
    await db.query(
      `UPDATE plugin_workload_sdk_invocation_attempts
          SET status = $3,
              lease_expires_at = CASE
                WHEN $3 = 'in_progress' AND $4 > 0
                  THEN now() + interval '1 second' * $4
                ELSE NULL
              END,
              completed_at = CASE WHEN $2 THEN now() ELSE NULL END
        WHERE invocation_id = $1 AND attempt_generation = $5`,
      [id, opts.completed ?? false, status, opts.leaseSeconds ?? 0, generation]
    )
    return true
  })
}

/**
 * Atomically claims the next physical attempt for a failed invocation. The
 * generation is the fencing token: every ticket, usage receipt, and terminal
 * status update must carry it, so a late result from an earlier attempt cannot
 * close a newer retry (ABA protection).
 */
export async function reviveFailedInvocation(input: {
  id: string
  recipeNamespace: string
  recipeName: string
  leaseSeconds: number
}): Promise<number | null> {
  return withTransaction(async db => {
    const result = await db.query(
      `UPDATE plugin_workload_sdk_invocations
          SET status = 'in_progress',
              attempt_generation = attempt_generation + 1,
              updated_at = now(),
              lease_expires_at = now() + interval '1 second' * $4,
              completed_at = NULL
        WHERE id = $1
          AND recipe_namespace = $2
          AND recipe_name = $3
          AND status = 'failed'
        RETURNING *`,
      [input.id, input.recipeNamespace, input.recipeName, input.leaseSeconds]
    )
    const row = result.rows[0] as Record<string, unknown> | undefined
    if (!row) return null
    const invocation = mapInvocationRow(row)
    await insertAttemptReceipt(db, invocation)
    return invocation.attemptGeneration
  })
}

/**
 * Close stale in_progress invocations conservatively as provider_unavailable
 * (mcp-host restart recovery — plan §2.4). A provider call may have executed
 * before the host disappeared, so marking the row failed would allow the
 * idempotency authorizer to revive it and create a second billable call. The
 * terminal provider_unavailable state is intentionally not auto-revivable;
 * callers must choose a fresh idempotency key after an operator investigates.
 * Returns the number of rows transitioned.
 */
export async function failStaleInvocations(olderThanSeconds: number): Promise<number> {
  return withTransaction(db => failStaleInvocationsInTransaction(olderThanSeconds, db))
}

/**
 * Transaction-scoped stale recovery primitive. Keeping the transaction
 * injectable makes the row-lock/ledger race testable against a real database
 * and lets callers that already own a control-plane transaction preserve the
 * same atomic fencing boundary as the scheduled sweeper.
 */
export async function failStaleInvocationsInTransaction(
  olderThanSeconds: number,
  db: DbClient
): Promise<number> {
  const timeoutSeconds = Math.max(1, Math.floor(olderThanSeconds))
  const stale = await db.query(
    `SELECT id, attempt_generation, recipe_namespace, recipe_name
       FROM plugin_workload_sdk_invocations
      WHERE status = 'in_progress'
        AND (
          (lease_expires_at IS NOT NULL AND lease_expires_at < now())
          OR (
            lease_expires_at IS NULL
            AND updated_at < now() - interval '1 second' * $1
          )
        )
      FOR UPDATE`,
    [timeoutSeconds]
  )
  let closed = 0
  for (const row of stale.rows as Array<{
    id: string
    attempt_generation: number
    recipe_namespace: string
    recipe_name: string
  }>) {
    // Invocation FOR UPDATE is the fence. Do not take the recipe advisory lock
    // here: finalize takes advisory first, then the invocation row. Row-then-
    // advisory deadlocks the sweeper/finalizer interleaving.
    const physicalAttempts = await db.query(
      `SELECT id, recipe_namespace, recipe_name, attempt_generation,
                attempt_index, target_ref, provider, model, credential_slot
           FROM plugin_workload_sdk_provider_attempts
          WHERE invocation_id = $1
            AND attempt_generation = $2
            AND status IN ('reserved', 'in_progress', 'complete', 'failed')
          FOR UPDATE`,
      [row.id, row.attempt_generation]
    )
    let linkedCodexInFlight = false
    const linkedByAttempt = new Map<string, LlmProviderAttemptRow>()
    for (const attempt of physicalAttempts.rows as Array<Record<string, unknown>>) {
      const provider = String(attempt.provider)
      if (!isOauthBrokerProvider(provider)) continue
      const linked = await loadLlmProviderAttemptBySdkAttemptId(db, String(attempt.id))
      if (!linked) continue
      linkedByAttempt.set(String(attempt.id), linked)
      if (isLinkedCodexInFlightWithoutUsage(linked)) {
        linkedCodexInFlight = true
        break
      }
    }
    // Codex still owns exact usage within the in-flight grace. Leave the
    // invocation leased so finalize can 409 ledger_pending. After the grace,
    // or once Codex finalizes without tokens, unknown spend is the honest close.
    if (linkedCodexInFlight) continue

    const marked = await db.query(
      `UPDATE plugin_workload_sdk_invocations
          SET status = 'provider_unavailable', updated_at = now(), completed_at = now()
        WHERE id = $1
          AND attempt_generation = $2
          AND status = 'in_progress'
        RETURNING id`,
      [row.id, row.attempt_generation]
    )
    if ((marked.rowCount ?? 0) !== 1) continue

    for (const attempt of physicalAttempts.rows as Array<Record<string, unknown>>) {
      // The host has disappeared, so no current JWT can prove a host_ref.
      // Persist an unattributed row instead of inventing a host identity from
      // recipe names; the physical-attempt PK makes this idempotent.
      //
      // Persist the best floor provable without a JWT: exact when the linked
      // Codex row already carries usage, unknown otherwise. Finalize derives
      // exact at read time for the in-flight residue that lands later.
      const exact = linkedCodexExactUsage(linkedByAttempt.get(String(attempt.id)) ?? null)
      await insertSpendOutcomeFloor(
        db,
        {
          providerAttemptId: String(attempt.id),
          invocationId: row.id,
          recipeNamespace: String(attempt.recipe_namespace),
          recipeName: String(attempt.recipe_name),
          attemptGeneration: Number(attempt.attempt_generation),
          attemptIndex: Number(attempt.attempt_index),
          targetRef: String(attempt.target_ref),
          hostRef: null,
          provider: String(attempt.provider),
          model: String(attempt.model),
          credentialSlot: String(attempt.credential_slot),
          reason: 'stale_lease',
          // The sweeper never ingests a usage_events row.
          usageRequestId: null,
        },
        exact ? { outcome: 'exact', ...exact } : spendFloor('unknown')
      )
    }
    await db.query(
      `UPDATE plugin_workload_sdk_invocation_attempts
            SET status = 'provider_unavailable', lease_expires_at = NULL, completed_at = now()
          WHERE invocation_id = $1 AND attempt_generation = $2`,
      [row.id, row.attempt_generation]
    )
    await db.query(
      `UPDATE plugin_workload_sdk_provider_attempts
            SET status = 'provider_unavailable', lease_expires_at = NULL, completed_at = now()
          WHERE invocation_id = $1
            AND attempt_generation = $2
            AND status IN ('reserved', 'in_progress')`,
      [row.id, row.attempt_generation]
    )
    closed += 1
  }
  return closed
}

export interface ListInvocationsFilter {
  recipeNamespace?: string
  recipeName?: string
  method?: PluginWorkloadSdkFamily
  status?: PluginWorkloadSdkInvocationStatus
  since?: string
  until?: string
  limit?: number
}

export async function listInvocations(
  filter: ListInvocationsFilter
): Promise<PluginWorkloadSdkInvocationRecord[]> {
  const clauses: string[] = []
  const values: unknown[] = []
  const push = (clause: string, value: unknown) => {
    values.push(value)
    clauses.push(clause.replace('?', `$${values.length}`))
  }
  if (filter.recipeNamespace) push('recipe_namespace = ?', filter.recipeNamespace)
  if (filter.recipeName) push('recipe_name = ?', filter.recipeName)
  if (filter.method) push('method = ?', filter.method)
  if (filter.status) push('status = ?', filter.status)
  if (filter.since) push('created_at >= ?', filter.since)
  if (filter.until) push('created_at <= ?', filter.until)
  const where = clauses.length > 0 ? `WHERE ${clauses.join(' AND ')}` : ''
  const limit = Math.min(Math.max(filter.limit ?? 100, 1), 500)
  values.push(limit)
  const result = await pool.query(
    `SELECT * FROM plugin_workload_sdk_invocations ${where}
     ORDER BY created_at DESC LIMIT $${values.length}`,
    values
  )
  return (result.rows as Record<string, unknown>[]).map(mapInvocationRow)
}

/**
 * Prune invocation rows past the idempotency TTL (OQ-5): after 24h the same
 * idempotency key becomes reusable (new invocation period). Only terminal
 * rows are pruned — an in_progress row past TTL is first failed by
 * failStaleInvocations.
 */
export async function prunePluginWorkloadSdkExpiredIdempotencyInTransaction(
  db: DbClient
): Promise<number> {
  // Child receipts and one-shot JTIs have no useful life once the parent
  // idempotency reservation expires. Delete them in one transaction so
  // usage and ticket lookups cannot observe orphaned authority.
  await db.query(
    `DELETE FROM plugin_workload_sdk_credential_ticket_jtis jt
        USING plugin_workload_sdk_invocations inv
       WHERE jt.invocation_id = inv.id
         AND inv.created_at < now() - interval '1 hour' * $1
         AND inv.status <> 'in_progress'`,
    [IDEMPOTENCY_TTL_HOURS]
  )
  await db.query(
    `DELETE FROM plugin_workload_sdk_invocation_attempts attempts
        USING plugin_workload_sdk_invocations inv
       WHERE attempts.invocation_id = inv.id
         AND inv.created_at < now() - interval '1 hour' * $1
         AND inv.status <> 'in_progress'`,
    [IDEMPOTENCY_TTL_HOURS]
  )
  // Unlink the durable Codex ledger before deleting SDK attempt rows.
  // 0108 also SET NULLs the FK on delete; this UPDATE keeps prune from
  // depending on that constraint name and never CASCADE-deletes spend.
  await db.query(
    `UPDATE llm_provider_attempts a
          SET plugin_workload_sdk_provider_attempt_id = NULL
         FROM plugin_workload_sdk_provider_attempts attempts
         JOIN plugin_workload_sdk_invocations inv
           ON attempts.invocation_id = inv.id
        WHERE a.plugin_workload_sdk_provider_attempt_id = attempts.id
          AND inv.created_at < now() - interval '1 hour' * $1
          AND inv.status <> 'in_progress'`,
    [IDEMPOTENCY_TTL_HOURS]
  )
  await db.query(
    `DELETE FROM plugin_workload_sdk_provider_attempts attempts
        USING plugin_workload_sdk_invocations inv
       WHERE attempts.invocation_id = inv.id
         AND inv.created_at < now() - interval '1 hour' * $1
         AND inv.status <> 'in_progress'`,
    [IDEMPOTENCY_TTL_HOURS]
  )
  const result = await db.query(
    `DELETE FROM plugin_workload_sdk_invocations
       WHERE created_at < now() - interval '1 hour' * $1
         AND status <> 'in_progress'`,
    [IDEMPOTENCY_TTL_HOURS]
  )
  return result.rowCount ?? 0
}

export async function prunePluginWorkloadSdkExpiredIdempotency(): Promise<number> {
  return withTransaction(prunePluginWorkloadSdkExpiredIdempotencyInTransaction)
}

// ─── Quota counters (historical-only, issue #348) ────────────────────────
// Per-run/period quota enforcement was removed (issue #348): nothing writes
// plugin_workload_sdk_quota_counters anymore (the enforcement writers
// consumePeriodQuota / resolveQuotaPeriodStart / getPeriodQuotaUsage and the
// eager epoch sentinel were deleted so a future re-wire cannot resurrect the
// never-resetting epoch bucket). Existing rows — including stale epoch
// sentinels — are inert historical data; getQuotaCounters keeps serving them
// to the admin read path. Hard removal of the table is a separate follow-up.

export interface QuotaCounterRow {
  recipeNamespace: string
  recipeName: string
  periodStart: string
  promptBridgeCount: number
  notificationCount: number
  lastUpdated: string
}

export async function getQuotaCounters(
  recipeNamespace: string,
  recipeName: string
): Promise<QuotaCounterRow[]> {
  const result = await pool.query(
    `SELECT * FROM plugin_workload_sdk_quota_counters
     WHERE recipe_namespace = $1 AND recipe_name = $2
     ORDER BY period_start DESC LIMIT 30`,
    [recipeNamespace, recipeName]
  )
  return (result.rows as Record<string, unknown>[]).map(row => ({
    recipeNamespace: String(row.recipe_namespace),
    recipeName: String(row.recipe_name),
    periodStart: new Date(row.period_start as string).toISOString(),
    promptBridgeCount: Number(row.prompt_bridge_count),
    notificationCount: Number(row.notification_count),
    lastUpdated: new Date(row.last_updated as string).toISOString(),
  }))
}

/**
 * Per-minute rate limiting derived from the invocation audit trail itself
 * (no separate window table): counts invocations recorded in the trailing
 * 60s for the recipe+method, optionally narrowed to one detail value
 * (event type) to prevent notification spam per eventType.
 */
export async function countRecentInvocations(
  recipeNamespace: string,
  recipeName: string,
  method: PluginWorkloadSdkFamily,
  opts: { detail?: string } = {}
): Promise<number> {
  const values: unknown[] = [recipeNamespace, recipeName, method]
  let detailClause = ''
  if (opts.detail !== undefined) {
    values.push(opts.detail)
    detailClause = `AND detail = $${values.length}`
  }
  const result = await pool.query(
    `SELECT count(*)::int AS n FROM plugin_workload_sdk_invocations
     WHERE recipe_namespace = $1 AND recipe_name = $2 AND method = $3 ${detailClause}
       AND created_at > now() - interval '1 minute'`,
    values
  )
  const row = result.rows[0] as { n?: number } | undefined
  return row?.n ?? 0
}