db/migrations/V202502031121__db_users_schema.sql

-- hash combination of private id with salt to generate unique external id
CREATE FUNCTION gu_generate_identifier_with_salt(private_uuid UUID, salt VARCHAR) RETURNS VARCHAR
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN encode(sha256((private_uuid || salt)::bytea), 'hex');

CREATE FUNCTION gu_google_tag_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, 'c16a3672d5404771baa2e10668cc1285');

CREATE TABLE users (
    id VARCHAR PRIMARY KEY,
    okta_id VARCHAR UNIQUE NOT NULL,
    username VARCHAR UNIQUE,
    braze_uuid UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
    private_uuid UUID UNIQUE NOT NULL DEFAULT gen_random_uuid(),
    google_tag_id VARCHAR UNIQUE NOT NULL
);

COMMENT ON COLUMN users.private_uuid IS 'private id used to generate external ids';
COMMENT ON COLUMN users.google_tag_id IS 'generated by the gu_before_insert_user_tr "before insert" trigger, do not provide manually';

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);
            RETURN NEW;
        END;
        $$;

CREATE TRIGGER gu_before_insert_user_tr
BEFORE INSERT
ON users
FOR EACH ROW
EXECUTE FUNCTION gu_before_insert_user_fn();