Supabase fails to start after updating to version v1.200.3. ERROR: type "one_time_token_type" already exists (SQLSTATE 42710). Crash ID:d2e88a6259ab4feeb5df888087f935d1

[nosizejosh]

Supabase fails to start after updating to version v1.200.3.

ERROR: type "one_time_token_type" already exists (SQLSTATE 42710)                                                                 

At statement 0: create type "auth"."one_time_token_type" as enum ('confirmation_token', 'reauthentication_token', 'recovery_token', 'email_change_token_new', 'email_change_token_current', 'phone_change_token')

System information

• Ticket ID: [d2e88a6259ab4feeb5df888087f935d1]
• Version of OS: [Windows 11]
• Version of CLI: [v1.200.3]
• Version of Docker: [v4.34.2]
• Versions of services:

        SERVICE IMAGE      │      LOCAL       │   LINKED
  ─────────────────────────┼──────────────────┼─────────────
    supabase/postgres      │ 15.6.1.121       │ 15.6.1.121
    supabase/gotrue        │ v2.160.0         │ v2.160.0
    postgrest/postgrest    │ v12.2.3          │ v12.2.3
    supabase/realtime      │ v2.30.34         │ -
    supabase/storage-api   │ v1.11.7          │ v1.11.7
    supabase/edge-runtime  │ v1.58.3          │ -
    supabase/studio        │ 20240729-ce42139 │ -
    supabase/postgres-meta │ v0.83.2          │ -
    supabase/logflare      │ 1.4.0            │ -
    supabase/supavisor     │ 1.1.56           │ -

I have tried to re-link the project, delete containers from docker and re-install via cli with supabase start.
I have also tried to disable some of the newly added configs but Nothing has worked.

Please help!

[nosizejosh]

The error seems to be coming from the application of this system generated migration

create type "auth"."one_time_token_type" as enum ('confirmation_token', 'reauthentication_token', 'recovery_token', 'email_change_token_new', 'email_change_token_current', 'phone_change_token');

drop trigger if exists "on_auth_user_created" on "auth"."users";

create table "auth"."one_time_tokens" (
    "id" uuid not null,
    "user_id" uuid not null,
    "token_type" auth.one_time_token_type not null,
    "token_hash" text not null,
    "relates_to" text not null,
    "created_at" timestamp without time zone not null default now(),
    "updated_at" timestamp without time zone not null default now()
);


CREATE UNIQUE INDEX one_time_tokens_pkey ON auth.one_time_tokens USING btree (id);

CREATE INDEX one_time_tokens_relates_to_hash_idx ON auth.one_time_tokens USING hash (relates_to);

CREATE INDEX one_time_tokens_token_hash_hash_idx ON auth.one_time_tokens USING hash (token_hash);

CREATE UNIQUE INDEX one_time_tokens_user_id_token_type_key ON auth.one_time_tokens USING btree (user_id, token_type);

alter table "auth"."one_time_tokens" add constraint "one_time_tokens_pkey" PRIMARY KEY using index "one_time_tokens_pkey";

alter table "auth"."one_time_tokens" add constraint "one_time_tokens_token_hash_check" CHECK ((char_length(token_hash) > 0)) not valid;

alter table "auth"."one_time_tokens" validate constraint "one_time_tokens_token_hash_check";

alter table "auth"."one_time_tokens" add constraint "one_time_tokens_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE not valid;

alter table "auth"."one_time_tokens" validate constraint "one_time_tokens_user_id_fkey";

grant delete on table "auth"."one_time_tokens" to "dashboard_user";

grant insert on table "auth"."one_time_tokens" to "dashboard_user";

grant references on table "auth"."one_time_tokens" to "dashboard_user";

grant select on table "auth"."one_time_tokens" to "dashboard_user";

grant trigger on table "auth"."one_time_tokens" to "dashboard_user";

grant truncate on table "auth"."one_time_tokens" to "dashboard_user";

grant update on table "auth"."one_time_tokens" to "dashboard_user";

grant delete on table "auth"."one_time_tokens" to "postgres";

grant insert on table "auth"."one_time_tokens" to "postgres";

