CHANGELOG.md

Changelog

All notable changes to this project will be documented in this file.

The format is based on Keep a Changelog, and this project adheres to Semantic Versioning.

[unreleased]

[6.0.10] - 2023-08-16

• Fixed an encoding/decoding issue for certain access token payloads

[6.0.9] - 2023-08-14

• Now using decimal notation to add numbers into the access token payload (instead of scientific notation)

[6.0.8] - 2023-08-01

• Fixes CUD validation starting with number.

[6.0.7] - 2023-07-28

• Fixes session removing for user with useridmapping when disassociating from tenant.
• Fixes issue with access token migration from version v1 and v2

[6.0.6] - 2023-07-24

• Adds all ee features enabled for in memory database.

[6.0.5] - 2023-07-20

• Fixes logging issue in API call where it used to print out the root CUD tenant info when querying with a tenant that does not exist.

[6.0.4] - 2023-07-13

• Fixes tenant prefix in stack trace log
• supertokens_default_cdi_version config renamed to supertokens_max_cdi_version
• Fixes /apiversion GET to return versions until supertokens_max_cdi_version if set
• Fixes /recipe/multitenancy/tenant GET to return TENANT_NOT_FOUND_ERROR with 200 status when tenant was not found

[6.0.3] - 2023-07-11

• Fixes duplicate users in users search queries when user is associated to multiple tenants
• Fixes wrong tenant id in logging for APIKeyUnauthorisedException

[6.0.2] - 2023-07-04

• Fixes some of the session APIs to return tenantId
• argon and bcrypt related configs are now configurable only from config.yaml
• ip_allow_regex and ip_deny_regex are now protected properties for SaaS
• hello is disallowed as a tenantId
• creation of apps enables all recipes by default but not during creation of tenant

[6.0.1]

• Fixes Invalid API key issue on hello API
• Fixes CreateOrUpdateThirdPartyConfigAPI as per CDI 3.0
• Fixes sessionHandle to include tenant information and the related APIs are now app specific
• Updated GET /appid-<appId>/<tenantId>/recipe/session/user
  • Adds fetchAcrossAllTenants with default true - controls fetching of sessions across all tenants or only a particular tenant
• Updated POST /appid-<appId>/<tenantId>/recipe/session/remove
  • Adds revokeAcrossAllTenants with default true - controls revoking of sessions across all tenants or only a particular tenant
• Updated telemetry to send connectionUriDomain, appId and mau information
• Updated feature flag stats to report usersCount per tenant

[6.0.0] - 2023-06-02

Adds

• Support for multitenancy.
• New config supertokens_saas_secret added to support multitenancy in SaaS mode.
• New config supertokens_default_cdi_version is added to specify the version of CDI core must assume when the version is not specified in the request. If this config is not specified, the core will assume the latest version.

Fixes

• Fixes an issue where session verification would fail for JWTs created using the JWT recipe

Changes

• Modifies the /recipe/dashboard/session/verify API to include the user's email in the response
• Support for multitenancy
  • New APIs to manage apps and tenants
    • /recipe/multitenancy/connectionuridomain PUT
    • /recipe/multitenancy/connectionuridomain/remove POST
    • /recipe/multitenancy/connectionuridomain/list GET
    • /recipe/multitenancy/app PUT
    • /recipe/multitenancy/app/remove POST
    • /recipe/multitenancy/app/list GET
    • /appid-<appid>/recipe/multitenancy/tenant PUT
    • /appid-<appid>/<tenantid>/recipe/multitenancy/tenant GET
    • /appid-<appid>/recipe/multitenancy/tenant/remove POST
    • /appid-<appid>/recipe/multitenancy/tenant/list GET
    • /appid-<appid>/recipe/multitenancy/config/thirdparty PUT
    • /appid-<appid>/recipe/multitenancy/config/thirdparty/remove POST
    • /appid-<appid>/<tenantid>/recipe/multitenancy/tenant/user POST
    • /appid-<appid>/<tenantid>/recipe/multitenancy/tenant/user/remove POST
  • API paths can be prefixed with /appid-<appid>/<tenantid> to perform app or tenant specific operations.

Migration steps for SQL

1. Ensure that the core is already upgraded to version 5.0.0 (CDI version 2.21)

2. Stop the core instance(s)

