db/migrations/V202502251530__puzzle_id_column.sql

-- This migration adds a puzzle_id column to the user table.
-- This column was previously part of the legacy identity DB, but was not initially migrated due to its lack of use at the time.

ALTER TABLE users ADD COLUMN puzzle_id VARCHAR UNIQUE;
COMMENT ON COLUMN users.puzzle_id IS 'generated by the gu_before_insert_user_tr "before insert" trigger, do not provide manually';

CREATE FUNCTION gu_puzzle_id(private_uuid UUID) RETURNS VARCHAR
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    -- the salts are not secrets, they are used to avoid sharing the private id with third parties
    RETURN gu_generate_identifier_with_salt(private_uuid, '8e833eab546c44a8a441ab052604ff2a');

CREATE OR REPLACE FUNCTION gu_before_insert_user_fn()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
AS
$$
    BEGIN
        NEW.google_tag_id = gu_google_tag_id(NEW.private_uuid);
        NEW.puzzle_id = gu_puzzle_id(NEW.private_uuid);
        RETURN NEW;
    END;
$$;

UPDATE users SET puzzle_id = gu_puzzle_id(private_uuid);
ALTER TABLE users ALTER COLUMN puzzle_id SET NOT NULL;