fix(db): disambiguate Kiro OAuth dedup by profileArn (#10815)

changelog.d/fixes/10815-kiro-oauth-profilearn-dedup.md

@@ -0,0 +1 @@
+- fix(db): disambiguate `createProviderConnection()`'s OAuth email dedup by `providerSpecificData.profileArn` in addition to `username`, so adding a second Kiro/AWS profile with the same email creates a new connection instead of silently merging into the first (#10815)

src/lib/db/providers.ts

@@ -26,7 +26,11 @@ import {
   isBcryptHash,
   verifyManagementPassword,
 } from "@/lib/auth/managementPassword";
-import { webSessionCredentialKey, parseProviderSpecificData } from "./webSessionDedup";
+import {
+  webSessionCredentialKey,
+  parseProviderSpecificData,
+  isMatchingOauthIdentity,
+} from "./webSessionDedup";
 import { pickCodexConnectionForUser } from "@/lib/oauth/utils/codexConnectionSelection";
 import { reconcileCodexUsageHistory } from "./providers/usageIdentityReconciliation";
 
@@ -435,30 +439,25 @@ export async function createProviderConnection(data: JsonRecord) {
       }
     } else {
       // For other providers (or Codex without workspaceId), match on email —
-      // disambiguated by providerSpecificData.username when present on both
-      // sides. Two different IdPs can share the same email address (e.g. a
-      // Google account and a HuggingFace account); 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 (legacy rows created before this
-      // disambiguation existed).
+      // 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);
       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) => {
-          const existingUsername = toStringOrNull(
-            parseProviderSpecificData(row.provider_specific_data)?.username
-          );
-          if (incomingUsername && existingUsername) {
-            return incomingUsername === existingUsername;
-          }
-          if (incomingUsername || existingUsername) return false;
-          return true;
-        }) || null;
+        emailMatches.find((row) =>
+          isMatchingOauthIdentity(row, incomingUsername, incomingProfileArn)
+        ) || null;
     }
   } else if (data.authType === "apikey") {
     // Name-based upsert (existing behavior): same provider + same name → update.

src/lib/db/webSessionDedup.ts

@@ -55,3 +55,43 @@ export function parseProviderSpecificData(raw: unknown): Record<string, unknown>
   }
   return null;
 }
+
+/** Trimmed non-empty string, else null — local to avoid a cross-module import for one coercion. */
+function nonEmptyString(value: unknown): string | null {
+  return typeof value === "string" && value.trim() ? value.trim() : null;
+}
+
+/**
+ * Two-sided disambiguator match: `true` when both sides agree, `false` when
+ * both carry a value and it differs, `undefined` when the field can't decide
+ * (at most one side carries it) — the caller then defers to other fields.
+ */
+function fieldMatch(incoming: string | null, existing: string | null): boolean | undefined {
+  if (incoming && existing) return incoming === existing;
+  if (incoming || existing) return false;
+  return undefined;
+}
+
+/**
+ * Decide whether `row` (an existing `provider_connections` record) is the
+ * same OAuth identity as an incoming connection carrying `incomingUsername`
+ * and `incomingProfileArn` (#10815).
+ *
+ * Two independent disambiguators, either of which can prove "different
+ * account": `providerSpecificData.username` (Raycast-style IdP dedup) and
+ * `providerSpecificData.profileArn` (Kiro/AWS profile dedup — Kiro never
+ * sets `username`). A field only rules a match IN/OUT when both the
+ * incoming and existing record carry it; when neither carries either field
+ * the legacy bare-email match still applies unchanged.
+ */
+export function isMatchingOauthIdentity(
+  row: { provider_specific_data?: unknown },
+  incomingUsername: string | null,
+  incomingProfileArn: string | null
+): boolean {
+  const existingPsd = parseProviderSpecificData(row.provider_specific_data);
+  const usernameMatch = fieldMatch(incomingUsername, nonEmptyString(existingPsd?.username));
+  const profileArnMatch = fieldMatch(incomingProfileArn, nonEmptyString(existingPsd?.profileArn));
+  if (usernameMatch === false || profileArnMatch === false) return false;
+  return true;
+}

