Skip to content

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

TypeScript
49.5 KB
Raw Blame
import { config } from '../config.js'
import { type DbClient, pool, withTransaction } from '../db.js'
import { rootLogger } from '../observability/logger.js'
import {
  INVITATION_TTL_HOURS,
  type InvitationTeamAssignment,
  insertInvitationForTeams,
  invitationForTeamsResponse,
} from './directory/invitationStore.js'
import { retireDesktopUserInTransaction } from './directory/users.js'
import { gfsDesktopOperatorLinkService } from './gfsDesktopOperatorLinkService.js'
import { currentAdministrativeRequestContext } from './tracing/adminOperationContext.js'
import { AdministrativeEventService } from './tracing/administrativeEvents.js'
import { CONTROL_API_LOCAL_ADMINISTRATIVE_PRINCIPAL_V1 } from './tracing/controlApiLocalAdministrativeBindingResolver.js'
import { canonicalTracingEnvironment } from './tracing/environment.js'

const administrativeEvents = new AdministrativeEventService({
  transaction: async () => {
    throw new Error('administrative event append requires the caller transaction')
  },
})

export type AdminUserRecord = {
  id: string
  username: string
  email: string | null
  passwordHash: string
  sessionVersion: number
  role: 'admin'
  status: 'active' | 'disabled'
  failedAttempts: number
  lockedUntil: Date | null
}

export type ControlAdminListItem = {
  id: string
  username: string
  email: string | null
  memberId: string | null
  status: 'active' | 'disabled' | 'pending_password'
  passwordPending?: boolean
  invitationId?: string
  replaceInviter?: boolean
  invitedByAdminId?: string | null
  gfsOperatorLink?: {
    desktopUserId: string
    controlAdminId: string
    source: 'initial_setup' | 'unknown'
    createdAt: string | null
    status: 'active' | 'inactive_admin' | 'revoked' | 'error'
    generation: number | null
    rowVersion: number | null
    revocationReason: string | null
  } | null
  gfsOperatorLinkStatus?: 'none' | 'active' | 'inactive_admin' | 'revoked' | 'error'
  lastLoginAt: string | null
  createdAt: string
}

export type ControlAdminInvitationRecord = {
  id: string
  email: string
  status: 'pending' | 'opened' | 'accepted' | 'revoked'
  expiresAt: Date
  createdAt: Date
  acceptedAt: Date | null
  /** Accepting this invitation retires the inviter (completeControlAdminInvitation). */
  replaceInviter: boolean
  /** NULL when the inviting admin row was deleted (FK ON DELETE SET NULL). */
  invitedByAdminId: string | null
}

const INVITATION_COLUMNS =
  'id, email, status, expires_at, created_at, accepted_at, invited_by_admin_id, replace_inviter'

export type ControlAdminEmailChangeRequestRecord = {
  id: string
  adminId: string
  email: string
  status: 'pending' | 'confirmed' | 'revoked'
  expiresAt: Date
  createdAt: Date
  confirmedAt: Date | null
}

export type ControlAdminPasswordResetRequestRecord = {
  id: string
  adminId: string
  email: string
  status: 'pending' | 'used' | 'revoked'
  expiresAt: Date
  createdAt: Date
  usedAt: Date | null
}

function mapAdminRow(row: {
  id: string
  username: string
  email: string | null
  password_hash: string
  session_version?: number | null
  role: 'admin'
  status: 'active' | 'disabled'
  failed_attempts: number
  locked_until: Date | null
}): AdminUserRecord {
  return {
    id: row.id,
    username: row.username,
    email: row.email || null,
    passwordHash: row.password_hash,
    sessionVersion: Number(row.session_version || 0),
    role: row.role,
    status: row.status,
    failedAttempts: Number(row.failed_attempts || 0),
    lockedUntil: row.locked_until ? new Date(row.locked_until) : null,
  }
}

function mapInvitationRow(row: {
  id: string
  email: string
  status: 'pending' | 'opened' | 'accepted' | 'revoked'
  expires_at: Date
  created_at: Date
  accepted_at: Date | null
  invited_by_admin_id: string | null
  replace_inviter: boolean | null
}): ControlAdminInvitationRecord {
  return {
    id: row.id,
    email: row.email,
    status: row.status,
    expiresAt: new Date(row.expires_at),
    createdAt: new Date(row.created_at),
    acceptedAt: row.accepted_at ? new Date(row.accepted_at) : null,
    replaceInviter: row.replace_inviter === true,
    invitedByAdminId: row.invited_by_admin_id ?? null,
  }
}

function mapEmailChangeRequestRow(row: {
  id: string
  admin_id: string
  email: string
  status: 'pending' | 'confirmed' | 'revoked'
  expires_at: Date
  created_at: Date
  confirmed_at: Date | null
}): ControlAdminEmailChangeRequestRecord {
  return {
    id: row.id,
    adminId: row.admin_id,
    email: row.email,
    status: row.status,
    expiresAt: new Date(row.expires_at),
    createdAt: new Date(row.created_at),
    confirmedAt: row.confirmed_at ? new Date(row.confirmed_at) : null,
  }
}

function mapPasswordResetRequestRow(row: {
  id: string
  admin_id: string
  email: string
  status: 'pending' | 'used' | 'revoked'
  expires_at: Date
  created_at: Date
  used_at: Date | null
}): ControlAdminPasswordResetRequestRecord {
  return {
    id: row.id,
    adminId: row.admin_id,
    email: row.email,
    status: row.status,
    expiresAt: new Date(row.expires_at),
    createdAt: new Date(row.created_at),
    usedAt: row.used_at ? new Date(row.used_at) : null,
  }
}

function normalizeEmail(email: string): string {
  return email.trim().toLowerCase()
}

export function isValidAdminEmail(email: string): boolean {
  const normalized = normalizeEmail(email)
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(normalized)
}

export function isValidAdminUsername(username: string): boolean {
  return /^[a-zA-Z0-9._-]{3,64}$/.test(username.trim())
}

export async function findAdminByUsername(username: string): Promise<AdminUserRecord | null> {
  const result = await pool.query(
    `SELECT id, username, email, password_hash, session_version, role, status, failed_attempts, locked_until
       FROM control_admin_users
      WHERE username = $1
      LIMIT 1`,
    [username]
  )
  if ((result.rowCount ?? 0) === 0) return null
  return mapAdminRow(result.rows[0] as never)
}

