README

Actions {
  :branch
  :create_node
  :update_node
  :remove_node
  :create_edge
  :remove_edge
}

Action.create(type, actor, at, payload)

-- READ

nodes.find(key, where: (expired: 0))

-- CREATE

Action.create(:create, { label: 'something' }, )

nodes.create(_id, key, created: NOW, expired: 0)

TRIGGER {
  proxies.create(key__IN, created: NOW, expired: 0)
  proxies.create(key__OUT, created: NOW, expired: 0)
  edges.create(src: key__IN, tgt: _id, created: NOW, expired: 0)
  edges.create(src: _id, tgt: key__OUT, created: NOW, expired: 0)
}

-- UPDATE
// copy on write

nodes.create(key, data: new_data, created: NOW, expired: 0)

TRIGGER {
  nodes.find(key).update(expired: NOW)
  edges.find(key__IN).update(expired: NOW)
  edges.find(key__OUT).update(expired: NOW)
  edges.create(src: key__IN, tgt: _id, created: NOW, expired: 0)
  edges.create(src: _id, tgt: created: NOW, expired: 0)
}

-- DELETE

nodes.find(id).update(expired: NOW)

TRIGGER {
  edges.find(key__IN).update(expired: NOW)
  edges.find(key__OUT).update(expired: NOW)
}

-- CONNECT

edges.create(src: src_key, tgt: tgt_key, created: NOW, expired: 0)

-- DISCONNECT

edges.find(src: src_key, tgt: tgt_key, expired: 0).update(expired: NOW)

triggers.pgsql

create extension if not exists citext;
create extension if not exists pgcrypto;

drop schema if exists app_public cascade;
create schema app_public;

create type action_type as enum ('create_node', 'update_node', 'remove_node', 'create_edge', 'remove_edge');

create table app_public.users (
  id uuid primary key default gen_random_uuid(),
  username citext unique not null,
  password text not null
);

create table app_public.teams (
  id uuid primary key default gen_random_uuid(),
  name text
);

create table app_public.team_members (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references app_public.users,
  team_id uuid not null references app_public.teams
);

create table app_public.flows (
  id uuid primary key default gen_random_uuid(),
  team_id uuid not null references app_public.teams,
  name text
);

create table app_public.actions (
  id uuid primary key default gen_random_uuid(),
  type action_type not null,
  -- payload jsonb not null default '{}',
  flow_id uuid not null references app_public.flows,
  actor_id uuid not null references app_public.users,
  created_at timestamp without time zone not null
);

create table app_public.nodes (
  -- id uuid primary key default gen_random_uuid(),
  id uuid default primary key gen_random_uuid(),
  _id uuid,
  flow_id uuid not null references app_public.flows,
  -- parent_id uuid references app_public.nodes,
  action_id uuid references app_public.actions,
  data jsonb not null default '{}',
  -- children uuid[] not null default array[]::uuid[],
  created_at timestamp without time zone not null default current_timestamp,
  expired_at timestamp without time zone
);

create table app_public.node_proxies (
  id uuid primary key not null,
  node_id uuid not null references app_public.nodes,
  is_outwards boolean not null
);

create table app_public.edges (
  src_node_id uuid not null references app_public.nodes,
  tgt_node_id uuid not null references app_public.nodes,
  created_at timestamp without time zone not null default current_timestamp,
  expired_at timestamp without time zone,
  PRIMARY KEY(src_node_id, tgt_node_id)
);

drop function if exists create_node();
create or replace function create_node()
  returns trigger as
$$
  DECLARE action_id uuid;
begin
  INSERT INTO app_public.actions("type", "flow_id", "actor_id", "created_at") VALUES (
    'create_node',
    new.flow_id,
    (SELECT id from app_public.users LIMIT 1),
    new.created_at
  ) RETURNING "id" INTO action_id;

  PERFORM pg_notify('my-channel', '{"id": "' || new.id || '", "type": "create_node", "at": "'|| new.created_at ||'" }');

  IF new._id IS NULL THEN
    new._id = new.id;
  END IF;
  new.action_id = action_id;
  return new;
end
$$ language plpgsql;

drop trigger if exists create_node on app_public.nodes;
create trigger create_node_trigger
  before insert on app_public.nodes
  for each row
  execute procedure create_node();


