Skip to content

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

TypeScript
44.6 KB
Raw Blame
import type { DbClient } from '../db.js'
import { decryptOAuthSecret, encryptOAuthSecret } from './encryption.js'

/**
 * Owner domain of a grant. `recipe_namespace`/`recipe_name` are reinterpreted
 * as the owner's coordinates:
 * - `recipe` (default) — the historical WorkflowRecipe owner (recipe ns/name).
 * - `mcpserver` — a McpServer owner (server ns/name); OAuth mcp-servers (U1).
 *
 * The column is called `owner_kind` and defaults to `'recipe'` so every row
 * that predates the generalization keeps its meaning. Callers that omit
 * `ownerKind` read/write the `'recipe'` domain — the recipe flows are unchanged.
 */
export type OAuthOwnerKind = 'recipe' | 'mcpserver'

/**
 * Identifies one `oauth_grants` row.
 *
 * - `user` grant — owned by a specific end-user. Keyed by
 *   `(ownerKind, recipeNamespace, recipeName, userId, oauthClientId)` and
 *   governed by the `oauth_grants_unique` constraint. This is per-user identity:
 *   the recipe embed Connect flow, and OAuth mcp-servers with
 *   `grantScope='user'`.
 * - `service` grant — owned by the recipe itself (the admin "connect for the
 *   recipe" flow, Path B). `user_id` is NULL; keyed by
 *   `(recipeNamespace, recipeName, oauthClientId)` and governed by the
 *   `oauth_grants_service_unique` partial index.
 * - `shared` grant — one shared identity per `(server, context)` for an OAuth
 *   mcp-server with `grantScope='context'` (U1 data model; exercised by U6).
 *   `user_id` is NULL; `context_id` is the coordinate that replaces `user_id`
 *   in the key. Keyed by `(ownerKind, recipeNamespace, recipeName, contextId,
 *   oauthClientId)` and governed by the `oauth_grants_shared_unique` partial
 *   index. `bootstrapped_by_user_id` records who first authorized the shared
 *   identity — audit only, OUTSIDE the key and never touched on refresh.
 *
 * `grantKind` is required on every call so each site declares intent — there
 * is no implicit default.
 *
 * `crUid` is the installation identity of an mcp-server owner (the live CR's
 * `metadata.uid`). The row readers ({@link getOAuthGrant}, {@link oauthGrantExists})
 * apply an installation fence to mcp-server keys:
 *   - a key WITHOUT a uid matches nothing;
 *   - a row sealed with another uid is invisible: a same-name reinstall gets a new
 *     uid, so a SEALED grant consented against the previous installation stays
 *     inert even if its teardown never ran;
 *   - an unsealed (legacy, `cr_uid IS NULL`) row is visible only when the key
 *     carries `legacyProvider` and the row's `provider` equals it. Only pods that
 *     predate install identity wrote such rows, and they only spoke the baked
 *     lane, so a remote/generic key, a key for another baked provider, or a key
 *     built by hand without `legacyProvider` never sees one.
 * Ignored for the recipe domain. Locks, deletes and listings do not apply it (see
 * {@link lockOAuthGrantForRefresh}).
 *
 * `legacyProvider` is the baked `spec.oauth.provider` of the CR the key was built
 * from; absent for every other lane. It is a separate name from the `provider`
 * that refresh inputs carry (the value to WRITE) so a spread never conflates them.
 */
export type OAuthGrantKey =
  | {
      grantKind: 'user'
      /** Owner domain; omit for the historical recipe domain (defaults to `'recipe'`). */
      ownerKind?: OAuthOwnerKind
      recipeNamespace: string
      recipeName: string
      userId: string
      oauthClientId: string
      crUid?: string
      legacyProvider?: string
    }
  | {
      grantKind: 'service'
      /** Owner domain; omit for the historical recipe domain (defaults to `'recipe'`). */
      ownerKind?: OAuthOwnerKind
      recipeNamespace: string
      recipeName: string
      oauthClientId: string
      crUid?: string
      legacyProvider?: string
    }
  | {
      grantKind: 'shared'
      /** Shared grants always belong to an mcp-server owner. */
      ownerKind: 'mcpserver'
      recipeNamespace: string
      recipeName: string
      contextId: string
      oauthClientId: string
      crUid?: string
      legacyProvider?: string
    }

/** Resolve the owner domain of a key, defaulting to the recipe domain. */
export function resolveOwnerKind(input: { ownerKind?: OAuthOwnerKind }): OAuthOwnerKind {
  return input.ownerKind ?? 'recipe'
}

/**
 * One row of `oauth_grants`. New grants of the same key replace the old.
 */
export interface OAuthGrantRow {
  grantKind: 'user' | 'service' | 'shared'
  ownerKind: OAuthOwnerKind
  recipeNamespace: string
  recipeName: string
  /** Present only for `user` grants; NULL/absent for `service`/`shared` grants. */
  userId?: string
  /** Present only for `shared` grants; the Context the shared identity belongs to. */
  contextId?: string
  /** Present only for `shared` grants; who first authorized the shared identity (audit). */
  bootstrappedByUserId?: string
  oauthClientId: string
  /** Provider-side identifier (salesforce, slack, ...) — denormalised for audit. */
  provider: string
  /** Plaintext access token; never persisted in the clear. */
  accessToken: string
  /** Plaintext refresh token, if the provider issued one. */
  refreshToken?: string
  /** When the access token expires (provider-supplied; null if not provided). */
  accessTokenExpiresAt?: Date
  /** When the row was last refreshed. */
  updatedAt: Date
  /** True when the user consented to a background workload using this grant. */
  background: boolean
  /** metadata.uid of the McpServer installation the grant was consented against; absent if unsealed. */
  crUid?: string
}