export async function findAdminByLogin(login: string): Promise<AdminUserRecord | null> {
  const normalized = login.trim()
  const result = await pool.query(
    `SELECT id, username, email, password_hash, session_version, role, status, failed_attempts, locked_until
       FROM control_admin_users
      WHERE username = $1
         OR lower(email) = lower($1)
      LIMIT 1`,
    [normalized]
  )
  if ((result.rowCount ?? 0) === 0) return null
  return mapAdminRow(result.rows[0] as never)
}

export async function findAdminById(id: string): Promise<AdminUserRecord | null> {
  const result = await pool.query(
    `SELECT id, username, email, password_hash, session_version, role, status, failed_attempts, locked_until
       FROM control_admin_users
      WHERE id = $1
      LIMIT 1`,
    [id]
  )
  if ((result.rowCount ?? 0) === 0) return null
  return mapAdminRow(result.rows[0] as never)
}

export async function getPendingControlAdminEmailChangeForAdmin(
  adminId: string
): Promise<ControlAdminEmailChangeRequestRecord | null> {
  const result = await pool.query(
    `SELECT id, admin_id, email, status, expires_at, created_at, confirmed_at
       FROM control_admin_email_change_requests
      WHERE admin_id = $1
        AND status = 'pending'
        AND expires_at > NOW()
      ORDER BY created_at DESC
      LIMIT 1`,
    [adminId]
  )
  if ((result.rowCount ?? 0) === 0) return null
  return mapEmailChangeRequestRow(result.rows[0] as never)
}

export async function getPendingControlAdminPasswordResetRequest(
  db: DbClient,
  requestId: string,
  email: string
): Promise<ControlAdminPasswordResetRequestRecord | null> {
  const result = await db.query(
    `SELECT id, admin_id, email, status, expires_at, created_at, used_at
       FROM control_admin_password_reset_requests
      WHERE id = $1
        AND lower(email) = lower($2)
        AND status = 'pending'
        AND expires_at > NOW()
      LIMIT 1`,
    [requestId, normalizeEmail(email)]
  )
  if ((result.rowCount ?? 0) === 0) return null
  return mapPasswordResetRequestRow(result.rows[0] as never)
}

export async function registerAdminFailedLogin(
  userId: string,
  lockMinutes: number,
  maxFailures: number
): Promise<void> {
  await pool.query(
    `UPDATE control_admin_users
        SET failed_attempts = failed_attempts + 1,
            locked_until = CASE
              WHEN failed_attempts + 1 >= $2 THEN NOW() + ($3::text || ' minutes')::interval
              ELSE locked_until
            END,
            updated_at = NOW()
      WHERE id = $1`,
    [userId, maxFailures, lockMinutes]
  )
}

export async function registerAdminSuccessfulLogin(userId: string): Promise<void> {
  await pool.query(
    `UPDATE control_admin_users
        SET failed_attempts = 0,
            locked_until = NULL,
            last_login_at = NOW(),
            updated_at = NOW()
      WHERE id = $1`,
    [userId]
  )
}

export async function revokeAdminTokenJti(jti: string, expUnix: number): Promise<void> {
  await pool.query(
    `INSERT INTO control_admin_revoked_tokens(jti, expires_at)
     VALUES($1, to_timestamp($2))
     ON CONFLICT (jti) DO NOTHING`,
    [jti, expUnix]
  )
}

export async function isAdminTokenRevoked(jti: string): Promise<boolean> {
  const result = await pool.query(
    `SELECT 1
       FROM control_admin_revoked_tokens
      WHERE jti = $1
      LIMIT 1`,
    [jti]
  )
  return (result.rowCount ?? 0) > 0
}

export async function cleanupExpiredAdminRevokedTokens(): Promise<number> {
  const result = await pool.query(
    `DELETE FROM control_admin_revoked_tokens WHERE expires_at < NOW()`
  )
  return result.rowCount ?? 0
}

let adminRevokedTokenCleanupHandle: ReturnType<typeof setInterval> | null = null

export function startAdminRevokedTokenCleanup(intervalMs: number): void {
  if (adminRevokedTokenCleanupHandle) return
  adminRevokedTokenCleanupHandle = setInterval(() => {
    cleanupExpiredAdminRevokedTokens().catch(error => {
      rootLogger.warn(
        {
          event: 'admin_revoked_token_cleanup_error',
          err: error instanceof Error ? error.message : String(error),
        },
        'admin revoked token cleanup failed'
      )
    })
  }, intervalMs)
  adminRevokedTokenCleanupHandle.unref()
}

export function stopAdminRevokedTokenCleanup(): void {
  if (!adminRevokedTokenCleanupHandle) return
  clearInterval(adminRevokedTokenCleanupHandle)
  adminRevokedTokenCleanupHandle = null
}

export async function setupInitialAdminCredentials(
  bootstrapUsername: string,
  email: string,
  username: string,
  passwordHash: string,
  db: Pick<DbClient, 'query'> = pool
): Promise<AdminUserRecord | null> {
  const result = await db.query(
    `UPDATE control_admin_users
        SET email = $2,
            username = $3,
            password_hash = $4,
            role = 'admin',
            status = 'active',
            failed_attempts = 0,
            locked_until = NULL,
            last_login_at = NOW(),
            updated_at = NOW()
      WHERE id IN (
        SELECT id
          FROM control_admin_users
         WHERE username = $1
           AND status = 'active'
           AND last_login_at IS NULL
         ORDER BY created_at ASC
         LIMIT 1
      )
        AND (
          SELECT COUNT(*)
            FROM control_admin_users
           WHERE status = 'active'
        ) = 1
        AND NOT EXISTS (
          SELECT 1 FROM control_admin_users WHERE lower(email) = lower($2)
        )
        AND NOT EXISTS (
          SELECT 1 FROM control_admin_users WHERE username = $3 AND username <> $1
        )
      RETURNING id, username, email, password_hash, session_version, role, status, failed_attempts, locked_until`,
    [bootstrapUsername, normalizeEmail(email), username.trim(), passwordHash]
  )
  if ((result.rowCount ?? 0) === 0) return null
  return mapAdminRow(result.rows[0] as never)
}

