Skip to content

Never stop coding. Free MIT AI gateway: one endpoint, 375 providers (150+ free), 1,400+ models Kimi, Claude, GPT, Gemini, GLM, DeepSeek, MiniMax. Works with Claude Code, Codex, Cursor, OpenCode, Cline & Copilot. Quota-aware auto-fallback, RTK+Caveman compression saves 15-95% tokens, MCP/A2A, Desktop/PWA. Built by 900+ contributors

47.3 KB
Raw Blame
/**
 * db/providers.js — Provider connections and nodes CRUD.
 */

import { v4 as uuidv4 } from "uuid";

import { isCommonChatGptWebRetiredProviderId } from "@/shared/constants/chatgptWebRetirement";
import { getDbInstance, rowToCamel, cleanNulls } from "./core";
import { backupDbFile } from "./backup";
import { encryptConnectionFields, decryptConnectionFields } from "./encryption";
import { createLazyRowProxy } from "./providers/lazyConnectionView";
import { invalidateDbCache, getCachedRawProviderConnections } from "./readCache";
import { invalidateConnectionUpdate, type UpdateOpts } from "./readCache";
import { reorderConnections } from "./providers/deletion";
import { normalizeConnectionProviderSpecificData } from "./providers/providerSpecificData";
import { applyAutomaticDisableMarker } from "@/lib/providers/operatorDisable";
import { isRetestAutoDisabledEnabled } from "@/lib/credentialHealth/retestAutoDisabledFlag";
import { ensureProviderSpecificDataSecretsEncrypted } from "./providers/migrations";
import { bumpProxyConfigGeneration, getSettings } from "./settings";
import {
  getStoredManagementPassword,
  isBcryptHash,
  verifyManagementPassword,
} from "@/lib/auth/managementPassword";
import {
  webSessionCredentialKey,
  parseProviderSpecificData,
  isMatchingOauthIdentity,
  isWebCookieProviderId,
} from "./webSessionDedup";
import { LOCAL_PROVIDERS } from "@/shared/constants/providers";
import { pickCodexConnectionForUser } from "@/lib/oauth/utils/codexConnectionSelection";
import { isMicrosoftDesignerWebRetiredProviderId } from "@/shared/constants/designerWebRetirement";
import { reconcileCodexUsageHistory } from "./providers/usageIdentityReconciliation";
import { isRuntimeRetiredProviderId } from "@/shared/constants/providerRetirement";
import { applyCodexChildCooldownClearOnUpdate } from "./providers/codexAccountState";

import {
  withNullableMaxConcurrent,
  withNullableQuotaWindowThresholds,
  withNullableRateLimitOverrides,
  normalizeBooleanColumn,
  sanitizeRateLimitOverrides,
  serializeJsonField,
  toRecord,
  sanitizeQuotaWindowThresholds,
  toStringOrNull,
  toNumberOrZero,
} from "./providers/columns";

type JsonRecord = Record<string, unknown>;

const CONNECTION_CREDENTIAL_FIELDS = ["apiKey", "accessToken", "refreshToken", "idToken"] as const;

/** Thrown when a write would store the dashboard login password as a provider credential. */
export class ManagementPasswordAsCredentialError extends Error {
  readonly code = "MANAGEMENT_PASSWORD_AS_CREDENTIAL" as const;

  constructor() {
    super(
      "That value is the dashboard login password, not a provider API key. Storing it would " +
        "send it upstream on every request routed through this connection."
    );
    this.name = "ManagementPasswordAsCredentialError";
  }
}

/**
 * Refuse to store the dashboard login password as a connection API key.
 *
 * A browser that autofills the management password into the API-key field
 * produces a connection whose credential authenticates against nothing, and
 * every request routed through it comes back 401. Rejecting it in the form
 * would not be enough: the same autofill fires again while an operator is
 * repairing the connection by hand, so the refusal has to sit on the write
 * path that all of those forms funnel into.
 *
 * Only an actual match blocks the write. A settings row that cannot be read,
 * or a bcrypt call that throws, logs and allows -- a guard against one specific
 * operator mistake must not become a way to lock out every connection write.
 *
 * Deliberately narrower than CONNECTION_CREDENTIAL_FIELDS. The OAuth tokens
 * arrive from a provider's token endpoint, and the refresh path writes them
 * back through updateProviderConnection on every renewal, so checking them
 * would put a bcrypt round on a renewal path to defend a field no autofill
 * reaches. apiKey is the only credential an operator types into a form.
 */
async function assertApiKeyIsNotManagementPassword(apiKey: unknown): Promise<void> {
  if (typeof apiKey !== "string") return;
  const trimmed = apiKey.trim();
  if (!trimmed) return;

  try {
    const settings = (await getSettings()) as JsonRecord;
    const stored = getStoredManagementPassword(settings);
    // Only a stored bcrypt hash is comparable. A fresh install that has never
    // bootstrapped a password has nothing to collide with.
    if (!isBcryptHash(stored)) return;
    // Both forms of the value, because neither the login route nor the
    // set-password route trims: a paste carries whitespace the password does
    // not have, and a password is allowed to carry whitespace of its own. The
    // second comparison only runs when the first fails on a different string.
    const matches =
      (await verifyManagementPassword(trimmed, stored)) ||
      (trimmed !== apiKey && (await verifyManagementPassword(apiKey, stored)));
    if (!matches) return;
  } catch (err) {
    console.warn(
      "[Providers] could not check the credential against the dashboard password:",
      err instanceof Error ? err.message : String(err)
    );
    return;
  }

  throw new ManagementPasswordAsCredentialError();
}

interface StatementLike<TRow = unknown> {
  all: (...params: unknown[]) => TRow[];
  get: (...params: unknown[]) => TRow | undefined;
  run: (...params: unknown[]) => { changes?: number };
}

interface DbLike {
  prepare: <TRow = unknown>(sql: string) => StatementLike<TRow>;
  transaction: <T>(fn: () => T) => () => T;
}

