vnctalk-prosody/db-customization/prosody-13-migration-once.sql

-- Migration script — Prosody 0.11.6 -> 13.0.6
--
-- Run EXACTLY ONCE, after the Prosody image has been upgraded to 13.0.6 and
-- AFTER the existing db-customization scripts
-- (prosody-queries-noowner.sql.notifyfix + prosody-trigger-noowner.sql) have
-- been applied to this database.
--
-- This script:
--   1. Reconciles derived tables with the new MUC storage layout (affiliations
--      stored as one row per bare JID in the `prosody` `config` store, no
--      `_affiliations` / `_occupants` keys).
--   2. Removes the now-obsolete `room_nicknames` view and the
--      `update_room_nick_jid_map` rule that depended on it.
--   3. Is a NO-OP if run a second time (all statements are idempotent), but it
--      is intended to be run only once.
--
-- Prerequisite: the `prosody` and `prosodyarchive` tables must already exist
-- (created by the 13.0.6 storage backend / the regular db-customization
-- scripts). This script aborts if they do not.
--
-- The `room_nick_jid_map` rebuild assumes all allowed clients use their bare
-- JID as their nickname in rooms, so the map can be created purely from
-- affiliations (nickname = "<room@host>/<bare-jid>").

\set ON_ERROR_STOP on

-- Guard: bail out 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 $$;

-- ---------------------------------------------------------------------------
-- 0. Create the unique index that Prosody 13 expects on the `prosody` table.
--
-- On a fresh DB, mod_storage_sql creates `prosody_unique_index` together with
-- the table. On a DB upgraded from 0.11.6 the table already existed, so
-- Prosody's upgrade path only *warns* ("Index prosody_unique_index does not
-- exist") and sets has_upsert_index=false — disabling ON CONFLICT upserts and
-- falling back to slower SELECT-then-INSERT/UPDATE. Create it here so the
-- index exists after migration and the warning is gone on next startup.
--
-- Without the unique index, mod_storage_sql falls back to SELECT-then-INSERT
-- instead of ON CONFLICT upsert, which can create duplicate rows under
-- concurrent writes (common in the fcmtoken map store). Deduplicate before
-- creating the index so it does not fail on pre-existing duplicates.
-- Idempotent: safe to re-run.
-- ---------------------------------------------------------------------------

-- Remove duplicate rows, keeping only the last-written row per
-- (host, user, store, key). ctid is the physical row identifier; the
-- highest ctid is the most recently inserted row. No-op if no duplicates.
delete from prosody
where ctid in (
  select ctid from (
    select ctid,
           row_number() over (
             partition by host, "user", store, key
             order by ctid desc
           ) as rn
    from prosody
  ) t
  where rn > 1
);

create unique index if not exists prosody_unique_index
  on prosody ("host", "user", "store", "key");

-- ---------------------------------------------------------------------------
-- 1. Drop obsolete objects that referenced the old `_affiliations` / `_occupants` rows
-- ---------------------------------------------------------------------------

-- The `room_nicknames` view read `key='_occupants'`, which no longer exists in
-- 13.0.6 (occupant state moved to the `state` store and is only populated on
-- graceful shutdown, so it is not usable for live nickname resolution).
drop view if exists room_nicknames cascade;

-- `update_room_nick_jid_map` refreshed `room_nick_jid_map` from `room_nicknames`
-- on every `muc_log` insert; with the view gone it is dead.
drop rule if exists update_room_nick_jid_map on prosodyarchive cascade;

-- ---------------------------------------------------------------------------
-- 2. Backfill `room_nick_jid_map` from the new per-affiliation rows
-- ---------------------------------------------------------------------------