export async function updateAdminUsername(
  adminId: string,
  username: string
): Promise<AdminUserRecord | { error: 'duplicate_username' | 'not_found' }> {
  const normalizedUsername = username.trim()
  const result = await pool.query(
    `UPDATE control_admin_users
        SET username = $2,
            updated_at = NOW()
      WHERE id = $1
        AND NOT EXISTS (
          SELECT 1 FROM control_admin_users WHERE id <> $1 AND username = $2
        )
      RETURNING id, username, email, password_hash, session_version, role, status, failed_attempts, locked_until`,
    [adminId, normalizedUsername]
  )

  if ((result.rowCount ?? 0) === 0) {
    const current = await findAdminById(adminId)
    if (!current) return { error: 'not_found' }
    return { error: 'duplicate_username' }
  }

  return mapAdminRow(result.rows[0] as never)
}

export async function createControlAdminEmailChangeRequest(
  adminId: string,
  email: string
): Promise<ControlAdminEmailChangeRequestRecord | { error: 'duplicate_email' | 'not_found' }> {
  const normalizedEmail = normalizeEmail(email)

  return withTransaction(async db => {
    const admin = await db.query(
      `SELECT id, email
         FROM control_admin_users
        WHERE id = $1
          AND status = 'active'
        LIMIT 1`,
      [adminId]
    )
    if ((admin.rowCount ?? 0) === 0) return { error: 'not_found' as const }

    await db.query(
      `UPDATE control_admin_email_change_requests
          SET status = 'revoked'
        WHERE status = 'pending'
          AND (expires_at <= NOW() OR admin_id = $1)`,
      [adminId]
    )

    const result = await db.query(
      `INSERT INTO control_admin_email_change_requests(admin_id, email)
       SELECT $1, $2
        WHERE NOT EXISTS (
          SELECT 1 FROM control_admin_users
           WHERE id <> $1
             AND lower(email) = lower($2)
        )
          AND NOT EXISTS (
            SELECT 1 FROM control_admin_email_change_requests
             WHERE lower(email) = lower($2)
               AND status = 'pending'
          )
       RETURNING id, admin_id, email, status, expires_at, created_at, confirmed_at`,
      [adminId, normalizedEmail]
    )

    if ((result.rowCount ?? 0) === 0) return { error: 'duplicate_email' as const }
    return mapEmailChangeRequestRow(result.rows[0] as never)
  })
}

export async function createControlAdminPasswordResetRequest(
  login: string
): Promise<ControlAdminPasswordResetRequestRecord | { skipped: true }> {
  const normalizedLogin = login.trim()
  if (!normalizedLogin) return { skipped: true }

  const admin = await findAdminByLogin(normalizedLogin)
  if (!admin || admin.status !== 'active' || !admin.email) {
    return { skipped: true }
  }
  const adminEmail = admin.email

  return withTransaction(async db => {
    await db.query(
      `UPDATE control_admin_password_reset_requests
          SET status = 'revoked'
        WHERE status = 'pending'
          AND (expires_at <= NOW() OR admin_id = $1)`,
      [admin.id]
    )

    const result = await db.query(
      `INSERT INTO control_admin_password_reset_requests(admin_id, email)
       VALUES ($1, $2)
       RETURNING id, admin_id, email, status, expires_at, created_at, used_at`,
      [admin.id, normalizeEmail(adminEmail)]
    )

    return mapPasswordResetRequestRow(result.rows[0] as never)
  })
}

export async function revokeControlAdminPasswordResetRequest(requestId: string): Promise<void> {
  await pool.query(
    `UPDATE control_admin_password_reset_requests
        SET status = 'revoked'
      WHERE id = $1
        AND status = 'pending'`,
    [requestId]
  )
}

export async function revokeControlAdminEmailChangeRequest(requestId: string): Promise<void> {
  await pool.query(
    `UPDATE control_admin_email_change_requests
        SET status = 'revoked'
      WHERE id = $1
        AND status = 'pending'`,
    [requestId]
  )
}

export async function updateAdminPassword(adminId: string, passwordHash: string): Promise<void> {
  await pool.query(
    `UPDATE control_admin_users
        SET password_hash = $2,
            failed_attempts = 0,
            locked_until = NULL,
            updated_at = NOW(),
            session_version = session_version + 1
      WHERE id = $1`,
    [adminId, passwordHash]
  )
}

export async function completeControlAdminPasswordResetRequest(input: {
  requestId: string
  email: string
  passwordHash: string
}): Promise<AdminUserRecord | { error: 'not_found' }> {
  return withTransaction(async db => {
    const resetResult = await db.query(
      `UPDATE control_admin_password_reset_requests
          SET status = 'used',
              used_at = NOW()
        WHERE id = $1
          AND lower(email) = lower($2)
          AND status = 'pending'
          AND expires_at > NOW()
        RETURNING admin_id`,
      [input.requestId, normalizeEmail(input.email)]
    )

    if ((resetResult.rowCount ?? 0) === 0) return { error: 'not_found' as const }

    const reset = resetResult.rows[0] as { admin_id: string }
    const adminResult = await db.query(
      `UPDATE control_admin_users
          SET password_hash = $2,
              failed_attempts = 0,
              locked_until = NULL,
              updated_at = NOW(),
              session_version = session_version + 1
        WHERE id = $1
          AND lower(email) = lower($3)
          AND status = 'active'
        RETURNING id, username, email, password_hash, session_version, role, status, failed_attempts, locked_until`,
      [reset.admin_id, input.passwordHash, normalizeEmail(input.email)]
    )

    if ((adminResult.rowCount ?? 0) === 0) return { error: 'not_found' as const }
    return mapAdminRow(adminResult.rows[0] as never)
  })
}

export async function deleteControlAdmin(
  actorAdminId: string,
  adminId: string
): Promise<{ deleted: true } | { error: 'not_found' }> {
  return withTransaction(db =>
    retireControlAdminInTransaction(db, {
      actorAdminId,
      adminId,
      reason: 'control_admin_retired',
    })
  )
}

/**
 * Retire an active control admin inside the caller's transaction: operator
 * link retired, status disabled, sessions invalidated (session_version + 1),
 * deletion audit and governed administrative event appended. A target that is
 * missing or not active is a stable not_found.
 */
