resource.sql

-- Example of a postgre-driven immutable versioned and validated models

DROP TABLE orders CASCADE;
CREATE TABLE orders(
  id serial,                            -- Serial ID
  origin_id integer,                    -- ID of a first version
  version integer,                      -- Version number
  errors jsonb,                         -- Results of validation

  created_at TIMESTAMP WITH TIME ZONE,  -- Initial creation time
  updated_at TIMESTAMP WITH TIME ZONE,  -- Last time of update
  deleted_at TIMESTAMP WITH TIME ZONE,  -- Datestamp of deletion (inherited)
  
  email varchar(255)                    -- GENERATED: column types
);

-- Scope: history of changes
CREATE OR REPLACE 
VIEW orders_versions AS 
SELECT * from orders WHERE errors is null ORDER BY origin_id, version DESC;

-- Scope: last non-deleted versions 
CREATE OR REPLACE 
VIEW orders_current AS 
SELECT DISTINCT ON (origin_id) * from orders_versions WHERE deleted_at is null;

-- find last version of an row
CREATE OR REPLACE FUNCTION order_head(integer, integer DEFAULT 2147483646) RETURNS integer
    AS 'SELECT version from orders  WHERE origin_id = $1 and version < $2 ORDER BY version DESC'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

-- Inserts new version of a row
CREATE OR REPLACE FUNCTION
create_order() returns trigger language plpgsql AS $$ begin
  new.errors = '{}';
  
  -- GENERATED: column validations
  IF NOT new.email  ~ '^[^@]@.+\..+$' THEN
		SELECT jsonb_set(new.errors, '{email}', '"Email is incorrect"') into new.errors;
	END IF;

  -- fill created_at, start with 0 version and return errors
  return (
    new.id,
    coalesce(new.origin_id, new.id),        -- inherit origin_id or set to self
    coalesce(new.version, 0),               -- start with 0 version unless given
    CASE WHEN new.errors::text != '{}' THEN -- pass validation errors if any
      new.errors
    END,
    coalesce(new.created_at, now()),        -- inherit or set creation timestamp
    new.updated_at,                         -- inherit modification timestamp
    new.deleted_at,                         -- inherit deletion timestamp
    new.email);                             -- GENERATED: column names
end $$;

CREATE TRIGGER create_order
    BEFORE INSERT ON orders
    FOR EACH ROW EXECUTE PROCEDURE create_order();

-- Turns update into insert and bumps version
CREATE OR REPLACE FUNCTION
update_order() returns trigger language plpgsql AS $$ begin
  INSERT INTO orders(
    origin_id, version, 
    created_at, updated_at, deleted_at,
    email)                                  -- GENERATED: column names
  VALUES (
    new.origin_id,                          -- inherit origin_id
    order_head(old.origin_id) + 1,          -- bump version to max + 1
    old.created_at,                         -- inherit creation timestamp 
    now(),                                  -- update modification timestamp
    new.deleted_at,                         -- inherit deletion timestamp
    new.email);                             -- GENERATED: column names

  return null;                              -- keep row immutable
end $$;

CREATE TRIGGER update_order
    BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE PROCEDURE update_order();


-- Turns DELETEs into INSERTS with deleted_at timestamp
CREATE OR REPLACE FUNCTION
delete_order() returns trigger language plpgsql AS $$
  begin
    -- if deleting actual version, just set deleted_at timestamp
    if order_head(old.origin_id) = old.version and old.deleted_at is null   THEN
      EXECUTE 'UPDATE ' || TG_TABLE_NAME || ' SET deleted_at = now() WHERE id = $1' USING OLD.id;
    ELSE
    -- otherwise clone preceeding version without deletion flag and make it current
      UPDATE orders SET deleted_at=null 
      WHERE origin_id = old.origin_id and version=order_head(old.origin_id, old.version);
    END if;
      
    return null; -- dont delete original row
  end;
$$;

CREATE TRIGGER delete_order
    BEFORE DELETE ON orders
    FOR EACH ROW EXECUTE PROCEDURE delete_order();

-- Mark HEAD of an order as deleted (dont need to know version number)
CREATE OR REPLACE function
delete_current_order() returns trigger
language plpgsql
AS $$
	begin
	  DELETE from orders WHERE id=old.id;
    return null;
  end;
$$;

CREATE TRIGGER delete_current_order
    INSTEAD OF DELETE ON orders_current
    FOR EACH ROW EXECUTE PROCEDURE delete_current_order();

-- version 0 invalid  -- insert
INSERT into orders(email)  VALUES('a.com');
-- version 1 valid    -- update invalid to valid
UPDATE orders SET email='a@b.com' WHERE email='a.com';
-- version 2 valid    -- update valid to valid
UPDATE orders SET email='a@c4.com' WHERE email='a@b.com';
-- version 3 deleted  -- soft delete head
DELETE from orders_current;
-- version 4 valid    -- undelete
DELETE from orders WHERE version=3;
-- version 5          -- roll back to 2nd version 
UPDATE orders SET updated_at=now() WHERE version=1;

-- 1 current order
SELECT * from orders_current;
-- 5 versions
SELECT * from orders ORDER BY id;