-- drop function if exists delete_node();
-- create or replace function delete_node()
--   returns trigger as
-- $$
--   DECLARE action_id uuid;
-- begin
--   -- INSERT INTO app_public.actions("type", "flow_id", "actor_id", "created_at") VALUES (
--   --   'create_node',
--   --   new.flow_id,
--   --   (SELECT id from app_public.users LIMIT 1),
--   --   new.created_at
--   -- ) RETURNING "id" INTO action_id;

--   -- PERFORM pg_notify('my-channel', '{"id": "' || new.id || '", "type": "create_node", "at": "'|| new.created_at ||'" }');

--   UPDATE app_public.nodes SET expired_at = NOW() WHERE app_public.nodes.id = old.id;
--   return NULL;
-- end
-- $$ language plpgsql;

-- drop trigger if exists delete_node on app_public.nodes;
-- create trigger delete_node_trigger
--   before delete on app_public.nodes
--   for each row
--   execute procedure delete_node();

--------------------

-- CREATE OR REPLACE RULE delete_node AS
--   ON DELETE TO app_public.nodes
--   DO INSTEAD
--     UPDATE app_public.nodes SET expired_at = NOW()
--     WHERE app_public.nodes.id = old.id;

-- CREATE OR REPLACE RULE delete_categories_x AS
--   ON DELETE TO app_public.nodes
--   WHERE old.expired_at IS NOT NULL
--   DO ALSO
--     NOTHING;

-- CREATE OR REPLACE RULE delete_categories AS
--   ON DELETE TO app_public.nodes
--   WHERE old.expired_at IS NULL
--   DO INSTEAD
--     UPDATE app_public.nodes SET expired_at = NOW()
--     WHERE app_public.nodes.id = old.id;

-- CREATE OR REPLACE RULE delete_categories_x AS
--   ON DELETE TO app_public.nodes
--   DO INSTEAD
--     NOTHING;

-------------------

drop trigger if exists soft_delete_user on app_public.nodes;
CREATE TRIGGER soft_delete_user
  BEFORE DELETE ON app_public.nodes
  FOR EACH ROW EXECUTE PROCEDURE soft_delete();

drop function if exists soft_delete();
CREATE OR REPLACE FUNCTION soft_delete()
  RETURNS trigger AS $$
    DECLARE
      command text := ' SET expired_at = current_timestamp WHERE id = $1';
    BEGIN
      EXECUTE 'UPDATE app_public.' || TG_TABLE_NAME || command USING OLD.id;
      RETURN NULL;
    END;
  $$ LANGUAGE plpgsql;


drop function if exists update_node();
create or replace function update_node()
  returns trigger as
$$
  DECLARE action_id uuid;
begin
IF new.expired_at IS NULL THEN
  INSERT INTO app_public.actions("type", "flow_id", "actor_id", "created_at") VALUES (
    'update_node',
    old.flow_id,
    (SELECT id from app_public.users LIMIT 1),
    NOW()
  ) RETURNING "id" INTO action_id;

  INSERT INTO "app_public"."nodes"(
    "_id",
    "flow_id",
    "action_id",
    "data",
    "created_at"
  ) VALUES(
    old._id,
    old.flow_id,
    action_id,
    new.data,
    NOW()
  );

  new.data = old.data;
  new.expired_at = NOW();

  PERFORM pg_notify('my-channel', '{"id": "' || old.id || '", "type": "expire_node", "at": "'|| new.expired_at ||'" }');
END IF;
  return new;

end
$$ language plpgsql;

drop trigger if exists update_node_trigger on app_public.nodes;
create trigger update_node_trigger
  before update on app_public.nodes
  for each row
  execute procedure update_node();



INSERT INTO app_public.users("username", "password") VALUES('john', 'john') RETURNING "id";
INSERT INTO app_public.teams("name") VALUES('osl') RETURNING "id";

INSERT INTO app_public.team_members("team_id", "user_id") VALUES(
  (SELECT id from app_public.teams LIMIT 1),
  (SELECT id from app_public.users LIMIT 1)
) RETURNING "id";

INSERT INTO app_public.flows("name", "team_id") VALUES('gdpo',
  (SELECT id from app_public.teams LIMIT 1)
) RETURNING "id";

--------------------

INSERT INTO "app_public"."nodes"(
  "flow_id", "data"
) VALUES((SELECT id from app_public.flows LIMIT 1),'{"a":1}') RETURNING "id";

INSERT INTO "app_public"."nodes"(
  "flow_id", "data"
) VALUES((SELECT id from app_public.flows LIMIT 1),'{"a":2}') RETURNING "id";