Persistent "converting NULL to string" error on confirmation_token after applying migration patch (local dev)

[angelodias-appno]

FYI

I can re-create this bug issue on Auth or CLI repos if this is not the right place for it.

Edit:

This conversation has a workaround

Bug report

• [X] I confirm this is a bug with Supabase, not with my own application.
• [X] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When running supabase db reset in a local development environment, the Supabase Auth service fails to start correctly or query user data upon initial connection or a login attempt. The error message in the logs points to a converting NULL to string is unsupported issue related to the confirmation_token column in the auth.users table. This occurs even after a database migration has explicitly dropped the NOT NULL constraint on this and related columns.

To Reproduce

Steps to reproduce the behavior:

1. Set up a local Supabase project (supabase init, supabase start).
2. Create migrations that define some tables (like public.profiles).
3. Create a migration file (e.g., YYYYMMDDHHMMSS_fix_auth_nullable_columns.sql) that runs after remote_schema.sql and includes ALTER TABLE auth.users ALTER COLUMN column_name DROP NOT NULL; for columns like confirmation_token, email_change_token_new, recovery_token, email_change_token_current, and email_change_sent_at.
4. Have a seed script (seed.sql or files in seed/) that inserts users into auth.users (these users will naturally have NULLs in columns like confirmation_token).
5. Run supabase db reset.
6. After the process completes and containers restart, attempt to log in using one of the seeded users (e.g., via the local Auth API endpoint or Supabase UI).

Expected behavior

Login should succeed. The Supabase Auth service should be able to read user data from the auth.users table, correctly handling NULL values in columns that are nullable according to the database schema.

System information

• OS: macOS 15.4.1
• Version of supabase-js: 2.48.1
• Version of Node.js: v22.14.0
• Supabase CLI Version: 2.20.12

Additional context

This issue persists despite applying a migration intended to fix known nullable column issues in auth.users. I have verified directly via psql that the patch migration successfully drops the NOT NULL constraint on the affected columns (specifically confirmation_token, email_change_token_new, recovery_token, email_change_token_current, and email_change_sent_at).

The error message continues to appear upon attempted login, pointing to confirmation_token and the "converting NULL to string is unsupported" error, suggesting the Auth service's reading process is not correctly reflecting the nullable schema definition from the database.

Content of the patch migration (20250421140933_fix_auth_nullable_columns):

ALTER TABLE auth.users
ALTER COLUMN confirmation_token DROP NOT NULL;

ALTER TABLE auth.users
ALTER COLUMN email_change_token_new DROP NOT NULL;

ALTER TABLE auth.users
ALTER COLUMN recovery_token DROP NOT NULL;

ALTER TABLE auth.users
ALTER COLUMN email_change_token_current DROP NOT NULL;

ALTER TABLE auth.users
ALTER COLUMN email_change_sent_at DROP NOT NULL;

seeding file

INSERT INTO
    auth.users (
        instance_id,
        id,
        aud,
        role,
        email,
        encrypted_password,
        email_confirmed_at,
        last_sign_in_at,
        raw_app_meta_data,
        raw_user_meta_data,
        created_at,
        updated_at
    )
SELECT
    '00000000-0000-0000-0000-000000000000',
    uuid_generate_v4(),
    'authenticated',
    'authenticated',
    users_data.email,
    crypt(users_data.raw_password, gen_salt('bf')),
    current_timestamp,
    current_timestamp,
    '{"provider":"email","providers":["email"]}'::jsonb,
    '{}'::jsonb,
    current_timestamp,
    current_timestamp
FROM (
    VALUES
        ('[email protected]', 'AdminExample'),
        ('[email protected]', 'User1Example'),
        ('[email protected]', 'User2Example'),
        ('[email protected]', 'User3Example'),
        ('[email protected]', 'User4Example'),
        ('[email protected]', 'User5Example'),
        ('[email protected]', 'User6Example'),
        ('[email protected]', 'User7Example'),
        ('[email protected]', 'User8Example'),
        ('[email protected]', 'User9Example')
) AS users_data (email, raw_password);


INSERT INTO
    auth.identities (
        id,
        user_id,
        provider_id,
        identity_data,
        provider,
        last_sign_in_at,
        created_at,
        updated_at
    ) (
        SELECT
            uuid_generate_v4(),
            users.id,
            users.id,
            format('{"sub":"%s","email":"%s"}', users.id::text, users.email)::jsonb,
            'email',
            current_timestamp,
            current_timestamp,
            current_timestamp
        FROM
            auth.users
        WHERE
            users.email IN (
                '[email protected]',
                '[email protected]',
                '[email protected]',
                '[email protected]',
                '[email protected]',
                '[email protected]',
                '[email protected]',
                '[email protected]',
                '[email protected]',
                '[email protected]'
            )
    )
ON CONFLICT (provider_id, provider) DO NOTHING;

[Hallidayo]

Hi, I've moved this issue over from the supabase repo.

[sweatybridge]

At step 3, you are dropping the not-null constraint on auth.users table which isn't supported. You should always add those non-nullable columns in your seed.sql file instead of modifying the auth schema.