export async function retireControlAdminInTransaction(
  db: DbClient,
  input: { actorAdminId: string; adminId: string; reason: string; sourceStatusRef?: string }
): Promise<{ deleted: true } | { error: 'not_found' }> {
  const { actorAdminId, adminId } = input
  const actorResult = await db.query(
    `SELECT id, username, email
         FROM control_admin_users
        WHERE id = $1
        LIMIT 1`,
    [actorAdminId]
  )

  // Serialize retirement against a concurrent delete/disable. The lifecycle
  // transition is active -> disabled; a replay must be a stable not-found
  // result and must not revoke another generation or append duplicate audit.
  const targetResult = await db.query(
    `SELECT id, username, email, status
         FROM control_admin_users
        WHERE id = $1
        FOR UPDATE`,
    [adminId]
  )
  const target = targetResult.rows[0] as
    | { id: string; username: string; email: string | null; status: 'active' | 'disabled' }
    | undefined
  if (!target || target.status !== 'active') return { error: 'not_found' as const }

  await gfsDesktopOperatorLinkService.retireParentInTransaction(db, {
    kind: 'control_admin',
    parentId: adminId,
    actor: { kind: 'control_admin', controlAdminId: actorAdminId },
    reason: input.reason,
  })
  const deletedResult = await db.query(
    `UPDATE control_admin_users
          SET status = 'disabled', session_version = session_version + 1, updated_at = NOW()
        WHERE id = $1
          AND status = 'active'
        RETURNING id, username, email`,
    [adminId]
  )

  if ((deletedResult.rowCount ?? 0) === 0) return { error: 'not_found' as const }

  await revokeControlAdminInvitations(db, { invitedByAdminId: adminId })

  const actor = actorResult.rows[0] as
    | { id: string; username: string; email: string | null }
    | undefined
  const deleted = deletedResult.rows[0] as {
    id: string
    username: string
    email: string | null
  }

  const auditResult = await db.query(
    `INSERT INTO control_admin_deletion_audit (
         actor_admin_id,
         actor_username,
         actor_email,
         target_admin_id,
         target_username,
         target_email
       )
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING id::text AS id`,
    [
      actor?.id ?? actorAdminId,
      actor?.username ?? null,
      actor?.email ?? null,
      deleted.id,
      deleted.username,
      deleted.email,
    ]
  )

  const audit = auditResult.rows[0] as { id: string } | undefined
  if (!audit?.id) throw new Error('control admin deletion audit insert did not return an id')
  const requestContext = currentAdministrativeRequestContext()
  await administrativeEvents.appendInTransaction(
    db,
    CONTROL_API_LOCAL_ADMINISTRATIVE_PRINCIPAL_V1,
    {
      action: 'control_admin_deleted',
      outcome: 'committed',
      operatorSub: actor?.id ?? actorAdminId,
      operatorUserId: actor?.id ?? actorAdminId,
      operationId: audit.id,
      relatedRunId: null,
      requestId:
        requestContext?.operatorSub === (actor?.id ?? actorAdminId)
          ? requestContext.requestId
          : null,
      targetType: 'control_admin',
      targetRef: `control_admin:${deleted.id}`,
      environment: canonicalTracingEnvironment(),
      tenantId: null,
      teamId: null,
      namespace: null,
      sourceAuditRef: `control_admin_deletion_audit:${audit.id}`,
      identityIssuer: config.adminJwtIssuer,
      resourceAud: config.adminJwtAudience,
      effectiveScopes: [],
      authorizationDecision: 'allow',
      decisionActorSub: actor?.id ?? actorAdminId,
      targetIdentityIssuer: config.adminJwtIssuer,
      targetHumanSub: deleted.id,
      targetUserId: null,
    },
    {
      kind: 'service_action',
      sourceEventId: `control_admin_deletion_audit:${audit.id}`,
      occurredAt: new Date().toISOString(),
      // A hand-over records why the new admin appears as the deleting actor.
      reasonCode:
        input.reason === CONTROL_ADMIN_REPLACED_REASON
          ? CONTROL_ADMIN_REPLACED_REASON
          : 'control_admin_access_revoked',
      ...(input.sourceStatusRef ? { sourceStatusRef: input.sourceStatusRef } : {}),
      payload: {
        resource_class: 'control_admin_access',
        status: 'revoked',
        target_label: deleted.username,
      },
    }
  )

  return { deleted: true as const }
}

