psql.txt

# select * from users;
 id |  name   | personality
----+---------+-------------
  1 | adam    | f
  2 | bob     | f
  3 | charles | t
(3 rows)

# select * from things;
 id |  name  | owner_id
----+--------+----------
  1 | apple  |        1
  2 | banana |        1
  3 | pear   |        2
  4 | orange |        2
  5 | cherry |        3
  6 | mango  |        3
(6 rows)

# select * from delete_events;
 id | thing_id | sync_counter
----+----------+--------------
(0 rows)

# delete from things where id=1;
DELETE 1

# select * from delete_events;
 id | thing_id | sync_counter
----+----------+--------------
(0 rows)

# delete from things where id=5;
DELETE 1

# delete from things where id=6;
DELETE 1

# select * from delete_events;
 id | thing_id | sync_counter
----+----------+--------------
  1 |        5 |            1
  2 |        6 |            2
(1 row)

schema.sql

CREATE SEQUENCE sync_counter_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

CREATE FUNCTION bump_sync_counter() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ BEGIN NEW.sync_counter := nextval('sync_counter_seq'); RETURN NEW; END; $$;

# ----

CREATE TABLE users (
    id integer NOT NULL,
    name character varying(255) DEFAULT ''::character varying NOT NULL,
    personality boolean DEFAULT false NOT NULL
);

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE ONLY users
    ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);

insert into users (name, personality) values ('adam',    false);
insert into users (name, personality) values ('bob',     false);
insert into users (name, personality) values ('charles', true);

# ----

CREATE TABLE things (
    id integer NOT NULL,
    name character varying(255) DEFAULT ''::character varying NOT NULL,
    owner_id integer NOT NULL
);

ALTER TABLE ONLY things
    ADD CONSTRAINT things_pkey PRIMARY KEY (id);

CREATE SEQUENCE things_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE things_id_seq OWNED BY things.id;

ALTER TABLE ONLY things
    ALTER COLUMN id SET DEFAULT nextval('things_id_seq'::regclass);

ALTER TABLE ONLY things
    ADD CONSTRAINT things_owner_id_fk FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE;

insert into things (name, owner_id) values ('apple',  1);
insert into things (name, owner_id) values ('banana', 1);
insert into things (name, owner_id) values ('pear',   2);
insert into things (name, owner_id) values ('orange', 2);
insert into things (name, owner_id) values ('cherry', 3);
insert into things (name, owner_id) values ('mango',  3);

# ----

CREATE TABLE delete_events (
    id integer NOT NULL,
    thing_id integer,
    sync_counter integer
);

ALTER TABLE ONLY delete_events
    ADD CONSTRAINT delete_events_pkey PRIMARY KEY (id);

CREATE SEQUENCE delete_events_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE delete_events_id_seq OWNED BY delete_events.id;

ALTER TABLE ONLY delete_events
    ALTER COLUMN id SET DEFAULT nextval('delete_events_id_seq'::regclass);

CREATE TRIGGER bump_sync_counter BEFORE INSERT ON delete_events FOR EACH ROW EXECUTE PROCEDURE bump_sync_counter();

CREATE FUNCTION thing_deletes() RETURNS TRIGGER AS $thing_deletes$
    BEGIN
        IF(SELECT personality FROM users WHERE users.id = OLD.owner_id) THEN
            INSERT INTO delete_events (thing_id) VALUES (OLD.id);
        END IF;
        RETURN OLD;
    END;
$thing_deletes$ LANGUAGE PLPGSQL;

CREATE TRIGGER thing_deletes
    BEFORE DELETE ON things
    FOR EACH ROW EXECUTE PROCEDURE thing_deletes();