notifications: Consolidate and simplify creation of notifications

Preparing to extend the notification types, but with the old
individual-trigger-for-each-type that would easily turn into an
unmanagaeble mess. The new approach also correctly handles events that
may trigger multiple notification types.

Also removed the title and uid cache from the notifications table,
they don't simplify things and aren't necessary for performance. On the
other hand, doing a generic information lookup for vndbids is kind of
annoying. I now wrote an item_type() function to help with that, but
it's not sufficient to solve the broader problem.

One regression(?): Users will keep getting notifications for new posts
on threads even if they haven't read older notifications for the same
thread yet. I /think/ this behavior is more intuitive and expected, so
maybe I'll keep it.

(And possibly more regressions as well, who knows...)

6 files changed, 142 insertions, 177 deletions

diff --git a/lib/VNWeb/User/Notifications.pm b/lib/VNWeb/User/Notifications.pm
index 40417c63..aeaaa7f1 100644
--- a/lib/VNWeb/User/Notifications.pm
+++ b/lib/VNWeb/User/Notifications.pm
@@ -73,14 +73,14 @@ sub listing_ {
             my $lid = $l->{iid}.($l->{num}?'.'.$l->{num}:'');
             my $url = "/u$id/notify/$l->{id}/$lid";
             td_ class => 'tc1', sub { input_ type => 'checkbox', name => 'notifysel', value => $l->{id}; };
-            td_ class => 'tc2', $ntypes{$l->{ntype}};
+            td_ class => 'tc2', sub { join_ \&br_, sub { txt_ $ntypes{$_} }, $l->{ntype}->@* };
             td_ class => 'tc3', fmtage $l->{date};
             td_ class => 'tc4', sub { a_ href => $url, $lid };
             td_ class => 'tc5', sub {
                 a_ href => $url, sub {
                     txt_ $l->{iid} =~ /^w/ ? ($l->{num} ? 'Comment on ' : 'Review of ') :
                          $l->{iid} =~ /^t/ ? ($l->{num} == 1 ? 'New thread ' : 'Reply to ') : 'Edit of ';
-                    i_ $l->{c_title};
+                    i_ $l->{title};
                     txt_ ' by ';
                     i_ user_displayname $l;
                 };
@@ -109,17 +109,16 @@ TUWF::get qr{/$RE{uid}/notifies}, sub {
     )->data;
 
     my $where = sql_and(
-        sql('uid =', \$id),
-        $opt->{r} ? () : 'read IS NULL'
+        sql('n.uid =', \$id),
+        $opt->{r} ? () : 'n.read IS NULL'
     );
-    my $count = tuwf->dbVali('SELECT count(*) FROM notifications WHERE', $where);
+    my $count = tuwf->dbVali('SELECT count(*) FROM notifications n WHERE', $where);
     my $list = tuwf->dbPagei({ results => 25, page => $opt->{p} },
-       'SELECT n.id, n.ntype, n.iid, n.num, n.c_title
+       'SELECT n.id, n.ntype::text[] AS ntype, n.iid, n.num, t.title, ', sql_user(), '
              , ', sql_totime('n.date'), ' as date
              , ', sql_totime('n.read'), ' as read
-             , ', sql_user(),
-         'FROM notifications n
-          LEFT JOIN users u ON u.id = n.c_byuser
+          FROM notifications n, item_info(n.iid, n.num) t
+          LEFT JOIN users u ON u.id = t.uid
          WHERE ', $where,
         'ORDER BY n.id', $opt->{r} ? 'DESC' : 'ASC'
     );
diff --git a/sql/func.sql b/sql/func.sql
index 6b77d30a..94d442fa 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -353,6 +353,38 @@ CREATE OR REPLACE FUNCTION ulist_labels_create(integer) RETURNS void AS $$
 $$ LANGUAGE SQL;
 
 
+-- Returns the title and (where applicable) uid of the user who created the thing for almost every supported vndbid + num.
+-- While a function like this would be super useful in many places, it's too slow to be used in large or popular listings.
+-- A VIEW that can be joined would offer much better optimization possibilities, but I've not managed to write that in a performant way yet.
+-- A MATERIALIZED VIEW would likely be the fastest approach, but keeping that up-to-date seems like a pain.
+--
+-- Not currently supported: i#, g#, u#, ch#, cv#, sf#
+CREATE OR REPLACE FUNCTION item_info(id vndbid, num int) RETURNS TABLE(title text, uid int) AS $$
+  -- x#.#
+            SELECT v.title,  h.requester FROM changes h JOIN vn_hist v        ON h.id = v.chid WHERE h.type = 'v' AND vndbid_type($1) = 'v' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
+  UNION ALL SELECT r.title,  h.requester FROM changes h JOIN releases_hist r  ON h.id = r.chid WHERE h.type = 'r' AND vndbid_type($1) = 'r' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
+  UNION ALL SELECT p.name,   h.requester FROM changes h JOIN producers_hist p ON h.id = p.chid WHERE h.type = 'p' AND vndbid_type($1) = 'p' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
+  UNION ALL SELECT c.name,   h.requester FROM changes h JOIN chars_hist c     ON h.id = c.chid WHERE h.type = 'c' AND vndbid_type($1) = 'c' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
+  UNION ALL SELECT d.title,  h.requester FROM changes h JOIN docs_hist d      ON h.id = d.chid WHERE h.type = 'd' AND vndbid_type($1) = 'd' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
+  UNION ALL SELECT sa.name,  h.requester FROM changes h JOIN staff_hist s     ON h.id = s.chid JOIN staff_alias_hist sa ON sa.chid = s.chid AND sa.aid = s.aid WHERE h.type = 's' AND vndbid_type($1) = 's' AND h.itemid = vndbid_num($1) AND $2 IS NOT NULL AND h.rev = $2
+  -- x#
+  UNION ALL SELECT title,   NULL   FROM vn        WHERE vndbid_type($1) = 'v' AND id = vndbid_num($1) AND $2 IS NULL
+  UNION ALL SELECT title,   NULL   FROM releases  WHERE vndbid_type($1) = 'r' AND id = vndbid_num($1) AND $2 IS NULL
+  UNION ALL SELECT name,    NULL   FROM producers WHERE vndbid_type($1) = 'p' AND id = vndbid_num($1) AND $2 IS NULL
+  UNION ALL SELECT name,    NULL   FROM chars     WHERE vndbid_type($1) = 'c' AND id = vndbid_num($1) AND $2 IS NULL
+  UNION ALL SELECT title,   NULL   FROM docs      WHERE vndbid_type($1) = 'd' AND id = vndbid_num($1) AND $2 IS NULL
+  UNION ALL SELECT sa.name, NULL   FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE vndbid_type($1) = 's' AND s.id = vndbid_num($1) AND $2 IS NOT NULL AND $2 IS NULL
+  -- t#
+  UNION ALL SELECT title,   NULL   FROM threads WHERE vndbid_type($1) = 't' AND id = $1 AND $2 IS NULL
+  -- t#.#
+  UNION ALL SELECT t.title, tp.uid FROM threads t JOIN threads_posts tp ON tp.tid = t.id WHERE vndbid_type($1) = 't' AND t.id = $1 AND $2 IS NOT NULL AND tp.num = $2
+  -- w#
+  UNION ALL SELECT v.title, w.uid  FROM reviews w JOIN vn v ON v.id = w.vid WHERE vndbid_type($1) = 'w' AND w.id = $1 AND $2 IS NULL
+  -- w#.#
+  UNION ALL SELECT v.title, wp.uid FROM reviews w JOIN vn v ON v.id = w.vid JOIN reviews_posts wp ON wp.id = w.id WHERE vndbid_type($1) = 'w' AND w.id = $1 AND $2 IS NOT NULL AND wp.num = $2
+$$ LANGUAGE SQL ROWS 1;
+
+
 
 
 ----------------------------------------------------------
@@ -473,23 +505,9 @@ BEGIN
     PERFORM traits_chars_calc(xedit.itemid);
   END IF;
 
-  -- Call notify_dbdel() if an entry has been deleted
-  -- Call notify_listdel() if a vn/release entry has been deleted
-  IF xoldchid IS NOT NULL
-     AND EXISTS(SELECT 1 FROM changes WHERE id = xoldchid AND NOT ihid)
-     AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND ihid)
-  THEN
-    PERFORM notify_dbdel(xtype, xedit);
-    IF xtype = 'v' OR xtype = 'r' THEN
-      PERFORM notify_listdel(xtype, xedit);
-    END IF;
-  END IF;
-
-  -- Call notify_dbedit() if a non-hidden entry has been edited
-  IF xoldchid IS NOT NULL AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND NOT ihid)
-  THEN
-    PERFORM notify_dbedit(xtype, xedit);
-  END IF;
+  -- Create edit notifications
+  INSERT INTO notifications (uid, ntype, iid, num)
+       SELECT n.uid, n.ntype, n.iid, n.num FROM changes c, notify(vndbid(c.type::text, c.itemid), c.rev, c.requester) n WHERE c.id = xedit.chid;
 
   -- Make sure all visual novels linked to a release have a corresponding entry
   -- in ulist_vns for users who have the release in rlists. This is action (3) in