export type UpsertOAuthGrantInput = OAuthGrantKey & {
  provider: string
  accessToken: string
  refreshToken?: string
  /** Seconds until access token expires; null if provider didn't supply expires_in. */
  accessTokenExpiresInSec?: number
  /**
   * metadata.uid of the owning McpServer, sealing the grant to this installation
   * (user path only). Omitted for recipe-domain grants → NULL. The latest consent
   * re-seals the row so a reinstall's grant carries the new uid.
   */
  crUid?: string
}

/**
 * Insert (or replace) an oauth_grants row for the ALTA/CONSENT path only —
 * `user` and `service` grants, encrypting access + refresh tokens at rest with
 * the supplied AES-256-GCM key. This is an `INSERT … ON CONFLICT DO UPDATE`, so
 * it MAY create the row. Token refresh must NOT go through here (it would
 * resurrect a concurrently-deleted grant, R1-B1) — use
 * {@link refreshOAuthGrantTokens}. Shared grants are bootstrapped via
 * {@link bootstrapSharedOAuthGrant} and never inserted here.
 */
export async function upsertOAuthGrant(
  db: DbClient,
  encryptionKey: Buffer,
  input: UpsertOAuthGrantInput
): Promise<void> {
  const accessTokenEncrypted = encryptOAuthSecret(encryptionKey, input.accessToken)
  const refreshTokenEncrypted = input.refreshToken
    ? encryptOAuthSecret(encryptionKey, input.refreshToken)
    : null
  const accessTokenExpiresAt =
    typeof input.accessTokenExpiresInSec === 'number'
      ? new Date(Date.now() + input.accessTokenExpiresInSec * 1000)
      : null

  const ownerKind = resolveOwnerKind(input)

  if (input.grantKind === 'user') {
    await db.query(
      `INSERT INTO oauth_grants (
         owner_kind, recipe_namespace, recipe_name, user_id, oauth_client_id, grant_kind,
         provider, access_token_encrypted, refresh_token_encrypted,
         access_token_expires_at, cr_uid, updated_at
       ) VALUES ($1, $2, $3, $4, $5, 'user', $6, $7, $8, $9, $10, NOW())
       ON CONFLICT (owner_kind, recipe_namespace, recipe_name, user_id, oauth_client_id)
       DO UPDATE SET
         provider = EXCLUDED.provider,
         access_token_encrypted = EXCLUDED.access_token_encrypted,
         refresh_token_encrypted = EXCLUDED.refresh_token_encrypted,
         access_token_expires_at = EXCLUDED.access_token_expires_at,
         cr_uid = EXCLUDED.cr_uid,
         updated_at = NOW()`,
      [
        ownerKind,
        input.recipeNamespace,
        input.recipeName,
        input.userId,
        input.oauthClientId,
        input.provider,
        accessTokenEncrypted,
        refreshTokenEncrypted,
        accessTokenExpiresAt,
        input.crUid ?? null,
      ]
    )
    return
  }

  if (input.grantKind === 'shared') {
    // A shared grant is NEVER inserted through this path: first consent goes
    // through `bootstrapSharedOAuthGrant` (first-wins tokens AND bootstrapper),
    // and token refresh goes through `refreshOAuthGrantTokens` (UPDATE by key).
    // Reaching here means a caller mis-routed a shared grant into the
    // consent/insert path — fail loudly rather than mint one against the
    // service INSERT below.
    throw new Error('upsertOAuthGrant does not handle shared grants')
  }

  await db.query(
    `INSERT INTO oauth_grants (
       owner_kind, recipe_namespace, recipe_name, user_id, oauth_client_id, grant_kind,
       provider, access_token_encrypted, refresh_token_encrypted,
       access_token_expires_at, updated_at
     ) VALUES ($1, $2, $3, NULL, $4, 'service', $5, $6, $7, $8, NOW())
     ON CONFLICT (recipe_namespace, recipe_name, oauth_client_id) WHERE grant_kind = 'service'
     DO UPDATE SET
       provider = EXCLUDED.provider,
       access_token_encrypted = EXCLUDED.access_token_encrypted,
       refresh_token_encrypted = EXCLUDED.refresh_token_encrypted,
       access_token_expires_at = EXCLUDED.access_token_expires_at,
       updated_at = NOW()`,
    [
      ownerKind,
      input.recipeNamespace,
      input.recipeName,
      input.oauthClientId,
      input.provider,
      accessTokenEncrypted,
      refreshTokenEncrypted,
      accessTokenExpiresAt,
    ]
  )
}

export type RefreshOAuthGrantTokensInput = OAuthGrantKey & {
  provider: string
  accessToken: string
  refreshToken?: string
  /** Seconds until access token expires; null if provider didn't supply expires_in. */
  accessTokenExpiresInSec?: number
}

/**
 * Persist refreshed tokens onto an EXISTING grant row — an UPDATE keyed by the
 * grant's own coordinates, for every flavor (`user`/`service`/`shared`) — and
 * report whether a row was actually touched.
 *
 * Distinct from {@link upsertOAuthGrant} on purpose (R1-B1). The consent/alta
 * path inserts, but refresh must NEVER resurrect a row that was concurrently
 * deleted: a disconnect (DELETE) can land between the token read
 * (`getOAuthGrant`) and this write. An `INSERT … ON CONFLICT` would recreate the
 * deleted grant with fresh tokens; an UPDATE by key touches 0 rows once the row
 * is gone, and the caller surfaces that as "needs reauth" rather than silently
 * re-granting.
 *
 * The `shared` branch is the former `upsertOAuthGrant` "Refresh path ONLY"
 * UPDATE, folded here (D4) so the refresh UPDATE lives in exactly one place: a
 * plain UPDATE by key that never rewrites the team identity or the
 * `bootstrapped_by_user_id` audit column (mini-spec 05 §3).
 *
 * Returns `{ updated }` — `false` when no row matched (the grant was deleted in
 * the refresh window).
 */
