Using triggers to keep a revision history for rows in table in PostgreSQL

revisions.sql

CREATE TABLE projects (
  id BIGINT DEFAULT pseudo_encrypt(nextval('projects_id_seq')),
  created_by BIGINT REFERENCES profiles(user_id) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  deleted BOOLEAN NOT NULL DEFAULT FALSE,

  slug VARCHAR(100) DEFAULT NULL,

  featured_image_id BIGINT REFERENCES images(id),
  name VARCHAR NOT NULL,
  notes VARCHAR NOT NULL,
);

CREATE TABLE project_revisions (
    revision_created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    like projects including all,

    PRIMARY KEY (id, revision_created_at)
);

ALTER TABLE projects ADD CONSTRAINT projects_primary_key PRIMARY KEY(id);
ALTER TABLE projects ADD CONSTRAINT projects_unique_slug UNIQUE (slug);

create or replace function trigger_on_project_revision()
    returns trigger
    language plpgsql as $body$
begin
    if old is distinct from new then
        insert into project_revisions SELECT NOW(), old.*;
        new.updated_at = NOW();
    else 
        -- if the record is unchanged, we won't save it
        new = NULL;
    end if;
    -- Return the `NEW` record so that update can carry on as usual
    return new;
end; $body$;

create trigger trigger_project_revision
  before update
  on projects
  for each row
execute procedure trigger_on_project_revision();