Migrate from Better Auth

SELECT count(*) AS users FROM "user";
SELECT "providerId", count(*) AS accounts FROM "account" GROUP BY "providerId" ORDER BY 2 DESC;
SELECT count(*) AS organizations FROM "organization";
SELECT "role", count(*) AS members FROM "member" GROUP BY "role" ORDER BY 2 DESC;
SELECT count(*) AS pending_invitations FROM "invitation" WHERE "status" = 'pending';

import { betterIam } from 'better-iam';
import { postgresAdapter } from 'better-iam/adapter-postgres';
import { createOAuthLogin } from 'better-iam/oauth';
import { sendMail } from './mail';

export const iam = betterIam({
  database: postgresAdapter({ connectionString: process.env.DATABASE_URL! }),
  secret: process.env.BETTER_IAM_SECRET!,
  baseURL: process.env.BETTER_IAM_BASE_URL!,
  authentication: {
    passwordlessEmail: true, // magic links and emailed codes, for people who used a password
    sendEmail: async (message) => sendMail(message),
  },
  permissions: {
    // Keeps the Better Auth user ID on each identity, readable in policies as principal.legacyId.
    identityAttributes: { legacyId: 'string' },
    resourceTypes: {
      project: { actions: ['projects:read', 'projects:write', 'projects:delete'] },
    },
  },
});

// One connection per provider and organization: every connection belongs to one tenant. createOAuthLogin refuses a
// connection without a tenant ID, so the connection is added only once the tenant exists (next step).
const acmeTenantId = process.env.ACME_TENANT_ID;
if (acmeTenantId)
  iam.useProtocol(
    createOAuthLogin({
      ...iam.protocolHost,
      basePath: '/api/iam/oauth/login',
      trustedOrigins: ['https://app.example.com'],
      connections: [
        {
          id: 'acme-google',
          tenantId: acmeTenantId,
          kind: 'google',
          clientId: process.env.GOOGLE_CLIENT_ID!,
          clientSecret: process.env.GOOGLE_CLIENT_SECRET!,
          redirectUri: 'https://app.example.com/api/iam/oauth/acme-google/callback',
        },
      ],
    }),
  );

npx better-iam login --config better-iam.config.mjs --tenant <root-tenant-id> --email root@example.com
export BETTER_IAM_TOKEN=$(npx better-iam token --config better-iam.config.mjs)
export ROOT_TENANT_ID=<root-tenant-id>
export BETTER_AUTH_DATABASE_URL=postgres://...

import pg from 'pg';
import { iam } from '../../iam';

const source = new pg.Pool({ connectionString: process.env.BETTER_AUTH_DATABASE_URL });
const admin = { token: process.env.BETTER_IAM_TOKEN! };
const rootTenantId = process.env.ROOT_TENANT_ID!;

await source.query(`
  CREATE TABLE IF NOT EXISTS iam_migration_tenants (
    organization_id text PRIMARY KEY,
    tenant_id text NOT NULL,
    owner_email text NOT NULL
  )`);

const { rows: organizations } = await source.query<{
  id: string;
  name: string;
  slug: string | null;
  ownerEmail: string | null;
  ownerName: string | null;
  ownerEmailVerified: boolean | null;
}>(`
  SELECT o.id, o.name, o.slug,
    owner.email AS "ownerEmail", owner.name AS "ownerName", owner."emailVerified" AS "ownerEmailVerified"
  FROM "organization" o
  LEFT JOIN LATERAL (
    SELECT u.email, u.name, u."emailVerified" FROM "member" m JOIN "user" u ON u.id = m."userId"
     WHERE m."organizationId" = o.id AND 'owner' = ANY(string_to_array(m.role, ','))
     ORDER BY m."createdAt" LIMIT 1) owner ON true
  WHERE NOT EXISTS (SELECT 1 FROM iam_migration_tenants t WHERE t.organization_id = o.id)
  ORDER BY o."createdAt"`);

for (const organization of organizations) {
  if (!organization.ownerEmail) {
    console.warn(`Skipped ${organization.name}: no owner`);
    continue;
  }
  const { tenant } = await iam.api.tenants.create(admin, {
    parentId: rootTenantId,
    type: 'organization',
    name: organization.name,
    // Better IAM slugs are 1-63 lowercase letters, digits, or hyphens, and unique (SLUG_TAKEN otherwise).
    ...(organization.slug ? { slug: organization.slug.toLowerCase() } : {}),
    ownerEmail: organization.ownerEmail,
    activate: true, // active now; the owner's account is created without an invitation
    ...(organization.ownerName?.trim() ? { ownerName: organization.ownerName.trim() } : {}),
    ownerEmailVerified: organization.ownerEmailVerified === true,
  });
  await source.query(
    'INSERT INTO iam_migration_tenants (organization_id, tenant_id, owner_email) VALUES ($1, $2, $3)',
    [organization.id, tenant.id, organization.ownerEmail],
  );
  console.log(`${organization.name} -> ${tenant.id}`);
}