export async function refreshOAuthGrantTokens(
  db: DbClient,
  encryptionKey: Buffer,
  input: RefreshOAuthGrantTokensInput
): Promise<{ updated: boolean }> {
  const accessTokenEncrypted = encryptOAuthSecret(encryptionKey, input.accessToken)
  const refreshTokenEncrypted = input.refreshToken
    ? encryptOAuthSecret(encryptionKey, input.refreshToken)
    : null
  const accessTokenExpiresAt =
    typeof input.accessTokenExpiresInSec === 'number'
      ? new Date(Date.now() + input.accessTokenExpiresInSec * 1000)
      : null

  const ownerKind = resolveOwnerKind(input)

  if (input.grantKind === 'user') {
    const result = await db.query(
      `UPDATE oauth_grants
          SET provider = $6,
              access_token_encrypted = $7,
              refresh_token_encrypted = $8,
              access_token_expires_at = $9,
              updated_at = NOW()
        WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
          AND user_id = $4 AND oauth_client_id = $5 AND grant_kind = 'user'
        RETURNING id`,
      [
        ownerKind,
        input.recipeNamespace,
        input.recipeName,
        input.userId,
        input.oauthClientId,
        input.provider,
        accessTokenEncrypted,
        refreshTokenEncrypted,
        accessTokenExpiresAt,
      ]
    )
    return { updated: (result.rowCount ?? 0) > 0 }
  }

  if (input.grantKind === 'shared') {
    const result = await db.query(
      `UPDATE oauth_grants
          SET provider = $6,
              access_token_encrypted = $7,
              refresh_token_encrypted = $8,
              access_token_expires_at = $9,
              updated_at = NOW()
        WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
          AND context_id = $4 AND oauth_client_id = $5 AND grant_kind = 'shared'
        RETURNING id`,
      [
        ownerKind,
        input.recipeNamespace,
        input.recipeName,
        input.contextId,
        input.oauthClientId,
        input.provider,
        accessTokenEncrypted,
        refreshTokenEncrypted,
        accessTokenExpiresAt,
      ]
    )
    return { updated: (result.rowCount ?? 0) > 0 }
  }

  const result = await db.query(
    `UPDATE oauth_grants
        SET provider = $5,
            access_token_encrypted = $6,
            refresh_token_encrypted = $7,
            access_token_expires_at = $8,
            updated_at = NOW()
      WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
        AND user_id IS NULL AND oauth_client_id = $4 AND grant_kind = 'service'
      RETURNING id`,
    [
      ownerKind,
      input.recipeNamespace,
      input.recipeName,
      input.oauthClientId,
      input.provider,
      accessTokenEncrypted,
      refreshTokenEncrypted,
      accessTokenExpiresAt,
    ]
  )
  return { updated: (result.rowCount ?? 0) > 0 }
}

/**
 * Bootstrap a `shared` (context-identity) grant: the FIRST member to authorize
 * an OAuth mcp-server with `grantScope='context'` lends their provider identity
 * to the whole Context.
 *
 * INSERT … ON CONFLICT — first-wins tokens AND bootstrapper WITHIN ONE
 * INSTALLATION. If a concurrent second member of the SAME install also completes
 * consent (both minted an authorize URL inside the state TTL), their row is a
 * no-op: they inherit the team's grant, and `bootstrapped_by_user_id` keeps
 * pointing at whoever landed first (mini-spec 05 §3).
 *
 * Fenced by `cr_uid` (R3-H5): a conflicting row of the SAME uid is left as-is
 * (first bootstrapper wins), but a row of a DIFFERENT uid — or a legacy row
 * (cr_uid NULL) left by a previous installation of the same name whose teardown
 * did not reach it — is NOT this install's first bootstrapper, so it is REPLACED
 * (the `WHERE cr_uid IS DISTINCT FROM` guard on the DO UPDATE). Distinct from the
 * refresh path (`upsertOAuthGrant` above): conflating them would let the last
 * completer of the same install silently overwrite the team identity and audit.
 *
 * Returns `{ inserted }` — true when THIS consent established this install's shared
 * identity (a fresh insert, or a replace of a stale other-install/legacy row);
 * false when it joined an existing same-install team identity.
 */
export type BootstrapSharedOAuthGrantInput = {
  ownerKind: 'mcpserver'
  recipeNamespace: string
  recipeName: string
  contextId: string
  oauthClientId: string
  /** Who authorized the shared identity (audit; from signed state claims, never a body param). */
  bootstrappedByUserId: string
  provider: string
  accessToken: string
  refreshToken?: string
  accessTokenExpiresInSec?: number
  /** metadata.uid of the owning McpServer, sealing the grant to this installation. */
  crUid?: string
}

export async function bootstrapSharedOAuthGrant(
  db: DbClient,
  encryptionKey: Buffer,
  input: BootstrapSharedOAuthGrantInput
): Promise<{ inserted: boolean }> {
  const accessTokenEncrypted = encryptOAuthSecret(encryptionKey, input.accessToken)
  const refreshTokenEncrypted = input.refreshToken
    ? encryptOAuthSecret(encryptionKey, input.refreshToken)
    : null
  const accessTokenExpiresAt =
    typeof input.accessTokenExpiresInSec === 'number'
      ? new Date(Date.now() + input.accessTokenExpiresInSec * 1000)
      : null

  const result = await db.query(
    `INSERT INTO oauth_grants (
       owner_kind, recipe_namespace, recipe_name, user_id, context_id,
       oauth_client_id, grant_kind, bootstrapped_by_user_id,
       provider, access_token_encrypted, refresh_token_encrypted,
       access_token_expires_at, cr_uid, updated_at
     ) VALUES ($1, $2, $3, NULL, $4, $5, 'shared', $6, $7, $8, $9, $10, $11, NOW())
     ON CONFLICT (owner_kind, recipe_namespace, recipe_name, context_id, oauth_client_id)
       WHERE grant_kind = 'shared'
     DO UPDATE SET
       bootstrapped_by_user_id = EXCLUDED.bootstrapped_by_user_id,
       provider = EXCLUDED.provider,
       access_token_encrypted = EXCLUDED.access_token_encrypted,
       refresh_token_encrypted = EXCLUDED.refresh_token_encrypted,
       access_token_expires_at = EXCLUDED.access_token_expires_at,
       cr_uid = EXCLUDED.cr_uid,
       updated_at = NOW()
     WHERE oauth_grants.cr_uid IS DISTINCT FROM EXCLUDED.cr_uid
     RETURNING id`,
    [
      input.ownerKind,
      input.recipeNamespace,
      input.recipeName,
      input.contextId,
      input.oauthClientId,
      input.bootstrappedByUserId,
      input.provider,
      accessTokenEncrypted,
      refreshTokenEncrypted,
      accessTokenExpiresAt,
      input.crUid ?? null,
    ]
  )
  return { inserted: (result.rowCount ?? 0) > 0 }
}

