User Migration | SanMarcSoft SOP Runbook

Overview

When transitioning between Clerk instances (e.g., testing to production, or switching Clerk plans), users must be migrated along with their associated D1 database records.

Prerequisites

Source Clerk secret key: pass verifieddit/clerk/test-secret-key (or source instance key)
Target Clerk secret key: pass verifieddit/clerk/secret-key (or target instance key)
Wrangler CLI for D1 operations

Procedure: Full User Migration

Step 1: Export Users from Source Instance

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
SOURCE_KEY=$(pass verifieddit/clerk/test-secret-key)

# Export all users
curl -s -H "Authorization: Bearer ${SOURCE_KEY}" \
  "https://api.clerk.com/v1/users?limit=500&offset=0" | \
  jq '[.data[] | {
    id: .id,
    email: .email_addresses[0].email_address,
    first_name: .first_name,
    last_name: .last_name,
    created_at: .created_at
  }]' > users-export.json

Step 2: Create Users in Target Instance

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
TARGET_KEY=$(pass verifieddit/clerk/secret-key)

# For each user in the export
cat users-export.json | jq -c '.[]' | while read user; do
  EMAIL=$(echo $user | jq -r '.email')

  curl -s -X POST \
    -H "Authorization: Bearer ${TARGET_KEY}" \
    -H "Content-Type: application/json" \
    "https://api.clerk.com/v1/users" \
    --data "{
      \"email_address\": [\"${EMAIL}\"],
      \"skip_password_requirement\": true
    }"

  echo "Created: ${EMAIL}"
done

Step 3: Map Old IDs to New IDs

After creating users in the target instance, build an ID mapping:

1
2
3
4
5
6
7
# Get new user IDs from target
curl -s -H "Authorization: Bearer ${TARGET_KEY}" \
  "https://api.clerk.com/v1/users?limit=500" | \
  jq '[.data[] | {email: .email_addresses[0].email_address, new_id: .id}]' > new-users.json

# Create mapping file (old_id -> new_id, matched by email)
# Manual process -- cross-reference users-export.json with new-users.json

Step 4: Migrate D1 Data

Export data from the source D1 database:

1
2
3
4
5
6
7
# Export users table
npx wrangler d1 execute verifieddit-badges-testing \
  --command "SELECT * FROM users" --json > d1-users-export.json

# Export badges for migrated users
npx wrangler d1 execute verifieddit-badges-testing \
  --command "SELECT * FROM badges" --json > d1-badges-export.json

Update Clerk IDs in the exported data using the mapping from Step 3, then import:

1
2
3
4
# Insert into production D1 (construct INSERT statements)
# Example for a single user:
npx wrangler d1 execute verifieddit-badges \
  --command "INSERT INTO users (clerk_id, email, plan, created_at) VALUES ('new_clerk_id', 'user@example.com', 'free', '2026-01-01')"

Step 5: Verify

1
2
3
4
5
6
7
# Verify user count matches
npx wrangler d1 execute verifieddit-badges \
  --command "SELECT COUNT(*) as count FROM users"

# Verify a specific user
npx wrangler d1 execute verifieddit-badges \
  --command "SELECT * FROM users WHERE email = 'test@example.com'"

Step 6: Update Application Configuration

After migration:

Update VITE_CLERK_PUBLISHABLE_KEY if switching Clerk instances
Update all backend secret keys
Recompute FOD hash if publishable key changed
Rebuild and redeploy

Rollback Plan

If migration fails:

Keep the source Clerk instance active (do not delete)
Revert application configuration to use source instance keys
Investigate and fix migration issues
Retry migration

Troubleshooting

Duplicate email error: User already exists in the target instance. Check with the Clerk API before creating.
ID mapping mismatch: Always map by email address, not by Clerk ID (IDs are unique per instance).
D1 foreign key errors: Import users table first, then badges/badge_images (foreign key dependencies).
Missing users after migration: Check pagination. Clerk API returns max 500 users per request. Use offset for larger user bases.