grant references on table "auth"."one_time_tokens" to "postgres";

grant select on table "auth"."one_time_tokens" to "postgres";

grant trigger on table "auth"."one_time_tokens" to "postgres";

grant truncate on table "auth"."one_time_tokens" to "postgres";

grant update on table "auth"."one_time_tokens" to "postgres";

so I replaced that migration with

-- Check if the type doesn't exist before creating it
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'one_time_token_type') THEN
        CREATE TYPE "auth"."one_time_token_type" AS ENUM (
            'confirmation_token',
            'reauthentication_token',
            'recovery_token',
            'email_change_token_new',
            'email_change_token_current',
            'phone_change_token'
        );
    END IF;
END$$;

-- Drop trigger if exists (keep this as is, it's already safe)
DROP TRIGGER IF EXISTS "on_auth_user_created" ON "auth"."users";

-- Check if the table doesn't exist before creating it
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'auth' AND table_name = 'one_time_tokens') THEN
        CREATE TABLE "auth"."one_time_tokens" (
            "id" uuid NOT NULL,
            "user_id" uuid NOT NULL,
            "token_type" auth.one_time_token_type NOT NULL,
            "token_hash" text NOT NULL,
            "relates_to" text NOT NULL,
            "created_at" timestamp without time zone NOT NULL DEFAULT now(),
            "updated_at" timestamp without time zone NOT NULL DEFAULT now()
        );
    END IF;
END$$;

-- Create indexes if they don't exist
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'one_time_tokens_pkey') THEN
        CREATE UNIQUE INDEX one_time_tokens_pkey ON auth.one_time_tokens USING btree (id);
    END IF;
END$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'one_time_tokens_relates_to_hash_idx') THEN
        CREATE INDEX one_time_tokens_relates_to_hash_idx ON auth.one_time_tokens USING hash (relates_to);
    END IF;
END$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'one_time_tokens_token_hash_hash_idx') THEN
        CREATE INDEX one_time_tokens_token_hash_hash_idx ON auth.one_time_tokens USING hash (token_hash);
    END IF;
END$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_indexes WHERE indexname = 'one_time_tokens_user_id_token_type_key') THEN
        CREATE UNIQUE INDEX one_time_tokens_user_id_token_type_key ON auth.one_time_tokens USING btree (user_id, token_type);
    END IF;
END$$;

-- Add constraints if they don't exist
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_name = 'one_time_tokens_pkey' AND table_name = 'one_time_tokens') THEN
        ALTER TABLE "auth"."one_time_tokens" ADD CONSTRAINT "one_time_tokens_pkey" PRIMARY KEY USING INDEX "one_time_tokens_pkey";
    END IF;
END$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.check_constraints WHERE constraint_name = 'one_time_tokens_token_hash_check') THEN
        ALTER TABLE "auth"."one_time_tokens" ADD CONSTRAINT "one_time_tokens_token_hash_check" CHECK ((char_length(token_hash) > 0)) NOT VALID;
    END IF;
END$$;

-- Validate the constraint (this is safe to run multiple times)
ALTER TABLE "auth"."one_time_tokens" VALIDATE CONSTRAINT "one_time_tokens_token_hash_check";

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.referential_constraints WHERE constraint_name = 'one_time_tokens_user_id_fkey') THEN
        ALTER TABLE "auth"."one_time_tokens" ADD CONSTRAINT "one_time_tokens_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE NOT VALID;
    END IF;
END$$;

-- Validate the foreign key constraint (this is safe to run multiple times)
ALTER TABLE "auth"."one_time_tokens" VALIDATE CONSTRAINT "one_time_tokens_user_id_fkey";

-- Grants (these are idempotent, so we can run them without checks)
GRANT DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE ON TABLE "auth"."one_time_tokens" TO "dashboard_user";
GRANT DELETE, INSERT, REFERENCES, SELECT, TRIGGER, TRUNCATE, UPDATE ON TABLE "auth"."one_time_tokens" TO "postgres"; 

.. And now I have a new error:

supabase_vector container is not ready: unhealthy

[nosizejosh]

Solved the supabase_vector container is not ready: unhealthy by setting analytics to false as explained here