export type GetOAuthGrantInput = OAuthGrantKey & {
  /** User grants only: require background=true (per-user broker, SEC-5). Ignored for service. */
  requireBackground?: boolean
}

const SELECT_COLUMNS = `owner_kind, recipe_namespace, recipe_name, user_id, context_id,
            bootstrapped_by_user_id, oauth_client_id, grant_kind,
            provider, access_token_encrypted, refresh_token_encrypted,
            access_token_expires_at, updated_at, background, cr_uid`

interface OAuthGrantDbRow {
  owner_kind: OAuthOwnerKind
  recipe_namespace: string
  recipe_name: string
  user_id: string | null
  context_id: string | null
  bootstrapped_by_user_id: string | null
  oauth_client_id: string
  grant_kind: 'user' | 'service' | 'shared'
  provider: string
  access_token_encrypted: string
  refresh_token_encrypted: string | null
  access_token_expires_at: Date | null
  updated_at: Date
  background: boolean
  cr_uid: string | null
}

/**
 * Installation-identity predicate for the row readers, appended to each flavor's
 * WHERE with `nextParam` (and `nextParam + 1`) as its placeholder indexes. Returns
 * null when the key can match no row (an mcp-server key without a uid). A row
 * sealed with the key's uid is always visible. An unsealed (`cr_uid IS NULL`) row
 * is visible only to a key carrying `legacyProvider`, and only when its `provider`
 * matches: pods that predate install identity wrote it, always on the baked lane,
 * so serving it to another lane or provider would hand its tokens to an endpoint
 * that was never consented to.
 */
function installIdentityFence(
  input: OAuthGrantKey,
  nextParam: number
): { sql: string; params: string[] } | null {
  if (resolveOwnerKind(input) !== 'mcpserver') return { sql: '', params: [] }
  if (typeof input.crUid !== 'string' || input.crUid.length === 0) return null
  if (typeof input.legacyProvider === 'string' && input.legacyProvider.length > 0) {
    return {
      sql: ` AND (cr_uid = $${nextParam} OR (cr_uid IS NULL AND provider = $${nextParam + 1}))`,
      params: [input.crUid, input.legacyProvider],
    }
  }
  return { sql: ` AND cr_uid = $${nextParam}`, params: [input.crUid] }
}

/**
 * Read one grant row, decrypting tokens. Returns null when no row exists, or when
 * an mcp-server row belongs to a different installation than `input.crUid`.
 */
export async function getOAuthGrant(
  db: DbClient,
  encryptionKey: Buffer,
  input: GetOAuthGrantInput
): Promise<OAuthGrantRow | null> {
  const ownerKind = resolveOwnerKind(input)
  const fence = installIdentityFence(input, input.grantKind === 'service' ? 5 : 6)
  if (!fence) return null
  const result =
    input.grantKind === 'user'
      ? await db.query(
          `SELECT ${SELECT_COLUMNS}
           FROM oauth_grants
           WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
             AND user_id = $4 AND oauth_client_id = $5 AND grant_kind = 'user'${
               input.requireBackground ? ' AND background = true' : ''
             }${fence.sql}`,
          [
            ownerKind,
            input.recipeNamespace,
            input.recipeName,
            input.userId,
            input.oauthClientId,
            ...fence.params,
          ]
        )
      : input.grantKind === 'shared'
        ? await db.query(
            `SELECT ${SELECT_COLUMNS}
             FROM oauth_grants
             WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
               AND user_id IS NULL AND context_id = $4 AND oauth_client_id = $5
               AND grant_kind = 'shared'${input.requireBackground ? ' AND background = true' : ''}${
                 fence.sql
               }`,
            [
              ownerKind,
              input.recipeNamespace,
              input.recipeName,
              input.contextId,
              input.oauthClientId,
              ...fence.params,
            ]
          )
        : await db.query(
            `SELECT ${SELECT_COLUMNS}
             FROM oauth_grants
             WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
               AND user_id IS NULL AND oauth_client_id = $4 AND grant_kind = 'service'${fence.sql}`,
            [
              ownerKind,
              input.recipeNamespace,
              input.recipeName,
              input.oauthClientId,
              ...fence.params,
            ]
          )
  if (result.rows.length === 0) return null
  const row = result.rows[0] as OAuthGrantDbRow
  return {
    grantKind: row.grant_kind,
    ownerKind: row.owner_kind,
    recipeNamespace: row.recipe_namespace,
    recipeName: row.recipe_name,
    userId: row.user_id ?? undefined,
    contextId: row.context_id ?? undefined,
    bootstrappedByUserId: row.bootstrapped_by_user_id ?? undefined,
    oauthClientId: row.oauth_client_id,
    provider: row.provider,
    accessToken: decryptOAuthSecret(encryptionKey, row.access_token_encrypted),
    refreshToken: row.refresh_token_encrypted
      ? decryptOAuthSecret(encryptionKey, row.refresh_token_encrypted)
      : undefined,
    accessTokenExpiresAt: row.access_token_expires_at ?? undefined,
    updatedAt: row.updated_at,
    background: row.background,
    crUid: row.cr_uid ?? undefined,
  }
}

