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

[sweatybridge]

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

What's the command you ran to generate this migration?

Schema changes to auth generally should not be included. The only exception is RLS policies.

[markcarroll]

I just hit this same issue. thank you @nosizejosh for posting a solution that worked for me. No idea why this is suddenly a problem. @sweatybridge I did manually do anything to create this migration, I think it came about from the initial pull.

[sweatybridge]

The supabase_vector issue is a separate one that's similar to #2737

I will close the original auth schema issue.