// Real column set for provider_connections (must match the CREATE TABLE in
// core.ts's SCHEMA_SQL). getProviderConnections()'s optional `columns`
// projection is interpolated directly into the SELECT clause, so every
// requested name must be validated against this allowlist before use —
// there is no current caller that passes untrusted input, but the
// projection API itself must never accept an arbitrary string.
export const PROVIDER_CONNECTIONS_COLUMNS = new Set([
  "id",
  "provider",
  "auth_type",
  "name",
  "email",
  "priority",
  "is_active",
  "access_token",
  "refresh_token",
  "expires_at",
  "token_expires_at",
  "scope",
  "project_id",
  "test_status",
  "error_code",
  "last_error",
  "last_error_at",
  "last_error_type",
  "last_error_source",
  "backoff_level",
  "rate_limited_until",
  "health_check_interval",
  "last_health_check_at",
  "last_tested",
  "api_key",
  "id_token",
  "provider_specific_data",
  "expires_in",
  "display_name",
  "global_priority",
  "default_model",
  "token_type",
  "consecutive_use_count",
  "rate_limit_protection",
  "last_used_at",
  "group",
  "max_concurrent",
  "proxy_enabled",
  "per_key_proxy_enabled",
  "quota_window_thresholds_json",
  "rate_limit_overrides_json",
  "created_at",
  "updated_at",
  "synced_models_at",
]);

// ──────────────── Provider Connections ────────────────

/**
 * Returns provider connections as lazy-decrypting proxies: encrypted
 * credential fields (apiKey, accessToken, refreshToken, idToken) are only
 * decrypted on first property access, not eagerly for every row. Column-
 * projected reads (`columns` passed) bypass the raw-row cache — the cache
 * key doesn't account for projection, so a projected read could otherwise
 * poison the cache for a subsequent full-row read of the same filter.
 *
 * When `limit`/`offset` are provided, the cache is also bypassed since
 * the cache key doesn't account for pagination.
 */
export async function getProviderConnections(
  filter: JsonRecord = {},
  limit?: number,
  offset?: number,
  columns?: string[]
) {
  const useCache = !columns?.length && limit === undefined && offset === undefined;
  const raw = useCache
    ? await getCachedRawProviderConnections(filter)
    : await getRawProviderConnections(filter, limit, offset, columns);
  return raw.map(createLazyRowProxy);
}

/**
 * Same as getProviderConnections but WITHOUT decryptConnectionFields.
 * Returns raw rows with encrypted credential fields intact — callers
 * that only need metadata (id, priority, backoffLevel, etc.) avoid
 * the O(n) AES-GCM decrypt cost on every cache fill.
 *
 * Used by the lazy-decryption path in auth selection (auth.ts) where
 * 10k+ connections are filtered in JS but only 1 needs its apiKey
 * decrypted.
 *
 * @param limit — Optional SQL LIMIT clause to cap rows returned
 * @param offset — Optional SQL OFFSET for pagination
 */
export async function getRawProviderConnections(
  filter: JsonRecord = {},
  limit?: number,
  offset?: number,
  columns?: string[]
) {
  const db = getDbInstance() as unknown as DbLike;
  ensureProviderSpecificDataSecretsEncrypted(db);
  let selectCols = "*";
  if (columns?.length) {
    const invalidColumns = columns.filter((col) => !PROVIDER_CONNECTIONS_COLUMNS.has(col));
    if (invalidColumns.length > 0) {
      throw new Error(
        `getProviderConnections: invalid column(s) requested: ${invalidColumns.join(", ")}`
      );
    }
    // "group" is a reserved SQL keyword — the schema declares it quoted, so
    // it must be re-quoted here too or the generated SELECT is a syntax error.
    selectCols = columns.map((col) => (col === "group" ? `"group"` : col)).join(", ");
  }
  let sql = `SELECT ${selectCols} FROM provider_connections`;
  const conditions: string[] = [];
  const params: Record<string, unknown> = {};

  if (filter.provider) {
    conditions.push("provider = @provider");
    params.provider = filter.provider;
  }
  if (filter.isActive !== undefined) {
    conditions.push("is_active = @isActive");
    params.isActive = filter.isActive ? 1 : 0;
  }
  if (filter.authType) {
    conditions.push("auth_type = @authType");
    params.authType = filter.authType;
  }

  if (conditions.length > 0) {
    sql += " WHERE " + conditions.join(" AND ");
  }
  sql += " ORDER BY priority ASC, updated_at DESC";
  if (limit !== undefined) {
    sql += " LIMIT @limit OFFSET @offset";
    params.limit = limit;
    params.offset = offset ?? 0;
  }

  const rows = db.prepare(sql).all(params);
  return rows.map((r) => {
    const camelRow = rowToCamel(r);
    return withNullableRateLimitOverrides(
      withNullableQuotaWindowThresholds(
        withNullableMaxConcurrent(cleanNulls(camelRow), camelRow),
        camelRow
      ),
      camelRow
    );
  });
}

export function getProviderConnectionsCount(filter: JsonRecord = {}): number {
  const db = getDbInstance() as unknown as DbLike;
  let sql = "SELECT count(*) as cnt FROM provider_connections";
  const conditions: string[] = [];
  const params: Record<string, unknown> = {};

  if (filter.provider) {
    conditions.push("provider = @provider");
    params.provider = filter.provider;
  }
  if (filter.isActive !== undefined) {
    conditions.push("is_active = @isActive");
    params.isActive = filter.isActive ? 1 : 0;
  }
  if (filter.authType) {
    conditions.push("auth_type = @authType");
    params.authType = filter.authType;
  }

  if (conditions.length > 0) {
    sql += " WHERE " + conditions.join(" AND ");
  }

  const row = db.prepare(sql).get(params) as { cnt: number };
  return row.cnt;
}

export async function getProviderConnectionById(id: string) {
  const db = getDbInstance() as unknown as DbLike;
  ensureProviderSpecificDataSecretsEncrypted(db);
  const row = db.prepare("SELECT * FROM provider_connections WHERE id = ?").get(id);
  if (!row) return null;

  const camelRow = rowToCamel(row);
  return decryptConnectionFields(
    withNullableRateLimitOverrides(
      withNullableQuotaWindowThresholds(
        withNullableMaxConcurrent(cleanNulls(camelRow), camelRow),
        camelRow
      ),
      camelRow
    )
  );
}