/**
 * Existence check without decrypting tokens — for status endpoints that only
 * need to know whether a grant is connected. Same installation-identity fence as
 * {@link getOAuthGrant}.
 */
export async function oauthGrantExists(db: DbClient, input: GetOAuthGrantInput): Promise<boolean> {
  const ownerKind = resolveOwnerKind(input)
  const fence = installIdentityFence(input, input.grantKind === 'service' ? 5 : 6)
  if (!fence) return false
  const result =
    input.grantKind === 'user'
      ? await db.query(
          `SELECT 1 FROM oauth_grants
           WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
             AND user_id = $4 AND oauth_client_id = $5 AND grant_kind = 'user'${
               input.requireBackground ? ' AND background = true' : ''
             }${fence.sql}
           LIMIT 1`,
          [
            ownerKind,
            input.recipeNamespace,
            input.recipeName,
            input.userId,
            input.oauthClientId,
            ...fence.params,
          ]
        )
      : input.grantKind === 'shared'
        ? await db.query(
            `SELECT 1 FROM oauth_grants
             WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
               AND user_id IS NULL AND context_id = $4 AND oauth_client_id = $5
               AND grant_kind = 'shared'${input.requireBackground ? ' AND background = true' : ''}${
                 fence.sql
               }
             LIMIT 1`,
            [
              ownerKind,
              input.recipeNamespace,
              input.recipeName,
              input.contextId,
              input.oauthClientId,
              ...fence.params,
            ]
          )
        : await db.query(
            `SELECT 1 FROM oauth_grants
             WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
               AND user_id IS NULL AND oauth_client_id = $4 AND grant_kind = 'service'${fence.sql}
             LIMIT 1`,
            [
              ownerKind,
              input.recipeNamespace,
              input.recipeName,
              input.oauthClientId,
              ...fence.params,
            ]
          )
  return result.rows.length > 0
}

/**
 * Delete a grant row by its flavored key. Returns the number of rows deleted so
 * callers can audit "did this actually revoke something" (0 ⇒ the grant was
 * already gone; the endpoint stays idempotent either way).
 */
export async function deleteOAuthGrant(db: DbClient, input: GetOAuthGrantInput): Promise<number> {
  const ownerKind = resolveOwnerKind(input)
  if (input.grantKind === 'user') {
    const result = await db.query(
      `DELETE FROM oauth_grants
       WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
         AND user_id = $4 AND oauth_client_id = $5 AND grant_kind = 'user'`,
      [ownerKind, input.recipeNamespace, input.recipeName, input.userId, input.oauthClientId]
    )
    return result.rowCount ?? 0
  }
  if (input.grantKind === 'shared') {
    const result = await db.query(
      `DELETE FROM oauth_grants
       WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
         AND user_id IS NULL AND context_id = $4 AND oauth_client_id = $5 AND grant_kind = 'shared'`,
      [ownerKind, input.recipeNamespace, input.recipeName, input.contextId, input.oauthClientId]
    )
    return result.rowCount ?? 0
  }
  const result = await db.query(
    `DELETE FROM oauth_grants
     WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
       AND user_id IS NULL AND oauth_client_id = $4 AND grant_kind = 'service'`,
    [ownerKind, input.recipeNamespace, input.recipeName, input.oauthClientId]
  )
  return result.rowCount ?? 0
}

/**
 * Purge EVERY oauth_grants row owned by one McpServer — server teardown only
 * (called from the CR uninstall). Deletes across all flavors (user/service/shared),
 * all users, all contexts, all client ids for the server coordinate. Returns the
 * row count deleted (0 ⇒ the server had none; idempotent).
 *
 * NOT the per-user revocation path (deleteOAuthGrant): that removes ONE user's grant
 * by full key and must never cascade to other users or to dynamic_clients. This one
 * is the opposite — a full wipe scoped to the server, run only when the server CR is
 * being deleted.
 */
export async function deleteOAuthGrantsForServer(
  db: DbClient,
  coords: { recipeNamespace: string; recipeName: string; crUid: string }
): Promise<number> {
  // `owner_kind = 'mcpserver'` is load-bearing: it must never touch recipe-domain
  // grants that share a name coordinate. No user_id / context_id / oauth_client_id /
  // grant_kind filter — the wipe is intentionally all-flavors FOR THIS INSTALLATION.
  //
  // Fenced by `cr_uid`: grants sealed with the uninstalled CR's uid are purged, and
  // legacy grants (cr_uid IS NULL, written before install identity or by a pod mid-
  // rollout) are purged too. A grant sealed with a DIFFERENT uid — a same-name
  // reinstall's consent that landed in the teardown window — SURVIVES (R3-H5).
  const result = await db.query(
    `DELETE FROM oauth_grants
     WHERE owner_kind = 'mcpserver' AND recipe_namespace = $1 AND recipe_name = $2
       AND (cr_uid = $3 OR cr_uid IS NULL)`,
    [coords.recipeNamespace, coords.recipeName, coords.crUid]
  )
  return result.rowCount ?? 0
}

/**
 * Set (or clear) the background-consent flag on a user grant. Separate from
 * upsertOAuthGrant so the refresh-on-demand re-upsert never disturbs consent.
 */
export async function setUserGrantBackground(
  db: DbClient,
  key: { recipeNamespace: string; recipeName: string; userId: string; oauthClientId: string },
  background: boolean
): Promise<void> {
  // Recipe-domain user grants only (Profile UI "background consent"). OAuth
  // mcp-server grants live under owner_kind='mcpserver'; their background
  // handling, if any, is future work — keep this scoped so the two never mix.
  await db.query(
    `UPDATE oauth_grants SET background = $5, updated_at = NOW()
     WHERE owner_kind = 'recipe' AND recipe_namespace = $1 AND recipe_name = $2
       AND user_id = $3 AND oauth_client_id = $4 AND grant_kind = 'user'`,
    [key.recipeNamespace, key.recipeName, key.userId, key.oauthClientId, background]
  )
}

