important

This is a contributors guide and NOT a user guide. Please visit these docs if you are using or evaluating SuperTokens.

Upgrading user's core version

Tables to update:
- apps (update core_version, plugin_interface_versio and plugin_version)
- aws_containers (update image_version)
System Manager:
- Find instance id
- Find StartUpdateSuperTokens command for relevant app
- Copy to new
- Change imageVersion parameter
- Make sure the Document version is relevant to the new core version
- Run command and check the container is updated and running

Core DB Migration

3.3 to 3.4#

No change

3.4 to 3.5#

Change user_id in emailverification_verified_emails to VARCHAR(128)
Change user_id in emailverification_tokens to VARCHAR(128)

Add new table (use the db_user associated with user's core or remember to change the owner of the new tables to db_user):

CREATE TABLE IF NOT EXISTS all_auth_recipe_users(
   user_id CHAR(36) NOT NULL,
   recipe_id VARCHAR(128) NOT NULL,
   time_joined BIGINT NOT NULL,
   PRIMARY KEY (user_id)
);
CREATE INDEX all_auth_recipe_users_pagination_index ON all_auth_recipe_users (time_joined DESC, user_id DESC);

This means that the content from other emailpassword_users and from thirdparty_users need to be copied into this table

Deleted index emailpassword_user_pagination_index and thirdparty_users_pagination_index

3.5 to 3.6#

No manual change required

3.6 to 3.7#

No manual change required

3.7 to 3.8#

No manual change required
Adds new tables for passwordless (is added automatically):
- passwordless_users that stores the users of the passwordless recipe
- passwordless_devices that stores devices/information about passwordless login attempts
- passwordless_codes that stores the codes each device can consume to finish the login process

3.8 to 3.9#

No manual change required

3.9 to 3.10#

No manual change required

3.10 to 3.11#

No manual change required

3.11 to 3.12#

No manual change required

3.12 to 3.13#

No manual change required

3.13 to 3.14#

No manual change required

3.14 to 3.15#

No manual change required

3.15 to 3.16#

No manual change required

3.16 to 4.0#

Run: ALTER TABLE thirdparty_users ALTER COLUMN third_party_user_id TYPE VARCHAR(256); ALTER TABLE emailpassword_users ALTER COLUMN password_hash TYPE VARCHAR(256);

4.0 to 4.1#

No manual change required

4.1 to 4.2#

No manual change required

4.2 to 4.3#

No manual change required

4.3 to 4.4#

No manual change required

4.4 to 4.5#

No manual change required

4.5 to 4.6#

No manual change required

4.6 to 5.0#

Renamed access_token_signing_key_update_interval to access_token_dynamic_signing_key_update_interval in config

If access_token_signing_key_dynamic is currently set to false then run:

ALTER TABLE session_info ADD COLUMN use_static_key BOOLEAN NOT NULL DEFAULT(true);
ALTER TABLE session_info ALTER COLUMN use_static_key DROP DEFAULT;
INSERT INTO jwt_signing_keys(key_id, key_string, algorithm, created_at)
   select CONCAT('s-', created_at_time) as key_id, value as key_string, 'RS256' as algorithm, created_at_time as created_at
   from session_access_token_signing_keys;

If access_token_signing_key_dynamic is currently set to true, or it not set at all:

ALTER TABLE session_info ADD COLUMN use_static_key BOOLEAN NOT NULL DEFAULT(false);
ALTER TABLE session_info ALTER COLUMN use_static_key DROP DEFAULT;

5.0 to 6.0#

Follow these steps: https://github.com/supertokens/backend/issues/275#issuecomment-1647547145
Use the following migration script

-- General Tables

CREATE TABLE IF NOT EXISTS apps  (
  app_id VARCHAR(64) NOT NULL DEFAULT 'public',
  created_at_time BIGINT,
  CONSTRAINT apps_pkey PRIMARY KEY(app_id)
);

INSERT INTO apps (app_id, created_at_time) 
  VALUES ('public', 0) ON CONFLICT DO NOTHING;

------------------------------------------------------------

CREATE TABLE IF NOT EXISTS tenants (
  app_id VARCHAR(64) NOT NULL DEFAULT 'public',
  tenant_id VARCHAR(64) NOT NULL DEFAULT 'public',
  created_at_time BIGINT ,
  CONSTRAINT tenants_pkey
    PRIMARY KEY (app_id, tenant_id),
  CONSTRAINT tenants_app_id_fkey FOREIGN KEY(app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE
);

INSERT INTO tenants (app_id, tenant_id, created_at_time) 
  VALUES ('public', 'public', 0) ON CONFLICT DO NOTHING;

CREATE INDEX IF NOT EXISTS tenants_app_id_index ON tenants (app_id);

------------------------------------------------------------

ALTER TABLE key_value
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public',
  ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE key_value
  DROP CONSTRAINT key_value_pkey;

ALTER TABLE key_value
  ADD CONSTRAINT key_value_pkey 
    PRIMARY KEY (app_id, tenant_id, name);

ALTER TABLE key_value
  DROP CONSTRAINT IF EXISTS key_value_tenant_id_fkey;

ALTER TABLE key_value
  ADD CONSTRAINT key_value_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS key_value_tenant_id_index ON key_value (app_id, tenant_id);

------------------------------------------------------------

CREATE TABLE IF NOT EXISTS app_id_to_user_id (
  app_id VARCHAR(64) NOT NULL DEFAULT 'public',
  user_id CHAR(36) NOT NULL,
  recipe_id VARCHAR(128) NOT NULL,
  CONSTRAINT app_id_to_user_id_pkey
    PRIMARY KEY (app_id, user_id),
  CONSTRAINT app_id_to_user_id_app_id_fkey
    FOREIGN KEY(app_id) REFERENCES apps (app_id) ON DELETE CASCADE
);

INSERT INTO app_id_to_user_id (user_id, recipe_id) 
  SELECT user_id, recipe_id
  FROM all_auth_recipe_users ON CONFLICT DO NOTHING;

CREATE INDEX IF NOT EXISTS app_id_to_user_id_app_id_index ON app_id_to_user_id (app_id);

------------------------------------------------------------

ALTER TABLE all_auth_recipe_users
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public',
  ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE all_auth_recipe_users
  DROP CONSTRAINT all_auth_recipe_users_pkey CASCADE;

ALTER TABLE all_auth_recipe_users
  ADD CONSTRAINT all_auth_recipe_users_pkey 
    PRIMARY KEY (app_id, tenant_id, user_id);

ALTER TABLE all_auth_recipe_users
  DROP CONSTRAINT IF EXISTS all_auth_recipe_users_tenant_id_fkey;

ALTER TABLE all_auth_recipe_users
  ADD CONSTRAINT all_auth_recipe_users_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;

ALTER TABLE all_auth_recipe_users
  DROP CONSTRAINT IF EXISTS all_auth_recipe_users_user_id_fkey;

ALTER TABLE all_auth_recipe_users
  ADD CONSTRAINT all_auth_recipe_users_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

DROP INDEX all_auth_recipe_users_pagination_index;

CREATE INDEX all_auth_recipe_users_pagination_index ON all_auth_recipe_users (time_joined DESC, user_id DESC, tenant_id DESC, app_id DESC);

CREATE INDEX IF NOT EXISTS all_auth_recipe_user_id_index ON all_auth_recipe_users (app_id, user_id);

CREATE INDEX IF NOT EXISTS all_auth_recipe_tenant_id_index ON all_auth_recipe_users (app_id, tenant_id);

-- Multitenancy

CREATE TABLE IF NOT EXISTS tenant_configs (
  connection_uri_domain VARCHAR(256) DEFAULT '',
  app_id VARCHAR(64) DEFAULT 'public',
  tenant_id VARCHAR(64) DEFAULT 'public',
  core_config TEXT,
  email_password_enabled BOOLEAN,
  passwordless_enabled BOOLEAN,
  third_party_enabled BOOLEAN,
  CONSTRAINT tenant_configs_pkey
    PRIMARY KEY (connection_uri_domain, app_id, tenant_id)
);

------------------------------------------------------------

CREATE TABLE IF NOT EXISTS tenant_thirdparty_providers (
  connection_uri_domain VARCHAR(256) DEFAULT '',
  app_id VARCHAR(64) DEFAULT 'public',
  tenant_id VARCHAR(64) DEFAULT 'public',
  third_party_id VARCHAR(28) NOT NULL,
  name VARCHAR(64),
  authorization_endpoint TEXT,
  authorization_endpoint_query_params TEXT,
  token_endpoint TEXT,
  token_endpoint_body_params TEXT,
  user_info_endpoint TEXT,
  user_info_endpoint_query_params TEXT,
  user_info_endpoint_headers TEXT,
  jwks_uri TEXT,
  oidc_discovery_endpoint TEXT,
  require_email BOOLEAN,
  user_info_map_from_id_token_payload_user_id VARCHAR(64),
  user_info_map_from_id_token_payload_email VARCHAR(64),
  user_info_map_from_id_token_payload_email_verified VARCHAR(64),
  user_info_map_from_user_info_endpoint_user_id VARCHAR(64),
  user_info_map_from_user_info_endpoint_email VARCHAR(64),
  user_info_map_from_user_info_endpoint_email_verified VARCHAR(64),
  CONSTRAINT tenant_thirdparty_providers_pkey
    PRIMARY KEY (connection_uri_domain, app_id, tenant_id, third_party_id),
  CONSTRAINT tenant_thirdparty_providers_tenant_id_fkey
    FOREIGN KEY(connection_uri_domain, app_id, tenant_id)
    REFERENCES tenant_configs (connection_uri_domain, app_id, tenant_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS tenant_thirdparty_providers_tenant_id_index ON tenant_thirdparty_providers (connection_uri_domain, app_id, tenant_id);

------------------------------------------------------------

CREATE TABLE IF NOT EXISTS tenant_thirdparty_provider_clients (
  connection_uri_domain VARCHAR(256) DEFAULT '',
  app_id VARCHAR(64) DEFAULT 'public',
  tenant_id VARCHAR(64) DEFAULT 'public',
  third_party_id VARCHAR(28) NOT NULL,
  client_type VARCHAR(64) NOT NULL DEFAULT '',
  client_id VARCHAR(256) NOT NULL,
  client_secret TEXT,
  scope VARCHAR(128)[],
  force_pkce BOOLEAN,
  additional_config TEXT,
  CONSTRAINT tenant_thirdparty_provider_clients_pkey
    PRIMARY KEY (connection_uri_domain, app_id, tenant_id, third_party_id, client_type),
  CONSTRAINT tenant_thirdparty_provider_clients_third_party_id_fkey
    FOREIGN KEY (connection_uri_domain, app_id, tenant_id, third_party_id)
    REFERENCES tenant_thirdparty_providers (connection_uri_domain, app_id, tenant_id, third_party_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS tenant_thirdparty_provider_clients_third_party_id_index ON tenant_thirdparty_provider_clients (connection_uri_domain, app_id, tenant_id, third_party_id);

-- Session

ALTER TABLE session_info
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public',
  ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE session_info
  DROP CONSTRAINT session_info_pkey CASCADE;

ALTER TABLE session_info
  ADD CONSTRAINT session_info_pkey 
    PRIMARY KEY (app_id, tenant_id, session_handle);

ALTER TABLE session_info
  DROP CONSTRAINT IF EXISTS session_info_tenant_id_fkey;

ALTER TABLE session_info
  ADD CONSTRAINT session_info_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS session_expiry_index ON session_info (expires_at);

CREATE INDEX IF NOT EXISTS session_info_tenant_id_index ON session_info (app_id, tenant_id);

------------------------------------------------------------

ALTER TABLE session_access_token_signing_keys
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE session_access_token_signing_keys
  DROP CONSTRAINT session_access_token_signing_keys_pkey CASCADE;

ALTER TABLE session_access_token_signing_keys
  ADD CONSTRAINT session_access_token_signing_keys_pkey 
    PRIMARY KEY (app_id, created_at_time);

ALTER TABLE session_access_token_signing_keys
  DROP CONSTRAINT IF EXISTS session_access_token_signing_keys_app_id_fkey;

ALTER TABLE session_access_token_signing_keys
  ADD CONSTRAINT session_access_token_signing_keys_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS access_token_signing_keys_app_id_index ON session_access_token_signing_keys (app_id);

-- JWT

ALTER TABLE jwt_signing_keys
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE jwt_signing_keys
  DROP CONSTRAINT jwt_signing_keys_pkey CASCADE;

ALTER TABLE jwt_signing_keys
  ADD CONSTRAINT jwt_signing_keys_pkey 
    PRIMARY KEY (app_id, key_id);

ALTER TABLE jwt_signing_keys
  DROP CONSTRAINT IF EXISTS jwt_signing_keys_app_id_fkey;

ALTER TABLE jwt_signing_keys
  ADD CONSTRAINT jwt_signing_keys_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS jwt_signing_keys_app_id_index ON jwt_signing_keys (app_id);

-- EmailVerification

ALTER TABLE emailverification_verified_emails
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE emailverification_verified_emails
  DROP CONSTRAINT emailverification_verified_emails_pkey CASCADE;

ALTER TABLE emailverification_verified_emails
  ADD CONSTRAINT emailverification_verified_emails_pkey 
    PRIMARY KEY (app_id, user_id, email);

ALTER TABLE emailverification_verified_emails
  DROP CONSTRAINT IF EXISTS emailverification_verified_emails_app_id_fkey;

ALTER TABLE emailverification_verified_emails
  ADD CONSTRAINT emailverification_verified_emails_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS emailverification_verified_emails_app_id_index ON emailverification_verified_emails (app_id);

------------------------------------------------------------

ALTER TABLE emailverification_tokens
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public',
  ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE emailverification_tokens
  DROP CONSTRAINT emailverification_tokens_pkey CASCADE;

ALTER TABLE emailverification_tokens
  ADD CONSTRAINT emailverification_tokens_pkey 
    PRIMARY KEY (app_id, tenant_id, user_id, email, token);

ALTER TABLE emailverification_tokens
  DROP CONSTRAINT IF EXISTS emailverification_tokens_tenant_id_fkey;

ALTER TABLE emailverification_tokens
  ADD CONSTRAINT emailverification_tokens_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS emailverification_tokens_tenant_id_index ON emailverification_tokens (app_id, tenant_id);

-- EmailPassword

ALTER TABLE emailpassword_users
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE emailpassword_users
  DROP CONSTRAINT emailpassword_users_pkey CASCADE;

ALTER TABLE emailpassword_users
  DROP CONSTRAINT IF EXISTS emailpassword_users_email_key CASCADE;

ALTER TABLE emailpassword_users
  ADD CONSTRAINT emailpassword_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE emailpassword_users
  DROP CONSTRAINT IF EXISTS emailpassword_users_user_id_fkey;

ALTER TABLE emailpassword_users
  ADD CONSTRAINT emailpassword_users_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

------------------------------------------------------------

CREATE TABLE IF NOT EXISTS emailpassword_user_to_tenant (
  app_id VARCHAR(64) DEFAULT 'public',
  tenant_id VARCHAR(64) DEFAULT 'public',
  user_id CHAR(36) NOT NULL,
  email VARCHAR(256) NOT NULL,
  CONSTRAINT emailpassword_user_to_tenant_email_key
    UNIQUE (app_id, tenant_id, email),
  CONSTRAINT emailpassword_user_to_tenant_pkey
    PRIMARY KEY (app_id, tenant_id, user_id),
  CONSTRAINT emailpassword_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE
);

ALTER TABLE emailpassword_user_to_tenant
  DROP CONSTRAINT IF EXISTS emailpassword_user_to_tenant_email_key;

ALTER TABLE emailpassword_user_to_tenant
  ADD CONSTRAINT emailpassword_user_to_tenant_email_key
    UNIQUE (app_id, tenant_id, email);

ALTER TABLE emailpassword_user_to_tenant
  DROP CONSTRAINT IF EXISTS emailpassword_user_to_tenant_user_id_fkey;

ALTER TABLE emailpassword_user_to_tenant
  ADD CONSTRAINT emailpassword_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;

INSERT INTO emailpassword_user_to_tenant (user_id, email)
  SELECT user_id, email FROM emailpassword_users ON CONFLICT DO NOTHING;

------------------------------------------------------------

ALTER TABLE emailpassword_pswd_reset_tokens
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE emailpassword_pswd_reset_tokens
  DROP CONSTRAINT emailpassword_pswd_reset_tokens_pkey CASCADE;

ALTER TABLE emailpassword_pswd_reset_tokens
  ADD CONSTRAINT emailpassword_pswd_reset_tokens_pkey 
    PRIMARY KEY (app_id, user_id, token);

ALTER TABLE emailpassword_pswd_reset_tokens
  DROP CONSTRAINT IF EXISTS emailpassword_pswd_reset_tokens_user_id_fkey;

ALTER TABLE emailpassword_pswd_reset_tokens
  ADD CONSTRAINT emailpassword_pswd_reset_tokens_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES emailpassword_users (app_id, user_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS emailpassword_pswd_reset_tokens_user_id_index ON emailpassword_pswd_reset_tokens (app_id, user_id);

-- Passwordless

ALTER TABLE passwordless_users
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE passwordless_users
  DROP CONSTRAINT passwordless_users_pkey CASCADE;

ALTER TABLE passwordless_users
  ADD CONSTRAINT passwordless_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE passwordless_users
  DROP CONSTRAINT IF EXISTS passwordless_users_email_key;

ALTER TABLE passwordless_users
  DROP CONSTRAINT IF EXISTS passwordless_users_phone_number_key;

ALTER TABLE passwordless_users
  DROP CONSTRAINT IF EXISTS passwordless_users_user_id_fkey;

ALTER TABLE passwordless_users
  ADD CONSTRAINT passwordless_users_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

------------------------------------------------------------

CREATE TABLE IF NOT EXISTS passwordless_user_to_tenant (
  app_id VARCHAR(64) DEFAULT 'public',
  tenant_id VARCHAR(64) DEFAULT 'public',
  user_id CHAR(36) NOT NULL,
  email VARCHAR(256),
  phone_number VARCHAR(256),
  CONSTRAINT passwordless_user_to_tenant_email_key
    UNIQUE (app_id, tenant_id, email),
  CONSTRAINT passwordless_user_to_tenant_phone_number_key
    UNIQUE (app_id, tenant_id, phone_number),
  CONSTRAINT passwordless_user_to_tenant_pkey
    PRIMARY KEY (app_id, tenant_id, user_id),
  CONSTRAINT passwordless_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE
);

ALTER TABLE passwordless_user_to_tenant
  DROP CONSTRAINT IF EXISTS passwordless_user_to_tenant_user_id_fkey;

ALTER TABLE passwordless_user_to_tenant
  ADD CONSTRAINT passwordless_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;

INSERT INTO passwordless_user_to_tenant (user_id, email, phone_number)
  SELECT user_id, email, phone_number FROM passwordless_users ON CONFLICT DO NOTHING;

------------------------------------------------------------

ALTER TABLE passwordless_devices
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public',
  ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE passwordless_devices
  DROP CONSTRAINT passwordless_devices_pkey CASCADE;

ALTER TABLE passwordless_devices
  ADD CONSTRAINT passwordless_devices_pkey 
    PRIMARY KEY (app_id, tenant_id, device_id_hash);

ALTER TABLE passwordless_devices
  DROP CONSTRAINT IF EXISTS passwordless_devices_tenant_id_fkey;

ALTER TABLE passwordless_devices
  ADD CONSTRAINT passwordless_devices_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;

DROP INDEX IF EXISTS passwordless_devices_email_index;

CREATE INDEX IF NOT EXISTS passwordless_devices_email_index ON passwordless_devices (app_id, tenant_id, email);

DROP INDEX IF EXISTS passwordless_devices_phone_number_index;

CREATE INDEX IF NOT EXISTS passwordless_devices_phone_number_index ON passwordless_devices (app_id, tenant_id, phone_number);

CREATE INDEX IF NOT EXISTS passwordless_devices_tenant_id_index ON passwordless_devices (app_id, tenant_id);

------------------------------------------------------------

ALTER TABLE passwordless_codes
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public',
  ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE passwordless_codes
  DROP CONSTRAINT passwordless_codes_pkey CASCADE;

ALTER TABLE passwordless_codes
  ADD CONSTRAINT passwordless_codes_pkey 
    PRIMARY KEY (app_id, tenant_id, code_id);

ALTER TABLE passwordless_codes
  DROP CONSTRAINT IF EXISTS passwordless_codes_device_id_hash_fkey;

ALTER TABLE passwordless_codes
  ADD CONSTRAINT passwordless_codes_device_id_hash_fkey 
    FOREIGN KEY (app_id, tenant_id, device_id_hash)
    REFERENCES passwordless_devices (app_id, tenant_id, device_id_hash) ON DELETE CASCADE;

ALTER TABLE passwordless_codes
  DROP CONSTRAINT passwordless_codes_link_code_hash_key;

ALTER TABLE passwordless_codes
  DROP CONSTRAINT IF EXISTS passwordless_codes_link_code_hash_key;

ALTER TABLE passwordless_codes
  ADD CONSTRAINT passwordless_codes_link_code_hash_key
    UNIQUE (app_id, tenant_id, link_code_hash);

DROP INDEX IF EXISTS passwordless_codes_created_at_index;

CREATE INDEX IF NOT EXISTS passwordless_codes_created_at_index ON passwordless_codes (app_id, tenant_id, created_at);

DROP INDEX IF EXISTS passwordless_codes_device_id_hash_index;
CREATE INDEX IF NOT EXISTS passwordless_codes_device_id_hash_index ON passwordless_codes (app_id, tenant_id, device_id_hash);

-- ThirdParty

ALTER TABLE thirdparty_users
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE thirdparty_users
  DROP CONSTRAINT thirdparty_users_pkey CASCADE;

ALTER TABLE thirdparty_users
  DROP CONSTRAINT IF EXISTS thirdparty_users_user_id_key CASCADE;

ALTER TABLE thirdparty_users
  ADD CONSTRAINT thirdparty_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE thirdparty_users
  DROP CONSTRAINT IF EXISTS thirdparty_users_user_id_fkey;

ALTER TABLE thirdparty_users
  ADD CONSTRAINT thirdparty_users_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

DROP INDEX IF EXISTS thirdparty_users_thirdparty_user_id_index;

CREATE INDEX IF NOT EXISTS thirdparty_users_thirdparty_user_id_index ON thirdparty_users (app_id, third_party_id, third_party_user_id);

DROP INDEX IF EXISTS thirdparty_users_email_index;

CREATE INDEX IF NOT EXISTS thirdparty_users_email_index ON thirdparty_users (app_id, email);

------------------------------------------------------------

CREATE TABLE IF NOT EXISTS thirdparty_user_to_tenant (
  app_id VARCHAR(64) DEFAULT 'public',
  tenant_id VARCHAR(64) DEFAULT 'public',
  user_id CHAR(36) NOT NULL,
  third_party_id VARCHAR(28) NOT NULL,
  third_party_user_id VARCHAR(256) NOT NULL,
  CONSTRAINT thirdparty_user_to_tenant_third_party_user_id_key
    UNIQUE (app_id, tenant_id, third_party_id, third_party_user_id),
  CONSTRAINT thirdparty_user_to_tenant_pkey
    PRIMARY KEY (app_id, tenant_id, user_id),
  CONSTRAINT thirdparty_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE
);

ALTER TABLE thirdparty_user_to_tenant
  DROP CONSTRAINT IF EXISTS thirdparty_user_to_tenant_third_party_user_id_key;

ALTER TABLE thirdparty_user_to_tenant
  ADD CONSTRAINT thirdparty_user_to_tenant_third_party_user_id_key
    UNIQUE (app_id, tenant_id, third_party_id, third_party_user_id);

ALTER TABLE thirdparty_user_to_tenant
  DROP CONSTRAINT IF EXISTS thirdparty_user_to_tenant_user_id_fkey;

ALTER TABLE thirdparty_user_to_tenant
  ADD CONSTRAINT thirdparty_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;

INSERT INTO thirdparty_user_to_tenant (user_id, third_party_id, third_party_user_id)
  SELECT user_id, third_party_id, third_party_user_id FROM thirdparty_users ON CONFLICT DO NOTHING;

-- UserIdMapping

ALTER TABLE userid_mapping
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE userid_mapping
  DROP CONSTRAINT IF EXISTS userid_mapping_pkey CASCADE;

ALTER TABLE userid_mapping
  ADD CONSTRAINT userid_mapping_pkey 
    PRIMARY KEY (app_id, supertokens_user_id, external_user_id);

ALTER TABLE userid_mapping
  DROP CONSTRAINT IF EXISTS userid_mapping_supertokens_user_id_key;

ALTER TABLE userid_mapping
  ADD CONSTRAINT userid_mapping_supertokens_user_id_key
    UNIQUE (app_id, supertokens_user_id);

ALTER TABLE userid_mapping
  DROP CONSTRAINT IF EXISTS userid_mapping_external_user_id_key;

ALTER TABLE userid_mapping
  ADD CONSTRAINT userid_mapping_external_user_id_key
    UNIQUE (app_id, external_user_id);

ALTER TABLE userid_mapping
  DROP CONSTRAINT IF EXISTS userid_mapping_supertokens_user_id_fkey;

ALTER TABLE userid_mapping
  ADD CONSTRAINT userid_mapping_supertokens_user_id_fkey 
    FOREIGN KEY (app_id, supertokens_user_id)
    REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS userid_mapping_supertokens_user_id_index ON userid_mapping (app_id, supertokens_user_id);

-- UserRoles

ALTER TABLE roles
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE roles
  DROP CONSTRAINT roles_pkey CASCADE;

ALTER TABLE roles
  ADD CONSTRAINT roles_pkey 
    PRIMARY KEY (app_id, role);

ALTER TABLE roles
  DROP CONSTRAINT IF EXISTS roles_app_id_fkey;

ALTER TABLE roles
  ADD CONSTRAINT roles_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS roles_app_id_index ON roles (app_id);

------------------------------------------------------------

ALTER TABLE role_permissions
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE role_permissions
  DROP CONSTRAINT role_permissions_pkey CASCADE;

ALTER TABLE role_permissions
  ADD CONSTRAINT role_permissions_pkey 
    PRIMARY KEY (app_id, role, permission);

ALTER TABLE role_permissions
  DROP CONSTRAINT IF EXISTS role_permissions_role_fkey;

ALTER TABLE role_permissions
  ADD CONSTRAINT role_permissions_role_fkey 
    FOREIGN KEY (app_id, role)
    REFERENCES roles (app_id, role) ON DELETE CASCADE;

DROP INDEX IF EXISTS role_permissions_permission_index;

CREATE INDEX IF NOT EXISTS role_permissions_permission_index ON role_permissions (app_id, permission);

CREATE INDEX IF NOT EXISTS role_permissions_role_index ON role_permissions (app_id, role);

------------------------------------------------------------

ALTER TABLE user_roles
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public',
  ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE user_roles
  DROP CONSTRAINT user_roles_pkey CASCADE;

ALTER TABLE user_roles
  ADD CONSTRAINT user_roles_pkey 
    PRIMARY KEY (app_id, tenant_id, user_id, role);

ALTER TABLE user_roles
  DROP CONSTRAINT IF EXISTS user_roles_tenant_id_fkey;

ALTER TABLE user_roles
  ADD CONSTRAINT user_roles_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;

ALTER TABLE user_roles
  DROP CONSTRAINT IF EXISTS user_roles_role_fkey;

ALTER TABLE user_roles
  ADD CONSTRAINT user_roles_role_fkey 
    FOREIGN KEY (app_id, role)
    REFERENCES roles (app_id, role) ON DELETE CASCADE;

DROP INDEX IF EXISTS user_roles_role_index;

CREATE INDEX IF NOT EXISTS user_roles_role_index ON user_roles (app_id, tenant_id, role);

CREATE INDEX IF NOT EXISTS user_roles_tenant_id_index ON user_roles (app_id, tenant_id);

CREATE INDEX IF NOT EXISTS user_roles_app_id_role_index ON user_roles (app_id, role);

-- UserMetadata

ALTER TABLE user_metadata
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE user_metadata
  DROP CONSTRAINT user_metadata_pkey CASCADE;

ALTER TABLE user_metadata
  ADD CONSTRAINT user_metadata_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE user_metadata
  DROP CONSTRAINT IF EXISTS user_metadata_app_id_fkey;

ALTER TABLE user_metadata
  ADD CONSTRAINT user_metadata_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS user_metadata_app_id_index ON user_metadata (app_id);

-- Dashboard

ALTER TABLE dashboard_users
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE dashboard_users
  DROP CONSTRAINT dashboard_users_pkey CASCADE;

ALTER TABLE dashboard_users
  ADD CONSTRAINT dashboard_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE dashboard_users
  DROP CONSTRAINT IF EXISTS dashboard_users_email_key;

ALTER TABLE dashboard_users
  ADD CONSTRAINT dashboard_users_email_key
    UNIQUE (app_id, email);

ALTER TABLE dashboard_users
  DROP CONSTRAINT IF EXISTS dashboard_users_app_id_fkey;

ALTER TABLE dashboard_users
  ADD CONSTRAINT dashboard_users_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS dashboard_users_app_id_index ON dashboard_users (app_id);

------------------------------------------------------------

ALTER TABLE dashboard_user_sessions
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE dashboard_user_sessions
  DROP CONSTRAINT dashboard_user_sessions_pkey CASCADE;

ALTER TABLE dashboard_user_sessions
  ADD CONSTRAINT dashboard_user_sessions_pkey 
    PRIMARY KEY (app_id, session_id);

ALTER TABLE dashboard_user_sessions
  DROP CONSTRAINT IF EXISTS dashboard_user_sessions_user_id_fkey;

ALTER TABLE dashboard_user_sessions
  ADD CONSTRAINT dashboard_user_sessions_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES dashboard_users (app_id, user_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS dashboard_user_sessions_user_id_index ON dashboard_user_sessions (app_id, user_id);

-- TOTP

ALTER TABLE totp_users
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE totp_users
  DROP CONSTRAINT totp_users_pkey CASCADE;

ALTER TABLE totp_users
  ADD CONSTRAINT totp_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE totp_users
  DROP CONSTRAINT IF EXISTS totp_users_app_id_fkey;

ALTER TABLE totp_users
  ADD CONSTRAINT totp_users_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS totp_users_app_id_index ON totp_users (app_id);

------------------------------------------------------------

ALTER TABLE totp_user_devices
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE totp_user_devices
  DROP CONSTRAINT totp_user_devices_pkey;

ALTER TABLE totp_user_devices
  ADD CONSTRAINT totp_user_devices_pkey 
    PRIMARY KEY (app_id, user_id, device_name);

ALTER TABLE totp_user_devices
  DROP CONSTRAINT IF EXISTS totp_user_devices_user_id_fkey;

ALTER TABLE totp_user_devices
  ADD CONSTRAINT totp_user_devices_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES totp_users (app_id, user_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS totp_user_devices_user_id_index ON totp_user_devices (app_id, user_id);

------------------------------------------------------------

ALTER TABLE totp_used_codes
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public',
  ADD COLUMN IF NOT EXISTS tenant_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE totp_used_codes
  DROP CONSTRAINT totp_used_codes_pkey CASCADE;

ALTER TABLE totp_used_codes
  ADD CONSTRAINT totp_used_codes_pkey 
    PRIMARY KEY (app_id, tenant_id, user_id, created_time_ms);

ALTER TABLE totp_used_codes
  DROP CONSTRAINT IF EXISTS totp_used_codes_user_id_fkey;

ALTER TABLE totp_used_codes
  ADD CONSTRAINT totp_used_codes_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES totp_users (app_id, user_id) ON DELETE CASCADE;

ALTER TABLE totp_used_codes
  DROP CONSTRAINT IF EXISTS totp_used_codes_tenant_id_fkey;

ALTER TABLE totp_used_codes
  ADD CONSTRAINT totp_used_codes_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;

DROP INDEX IF EXISTS totp_used_codes_expiry_time_ms_index;

CREATE INDEX IF NOT EXISTS totp_used_codes_expiry_time_ms_index ON totp_used_codes (app_id, tenant_id, expiry_time_ms);

CREATE INDEX IF NOT EXISTS totp_used_codes_user_id_index ON totp_used_codes (app_id, user_id);

CREATE INDEX IF NOT EXISTS totp_used_codes_tenant_id_index ON totp_used_codes (app_id, tenant_id);

-- ActiveUsers

ALTER TABLE user_last_active
  ADD COLUMN IF NOT EXISTS app_id VARCHAR(64) DEFAULT 'public';

ALTER TABLE user_last_active
  DROP CONSTRAINT user_last_active_pkey CASCADE;

ALTER TABLE user_last_active
  ADD CONSTRAINT user_last_active_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE user_last_active
  DROP CONSTRAINT IF EXISTS user_last_active_app_id_fkey;

ALTER TABLE user_last_active
  ADD CONSTRAINT user_last_active_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS user_last_active_app_id_index ON user_last_active (app_id);

6.0 to 7.0#

ALTER TABLE all_auth_recipe_users
  ADD COLUMN primary_or_recipe_user_id CHAR(36) NOT NULL DEFAULT ('0');

ALTER TABLE all_auth_recipe_users
  ADD COLUMN is_linked_or_is_a_primary_user BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE all_auth_recipe_users
  ADD COLUMN primary_or_recipe_user_time_joined BIGINT NOT NULL DEFAULT 0;

UPDATE all_auth_recipe_users
  SET primary_or_recipe_user_id = user_id
  WHERE primary_or_recipe_user_id = '0';

UPDATE all_auth_recipe_users
  SET primary_or_recipe_user_time_joined = time_joined
  WHERE primary_or_recipe_user_time_joined = 0;

ALTER TABLE all_auth_recipe_users
  ADD CONSTRAINT all_auth_recipe_users_primary_or_recipe_user_id_fkey
    FOREIGN KEY (app_id, primary_or_recipe_user_id)
    REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

ALTER TABLE all_auth_recipe_users
  ALTER primary_or_recipe_user_id DROP DEFAULT;

ALTER TABLE app_id_to_user_id
  ADD COLUMN primary_or_recipe_user_id CHAR(36) NOT NULL DEFAULT ('0');

ALTER TABLE app_id_to_user_id
  ADD COLUMN is_linked_or_is_a_primary_user BOOLEAN NOT NULL DEFAULT FALSE;

UPDATE app_id_to_user_id
  SET primary_or_recipe_user_id = user_id
  WHERE primary_or_recipe_user_id = '0';

ALTER TABLE app_id_to_user_id
  ADD CONSTRAINT app_id_to_user_id_primary_or_recipe_user_id_fkey
    FOREIGN KEY (app_id, primary_or_recipe_user_id)
    REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

ALTER TABLE app_id_to_user_id
    ALTER primary_or_recipe_user_id DROP DEFAULT;

DROP INDEX all_auth_recipe_users_pagination_index;

CREATE INDEX all_auth_recipe_users_pagination_index1 ON all_auth_recipe_users (
  app_id, tenant_id, primary_or_recipe_user_time_joined DESC, primary_or_recipe_user_id DESC);

CREATE INDEX all_auth_recipe_users_pagination_index2 ON all_auth_recipe_users (
  app_id, tenant_id, primary_or_recipe_user_time_joined ASC, primary_or_recipe_user_id DESC);

CREATE INDEX all_auth_recipe_users_pagination_index3 ON all_auth_recipe_users (
  recipe_id, app_id, tenant_id, primary_or_recipe_user_time_joined DESC, primary_or_recipe_user_id DESC);

CREATE INDEX all_auth_recipe_users_pagination_index4 ON all_auth_recipe_users (
  recipe_id, app_id, tenant_id, primary_or_recipe_user_time_joined ASC, primary_or_recipe_user_id DESC);

CREATE INDEX all_auth_recipe_users_primary_user_id_index ON all_auth_recipe_users (primary_or_recipe_user_id, app_id);

CREATE INDEX all_auth_recipe_users_recipe_id_index ON all_auth_recipe_users (app_id, recipe_id, tenant_id);

ALTER TABLE emailpassword_pswd_reset_tokens DROP CONSTRAINT IF EXISTS emailpassword_pswd_reset_tokens_user_id_fkey;

ALTER TABLE emailpassword_pswd_reset_tokens ADD CONSTRAINT emailpassword_pswd_reset_tokens_user_id_fkey FOREIGN KEY (app_id, user_id) REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

ALTER TABLE emailpassword_pswd_reset_tokens ADD COLUMN email VARCHAR(256);

7.0 to 8.0#

ALTER TABLE user_roles DROP CONSTRAINT IF EXISTS user_roles_role_fkey;

8.0 to 9.0#

ALTER TABLE totp_user_devices ADD COLUMN IF NOT EXISTS created_at BIGINT default 0;
ALTER TABLE totp_user_devices ALTER COLUMN created_at DROP DEFAULT;

9.0 to 9.1#

When transferring data from older / source master db to newer one, we have to modify the insert query to set is_first_factors_null to true when inserting into the tenant_configs (in the target master db).

9.1 to 9.2#

No changes required.

Auth Methods

Add ons

important

Upgrading user's core version

Core DB Migration

3.3 to 3.4#

3.4 to 3.5#

3.5 to 3.6#

3.6 to 3.7#

3.7 to 3.8#

3.8 to 3.9#

3.9 to 3.10#

3.10 to 3.11#

3.11 to 3.12#

3.12 to 3.13#

3.13 to 3.14#

3.14 to 3.15#

3.15 to 3.16#

3.16 to 4.0#

4.0 to 4.1#

4.1 to 4.2#

4.2 to 4.3#

4.3 to 4.4#

4.4 to 4.5#

4.5 to 4.6#

4.6 to 5.0#

5.0 to 6.0#

6.0 to 7.0#

7.0 to 8.0#

8.0 to 9.0#

9.0 to 9.1#

9.1 to 9.2#