export interface ProviderConnectionDisplayMetadata {
  id: string;
  name: string | null;
  displayName: string | null;
  email: string | null;
}

/**
 * Reads only the non-credential fields needed by account display-name resolvers.
 *
 * This avoids decrypting provider credentials when a dashboard only needs labels.
 */
export function getProviderConnectionDisplayMetadata(
  connectionIds: readonly string[]
): ProviderConnectionDisplayMetadata[] {
  const ids = [...new Set(connectionIds.filter((id) => id.length > 0))];
  if (ids.length === 0) return [];

  const db = getDbInstance() as unknown as DbLike;
  const rows = db
    .prepare(
      `SELECT id, name, display_name, email FROM provider_connections
       WHERE id IN (${ids.map(() => "?").join(", ")})`
    )
    .all(...ids);

  return rows.map((row) => {
    const view = rowToCamel(row) as JsonRecord;
    return {
      id: toStringOrNull(view.id) || "",
      name: toStringOrNull(view.name),
      displayName: toStringOrNull(view.displayName),
      email: toStringOrNull(view.email),
    };
  });
}

// #3368 PR6 — dedup web-session cookie/token credentials on connection create.
// Re-importing the same session (e.g. via bulk web-session import) under a
// different or blank name must update the existing connection instead of
// inserting a duplicate, mirroring the apikey dedup (#3023). Extracted from
// createProviderConnection to keep that function below the complexity baseline.
// provider_specific_data is plaintext JSON, so the value is compared directly
// without decryption.
/**
 * #12173 — the API-key-value dedup (#3023) matches purely on `provider +
 * apiKey`, which is correct for hosted providers where the key alone is the
 * account identity. Local/self-hosted providers (LM Studio, Ollama, vLLM,
 * llama.cpp, ...) commonly ship an optional/cosmetic API key, so users
 * legitimately reuse the same placeholder value (e.g. "lm-studio") across two
 * physically distinct servers that are actually distinguished by base URL.
 * Gate the extra baseUrl check to this provider set only — hosted-provider
 * dedup must stay untouched.
 */
function isLocalProviderId(providerId: unknown): boolean {
  return (
    typeof providerId === "string" &&
    Object.prototype.hasOwnProperty.call(LOCAL_PROVIDERS, providerId)
  );
}

/** Trim + strip a trailing slash so cosmetic differences don't defeat the match. */
function normalizeBaseUrlForDedup(value: unknown): string {
  return typeof value === "string" ? value.trim().replace(/\/+$/, "") : "";
}

/**
 * Find the cookie connection an incoming import should overwrite.
 *
 * #15159 / B-01 — the credential is the identity; `name` is not. This used to try
 * the name FIRST ("name-based upsert for parity with the apikey path"), so
 * re-importing under an existing name returned that row and the caller's
 * `merged = { ...decryptedExisting, ...data }` wrote the new cookie over it.
 * Because `name` is a user-editable display label, two different accounts
 * legitimately share one — and the second import silently destroyed the first
 * account's stored session, unrecoverably.
 *
 * This is the same hazard the OAuth branch of this file already fixed
 * (:532-540): "two different IdPs ... can share the same email address; matching
 * on email alone would silently overwrite the other account's connection on the
 * second login." The cookie branch was never given the same treatment.
 *
 * The name lookup is now only a fallback for rows with no derivable credential
 * key — there the name is genuinely all there is to match on. Every other import
 * matches on its credential, and one with a different credential gets its own row.
 *
 * Trade-off, stated plainly: a *rotated* cookie under a stable name now creates a
 * second row instead of updating in place. That is the intended direction of the
 * error — a caller can delete a stale row, but cannot recover a session that was
 * already overwritten. #3368's actual goal (same cookie under a different name
 * dedupes) is unaffected: that was always the credential loop's job.
 */
/**
 * Cookie-session identity checks must use plaintext for both sides.
 * #16836 encrypts selected providerSpecificData cookies with randomized AES-GCM,
 * so matching a new plaintext cookie against the raw JSON column would always
 * miss the same session and create duplicate provider connections.
 * Preserve the existing fallback behavior for legacy unencrypted metadata.
 */
function storedCookieCredentialKey(raw: unknown): string | null {
  const parsed = parseProviderSpecificData(raw);
  if (!parsed) return null;
  return webSessionCredentialKey(
    decryptConnectionFields({ providerSpecificData: parsed }).providerSpecificData
  );
}

function findExistingCookieConnection(
  db: DbLike,
  provider: unknown,
  name: unknown,
  normalizedProviderSpecificData: unknown
): JsonRecord | null {
  // 1) Credential-value dedup — the authoritative identity for a web session.
  const newCredKey = webSessionCredentialKey(normalizedProviderSpecificData);
  if (newCredKey) {
    const cookieRows = db
      .prepare("SELECT * FROM provider_connections WHERE provider = ? AND auth_type = 'cookie'")
      .all(provider) as JsonRecord[];
    for (const row of cookieRows) {
      if (storedCookieCredentialKey(row.provider_specific_data) === newCredKey) return row;
    }
  }

  // 2) Name fallback, only when this import carries no credential to compare
  //    against AND the candidate row itself has none. Matching a credential-less
  //    import onto a row that *does* hold a credential would clobber it with
  //    nothing — the same data loss, reached from the other direction.
  if (name && !newCredKey) {
    const byName =
      (db
        .prepare(
          "SELECT * FROM provider_connections WHERE provider = ? AND auth_type = 'cookie' AND name = ?"
        )
        .get(provider, name) as JsonRecord | undefined) || null;
    if (byName && !storedCookieCredentialKey(byName.provider_specific_data)) {
      return byName;
    }
  }

  return null;
}

