synctv/migrations/20260426041_create_auth_email_registration_tokens.sql

CREATE TABLE IF NOT EXISTS auth_email_registration_tokens (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    token_hash VARCHAR(64) UNIQUE NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    used_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT auth_email_registration_tokens_token_hash_sha256_hex
        CHECK (token_hash ~ '^[0-9a-f]{64}$'),
    CONSTRAINT auth_email_registration_tokens_username_not_empty
        CHECK (length(trim(username)) > 0),
    CONSTRAINT auth_email_registration_tokens_email_not_empty
        CHECK (length(trim(email)) > 0)
);

CREATE INDEX IF NOT EXISTS idx_auth_email_registration_tokens_expires_at
    ON auth_email_registration_tokens(expires_at);

CREATE UNIQUE INDEX IF NOT EXISTS idx_auth_email_registration_tokens_pending_username
    ON auth_email_registration_tokens(LOWER(username))
    WHERE used_at IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_auth_email_registration_tokens_pending_email
    ON auth_email_registration_tokens(LOWER(email))
    WHERE used_at IS NULL;