export interface UserGrantSummary {
  /** Owner domain of the grant; lets the client route the DELETE by owner (D-1). */
  ownerKind: OAuthOwnerKind
  recipeNamespace: string
  recipeName: string
  oauthClientId: string
  provider: string
  background: boolean
  updatedAt: Date
  /** Present only when `ownerKind==='mcpserver'`: the McpServer name (= recipe_name). */
  mcpServerName?: string
}

/**
 * All of a user's grants (for the Profile UI "Connected accounts" list).
 *
 * `ownerKind` selects the owner domain: the default `'recipe'` preserves the
 * historical recipe-only behavior byte-for-byte (invariant 3); `'all'` returns
 * both recipe and mcpserver grants (spec 04 D-1) so Profile UI can see and
 * revoke mcp-server identities; a specific kind filters to that one domain. The
 * query is not duplicated — the owner filter is parameterised in place (D4).
 */
export async function listUserOAuthGrants(
  db: DbClient,
  userId: string,
  ownerKind: OAuthOwnerKind | 'all' = 'recipe'
): Promise<UserGrantSummary[]> {
  const params: unknown[] = [userId]
  let ownerFilter = ''
  if (ownerKind !== 'all') {
    params.push(ownerKind)
    ownerFilter = ` AND owner_kind = $${params.length}`
  }
  const result = await db.query(
    `SELECT owner_kind, recipe_namespace, recipe_name, oauth_client_id, provider, background, updated_at
     FROM oauth_grants
     WHERE user_id = $1 AND grant_kind = 'user'${ownerFilter}
     ORDER BY recipe_name, oauth_client_id`,
    params
  )
  return result.rows.map(r => {
    const row = r as {
      owner_kind: OAuthOwnerKind
      recipe_namespace: string
      recipe_name: string
      oauth_client_id: string
      provider: string
      background: boolean
      updated_at: Date
    }
    const summary: UserGrantSummary = {
      ownerKind: row.owner_kind,
      recipeNamespace: row.recipe_namespace,
      recipeName: row.recipe_name,
      oauthClientId: row.oauth_client_id,
      provider: row.provider,
      background: row.background,
      updatedAt: row.updated_at,
    }
    // For mcp-server owners the McpServer name lives in recipe_name; surface it
    // under a purpose-named field so the UI never parses the recipe field.
    if (row.owner_kind === 'mcpserver') {
      summary.mcpServerName = row.recipe_name
    }
    return summary
  })
}

/** All user grants for one (recipe, client) — admin oversight (read-only). */
export async function listUserGrantsForClient(
  db: DbClient,
  key: { recipeNamespace: string; recipeName: string; oauthClientId: string }
): Promise<{ userId: string; background: boolean; updatedAt: Date }[]> {
  const result = await db.query(
    `SELECT user_id, background, updated_at
     FROM oauth_grants
     WHERE owner_kind = 'recipe' AND recipe_namespace = $1 AND recipe_name = $2
       AND oauth_client_id = $3 AND grant_kind = 'user'
     ORDER BY user_id`,
    [key.recipeNamespace, key.recipeName, key.oauthClientId]
  )
  return result.rows.map(r => {
    const row = r as { user_id: string; background: boolean; updated_at: Date }
    return { userId: row.user_id, background: row.background, updatedAt: row.updated_at }
  })
}

/**
 * All user grants for one mcp-server — admin oversight (read-only, spec 04 U3).
 *
 * Mirror of {@link listUserGrantsForClient} but keyed by the server coordinate
 * `(namespace, name)` WITHOUT an oauthClientId: it lists EVERY user grant of the
 * server across clients, returning each row's `oauthClientId` so the admin can
 * force-revoke a specific one. Scoped to `owner_kind='mcpserver'`,
 * `grant_kind='user'`.
 */
export async function listUserGrantsForServer(
  db: DbClient,
  key: { namespace: string; name: string }
): Promise<{ userId: string; oauthClientId: string; background: boolean; updatedAt: Date }[]> {
  const result = await db.query(
    `SELECT user_id, oauth_client_id, background, updated_at
     FROM oauth_grants
     WHERE owner_kind = 'mcpserver' AND recipe_namespace = $1 AND recipe_name = $2
       AND grant_kind = 'user'
     ORDER BY user_id, oauth_client_id`,
    [key.namespace, key.name]
  )
  return result.rows.map(r => {
    const row = r as {
      user_id: string
      oauth_client_id: string
      background: boolean
      updated_at: Date
    }
    return {
      userId: row.user_id,
      oauthClientId: row.oauth_client_id,
      background: row.background,
      updatedAt: row.updated_at,
    }
  })
}

/**
 * List the userIds with a background-consented user grant for (recipe, client).
 * SEC-6: scoped to one recipe + client; returns opaque platform user ids only.
 */
export async function listBackgroundUserGrants(
  db: DbClient,
  key: { recipeNamespace: string; recipeName: string; oauthClientId: string }
): Promise<string[]> {
  const result = await db.query(
    `SELECT user_id FROM oauth_grants
     WHERE owner_kind = 'recipe' AND recipe_namespace = $1 AND recipe_name = $2
       AND oauth_client_id = $3 AND grant_kind = 'user' AND background = true
     ORDER BY user_id`,
    [key.recipeNamespace, key.recipeName, key.oauthClientId]
  )
  return result.rows.map(r => (r as { user_id: string }).user_id)
}

/**
 * Buffers (milliseconds) that define the proactive window on the token's life:
 * a grant is a proactive-refresh candidate while
 * `now + reactiveBufferMs < access_token_expires_at ≤ now + proactiveBufferMs`.
 * `proactiveBufferMs` = Bp, `reactiveBufferMs` = Br (Bp > Br, config invariant).
 */