export async function createProviderConnection(data: JsonRecord) {
  await assertApiKeyIsNotManagementPassword(data.apiKey);
  const db = getDbInstance() as unknown as DbLike;
  const now = new Date().toISOString();
  const normalizedProviderSpecificData = normalizeConnectionProviderSpecificData(
    toStringOrNull(data.provider),
    data.providerSpecificData,
    data
  );

  let existing: JsonRecord | null = null;
  let promotedCodexIdentity = false;

  const providerSpecificData = toRecord(data.providerSpecificData);
  const workspaceId = toStringOrNull(providerSpecificData.workspaceId);
  const chatgptUserId = toStringOrNull(providerSpecificData.chatgptUserId);

  if (data.authType === "oauth" && data.provider === "codex" && chatgptUserId) {
    const strongSql = workspaceId
      ? "SELECT * FROM provider_connections WHERE provider = ? AND auth_type = 'oauth' AND json_extract(provider_specific_data, '$.workspaceId') = ? AND json_extract(provider_specific_data, '$.chatgptUserId') = ?"
      : "SELECT * FROM provider_connections WHERE provider = ? AND auth_type = 'oauth' AND (json_extract(provider_specific_data, '$.workspaceId') IS NULL OR json_extract(provider_specific_data, '$.workspaceId') = '') AND json_extract(provider_specific_data, '$.chatgptUserId') = ?";
    existing =
      ((workspaceId
        ? db.prepare(strongSql).get(data.provider, workspaceId, chatgptUserId)
        : db.prepare(strongSql).get(data.provider, chatgptUserId)) as JsonRecord | undefined) ||
      null;

    if (!existing && workspaceId) {
      const workspaceMatches = db
        .prepare(
          `SELECT * FROM provider_connections
           WHERE provider = ? AND auth_type = 'oauth'
             AND json_extract(provider_specific_data, '$.workspaceId') = ?
           ORDER BY created_at`
        )
        .all(data.provider, workspaceId) as JsonRecord[];
      existing = pickCodexConnectionForUser(
        workspaceMatches,
        chatgptUserId,
        toStringOrNull(data.email)
      );
      promotedCodexIdentity = existing !== null;
    }
  } else if (data.authType === "oauth" && data.email) {
    if (data.provider === "codex") {
      if (workspaceId) {
        existing =
          (db
            .prepare(
              `SELECT * FROM provider_connections
               WHERE provider = ? AND auth_type = 'oauth'
                 AND json_extract(provider_specific_data, '$.workspaceId') = ?
                 AND email = ?
               LIMIT 1`
            )
            .get(data.provider, workspaceId, data.email) as JsonRecord | undefined) || null;
      }
    } else {
      // For other providers (or Codex without workspaceId), match on email —
      // disambiguated by providerSpecificData.username and/or
      // providerSpecificData.profileArn when present on both sides. Two
      // different IdPs (or two distinct Kiro/AWS profiles authenticated via
      // the same email-carrying IdP) can share the same email address;
      // matching on email alone would silently overwrite the other
      // account's connection on the second login. Only fall back to the
      // bare email-only match when neither side carries a username/profileArn
      // (legacy rows created before this disambiguation existed).
      const incomingUsername = toStringOrNull(providerSpecificData.username);
      const incomingProfileArn = toStringOrNull(providerSpecificData.profileArn);
      // Claude: one identity reaches its personal workspace and every Team
      // organization with the same email and the same accountUUID, so
      // organizationUUID is what separates the accounts.
      const incomingOrganizationUuid = toStringOrNull(providerSpecificData.organizationUUID);
      const emailMatches = db
        .prepare(
          "SELECT * FROM provider_connections WHERE provider = ? AND auth_type = 'oauth' AND email = ?"
        )
        .all(data.provider, data.email) as JsonRecord[];
      existing =
        emailMatches.find((row) =>
          isMatchingOauthIdentity(
            row,
            incomingUsername,
            incomingProfileArn,
            incomingOrganizationUuid
          )
        ) || null;
    }
  } else if (data.authType === "apikey") {
    if (data.name && !isWebCookieProviderId(data.provider)) {
      existing =
        (db
          .prepare(
            "SELECT * FROM provider_connections WHERE provider = ? AND auth_type = 'apikey' AND name = ?"
          )
          .get(data.provider, data.name) as JsonRecord | undefined) || null;
    }
    // #3023 — dedup by API key value: re-adding the same key (under a different
    // or blank name) must update the existing connection, not insert a duplicate
    // row. Stored keys use non-deterministic AES-GCM, so ciphertext can't be
    // compared directly — decrypt each apikey row for this provider and match the
    // plaintext (trimmed) instead.
    const newApiKey = typeof data.apiKey === "string" ? data.apiKey.trim() : "";
    if (!existing && newApiKey) {
      const isLocal = isLocalProviderId(data.provider);
      const newBaseUrl = normalizeBaseUrlForDedup(providerSpecificData.baseUrl);
      const apiKeyRows = db
        .prepare("SELECT * FROM provider_connections WHERE provider = ? AND auth_type = 'apikey'")
        .all(data.provider) as JsonRecord[];
      for (const row of apiKeyRows) {
        const decrypted = decryptConnectionFields(toRecord(rowToCamel(row)));
        if (toStringOrNull(decrypted.apiKey)?.trim() !== newApiKey) continue;
        // #12173 — for local/self-hosted providers, a differing base URL means
        // this is a different physical server, not the same account; fall
        // through to inserting a new connection even though the apiKey matches.
        if (isLocal) {
          const existingBaseUrl = normalizeBaseUrlForDedup(
            parseProviderSpecificData(row.provider_specific_data)?.baseUrl
          );
          if (existingBaseUrl !== newBaseUrl) continue;
        }
        existing = row;
        break;
      }
    }
  } else if (data.authType === "cookie") {
    existing = findExistingCookieConnection(
      db,
      data.provider,
      data.name,
      normalizedProviderSpecificData
    );
  } else if (data.authType === "access_token") {
    // #1290 — bare access-token imports (e.g. a raw ChatGPT website access
    // token with no refresh token) are intentionally never deduped: every
    // import creates a new connection. Unlike oauth (workspace+email) or
    // apikey (key-value) imports, a bare access token has no refresh token
    // and no stable long-lived identity to safely dedup against — matching
    // on email alone here would risk silently overwriting an existing full
    // oauth connection for the same account.
  }

  if (existing) {
    const existingId = toStringOrNull(existing.id);
    if (!existingId) return null;
    const rawExisting = toRecord(rowToCamel(existing));
    const decryptedExisting = decryptConnectionFields({ ...rawExisting });
    const merged: JsonRecord = { ...decryptedExisting, ...data, updatedAt: now };
    merged.providerSpecificData = normalizeConnectionProviderSpecificData(
      toStringOrNull(merged.provider),
      merged.providerSpecificData,
      merged,
      decryptedExisting.providerSpecificData
    );
    const persistence: JsonRecord = { ...merged };
    for (const field of CONNECTION_CREDENTIAL_FIELDS) {
      if (!Object.hasOwn(data, field)) {
        persistence[field] = rawExisting[field];
      }
    }
    db.transaction(() => {
      if (promotedCodexIdentity) {
        reconcileCodexUsageHistory(db, {
          connectionId: existingId,
          existing,
          merged,
          matchedExistingCodexByWorkspace: true,
        });
      }
      _updateConnectionRow(db, existingId, encryptConnectionFields(persistence));
    })();
    backupDbFile("pre-write");
    invalidateDbCache("connections");
    const returnedConnection = withNullableRateLimitOverrides(
      withNullableQuotaWindowThresholds(
        withNullableMaxConcurrent(cleanNulls(merged), merged),
        merged
      ),
      merged
    );

    if (
      isMicrosoftDesignerWebRetiredProviderId(merged.provider) ||
      isRuntimeRetiredProviderId(merged.provider) ||
      isCommonChatGptWebRetiredProviderId(merged.provider)
    ) {
      invalidateDbCache("connections");
      return (await getProviderConnectionById(existingId)) ?? returnedConnection;
    }

    return returnedConnection;
  }

  // Generate name: prefer explicit name, then email, then a stable short-ID label.
  // Avoid sequential "Account N" — it reassigns when accounts are deleted/reordered.
  let connectionName = data.name || null;
  if (!connectionName && (data.authType === "oauth" || data.authType === "access_token")) {
    if (data.email) {
      connectionName = data.email as string;
    } else if (data.displayName) {
      connectionName = data.displayName as string;
    }
    // Otherwise leave null — UI will fall back to getAccountDisplayName() → "Account #<id>"
  }

  // Auto-increment priority
  let connectionPriority = data.priority;
  if (!connectionPriority) {
    const max = db
      .prepare("SELECT MAX(priority) as maxP FROM provider_connections WHERE provider = ?")
      .get(data.provider) as JsonRecord | undefined;
    const maxPriority = toNumberOrZero(toRecord(max).maxP);
    connectionPriority = maxPriority + 1;
  }

  const connection: Record<string, unknown> = {
    id: uuidv4(),
    provider: data.provider,
    authType: data.authType || "oauth",
    name: connectionName,
    priority: connectionPriority,
    isActive: data.isActive !== undefined ? data.isActive : true,
    createdAt: now,
    updatedAt: now,
    proxyEnabled: normalizeBooleanColumn(data.proxyEnabled, true),
    perKeyProxyEnabled: normalizeBooleanColumn(data.perKeyProxyEnabled, false),
    quotaVisible: normalizeBooleanColumn(data.quotaVisible, true),
  };

  // Optional fields
  const optionalFields = [
    "displayName",
    "email",
    "globalPriority",
    "defaultModel",
    "accessToken",
    "refreshToken",
    "expiresAt",
    // #5326's payload sets this and _insertConnectionRow binds it, but it was
    // missing from this allowlist — so every created row stored NULL however good
    // the payload was. The update path already carries it (`data.tokenExpiresAt`).
    "tokenExpiresAt",
    "tokenType",
    "scope",
    "idToken",
    "projectId",
    "apiKey",
    "testStatus",
    "lastTested",
    "lastError",
    "lastErrorAt",
    "lastErrorType",
    "lastErrorSource",
    "rateLimitedUntil",
    "expiresIn",
    "errorCode",
    "consecutiveUseCount",
    "rateLimitProtection",
    "group",
    "maxConcurrent",
    "proxyEnabled",
    "perKeyProxyEnabled",
    "quotaVisible",
    "quotaWindowThresholds",
    "rateLimitOverrides",
    "healthCheckInterval",
  ];
  for (const field of optionalFields) {
    if (data[field] !== undefined && data[field] !== null) {
      connection[field] = data[field];
    }
  }
  if (normalizedProviderSpecificData && Object.keys(normalizedProviderSpecificData).length > 0) {
    connection.providerSpecificData = normalizedProviderSpecificData;
  }
  // Sanitize the window-thresholds map up front so the in-memory `connection`
  // matches the row we're about to insert. The serialize path runs the same
  // sanitizer on the way to SQLite. Assigning null (when sanitize collapses
  // to no-overrides) keeps the field present on the returned object so the
  // UI can tell "field was read, no overrides" apart from "field absent."
  if ("quotaWindowThresholds" in connection) {
    const result = sanitizeQuotaWindowThresholds(connection.quotaWindowThresholds);
    if (result.rejected.length > 0) {
      throw new Error(
        `Refusing to persist quotaWindowThresholds with rejected keys: ${result.rejected.join(", ")}`
      );
    }
    connection.quotaWindowThresholds = result.sanitized;
  }

  // Same sanitization for rateLimitOverrides — keep in-memory representation
  // in sync with what gets persisted. Reject (don't silently drop) invalid
  // keys/values so a direct DB writer can't lose operator intent.
  if ("rateLimitOverrides" in connection) {
    const result = sanitizeRateLimitOverrides(connection.rateLimitOverrides);
    if (result.rejected.length > 0) {
      throw new Error(
        `Refusing to persist rateLimitOverrides with rejected keys: ${result.rejected.join(", ")}`
      );
    }
    connection.rateLimitOverrides = result.sanitized;
  }

  _insertConnectionRow(db, encryptConnectionFields({ ...connection }));
  const providerId = toStringOrNull(data.provider);
  if (providerId) {
    reorderConnections(db, providerId);
  }
  backupDbFile("pre-write");
  invalidateDbCache("connections"); // Bust connections read cache

  const returnedConnection = withNullableRateLimitOverrides(
    withNullableQuotaWindowThresholds(
      withNullableMaxConcurrent(cleanNulls(connection), connection),
      connection
    ),
    connection
  );

  if (
    isMicrosoftDesignerWebRetiredProviderId(data.provider) ||
    isRuntimeRetiredProviderId(providerId) ||
    isCommonChatGptWebRetiredProviderId(providerId)
  ) {
    return (await getProviderConnectionById(String(connection.id))) ?? returnedConnection;
  }

  return returnedConnection;
}

