fix(usage): discover migrated database logins and relative paths

Match the source application’s migration and database-path rules so usage discovery does not keep reporting a stale file login after migration.

Co-authored-by: Jiayang Wu <651475459@qq.com>

packages/server/src/server/plugins/usage-sources.e2e.test.ts

@@ -224,7 +224,7 @@ async function fetchApi(url, init) {
     return new Response(JSON.stringify({ account: { uuid: second ? 'claude-two' : 'claude-one' }, organization: { uuid: second ? 'org-two' : 'org-one' } }));
   }
   if (token === 'Bearer fixture-revoked') return new Response(null, { status: 401 });
-  const used = { 'Bearer fixture-codex': 11, 'Bearer fixture-opencode': 22, 'Bearer fixture-pi-codex': 33, 'Bearer fixture-omp-codex': 44, 'Bearer fixture-omp-claude': 66, 'Bearer fixture-pi-claude': 77 }[token] || 55;
+  const used = { 'Bearer fixture-codex': 11, 'Bearer fixture-opencode': 22, 'Bearer fixture-opencode-db': 23, 'Bearer fixture-pi-codex': 33, 'Bearer fixture-omp-codex': 44, 'Bearer fixture-omp-claude': 66, 'Bearer fixture-pi-claude': 77 }[token] || 55;
   return new Response(JSON.stringify({ rate_limit: { primary_window: { used_percent: used } }, five_hour: { utilization: used } }));
 }
 export default function contribute(server) {
@@ -270,6 +270,31 @@ export default function contribute(server) {
       (await client.listUsageReports({ reportIds: ["codex:chatgpt-one"], forceRefresh: true }))
         .reports[0];
     expect((await refresh())?.report.windows[0]?.usedPct).toBe(22);
+    const openCodeDatabase = path.join(path.dirname(openCodePath), "opencode.db");
+    const openCodeDb = new DatabaseSync(openCodeDatabase);
+    openCodeDb.exec(
+      "CREATE TABLE credential (id TEXT PRIMARY KEY, integration_id TEXT, label TEXT, value TEXT, active INTEGER, time_created INTEGER, time_updated INTEGER)",
+    );
+    // OpenCode's v1 migration leaves active NULL and does not update auth.json.
+    openCodeDb.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, NULL, 1, 1)").run(
+      "migrated-login",
+      "openai",
+      "OpenAI",
+      JSON.stringify({
+        type: "oauth",
+        access: "fixture-opencode-db",
+        expires: Date.now() + 60_000,
+        metadata: { accountID: "chatgpt-one" },
+      }),
+    );
+    openCodeDb.close();
+    const rediscover = async () =>
+      (await client.listUsageReports({ forceRefresh: true })).reports.find(
+        (entry) => entry.id === "codex:chatgpt-one",
+      );
+    expect((await rediscover())!.report.windows[0]!.usedPct).toBe(23);
+    await rm(openCodeDatabase);
+    expect((await rediscover())!.report.windows[0]!.usedPct).toBe(22);
     await rm(openCodePath);
     expect((await refresh())?.report.windows[0]?.usedPct).toBe(33);
     await json(piPath, {

plugins/codex-usage-source/server/stores.test.ts

@@ -78,6 +78,27 @@ async function database(path: string) {
   db.close();
 }
 
+async function opencodeDatabase(path: string) {
+  await mkdir((await import("node:path")).dirname(path), { recursive: true });
+  const { DatabaseSync } = await import("node:sqlite");
+  const db = new DatabaseSync(path);
+  db.exec(
+    "CREATE TABLE credential (id TEXT PRIMARY KEY, integration_id TEXT, label TEXT, value TEXT, connector_id TEXT, method_id TEXT, active INTEGER, time_created INTEGER, time_updated INTEGER)",
+  );
+  return db;
+}
+
+function opencodeOauth(access: string, expires: number, accountId?: string) {
+  return JSON.stringify({
+    type: "oauth",
+    methodID: "chatgpt-headless",
+    access,
+    refresh: "fixture-refresh",
+    expires,
+    ...(accountId ? { metadata: { accountID: accountId } } : {}),
+  });
+}
+
 test("Pi uses its own OAuth identity and re-reads rotated tokens without writing", async () => {
   const path = join(home, ".pi", "agent", "auth.json");
   await json(path, {
@@ -260,3 +281,269 @@ test("OMP fetch re-reads its row after rotation and skips a newly disabled login
     ),
   ).rejects.toThrow("login store no longer exists");
 });
+
+function opencodeUsageResponse() {
+  return Response.json({
+    plan_type: "plus",
+    rate_limit: {
+      primary_window: { used_percent: 30, limit_window_seconds: 18000 },
+      secondary_window: { used_percent: 81, limit_window_seconds: 604800 },
+    },
+  });
+}
+
+test("OpenCode v2 database login replaces a stale v1 file login", async () => {
+  const data = join(home, "data");
+  const env = { XDG_DATA_HOME: data };
+  const databasePath = join(data, "opencode", "opencode.db");
+  await json(join(data, "opencode", "auth.json"), {
+    openai: { type: "oauth", access: "fixture-expired", expires: 500 },
+  });
+  const db = await opencodeDatabase(databasePath);
+  db.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)").run(
+    "cred_1",
+    "openai",
+    "OpenAI",
+    opencodeOauth("fixture-db", 2000, "db-account"),
+    null,
+    "chatgpt-headless",
+    1,
+    1,
+    1,
+  );
+  db.close();
+
+  const inputs = await logins(lookup(env));
+  expect(inputs).toEqual([
+    { route: { store: "opencode-db", path: databasePath, credentialId: "cred_1" } },
+  ]);
+  expect(await accountIdentity(inputs[0]!, env)).toEqual({ key: "db-account" });
+  const report = await fetchUsage(
+    inputs[0]!,
+    async (_url, init) => {
+      const headers = new Headers(init?.headers);
+      expect(headers.get("Authorization")).toBe("Bearer fixture-db");
+      expect(headers.get("ChatGPT-Account-Id")).toBe("db-account");
+      return opencodeUsageResponse();
+    },
+    lookup(env),
+  );
+  expect(report).toMatchObject({
+    status: "available",
+    planLabel: "plus",
+    windows: [
+      { id: "five_hour", usedPct: 30 },
+      { id: "weekly", usedPct: 81 },
+    ],
+  });
+});
+
+test("OpenCode v2 database login with an expired token is unavailable without a request", async () => {
+  const data = join(home, "data");
+  const env = { XDG_DATA_HOME: data };
+  const databasePath = join(data, "opencode", "opencode.db");
+  const db = await opencodeDatabase(databasePath);
+  db.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)").run(
+    "cred_1",
+    "openai",
+    "OpenAI",
+    opencodeOauth("fixture-expired", 500, "db-account"),
+    null,
+    "chatgpt-headless",
+    1,
+    1,
+    1,
+  );
+  db.close();
+
+  const [input] = await logins(lookup(env));
+  expect(
+    await fetchUsage(
+      input!,
+      async () => {
+        throw new Error("must not fetch an expired token");
+      },
+      lookup(env),
+    ),
+  ).toEqual({
+    status: "unavailable",
+    problem: { kind: "expired", expiresAt: new Date(500).toISOString(), refreshedBy: "opencode" },
+  });
+});
+
+test("OpenCode v2 database is unchanged after its login is read", async () => {
+  const data = join(home, "data");
+  const env = { XDG_DATA_HOME: data };
+  const databasePath = join(data, "opencode", "opencode.db");
+  const db = await opencodeDatabase(databasePath);
+  db.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)").run(
+    "cred_1",
+    "openai",
+    "OpenAI",
+    opencodeOauth("fixture-db", 2000, "db-account"),
+    null,
+    "chatgpt-headless",
+    1,
+    1,
+    1,
+  );
+  db.close();
+  const before = await readFile(databasePath);
+
+  const [input] = await logins(lookup(env));
+  await fetchUsage(input!, async () => opencodeUsageResponse(), lookup(env));
+  expect(await readFile(databasePath)).toEqual(before);
+});
+
+test("OpenCode v2 database login is re-read after the stored token rotates", async () => {
+  const data = join(home, "data");
+  const env = { XDG_DATA_HOME: data };
+  const databasePath = join(data, "opencode", "opencode.db");
+  const db = await opencodeDatabase(databasePath);
+  db.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)").run(
+    "cred_1",
+    "openai",
+    "OpenAI",
+    opencodeOauth("fixture-first", 2000, "db-account"),
+    null,
+    "chatgpt-headless",
+    1,
+    1,
+    1,
+  );
+  db.close();
+
+  const [input] = await logins(lookup(env));
+  const { DatabaseSync } = await import("node:sqlite");
+  const writer = new DatabaseSync(databasePath);
+  writer
+    .prepare("UPDATE credential SET value = ? WHERE id = 'cred_1'")
+    .run(opencodeOauth("fixture-rotated", 2000, "db-account"));
+  writer.close();
+
+  const report = await fetchUsage(
+    input!,
+    async (_url, init) => {
+      expect(new Headers(init?.headers).get("Authorization")).toBe("Bearer fixture-rotated");
+      return opencodeUsageResponse();
+    },
+    lookup(env),
+  );
+  expect(report.status).toBe("available");
+});
+
+test.each([
+  ["there is no v2 database", null],
+  [
+    "the v2 database holds no OpenAI login",
+    { integration: "zenai", value: JSON.stringify({ type: "key", key: "fixture-key" }) },
+  ],
+  [
+    "the v2 OpenAI login is not active",
+    {
+      integration: "openai",
+      value: opencodeOauth("fixture-inactive", 2000, "db-account"),
+      active: 0,
+    },
+  ],
+])("OpenCode v1 file login stays when %s", async (_name, row) => {
+  const data = join(home, "data");
+  const env = { XDG_DATA_HOME: data };
+  const file = join(data, "opencode", "auth.json");
+  await json(file, { openai: { type: "oauth", access: "fixture-file", expires: 2000 } });
+  if (row) {
+    const databasePath = join(data, "opencode", "opencode.db");
+    const db = await opencodeDatabase(databasePath);
+    db.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)").run(
+      "cred_1",
+      row.integration,
+      "OpenAI",
+      row.value,
+      null,
+      "chatgpt-headless",
+      "active" in row ? row.active : 1,
+      1,
+      1,
+    );
+    db.close();
+  }
+  expect(await logins(lookup(env))).toEqual([{ route: { store: "opencode", path: file } }]);
+});
+
+test.each(["absolute", "relative"])(
+  "OpenCode v2 database path honours %s OPENCODE_DB",
+  async (kind) => {
+    const data = join(home, "data");
+    const databasePath = join(data, "opencode", "custom-opencode.db");
+    const db = await opencodeDatabase(databasePath);
+    db.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)").run(
+      "cred_1",
+      "openai",
+      "OpenAI",
+      opencodeOauth("fixture-db", 2000, "db-account"),
+      null,
+      "chatgpt-headless",
+      1,
+      1,
+      1,
+    );
+    db.close();
+    expect(
+      await logins(
+        lookup({
+          XDG_DATA_HOME: data,
+          OPENCODE_DB: kind === "absolute" ? databasePath : "custom-opencode.db",
+        }),
+      ),
+    ).toEqual([{ route: { store: "opencode-db", path: databasePath, credentialId: "cred_1" } }]);
+  },
+);
+
+test.each([false, true])(
+  "OpenCode v2 selects its current login after legacy migration (explicit selection: %s)",
+  async (selected) => {
+    const data = join(home, "data");
+    const env = { XDG_DATA_HOME: data };
+    const databasePath = join(data, "opencode", "opencode.db");
+    const db = await opencodeDatabase(databasePath);
+    const insert = db.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
+    // The v1 migration leaves active NULL; OpenCode uses creation order until a login is selected.
+    insert.run(
+      "older",
+      "openai",
+      "OpenAI",
+      opencodeOauth("fixture-older", 2000, "older-account"),
+      null,
+      "chatgpt-headless",
+      selected ? 1 : null,
+      1,
+      3,
+    );
+    insert.run(
+      "newer",
+      "openai",
+      "OpenAI",
+      opencodeOauth("fixture-newer", 2000, "newer-account"),
+      null,
+      "chatgpt-headless",
+      null,
+      2,
+      2,
+    );
+    db.close();
+
+    const inputs = await logins(lookup(env));
+    expect(inputs).toEqual([
+      {
+        route: {
+          store: "opencode-db",
+          path: databasePath,
+          credentialId: selected ? "older" : "newer",
+        },
+      },
+    ]);
+    expect(await accountIdentity(inputs[0]!, env)).toEqual({
+      key: selected ? "older-account" : "newer-account",
+    });
+  },
+);