@@ -522,70 +540,83 @@ $$ LANGUAGE plpgsql;
 ----------------------------------------------------------
 
 
--- called when an entry has been deleted
-CREATE OR REPLACE FUNCTION notify_dbdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
-  INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
-    SELECT DISTINCT 'dbdel'::notification_ntype, h.requester, vndbid(xtype::text, xedit.itemid), xedit.rev, x.title, h2.requester
-      FROM changes h
-      -- join info about the deletion itself
-      JOIN changes h2 ON h2.id = xedit.chid
-      -- Fetch the latest name/title of the entry
-      -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
-      JOIN (  SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid
-        UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
-        UNION SELECT p.name  FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
-        UNION SELECT c.name  FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
-        UNION SELECT d.title FROM docs d WHERE xtype = 'd' AND d.id = xedit.itemid
-        UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
-      ) x(title) ON true
-     WHERE h.type = xtype AND h.itemid = xedit.itemid
-       AND h.requester <> 1 -- exclude Multi
-       AND h.requester <> h2.requester; -- exclude the user who deleted the entry
-$$ LANGUAGE sql;
-
-
-
--- Called when a non-deleted item has been edited.
-CREATE OR REPLACE FUNCTION notify_dbedit(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
-  INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
-    SELECT DISTINCT 'dbedit'::notification_ntype, h.requester, vndbid(xtype::text, xedit.itemid), xedit.rev, x.title, h2.requester
-      FROM changes h
-      -- join info about the edit itself
-      JOIN changes h2 ON h2.id = xedit.chid
-      -- Fetch the latest name/title of the entry
-      JOIN (  SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid
-        UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
-        UNION SELECT p.name  FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
-        UNION SELECT c.name  FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
-        UNION SELECT d.title FROM docs d WHERE xtype = 'd' AND d.id = xedit.itemid
-        UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
-      ) x(title) ON true
-     WHERE h.type = xtype AND h.itemid = xedit.itemid
-       AND h.requester <> h2.requester -- exclude the user who edited the entry
-       AND h2.requester <> 1 -- exclude edits by Multi
-       -- exclude users who don't want this notify
-       AND EXISTS(SELECT 1 FROM users u WHERE u.id = h.requester AND notify_dbedit);
-$$ LANGUAGE sql;
-
-
+-- Called after a certain event has occurred (new edit, post, etc).
+--  'iid' and 'num' identify the item that has been created.
+--  'uid' indicates who created the item, providing an easy method of not creating a notification for that user.
+--     (can technically be fetched with a DB lookup, too)
+--
+-- TODO: Don't create a notification if the user still has an unread notification on the same item, but lower 'num'?
+CREATE OR REPLACE FUNCTION notify(iid vndbid, num integer, uid integer) RETURNS TABLE (uid integer, ntype notification_ntype[], iid vndbid, num int) AS $$
+  SELECT uid, array_agg(ntype), $1, $2
+    FROM (
 
--- called when a VN/release entry has been deleted
-CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
-  INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
-    SELECT DISTINCT 'listdel'::notification_ntype, u.uid, vndbid(xtype::text, xedit.itemid), xedit.rev, x.title, c.requester
-      -- look for users who should get this notify
-      FROM (
-              SELECT uid FROM ulist_vns WHERE xtype = 'v' AND vid = xedit.itemid
-        UNION SELECT uid FROM rlists    WHERE xtype = 'r' AND rid = xedit.itemid
-      ) u
-      -- fetch info about this edit
-      JOIN changes c ON c.id = xedit.chid
-      JOIN (
-              SELECT title FROM vn       WHERE xtype = 'v' AND id = xedit.itemid
-        UNION SELECT title FROM releases WHERE xtype = 'r' AND id = xedit.itemid
-      ) x ON true
-     WHERE c.requester <> u.uid;
-$$ LANGUAGE sql;
+      -- pm
+      SELECT 'pm'::notification_ntype, tb.iid
+        FROM threads_boards tb
+       WHERE vndbid_type($1) = 't' AND tb.tid = $1 AND tb.type = 'u'
+
+      -- dbdel
+      UNION
+      SELECT 'dbdel', c_all.requester
+        FROM changes c_cur, changes c_all, changes c_pre
+       WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2   -- Current edit
+         AND c_pre.type = vndbid_type($1)::dbentry_type AND c_pre.itemid = vndbid_num($1) AND c_pre.rev = $2-1 -- Previous edit, to check if .ihid changed
+         AND c_all.type = vndbid_type($1)::dbentry_type AND c_all.itemid = vndbid_num($1) -- All edits on this entry, to see whom to notify
+         AND c_cur.ihid AND NOT c_pre.ihid
+         AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd')
+
+      -- listdel
+      UNION
+      SELECT 'listdel', u.uid
+        FROM changes c_cur, changes c_pre,
+             ( SELECT uid FROM ulist_vns WHERE vndbid_type($1) = 'v' AND vid = vndbid_num($1) -- TODO: Could use an index on ulist_vns.vid
+               UNION ALL
+               SELECT uid FROM rlists    WHERE vndbid_type($1) = 'r' AND rid = vndbid_num($1) -- TODO: Could also use an index, but the rlists table isn't that large so it's still okay
+             ) u(uid)
+       WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2   -- Current edit
+         AND c_pre.type = vndbid_type($1)::dbentry_type AND c_pre.itemid = vndbid_num($1) AND c_pre.rev = $2-1 -- Previous edit, to check if .ihid changed
+         AND c_cur.ihid AND NOT c_pre.ihid
+         AND $2 > 1 AND vndbid_type($1) IN('v','r')
+
+      -- dbedit
+      UNION
+      SELECT 'dbedit', c_all.requester
+        FROM changes c_cur, changes c_all
+        JOIN users u ON u.id = c_all.requester
+       WHERE c_cur.type = vndbid_type($1)::dbentry_type AND c_cur.itemid = vndbid_num($1) AND c_cur.rev = $2
+         AND c_all.type = vndbid_type($1)::dbentry_type AND c_all.itemid = vndbid_num($1)
+         AND $2 > 1 AND vndbid_type($1) IN('v', 'r', 'p', 'c', 's', 'd')
+         AND c_cur.requester <> 1 -- exclude edits by Multi
+         AND u.notify_dbedit
+
+      -- announce
+      UNION
+      SELECT 'announce', u.id
+        FROM threads t
+        JOIN threads_boards tb ON tb.tid = t.id
+        JOIN users u ON u.notify_announce
+       WHERE vndbid_type($1) = 't' AND $2 = 1 AND t.id = $1 AND tb.type = 'an'
+
+      -- post
+      -- TODO: Should this also include comments on reviews the user has commented on?
+      UNION
+      SELECT 'post', u.id
+        FROM threads t, threads_posts tp
+        JOIN users u ON tp.uid = u.id
+       WHERE t.id = $1 AND tp.tid = $1 AND vndbid_type($1) = 't' AND $2 > 1 AND NOT t.private AND NOT t.hidden AND u.notify_post
+
+      -- comment
+      UNION
+      SELECT 'comment', u.id
+        FROM reviews w
+        JOIN users u ON w.uid = u.id
+       WHERE w.id = $1 AND vndbid_type($1) = 'w' AND $2 IS NOT NULL AND u.notify_comment
+
+    ) AS noti(ntype, uid)
+   WHERE uid <> $3
+     AND uid <> 1 -- No announcements for Multi
+   GROUP BY uid;
+$$ LANGUAGE SQL;
 
 
 
diff --git a/sql/schema.sql b/sql/schema.sql
index 3605e9cc..5dec6abe 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -251,11 +251,9 @@ CREATE TABLE notifications (
   uid      integer NOT NULL,
   date     timestamptz NOT NULL DEFAULT NOW(),
   read     timestamptz,
-  ntype    notification_ntype NOT NULL,
+  ntype    notification_ntype[] NOT NULL,
   iid      vndbid NOT NULL,
-  num      integer,
-  c_title  text NOT NULL,
-  c_byuser integer
+  num      integer
 );
 
 -- producers
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index f00db33d..15d81b95 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -19,7 +19,6 @@ ALTER TABLE chars_vns_hist           ADD CONSTRAINT chars_vns_hist_rid_fkey
 ALTER TABLE image_votes              ADD CONSTRAINT image_votes_id_fkey                FOREIGN KEY (id)        REFERENCES images        (id) ON DELETE CASCADE;
 ALTER TABLE image_votes              ADD CONSTRAINT image_votes_uid_fkey               FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE SET DEFAULT;
 ALTER TABLE notifications            ADD CONSTRAINT notifications_uid_fkey             FOREIGN KEY (uid)       REFERENCES users         (id) ON DELETE CASCADE;
-ALTER TABLE notifications            ADD CONSTRAINT notifications_c_byuser_fkey        FOREIGN KEY (c_byuser)  REFERENCES users         (id) ON DELETE SET DEFAULT;
 ALTER TABLE producers                ADD CONSTRAINT producers_l_wikidata_fkey          FOREIGN KEY (l_wikidata)REFERENCES wikidata      (id);
 ALTER TABLE producers_hist           ADD CONSTRAINT producers_chid_id_fkey             FOREIGN KEY (chid)      REFERENCES changes       (id) ON DELETE CASCADE;
 ALTER TABLE producers_hist           ADD CONSTRAINT producers_hist_l_wikidata_fkey     FOREIGN KEY (l_wikidata)REFERENCES wikidata      (id);
diff --git a/sql/triggers.sql b/sql/triggers.sql
index 2fd34f1c..d05f18ba 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -228,76 +228,11 @@ CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_se
 
 
 
--- Add a notification when someone posts in someone's board.
-
-CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$
-BEGIN
-  INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
-    SELECT 'pm', tb.iid, t.id, NEW.num, t.title, NEW.uid
-      FROM threads t
-      JOIN threads_boards tb ON tb.tid = t.id
-     WHERE t.id = NEW.tid
-       AND tb.type = 'u'
-       AND tb.iid <> NEW.uid -- don't notify when posting in your own board
-       AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet
-         SELECT 1
-           FROM notifications n
-          WHERE n.uid = tb.iid
-            AND n.iid = t.id
-            AND n.read IS NULL
-       );
-  RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
-
-
-
-
--- Add a notification when a thread is created in /t/an
-
-CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
-BEGIN
-  INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
-    SELECT 'announce', u.id, t.id, 1, t.title, NEW.uid
-      FROM threads t
-      JOIN threads_boards tb ON tb.tid = t.id
-      -- get the users who want this announcement
-      JOIN users u ON u.notify_announce
-     WHERE t.id = NEW.tid
-       AND tb.type = 'an' -- announcement board
-       AND NOT t.hidden;
-  RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
-
-
-
-
--- Add a notification on new posts
+-- Create notifications for new posts.
 
 CREATE OR REPLACE FUNCTION notify_post() RETURNS trigger AS $$
 BEGIN