function _insertConnectionRow(db: DbLike, conn: JsonRecord) {
  db.prepare(
    `
    INSERT INTO provider_connections (
      id, provider, auth_type, name, email, priority, is_active,
      access_token, refresh_token, expires_at, token_expires_at,
      scope, project_id, test_status, error_code, last_error,
      last_error_at, last_error_type, last_error_source, backoff_level,
      rate_limited_until, health_check_interval, last_health_check_at,
      last_tested, api_key, id_token, provider_specific_data,
      expires_in, display_name, global_priority, default_model,
      token_type, consecutive_use_count, rate_limit_protection, last_used_at, "group", max_concurrent,
      proxy_enabled, per_key_proxy_enabled, quota_visible, quota_window_thresholds_json, rate_limit_overrides_json,
      created_at, updated_at
    ) VALUES (
      @id, @provider, @authType, @name, @email, @priority, @isActive,
      @accessToken, @refreshToken, @expiresAt, @tokenExpiresAt,
      @scope, @projectId, @testStatus, @errorCode, @lastError,
      @lastErrorAt, @lastErrorType, @lastErrorSource, @backoffLevel,
      @rateLimitedUntil, @healthCheckInterval, @lastHealthCheckAt,
      @lastTested, @apiKey, @idToken, @providerSpecificData,
      @expiresIn, @displayName, @globalPriority, @defaultModel,
      @tokenType, @consecutiveUseCount, @rateLimitProtection, @lastUsedAt, @group, @maxConcurrent,
      @proxyEnabled, @perKeyProxyEnabled, @quotaVisible, @quotaWindowThresholdsJson, @rateLimitOverridesJson,
      @createdAt, @updatedAt
    )
  `
  ).run({
    id: conn.id,
    provider: conn.provider,
    authType: conn.authType || null,
    name: conn.name || null,
    email: conn.email || null,
    priority: conn.priority || 0,
    isActive: conn.isActive === false ? 0 : 1,
    accessToken: conn.accessToken || null,
    refreshToken: conn.refreshToken || null,
    expiresAt: conn.expiresAt || null,
    tokenExpiresAt: conn.tokenExpiresAt || null,
    scope: conn.scope || null,
    projectId: conn.projectId || null,
    testStatus: conn.testStatus || null,
    errorCode: conn.errorCode || null,
    lastError: conn.lastError || null,
    lastErrorAt: conn.lastErrorAt || null,
    lastErrorType: conn.lastErrorType || null,
    lastErrorSource: conn.lastErrorSource || null,
    backoffLevel: conn.backoffLevel || 0,
    rateLimitedUntil: conn.rateLimitedUntil || null,
    healthCheckInterval: conn.healthCheckInterval ?? null,
    lastHealthCheckAt: conn.lastHealthCheckAt || null,
    lastTested: conn.lastTested || null,
    apiKey: conn.apiKey || null,
    idToken: conn.idToken || null,
    providerSpecificData: conn.providerSpecificData
      ? JSON.stringify(conn.providerSpecificData)
      : null,
    expiresIn: conn.expiresIn || null,
    displayName: conn.displayName || null,
    globalPriority: conn.globalPriority || null,
    defaultModel: conn.defaultModel || null,
    tokenType: conn.tokenType || null,
    consecutiveUseCount: conn.consecutiveUseCount || 0,
    rateLimitProtection:
      conn.rateLimitProtection === true || conn.rateLimitProtection === 1 ? 1 : 0,
    lastUsedAt: conn.lastUsedAt || null,
    group: conn.group || null,
    maxConcurrent: conn.maxConcurrent ?? null,
    proxyEnabled: normalizeBooleanColumn(conn.proxyEnabled, true) ? 1 : 0,
    perKeyProxyEnabled: normalizeBooleanColumn(conn.perKeyProxyEnabled, false) ? 1 : 0,
    quotaVisible: normalizeBooleanColumn(conn.quotaVisible, true) ? 1 : 0,
    quotaWindowThresholdsJson: serializeJsonField(conn.quotaWindowThresholds),
    rateLimitOverridesJson: serializeJsonField(conn.rateLimitOverrides),
    createdAt: conn.createdAt,
    updatedAt: conn.updatedAt,
  });
}