-- Remove rows for affiliations that no longer exist (rooms whose members were
-- changed under 13.0.6 before this migration, or stale entries from the old
-- `_occupants`-based population).
delete from room_nick_jid_map
where room_name in (
  select p.user || '@' || p.host
  from prosody p
  where p.store = 'config' and p.host like 'conference.%'
  group by p.user, p.host
)
and not exists (
  select 1 from prosody p2
  where p2.store = 'config'
    and p2.host like 'conference.%'
    and p2.key like '%@%'
    and p2.value in ('owner','admin','member','outcast','none')
    and p2.user || '@' || p2.host = room_nick_jid_map.room_name
    and p2.key = room_nick_jid_map.user_jid
);

-- Upsert current members. Nickname = "<room@host>/<bare-jid>" (bare JID is the
-- only nickname used by allowed clients). 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
  p.user || '@' || p.host as room_name,
  p.key as user_jid,
  (p.user || '@' || p.host) || '/' || p.key as nickname,
  (extract(epoch from now())::integer - 60) as since
from prosody p
where p.store = 'config'
  and p.host like 'conference.%'
  and p.key like '%@%'
  and p.value in ('owner','admin','member','outcast','none')
  and not exists (
    select 1 from room_nick_jid_map r
    where r.room_name = p.user || '@' || p.host
      and r.user_jid = p.key
      and r.nickname = (p.user || '@' || p.host) || '/' || p.key
  );

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

-- ---------------------------------------------------------------------------
-- 3. Reconcile `group_owners` with the new per-affiliation rows
-- ---------------------------------------------------------------------------

-- Drop owners that are no longer present as `value='owner'` affiliation rows.
delete from group_owners go
where not exists (
  select 1 from prosody p
  where p.store = 'config'
    and p.host like 'conference.%'
    and p.key like '%@%'
    and p.value = 'owner'
    and p.user || '@' || p.host = go.room
    and p.key = go.owner
);

-- Upsert current owners (one row per room; pick the first owner by JID).
-- group_owners has an INSERT INSTEAD rule (upsert_group_owners) so ON CONFLICT
-- cannot be used; insert rooms that are missing, update the rest.
insert into group_owners (room, owner, created, updated)
select
  sub.room,
  sub.owner,
  extract(epoch from now())::integer as created,
  extract(epoch from now())::integer as updated
from (
  select
    p.user || '@' || p.host as room,
    min(p.key) as owner
  from prosody p
  where p.store = 'config'
    and p.host like 'conference.%'
    and p.key like '%@%'
    and p.value = 'owner'
  group by p.user, p.host
) sub
where not exists (
  select 1 from group_owners go where go.room = sub.room
);

update group_owners go
set owner = sub.owner,
    updated = extract(epoch from now())::integer
from (
  select
    p.user || '@' || p.host as room,
    min(p.key) as owner
  from prosody p
  where p.store = 'config'
    and p.host like 'conference.%'
    and p.key like '%@%'
    and p.value = 'owner'
  group by p.user, p.host
) sub
where go.room = sub.room
  and go.owner <> sub.owner;

-- ---------------------------------------------------------------------------
-- 4. Mark `recent_history_table` rows for users no longer affiliated
-- ---------------------------------------------------------------------------

-- Members removed from a room before this migration may still have non-deleted
-- recent-history rows. Mark them deleted now (the rewritten trigger in the
-- idempotent script will keep this in sync going forward).
update recent_history_table rht
set deleted = true
where rht.type = 'groupchat'
  and rht.target like '%@conference.%'
  and not exists (
    select 1 from prosody p
    where p.store = 'config'
      and p.host like 'conference.%'
      and p.key like '%@%'
      and p.value in ('owner','admin','member','outcast','none')
      and p.user || '@' || p.host = rht.target
      and p.key = rht.username
  )
  and exists (
    select 1 from prosody p3
    where p3.store = 'config'
      and p3.host like 'conference.%'
      and p3.user || '@' || p3.host = rht.target
  );

-- Done. The derived tables now match the 13.0.6 storage layout. Going forward
-- the rewritten rules/triggers from `prosody-13-migration-rules-triggers.sql`
-- keep them consistent.