Upgrade SuperTokens to v6

[n1ru4l]

Supertokens 5.6 → 6.0 Upgrade Protocol

Supertokens 4.6 to 5.0 has breaking changes, which require careful granular steps for upgrading and deploying GraphQL Hive.

Rollout Plan

1. Make sure supertokens 5.0 is running

If you are still running supertokens pre 5.0, you first need to follow the steps in #2612

2. Stop the supertokens instance

Unfortunately, it is not possible to run the upgrade without downtime. You need to stop the supertokens core instance.
See the supertokens changelog

3. Execute SQL on the database

-- General Tables

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

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

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

CREATE TABLE IF NOT EXISTS supertokens_tenants (
  app_id VARCHAR(64) NOT NULL DEFAULT 'public',
  tenant_id VARCHAR(64) NOT NULL DEFAULT 'public',
  created_at_time BIGINT ,
  CONSTRAINT supertokens_tenants_pkey
    PRIMARY KEY (app_id, tenant_id),
  CONSTRAINT supertokens_tenants_app_id_fkey FOREIGN KEY(app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE
);

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

CREATE INDEX IF NOT EXISTS supertokens_tenants_app_id_index ON supertokens_tenants (app_id);

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

ALTER TABLE supertokens_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 supertokens_key_value
  DROP CONSTRAINT supertokens_key_value_pkey;

ALTER TABLE supertokens_key_value
  ADD CONSTRAINT supertokens_key_value_pkey 
    PRIMARY KEY (app_id, tenant_id, name);

ALTER TABLE supertokens_key_value
  DROP CONSTRAINT IF EXISTS supertokens_key_value_tenant_id_fkey;

ALTER TABLE supertokens_key_value
  ADD CONSTRAINT supertokens_key_value_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES supertokens_tenants (app_id, tenant_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_key_value_tenant_id_index ON supertokens_key_value (app_id, tenant_id);

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

CREATE TABLE IF NOT EXISTS supertokens_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 supertokens_app_id_to_user_id_pkey
    PRIMARY KEY (app_id, user_id),
  CONSTRAINT supertokens_app_id_to_user_id_app_id_fkey
    FOREIGN KEY(app_id) REFERENCES supertokens_apps (app_id) ON DELETE CASCADE
);

INSERT INTO supertokens_app_id_to_user_id (user_id, recipe_id) 
  SELECT user_id, recipe_id
  FROM supertokens_all_auth_recipe_users ON CONFLICT DO NOTHING;

CREATE INDEX IF NOT EXISTS supertokens_app_id_to_user_id_app_id_index ON supertokens_app_id_to_user_id (app_id);

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

ALTER TABLE supertokens_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 supertokens_all_auth_recipe_users
  DROP CONSTRAINT supertokens_all_auth_recipe_users_pkey CASCADE;

ALTER TABLE supertokens_all_auth_recipe_users
  ADD CONSTRAINT supertokens_all_auth_recipe_users_pkey 
    PRIMARY KEY (app_id, tenant_id, user_id);

ALTER TABLE supertokens_all_auth_recipe_users
  DROP CONSTRAINT IF EXISTS supertokens_all_auth_recipe_users_tenant_id_fkey;

ALTER TABLE supertokens_all_auth_recipe_users
  ADD CONSTRAINT supertokens_all_auth_recipe_users_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES supertokens_tenants (app_id, tenant_id) ON DELETE CASCADE;

ALTER TABLE supertokens_all_auth_recipe_users
  DROP CONSTRAINT IF EXISTS supertokens_all_auth_recipe_users_user_id_fkey;

ALTER TABLE supertokens_all_auth_recipe_users
  ADD CONSTRAINT supertokens_all_auth_recipe_users_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES supertokens_app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

DROP INDEX IF EXISTS supertokens_all_auth_recipe_users_pagination_index;
DROP INDEX IF EXISTS all_auth_recipe_users_pagination_index;

CREATE INDEX supertokens_all_auth_recipe_users_pagination_index ON supertokens_all_auth_recipe_users (time_joined DESC, user_id DESC, tenant_id DESC, app_id DESC);

CREATE INDEX IF NOT EXISTS supertokens_all_auth_recipe_user_id_index ON supertokens_all_auth_recipe_users (app_id, user_id);

CREATE INDEX IF NOT EXISTS supertokens_all_auth_recipe_tenant_id_index ON supertokens_all_auth_recipe_users (app_id, tenant_id);

-- Multitenancy

CREATE TABLE IF NOT EXISTS supertokens_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 supertokens_tenant_configs_pkey
    PRIMARY KEY (connection_uri_domain, app_id, tenant_id)
);

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