export async function listControlAdmins(): Promise<{
  admins: ControlAdminListItem[]
  invitations: ControlAdminInvitationRecord[]
}> {
  const [adminsResult, setupInvitationsResult, invitationsResult] = await Promise.all([
    pool.query(
      `SELECT a.id,
              a.username,
              a.email,
              u.id AS member_id,
              gfs_link.user_id AS gfs_operator_user_id,
              gfs_link.control_admin_id AS gfs_operator_admin_id,
              gfs_link.source AS gfs_operator_source,
              gfs_link.created_at AS gfs_operator_link_created_at,
              gfs_link.state AS gfs_operator_link_state,
              gfs_link.generation AS gfs_operator_link_generation,
              gfs_link.row_version AS gfs_operator_link_row_version,
              gfs_link.revocation_reason AS gfs_operator_link_revocation_reason,
              a.status,
              a.last_login_at,
              a.created_at
         FROM control_admin_users a
    LEFT JOIN users u ON lower(u.email) = lower(a.email)
    LEFT JOIN LATERAL (
      SELECT user_id, control_admin_id, source, created_at, state, generation,
             row_version, revocation_reason
        FROM gfs_desktop_operator_links
       WHERE control_admin_id = a.id
       ORDER BY generation DESC NULLS LAST, created_at DESC
       LIMIT 1
    ) gfs_link ON TRUE
        ORDER BY a.created_at ASC`
    ),
    pool.query(
      `SELECT id, email, expires_at, created_at, accepted_at,
              invited_by_admin_id, replace_inviter
         FROM control_admin_invitations
        WHERE status = 'opened'
          AND expires_at > NOW()
        ORDER BY accepted_at DESC, created_at DESC`
    ),
    pool.query(
      `SELECT ${INVITATION_COLUMNS}
         FROM control_admin_invitations
        WHERE status = 'pending'
        ORDER BY created_at DESC`
    ),
  ])

  return {
    admins: [
      ...adminsResult.rows.map(row => {
        const record = row as {
          id: string
          username: string
          email: string | null
          member_id: string | null
          gfs_operator_user_id: string | null
          gfs_operator_admin_id: string | null
          gfs_operator_source: 'initial_setup' | null
          gfs_operator_link_created_at: Date | null
          gfs_operator_link_state: 'active' | 'revoked' | null
          gfs_operator_link_generation: number | null
          gfs_operator_link_row_version: number | null
          gfs_operator_link_revocation_reason: string | null
          status: 'active' | 'disabled'
          last_login_at: Date | null
          created_at: Date
        }
        return {
          id: record.id,
          username: record.username,
          email: record.email || null,
          memberId: record.member_id || null,
          gfsOperatorLink: record.gfs_operator_user_id
            ? {
                desktopUserId: record.gfs_operator_user_id,
                controlAdminId: record.gfs_operator_admin_id || 'unknown',
                source: (record.gfs_operator_source === 'initial_setup'
                  ? 'initial_setup'
                  : 'unknown') as 'initial_setup' | 'unknown',
                createdAt: record.gfs_operator_link_created_at
                  ? record.gfs_operator_link_created_at.toISOString()
                  : null,
                status: (record.gfs_operator_link_state === 'revoked'
                  ? 'revoked'
                  : record.gfs_operator_source === 'initial_setup' &&
                      record.gfs_operator_link_created_at
                    ? record.status === 'active'
                      ? 'active'
                      : 'inactive_admin'
                    : 'error') as 'active' | 'inactive_admin' | 'revoked' | 'error',
                generation:
                  record.gfs_operator_link_generation === null
                    ? null
                    : Number(record.gfs_operator_link_generation),
                rowVersion:
                  record.gfs_operator_link_row_version === null
                    ? null
                    : Number(record.gfs_operator_link_row_version),
                revocationReason: record.gfs_operator_link_revocation_reason ?? null,
              }
            : null,
          gfsOperatorLinkStatus: (record.gfs_operator_user_id
            ? record.gfs_operator_link_state === 'revoked'
              ? 'revoked'
              : record.gfs_operator_source === 'initial_setup' &&
                  record.gfs_operator_link_created_at
                ? record.status === 'active'
                  ? 'active'
                  : 'inactive_admin'
                : 'error'
            : 'none') as 'none' | 'active' | 'inactive_admin' | 'revoked' | 'error',
          status: record.status,
          lastLoginAt: record.last_login_at ? record.last_login_at.toISOString() : null,
          createdAt: record.created_at.toISOString(),
        }
      }),
      ...setupInvitationsResult.rows.map(row => {
        const record = row as {
          id: string
          email: string
          expires_at: Date
          created_at: Date
          accepted_at: Date | null
          invited_by_admin_id: string | null
          replace_inviter: boolean | null
        }
        return {
          id: `invitation:${record.id}`,
          username: 'Pending setup',
          email: record.email,
          memberId: null,
          status: 'pending_password' as const,
          passwordPending: true,
          invitationId: record.id,
          replaceInviter: record.replace_inviter === true,
          invitedByAdminId: record.invited_by_admin_id ?? null,
          lastLoginAt: null,
          createdAt: record.accepted_at
            ? record.accepted_at.toISOString()
            : record.created_at.toISOString(),
        }
      }),
    ],
    invitations: invitationsResult.rows.map(row => mapInvitationRow(row as never)),
  }
}

export async function createControlAdminInvitation(
  email: string,
  invitedByAdminId: string,
  options: {
    replaceInviter?: boolean
    desktopAccess?: { teamAssignments: readonly InvitationTeamAssignment[] }
  } = {}
): Promise<
  | ControlAdminInvitationRecord
  | {
      invitation: ControlAdminInvitationRecord
      desktopInvitation: ReturnType<typeof invitationForTeamsResponse>
    }
  | { error: 'duplicate_email' | 'inviter_not_active' }
> {
  type CreationResult =
    | { error: 'duplicate_email' | 'inviter_not_active' }
    | { invitation: ControlAdminInvitationRecord }
    | {
        invitation: ControlAdminInvitationRecord
        desktopInvitation: ReturnType<typeof invitationForTeamsResponse>
      }

  const normalizedEmail = normalizeEmail(email)
  const replaceInviter = options.replaceInviter === true
  // Revoke expired active invitations first so the INSERT guard can treat any remaining
  // pending/opened invitation as a real blocker for the partial unique index.
  await pool.query(
    `UPDATE control_admin_invitations
        SET status = 'revoked'
      WHERE lower(email) = lower($1)
        AND status IN ('pending', 'opened')
        AND expires_at <= NOW()`,
    [normalizedEmail]
  )

  let created: CreationResult
  try {
    created = await withTransaction<CreationResult>(async db => {
      const inviter = await db.query(
        `SELECT id
           FROM control_admin_users
          WHERE id = $1::uuid
            AND status = 'active'
          FOR UPDATE`,
        [invitedByAdminId]
      )
      if ((inviter.rowCount ?? 0) === 0) return { error: 'inviter_not_active' as const }

      const result = await db.query(
        `INSERT INTO control_admin_invitations(email, invited_by_admin_id, replace_inviter, expires_at)
         SELECT $1, $2::uuid, $3::boolean,
                NOW() + ($4::int * INTERVAL '1 hour')
          WHERE NOT EXISTS (
            SELECT 1 FROM control_admin_users WHERE lower(email) = lower($1)
          )
            AND NOT EXISTS (
              SELECT 1 FROM control_admin_invitations
               WHERE lower(email) = lower($1)
                 AND status IN ('pending', 'opened')
            )
         RETURNING ${INVITATION_COLUMNS}`,
        [normalizedEmail, invitedByAdminId, replaceInviter, INVITATION_TTL_HOURS]
      )
      if ((result.rowCount ?? 0) === 0) return { error: 'duplicate_email' as const }

      const invitation = mapInvitationRow(result.rows[0] as never)
      if (!options.desktopAccess) return { invitation }

      const desktopInvitation = await insertInvitationForTeams(db, {
        inviteeName: normalizedEmail,
        email: normalizedEmail,
        teamAssignments: options.desktopAccess.teamAssignments,
        fallbackRole: 'member',
        purpose: 'admin_desktop_access',
        status: 'pending',
      })
      return {
        invitation,
        desktopInvitation: invitationForTeamsResponse(
          desktopInvitation.invitation,
          desktopInvitation.teams
        ),
      }
    })
  } catch (error) {
    if (
      error &&
      typeof error === 'object' &&
      (error as { code?: unknown }).code === '23505' &&
      String((error as { constraint?: unknown }).constraint || '').includes(
        'control_admin_invitations'
      )
    ) {
      return { error: 'duplicate_email' as const }
    }
    throw error
  }

  if ('error' in created) return created
  if ('desktopInvitation' in created) return created
  return created.invitation
}

