Postgres Temporal model (a simplest way)

model.sql

drop schema finance cascade;
CREATE SCHEMA if not exists finance;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";


create domain finance.money as integer not null default 0;
create domain finance.cnpj as varchar check (value ~ '^\d{2}\.\d{3}\.\d{3}\/\d{4}\-\d{2}$');
create domain finance.id as uuid not null;
create domain finance.ts as timestamp not null default now();
create domain finance.deleted as bool default false not null; 
create domain finance.email as varchar check (value ~ '^[^@]+@\w+\.\w+$');
create domain finance.phone as varchar check (value ~ '^(\d{2} )?\d?\d{4}-\d{4}$');


create table finance.users (
    dt finance.ts,
    id finance.id,
    deleted finance.deleted,
    name varchar not null,
    email finance.email,
    phone finance.phone,
    primary key(dt, id)
);

create index users_dt_idx on finance.users(dt);

create function finance.users_id() returns trigger as $$
BEGIN
	IF NEW.name IS NOT NULL AND NEW.ID IS NULL THEN
		NEW.ID := uuid_generate_v5(uuid_ns_oid(), NEW.name);
	END IF;
	RETURN NEW;
END
$$ language 'plpgsql';


create trigger users_trigger
	BEFORE INSERT
	on finance.users
	for each row
	execute procedure finance.users_id()
;


create function finance.users_Upsert(p_id uuid, p_dt timestamp, p_name varchar, p_email finance.email, p_phone finance.phone) 
returns finance.users as $$
DECLARE
    res finance.users;
	ts timestamp;
	id uuid;
begin
	ts := coalesce(p_dt, now());
	id := coalesce(p_id, uuid_generate_v5(uuid_ns_oid(), p_name));
    insert into finance.users(dt, id, name, phone, email) 
	values(ts, id, p_name, p_phone, p_email) 
	returning * into res;
    return res;
exception when unique_violation then
	if p_dt is null then
			insert into finance.users(dt, id, name, phone, email) 
			values(ts + interval '1 us', id, p_name, p_phone, p_email) 
			returning * into res;
			return res;
	else
		raise exception 'duplicate key value violates unique constraint "users_pkey": %', p_dt;
	end if;
end
$$ language 'plpgsql';


create function finance.users_GetById(p_id uuid, p_dt timestamp default now()) 
returns finance.users as $$
DECLARE
    result finance.users;
begin
    select * from finance.users 
    where 
            dt = (select dt from finance.users where id = p_id and dt <= p_dt order by dt desc limit 1) 
            and id = p_id 
            and deleted is false
    into result;
    return result;
end
$$ language 'plpgsql';


create function finance.users_GetAll(p_dt timestamp default now()) 
returns setof finance.users as $$
begin
    return query select * from finance.users where concat(dt, id) in (
        select max(concat(dt, id)) from finance.users where dt <= now() and deleted is false group by id
    );
end
$$ language 'plpgsql';


create function finance.users_Delete(p_id uuid) 
returns finance.users as $$
DECLARE
    result finance.users;
begin
    insert into finance.users
    select now()::timestamp as "dt", id, true as "deleted", name
    from finance.users_GetById(p_id) 
    returning * into result;
    return result;
end
$$ language 'plpgsql';


create function finance.users_History(p_id uuid) 
returns setof finance.users as $$
begin
    return query select * from finance.users where id = p_id order by dt desc;
end
$$ language 'plpgsql';


select * from finance.users_Upsert(null, null, 'blah1', 'blah@email.com', '11 91234-2345');
select * from finance.users_Upsert(null, now()::timestamp, 'blah2', 'blah@email.com', '11 91234-2345');
select * from finance.users_Upsert(null, now()::timestamp, 'blah2', 'blah@email.com', '11 91234-2345');
select * from finance.users_GetById('758b862b-5942-5c92-a535-2688a23fd5ef');
select * from finance.users_GetById('e484fd76-f249-5a3f-b558-8a2f6845099a');
select * from finance.users_Delete('758b862b-5942-5c92-a535-2688a23fd5ef');
select * from finance.users_History('e484fd76-f249-5a3f-b558-8a2f6845099a');
select * from finance.users_GetAll();