CREATE TABLE IF NOT EXISTS supertokens_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 supertokens_tenant_thirdparty_providers_pkey
    PRIMARY KEY (connection_uri_domain, app_id, tenant_id, third_party_id),
  CONSTRAINT supertokens_tenant_thirdparty_providers_tenant_id_fkey
    FOREIGN KEY(connection_uri_domain, app_id, tenant_id)
    REFERENCES supertokens_tenant_configs (connection_uri_domain, app_id, tenant_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS supertokens_tenant_thirdparty_providers_tenant_id_index ON supertokens_tenant_thirdparty_providers (connection_uri_domain, app_id, tenant_id);

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

CREATE TABLE IF NOT EXISTS supertokens_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 supertokens_tenant_thirdparty_provider_clients_pkey
    PRIMARY KEY (connection_uri_domain, app_id, tenant_id, third_party_id, client_type),
  CONSTRAINT supertokens_tenant_thirdparty_provider_clients_third_party_id_fkey
    FOREIGN KEY (connection_uri_domain, app_id, tenant_id, third_party_id)
    REFERENCES supertokens_tenant_thirdparty_providers (connection_uri_domain, app_id, tenant_id, third_party_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS supertokens_tenant_thirdparty_provider_clients_third_party_id_index ON supertokens_tenant_thirdparty_provider_clients (connection_uri_domain, app_id, tenant_id, third_party_id);

-- Session

ALTER TABLE supertokens_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 supertokens_session_info
  DROP CONSTRAINT supertokens_session_info_pkey CASCADE;

ALTER TABLE supertokens_session_info
  ADD CONSTRAINT supertokens_session_info_pkey 
    PRIMARY KEY (app_id, tenant_id, session_handle);

ALTER TABLE supertokens_session_info
  DROP CONSTRAINT IF EXISTS supertokens_session_info_tenant_id_fkey;

ALTER TABLE supertokens_session_info
  ADD CONSTRAINT supertokens_session_info_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES supertokens_tenants (app_id, tenant_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_session_expiry_index ON supertokens_session_info (expires_at);

CREATE INDEX IF NOT EXISTS supertokens_session_info_tenant_id_index ON supertokens_session_info (app_id, tenant_id);

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

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

ALTER TABLE supertokens_session_access_token_signing_keys
  DROP CONSTRAINT supertokens_session_access_token_signing_keys_pkey CASCADE;

ALTER TABLE supertokens_session_access_token_signing_keys
  ADD CONSTRAINT supertokens_session_access_token_signing_keys_pkey 
    PRIMARY KEY (app_id, created_at_time);

ALTER TABLE supertokens_session_access_token_signing_keys
  DROP CONSTRAINT IF EXISTS supertokens_session_access_token_signing_keys_app_id_fkey;

ALTER TABLE supertokens_session_access_token_signing_keys
  ADD CONSTRAINT supertokens_session_access_token_signing_keys_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_access_token_signing_keys_app_id_index ON supertokens_session_access_token_signing_keys (app_id);

-- JWT

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

ALTER TABLE supertokens_jwt_signing_keys
  DROP CONSTRAINT supertokens_jwt_signing_keys_pkey CASCADE;

ALTER TABLE supertokens_jwt_signing_keys
  ADD CONSTRAINT supertokens_jwt_signing_keys_pkey 
    PRIMARY KEY (app_id, key_id);

ALTER TABLE supertokens_jwt_signing_keys
  DROP CONSTRAINT IF EXISTS supertokens_jwt_signing_keys_app_id_fkey;

ALTER TABLE supertokens_jwt_signing_keys
  ADD CONSTRAINT supertokens_jwt_signing_keys_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_jwt_signing_keys_app_id_index ON supertokens_jwt_signing_keys (app_id);

-- EmailVerification

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

ALTER TABLE supertokens_emailverification_verified_emails
  DROP CONSTRAINT supertokens_emailverification_verified_emails_pkey CASCADE;

ALTER TABLE supertokens_emailverification_verified_emails
  ADD CONSTRAINT supertokens_emailverification_verified_emails_pkey 
    PRIMARY KEY (app_id, user_id, email);

ALTER TABLE supertokens_emailverification_verified_emails
  DROP CONSTRAINT IF EXISTS supertokens_emailverification_verified_emails_app_id_fkey;

ALTER TABLE supertokens_emailverification_verified_emails
  ADD CONSTRAINT supertokens_emailverification_verified_emails_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_emailverification_verified_emails_app_id_index ON supertokens_emailverification_verified_emails (app_id);

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

ALTER TABLE supertokens_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 supertokens_emailverification_tokens
  DROP CONSTRAINT supertokens_emailverification_tokens_pkey CASCADE;

ALTER TABLE supertokens_emailverification_tokens
  ADD CONSTRAINT supertokens_emailverification_tokens_pkey 
    PRIMARY KEY (app_id, tenant_id, user_id, email, token);

ALTER TABLE supertokens_emailverification_tokens
  DROP CONSTRAINT IF EXISTS supertokens_emailverification_tokens_tenant_id_fkey;

ALTER TABLE supertokens_emailverification_tokens
  ADD CONSTRAINT supertokens_emailverification_tokens_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES supertokens_tenants (app_id, tenant_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_emailverification_tokens_tenant_id_index ON supertokens_emailverification_tokens (app_id, tenant_id);

-- EmailPassword

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

ALTER TABLE supertokens_emailpassword_users
  DROP CONSTRAINT supertokens_emailpassword_users_pkey CASCADE;

ALTER TABLE supertokens_emailpassword_users
  DROP CONSTRAINT IF EXISTS supertokens_emailpassword_users_email_key CASCADE;

ALTER TABLE supertokens_emailpassword_users
  ADD CONSTRAINT supertokens_emailpassword_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE supertokens_emailpassword_users
  DROP CONSTRAINT IF EXISTS supertokens_emailpassword_users_user_id_fkey;

ALTER TABLE supertokens_emailpassword_users
  ADD CONSTRAINT supertokens_emailpassword_users_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES supertokens_app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

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

CREATE TABLE IF NOT EXISTS supertokens_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 supertokens_emailpassword_user_to_tenant_email_key
    UNIQUE (app_id, tenant_id, email),
  CONSTRAINT supertokens_emailpassword_user_to_tenant_pkey
    PRIMARY KEY (app_id, tenant_id, user_id),
  CONSTRAINT supertokens_emailpassword_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES supertokens_all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE
);

ALTER TABLE supertokens_emailpassword_user_to_tenant
  DROP CONSTRAINT IF EXISTS supertokens_emailpassword_user_to_tenant_email_key;

ALTER TABLE supertokens_emailpassword_user_to_tenant
  ADD CONSTRAINT supertokens_emailpassword_user_to_tenant_email_key
    UNIQUE (app_id, tenant_id, email);

ALTER TABLE supertokens_emailpassword_user_to_tenant
  DROP CONSTRAINT IF EXISTS supertokens_emailpassword_user_to_tenant_user_id_fkey;

ALTER TABLE supertokens_emailpassword_user_to_tenant
  ADD CONSTRAINT supertokens_emailpassword_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES supertokens_all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;

INSERT INTO supertokens_emailpassword_user_to_tenant (user_id, email)
  SELECT user_id, email FROM supertokens_emailpassword_users ON CONFLICT DO NOTHING;

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

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

ALTER TABLE supertokens_emailpassword_pswd_reset_tokens
  DROP CONSTRAINT supertokens_emailpassword_pswd_reset_tokens_pkey CASCADE;

ALTER TABLE supertokens_emailpassword_pswd_reset_tokens
  ADD CONSTRAINT supertokens_emailpassword_pswd_reset_tokens_pkey 
    PRIMARY KEY (app_id, user_id, token);

ALTER TABLE supertokens_emailpassword_pswd_reset_tokens
  DROP CONSTRAINT IF EXISTS supertokens_emailpassword_pswd_reset_tokens_user_id_fkey;

ALTER TABLE supertokens_emailpassword_pswd_reset_tokens
  ADD CONSTRAINT supertokens_emailpassword_pswd_reset_tokens_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES supertokens_emailpassword_users (app_id, user_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_emailpassword_pswd_reset_tokens_user_id_index ON supertokens_emailpassword_pswd_reset_tokens (app_id, user_id);

-- Passwordless

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

ALTER TABLE supertokens_passwordless_users
  DROP CONSTRAINT supertokens_passwordless_users_pkey CASCADE;

ALTER TABLE supertokens_passwordless_users
  ADD CONSTRAINT supertokens_passwordless_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE supertokens_passwordless_users
  DROP CONSTRAINT IF EXISTS supertokens_passwordless_users_email_key;

ALTER TABLE supertokens_passwordless_users
  DROP CONSTRAINT IF EXISTS supertokens_passwordless_users_phone_number_key;

ALTER TABLE supertokens_passwordless_users
  DROP CONSTRAINT IF EXISTS supertokens_passwordless_users_user_id_fkey;

ALTER TABLE supertokens_passwordless_users
  ADD CONSTRAINT supertokens_passwordless_users_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES supertokens_app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

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

CREATE TABLE IF NOT EXISTS supertokens_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 supertokens_passwordless_user_to_tenant_email_key
    UNIQUE (app_id, tenant_id, email),
  CONSTRAINT supertokens_passwordless_user_to_tenant_phone_number_key
    UNIQUE (app_id, tenant_id, phone_number),
  CONSTRAINT supertokens_passwordless_user_to_tenant_pkey
    PRIMARY KEY (app_id, tenant_id, user_id),
  CONSTRAINT supertokens_passwordless_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES supertokens_all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE
);

ALTER TABLE supertokens_passwordless_user_to_tenant
  DROP CONSTRAINT IF EXISTS supertokens_passwordless_user_to_tenant_user_id_fkey;

ALTER TABLE supertokens_passwordless_user_to_tenant
  ADD CONSTRAINT supertokens_passwordless_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES supertokens_all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;

INSERT INTO supertokens_passwordless_user_to_tenant (user_id, email, phone_number)
  SELECT user_id, email, phone_number FROM supertokens_passwordless_users ON CONFLICT DO NOTHING;

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

ALTER TABLE supertokens_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 supertokens_passwordless_devices
  DROP CONSTRAINT supertokens_passwordless_devices_pkey CASCADE;

ALTER TABLE supertokens_passwordless_devices
  ADD CONSTRAINT supertokens_passwordless_devices_pkey 
    PRIMARY KEY (app_id, tenant_id, device_id_hash);

ALTER TABLE supertokens_passwordless_devices
  DROP CONSTRAINT IF EXISTS supertokens_passwordless_devices_tenant_id_fkey;

ALTER TABLE supertokens_passwordless_devices
  ADD CONSTRAINT supertokens_passwordless_devices_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES supertokens_tenants (app_id, tenant_id) ON DELETE CASCADE;

DROP INDEX IF EXISTS supertokens_passwordless_devices_email_index;

CREATE INDEX IF NOT EXISTS supertokens_passwordless_devices_email_index ON supertokens_passwordless_devices (app_id, tenant_id, email);

DROP INDEX IF EXISTS supertokens_passwordless_devices_phone_number_index;

CREATE INDEX IF NOT EXISTS supertokens_passwordless_devices_phone_number_index ON supertokens_passwordless_devices (app_id, tenant_id, phone_number);

CREATE INDEX IF NOT EXISTS supertokens_passwordless_devices_tenant_id_index ON supertokens_passwordless_devices (app_id, tenant_id);

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

ALTER TABLE supertokens_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 supertokens_passwordless_codes
  DROP CONSTRAINT supertokens_passwordless_codes_pkey CASCADE;

ALTER TABLE supertokens_passwordless_codes
  ADD CONSTRAINT supertokens_passwordless_codes_pkey 
    PRIMARY KEY (app_id, tenant_id, code_id);

ALTER TABLE supertokens_passwordless_codes
  DROP CONSTRAINT IF EXISTS supertokens_passwordless_codes_device_id_hash_fkey;

ALTER TABLE supertokens_passwordless_codes
  ADD CONSTRAINT supertokens_passwordless_codes_device_id_hash_fkey 
    FOREIGN KEY (app_id, tenant_id, device_id_hash)
    REFERENCES supertokens_passwordless_devices (app_id, tenant_id, device_id_hash) ON DELETE CASCADE;

ALTER TABLE supertokens_passwordless_codes
  DROP CONSTRAINT supertokens_passwordless_codes_link_code_hash_key;

ALTER TABLE supertokens_passwordless_codes
  DROP CONSTRAINT IF EXISTS supertokens_passwordless_codes_link_code_hash_key;

ALTER TABLE supertokens_passwordless_codes
  ADD CONSTRAINT supertokens_passwordless_codes_link_code_hash_key
    UNIQUE (app_id, tenant_id, link_code_hash);

DROP INDEX IF EXISTS supertokens_passwordless_codes_created_at_index;

CREATE INDEX IF NOT EXISTS supertokens_passwordless_codes_created_at_index ON supertokens_passwordless_codes (app_id, tenant_id, created_at);

DROP INDEX IF EXISTS supertokens_passwordless_codes_device_id_hash_index;
CREATE INDEX IF NOT EXISTS supertokens_passwordless_codes_device_id_hash_index ON supertokens_passwordless_codes (app_id, tenant_id, device_id_hash);

-- ThirdParty

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

ALTER TABLE supertokens_thirdparty_users
  DROP CONSTRAINT supertokens_thirdparty_users_pkey CASCADE;

ALTER TABLE supertokens_thirdparty_users
  DROP CONSTRAINT IF EXISTS supertokens_thirdparty_users_user_id_key CASCADE;

ALTER TABLE supertokens_thirdparty_users
  ADD CONSTRAINT supertokens_thirdparty_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE supertokens_thirdparty_users
  DROP CONSTRAINT IF EXISTS supertokens_thirdparty_users_user_id_fkey;

ALTER TABLE supertokens_thirdparty_users
  ADD CONSTRAINT supertokens_thirdparty_users_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES supertokens_app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

DROP INDEX IF EXISTS supertokens_thirdparty_users_thirdparty_user_id_index;

CREATE INDEX IF NOT EXISTS supertokens_thirdparty_users_thirdparty_user_id_index ON supertokens_thirdparty_users (app_id, third_party_id, third_party_user_id);

DROP INDEX IF EXISTS supertokens_thirdparty_users_email_index;

CREATE INDEX IF NOT EXISTS supertokens_thirdparty_users_email_index ON supertokens_thirdparty_users (app_id, email);

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

CREATE TABLE IF NOT EXISTS supertokens_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 supertokens_thirdparty_user_to_tenant_third_party_user_id_key
    UNIQUE (app_id, tenant_id, third_party_id, third_party_user_id),
  CONSTRAINT supertokens_thirdparty_user_to_tenant_pkey
    PRIMARY KEY (app_id, tenant_id, user_id),
  CONSTRAINT supertokens_thirdparty_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES supertokens_all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE
);

ALTER TABLE supertokens_thirdparty_user_to_tenant
  DROP CONSTRAINT IF EXISTS supertokens_thirdparty_user_to_tenant_third_party_user_id_key;

ALTER TABLE supertokens_thirdparty_user_to_tenant
  ADD CONSTRAINT supertokens_thirdparty_user_to_tenant_third_party_user_id_key
    UNIQUE (app_id, tenant_id, third_party_id, third_party_user_id);

ALTER TABLE supertokens_thirdparty_user_to_tenant
  DROP CONSTRAINT IF EXISTS supertokens_thirdparty_user_to_tenant_user_id_fkey;

ALTER TABLE supertokens_thirdparty_user_to_tenant
  ADD CONSTRAINT supertokens_thirdparty_user_to_tenant_user_id_fkey
    FOREIGN KEY (app_id, tenant_id, user_id)
    REFERENCES supertokens_all_auth_recipe_users (app_id, tenant_id, user_id) ON DELETE CASCADE;

INSERT INTO supertokens_thirdparty_user_to_tenant (user_id, third_party_id, third_party_user_id)
  SELECT user_id, third_party_id, third_party_user_id FROM supertokens_thirdparty_users ON CONFLICT DO NOTHING;

-- UserIdMapping

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

ALTER TABLE supertokens_userid_mapping
  DROP CONSTRAINT IF EXISTS supertokens_userid_mapping_pkey CASCADE;

ALTER TABLE supertokens_userid_mapping
  ADD CONSTRAINT supertokens_userid_mapping_pkey 
    PRIMARY KEY (app_id, supertokens_user_id, external_user_id);

ALTER TABLE supertokens_userid_mapping
  DROP CONSTRAINT IF EXISTS supertokens_userid_mapping_supertokens_user_id_key;

ALTER TABLE supertokens_userid_mapping
  ADD CONSTRAINT supertokens_userid_mapping_supertokens_user_id_key
    UNIQUE (app_id, supertokens_user_id);

ALTER TABLE supertokens_userid_mapping
  DROP CONSTRAINT IF EXISTS supertokens_userid_mapping_external_user_id_key;

ALTER TABLE supertokens_userid_mapping
  ADD CONSTRAINT supertokens_userid_mapping_external_user_id_key
    UNIQUE (app_id, external_user_id);

ALTER TABLE supertokens_userid_mapping
  DROP CONSTRAINT IF EXISTS supertokens_userid_mapping_supertokens_user_id_fkey;

ALTER TABLE supertokens_userid_mapping
  ADD CONSTRAINT supertokens_userid_mapping_supertokens_user_id_fkey 
    FOREIGN KEY (app_id, supertokens_user_id)
    REFERENCES supertokens_app_id_to_user_id (app_id, user_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_userid_mapping_supertokens_user_id_index ON supertokens_userid_mapping (app_id, supertokens_user_id);

-- UserRoles

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

ALTER TABLE supertokens_roles
  DROP CONSTRAINT supertokens_roles_pkey CASCADE;

ALTER TABLE supertokens_roles
  ADD CONSTRAINT supertokens_roles_pkey 
    PRIMARY KEY (app_id, role);

ALTER TABLE supertokens_roles
  DROP CONSTRAINT IF EXISTS supertokens_roles_app_id_fkey;

ALTER TABLE supertokens_roles
  ADD CONSTRAINT supertokens_roles_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_roles_app_id_index ON supertokens_roles (app_id);

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

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

ALTER TABLE supertokens_role_permissions
  DROP CONSTRAINT supertokens_role_permissions_pkey CASCADE;

ALTER TABLE supertokens_role_permissions
  ADD CONSTRAINT supertokens_role_permissions_pkey 
    PRIMARY KEY (app_id, role, permission);

ALTER TABLE supertokens_role_permissions
  DROP CONSTRAINT IF EXISTS supertokens_role_permissions_role_fkey;

ALTER TABLE supertokens_role_permissions
  ADD CONSTRAINT supertokens_role_permissions_role_fkey 
    FOREIGN KEY (app_id, role)
    REFERENCES supertokens_roles (app_id, role) ON DELETE CASCADE;

DROP INDEX IF EXISTS supertokens_role_permissions_permission_index;

CREATE INDEX IF NOT EXISTS supertokens_role_permissions_permission_index ON supertokens_role_permissions (app_id, permission);

CREATE INDEX IF NOT EXISTS supertokens_role_permissions_role_index ON supertokens_role_permissions (app_id, role);

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

ALTER TABLE supertokens_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 supertokens_user_roles
  DROP CONSTRAINT supertokens_user_roles_pkey CASCADE;

ALTER TABLE supertokens_user_roles
  ADD CONSTRAINT supertokens_user_roles_pkey 
    PRIMARY KEY (app_id, tenant_id, user_id, role);

ALTER TABLE supertokens_user_roles
  DROP CONSTRAINT IF EXISTS supertokens_user_roles_tenant_id_fkey;

ALTER TABLE supertokens_user_roles
  ADD CONSTRAINT supertokens_user_roles_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES supertokens_tenants (app_id, tenant_id) ON DELETE CASCADE;

ALTER TABLE supertokens_user_roles
  DROP CONSTRAINT IF EXISTS supertokens_user_roles_role_fkey;

ALTER TABLE supertokens_user_roles
  ADD CONSTRAINT supertokens_user_roles_role_fkey 
    FOREIGN KEY (app_id, role)
    REFERENCES supertokens_roles (app_id, role) ON DELETE CASCADE;

DROP INDEX IF EXISTS supertokens_user_roles_role_index;

CREATE INDEX IF NOT EXISTS supertokens_user_roles_role_index ON supertokens_user_roles (app_id, tenant_id, role);

CREATE INDEX IF NOT EXISTS supertokens_user_roles_tenant_id_index ON supertokens_user_roles (app_id, tenant_id);

CREATE INDEX IF NOT EXISTS supertokens_user_roles_app_id_role_index ON supertokens_user_roles (app_id, role);

-- UserMetadata

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

ALTER TABLE supertokens_user_metadata
  DROP CONSTRAINT supertokens_user_metadata_pkey CASCADE;

ALTER TABLE supertokens_user_metadata
  ADD CONSTRAINT supertokens_user_metadata_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE supertokens_user_metadata
  DROP CONSTRAINT IF EXISTS supertokens_user_metadata_app_id_fkey;

ALTER TABLE supertokens_user_metadata
  ADD CONSTRAINT supertokens_user_metadata_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_user_metadata_app_id_index ON supertokens_user_metadata (app_id);

-- Dashboard

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

ALTER TABLE supertokens_dashboard_users
  DROP CONSTRAINT supertokens_dashboard_users_pkey CASCADE;

ALTER TABLE supertokens_dashboard_users
  ADD CONSTRAINT supertokens_dashboard_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE supertokens_dashboard_users
  DROP CONSTRAINT IF EXISTS supertokens_dashboard_users_email_key;

ALTER TABLE supertokens_dashboard_users
  ADD CONSTRAINT supertokens_dashboard_users_email_key
    UNIQUE (app_id, email);

ALTER TABLE supertokens_dashboard_users
  DROP CONSTRAINT IF EXISTS supertokens_dashboard_users_app_id_fkey;

ALTER TABLE supertokens_dashboard_users
  ADD CONSTRAINT supertokens_dashboard_users_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_dashboard_users_app_id_index ON supertokens_dashboard_users (app_id);

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

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

ALTER TABLE supertokens_dashboard_user_sessions
  DROP CONSTRAINT supertokens_dashboard_user_sessions_pkey CASCADE;

ALTER TABLE supertokens_dashboard_user_sessions
  ADD CONSTRAINT supertokens_dashboard_user_sessions_pkey 
    PRIMARY KEY (app_id, session_id);

ALTER TABLE supertokens_dashboard_user_sessions
  DROP CONSTRAINT IF EXISTS supertokens_dashboard_user_sessions_user_id_fkey;

ALTER TABLE supertokens_dashboard_user_sessions
  ADD CONSTRAINT supertokens_dashboard_user_sessions_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES supertokens_dashboard_users (app_id, user_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_dashboard_user_sessions_user_id_index ON supertokens_dashboard_user_sessions (app_id, user_id);

-- TOTP

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

ALTER TABLE supertokens_totp_users
  DROP CONSTRAINT supertokens_totp_users_pkey CASCADE;

ALTER TABLE supertokens_totp_users
  ADD CONSTRAINT supertokens_totp_users_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE supertokens_totp_users
  DROP CONSTRAINT IF EXISTS supertokens_totp_users_app_id_fkey;

ALTER TABLE supertokens_totp_users
  ADD CONSTRAINT supertokens_totp_users_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_totp_users_app_id_index ON supertokens_totp_users (app_id);

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

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

ALTER TABLE supertokens_totp_user_devices
  DROP CONSTRAINT supertokens_totp_user_devices_pkey;

ALTER TABLE supertokens_totp_user_devices
  ADD CONSTRAINT supertokens_totp_user_devices_pkey 
    PRIMARY KEY (app_id, user_id, device_name);

ALTER TABLE supertokens_totp_user_devices
  DROP CONSTRAINT IF EXISTS supertokens_totp_user_devices_user_id_fkey;

ALTER TABLE supertokens_totp_user_devices
  ADD CONSTRAINT supertokens_totp_user_devices_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES supertokens_totp_users (app_id, user_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_totp_user_devices_user_id_index ON supertokens_totp_user_devices (app_id, user_id);

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

ALTER TABLE supertokens_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 supertokens_totp_used_codes
  DROP CONSTRAINT supertokens_totp_used_codes_pkey CASCADE;

ALTER TABLE supertokens_totp_used_codes
  ADD CONSTRAINT supertokens_totp_used_codes_pkey 
    PRIMARY KEY (app_id, tenant_id, user_id, created_time_ms);

ALTER TABLE supertokens_totp_used_codes
  DROP CONSTRAINT IF EXISTS supertokens_totp_used_codes_user_id_fkey;

ALTER TABLE supertokens_totp_used_codes
  ADD CONSTRAINT supertokens_totp_used_codes_user_id_fkey 
    FOREIGN KEY (app_id, user_id)
    REFERENCES supertokens_totp_users (app_id, user_id) ON DELETE CASCADE;

ALTER TABLE supertokens_totp_used_codes
  DROP CONSTRAINT IF EXISTS supertokens_totp_used_codes_tenant_id_fkey;

ALTER TABLE supertokens_totp_used_codes
  ADD CONSTRAINT supertokens_totp_used_codes_tenant_id_fkey 
    FOREIGN KEY (app_id, tenant_id)
    REFERENCES supertokens_tenants (app_id, tenant_id) ON DELETE CASCADE;

DROP INDEX IF EXISTS supertokens_totp_used_codes_expiry_time_ms_index;

CREATE INDEX IF NOT EXISTS supertokens_totp_used_codes_expiry_time_ms_index ON supertokens_totp_used_codes (app_id, tenant_id, expiry_time_ms);

CREATE INDEX IF NOT EXISTS supertokens_totp_used_codes_user_id_index ON supertokens_totp_used_codes (app_id, user_id);

CREATE INDEX IF NOT EXISTS supertokens_totp_used_codes_tenant_id_index ON supertokens_totp_used_codes (app_id, tenant_id);

-- ActiveUsers

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

ALTER TABLE supertokens_user_last_active
  DROP CONSTRAINT supertokens_user_last_active_pkey CASCADE;

ALTER TABLE supertokens_user_last_active
  ADD CONSTRAINT supertokens_user_last_active_pkey 
    PRIMARY KEY (app_id, user_id);

ALTER TABLE supertokens_user_last_active
  DROP CONSTRAINT IF EXISTS supertokens_user_last_active_app_id_fkey;

ALTER TABLE supertokens_user_last_active
  ADD CONSTRAINT supertokens_user_last_active_app_id_fkey 
    FOREIGN KEY (app_id)
    REFERENCES supertokens_apps (app_id) ON DELETE CASCADE;

CREATE INDEX IF NOT EXISTS supertokens_user_last_active_app_id_index ON supertokens_user_last_active (app_id);

4. Start supertokens v6 container

Implementation PR:
#2627
Docker Hash to Deploy to Production:
ff4d26aada7d1138ecf9fcb589cd2a74079384a0

5. Upgrade to the latest supertokens CDI version

Implementation PR:
#2631
Docker Hash to Deploy to Production:
a90f25b953487c3b13bca8eaff1722ae7038f52f