tests/unit/kiro-second-oauth-connection-10815.test.ts

@@ -0,0 +1,115 @@
+import test from "node:test";
+import assert from "node:assert/strict";
+import fs from "node:fs";
+import os from "node:os";
+import path from "node:path";
+
+const TEST_DATA_DIR = fs.mkdtempSync(path.join(os.tmpdir(), "omniroute-kiro-10815-"));
+process.env.DATA_DIR = TEST_DATA_DIR;
+
+const core = await import("../../src/lib/db/core.ts");
+const providersDb = await import("../../src/lib/db/providers.ts");
+
+test.after(async () => {
+  core.resetDbInstance();
+  fs.rmSync(TEST_DATA_DIR, { recursive: true, force: true });
+});
+
+test("createProviderConnection keeps two Kiro oauth connections with the same email but different profileArn separate (#10815)", async () => {
+  const first = await providersDb.createProviderConnection({
+    provider: "kiro",
+    authType: "oauth",
+    email: "user@example.com",
+    accessToken: "token-account-1",
+    refreshToken: "refresh-account-1",
+    providerSpecificData: {
+      authMethod: "imported",
+      provider: "Google",
+      profileArn: "arn:aws:codewhisperer:us-east-1:111111111111:profile/AAAA",
+    },
+  });
+
+  const second = await providersDb.createProviderConnection({
+    provider: "kiro",
+    authType: "oauth",
+    email: "user@example.com",
+    accessToken: "token-account-2",
+    refreshToken: "refresh-account-2",
+    providerSpecificData: {
+      authMethod: "imported",
+      provider: "Google",
+      profileArn: "arn:aws:codewhisperer:us-east-1:222222222222:profile/BBBB",
+    },
+  });
+
+  const kiroConnections = await providersDb.getProviderConnections({ provider: "kiro" });
+
+  assert.notEqual(
+    second.id,
+    first.id,
+    "second Kiro connection should be a new row, not an update of the first"
+  );
+  assert.equal(
+    kiroConnections.length,
+    2,
+    `expected 2 Kiro connections after adding a second account, got ${kiroConnections.length}`
+  );
+});
+
+test("createProviderConnection re-auth of the SAME Kiro profileArn still updates in place (#10815)", async () => {
+  const first = await providersDb.createProviderConnection({
+    provider: "kiro",
+    authType: "oauth",
+    email: "same-profile@example.com",
+    accessToken: "token-a",
+    refreshToken: "refresh-a",
+    providerSpecificData: {
+      authMethod: "imported",
+      provider: "Google",
+      profileArn: "arn:aws:codewhisperer:us-east-1:333333333333:profile/CCCC",
+    },
+  });
+
+  const reauth = await providersDb.createProviderConnection({
+    provider: "kiro",
+    authType: "oauth",
+    email: "same-profile@example.com",
+    accessToken: "token-a-refreshed",
+    refreshToken: "refresh-a-refreshed",
+    providerSpecificData: {
+      authMethod: "imported",
+      provider: "Google",
+      profileArn: "arn:aws:codewhisperer:us-east-1:333333333333:profile/CCCC",
+    },
+  });
+
+  assert.equal(
+    reauth.id,
+    first.id,
+    "re-auth of the same profileArn should update the existing row"
+  );
+});
+
+test("createProviderConnection keeps legacy email-only OAuth dedup for rows without profileArn/username (#10815)", async () => {
+  const first = await providersDb.createProviderConnection({
+    provider: "google",
+    authType: "oauth",
+    email: "legacy@example.com",
+    accessToken: "legacy-token-1",
+    refreshToken: "legacy-refresh-1",
+  });
+
+  const second = await providersDb.createProviderConnection({
+    provider: "google",
+    authType: "oauth",
+    email: "legacy@example.com",
+    accessToken: "legacy-token-2",
+    refreshToken: "legacy-refresh-2",
+  });
+
+  assert.equal(
+    second.id,
+    first.id,
+    "legacy rows without profileArn/username should still dedup by bare email match"
+  );
+});