// Assembles the `.run()` params for _updateConnectionRow's UPDATE statement.
// Split out purely to keep _updateConnectionRow under the max-lines-per-function
// gate — same field mapping/normalization as before, just relocated.
function _buildUpdateConnectionRowParams(id: string, data: JsonRecord, now: unknown) {
  return {
    id,
    provider: data.provider,
    authType: data.authType || null,
    name: data.name || null,
    email: data.email || null,
    priority: data.priority || 0,
    isActive: data.isActive === false ? 0 : 1,
    accessToken: data.accessToken || null,
    refreshToken: data.refreshToken || null,
    expiresAt: data.expiresAt || null,
    tokenExpiresAt: data.tokenExpiresAt || null,
    scope: data.scope || null,
    projectId: data.projectId || null,
    testStatus: data.testStatus || null,
    errorCode: data.errorCode || null,
    lastError: data.lastError || null,
    lastErrorAt: data.lastErrorAt || null,
    lastErrorType: data.lastErrorType || null,
    lastErrorSource: data.lastErrorSource || null,
    backoffLevel: data.backoffLevel || 0,
    rateLimitedUntil: data.rateLimitedUntil || null,
    healthCheckInterval: data.healthCheckInterval ?? null,
    lastHealthCheckAt: data.lastHealthCheckAt || null,
    lastTested: data.lastTested || null,
    apiKey: data.apiKey || null,
    idToken: data.idToken || null,
    providerSpecificData: data.providerSpecificData
      ? JSON.stringify(data.providerSpecificData)
      : null,
    expiresIn: data.expiresIn || null,
    displayName: data.displayName || null,
    globalPriority: data.globalPriority || null,
    defaultModel: data.defaultModel || null,
    tokenType: data.tokenType || null,
    consecutiveUseCount: data.consecutiveUseCount || 0,
    rateLimitProtection:
      data.rateLimitProtection === true || data.rateLimitProtection === 1 ? 1 : 0,
    lastUsedAt: data.lastUsedAt || null,
    group: data.group || null,
    maxConcurrent: data.maxConcurrent ?? null,
    quotaWindowThresholdsJson: serializeJsonField(data.quotaWindowThresholds),
    proxyEnabled: normalizeBooleanColumn(data.proxyEnabled, true) ? 1 : 0,
    perKeyProxyEnabled: normalizeBooleanColumn(data.perKeyProxyEnabled, false) ? 1 : 0,
    quotaVisible: normalizeBooleanColumn(data.quotaVisible, true) ? 1 : 0,
    rateLimitOverridesJson: serializeJsonField(data.rateLimitOverrides),
    lastPingAt: data.lastPingAt || null,
    lastPingedResetKey: data.lastPingedResetKey || null,
    updatedAt: now,
  };
}

