Upgrade Supertokens to v7

[n1ru4l]

We need to upgrade to SuperTokens core 7.0 in order to upgrade SDKs #3328

This will require running SQL on the production database and implies that we need to stop the supertokens service for a small period of time while the database migration is running.

• Test running database migration on production database dump

• Migrations SQL on Supertokens Changelog

Rollout Plan

1. Verify the current stack version

Before upgrading, ensure your stack is already running with SuperTokens core 6.X. For self-hosters you can check the docker-compose.community.yml file that you are running or the docker tag of the registry.supertokens.io/supertokens/supertokens-postgresql image.
If you are on another version, please first follow the upgrade guide of previous versions here: #2656

2. Stop the SuperTokens container

Upgrading SuperTokens implies a downtime. For self-hosters, we recommend stopping the whole Hive stack, except the postgres database.

3. Execute SQL on database

Execute the following SQL on the database.

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

ALTER TABLE "supertokens_all_auth_recipe_users"
  ADD COLUMN is_linked_or_is_a_primary_user BOOLEAN NOT NULL DEFAULT FALSE
;

ALTER TABLE "supertokens_all_auth_recipe_users"
  ADD COLUMN primary_or_recipe_user_time_joined BIGINT NOT NULL DEFAULT 0
;

UPDATE "supertokens_all_auth_recipe_users"
SET
  "primary_or_recipe_user_id" = "user_id"
WHERE
  "primary_or_recipe_user_id" = '0'
;

UPDATE "supertokens_all_auth_recipe_users"
SET
  "primary_or_recipe_user_time_joined" = "time_joined"
WHERE
  "primary_or_recipe_user_time_joined" = 0
;

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

ALTER TABLE "supertokens_all_auth_recipe_users"
  ALTER "primary_or_recipe_user_id" DROP DEFAULT
;

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

ALTER TABLE "supertokens_app_id_to_user_id"
  ADD COLUMN "is_linked_or_is_a_primary_user" BOOLEAN NOT NULL DEFAULT FALSE
;

UPDATE "supertokens_app_id_to_user_id"
SET
  "primary_or_recipe_user_id" = "user_id"
WHERE
  "primary_or_recipe_user_id" = '0'
;

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

ALTER TABLE "supertokens_app_id_to_user_id"
  ALTER COLUMN "primary_or_recipe_user_id" DROP DEFAULT
;

DROP INDEX "supertokens_all_auth_recipe_users_pagination_index";

CREATE INDEX "supertokens_all_auth_recipe_users_pagination_index1" ON "supertokens_all_auth_recipe_users" (
  "app_id",
  "tenant_id",
  "primary_or_recipe_user_time_joined" DESC,
  "primary_or_recipe_user_id" DESC
);

CREATE INDEX "supertokens_all_auth_recipe_users_pagination_index2" ON "supertokens_all_auth_recipe_users" (
  "app_id",
  "tenant_id",
  "primary_or_recipe_user_time_joined" ASC,
  "primary_or_recipe_user_id" DESC
);

 CREATE INDEX "supertokens_all_auth_recipe_users_pagination_index3" ON "supertokens_all_auth_recipe_users" (
   "recipe_id",
   "app_id", "tenant_id",
   "primary_or_recipe_user_time_joined" DESC,
   "primary_or_recipe_user_id" DESC
 );

CREATE INDEX "supertokens_all_auth_recipe_users_pagination_index4" ON "supertokens_all_auth_recipe_users" (
  "recipe_id",
  "app_id",
  "tenant_id",
  "primary_or_recipe_user_time_joined" ASC,
  "primary_or_recipe_user_id" DESC
);

CREATE INDEX "supertokens_all_auth_recipe_users_primary_user_id_index" ON "supertokens_all_auth_recipe_users" (
  "primary_or_recipe_user_id",
  "app_id"
);

CREATE INDEX "supertokens_all_auth_recipe_users_recipe_id_index" ON "supertokens_all_auth_recipe_users" (
  "app_id",
  "recipe_id",
  "tenant_id"
);

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_app_id_to_user_id" ("app_id", "user_id") ON DELETE CASCADE
;

ALTER TABLE "supertokens_emailpassword_pswd_reset_tokens"
  ADD COLUMN email VARCHAR(256)
;

4. Deploy new stack

Now that your database schema has been upgraded, you can deploy the following stack version, which includes SuperTokens v7.

Docker Hash to Deploy to Production:
778f97f44d8ffea17c8b0aa8577ad65bfda4745b