await source.end();

import pg from 'pg';
import { iam } from '../../iam';

type CredentialInput = Parameters<typeof iam.api.roles.list>[0];
type Link = { providerId: string; issuer: string };

const source = new pg.Pool({ connectionString: process.env.BETTER_AUTH_DATABASE_URL });
const admin: CredentialInput = { token: process.env.BETTER_IAM_TOKEN! };

/**
 * Better Auth account.providerId -> the OAuth connection that replaces it, per tenant (the connections in iam.ts).
 * providerId is the connection's id; issuer is the provider's `iss`, exactly. account.accountId is the subject:
 * Google's `sub`, or the numeric GitHub user ID.
 */
const connections: Record<string, Record<string, Link>> = {
  [process.env.ACME_TENANT_ID!]: {
    google: { providerId: 'acme-google', issuer: 'https://accounts.google.com' },
    github: { providerId: 'acme-github', issuer: 'https://github.com' }, // a kind: 'github' connection
  },
};

const roleDefinitions = [
  {
    name: 'Admin',
    description: 'Manages projects and members',
    permissions: [
      'projects:read',
      'projects:write',
      'projects:delete',
      'iam:identities:create',
      'iam:identities:read',
      'iam:bindings:create',
      'iam:bindings:read',
      'iam:bindings:delete',
      'iam:roles:read',
    ],
  },
  { name: 'Member', description: 'Reads and edits projects', permissions: ['projects:read', 'projects:write'] },
];

/** Better Auth role name -> Better IAM role name. `owner` is handled by ownership, not a role binding. */
const roleNames: Record<string, string> = { admin: 'Admin', member: 'Member' };

async function ensureRoles(tenantId: string): Promise<Map<string, string>> {
  const existing = await iam.api.roles.list(admin, { tenantId });
  const ids = new Map(existing.map((role) => [role.name, role.id]));
  for (const definition of roleDefinitions)
    if (!ids.has(definition.name)) {
      const role = await iam.api.roles.create(admin, { tenantId, ...definition });
      ids.set(role.name, role.id);
    }
  return ids;
}

await source.query(`
  CREATE TABLE IF NOT EXISTS iam_migration_users (
    legacy_user_id text NOT NULL,
    tenant_id text NOT NULL,
    identity_id text NOT NULL,
    PRIMARY KEY (legacy_user_id, tenant_id)
  )`);

const { rows: tenants } = await source.query<{
  organization_id: string;
  tenant_id: string;
  owner_email: string;
}>('SELECT organization_id, tenant_id, owner_email FROM iam_migration_tenants');

for (const { organization_id, tenant_id: tenantId, owner_email } of tenants) {
  const tenant = await iam.api.tenants.get(admin, { tenantId });
  const roles = await ensureRoles(tenantId);

  const { rows: members } = await source.query<{
    id: string;
    email: string;
    name: string | null;
    emailVerified: boolean;
    role: string;
  }>(
    `SELECT u.id, u.email, u.name, u."emailVerified", m.role
       FROM "member" m JOIN "user" u ON u.id = m."userId"
      WHERE m."organizationId" = $1
        AND NOT EXISTS (SELECT 1 FROM iam_migration_users x
                         WHERE x.legacy_user_id = u.id AND x.tenant_id = $2)
      ORDER BY u.id`,
    [organization_id, tenantId],
  );

  // Each member's social accounts that have a connection in this tenant become links ('credential' never does).
  const { rows: accounts } = await source.query<{ userId: string; providerId: string; accountId: string }>(
    'SELECT "userId", "providerId", "accountId" FROM "account" WHERE "userId" = ANY($1)',
    [members.map((member) => member.id)],
  );
  const links = new Map<string, Array<Link & { subject: string }>>();
  for (const account of accounts) {
    const target = connections[tenantId]?.[account.providerId];
    if (target)
      links.set(account.userId, [...(links.get(account.userId) ?? []), { ...target, subject: account.accountId }]);
  }

  // The owner already exists: tenants.create created them. Record their mapping, link their providers, skip them.
  const owner = members.find((member) => member.email.toLowerCase() === owner_email.toLowerCase());
  if (owner) {
    const [identity] = await iam.api.identities.list(admin, { tenantId, query: owner_email, limit: 1 });
    if (identity) {
      await iam.api.identities.update(admin, { tenantId, identityId: identity.id, attributes: { legacyId: owner.id } });
      for (const link of links.get(owner.id) ?? [])
        await iam.api.identities.linkProvider(admin, { tenantId, identityId: identity.id, ...link });
      await source.query(
        'INSERT INTO iam_migration_users (legacy_user_id, tenant_id, identity_id) VALUES ($1, $2, $3)',
        [owner.id, tenantId, identity.id],
      );
    }
  }
  const pending = members.filter((member) => member !== owner);

  for (let start = 0; start < pending.length; start += 100) {
    const batch = pending.slice(start, start + 100);
    const { identities } = await iam.api.identities.createMany(admin, {
      tenantId,
      identities: batch.map((member) => ({
        email: member.email,
        name: member.name?.trim() || member.email.split('@')[0]!,
        emailVerified: member.emailVerified === true,
        providers: links.get(member.id) ?? [],
        attributes: { legacyId: member.id },
        roleIds: member.role
          .split(',')
          .map((name) => roles.get(roleNames[name.trim()] ?? ''))
          .filter((id): id is string => id !== undefined),
      })),
    });
    // identities[i] replaces batch[i].
    for (const [i, identity] of identities.entries())
      await source.query(
        'INSERT INTO iam_migration_users (legacy_user_id, tenant_id, identity_id) VALUES ($1, $2, $3)',
        [batch[i]!.id, tenantId, identity.id],
      );
    console.log(`${tenant.name}: ${start + batch.length}/${pending.length}`);
  }
}

