SuperTokens

If you can tolerate a maintenance window, you can perform a 'cold migration' using an offline SQL script. This is recommended for single-region/single-instance setups or self-hosted dev/staging environments. This process involves stopping traffic, deploying new binaries in LEGACY mode to initialize the schema, running a backfill script, and then manually setting the migration mode to MIGRATED.

Pre-flight

1. Confirm versions.
2. Schedule a maintenance window.
3. Backup your database.

Migration Steps

1. Stop traffic

Drain or block all SuperTokens API traffic. No writes can be in flight during the migration.

2. Deploy new binaries in LEGACY mode

Boot one core+plugin instance against the database. This creates the new tables and columns via GeneralQueries.createTablesIfNotExists.

Verify the schema exists using:

\dt recipe_user_account_infos
\dt recipe_user_tenants
\dt primary_user_tenants
\d+ app_id_to_user_id      -- expect time_joined, primary_or_recipe_user_time_joined columns

Shut the instance down before the backfill.

3. Run the offline backfill

Use the migration-backfill.sql script from the supertokens-postgresql-plugin repository.

psql "<connection-uri>" -v app_id="'my-app'" -f migration-scripts/migration-backfill.sql

Note: Use -v app_id="''" to scope to all apps, or -v app_id="'my-app'" to scope to a single app.

4. Verify data integrity

Run the verification queries included at the bottom of migration-backfill.sql. They should all return 0. For absolute confidence, run a canonical dump comparison:

psql "<connection-uri>" -f migration-scripts/dump_old_canonical.sql > old.csv
psql "<connection-uri>" -f migration-scripts/dump_new_canonical.sql > new.csv
diff old.csv new.csv

5. Set tenant to MIGRATED mode

You can do this in one of two ways:

Option A: Direct DB Edit Update the tenant_configs row directly. Adjust the syntax based on whether your column is text or jsonb:

UPDATE tenant_configs
SET core_config = jsonb_set(
    core_config::jsonb,
    '{migration_mode}',
    '"MIGRATED"'::jsonb
)::text
WHERE connection_uri_domain = '' AND app_id = 'public' AND tenant_id = 'public';

Option B: API Flip

1. Boot the new core in LEGACY mode.
2. Issue a PUT /recipe/multitenancy/connectionuridomain/v2 with migration_mode: "MIGRATED".
3. Shut down the instance.

6. Bring traffic back up

Start all instances. Verify with synthetic users (create, link, update email) and check that GET /migration/mode returns "mode": "MIGRATED" for every CUD.

This guide outlines the zero-downtime production path for migrating SuperTokens from a legacy schema to the new schema (introduced in v12.0.0). The process uses a multi-step transition through different migration_mode states to ensure data consistency and allow for reversible rollbacks until the final cutover.

Migration Modes

• LEGACY: Old and new instances coexist. New tables exist but remain empty. Reads and writes only use old tables.
• DUAL_WRITE_READ_OLD: New writes are performed to both old and new tables atomically. Reads still come from old tables.
• DUAL_WRITE_READ_NEW: New writes are performed to both old and new tables. Reads now come from the new reservation tables. This is the first high-risk step.
• MIGRATED: Writes to old tables stop. The old tables become stale. This step is one-way via the standard API.

Pre-flight Requirements

• Version Check: Ensure plugin-interface >= 8.6.0, postgresql-plugin >= 9.5.0, and core >= 12.0.0 are bundled.
• Backup: Take a logical backup of the live database (e.g., pg_dump --schema-only and a full data dump).
• Capacity: Verify the cluster has CPU headroom for dual-writes, as operations like updateEmail will perform double the work during DUAL_WRITE phases.

Before switching reads to the new tables, run these SQL queries to ensure the backfill was successful and data is consistent across the old and new structures.

-- 1. No users missing time_joined
SELECT COUNT(*) FROM app_id_to_user_id
WHERE time_joined = 0 AND app_id = 'public';
-- Expected: 0

-- 2. All users have account info entries
SELECT COUNT(*) FROM app_id_to_user_id a
WHERE a.app_id = 'public' AND NOT EXISTS (
    SELECT 1 FROM recipe_user_account_infos rai
    WHERE rai.app_id = a.app_id AND rai.recipe_user_id = a.user_id
);
-- Expected: 0

-- 3. Tenant coverage matches
SELECT
    (SELECT COUNT(*) FROM all_auth_recipe_users WHERE app_id = 'public') AS old_count,
    (SELECT COUNT(*) FROM recipe_user_tenants WHERE app_id = 'public') AS new_count;
-- new_count should be >= old_count

-- 4. All linked users have primary reservations
SELECT DISTINCT a.primary_or_recipe_user_id
FROM app_id_to_user_id a
WHERE a.is_linked_or_is_a_primary_user = TRUE AND a.app_id = 'public'
AND NOT EXISTS (
    SELECT 1 FROM primary_user_tenants pt
    WHERE pt.app_id = a.app_id AND pt.primary_user_id = a.primary_or_recipe_user_id
);
-- Expected: empty

Once in DUAL_WRITE_READ_OLD, the system automatically starts a cron job (5-minute tick, batch size 1000) to backfill existing users into the new tables.

Monitoring Progress

Use the following endpoint to check the status of the backfill: GET /migration/backfill/progress

Wait until pendingUsers == 0 for every CUD before proceeding to the next step.

Verification

• Completeness Scan: GET /migration/backfill/progress?verify=true returns inconsistentUsersCount. This should be 0.
• Parity Dumps: For large datasets, use the dump_old_canonical.sql and dump_new_canonical.sql scripts from the supertokens-postgresql-plugin repository to diff the two views.

Troubleshooting

If the backfill fails with Unknown recipeId during backfill: '...', it means a recipe has introduced rows that the backfill logic does not recognize. This requires a patch release to handle the new recipeId.

# Check progress
GET /migration/backfill/progress

# Verify completeness
GET /migration/backfill/progress?verify=true