vnctalk-prosody/db-customization/prosody-13-rules-triggers.sql

-- Idempotent rules, views and triggers for Prosody 13.0.6
--
-- This script creates the rules, views and trigger functions that had to be
-- adapted to the new MUC storage layout (affiliations stored as one row per
-- bare JID; `_affiliations` / `_occupants` keys gone).
--
-- It is IDEMPOTENT: every object is created with `or replace` / `drop if
-- exists` first, so it can be re-run safely on any 13.0.6 database.
--
-- It only creates the objects that CHANGED vs the 0.11.6 scripts
-- (`prosody-queries-noowner.sql.notifyfix` + `prosody-trigger-noowner.sql`).
-- It does NOT recreate the many rules/views/triggers that are unaffected by
-- the storage change — those remain defined by the original two scripts.
--
-- The `room_nick_jid_map` maintenance assumes all allowed clients use their
-- bare JID as their nickname in rooms, so the map is derived purely from
-- affiliations (nickname = "<room@host>/<bare-jid>").
--
-- Prerequisite: the `prosody` and `prosodyarchive` tables must already exist.
-- The script aborts if they do not.

\set ON_ERROR_STOP on

-- Guard: do nothing if the Prosody tables are missing.
do $$
begin
  if not exists (select 1 from information_schema.tables where table_name = 'prosody') then
    raise exception 'Table "prosody" does not exist. Apply the regular db-customization scripts first.';
  end if;
  if not exists (select 1 from information_schema.tables where table_name = 'prosodyarchive') then
    raise exception 'Table "prosodyarchive" does not exist. Apply the regular db-customization scripts first.';
  end if;
end $$;

-- ===========================================================================
-- VIEWS
-- ===========================================================================

-- `room_membership`: was `where prosody.key='_affiliations'` with
-- `jsonb_object_keys(value::jsonb)`. Affiliations are now one row per bare JID
-- (`key=<jid>`, `value=<affiliation string>`). Column shape is preserved so
-- all consumers (update_muc_recent, insert_unread_message_ids_muc,
-- insert_unread_mention_ids_muc, fprocess_messages muc_log branches) work
-- unchanged.
create or replace view room_membership as
  select
    prosody.key as username,
    ''::text as user_room_nickname,
    prosody.host as host,
    prosody.user as room
  from prosody
  where prosody.store = 'config'
    and prosody.host like 'conference.%'
    and prosody.key like '%@%'
    and prosody.value in ('owner','admin','member','outcast','none');

-- `room_nicknames` is obsolete (read `key='_occupants'`, which no longer
-- exists). Drop it so stale references do not linger. `room_nick_jid_map`
-- (maintained below from affiliations) replaces it for nickname resolution.
drop view if exists room_nicknames cascade;

-- ===========================================================================
-- TRIGGER: group_owners maintenance (replaces the old `update_group_owners` rule)
-- ===========================================================================
--
-- Was a rule `on insert to prosody where key='_affiliations' ...`. Now owners
-- are rows with `key=<bare jid>`, `value='owner'`. A trigger is used instead
-- of a rule because `group_owners` already has an INSERT INSTEAD rule
-- (`upsert_group_owners`); combining DO-INSTEAD and DO rules on the same
-- insert produces ambiguous/conflicting behaviour. The trigger fires on the
-- `_data` row (always written on every room save) and reconciles
-- `group_owners` for the whole room, mirroring how `fupdate_room_nick_jid`
-- rebuilds `room_nick_jid_map`.
create or replace function fupdate_group_owners()
  returns trigger AS
  $BODY$
  DECLARE
    _room text;
  BEGIN
    _room := NEW.user || '@' || NEW.host;

    -- remove owners that are no longer affiliated as owner
    delete from group_owners
    where room = _room
      and owner not in (
        select key from prosody
        where store = 'config'
          and host = NEW.host
          and "user" = NEW.user
          and key like '%@%'
          and value = 'owner'
      );

    -- upsert current owners. group_owners has an INSERT INSTEAD rule
    -- (upsert_group_owners) so ON CONFLICT cannot be used; insert the room
    -- if missing, otherwise update the owner.
    insert into group_owners (room, owner, created, updated)
    select
      _room,
      sub.owner,
      extract(epoch from now())::integer,
      extract(epoch from now())::integer
    from (
      select key as owner
      from prosody
      where store = 'config'
        and host = NEW.host
        and "user" = NEW.user
        and key like '%@%'
        and value = 'owner'
      order by key
      limit 1
    ) sub
    where not exists (
      select 1 from group_owners go where go.room = _room
    );

    update group_owners
    set owner = sub.owner,
        updated = extract(epoch from now())::integer
    from (
      select key as owner
      from prosody
      where store = 'config'
        and host = NEW.host
        and "user" = NEW.user
        and key like '%@%'
        and value = 'owner'
      order by key
      limit 1
    ) sub
    where group_owners.room = _room
      and group_owners.owner <> sub.owner;

    RETURN NEW;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