export async function revokeControlAdminInvitation(invitationId: string): Promise<void> {
  await revokeControlAdminInvitations(pool, { invitationId })
}

async function revokeControlAdminInvitations(
  db: DbClient,
  selector: { invitationId: string } | { invitedByAdminId: string }
): Promise<void> {
  // The admin invitation and its paired silent Desktop invitation share the
  // email and are revoked in one statement, so no paired Desktop access remains.
  const predicate =
    'invitedByAdminId' in selector ? 'invited_by_admin_id = $1::uuid' : 'id = $1::uuid'
  const id = 'invitedByAdminId' in selector ? selector.invitedByAdminId : selector.invitationId

  await db.query(
    `WITH revoked AS (
       UPDATE control_admin_invitations
          SET status = 'revoked'
        WHERE ${predicate}
          AND status IN ('pending', 'opened')
        RETURNING email
     )
     UPDATE invitations
        SET status = 'revoked'
      WHERE purpose = 'admin_desktop_access'
        AND status = 'pending'
        AND lower(email) IN (SELECT lower(email) FROM revoked)`,
    [id]
  )
}

export async function getPendingControlAdminInvitation(
  db: DbClient,
  invitationId: string,
  email: string
): Promise<ControlAdminInvitationRecord | null> {
  const result = await db.query(
    `SELECT ${INVITATION_COLUMNS}
       FROM control_admin_invitations
      WHERE id = $1
        AND lower(email) = lower($2)
        AND (
          status = 'pending'
          OR status = 'opened'
        )
        AND expires_at > NOW()
      LIMIT 1`,
    [invitationId, normalizeEmail(email)]
  )
  if ((result.rowCount ?? 0) === 0) return null
  return mapInvitationRow(result.rows[0] as never)
}

export async function markControlAdminInvitationOpened(invitationId: string): Promise<void> {
  await pool.query(
    `UPDATE control_admin_invitations
        SET status = 'opened',
            accepted_at = COALESCE(accepted_at, NOW())
      WHERE id = $1
        AND status = 'pending'
        AND accepted_admin_id IS NULL`,
    [invitationId]
  )
}

export async function getPendingControlAdminEmailChangeRequest(
  db: DbClient,
  requestId: string,
  email: string
): Promise<ControlAdminEmailChangeRequestRecord | null> {
  const result = await db.query(
    `SELECT id, admin_id, email, status, expires_at, created_at, confirmed_at
       FROM control_admin_email_change_requests
      WHERE id = $1
        AND lower(email) = lower($2)
        AND status = 'pending'
        AND expires_at > NOW()
      LIMIT 1`,
    [requestId, normalizeEmail(email)]
  )
  if ((result.rowCount ?? 0) === 0) return null
  return mapEmailChangeRequestRow(result.rows[0] as never)
}

export async function completeControlAdminInvitation(input: {
  email: string
  invitationId: string
  username: string
  passwordHash: string
}): Promise<
  | AdminUserRecord
  | {
      error: 'duplicate_email' | 'duplicate_username' | 'not_found' | ReplaceInviterRefusal
    }
> {
  const email = normalizeEmail(input.email)
  const username = input.username.trim()
  return withTransaction(async db => {
    const invitation = await getPendingControlAdminInvitation(db, input.invitationId, email)
    if (!invitation) return { error: 'not_found' as const }

    // Retirement locks the inviter before revoking their pending invitations.
    // Taking that same lock before the invitation serializes redemption against
    // retirement and keeps the lock order consistent for replacement handovers.
    if (invitation.invitedByAdminId) {
      const inviterResult = await db.query(
        `SELECT status FROM control_admin_users WHERE id = $1::uuid FOR UPDATE`,
        [invitation.invitedByAdminId]
      )
      const inviterStatus = (inviterResult.rows[0] as { status?: string } | undefined)?.status
      if (inviterStatus !== 'active') {
        await revokeControlAdminInvitations(db, { invitationId: invitation.id })
        return { error: 'not_found' as const }
      }
    }

    // Lock and re-check before creating anything: getPendingControlAdminInvitation
    // does not lock, and the accept UPDATE below has no status guard, so a revoke
    // that commits meanwhile (DELETE route, MCC resend/cancel, or a replace-inviter
    // acceptance revoking this inviter's other invitations) must win here.
    const lockedInvitation = await db.query(
      `SELECT id
         FROM control_admin_invitations
        WHERE id = $1
          AND status IN ('pending', 'opened')
          AND expires_at > NOW()
        FOR UPDATE`,
      [invitation.id]
    )
    if ((lockedInvitation.rowCount ?? 0) === 0) return { error: 'not_found' as const }

    const conflict = await db.query(
      `SELECT
         EXISTS(SELECT 1 FROM control_admin_users WHERE lower(email) = lower($1)) AS email_exists,
         EXISTS(SELECT 1 FROM control_admin_users WHERE username = $2) AS username_exists`,
      [email, username]
    )
    const row = conflict.rows[0] as { email_exists: boolean; username_exists: boolean }
    if (row.email_exists) return { error: 'duplicate_email' as const }
    if (row.username_exists) return { error: 'duplicate_username' as const }

    const inserted = await db.query(
      `INSERT INTO control_admin_users(email, username, password_hash, role, status, session_version)
       VALUES($1, $2, $3, 'admin', 'active', 1)
       RETURNING id, username, email, password_hash, session_version, role, status, failed_attempts, locked_until`,
      [email, username, input.passwordHash]
    )
    const admin = mapAdminRow(inserted.rows[0] as never)

    // 0118 rejects this update unless the current pod opts in. An N-1 pod has
    // no such opt-in, so it cannot accept a hand-over as a plain invitation.
    if (invitation.replaceInviter) {
      await db.query(`SELECT set_config('evenfire.replace_inviter_handover', 'on', true)`)
    }

    await db.query(
      `UPDATE control_admin_invitations
          SET status = 'accepted',
              accepted_admin_id = $2,
              accepted_at = NOW()
        WHERE id = $1`,
      [invitation.id, admin.id]
    )

    if (invitation.replaceInviter && invitation.invitedByAdminId) {
      const refusal = await replaceInviterInTransaction(db, {
        invitationId: invitation.id,
        inviterAdminId: invitation.invitedByAdminId,
        newAdmin: admin,
      })
      if (refusal) throw new ReplaceInviterRefused(refusal)
    }

    return admin
  }).catch((error: unknown) => {
    // Rolls back the new admin and the accepted invitation; nothing is retired.
    if (error instanceof ReplaceInviterRefused) return { error: error.reason }
    throw error
  })
}