export interface ProactiveWindow {
  proactiveBufferMs: number
  reactiveBufferMs: number
}

interface RemoteGrantWindowDbRow {
  owner_kind: OAuthOwnerKind
  recipe_namespace: string
  recipe_name: string
  user_id: string | null
  context_id: string | null
  oauth_client_id: string
  grant_kind: 'user' | 'service' | 'shared'
  cr_uid: string
}

/**
 * Reconstruct the flavored {@link OAuthGrantKey} for an mcpserver-owned grant
 * enumerated by {@link listRemoteGrantsInProactiveWindow} (remote OR generic lane;
 * the key is derived from `grant_kind`, independent of provider). The enumeration
 * filter admits only `shared` and background `user` grants, so those are the only
 * two flavors handled; a `service`/unexpected row throws (fail loud, never mint a
 * malformed key). The key carries the row's own `cr_uid` so the engine's re-read
 * finds this row; whether that uid is still the live installation is decided by
 * the engine against the owner CR it reads before refreshing.
 */
function remoteGrantKeyFromRow(row: RemoteGrantWindowDbRow): OAuthGrantKey {
  if (row.grant_kind === 'user') {
    if (!row.user_id) throw new Error('remote user grant row missing user_id')
    return {
      grantKind: 'user',
      ownerKind: 'mcpserver',
      recipeNamespace: row.recipe_namespace,
      recipeName: row.recipe_name,
      userId: row.user_id,
      oauthClientId: row.oauth_client_id,
      crUid: row.cr_uid,
    }
  }
  if (row.grant_kind === 'shared') {
    if (!row.context_id) throw new Error('remote shared grant row missing context_id')
    return {
      grantKind: 'shared',
      ownerKind: 'mcpserver',
      recipeNamespace: row.recipe_namespace,
      recipeName: row.recipe_name,
      contextId: row.context_id,
      oauthClientId: row.oauth_client_id,
      crUid: row.cr_uid,
    }
  }
  throw new Error(`unexpected grant_kind for remote proactive candidate: ${row.grant_kind}`)
}

/**
 * Enumerate the mcpserver-owned grants whose access token sits inside the
 * proactive window (mini-spec L §6.3). Covers BOTH the remote and generic
 * (mcpserver-owned) lanes: a `generic` grant admits background/context use and a
 * rotating refresh token exactly like remote (its reactive refresh already runs
 * via `refreshGenericGrant`), so an unattended one has the same silent-expiry risk
 * and belongs in the sweep (R1-L1). The DCR secret-expiry sweep stays remote-only
 * — dynamic clients are a remote-lane concept — so this function keeps its name.
 * A snapshot SELECT with NO lock — this enumeration does not hold a connection
 * across the whole sweep; the cron re-claims each row under `FOR UPDATE SKIP
 * LOCKED` in its OWN short transaction ({@link claimRemoteGrantForRefresh}), and
 * that per-row transaction DOES pin its connection across ITS refresh POST,
 * bounded by the carrier idle-in-transaction timeout
 * (`boundOAuthRefreshLockIdleTimeout`).
 *
 * Filter (§4 eligibility + window): `provider IN ('remote','generic')`, non-null
 * expiry in `(now+Br, now+Bp]`, and `grant_kind='shared' OR (grant_kind='user' AND
 * background=true)` (SEC-5: unattended use only — non-background user grants are
 * excluded here as a first line of defense, `requireBackground:true` being the
 * second), sealed to an installation (`cr_uid IS NOT NULL`). Returns the flavored
 * keys; tokens are never read.
 */
export async function listRemoteGrantsInProactiveWindow(
  db: DbClient,
  window: ProactiveWindow
): Promise<OAuthGrantKey[]> {
  const result = await db.query(
    `SELECT owner_kind, recipe_namespace, recipe_name, user_id, context_id,
            oauth_client_id, grant_kind, cr_uid
       FROM oauth_grants
      WHERE owner_kind = 'mcpserver'
        AND provider IN ('remote', 'generic')
        AND access_token_expires_at IS NOT NULL
        AND access_token_expires_at > NOW() + ($1::bigint * INTERVAL '1 millisecond')
        AND access_token_expires_at <= NOW() + ($2::bigint * INTERVAL '1 millisecond')
        AND (grant_kind = 'shared' OR (grant_kind = 'user' AND background = true))
        -- An unsealed (legacy) row has no installation the sweep could check it
        -- against: the key built from it would match nothing in the fenced reader.
        -- The fenced reader serves an unsealed row only to a baked key of the same
        -- provider, so an unsealed remote/generic row is never refreshed on any
        -- path; reconnecting replaces and seals it.
        AND cr_uid IS NOT NULL
      ORDER BY recipe_namespace, recipe_name, oauth_client_id, grant_kind,
               user_id NULLS FIRST, context_id NULLS FIRST`,
    [window.reactiveBufferMs, window.proactiveBufferMs]
  )
  return result.rows.map(r => remoteGrantKeyFromRow(r as RemoteGrantWindowDbRow))
}

/**
 * Claim one enumerated grant for proactive refresh inside an open transaction
 * (mini-spec L §6.3). Runs `SELECT 1 … FOR UPDATE SKIP LOCKED` keyed by the exact
 * grant coordinate AND re-checks the proactive window, so a row another replica
 * already holds, or one the reactive path renewed out of the window since the
 * snapshot, is skipped (returns `false`). On `true` the caller holds the row lock
 * for the rest of the transaction and MUST run the refresh UPDATE on the SAME
 * `txClient` so it lands under this lock.
 *
 * `txClient` MUST be a client inside an open transaction (BEGIN issued); the lock
 * only survives until COMMIT/ROLLBACK.
 */