plugins/codex-usage-source/server/stores.ts

@@ -27,6 +27,22 @@ export const oauthSchema = z.object({
 const rowSchema = z.object({ id: z.number().int().positive(), data: z.string() });
 export type Route = z.infer<typeof routeSchema>;
 
+export interface OpencodeOauthLogin {
+  id: string;
+  access: string;
+  accountId?: string;
+  expires?: number;
+}
+
+const opencodeRowSchema = z.object({ id: z.string(), value: z.string() });
+const opencodeColumnSchema = z.object({ name: z.string() });
+const opencodeCredentialSchema = z.object({
+  type: z.literal("oauth"),
+  access: z.string().min(1),
+  expires: z.number().optional(),
+  metadata: z.object({ accountID: z.string().min(1).optional() }).optional(),
+});
+
 export async function readJson(path: string): Promise<unknown> {
   try {
     return JSON.parse(await readFile(path, "utf8"));
@@ -47,6 +63,20 @@ export function piAuthPath(lookup: StoreLookup): string {
   return join(directory, "auth.json");
 }
 
+export interface OpencodeStorePaths {
+  file: string;
+  database: string;
+}
+
+export function opencodeStorePaths(env: NodeJS.ProcessEnv, home: string): OpencodeStorePaths {
+  const dataHome = env.XDG_DATA_HOME || join(home, ".local", "share");
+  const directory = join(dataHome, "opencode");
+  return {
+    file: join(directory, "auth.json"),
+    database: resolve(directory, env.OPENCODE_DB || "opencode.db"),
+  };
+}
+
 function ompDatabasePath(lookup: StoreLookup): string {
   const home = lookup.home ?? homedir();
   const env = lookup.env ?? process.env;
@@ -118,6 +148,61 @@ export function readOmp(route: Extract<Route, { store: "omp" }>, lookup: StoreLo
   return ompRows(route.path, lookup).find((row) => row.id === route.credentialId)?.oauth ?? null;
 }
 
+function parseCredential(value: string): unknown | null {
+  try {
+    return JSON.parse(value);
+  } catch (error) {
+    if (error instanceof SyntaxError) return null;
+    throw error;
+  }
+}
+
+/**
+ * OpenCode v2 keeps its logins in its own database and no longer writes the v1
+ * credential file, so the file alone cannot describe the active ChatGPT login.
+ */
+export function readOpencodeOauth(path: string, lookup: StoreLookup = {}): OpencodeOauthLogin[] {
+  const sqlite = (lookup.sqlite ?? builtinSqlite)();
+  if (!sqlite || !existsSync(path)) return [];
+  let db: Database | undefined;
+  try {
+    db = new sqlite.DatabaseSync(path, { readOnly: true });
+    const columns = db
+      .prepare("PRAGMA table_info(credential)")
+      .all()
+      .map((value) => opencodeColumnSchema.safeParse(value));
+    if (!columns.some((column) => column.success && column.data.name === "integration_id")) {
+      return [];
+    }
+    const rows = db
+      .prepare(
+        // Migrated v1 rows have no active flag. OpenCode selects the newest
+        // created login until an explicit selection pins another one.
+        "SELECT id, value FROM credential WHERE integration_id = ? AND (active = 1 OR active IS NULL) ORDER BY active DESC, time_created DESC, id DESC LIMIT 1",
+      )
+      .all("openai");
+    return rows.flatMap((value) => {
+      const row = opencodeRowSchema.safeParse(value);
+      if (!row.success) return [];
+      const credential = opencodeCredentialSchema.safeParse(parseCredential(row.data.value));
+      if (!credential.success) return [];
+      const accountId = credential.data.metadata?.accountID;
+      return [
+        {
+          id: row.data.id,
+          access: credential.data.access,
+          ...(accountId ? { accountId } : {}),
+          ...(credential.data.expires !== undefined ? { expires: credential.data.expires } : {}),
+        },
+      ];
+    });
+  } catch {
+    return [];
+  } finally {
+    db?.close();
+  }
+}
+
 export async function readHarness(
   route: Extract<Route, { path: string }>,
   lookup: StoreLookup = {},

plugins/codex-usage-source/server/usage.test.ts

@@ -230,6 +230,72 @@ test("discovers a Pi OAuth login independently of the CLI login", async () => {
   }
 });
 
+test("OpenCode v2 database login reports usage while the v1 file login expired", async () => {
+  const directory = await mkdtemp(join(tmpdir(), "usage-opencode-v2-"));
+  try {
+    const data = join(directory, "data");
+    await mkdir(join(data, "opencode"), { recursive: true });
+    await writeFile(
+      join(data, "opencode", "auth.json"),
+      JSON.stringify({ openai: { type: "oauth", access: "fixture-expired", expires: 500 } }),
+    );
+    const { DatabaseSync } = await import("node:sqlite");
+    const db = new DatabaseSync(join(data, "opencode", "opencode.db"));
+    db.exec(
+      "CREATE TABLE credential (id TEXT PRIMARY KEY, integration_id TEXT, label TEXT, value TEXT, connector_id TEXT, method_id TEXT, active INTEGER, time_created INTEGER, time_updated INTEGER)",
+    );
+    db.prepare("INSERT INTO credential VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)").run(
+      "cred_1",
+      "openai",
+      "OpenAI",
+      JSON.stringify({
+        type: "oauth",
+        methodID: "chatgpt-headless",
+        access: "fixture-db",
+        expires: 2000,
+        metadata: { accountID: "db-account" },
+      }),
+      null,
+      "chatgpt-headless",
+      1,
+      1,
+      1,
+    );
+    db.close();
+    const lookup = {
+      home: directory,
+      env: { XDG_DATA_HOME: data },
+      platform: "linux" as const,
+      now: () => 1000,
+    };
+
+    const accounts = await discover({ kind: "global" }, lookup);
+    expect(accounts.map((account) => account.input)).toEqual([
+      {
+        route: {
+          store: "opencode-db",
+          path: join(data, "opencode", "opencode.db"),
+          credentialId: "cred_1",
+        },
+      },
+    ]);
+    const report = await fetchUsage(
+      inputSchema.parse(accounts[0]!.input),
+      async (_url, init) => {
+        expect(new Headers(init?.headers).get("Authorization")).toBe("Bearer fixture-db");
+        return Response.json({
+          plan_type: "plus",
+          rate_limit: { primary_window: { used_percent: 30, limit_window_seconds: 18000 } },
+        });
+      },
+      lookup,
+    );
+    expect(report).toMatchObject({ status: "available", planLabel: "plus" });
+  } finally {
+    await rm(directory, { recursive: true, force: true });
+  }
+});
+
 async function accountFor(input: CodexUsageInput) {
   const accounts = await discover(
     { kind: "global" },

plugins/codex-usage-source/server/usage.ts

@@ -1,4 +1,12 @@
-import { discoverOmp, piAuthPath, readHarness, readJson, type StoreLookup } from "./stores.js";
+import {
+  discoverOmp,
+  opencodeStorePaths,
+  piAuthPath,
+  readHarness,
+  readJson,
+  readOpencodeOauth,
+  type StoreLookup,
+} from "./stores.js";
 import { homedir } from "node:os";
 import { join } from "node:path";
 import {
@@ -88,19 +96,33 @@ function globalRoutes(lookup: StoreLookup): CodexUsageInput[] {
   const candidates: CodexUsageInput[] = [...new Set(paths)].map((path) => ({
     route: { store: "codex", path },
   }));
+  candidates.push(...opencodeRoutes(env, home, lookup));
   candidates.push(
-    {
-      route: {
-        store: "opencode",
-        path: join(env.XDG_DATA_HOME || join(home, ".local", "share"), "opencode", "auth.json"),
-      },
-    },
     { route: { store: "pi", path: piAuthPath(lookup) } },
     ...discoverOmp(lookup).map((route) => ({ route })),
   );
   return candidates;
 }
 
+/**
+ * OpenCode v2 stores its logins in a database and leaves the v1 credential file
+ * untouched, so a file login can be stale while the database login is current.
+ * The database wins whenever it holds an OpenAI login; the file stays the
+ * source for v1 installations that have no such database.
+ */
+function opencodeRoutes(
+  env: NodeJS.ProcessEnv,
+  home: string,
+  lookup: StoreLookup,
+): CodexUsageInput[] {
+  const { file, database } = opencodeStorePaths(env, home);
+  const logins = readOpencodeOauth(database, lookup);
+  if (!logins.length) return [{ route: { store: "opencode", path: file } }];
+  return logins.map((login) => ({
+    route: { store: "opencode-db", path: database, credentialId: login.id },
+  }));
+}
+
 function sessionRoutes(
   scope: Extract<UsageScope, { kind: "session" }>,
   lookup: StoreLookup,
@@ -119,14 +141,7 @@ function sessionRoutes(
   if (scope.provider === "pi") return [{ route: { store: "pi", path: piAuthPath(lookup) } }];
   if (scope.provider === "omp") return discoverOmp(lookup).map((route) => ({ route }));
   if (scope.provider === "opencode")
-    return [
-      {
-        route: {
-          store: "opencode",
-          path: join(env.XDG_DATA_HOME || join(home, ".local", "share"), "opencode", "auth.json"),
-        },
-      },
-    ];
+    return opencodeRoutes(scope.env, lookup.home ?? homedir(), lookup);
   return [];
 }
 
@@ -135,6 +150,14 @@ export async function readAuth(
   lookup: StoreLookup = {},
 ): Promise<Auth | null> {
   const route = input.route;
+  if (route.store === "opencode-db") {
+    const login = readOpencodeOauth(route.path, lookup).find(
+      (candidate) => candidate.id === route.credentialId,
+    );
+    return login
+      ? { token: login.access, accountId: login.accountId, expires: login.expires }
+      : null;
+  }
   if (route.store !== "codex") {
     const oauth = await readHarness(route, lookup);
     return oauth
@@ -191,7 +214,7 @@ export async function fetchUsage(
 ): Promise<UsageReport> {
   const auth = await readAuth(input, lookup);
   if (!auth) throw new Error("Codex login store no longer exists");
-  const refreshedBy = input.route.store;
+  const refreshedBy = refreshCommand(input.route.store);
   if (auth.expires !== undefined && auth.expires <= (lookup.now ?? Date.now)())
     return unavailable({
       kind: "expired",
@@ -297,6 +320,17 @@ function accountIdentity(auth: Auth, input: CodexUsageInput): { key: string; lab
 }
 
 function harnessLabel(input: CodexUsageInput): string {
-  const labels = { codex: "Codex", opencode: "OpenCode", pi: "Pi", omp: "OMP" };
+  const labels = {
+    codex: "Codex",
+    opencode: "OpenCode",
+    "opencode-db": "OpenCode",
+    pi: "Pi",
+    omp: "OMP",
+  };
   return labels[input.route.store];
 }
+
+/** The CLI that refreshes the login behind each store. */
+function refreshCommand(store: CodexUsageInput["route"]["store"]): string {
+  return store === "opencode-db" ? "opencode" : store;
+}

plugins/codex-usage-source/shared/input.ts

@@ -2,6 +2,13 @@ import { z } from "zod";
 
 export const routeSchema = z.discriminatedUnion("store", [
   z.object({ store: z.enum(["codex", "opencode", "pi"]), path: z.string().min(1) }).strict(),
+  z
+    .object({
+      store: z.literal("opencode-db"),
+      path: z.string().min(1),
+      credentialId: z.string().min(1),
+    })
+    .strict(),
   z
     .object({
       store: z.literal("omp"),