drop trigger if exists update_group_owners on prosody;
create trigger update_group_owners after insert on prosody
  for each row
  when (NEW.store = 'config' and NEW.key = '_data' and NEW.host like 'conference.%')
  execute procedure fupdate_group_owners();

-- ===========================================================================
-- RULES ON `prosodyarchive`
-- ===========================================================================

-- `update_room_nick_jid_map` refreshed `room_nick_jid_map` from the now-dropped
-- `room_nicknames` view on every `muc_log` insert. With `_occupants` gone it
-- is dead; `room_nick_jid_map` is maintained by the `fupdate_room_nick_jid`
-- trigger on the `config` store plus the (unchanged) invite rules.
drop rule if exists update_room_nick_jid_map on prosodyarchive cascade;

-- ===========================================================================
-- TRIGGER FUNCTION: fupdate_room_nick_jid
-- ===========================================================================

-- Was: trigger `when (NEW.key='_affiliations')`, body rebuilt the room's map
-- from `jsonb_object_keys(NEW.value::jsonb)`. Now: there is no `_affiliations`
-- row; affiliations are one row per bare JID.
--
-- Approach: fire on the `_data` row, which is always written on every room
-- save (it is always present in `freeze()` output), and rebuild
-- `room_nick_jid_map` from the current set of affiliation rows for the room.
-- (The kv `set` DELETEs then re-inserts all rows in one transaction; the
-- `_data` row is inserted after the affiliation rows, so they are already
-- present when this trigger fires.)
--
-- Nickname = "<room@host>/<bare-jid>" — bare JID is the only nickname used by
-- allowed clients, so the map is derived purely from affiliations.
create or replace function fupdate_room_nick_jid()
  returns trigger AS
  $BODY$
  DECLARE
    _room text;
  BEGIN
    _room := NEW.user || '@' || NEW.host;

    -- remove mapping entries for users no longer affiliated
    delete from room_nick_jid_map
    where room_name = _room
      and user_jid not in (
        select key from prosody
        where store = 'config'
          and host = NEW.host
          and "user" = NEW.user
          and key like '%@%'
          and value in ('owner','admin','member','outcast','none')
      );

    -- mark recent_history rows for dropped members as deleted
    update recent_history_table
    set deleted = true
    where target = _room
      and username not in (
        select key from prosody
        where store = 'config'
          and host = NEW.host
          and "user" = NEW.user
          and key like '%@%'
          and value in ('owner','admin','member','outcast','none')
      );

    -- upsert current members (nickname = room/bare-jid). room_nick_jid_map has
    -- an INSERT INSTEAD rule (insert_room_nick_jid_map) so ON CONFLICT cannot
    -- be used; insert rows that are missing, update the rest.
    insert into room_nick_jid_map (room_name, user_jid, nickname, since)
    select
      _room,
      key,
      _room || '/' || key,
      (extract(epoch from now())::integer - 60)
    from prosody
    where store = 'config'
      and host = NEW.host
      and "user" = NEW.user
      and key like '%@%'
      and value in ('owner','admin','member','outcast','none')
      and not exists (
        select 1 from room_nick_jid_map r
        where r.room_name = _room
          and r.user_jid = prosody.key
          and r.nickname = _room || '/' || prosody.key
      );

    update room_nick_jid_map r
    set since = (extract(epoch from now())::integer - 60)
    from prosody
    where prosody.store = 'config'
      and prosody.host = NEW.host
      and prosody."user" = NEW.user
      and prosody.key like '%@%'
      and prosody.value in ('owner','admin','member','outcast','none')
      and r.room_name = _room
      and r.user_jid = prosody.key
      and r.nickname = _room || '/' || prosody.key;

    RETURN NEW;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE;

