migrate-auth

name

migrate-auth

description

Use when migrating Supabase auth.users (and optionally auth.identities for OAuth) to modern InsForge auth.users + auth.user_providers. Preserves UUIDs (critical for FK integrity). Path A (direct Postgres, SUPABASE_DB_URL) copies bcrypt hashes so users keep passwords. Path B (no DB access — only a service_role / sb_secret_ key) exports users via the GoTrue Admin API and closes the password gap with a JIT bridge or reset.

Migrate Auth (Supabase → modern InsForge)

When to invoke

• Orchestrator dispatched to this skill
• migrate-database schema load complete (tables exist, RLS policies exist)
• BEFORE migrate-database data load — user rows must exist before public.* data with FK-ish references

When NOT to invoke

• Legacy InsForge target (_accounts instead of auth.users) → use reference toolkit
• Source uses MFA / SSO / SAML / webauthn — those tables are not portable; surface to user

Choose your path FIRST

Everything hinges on one question: can you reach the source Postgres directly?

You have…	Path	Password continuity
SUPABASE_DB_URL (DB password from Settings → Database)	Path A — direct DB	bcrypt hashes copy byte-for-byte; zero user impact
Only a service_role JWT or sb_secret_... key (locked out of the dashboard, or that's all you were given)	Path B — Admin API	hashes are unreachable; use JIT bridge (seamless) or reset email

Why Path B exists: the source DB password lives in a dashboard you may not be able to log into, and a service key over PostgREST only sees the public/storage schemas — never auth. But the same service key is also the GoTrue admin credential, so GET /auth/v1/admin/users lists every user (UUIDs, emails, metadata, OAuth identities) without any DB connection string. The one thing the Admin API will not return is encrypted_password — hence the separate password step.

Key-tier note (both keys are worth capturing up front): the service_role JWT and the newer sb_secret_... key both work as admin credentials for the export. The anon / sb_publishable_... key does NOT — admin endpoints reject it with 401. The publishable/anon key is still needed, but only for Path B's JIT password check (a public endpoint).

---

Path A — direct Postgres access

Inputs required

SUPABASE_DB_URL      # postgresql://postgres.<ref>:<pw>@<pooler>:6543/postgres
INSFORGE_DB_URL      # postgresql://postgres:<pw>@<host>:5432/insforge?sslmode=require

Diagnostic probe

export PGPASSWORD='<supabase-password>'
psql "$SUPABASE_DB_URL" -c "
SELECT
  (SELECT count(*) FROM auth.users) AS source_users,
  (SELECT count(*) FROM auth.users WHERE encrypted_password IS NOT NULL) AS with_password,
  (SELECT count(*) FROM auth.users WHERE is_anonymous) AS anonymous_users,
  (SELECT count(*) FROM auth.identities) AS source_identities,
  (SELECT count(*) FROM auth.mfa_factors) AS mfa_factors,
  (SELECT count(*) FROM auth.sso_providers) AS sso_providers;
"

Decision:

• mfa_factors > 0 → warn user: MFA factors will NOT migrate; users with MFA will have to re-enroll.
• sso_providers > 0 → warn user: SSO config must be re-entered in InsForge manually.
• source_identities where provider <> 'email' → these are OAuth linkages; preserve as auth.user_providers rows.
• source_identities where provider = 'email' → skip. These are redundant with auth.users.password on InsForge.

export PGPASSWORD='<insforge-password>'
psql "$INSFORGE_DB_URL" -c "
SELECT column_name, data_type, is_nullable FROM information_schema.columns
  WHERE table_schema='auth' AND table_name='users' ORDER BY ordinal_position;
"

Expected modern shape: id, email, password, email_verified, created_at, updated_at, profile, metadata, is_project_admin, is_anonymous (10 columns). If the column set differs (e.g., new required column added), STOP — surface to user.

Procedure

1. Export users as upsertable INSERT statements (preserves UUIDs + bcrypt)

export PGPASSWORD='<supabase-password>'
psql "$SUPABASE_DB_URL" -t -A -c "
SELECT format(
  'INSERT INTO auth.users (id, email, password, email_verified, created_at, updated_at, metadata, profile, is_anonymous) VALUES (%L, %L, %L, %L, %L, %L, %L, %L, %L) ON CONFLICT (id) DO UPDATE SET email=EXCLUDED.email, password=EXCLUDED.password, metadata=EXCLUDED.metadata, profile=EXCLUDED.profile, updated_at=EXCLUDED.updated_at;',
  id, email, encrypted_password,
  (email_confirmed_at IS NOT NULL)::boolean,
  created_at, updated_at,
  COALESCE(raw_user_meta_data, '{}'::jsonb),
  COALESCE(raw_app_meta_data, '{}'::jsonb),
  is_anonymous
) FROM auth.users ORDER BY created_at;
" > auth-import.sql
wc -l auth-import.sql  # should equal source_users count

Column mapping:

Supabase auth.users	InsForge auth.users	Notes
id (uuid)	id (uuid)	preserve — FK integrity depends on it
email	email
encrypted_password	password	bcrypt $2a$/$2b$ — direct copy, users keep passwords
email_confirmed_at IS NOT NULL	email_verified (boolean)
raw_user_meta_data (jsonb)	metadata (jsonb)
raw_app_meta_data (jsonb)	profile (jsonb)
is_anonymous	is_anonymous
created_at, updated_at	same
phone, email_change_*, recovery_*, confirmation_*, banned_until, reauthentication_*, *_token*	—	dropped; not supported in InsForge core

2. Apply to target

export PGPASSWORD='<insforge-password>'
psql "$INSFORGE_DB_URL" -v ON_ERROR_STOP=0 -f auth-import.sql 2>&1 | tail -10

Every line should be INSERT 0 1.

3. Migrate OAuth identities (skip if all source identities are email)

export PGPASSWORD='<supabase-password>'
psql "$SUPABASE_DB_URL" -t -A -c "
SELECT format(
  'INSERT INTO auth.user_providers (id, user_id, provider, provider_account_id, provider_data, created_at, updated_at) VALUES (gen_random_uuid(), %L, %L, %L, %L, %L, %L) ON CONFLICT DO NOTHING;',
  user_id, provider, provider_id,
  COALESCE(identity_data, '{}'::jsonb),
  created_at, updated_at
) FROM auth.identities WHERE provider <> 'email' ORDER BY created_at;
" > identities-import.sql

export PGPASSWORD='<insforge-password>'
psql "$INSFORGE_DB_URL" -v ON_ERROR_STOP=0 -f identities-import.sql 2>&1 | tail -5

NOTE: Supabase auth.identities does not store OAuth access/refresh tokens — only the linkage. auth.user_providers.access_token / refresh_token stay NULL. Users will have to re-authorize on their first sign-in after migration.

---

Path B — Admin API export (no direct Postgres on the source)

Use this whenever you cannot get SUPABASE_DB_URL. You still need write access to the target — either INSFORGE_DB_URL (preferred, keeps UUID-preserving SQL INSERT) or the InsForge CLI linked to the project. Only the source side changes.

Inputs required

SUPABASE_URL              # https://<ref>.supabase.co
SUPABASE_SERVICE_KEY      # service_role JWT  OR  sb_secret_...   (NOT anon/publishable)
SUPABASE_ANON_KEY         # anon JWT OR sb_publishable_...        (only for JIT password step)
INSFORGE_DB_URL           # target write access (same as Path A)

Diagnostic probe (confirm the key really is admin-tier)

curl -s -o /dev/null -w '%{http_code}\n' \
  -H "apikey: $SUPABASE_SERVICE_KEY" -H "Authorization: Bearer $SUPABASE_SERVICE_KEY" \
  "$SUPABASE_URL/auth/v1/admin/users?page=1&per_page=1"

• 200 → key is service_role/secret; proceed.
• 401 → key is anon/publishable. STOP — you cannot export users with it. Ask the user for the service_role or an sb_secret_... key (Supabase → Settings → API, or via supabase projects api-keys --project-ref <ref> which does NOT need the DB password).

Procedure

1. Export + transform via the Admin API

export SUPABASE_URL='https://<ref>.supabase.co'
export SUPABASE_SERVICE_KEY='<service_role or sb_secret_ key>'
./export-via-api.sh

export-via-api.sh paginates /auth/v1/admin/users, then writes the SAME target shape Path A produces:

• auth-import.sql — INSERT ... ON CONFLICT into auth.users, UUIDs preserved, password left empty.
• identities-import.sql — OAuth identities → auth.user_providers (email identities filtered out).
• password-migration-manifest.json — every user with no portable hash, split into password_only vs oauth.

API → InsForge column mapping (what the Admin API can and cannot give you):

Admin API field	InsForge auth.users	Notes
id (uuid)	id	preserve — same FK-integrity reason as Path A
email	email
— (never returned)	password	written as '' → filled later by JIT bridge or reset
email_confirmed_at present	email_verified (boolean)
user_metadata	metadata (jsonb)
app_metadata	profile (jsonb)
is_anonymous	is_anonymous
identities[] (provider ≠ email)	auth.user_providers rows	provider account id from provider_id/id/identity_data.sub
phone, banned_until, confirmation_*, ...	—	dropped, as in Path A

2. Apply to target

Identical to Path A step 2 — the SQL files have the same shape:

export PGPASSWORD='<insforge-password>'
psql "$INSFORGE_DB_URL" -v ON_ERROR_STOP=0 -f auth-import.sql       2>&1 | tail -10
psql "$INSFORGE_DB_URL" -v ON_ERROR_STOP=0 -f identities-import.sql 2>&1 | tail -5

At this point every user exists on InsForge with the correct UUID and metadata. OAuth users are fully done — they log in through the provider, matched back by UUID/identity. Only email/password users still can't sign in, because their row has an empty password. Close that gap with 2a or 2b.

3a. Seamless: JIT (trickle) password migration — recommended

Keep the old Supabase project running through a transition window and deploy jit-password-bridge.ts (an InsForge edge function). On a user's first login it verifies the plaintext against old Supabase (POST /auth/v1/token?grant_type=password, which only needs the anon/publishable key), and on success writes the bcrypt hash into InsForge. Every later login is native. Users never notice.

npx @insforge/cli secrets add SUPABASE_URL      "$SUPABASE_URL"
npx @insforge/cli secrets add SUPABASE_ANON_KEY "$SUPABASE_ANON_KEY"
npx @insforge/cli secrets add INSFORGE_DB_URL   "$INSFORGE_DB_URL"
npx @insforge/cli functions deploy jit-password-bridge --file ./jit-password-bridge.ts

Wire the frontend to call the bridge before native login (see the header of jit-password-bridge.ts). After a few weeks, send reset emails (3b) to whoever in the manifest still has an empty password, then retire the Supabase project.

3b. Simple: forced reset

Skip the bridge. Send an InsForge password-reset email to every password_only user in the manifest; they set a new password on first return. One day of work, worse UX, but no dependency on keeping Supabase alive.

python3 -c "import json;print('\n'.join(u['email'] for u in json.load(open('password-migration-manifest.json'))['users'] if u['password_only'] and u['email']))" > reset-list.txt

---

Verification (both paths)

psql "$INSFORGE_DB_URL" -c "
SELECT count(*) AS total,
       count(*) FILTER (WHERE password LIKE '\$2%') AS with_bcrypt,
       count(*) FILTER (WHERE password = '') AS empty_password,
       count(*) FILTER (WHERE email_verified) AS verified,
       count(*) FILTER (WHERE is_anonymous) AS anon
FROM auth.users;
"

Expected: total ≥ source_users. (May be higher if target had pre-existing users — e.g., the InsForge admin account.)

• Path A: with_bcrypt ≈ source with_password; empty_password ≈ 0.
• Path B: empty_password starts ≈ the email/password user count and shrinks as the JIT bridge backfills hashes (or drops to 0 after everyone resets).

# Cross-check a specific source user exists on target with same UUID.
# Path A also checks the bcrypt prefix matches; Path B users start with empty password.
psql "$INSFORGE_DB_URL"  -c "SELECT id, email, LEFT(password, 7) AS bcp FROM auth.users WHERE email = '<known-email>';"

Path A: id, email, and bcp (e.g., $2a$10$) all match the source. Path B: id + email match; bcp is empty until JIT/reset runs.

Common pitfalls

• UUID regeneration: if you create users via InsForge's auth HTTP API (POST /api/auth/users) instead of direct SQL, new IDs are generated and every FK in your public schema breaks. Always use direct SQL INSERT with ON CONFLICT to preserve IDs — this is exactly why Path B still writes SQL against INSFORGE_DB_URL rather than calling the InsForge signup API.
• Wrong key tier on Path B: a 401 from /auth/v1/admin/users means you handed it an anon/publishable key. Admin export needs service_role or sb_secret_.... You can pull the right key with supabase projects api-keys --project-ref <ref> (Management API — no DB password needed) even when locked out of the dashboard UI.
• Source email identities are redundant: Supabase creates an auth.identities row for email signups too (provider='email'). Both paths filter these out — auth.user_providers is for OAuth only.
• Password hash verification (Path A): bcrypt $2a$/$2b$ from Supabase work on InsForge as-is. A different prefix (e.g., $argon2) means a custom hashing plugin and direct copy won't work — surface to user. (Path B sidesteps this: it never touches the hash; the JIT bridge re-hashes with InsForge-native bcrypt on first login.)
• Active sessions die: InsForge signs JWTs with its own key, so Supabase-minted JWTs are invalid immediately. Active sessions fail to renew. Plan the cutover when user impact is acceptable.
• app_metadata vs user_metadata convention: Supabase uses app_metadata for admin-set data (role, provider) and user_metadata for user-editable. Both paths map app_metadata → profile, user_metadata → metadata. If your app reads these via SDK, match the split in the frontend.
• Anonymous users: is_anonymous=true preserves directly on both paths. They have no password and no OAuth identity — nothing to migrate beyond the row.
• Path B needs the old project alive for JIT: the trickle bridge only works while old Supabase can still verify passwords. If you tear Supabase down immediately, you must use the forced-reset path (3b).

Scope boundary

Does not cover: MFA factors, SSO/SAML providers, one-time tokens, webauthn credentials, refresh tokens, sessions, audit_log_entries. All of those either don't migrate (MFA/webauthn) or don't survive platform change (sessions/JWT). Flag to user as manual follow-ups.