await source.end();

import pg from 'pg';
import { iam } from '../../iam';

const source = new pg.Pool({ connectionString: process.env.BETTER_AUTH_DATABASE_URL });
const admin = { token: process.env.BETTER_IAM_TOKEN! };

const { rows: owners } = await source.query<{ tenant_id: string; identity_id: string }>(`
  SELECT x.tenant_id, x.identity_id
    FROM iam_migration_users x
    JOIN iam_migration_tenants t ON t.tenant_id = x.tenant_id
    JOIN "member" m ON m."userId" = x.legacy_user_id AND m."organizationId" = t.organization_id
    JOIN "user" u ON u.id = x.legacy_user_id
   WHERE 'owner' = ANY(string_to_array(m.role, ',')) AND lower(u.email) <> lower(t.owner_email)`);

for (const owner of owners)
  await iam.api.identities.setOwner(admin, {
    tenantId: owner.tenant_id,
    identityId: owner.identity_id,
    owner: true,
  });

// Admin plugin only: remove this query if your "user" table has no "banned" column.
const { rows: banned } = await source.query<{ tenant_id: string; identity_id: string }>(`
  SELECT x.tenant_id, x.identity_id
    FROM iam_migration_users x JOIN "user" u ON u.id = x.legacy_user_id
   WHERE u.banned = true`);

for (const person of banned)
  await iam.api.identities.setStatus(admin, {
    tenantId: person.tenant_id,
    identityId: person.identity_id,
    status: 'disabled',
  });

await source.end();

import pg from 'pg';
import { iam } from '../../iam';

const source = new pg.Pool({ connectionString: process.env.BETTER_AUTH_DATABASE_URL });
const admin = { token: process.env.BETTER_IAM_TOKEN! };
const roleNames: Record<string, string> = { admin: 'Admin', member: 'Member' };

const { rows: invitations } = await source.query<{ tenant_id: string; email: string; role: string | null }>(`
  SELECT t.tenant_id, i.email, i.role
    FROM "invitation" i JOIN iam_migration_tenants t ON t.organization_id = i."organizationId"
   WHERE i.status = 'pending' AND i."expiresAt" > now()`);

for (const invitation of invitations) {
  const roles = await iam.api.roles.list(admin, { tenantId: invitation.tenant_id });
  const role = roles.find((candidate) => candidate.name === roleNames[invitation.role ?? 'member']);
  await iam.api.identities.invite(admin, {
    tenantId: invitation.tenant_id,
    email: invitation.email,
    roleIds: role ? [role.id] : [],
  });
}

await iam.auth.dispatchOutbox();
await source.end();

import pg from 'pg';
import { iam } from '../../iam';

const source = new pg.Pool({ connectionString: process.env.BETTER_AUTH_DATABASE_URL });
const admin = { token: process.env.BETTER_IAM_TOKEN! };

// People who signed in with a password in Better Auth.
const { rows } = await source.query<{ tenant_id: string; identity_id: string }>(`
  SELECT x.tenant_id, x.identity_id
    FROM iam_migration_users x
   WHERE EXISTS (SELECT 1 FROM "account" a
                  WHERE a."userId" = x.legacy_user_id AND a."providerId" = 'credential')
   LIMIT 200`);

for (const row of rows)
  await iam.api.identities.requestPasswordReset(admin, { tenantId: row.tenant_id, identityId: row.identity_id });

await iam.auth.dispatchOutbox();
await source.end();