-- (Re)create the trigger. Drop the old `when (NEW.key='_affiliations')`
-- trigger first; the duplicate function definitions from the 0.11.6 script
-- are consolidated by the `create or replace function` above.
drop trigger if exists update_room_nick_jid on prosody;
create trigger update_room_nick_jid after insert on prosody
  for each row
  when (NEW.store = 'config' and NEW.key = '_data' and NEW.host like 'conference.%')
  execute procedure fupdate_room_nick_jid();

-- ===========================================================================
-- TRIGGER FUNCTION: fupdate_groupchat  (UNCHANGED — kept here for completeness)
-- ===========================================================================
--
-- `fupdate_groupchat` reads `key='_data'`, which 13.0.6 still writes with a
-- JSON value containing `subject`. It therefore keeps working as-is. It is
-- already created by `prosody-trigger-noowner.sql`; no change is needed and
-- it is NOT recreated here to avoid drifting from the original definition.

-- ===========================================================================
-- TRIGGERS: replace conditional DO rules on `prosody` (ON CONFLICT compat)
-- ===========================================================================
--
-- PostgreSQL rejects `INSERT ... ON CONFLICT ... DO UPDATE` on any table that
-- has a conditional (WHERE) DO/DO ALSO rule or a non-NOTHING DO INSTEAD rule
-- (error: "INSERT with ON CONFLICT clause cannot be used with table that has
-- INSERT or UPDATE rules"). Prosody 13's mod_storage_sql uses ON CONFLICT
-- upserts against the `prosody` kv table whenever `prosody_unique_index`
-- exists (created by the migration/new-deployment scripts). The historical
-- DO rules on `prosody` therefore broke every kv upsert (vcard, vcard_muc,
-- muc_remote, config, fcmtoken, ...).
--
-- Fix: convert each conditional DO rule on `prosody` to an AFTER INSERT
-- (/OR UPDATE) row-level trigger. Triggers do not block ON CONFLICT. The
-- derived-table INSTEAD upsert rules (upsert_group_avatarids,
-- upsert_profile_update_queue, upsert_remote_muc_names,
-- insert_room_nick_jid_map) are unchanged — they live on the derived tables,
-- not on `prosody`, and continue to rewrite the inserts these triggers emit.
--
-- This block is idempotent (drop if exists + create or replace) and is safe
-- to run on any 13.0.6 database, whether the rules are still present or were
-- already converted.
--
-- Also drop the legacy 0.11.6 `update_group_owners` rule (fires on
-- `key='_affiliations'`, dead under 13.0.6). PostgreSQL checks rule
-- *existence* at plan time, so even a dead conditional rule blocks ON
-- CONFLICT — dropping it is mandatory, not optional.

-- vcard_muc room avatar cache (was rule `cache_group_avatarids`)
create or replace function fcache_group_avatarids()
  returns trigger as $BODY$
begin
  insert into group_avatarids (room, avatarid)
  select
    NEW.user || '@' || NEW.host as room,
    encode(digest(decode(split_part(split_part(split_part(NEW.value, 'BINVAL', 2), '__array":["', 2), '"', 1), 'base64'), 'sha1'), 'hex') as avatarid;
  return NEW;
end;
$BODY$ language plpgsql volatile;

drop trigger if exists cache_group_avatarids on prosody;
create trigger cache_group_avatarids after insert on prosody
  for each row
  when (NEW.store = 'vcard_muc' and NEW.key = '' and NEW.type = 'json' and NEW.host like 'conference.%')
  execute procedure fcache_group_avatarids();

-- vcard profile-update queue. Was two rules (on insert + on update); merged
-- into one AFTER INSERT OR UPDATE trigger so the UPDATE branch of an ON
-- CONFLICT upsert is also covered (ON CONFLICT DO UPDATE fires AFTER UPDATE
-- triggers, not AFTER INSERT, when the conflict is taken).
create or replace function fupdate_profile_queue()
  returns trigger as $BODY$
begin
  insert into profile_update_queue (username, timestamp)
  select
    NEW.user || '@' || NEW.host as username,
    extract(epoch from now())::integer as timestamp;
  return NEW;
end;
$BODY$ language plpgsql volatile;

drop trigger if exists update_profile_queue on prosody;
create trigger update_profile_queue after insert or update on prosody
  for each row
  when (NEW.store = 'vcard' and NEW.type = 'json' and NEW.key = '')
  execute procedure fupdate_profile_queue();

-- muc_remote nickname map (was rule `update_muc_remote_name`)
create or replace function fupdate_muc_remote_name()
  returns trigger as $BODY$
begin
  insert into remote_muc_names (username, remotemuc, displayname)
  select
    NEW.user || '@' || NEW.host as username,
    NEW.key as remotemuc,
    NEW.value as displayname;
  return NEW;
end;
$BODY$ language plpgsql volatile;

drop trigger if exists update_muc_remote_name on prosody;
create trigger update_muc_remote_name after insert on prosody
  for each row
  when (NEW.store = 'muc_remote' and not (NEW.value ilike '%/%'))
  execute procedure fupdate_muc_remote_name();

-- room_nick_jid_map from muc_remote (was rule `update_room_nick_jid_map_remote`)
create or replace function fupdate_room_nick_jid_map_remote()
  returns trigger as $BODY$
begin
  insert into room_nick_jid_map (room_name, user_jid, nickname, since)
  select
    split_part(NEW.key, '/', 1) as room_name,
    NEW.value as user_jid,
    NEW.key as nickname,
    extract(epoch from now())::integer as since;
  return NEW;
end;
$BODY$ language plpgsql volatile;

drop trigger if exists update_room_nick_jid_map_remote on prosody;
create trigger update_room_nick_jid_map_remote after insert on prosody
  for each row
  when (NEW.store = 'muc_remote' and NEW.key like '%/%' and NEW.value like '%@%')
  execute procedure fupdate_room_nick_jid_map_remote();

-- Drop the rule counterparts these triggers replace.
drop rule if exists cache_group_avatarids on prosody;
drop rule if exists update_profile_queue_from_insert on prosody;
drop rule if exists update_profile_queue_from_update on prosody;
drop rule if exists update_muc_remote_name on prosody;
drop rule if exists update_room_nick_jid_map_remote on prosody;
-- Legacy 0.11.6 rule (dead under 13.0.6 but blocks ON CONFLICT by existence).
drop rule if exists update_group_owners on prosody;

-- ===========================================================================
-- VERIFICATION (optional, commented out)
-- ===========================================================================
-- Inspect the storage layout for a known room to confirm affiliations are
-- stored as one row per bare JID:
--
--   select key, type, value from prosody
--   where store='config' and host='conference.<domain>' and user='<room-node>';
--
-- Expected: rows with key = bare JID and value in
-- (owner, admin, member, outcast, none), plus _jid / _data /
-- _affiliation_data meta rows.