3. Run the migration script

   If using PostgreSQL

   Run the following SQL 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);
   

   If using MySQL

   Run the following SQL script

   -- helper stored procedures
   
   CREATE PROCEDURE st_drop_all_fkeys()
   BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE dropCommand VARCHAR(255);
     DECLARE dropCur CURSOR for 
             SELECT concat('ALTER TABLE ', table_schema,'.',table_name,' DROP FOREIGN KEY ', constraint_name, ';') 
             FROM information_schema.table_constraints
             WHERE constraint_type='FOREIGN KEY' 
                 AND table_schema = DATABASE();
   
     DECLARE CONTINUE handler for NOT found SET done = true;
       OPEN dropCur;
   
       read_loop: LOOP
           FETCH dropCur INTO dropCommand;
           IF done THEN
               leave read_loop;
           END IF;
   
           SET @sdropCommand = dropCommand;
   
           PREPARE dropClientUpdateKeyStmt FROM @sdropCommand;
   
           EXECUTE dropClientUpdateKeyStmt;
   
           DEALLOCATE prepare dropClientUpdateKeyStmt;
       END LOOP;
   
       CLOSE dropCur;
   END
   
   ---
   
   CREATE PROCEDURE st_drop_all_pkeys()
   BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE dropCommand VARCHAR(255);
     DECLARE dropCur CURSOR for 
             SELECT concat('ALTER TABLE ', table_schema,'.',table_name,' DROP PRIMARY KEY ', ';') 
             FROM information_schema.table_constraints
             WHERE constraint_type='PRIMARY KEY' 
                 AND table_schema = DATABASE();
   
     DECLARE CONTINUE handler for NOT found SET done = true;
       OPEN dropCur;
   
       read_loop: LOOP
           FETCH dropCur INTO dropCommand;
           IF done THEN
               leave read_loop;
           END IF;
   
           SET @sdropCommand = dropCommand;
   
           PREPARE dropClientUpdateKeyStmt FROM @sdropCommand;
   
           EXECUTE dropClientUpdateKeyStmt;
   
           DEALLOCATE prepare dropClientUpdateKeyStmt;
       END LOOP;
   
       CLOSE dropCur;
   END
   
   ---
   
   CREATE PROCEDURE st_drop_all_keys()
   BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE dropCommand VARCHAR(255);
     DECLARE dropCur CURSOR for 
             SELECT concat('ALTER TABLE ', table_schema,'.',table_name,' DROP INDEX ', constraint_name, ';') 
             FROM information_schema.table_constraints
             WHERE constraint_type='UNIQUE' 
                 AND table_schema = DATABASE();
   
     DECLARE CONTINUE handler for NOT found SET done = true;
       OPEN dropCur;
   
       read_loop: LOOP
           FETCH dropCur INTO dropCommand;
           IF done THEN
               leave read_loop;
           END IF;
   
           SET @sdropCommand = dropCommand;
   
           PREPARE dropClientUpdateKeyStmt FROM @sdropCommand;
   
           EXECUTE dropClientUpdateKeyStmt;
   
           DEALLOCATE prepare dropClientUpdateKeyStmt;
       END LOOP;
   
       CLOSE dropCur;
   END
   
   ---
   
   CREATE PROCEDURE st_drop_all_indexes()
   BEGIN
     DECLARE done INT DEFAULT FALSE;
     DECLARE dropCommand VARCHAR(255);
     DECLARE dropCur CURSOR for 
             SELECT DISTINCT concat('ALTER TABLE ', table_schema, '.', table_name, ' DROP INDEX ', index_name, ';')
             FROM information_schema.statistics
             WHERE NON_UNIQUE = 1 AND table_schema = database();
   
     DECLARE CONTINUE handler for NOT found SET done = true;
       OPEN dropCur;
   
       read_loop: LOOP
           FETCH dropCur INTO dropCommand;
           IF done THEN
               leave read_loop;
           END IF;
   
           SET @sdropCommand = dropCommand;
   
           PREPARE dropClientUpdateKeyStmt FROM @sdropCommand;
   
           EXECUTE dropClientUpdateKeyStmt;
   
           DEALLOCATE prepare dropClientUpdateKeyStmt;
       END LOOP;
   
       CLOSE dropCur;
   END
   
   ---
   
   CREATE PROCEDURE st_add_column_if_not_exists(
   IN p_table_name varchar(50), 
   IN p_column_name varchar(50),
   IN p_column_type varchar(50),
   IN p_additional varchar(100),
   OUT p_status_message varchar(100))
       READS SQL DATA
   BEGIN
       DECLARE v_count INT;
       
       # Check wether column exist or not
       SELECT count(*) INTO v_count
       FROM information_schema.columns
       WHERE table_schema = database()
           AND table_name   = p_table_name
           AND column_name  = p_column_name;
           
       IF v_count > 0 THEN
         # Return column already exists message
         SELECT 'Column already Exists' INTO p_status_message;
       ELSE
           # Add Column and return success message
         set @ddl_addcolumn=CONCAT('ALTER TABLE ',database(),'.',p_table_name,
         ' ADD COLUMN ',p_column_name,' ',p_column_type,' ',p_additional);
       prepare add_column_sql from @ddl_addcolumn;
       execute add_column_sql;
         SELECT 'Column Successfully  Created!' INTO p_status_message;
       END IF;
   END
   
   -- Drop constraints and indexes
   
   CALL st_drop_all_fkeys();
   CALL st_drop_all_keys();
   CALL st_drop_all_pkeys();
   CALL st_drop_all_indexes(); 
   
   -- General Tables
   
   CREATE TABLE IF NOT EXISTS apps  (
     app_id VARCHAR(64) NOT NULL DEFAULT 'public',
     created_at_time BIGINT UNSIGNED
   );
   
   ALTER TABLE apps
     ADD PRIMARY KEY(app_id);
   
   INSERT IGNORE INTO apps (app_id, created_at_time) 
     VALUES ('public', 0);
   
   ------------------------------------------------------------
   
   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 UNSIGNED
   );
   
   ALTER TABLE tenants
     ADD PRIMARY KEY(app_id, tenant_id);
   
   ALTER TABLE tenants
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   INSERT IGNORE INTO tenants (app_id, tenant_id, created_at_time) 
     VALUES ('public', 'public', 0);
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('key_value', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   CALL st_add_column_if_not_exists('key_value', 'tenant_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE key_value
     ADD PRIMARY KEY (app_id, tenant_id, name);
   
   ALTER TABLE key_value
     ADD FOREIGN KEY (app_id, tenant_id)
       REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;
   
   ------------------------------------------------------------
   
   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
   );
   
   ALTER TABLE app_id_to_user_id
     ADD PRIMARY KEY (app_id, user_id);
   
   ALTER TABLE app_id_to_user_id
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   INSERT IGNORE INTO app_id_to_user_id (user_id, recipe_id) 
     SELECT user_id, recipe_id
     FROM all_auth_recipe_users;
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('all_auth_recipe_users', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   CALL st_add_column_if_not_exists('all_auth_recipe_users', 'tenant_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE all_auth_recipe_users
     ADD PRIMARY KEY (app_id, tenant_id, user_id);
   
   ALTER TABLE all_auth_recipe_users
     ADD FOREIGN KEY (app_id, tenant_id)
       REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;
   
   ALTER TABLE all_auth_recipe_users
     ADD FOREIGN KEY (app_id, user_id)
       REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;
   
   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);
   
   -- 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
   );
   
   ALTER TABLE tenant_configs
     ADD 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)
   );
   
   ALTER TABLE tenant_thirdparty_providers
     ADD PRIMARY KEY (connection_uri_domain, app_id, tenant_id, third_party_id);
   
   ALTER TABLE tenant_thirdparty_providers
     ADD FOREIGN KEY (connection_uri_domain, app_id, tenant_id)
       REFERENCES tenant_configs (connection_uri_domain, app_id, tenant_id) ON DELETE CASCADE;
   
   ------------------------------------------------------------
   
   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 TEXT,
     force_pkce BOOLEAN,
     additional_config TEXT
   );
   
   ALTER TABLE tenant_thirdparty_provider_clients
     ADD PRIMARY KEY (connection_uri_domain, app_id, tenant_id, third_party_id, client_type);
   
   ALTER TABLE tenant_thirdparty_provider_clients
     ADD 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;
   
   
   -- Session
   
   CALL st_add_column_if_not_exists('session_info', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   CALL st_add_column_if_not_exists('session_info', 'tenant_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE session_info
     ADD PRIMARY KEY (app_id, tenant_id, session_handle);
   
   ALTER TABLE session_info
     ADD FOREIGN KEY (app_id, tenant_id)
       REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;
   
   CREATE INDEX session_expiry_index ON session_info (expires_at);
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('session_access_token_signing_keys', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE session_access_token_signing_keys
     ADD PRIMARY KEY (app_id, created_at_time);
   
   ALTER TABLE session_access_token_signing_keys
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   -- JWT
   
   CALL st_add_column_if_not_exists('jwt_signing_keys', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE jwt_signing_keys
     ADD PRIMARY KEY (app_id, key_id);
   
   ALTER TABLE jwt_signing_keys
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   -- EmailVerification
   
   CALL st_add_column_if_not_exists('emailverification_verified_emails', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE emailverification_verified_emails
     ADD PRIMARY KEY (app_id, user_id, email);
   
   ALTER TABLE emailverification_verified_emails
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('emailverification_tokens', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   CALL st_add_column_if_not_exists('emailverification_tokens', 'tenant_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE emailverification_tokens
     ADD PRIMARY KEY (app_id, tenant_id, user_id, email, token);
   
   ALTER TABLE emailverification_tokens
     ADD FOREIGN KEY (app_id, tenant_id)
       REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;
   
   ALTER TABLE emailverification_tokens
     ADD CONSTRAINT token UNIQUE (token);
   
   CREATE INDEX emailverification_tokens_index ON emailverification_tokens(token_expiry);
   
   -- EmailPassword
   
   CALL st_add_column_if_not_exists('emailpassword_users', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE emailpassword_users
     ADD PRIMARY KEY (app_id, user_id);
   
   ALTER TABLE emailpassword_users
     ADD 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
   );
   
   ALTER TABLE emailpassword_user_to_tenant
     ADD PRIMARY KEY (app_id, tenant_id, user_id);
   
   ALTER TABLE emailpassword_user_to_tenant
     ADD CONSTRAINT email UNIQUE (app_id, tenant_id, email);
   
   ALTER TABLE emailpassword_user_to_tenant
     ADD CONSTRAINT FOREIGN KEY (app_id, tenant_id, user_id)
       REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;
   
   INSERT IGNORE INTO emailpassword_user_to_tenant (user_id, email)
     SELECT user_id, email FROM emailpassword_users;
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('emailpassword_pswd_reset_tokens', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE emailpassword_pswd_reset_tokens
     ADD PRIMARY KEY (app_id, user_id, token);
   
   ALTER TABLE emailpassword_pswd_reset_tokens
     ADD FOREIGN KEY (app_id, user_id)
       REFERENCES emailpassword_users (app_id, user_id) ON DELETE CASCADE;
   
   ALTER TABLE emailpassword_pswd_reset_tokens
     ADD CONSTRAINT token UNIQUE (token);
   
   CREATE INDEX emailpassword_password_reset_token_expiry_index ON emailpassword_pswd_reset_tokens (token_expiry);
   
   -- Passwordless
   
   CALL st_add_column_if_not_exists('passwordless_users', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE passwordless_users
     ADD PRIMARY KEY (app_id, user_id);
   
   ALTER TABLE passwordless_users
     ADD 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)
   );
   
   ALTER TABLE passwordless_user_to_tenant
     ADD PRIMARY KEY (app_id, tenant_id, user_id);
   
   ALTER TABLE passwordless_user_to_tenant
     ADD CONSTRAINT email UNIQUE (app_id, tenant_id, email);
   
   ALTER TABLE passwordless_user_to_tenant
     ADD CONSTRAINT phone_number UNIQUE (app_id, tenant_id, phone_number);
   
   ALTER TABLE passwordless_user_to_tenant
     ADD FOREIGN KEY (app_id, tenant_id, user_id)
       REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;
   
   INSERT IGNORE INTO passwordless_user_to_tenant (user_id, email, phone_number)
     SELECT user_id, email, phone_number FROM passwordless_users;
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('passwordless_devices', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   CALL st_add_column_if_not_exists('passwordless_devices', 'tenant_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE passwordless_devices
     ADD PRIMARY KEY (app_id, tenant_id, device_id_hash);
   
   ALTER TABLE passwordless_devices
     ADD FOREIGN KEY (app_id, tenant_id)
       REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;
   
   CREATE INDEX passwordless_devices_email_index ON passwordless_devices (app_id, tenant_id, email);
   
   CREATE INDEX passwordless_devices_phone_number_index ON passwordless_devices (app_id, tenant_id, phone_number);
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('passwordless_codes', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   CALL st_add_column_if_not_exists('passwordless_codes', 'tenant_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE passwordless_codes
     ADD PRIMARY KEY (app_id, tenant_id, code_id);
   
   ALTER TABLE passwordless_codes
     ADD 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
     ADD CONSTRAINT link_code_hash
       UNIQUE (app_id, tenant_id, link_code_hash);
   
   CREATE INDEX passwordless_codes_created_at_index ON passwordless_codes (app_id, tenant_id, created_at);
   
   -- ThirdParty
   
   CALL st_add_column_if_not_exists('thirdparty_users', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE thirdparty_users
     ADD PRIMARY KEY (app_id, user_id);
   
   ALTER TABLE thirdparty_users
     ADD FOREIGN KEY (app_id, user_id)
       REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;
   
   CREATE INDEX thirdparty_users_thirdparty_user_id_index ON thirdparty_users (app_id, third_party_id, third_party_user_id);
   
   CREATE INDEX 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
   );
   
   ALTER TABLE thirdparty_user_to_tenant
     ADD PRIMARY KEY (app_id, tenant_id, user_id);
   
   ALTER TABLE thirdparty_user_to_tenant
     ADD CONSTRAINT third_party_user_id
       UNIQUE (app_id, tenant_id, third_party_id, third_party_user_id);
   
   ALTER TABLE thirdparty_user_to_tenant
     ADD FOREIGN KEY (app_id, tenant_id, user_id)
       REFERENCES all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;
   
   INSERT IGNORE 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;
   
   -- UserIdMapping
   
   CALL st_add_column_if_not_exists('userid_mapping', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE userid_mapping
     ADD PRIMARY KEY (app_id, supertokens_user_id, external_user_id);
   
   ALTER TABLE userid_mapping
     ADD CONSTRAINT supertokens_user_id
       UNIQUE (app_id, supertokens_user_id);
   
   ALTER TABLE userid_mapping
     ADD CONSTRAINT external_user_id
       UNIQUE (app_id, external_user_id);
   
   ALTER TABLE userid_mapping
     ADD FOREIGN KEY (app_id, supertokens_user_id)
       REFERENCES app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;
   
   -- UserRoles
   
   CALL st_add_column_if_not_exists('roles', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE roles
     ADD PRIMARY KEY (app_id, role);
   
   ALTER TABLE roles
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('role_permissions', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE role_permissions
     ADD PRIMARY KEY (app_id, role, permission);
   
   ALTER TABLE role_permissions
     ADD FOREIGN KEY (app_id, role)
       REFERENCES roles (app_id, role) ON DELETE CASCADE;
   
   CREATE INDEX role_permissions_permission_index ON role_permissions (app_id, permission);
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('user_roles', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   CALL st_add_column_if_not_exists('user_roles', 'tenant_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE user_roles
     ADD PRIMARY KEY (app_id, tenant_id, user_id, role);
   
   ALTER TABLE user_roles
     ADD FOREIGN KEY (app_id, role)
       REFERENCES roles (app_id, role) ON DELETE CASCADE;
   
   ALTER TABLE user_roles
     ADD FOREIGN KEY (app_id, tenant_id)
       REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;
   
   CREATE INDEX user_roles_role_index ON user_roles (app_id, tenant_id, role);
   
   -- UserMetadata
   
   CALL st_add_column_if_not_exists('user_metadata', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE user_metadata
     ADD PRIMARY KEY (app_id, user_id);
   
   ALTER TABLE user_metadata
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   -- Dashboard
   
   CALL st_add_column_if_not_exists('dashboard_users', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE dashboard_users
     ADD PRIMARY KEY (app_id, user_id);
   
   ALTER TABLE dashboard_users
     ADD CONSTRAINT email
       UNIQUE (app_id, email);
   
   ALTER TABLE dashboard_users
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('dashboard_user_sessions', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE dashboard_user_sessions
     ADD PRIMARY KEY (app_id, session_id);
   
   ALTER TABLE dashboard_user_sessions
     ADD FOREIGN KEY (app_id, user_id)
       REFERENCES dashboard_users (app_id, user_id) ON DELETE CASCADE;
   
   CREATE INDEX dashboard_user_sessions_expiry_index ON dashboard_user_sessions (expiry);
   
   -- TOTP
   
   CALL st_add_column_if_not_exists('totp_users', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE totp_users
     ADD PRIMARY KEY (app_id, user_id);
   
   ALTER TABLE totp_users
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('totp_user_devices', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE totp_user_devices
     ADD PRIMARY KEY (app_id, user_id, device_name);
   
   ALTER TABLE totp_user_devices
     ADD FOREIGN KEY (app_id, user_id)
       REFERENCES totp_users (app_id, user_id) ON DELETE CASCADE;
   
   ------------------------------------------------------------
   
   CALL st_add_column_if_not_exists('totp_used_codes', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   CALL st_add_column_if_not_exists('totp_used_codes', 'tenant_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE totp_used_codes
     ADD PRIMARY KEY (app_id, tenant_id, user_id, created_time_ms);
   
   ALTER TABLE totp_used_codes
     ADD FOREIGN KEY (app_id, user_id)
       REFERENCES totp_users (app_id, user_id) ON DELETE CASCADE;
   
   ALTER TABLE totp_used_codes
     ADD FOREIGN KEY (app_id, tenant_id)
       REFERENCES tenants (app_id, tenant_id) ON DELETE CASCADE;
   
   CREATE INDEX totp_used_codes_expiry_time_ms_index ON totp_used_codes (app_id, tenant_id, expiry_time_ms);
   
   -- ActiveUsers
   
   CALL st_add_column_if_not_exists('user_last_active', 'app_id', 'VARCHAR(64)', 'NOT NULL DEFAULT \'public\'', @status_message);
   
   ALTER TABLE user_last_active
     ADD PRIMARY KEY (app_id, user_id);
   
   ALTER TABLE user_last_active
     ADD FOREIGN KEY (app_id)
       REFERENCES apps (app_id) ON DELETE CASCADE;
   
   -- Drop procedures
   
   DROP PROCEDURE st_drop_all_fkeys;
   
   DROP PROCEDURE st_drop_all_keys;
   
   DROP PROCEDURE st_drop_all_pkeys;
   
   DROP PROCEDURE st_drop_all_indexes;
   
   DROP PROCEDURE st_add_column_if_not_exists;

4. Start the new instance(s) of the core (version 6.0.0)

[5.0.0] - 2023-04-05

Changes

• Updated the java-jwt dependency version
• Increases free Dashboard user count to 3

Fixes

• Fixed creating JWTs using MongoDB if a key already exists

Breaking changes

• Using an internal SemVer class to handle version numbers. This will make handling CDI version ranges easier.
• Support for CDI version 2.21
  • Removed POST /recipe/handshake
  • Added useDynamicSigningKey into createNewSession (POST /recipe/session), replacing access_token_signing_key_dynamic used in CDI<=2.18
  • Added useStaticSigningKey into createSignedJWT (POST /recipe/jwt)
  • Added checkDatabase into verifySession (POST /recipe/session/verify), replacing access_token_blacklisting used in CDI<=2.18
  • Removed idRefreshToken, jwtSigningPublicKey, jwtSigningPublicKeyExpiryTime and jwtSigningPublicKeyList from responses
  • Deprecated GET /recipe/jwt/jwks
  • Added GET /.well-known/jwks.json: a standard jwks
• Added new access token version
  • Uses standard prop names (i.e.: sub instead of userId)
  • Contains the id of the signing key in the header (as kid)
  • Stores the user payload merged into the root level, instead of the userData prop
• Session handling function now throw if the user payload contains protected props (sub, iat, exp, sessionHandle, refreshTokenHash1, parentRefreshTokenHash1, antiCsrfToken)
  • A related exception type was added as AccessTokenPayloadError
• Refactored the handling of signing keys
• createNewSession now takes a useStaticKey parameter instead of depending on the access_token_signing_key_dynamic config value
• createJWTToken now supports signing by a dynamic key
• getSession now takes a checkDatabase parameter instead of using the access_token_blacklisting config value
• Updated plugin interface version to 2.21

Configuration Changes

• access_token_signing_key_dynamic is now deprecated, only used for requests with CDI<=2.18
• access_token_blacklisting is now deprecated, only used for requests with CDI<=2.18
• Renamed access_token_signing_key_update_interval to access_token_dynamic_signing_key_update_interval

Database Changes

• Added new useStaticKey field into session info
• Manual migration is also required if access_token_signing_key_dynamic was set to false

Migration steps for SQL

• If using access_token_signing_key_dynamic false:

  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 using access_token_signing_key_dynamic true or not set:

  • 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;

Migration steps for MongoDB

• If using access_token_signing_key_dynamic false:

  db.session_info.update({},
    {
      "$set": {
        "use_static_key": true
      }
    });
  

  db.key_value.aggregate([
    {
      "$match": {
        _id: "access_token_signing_key_list"
      }
    },
    {
      $unwind: "$keys"
    },
    {
      $addFields: {
        _id: {
          "$concat": [
            "s-",
            {
              $convert: {
                input: "$keys.created_at_time",
                to: "string"
              }
            }
          ]
        },
        "key_string": "$keys.value",
        "algorithm": "RS256",
        "created_at": "$keys.created_at_time",
        
      }
    },
    {
      "$project": {
        "keys": 0,
        
      }
    },
    {
      "$merge": {
        "into": "jwt_signing_keys",
        
      }
    }
  ]);
  

• If using access_token_signing_key_dynamic true or not set:

  db.session_info.update({},
    {
      "$set": {
        "use_static_key": false
      }
    });
  

[4.6.0] - 2023-03-30

• Add Optional Search Tags to Pagination API to enable dashboard search

New APIs:

• GET /user/search/tags retrieves the available search tags

[4.5.0] - 2023-03-27

• Add TOTP recipe

Database changes:

• Add new tables for TOTP recipe:
  • totp_users that stores the users that have enabled TOTP
  • totp_user_devices that stores devices (each device has its own secret) for each user
  • totp_used_codes that stores used codes for each user. This is to implement rate limiting and prevent replay attacks.
  • user_last_active that stores the last active time for each user.

New APIs:

• GET /users/count/active to fetch the number of active users after the given timestamp.
• POST /recipe/totp/device to create a new device as well as the user if it doesn't exist.
• POST /recipe/totp/device/verify to verify a device. This is to ensure that the user has access to the device.
• POST /recipe/totp/verify to verify a code and continue the login flow.
• PUT /recipe/totp/device to update the name of a device. Name is just a string that the user can set to identify the device.
• GET /recipe/totp/device/list to get all devices for a user.
• POST /recipe/totp/device/remove to remove a device. If the user has no more devices, the user is also removed.

[4.4.2] - 2023-03-16

• Adds null check in email normalisation to fix: supertokens/supertokens-node#514

[4.4.1] - 2023-03-09

• Normalises email in all APIs in which email was not being normalised: supertokens#577

[4.4.0] - 2023-02-21

Added

• Dashboard Recipe
• Support with CDI version 2.18

Database Changes

• Adds dashboard_users table
• Adds dashboard_user_sessions table

[4.3.0] - 2023-01-05

• Adds feature flag, ee folder and APIs to add / remove license keys for enterprise features.

[4.2.1] - 2022-11-24

• Updates the type of access_token_validity in the CoreConfig from int to long

[4.2.0] - 2022-11-07

• Update dependencies for security updates: supertokens#525

[4.1.1] - 2022-10-13

• Updates core routes to now allow for trailing slashes

[4.1.0] - 2022-09-22

• Adds request IP allow & deny list: supertokens#511

[4.0.1] - 2022-09-19

• Fixes bug related to implementationDependencies.json

[4.0.0] - 2022-09-19

Added

• EmailPassword User migration API which allows you to import users with their email and password hashes.
• Support to import users with password hashes from Firebase
• Support with CDI version 2.16
• Hello API on / route.

Database Changes

• Updates the password_hash column in the emailpassword_users table from VARCHAR(128) to VARCHAR(256) to support more password hash lengths.

• Updates the third_party_user_id column in the thirdparty_users table from VARCHAR(128) to VARCHAR(256) to resolve supertokens#306

• For legacy users who are self hosting the SuperTokens core run the following command to update your database with the changes:

  • With MySql: ALTER TABLE thirdparty_users MODIFY third_party_user_id VARCHAR(256); ALTER TABLE emailpassword_users MODIFY password_hash VARCHAR(256);
  • With PostgreSQL: ALTER TABLE thirdparty_users ALTER COLUMN third_party_user_id TYPE VARCHAR(256); ALTER TABLE emailpassword_users ALTER COLUMN password_hash TYPE VARCHAR(256);

[3.16.2] - 2022-09-02

Bug fixes

• Updated java-jwt to handle null claims in JWTs

[3.16.1] - 2022-09-02

Bug fixes

• Fixed handling of null in access token payloads: supertokens#499

[3.16.0] - 2022-08-18

• Changes logging level of API start / finished & Cronjob start / finished to be INFO level instead of DEBUG level.
• Added new config log_level to set logging level. Possible values are DEBUG | INFO | WARN | ERROR | NONE. As an example, setting the log level to WARN would make the core print out WARN and ERROR level logs.

[3.15.1] - 2022-08-10

• Updates UserIdMapping recipe to resolve UserId Mappings for Auth recipes in the core itself

[3.15.0] - 2022-07-25

• Adds UserIdMapping recipe
• Support for collecting and displaying failing tests

Database changes

• Adds userid_mapping table

[3.14.0] - 2022-06-07

• Fixes /recipe/session/user GET to return only session handles that have not expired.
• Support for new plugin interface version (v2.15)
• Checks for if the session has expired in updateSession before calling the update function.

[3.13.0] - 2022-05-05

• Adds UserRoles recipe
• Fixes base_path config option not being observed when running supertokens list
• Adds base_path normalization logic

Database changes

• Adds roles, role_permissions and user_roles table

[3.12.1] - 2022-04-02

Changes

• Changed default --with_argon2_hashing_pool_size in hashingCalibrate CLI command to 1.

[3.12.0] - 2022-04-01

• Adds github action for running tests against in memory db.
• Adds github action for checking if "Run tests" action was completed (to run in PRs)
• Fixes how config values are changed during tests.
• Adds 60 mins timeout to github action jobs
• Moves deleting user metadata to happen before deleting the actual user.
• Adds support for argon2 hashing.
• Adds colours to CLI output (in case of errors).

New config:

• password_hashing_alg
• argon2_iterations
• argon2_memory_kb
• argon2_parallelism
• argon2_hashing_pool_size
• bcrypt_log_rounds

New CLI command:

• supertokens hashingCalibrate: Used to calibrate argon2 and bcrypt passing hashing params.

[3.11.0] - 2022-03-19

Changes

• Fixes memory leak during testing.
• Updated plugin interface version
• Adds usermetadata recipe
• Update CONTRIBUTING.md with instructions for gitpod setup

Database changes

• Added user_metadata table

[3.10.0] - 2022-02-23

• Updated plugin interface version
• Fixed ResultSet instances to avoid Memory Leaks

[3.9.1] - 2022-02-16

• Fixed supertokens#373: Catching StorageTransactionLogicException in transaction helper function for retries

[3.9.0] - 2022-01-31

Changes

• Supporting CDI v2.12
• Adding the userId to the reponse of recipe/user/password/reset
• Adds support for providing base path for all APIs: supertokens/supertokens-node#252
• Add workflow to verify if pr title follows conventional commits

New config param:

• base_path - default is "" (No base path)

[3.8.0] - 2022-01-14

Added

• Added Passwordless recipe ( with unit test coverage )

Database changes

• Adds new tables for passwordless:
  • 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

Changes

• New recipeId in /users response with a corresponding new user type

[3.7.0] - 2021-12-16

Added

• Delete user endpoint

[3.6.1] - 2021-11-15

Fixes

• Issue with JWT expiry always being lower than expected
• Modulus and exponent for JsonWebKeys are now sent as unsigned when fetching public keys from the /jwt/jwks.json endpoint. Both values are url encoded without any padding.

Changes

• JWT creation logic to add a iss claim only if none is provided

[3.6.0] - 2021-08-26

Added

• New config values password_reset_token_lifetime and email_verification_token_lifetime: supertokens#297
• Added support for multiple access token signing keys: supertokens#305
• Updated CDI version
• Added a table to store access token signing keys into SQL schema, called session_access_token_signing_keys
• New JWT recipe to create JWT tokens using SuperTokens
• New table jwt_signing_keys added to store keys used by the JWT recipe

[3.5.3] - 2021-09-20

Changes

• Explicitly adds UTF-8 compatible conversion when encoding / decoding base64 strings.

[3.5.2] - 2021-09-01

Fixes

• Issue with verifying refresh token throwing an unauthorised exception due to a db connection error.
• Sends far ahead jwt signing key expiry time in case updating them is disabled: supertokens#304

Changes

• Changes JWT signing key update interval to not be limited to 720 hours

[3.5.1] - 2021-08-25

Added

• Logs non "OK" status code from APIs for debugging purposes.

Fixed:

• Always throws unauthorised response if refresh token is not valid - previously it was throwing a 500 error in case it was not properly base 64 encoded.

[3.5.0] - 2021-06-20

Changed

• Make emailverificaiton tables take a generic userId: supertokens#258
• Adds new count and pagination APIs: supertokens#259
• Adds new API to get session data, and deprecates older one to get session and JWT payload separately: supertokens#255
• Removed isVerified boolean from thirdparty sign in up API as per CDI spec 2.8, and hence does not do email verification in this API either. Also related to supertokens#295

Added

• Add GET /recipe/users/by-email?email=john@example.com endpoint for ThirdParty recipe to fetch all users with given email
• Add new emailverification APIs for remove tokens and unverify email.
• Add PUT /recipe/user for emailpassword recipe to change user's password or email.

[3.4.2] - 2021-06-27

Fixes

• NullPointerException that is thrown in AccessTokenSigningKey.java class when the keyInfo object is accessed in parallel after the signing key has expired: supertokens#282

[3.4.1] - 2021-06-18

Added

• test_mode to the options for running the core so that it can be run in test mode whilst being tested by the backend SDK.
• Adds jwtSigningPublicKey and jwtSigningPublicKeyExpiryTime to API response when returning TRY_REFRESH_TOKEN from session verify.

[3.4.0] - 2021-04-22

Changed

• Uses Open JDK 15.0.1

[3.3.0] - 2021-02-16

Changed

• Extracted email verification into its own recipe
• ThirdParty recipe API

[3.2.0] - 2021-01-26

Changed

• Normalises email by making it all lower case
• Changes in handshake API
• Changes in config
• Changes in session create, verify and refresh APis

[3.1.0] - 2021-01-14

Changed

• Used rowmapper for in memory db
• Adds email verification APIs
• Adds user pagination APIs
• Adds timeJoined to whenever a user object is returned from an API

[3.0.1] - 2020-10-27

Changed

• Makes Hello API do a db query as well for better status checking

[3.0.0] - 2020-10-25

Changed

• Changes as per CDI 2.4: supertokens/core-driver-interface#1
• In memory db uses the SQL interface
• Emailpassword recipe functions and APIs
• Deprecates the need for a separate SQLite repo (since the in mem one already exists within the core)

[2.5.2] - 2020-10-25

Fixed

• Issue #84 - Correct access token signing key expiry not being sent by APIs

[2.5.1] - 2020-10-08

Changed

• Fixed issue of docker image hanging when run in foreground

[2.5.0] - 2020-10-08

Added

• Updates the access token if blacklisting is switched on and the JWT payload has been changed somehow
• API key support
• JWT Api Key Rotation

Removed

• Compatibility with the inefficient method for handling refresh tokens.

[2.4.0] - 2020-09-09

Added

• CSRF check in refresh API
• set csrf config to false by default
• compatibility with CDI 2.3

Fixed

• When regenerating session, uses old access tokens' parentRefreshTokenHash1 instead of null

Changed

• Optimises refresh token to not store old tokens in the database
• removes the need for a license key
• removes API Pings

[2.3.0] - 2020-08-11

Changed

• Makes default session expiry status code 401
• Makes default refresh API path "/session/refresh"
• Compatibility with CDI 2.2. Makes cookie_domain default value to not set, so that it will work with any API
• Makes sameSite = lax by default
• If licenseKey is missing, then dependency jars are downloaded in DEV mode

[2.2.3] - 2020-08-10

Changes

• Makes license Apache 2.0

[2.2.2] - 2020-07-02

Fixed

• Changes how versioning works to make it per API call.
• Supports CDI 2.1

[2.2.1] - 2020-05-14

Fixed

• Forcing of no in memory database flag to start command on Linux fixed

[2.2.0] - 2020-05-20

Added

• Uses in memory database in dev mode if database is not configured
• Removes the need to specify dev / production when running the start command

[2.1.0] - 2020-04-30

Added

• Compatibility with CDI 2.0
• API versions
• SameSite cookie option
• Updating of JWT payload
• Session expired status code configuration
• Partial lmrt support

[2.0.0] - 2020-04-07

Added

• Compatibility with NoSQL databases like MongoDB
• Setting sameSite cookie option. However, this is not usable in this release.

[1.1.1] - 2020-03-23

Changed

• Adds #!/bin/bash in scripts

[1.1.0] - 2020-03-23

Changed

• Allow for an unlimited number of SuperTokens instances in production mode
• License changes to reflect the above