-  INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
-    SELECT DISTINCT 'post'::notification_ntype, u.id, t.id, NEW.num, t.title, NEW.uid
-      FROM threads t
-      JOIN threads_posts tp ON tp.tid = t.id
-      JOIN users u ON tp.uid = u.id
-     WHERE t.id = NEW.tid
-       AND u.notify_post
-       AND u.id <> NEW.uid
-       AND NOT t.hidden
-       AND NOT t.private -- don't leak posts in private threads, these are handled by notify_pm anyway
-       AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet (also avoids double notification with notify_pm)
-         SELECT 1
-           FROM notifications n
-          WHERE n.uid = u.id
-            AND n.iid = t.id
-            AND n.read IS NULL
-       );
+  INSERT INTO notifications (uid, ntype, iid, num) SELECT uid, ntype, iid, num FROM notify(NEW.tid, NEW.num, NEW.uid) n;
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
@@ -307,25 +242,11 @@ CREATE TRIGGER notify_post AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PR
 
 
 
--- Add a notification on new comment to review
+-- Create notifications for new review comments.
 
 CREATE OR REPLACE FUNCTION notify_comment() RETURNS trigger AS $$
 BEGIN
-  INSERT INTO notifications (ntype, uid, iid, num, c_title, c_byuser)
-    SELECT 'comment', u.id, w.id, NEW.num, v.title, NEW.uid
-      FROM reviews w
-      JOIN vn v ON v.id = w.vid
-      JOIN users u ON w.uid = u.id
-     WHERE w.id = NEW.id
-       AND u.notify_comment
-       AND u.id <> NEW.uid
-       AND NOT EXISTS( -- don't notify when you haven't read earlier comments yet
-         SELECT 1
-           FROM notifications n
-          WHERE n.uid = u.id
-            AND n.iid = w.id
-            AND n.read IS NULL
-       );
+  INSERT INTO notifications (uid, ntype, iid, num) SELECT uid, ntype, iid, num FROM notify(NEW.id, NEW.num, NEW.uid) n;
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
diff --git a/util/updates/wip-notifications.sql b/util/updates/wip-notifications.sql
new file mode 100644
index 00000000..31dababa
--- /dev/null
+++ b/util/updates/wip-notifications.sql
@@ -0,0 +1,17 @@
+-- Simplified triggers, all the logic is consolidated in notify().
+DROP TRIGGER notify_pm ON threads_posts;
+DROP TRIGGER notify_announce ON threads_posts;
+DROP FUNCTION notify_pm();
+DROP FUNCTION notify_announce();
+
+DROP FUNCTION notify_dbdel(dbentry_type, edit_rettype);
+DROP FUNCTION notify_dbedit(dbentry_type, edit_rettype);
+DROP FUNCTION notify_listdel(dbentry_type, edit_rettype);
+
+-- Table changes
+ALTER TABLE notifications ALTER COLUMN ntype TYPE notification_ntype[] USING ARRAY[ntype];
+ALTER TABLE notifications DROP COLUMN c_title;
+ALTER TABLE notifications DROP COLUMN c_byuser;
+
+\i sql/func.sql
+\i sql/triggers.sql