UmbraTrans/umbratrans_web/migrations/0002_password_reset_purpose.sql

-- 0002: give single-use tokens a purpose.
--
-- 0001 reused the control plane's existing, unused `bootstrap_tokens` table for
-- email verification. That table has no notion of what a token is for, so with
-- only one kind of token in play the omission was invisible.
--
-- It stops being invisible the moment a second kind exists. A password-reset
-- token and an email-verification token would be interchangeable: a reset link
-- clicked by anyone holding it would confirm an address, and a verification link
-- would set a password. Neither is acceptable, and the table as it stands cannot
-- tell them apart.
--
-- The column defaults to the existing purpose, so every row already written stays
-- valid and the verification flow is unchanged. No existing row is rewritten, and
-- the verification code continues to work without modification.
--
-- `0002_password_reset_purpose.sql`, additive, and safe to apply more than once.

BEGIN;

-- ---------------------------------------------------------------------
-- 1. Purpose
-- ---------------------------------------------------------------------

-- A default, not a null, so the column can be NOT NULL and an insert that
-- forgets to name a purpose is still valid and still safe: it lands as
-- email_verification, which is the only purpose that existed before this
-- migration and therefore the conservative choice.
ALTER TABLE public.bootstrap_tokens
    ADD COLUMN IF NOT EXISTS purpose text NOT NULL DEFAULT 'email_verification';

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint c
        JOIN pg_class t ON t.oid = c.conrelid
        JOIN pg_namespace n ON n.oid = t.relnamespace
        WHERE n.nspname = 'public'
          AND t.relname = 'bootstrap_tokens'
          AND c.conname = 'bootstrap_tokens_purpose_check'
    ) THEN
        ALTER TABLE public.bootstrap_tokens
            ADD CONSTRAINT bootstrap_tokens_purpose_check
            CHECK (purpose IN ('email_verification', 'password_reset'));
        RAISE NOTICE 'attached bootstrap_tokens_purpose_check';
    ELSE
        RAISE NOTICE 'bootstrap_tokens_purpose_check already present; leaving it alone';
    END IF;
END
$$;

COMMENT ON COLUMN public.bootstrap_tokens.purpose IS
    'What this token authorises: email_verification confirms an address, password_reset sets a new password. They are deliberately not interchangeable, because each would otherwise grant the other''s capability.';

-- ---------------------------------------------------------------------
-- 2. Lookup
-- ---------------------------------------------------------------------

-- Every token read filters on user and purpose together, and an unconsumed token
-- for one purpose is looked up on every reset and every verification. The index
-- covers that; without it each of those is a sequential scan of the table, which
-- on a busy deployment is also a cheap way for an unauthenticated endpoint to
-- generate load.
CREATE INDEX IF NOT EXISTS bootstrap_tokens_user_purpose_idx
    ON public.bootstrap_tokens (user_id, purpose, expires_at)
    WHERE consumed_at IS NULL;

-- ---------------------------------------------------------------------
-- 3. Rollback
-- ---------------------------------------------------------------------
-- Additive, so a rollback is dropping what was added. The column is dropped
-- before the index, since the index depends on it. Any token of a purpose other
-- than email_verification must be deleted first, or the drop will leave rows the
-- column no longer describes; the guarded delete below handles that, and the
-- NOTICE says how many rows it removed rather than passing over them.
--
-- DROP INDEX IF EXISTS public.bootstrap_tokens_user_purpose_idx;
--
-- DO $$
-- BEGIN
--     IF EXISTS (
--         SELECT 1 FROM information_schema.columns
--          WHERE table_schema = 'public'
--            AND table_name = 'bootstrap_tokens'
--            AND column_name = 'purpose'
--     ) THEN
--         DELETE FROM public.bootstrap_tokens WHERE purpose <> 'email_verification';
--         ALTER TABLE public.bootstrap_tokens DROP COLUMN purpose;
--     END IF;
-- END
-- $$;

COMMIT;