export type ReplaceInviterRefusal = 'replace_inviter_receiver_missing'

class ReplaceInviterRefused extends Error {
  constructor(readonly reason: ReplaceInviterRefusal) {
    super(reason)
  }
}

export const CONTROL_ADMIN_REPLACED_REASON = 'control_admin_replaced'

// admin > inviter > member; used so a copied role never downgrades a target.
const TEAM_ROLE_RANK = (column: string) =>
  `(CASE ${column} WHEN 'admin' THEN 3 WHEN 'inviter' THEN 2 ELSE 1 END)`

/**
 * Hand the inviter's access to the invitee and retire the inviter, inside the
 * transaction that created the invitee's control admin.
 *
 * An inviter that is missing or not active is left as it is. That is not an
 * empty hand-over: deleteControlAdmin disables the control admin and can leave
 * the desktop user, its team memberships, and its approval accounts active.
 * Skipping avoids retiring that desktop identity again and does not transfer
 * its roles. The skip is logged with the invitation id and the inviter id.
 */
async function replaceInviterInTransaction(
  db: DbClient,
  input: { invitationId: string; inviterAdminId: string; newAdmin: AdminUserRecord }
): Promise<ReplaceInviterRefusal | null> {
  const inviterResult = await db.query(
    `SELECT id::text AS id, email, status
       FROM control_admin_users
      WHERE id = $1
      FOR UPDATE`,
    [input.inviterAdminId]
  )
  const inviter = inviterResult.rows[0] as
    | { id: string; email: string | null; status: 'active' | 'disabled' }
    | undefined
  if (!inviter || inviter.status !== 'active') {
    rootLogger.warn(
      {
        event: 'control_admin_replace_inviter_skipped',
        invitationId: input.invitationId,
        inviterAdminId: input.inviterAdminId,
        inviterStatus: inviter?.status ?? 'missing',
      },
      'replace-inviter hand-over skipped because the inviter is not active'
    )
    return null
  }

  // The most recent operator link names this admin's desktop user, revoked or
  // not. An admin email change never updates users.email, and a revoked link
  // must not fall through to that email. Email is only the mapping for an
  // invited admin, who has no link history, and it must not select a user
  // linked to a different admin.
  const linkHistory = await db.query(
    `SELECT u.id::text AS id, u.email, u.lifecycle_state
       FROM gfs_desktop_operator_links link
       JOIN users u ON u.id = link.user_id
      WHERE link.control_admin_id = $1::uuid
      ORDER BY link.generation DESC, link.created_at DESC
      LIMIT 1
      FOR UPDATE OF u`,
    [inviter.id]
  )
  const linked = linkHistory.rows[0] as
    | { id: string; email: string | null; lifecycle_state: string }
    | undefined
  let inviterDesktop: { id: string; email: string | null } | undefined
  if (linked?.lifecycle_state === 'active') {
    inviterDesktop = linked
  } else if (!linked && inviter.email) {
    const emailMatch = await db.query(
      `SELECT u.id::text AS id, u.email
         FROM users u
        WHERE lower(u.email) = lower($1)
          AND u.lifecycle_state = 'active'
          AND NOT EXISTS (
            SELECT 1
              FROM gfs_desktop_operator_links l
             WHERE l.user_id = u.id
               AND l.control_admin_id <> $2::uuid
          )
        ORDER BY u.id
        LIMIT 1
        FOR UPDATE OF u`,
      [inviter.email, inviter.id]
    )
    inviterDesktop = emailMatch.rows[0] as { id: string; email: string | null } | undefined
  }
  const inviterDesktopUserId = inviterDesktop?.id ?? null
  const inviterDesktopEmail = inviterDesktop?.email ?? null
  // completeControlAdminInvitation always inserts the normalized invitation email.
  const newAdminEmail = input.newAdmin.email ?? ''

  if (inviterDesktopUserId) {
    // The invitee is the inviter's own desktop user. Copying roles onto that
    // row and then retiring it drops the teams on the only person who held them.
    if (
      inviterDesktopEmail &&
      newAdminEmail &&
      inviterDesktopEmail.toLowerCase() === newAdminEmail.toLowerCase()
    ) {
      return 'replace_inviter_receiver_missing'
    }

    // Retiring the inviter must not drop team access nobody can receive. A
    // non-active user holding the invitee email cannot accept the desktop
    // invitation, and the inviter's own desktop user is not a receiver.
    const receiver = await db.query(
      `SELECT
         EXISTS(SELECT 1 FROM team_members WHERE user_id = $1::uuid AND status = 'active') AS has_roles,
         (
           NOT EXISTS (
             SELECT 1 FROM users
              WHERE lower(email) = lower($2)
                AND lifecycle_state <> 'active'
           )
           AND (
             EXISTS (
               SELECT 1 FROM users
                WHERE lower(email) = lower($2)
                  AND lifecycle_state = 'active'
                  AND id <> $1::uuid
             )
             OR EXISTS (
               SELECT 1 FROM invitations
                WHERE lower(email) = lower($2)
                  AND purpose = 'admin_desktop_access'
                  AND status = 'pending'
                  AND expires_at > NOW()
             )
           )
         ) AS has_receiver`,
      [inviterDesktopUserId, newAdminEmail]
    )
    const { has_roles: hasRoles, has_receiver: hasReceiver } = receiver.rows[0] as {
      has_roles: boolean
      has_receiver: boolean
    }
    if (hasRoles && !hasReceiver) return 'replace_inviter_receiver_missing'

    // (1a) The invitee already has a desktop user: raise its memberships.
    await db.query(
      `INSERT INTO team_members(team_id, user_id, role, status)
       SELECT source.team_id, target.id, source.role, 'active'
         FROM team_members source
         JOIN users target
           ON lower(target.email) = lower($2)
          AND target.lifecycle_state = 'active'
          AND target.id <> $1::uuid
        WHERE source.user_id = $1::uuid
          AND source.status = 'active'
       ON CONFLICT (team_id, user_id) DO UPDATE
          SET role = CASE
                       WHEN team_members.status <> 'active' THEN EXCLUDED.role
                       WHEN ${TEAM_ROLE_RANK('EXCLUDED.role')} > ${TEAM_ROLE_RANK('team_members.role')}
                         THEN EXCLUDED.role
                       ELSE team_members.role
                     END,
              status = 'active',
              updated_at = NOW()`,
      [inviterDesktopUserId, newAdminEmail]
    )
    // (1b) The desktop invitation is accepted after this transaction commits
    // (POST /admin/auth/control-admin-invitations/complete) and upserts
    // role = EXCLUDED.role, so the copied role has to already be on the
    // invitation or 1a would be downgraded.
    // Each copied role is the higher of the inviter's and any role the invitee's
    // desktop user already holds, which acceptance would otherwise overwrite.
    // Materialize a legacy single-team invitation first so adding rows cannot
    // hide its fallback team (loadInvitationTeams, membership.ts:242-267).
    await db.query(
      `INSERT INTO invitation_teams(invitation_id, team_id, role)
       SELECT i.id, i.team_id, i.role
         FROM invitations i
        WHERE lower(i.email) = lower($1)
          AND i.purpose = 'admin_desktop_access'
          AND i.status = 'pending'
          AND i.team_id IS NOT NULL
       ON CONFLICT (invitation_id, team_id) DO NOTHING`,
      [newAdminEmail]
    )
    await db.query(
      `INSERT INTO invitation_teams(invitation_id, team_id, role)
       SELECT i.id, source.team_id,
              CASE
                WHEN held.role IS NOT NULL
                 AND ${TEAM_ROLE_RANK('held.role')} > ${TEAM_ROLE_RANK('source.role')}
                  THEN held.role
                ELSE source.role
              END
         FROM invitations i
         JOIN team_members source
           ON source.user_id = $1::uuid
          AND source.status = 'active'
         LEFT JOIN users target
           ON lower(target.email) = lower(i.email)
          AND target.lifecycle_state = 'active'
         LEFT JOIN team_members held
           ON held.user_id = target.id
          AND held.team_id = source.team_id
          AND held.status = 'active'
        WHERE lower(i.email) = lower($2)
          AND i.purpose = 'admin_desktop_access'
          AND i.status = 'pending'
       ON CONFLICT (invitation_id, team_id) DO UPDATE
          SET role = CASE
                       WHEN ${TEAM_ROLE_RANK('EXCLUDED.role')} > ${TEAM_ROLE_RANK('invitation_teams.role')}
                         THEN EXCLUDED.role
                       ELSE invitation_teams.role
                     END`,
      [inviterDesktopUserId, newAdminEmail]
    )

    // (2) Retire, never hard-delete, the inviter's desktop identity.
    // retireDesktopUser disables approval medium accounts on the tombstone
    // path as well as the hard-delete path.
    await retireDesktopUserInTransaction(db, {
      actor: { kind: 'control_admin', controlAdminId: input.newAdmin.id },
      userId: inviterDesktopUserId,
      reason: CONTROL_ADMIN_REPLACED_REASON,
      idempotencyKey: `${CONTROL_ADMIN_REPLACED_REASON}:${input.invitationId}`,
      requestId: null,
      retainWithoutLinkHistory: true,
    })
  }

  // (3) Retire the inviter exactly as deleteControlAdmin does.
  const retired = await retireControlAdminInTransaction(db, {
    actorAdminId: input.newAdmin.id,
    adminId: inviter.id,
    reason: CONTROL_ADMIN_REPLACED_REASON,
    sourceStatusRef: `control_admin_invitation:${input.invitationId}`,
  })
  if ('error' in retired) {
    throw new Error('inviting control admin changed during replace-inviter acceptance')
  }

  // retireControlAdminInTransaction revokes the inviter's other outstanding
  // Control Admin invitations and their paired Desktop invitations atomically.
  return null
}