function _updateConnectionRow(db: DbLike, id: string, data: JsonRecord) {
  const now = data.updatedAt || new Date().toISOString();
  db.prepare(
    `
    UPDATE provider_connections SET
      provider = @provider, auth_type = @authType, name = @name, email = @email,
      priority = @priority, is_active = @isActive, access_token = @accessToken,
      refresh_token = @refreshToken, expires_at = @expiresAt, token_expires_at = @tokenExpiresAt,
      scope = @scope, project_id = @projectId, test_status = @testStatus, error_code = @errorCode,
      last_error = @lastError, last_error_at = @lastErrorAt, last_error_type = @lastErrorType,
      last_error_source = @lastErrorSource, backoff_level = @backoffLevel,
      rate_limited_until = @rateLimitedUntil, health_check_interval = @healthCheckInterval,
      last_health_check_at = @lastHealthCheckAt, last_tested = @lastTested, api_key = @apiKey,
      id_token = @idToken, provider_specific_data = @providerSpecificData,
      expires_in = @expiresIn, display_name = @displayName, global_priority = @globalPriority,
      default_model = @defaultModel, token_type = @tokenType,
      consecutive_use_count = @consecutiveUseCount,
      rate_limit_protection = @rateLimitProtection,
      last_used_at = @lastUsedAt,
      "group" = @group,
      max_concurrent = @maxConcurrent,
      quota_window_thresholds_json = @quotaWindowThresholdsJson,
      proxy_enabled = @proxyEnabled,
      per_key_proxy_enabled = @perKeyProxyEnabled,
      quota_visible = @quotaVisible,
      rate_limit_overrides_json = @rateLimitOverridesJson,
      last_ping_at = @lastPingAt,
      last_pinged_reset_key = @lastPingedResetKey,
      updated_at = @updatedAt
    WHERE id = @id
  `
  ).run(_buildUpdateConnectionRowParams(id, data, now));
}

export type ProviderConnectionUpdateOpts = UpdateOpts & {
  /** #16190: this isActive:false write is the permanent-ban auto-disable (never retested). */
  permanentBan?: boolean;
};

export async function updateProviderConnection(
  id: string,
  data: JsonRecord,
  opts?: ProviderConnectionUpdateOpts
) {
  const db = getDbInstance() as unknown as DbLike;
  const existing = db.prepare("SELECT * FROM provider_connections WHERE id = ?").get(id);
  if (!existing) return null;

  // The incoming value only. A connection that already holds the password has
  // to stay editable, or an operator cannot repair the one this guard exists
  // to prevent -- and re-checking the merged value would spend a bcrypt round
  // on every unrelated field edit.
  await assertApiKeyIsNotManagementPassword(data.apiKey);

  const existingCamel = toRecord(rowToCamel(existing));
  const merged: JsonRecord = {
    ...existingCamel,
    ...data,
    updatedAt: new Date().toISOString(),
  };
  merged.providerSpecificData = applyCodexChildCooldownClearOnUpdate(
    data,
    normalizeConnectionProviderSpecificData(
      toStringOrNull(merged.provider),
      merged.providerSpecificData,
      merged,
      existingCamel.providerSpecificData
    )
  );
  // #16190: with CREDENTIAL_HEALTH_RETEST_AUTO_DISABLED on, an automated switch-off is
  // marked so the health sweep can retest it; a permanent-ban switch-off is always marked
  // so that sweep never retests it, whenever the flag is turned on.
  if (typeof data.isActive === "boolean") {
    merged.providerSpecificData = applyAutomaticDisableMarker(
      merged.providerSpecificData,
      existingCamel.isActive,
      data.isActive,
      undefined,
      {
        stampAutomatic: data.isActive === false && isRetestAutoDisabledEnabled(),
        permanentBan: opts?.permanentBan === true,
      }
    );
  }
  // Mirror the sanitization the create path applies — keep the returned
  // object in lockstep with what we persist.
  if ("quotaWindowThresholds" in merged) {
    const result = sanitizeQuotaWindowThresholds(merged.quotaWindowThresholds);
    if (result.rejected.length > 0) {
      throw new Error(
        `Refusing to persist quotaWindowThresholds with rejected keys: ${result.rejected.join(", ")}`
      );
    }
    // For updates we always carry the key forward (even as null) so the read
    // path surfaces the cleared state to callers that merged it.
    merged.quotaWindowThresholds = result.sanitized;
  }
  if ("rateLimitOverrides" in merged) {
    const result = sanitizeRateLimitOverrides(merged.rateLimitOverrides);
    if (result.rejected.length > 0) {
      throw new Error(
        `Refusing to persist rateLimitOverrides with rejected keys: ${result.rejected.join(", ")}`
      );
    }
    merged.rateLimitOverrides = result.sanitized;
  }
  const existingRecord = toRecord(existing);

  db.transaction(() => {
    reconcileCodexUsageHistory(db, {
      connectionId: id,
      existing: existingRecord,
      merged,
    });
    _updateConnectionRow(db, id, encryptConnectionFields({ ...merged }));
  })();
  backupDbFile("pre-write");
  invalidateConnectionUpdate(id, data, opts);
  bumpProxyConfigGeneration();

  // Zero is the internal move-to-top sentinel. Explicit positive priorities
  // are operator-selected values, not ranks to compact after every edit.
  if (data.priority === 0) {
    const existingRecord = toRecord(existing);
    const providerId =
      typeof existingRecord.provider === "string"
        ? existingRecord.provider
        : String(existingRecord.provider || "");
    reorderConnections(db, providerId);
    return getProviderConnectionById(id);
  }

  // The write path keeps existing ciphertext in merged.providerSpecificData
  // to avoid re-encrypting unchanged values. Return a decrypted copy instead:
  // callers of updateProviderConnection must never see enc:v1 metadata as the
  // usable console credentials (#16836).
  const returned = decryptConnectionFields({ ...merged });
  const returnedConnection = withNullableRateLimitOverrides(
    withNullableQuotaWindowThresholds(
      withNullableMaxConcurrent(cleanNulls(returned), returned),
      returned
    ),
    returned
  );

  if (
    isMicrosoftDesignerWebRetiredProviderId(merged.provider) ||
    isRuntimeRetiredProviderId(merged.provider) ||
    isCommonChatGptWebRetiredProviderId(merged.provider)
  ) {
    return (await getProviderConnectionById(id)) ?? returnedConnection;
  }

  return returnedConnection;
}

