// ---------------------------------------------------------------------------// Cloud code migration: GC dead DCR `oauth_client` rows + backfill the// surviving ones' `origin_issuer` (issue #1120, Part C).//// The cloud counterpart to the local libSQL boot migration// (`sqlite-oauth-client-gc-migration` in @executor-js/sdk). It runs through the// stamped `cloud_code_migration` ledger (see runner.ts), out-of-band via// `scripts/migrate.ts`, with `--dry-run` support and an advisory lock.//// It is a CODE migration, not a numbered `.sql` one, deliberately: the DCR// classification and the registrable-origin backfill are the SAME TypeScript// predicates the local path and the runtime reuse lookup use// (`classifyOAuthClientGc`, `registrableOriginOfUrl`). Re-encoding the eTLD+1// registrable-origin logic and the DCR heuristic as Postgres regex/SQL would be// a second, drift-prone source of truth for a migration that DELETES user data.// Reading the (small) oauth_client table and deciding in-process keeps exactly// one source of truth across both hosts.// --------------------------------------------------------------------------- import { classifyOAuthClientGc, isDcrClassifiedRow, registrableOriginOfUrl,} from "@executor-js/sdk"; import type { CodeMigration, CodeMigrationContext } from "./runner"; interface OAuthClientRow { readonly tenant: string; readonly owner: string; readonly slug: string; readonly grant: string | null; readonly resource: string | null; readonly origin_kind: string | null; readonly origin_issuer: string | null; readonly token_url: string | null;} const readOAuthClients = (context: CodeMigrationContext): Promise<readonly OAuthClientRow[]> => // `grant` is a reserved word in Postgres, so it must be quoted. Deterministic // row order keeps the dry-run report + logs stable. context.sql.unsafe<OAuthClientRow[]>(` SELECT tenant, owner, slug, "grant", resource, origin_kind, origin_issuer, token_url FROM oauth_client ORDER BY tenant, owner, slug `); interface ConnectionCountRow { readonly tenant: string; readonly oauth_client_owner: string; readonly oauth_client: string; readonly count: string | number;} const referenceKey = (tenant: string, owner: string, slug: string): string => `${tenant}\u0000${owner}\u0000${slug}`; /** One grouped pass over `connection` instead of a COUNT per oauth_client row * (N+1). Returns a Map from (tenant, oauth_client_owner, oauth_client) to the * number of referencing connections. */const readReferencingConnectionCounts = async ( context: CodeMigrationContext,): Promise<ReadonlyMap<string, number>> => { const rows = await context.sql.unsafe<ConnectionCountRow[]>(` SELECT tenant, oauth_client_owner, oauth_client, COUNT(*)::int AS count FROM connection WHERE oauth_client IS NOT NULL GROUP BY tenant, oauth_client_owner, oauth_client `); const counts = new Map<string, number>(); for (const row of rows) { counts.set( referenceKey(row.tenant, row.oauth_client_owner, row.oauth_client), Number(row.count ?? 0), ); } return counts;}; export const gcDeadDcrOAuthClientsMigration: CodeMigration = { name: "2026-07-02-gc-dead-dcr-oauth-clients", run: async (context) => { const rows = await readOAuthClients(context); const referenceCounts = await readReferencingConnectionCounts(context); let deleted = 0; let backfilled = 0; let referencedDcr = 0; let stampedDcr = 0; let stampedManual = 0; for (const row of rows) { const isDcr = isDcrClassifiedRow(row); // Manual apps are never deleted or issuer-backfilled, but a legacy (null // origin_kind) manual row still gets an explicit `manual` stamp so every // surviving row ends this migration with a concrete classification. if (!isDcr) { if (row.origin_kind == null) { if (!context.dryRun) { await context.sql.unsafe( `UPDATE oauth_client SET origin_kind = 'manual' WHERE tenant = $1 AND owner = $2 AND slug = $3`, [row.tenant, row.owner, row.slug], ); } stampedManual += 1; } continue; } const count = referenceCounts.get(referenceKey(row.tenant, row.owner, row.slug)) ?? 0; const decision = classifyOAuthClientGc(row, count); if (decision.action === "delete") { if (!context.dryRun) { await context.sql.unsafe( `DELETE FROM oauth_client WHERE tenant = $1 AND owner = $2 AND slug = $3`, [row.tenant, row.owner, row.slug], ); } deleted += 1; continue; } referencedDcr += 1; // Surviving DCR row. Stamp a legacy (null origin_kind) survivor as DCR, // and backfill its `origin_issuer` from the registrable origin of // token_url when it has none, so the per-AS reuse lookup keys on it and // mints no new duplicate. Both fold into one UPDATE per row. const setClauses: string[] = []; const setArgs: unknown[] = []; if (row.origin_kind == null) { setClauses.push(`origin_kind = 'dynamic_client_registration'`); stampedDcr += 1; } if (row.origin_issuer == null) { const issuer = row.token_url == null ? null : registrableOriginOfUrl(row.token_url); if (issuer !== null) { setArgs.push(issuer); setClauses.push(`origin_issuer = $${setArgs.length}`); backfilled += 1; } } if (setClauses.length > 0 && !context.dryRun) { const whereBase = setArgs.length; await context.sql.unsafe( `UPDATE oauth_client SET ${setClauses.join(", ")} WHERE tenant = $${whereBase + 1} AND owner = $${whereBase + 2} AND slug = $${whereBase + 3}`, [...setArgs, row.tenant, row.owner, row.slug], ); } } const verb = context.dryRun ? "would delete" : "deleted"; const backfillVerb = context.dryRun ? "would backfill" : "backfilled"; const stampVerb = context.dryRun ? "would stamp" : "stamped"; return { summary: `${rows.length} oauth_client row(s): ${verb} ${deleted} orphaned DCR client(s), ` + `${backfillVerb} ${backfilled} of ${referencedDcr} referenced DCR client(s), ` + `${stampVerb} ${stampedDcr + stampedManual} legacy row(s) ` + `(${stampedDcr} dcr, ${stampedManual} manual)`, }; },};
// ---------------------------------------------------------------------------
// Cloud code migration: GC dead DCR `oauth_client` rows + backfill the
// surviving ones' `origin_issuer` (issue #1120, Part C).
//
// The cloud counterpart to the local libSQL boot migration
// (`sqlite-oauth-client-gc-migration` in @executor-js/sdk). It runs through the
// stamped `cloud_code_migration` ledger (see runner.ts), out-of-band via
// `scripts/migrate.ts`, with `--dry-run` support and an advisory lock.
//
// It is a CODE migration, not a numbered `.sql` one, deliberately: the DCR
// classification and the registrable-origin backfill are the SAME TypeScript
// predicates the local path and the runtime reuse lookup use
// (`classifyOAuthClientGc`, `registrableOriginOfUrl`). Re-encoding the eTLD+1
// registrable-origin logic and the DCR heuristic as Postgres regex/SQL would be
// a second, drift-prone source of truth for a migration that DELETES user data.
// Reading the (small) oauth_client table and deciding in-process keeps exactly
// one source of truth across both hosts.
// ---------------------------------------------------------------------------
import {
classifyOAuthClientGc,
isDcrClassifiedRow,
registrableOriginOfUrl,
} from "@executor-js/sdk";
import type { CodeMigration, CodeMigrationContext } from "./runner";
interface OAuthClientRow {
readonly tenant: string;
readonly owner: string;
readonly slug: string;
readonly grant: string | null;
readonly resource: string | null;
readonly origin_kind: string | null;
readonly origin_issuer: string | null;
readonly token_url: string | null;
}
const readOAuthClients = (context: CodeMigrationContext): Promise<readonly OAuthClientRow[]> =>
// `grant` is a reserved word in Postgres, so it must be quoted. Deterministic
// row order keeps the dry-run report + logs stable.
context.sql.unsafe<OAuthClientRow[]>(`
SELECT
tenant,
owner,
slug,
"grant",
resource,
origin_kind,
origin_issuer,
token_url
FROM oauth_client
ORDER BY tenant, owner, slug
`);
interface ConnectionCountRow {
readonly tenant: string;
readonly oauth_client_owner: string;
readonly oauth_client: string;
readonly count: string | number;
}
const referenceKey = (tenant: string, owner: string, slug: string): string =>
`${tenant}\u0000${owner}\u0000${slug}`;
/** One grouped pass over `connection` instead of a COUNT per oauth_client row
* (N+1). Returns a Map from (tenant, oauth_client_owner, oauth_client) to the
* number of referencing connections. */
const readReferencingConnectionCounts = async (
context: CodeMigrationContext,
): Promise<ReadonlyMap<string, number>> => {
const rows = await context.sql.unsafe<ConnectionCountRow[]>(`
SELECT tenant, oauth_client_owner, oauth_client, COUNT(*)::int AS count
FROM connection
WHERE oauth_client IS NOT NULL
GROUP BY tenant, oauth_client_owner, oauth_client
`);
const counts = new Map<string, number>();
for (const row of rows) {
counts.set(
referenceKey(row.tenant, row.oauth_client_owner, row.oauth_client),
Number(row.count ?? 0),
);
}
return counts;
};
export const gcDeadDcrOAuthClientsMigration: CodeMigration = {
name: "2026-07-02-gc-dead-dcr-oauth-clients",
run: async (context) => {
const rows = await readOAuthClients(context);
const referenceCounts = await readReferencingConnectionCounts(context);
let deleted = 0;
let backfilled = 0;
let referencedDcr = 0;
let stampedDcr = 0;
let stampedManual = 0;
for (const row of rows) {
const isDcr = isDcrClassifiedRow(row);
// Manual apps are never deleted or issuer-backfilled, but a legacy (null
// origin_kind) manual row still gets an explicit `manual` stamp so every
// surviving row ends this migration with a concrete classification.
if (!isDcr) {
if (row.origin_kind == null) {
if (!context.dryRun) {
await context.sql.unsafe(
`UPDATE oauth_client SET origin_kind = 'manual'
WHERE tenant = $1 AND owner = $2 AND slug = $3`,
[row.tenant, row.owner, row.slug],
);
}
stampedManual += 1;
}
continue;
}
const count = referenceCounts.get(referenceKey(row.tenant, row.owner, row.slug)) ?? 0;
const decision = classifyOAuthClientGc(row, count);
if (decision.action === "delete") {
if (!context.dryRun) {
await context.sql.unsafe(
`DELETE FROM oauth_client WHERE tenant = $1 AND owner = $2 AND slug = $3`,
[row.tenant, row.owner, row.slug],
);
}
deleted += 1;
continue;
}
referencedDcr += 1;
// Surviving DCR row. Stamp a legacy (null origin_kind) survivor as DCR,
// and backfill its `origin_issuer` from the registrable origin of
// token_url when it has none, so the per-AS reuse lookup keys on it and
// mints no new duplicate. Both fold into one UPDATE per row.
const setClauses: string[] = [];
const setArgs: unknown[] = [];
if (row.origin_kind == null) {
setClauses.push(`origin_kind = 'dynamic_client_registration'`);
stampedDcr += 1;
}
if (row.origin_issuer == null) {
const issuer = row.token_url == null ? null : registrableOriginOfUrl(row.token_url);
if (issuer !== null) {
setArgs.push(issuer);
setClauses.push(`origin_issuer = $${setArgs.length}`);
backfilled += 1;
}
}
if (setClauses.length > 0 && !context.dryRun) {
const whereBase = setArgs.length;
await context.sql.unsafe(
`UPDATE oauth_client SET ${setClauses.join(", ")}
WHERE tenant = $${whereBase + 1} AND owner = $${whereBase + 2} AND slug = $${whereBase + 3}`,
[...setArgs, row.tenant, row.owner, row.slug],
);
}
}
const verb = context.dryRun ? "would delete" : "deleted";
const backfillVerb = context.dryRun ? "would backfill" : "backfilled";
const stampVerb = context.dryRun ? "would stamp" : "stamped";
return {
summary:
`${rows.length} oauth_client row(s): ${verb} ${deleted} orphaned DCR client(s), ` +
`${backfillVerb} ${backfilled} of ${referencedDcr} referenced DCR client(s), ` +
`${stampVerb} ${stampedDcr + stampedManual} legacy row(s) ` +
`(${stampedDcr} dcr, ${stampedManual} manual)`,
};
},
};