export async function completeControlAdminEmailChangeRequest(input: {
  requestId: string
  email: string
}): Promise<
  | AdminUserRecord
  | { alreadyConfirmed: true; admin: AdminUserRecord }
  | { error: 'duplicate_email' | 'not_found' }
> {
  const email = normalizeEmail(input.email)

  return withTransaction(async db => {
    const request = await getPendingControlAdminEmailChangeRequest(db, input.requestId, email)
    if (!request) {
      const confirmed = await db.query(
        `SELECT u.id,
                u.username,
                u.email,
                u.password_hash,
                u.session_version,
                u.role,
                u.status,
                u.failed_attempts,
                u.locked_until
           FROM control_admin_email_change_requests r
           JOIN control_admin_users u ON u.id = r.admin_id
          WHERE r.id = $1
            AND lower(r.email) = lower($2)
            AND r.status = 'confirmed'
            AND lower(u.email) = lower($2)
          LIMIT 1`,
        [input.requestId, email]
      )
      if ((confirmed.rowCount ?? 0) > 0) {
        return {
          alreadyConfirmed: true as const,
          admin: mapAdminRow(confirmed.rows[0] as never),
        }
      }
      return { error: 'not_found' as const }
    }

    const conflict = await db.query(
      `SELECT 1
         FROM control_admin_users
        WHERE id <> $1
          AND lower(email) = lower($2)
        LIMIT 1`,
      [request.adminId, email]
    )
    if ((conflict.rowCount ?? 0) > 0) return { error: 'duplicate_email' as const }

    const updated = await db.query(
      `UPDATE control_admin_users
          SET email = $2,
              updated_at = NOW()
        WHERE id = $1
        RETURNING id, username, email, password_hash, session_version, role, status, failed_attempts, locked_until`,
      [request.adminId, email]
    )
    if ((updated.rowCount ?? 0) === 0) return { error: 'not_found' as const }

    await db.query(
      `UPDATE control_admin_email_change_requests
          SET status = 'confirmed',
              confirmed_at = NOW()
        WHERE id = $1`,
      [request.id]
    )

    return mapAdminRow(updated.rows[0] as never)
  })
}