export { mergeConnectionProviderSpecificData } from "./providers/providerSpecificDataMerge";

export {
  updateCodexScopedQuotaState,
  updateCodexScopeCooldown,
  applyCodexChildCooldownClearOnUpdate,
  stripCodexChildCooldownFields,
  stripCodexChildCooldownsFromConnection,
  hasCodexScopeCooldown,
  liftCodexScopeCooldownOnHeadroom,
} from "./providers/codexAccountState";

/**
 * Lightweight stat bump — updates lastUsedAt and consecutiveUseCount without
 * SELECT, re-encrypt, cache invalidation, or file backup.
 * Safe for the hot getProviderCredentials path where only usage stats change.
 * Fixes the cache-thrashing bug where every credential selection invalidated
 * the 5s TTL cache and paid 3000-row decryption cost on the next request.
 */
export async function touchConnectionLastUsed(
  id: string,
  consecutiveUseCount: number
): Promise<void> {
  if (!id) return;
  const db = getDbInstance() as unknown as DbLike;
  const now = new Date().toISOString();
  db.prepare(
    `UPDATE provider_connections SET
      last_used_at = @lastUsedAt,
      consecutive_use_count = @consecutiveUseCount,
      updated_at = @updatedAt
    WHERE id = @id`
  ).run({
    lastUsedAt: now,
    consecutiveUseCount,
    updatedAt: now,
    id,
  });
}

/**
 * #12849: stamp when a connection's synced model catalog was last written.
 * getActiveSyncedCatalog reads this to stop treating a synced catalog as
 * authoritative forever — a connection synced once and never refreshed
 * silently pinned routing to that point-in-time snapshot with no staleness
 * check. Lightweight targeted UPDATE, mirrors touchConnectionLastUsed.
 */
export async function touchConnectionSyncedModelsAt(id: string): Promise<void> {
  if (!id) return;
  const db = getDbInstance() as unknown as DbLike;
  const now = new Date().toISOString();
  db.prepare(
    `UPDATE provider_connections SET
      synced_models_at = @syncedModelsAt,
      updated_at = @updatedAt
    WHERE id = @id`
  ).run({
    syncedModelsAt: now,
    updatedAt: now,
    id,
  });
  // New successful-sync timestamp must be visible without waiting for the
  // connection row cache to expire.
  invalidateDbCache("connections", id, { skipModelCatalog: true });
}

/**
 * Lightweight backoff reset — runs a targeted UPDATE without SELECT or re-encrypt.
 * Follows the `clearConnectionErrorIfUnchanged` pattern but without the CAS check,
 * since the caller already verified the connection is eligible for reset.
 * Resets all backoff/error columns so the connection re-enters the selection pool.
 * Does invalidateDbCache + bumpProxyConfigGeneration since backoff affects priority.
 * #13389: `skipModelCatalog` — the catalog builder never reads backoff/error
 * state, so this must not bust the expensive-to-rebuild `/v1/models` cache.
 */
export async function resetConnectionBackoff(id: string): Promise<void> {
  if (!id) return;
  const db = getDbInstance() as unknown as DbLike;
  const now = new Date().toISOString();
  db.prepare(
    `UPDATE provider_connections SET
      backoff_level = 0,
      test_status = 'active',
      last_error = NULL,
      last_error_at = NULL,
      last_error_type = NULL,
      last_error_source = NULL,
      error_code = NULL,
      updated_at = @updatedAt
    WHERE id = @id`
  ).run({
    updatedAt: now,
    id,
  });
  invalidateDbCache("connections", id, { skipModelCatalog: true });
  bumpProxyConfigGeneration();
}

export async function cleanupProviderConnections() {
  return 0;
}

export async function getDistinctGroups(): Promise<string[]> {
  const db = getDbInstance() as unknown as DbLike;
  const rows = db
    .prepare(
      'SELECT DISTINCT "group" FROM provider_connections WHERE "group" IS NOT NULL ORDER BY "group"'
    )
    .all() as Array<{ group?: string }>;
  return rows.map((r) => String(r.group ?? "")).filter(Boolean);
}

export { autoMigrateLegacyEncryptedConnections, getGheCopilotHosts } from "./providers/migrations";
export {
  deleteProviderConnection,
  deleteProviderConnections,
  deleteProviderConnectionsByProvider,
  reorderProviderConnections,
} from "./providers/deletion";

// ──────────────── Re-exports from leaf modules ────────────────

export {
  getProviderNodes,
  getProviderNodesCount,
  getProviderNodeById,
  resolveProviderNodeForConnection,
  createProviderNode,
  updateProviderNode,
  deleteProviderNode,
} from "./providers/nodes";
export {
  setConnectionRateLimitUntil,
  markConnectionRateLimitedUntil,
  clearConnectionRateLimit,
  getEffectiveQuotaUsage,
  clearStaleCrashCooldowns,
  formatResetCountdown,
  isConnectionRateLimited,
  getRateLimitedConnections,
  clearConnectionErrorIfUnchanged,
} from "./providers/rateLimit";