export async function claimRemoteGrantForRefresh(
  txClient: DbClient,
  key: OAuthGrantKey,
  window: ProactiveWindow
): Promise<boolean> {
  const ownerKind = resolveOwnerKind(key)
  // The window buffers are the FIRST two params ($1 = reactive, $2 = proactive)
  // of every branch so the window predicate is byte-identical across flavors and
  // needs no string surgery. The flavor's key params follow from $3.
  const windowPredicate = `access_token_expires_at IS NOT NULL
        AND access_token_expires_at > NOW() + ($1::bigint * INTERVAL '1 millisecond')
        AND access_token_expires_at <= NOW() + ($2::bigint * INTERVAL '1 millisecond')`
  const windowParams = [window.reactiveBufferMs, window.proactiveBufferMs]

  if (key.grantKind === 'user') {
    const result = await txClient.query(
      `SELECT 1 FROM oauth_grants
        WHERE ${windowPredicate}
          AND owner_kind = $3 AND recipe_namespace = $4 AND recipe_name = $5
          AND user_id = $6 AND oauth_client_id = $7 AND grant_kind = 'user'
        FOR UPDATE SKIP LOCKED`,
      [
        ...windowParams,
        ownerKind,
        key.recipeNamespace,
        key.recipeName,
        key.userId,
        key.oauthClientId,
      ]
    )
    return (result.rowCount ?? 0) > 0
  }

  if (key.grantKind === 'shared') {
    const result = await txClient.query(
      `SELECT 1 FROM oauth_grants
        WHERE ${windowPredicate}
          AND owner_kind = $3 AND recipe_namespace = $4 AND recipe_name = $5
          AND user_id IS NULL AND context_id = $6 AND oauth_client_id = $7
          AND grant_kind = 'shared'
        FOR UPDATE SKIP LOCKED`,
      [
        ...windowParams,
        ownerKind,
        key.recipeNamespace,
        key.recipeName,
        key.contextId,
        key.oauthClientId,
      ]
    )
    return (result.rowCount ?? 0) > 0
  }

  const result = await txClient.query(
    `SELECT 1 FROM oauth_grants
      WHERE ${windowPredicate}
        AND owner_kind = $3 AND recipe_namespace = $4 AND recipe_name = $5
        AND user_id IS NULL AND oauth_client_id = $6 AND grant_kind = 'service'
      FOR UPDATE SKIP LOCKED`,
    [...windowParams, ownerKind, key.recipeNamespace, key.recipeName, key.oauthClientId]
  )
  return (result.rowCount ?? 0) > 0
}

/**
 * Take the per-grant row lock for the REACTIVE refresh path (mini-spec 16,
 * R2-M1). `SELECT 1 … FOR UPDATE` keyed by the exact grant coordinate — BLOCKING
 * (no `SKIP LOCKED`) and with NO window predicate, unlike the proactive claim
 * ({@link claimRemoteGrantForRefresh}): a reactive caller must WAIT for whoever
 * holds the row (proactive or another reactive) and then re-read the fresh token,
 * rather than skip and double-spend the rotating refresh token. Sharing the same
 * row is what makes the reactive lock mutually exclusive with the proactive
 * `FOR UPDATE SKIP LOCKED`.
 *
 * Returns `rowCount > 0`: `false` means the row is absent (deleted, or never
 * existed). The engine's re-read under the lock already surfaces that as
 * `no_grant`, so the boolean is an informational signal, not a required branch.
 *
 * `txClient` MUST be inside an open transaction (BEGIN issued); the lock only
 * survives until COMMIT/ROLLBACK.
 *
 * F2 Class-check: the per-flavor WHERE (owner_kind + coordinate + grant_kind) is
 * repeated verbatim across `getOAuthGrant`, `oauthGrantExists`, `deleteOAuthGrant`,
 * `refreshOAuthGrantTokens`, `claimRemoteGrantForRefresh` — this is the 6th site.
 * Deliberately NOT extracted to a shared helper: that would touch the proactive
 * claim (hot path, green). If the key semantics change, grep `grant_kind = 'user'`
 * / `'shared'` / `'service'` and change all six together.
 *
 * One deliberate exception: the installation-identity fence (`cr_uid`,
 * {@link installIdentityFence}) lives ONLY in the two row readers. It is a read
 * fence. Deletes must still reach rows of a dead installation (a revoke or a
 * teardown has to be able to remove them), the locks only serialize — the engine
 * that runs under them re-reads through the fenced reader — and the refresh UPDATE
 * only ever follows that fenced read and never writes `cr_uid`.
 */
export async function lockOAuthGrantForRefresh(
  txClient: DbClient,
  key: OAuthGrantKey
): Promise<boolean> {
  const ownerKind = resolveOwnerKind(key)

  if (key.grantKind === 'user') {
    const result = await txClient.query(
      `SELECT 1 FROM oauth_grants
        WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
          AND user_id = $4 AND oauth_client_id = $5 AND grant_kind = 'user'
        FOR UPDATE`,
      [ownerKind, key.recipeNamespace, key.recipeName, key.userId, key.oauthClientId]
    )
    return (result.rowCount ?? 0) > 0
  }

  if (key.grantKind === 'shared') {
    const result = await txClient.query(
      `SELECT 1 FROM oauth_grants
        WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
          AND user_id IS NULL AND context_id = $4 AND oauth_client_id = $5
          AND grant_kind = 'shared'
        FOR UPDATE`,
      [ownerKind, key.recipeNamespace, key.recipeName, key.contextId, key.oauthClientId]
    )
    return (result.rowCount ?? 0) > 0
  }

  const result = await txClient.query(
    `SELECT 1 FROM oauth_grants
      WHERE owner_kind = $1 AND recipe_namespace = $2 AND recipe_name = $3
        AND user_id IS NULL AND oauth_client_id = $4 AND grant_kind = 'service'
      FOR UPDATE`,
    [ownerKind, key.recipeNamespace, key.recipeName, key.oauthClientId]
  )
  return (result.rowCount ?? 0) > 0
}