/* oxlint-disable executor/no-try-catch-or-throw -- boundary: out-of-band migration script over a raw postgres connection */// ---------------------------------------------------------------------------// One-off data migration: re-file mis-partitioned WorkOS Vault metadata rows.//// A bug in the v1.5 vault provider (`ownerOf(binding)`) filed every credential// created by a bound user — including ORG-shared connections — under that// user's private partition. Org-shared credentials then resolved only for// whoever pasted them; every other org member got `connection_value_missing`.//// The fix (secret-store.ts: `ownerForItemId`) files by the owner embedded in// the item id. This script repairs the rows already written wrong: an item id// of `connection:org:…` / `oauth:org:…` / `oauth-client:org:…` whose metadata// row sits at owner='user' is moved to owner='org', subject=''. The Vault// object itself is untouched (flat context) — only the metadata pointer moves.//// bun run db:repartition-vault:prod # op run --env-file=.env.production// bun run db:repartition-vault:dev # against the local PGlite dev db//// Idempotent — already-correct rows are skipped. Pass --dry-run to print the// plan without writing.// ---------------------------------------------------------------------------import postgres from "postgres";const VAULT_PLUGIN_ID = "workosVault";const METADATA_COLLECTION = "metadata";// Item-id prefixes whose second colon-segment is the owning partition.const OWNER_SCOPED_PREFIXES = ["connection", "oauth", "oauth-client"];const connectionString = process.env.DATABASE_URL;if (!connectionString) { console.error("DATABASE_URL is not set"); process.exit(1);}const dryRun = process.argv.includes("--dry-run");// Direct (non-Hyperdrive) connection — PlanetScale requires TLS.const sql = postgres(connectionString, { max: 1, prepare: false, ssl: "require" });type Row = { row_id: string; tenant: string; owner: string; subject: string; key: string;};const embeddedOwner = (key: string): "org" | "user" | null => { const [prefix, owner] = key.split(":"); if (!OWNER_SCOPED_PREFIXES.includes(prefix ?? "")) return null; return owner === "org" || owner === "user" ? owner : null;};try { const rows = await sql<Row[]>` SELECT row_id, tenant, owner, subject, key FROM plugin_storage WHERE plugin_id = ${VAULT_PLUGIN_ID} AND collection = ${METADATA_COLLECTION} `; // A row is mis-filed when its stored partition disagrees with the owner // embedded in its item id. In practice only org credentials stuck in a user // partition, but compute it generally and symmetrically. const misfiled = rows.filter((row) => { const want = embeddedOwner(row.key); if (want === null) return false; const wantSubject = want === "org" ? "" : row.subject; return row.owner !== want || row.subject !== wantSubject; }); console.log(`${rows.length} vault metadata row(s), ${misfiled.length} mis-partitioned`); const byPrefix = new Map<string, number>(); for (const row of misfiled) { const prefix = row.key.split(":")[0] ?? "?"; byPrefix.set(prefix, (byPrefix.get(prefix) ?? 0) + 1); } for (const [prefix, n] of byPrefix) console.log(` ${prefix}: ${n}`); if (dryRun) { for (const row of misfiled) { console.log( ` would move ${row.owner}/${row.subject || "''"} → ${embeddedOwner(row.key)} : ${row.key}`, ); } } else if (misfiled.length > 0) { let moved = 0; await sql.begin(async (tx) => { for (const row of misfiled) { const want = embeddedOwner(row.key)!; const wantSubject = want === "org" ? "" : row.subject; // Re-file in place: copy the data into the correct partition (no-op if a // post-fix write already created it), then drop the mis-filed row. await tx` INSERT INTO plugin_storage (row_id, tenant, owner, subject, plugin_id, collection, key, data, created_at, updated_at) SELECT row_id || '-repart', tenant, ${want}, ${wantSubject}, plugin_id, collection, key, data, created_at, now() FROM plugin_storage WHERE row_id = ${row.row_id} ON CONFLICT (tenant, owner, subject, plugin_id, collection, key) DO NOTHING `; await tx`DELETE FROM plugin_storage WHERE row_id = ${row.row_id}`; moved += 1; } }); console.log(`re-filed ${moved} row(s)`); }} finally { await sql.end();}
/* oxlint-disable executor/no-try-catch-or-throw -- boundary: out-of-band migration script over a raw postgres connection */
// ---------------------------------------------------------------------------
// One-off data migration: re-file mis-partitioned WorkOS Vault metadata rows.
//
// A bug in the v1.5 vault provider (`ownerOf(binding)`) filed every credential
// created by a bound user — including ORG-shared connections — under that
// user's private partition. Org-shared credentials then resolved only for
// whoever pasted them; every other org member got `connection_value_missing`.
//
// The fix (secret-store.ts: `ownerForItemId`) files by the owner embedded in
// the item id. This script repairs the rows already written wrong: an item id
// of `connection:org:…` / `oauth:org:…` / `oauth-client:org:…` whose metadata
// row sits at owner='user' is moved to owner='org', subject=''. The Vault
// object itself is untouched (flat context) — only the metadata pointer moves.
//
// bun run db:repartition-vault:prod # op run --env-file=.env.production
// bun run db:repartition-vault:dev # against the local PGlite dev db
//
// Idempotent — already-correct rows are skipped. Pass --dry-run to print the
// plan without writing.
// ---------------------------------------------------------------------------
import postgres from "postgres";
const VAULT_PLUGIN_ID = "workosVault";
const METADATA_COLLECTION = "metadata";
// Item-id prefixes whose second colon-segment is the owning partition.
const OWNER_SCOPED_PREFIXES = ["connection", "oauth", "oauth-client"];
const connectionString = process.env.DATABASE_URL;
if (!connectionString) {
console.error("DATABASE_URL is not set");
process.exit(1);
}
const dryRun = process.argv.includes("--dry-run");
// Direct (non-Hyperdrive) connection — PlanetScale requires TLS.
const sql = postgres(connectionString, { max: 1, prepare: false, ssl: "require" });
type Row = {
row_id: string;
tenant: string;
owner: string;
subject: string;
key: string;
};
const embeddedOwner = (key: string): "org" | "user" | null => {
const [prefix, owner] = key.split(":");
if (!OWNER_SCOPED_PREFIXES.includes(prefix ?? "")) return null;
return owner === "org" || owner === "user" ? owner : null;
};
try {
const rows = await sql<Row[]>`
SELECT row_id, tenant, owner, subject, key
FROM plugin_storage
WHERE plugin_id = ${VAULT_PLUGIN_ID} AND collection = ${METADATA_COLLECTION}
`;
// A row is mis-filed when its stored partition disagrees with the owner
// embedded in its item id. In practice only org credentials stuck in a user
// partition, but compute it generally and symmetrically.
const misfiled = rows.filter((row) => {
const want = embeddedOwner(row.key);
if (want === null) return false;
const wantSubject = want === "org" ? "" : row.subject;
return row.owner !== want || row.subject !== wantSubject;
});
console.log(`${rows.length} vault metadata row(s), ${misfiled.length} mis-partitioned`);
const byPrefix = new Map<string, number>();
for (const row of misfiled) {
const prefix = row.key.split(":")[0] ?? "?";
byPrefix.set(prefix, (byPrefix.get(prefix) ?? 0) + 1);
}
for (const [prefix, n] of byPrefix) console.log(` ${prefix}: ${n}`);
if (dryRun) {
for (const row of misfiled) {
console.log(
` would move ${row.owner}/${row.subject || "''"} → ${embeddedOwner(row.key)} : ${row.key}`,
);
}
} else if (misfiled.length > 0) {
let moved = 0;
await sql.begin(async (tx) => {
for (const row of misfiled) {
const want = embeddedOwner(row.key)!;
const wantSubject = want === "org" ? "" : row.subject;
// Re-file in place: copy the data into the correct partition (no-op if a
// post-fix write already created it), then drop the mis-filed row.
await tx`
INSERT INTO plugin_storage
(row_id, tenant, owner, subject, plugin_id, collection, key, data, created_at, updated_at)
SELECT row_id || '-repart', tenant, ${want}, ${wantSubject},
plugin_id, collection, key, data, created_at, now()
FROM plugin_storage WHERE row_id = ${row.row_id}
ON CONFLICT (tenant, owner, subject, plugin_id, collection, key) DO NOTHING
`;
await tx`DELETE FROM plugin_storage WHERE row_id = ${row.row_id}`;
moved += 1;
}
});
console.log(`re-filed ${moved} row(s)`);
}
} finally {
await sql.end();
}