vnctalk-prosody/db-customization/README.md

db-customization

PostgreSQL schema, rules, triggers and migration scripts for the VNCtalk Prosody 13.0.6 database. Applied out-of-band (manually or via a job), not by the Prosody container itself.

Files

File	Idempotent	Purpose
prosody-13-new-deployment.sql	Yes	All-in-one script for new/fresh deployments. Creates every derived table, rule, view, trigger and index adapted to the 13.0.6 storage layout. Replaces the two legacy 0.11.6 scripts entirely.
prosody-queries-noowner.sql	No	Legacy 0.11.6 table + rule definitions (original non-idempotent form). Used only on databases that were set up under 0.11.6 and have not yet been migrated.
prosody-trigger-noowner.sql	No	Legacy 0.11.6 trigger definitions (original non-idempotent form). Companion to prosody-queries-noowner.sql.
prosody-13-rules-triggers.sql	Yes	Incremental upgrade script for existing 0.11.6 databases. Recreates only the rules, views and trigger functions that changed with the 13.0.6 MUC storage layout (affiliations stored one row per bare JID; _affiliations / _occupants keys gone). Does not touch the many objects that are unaffected.
prosody-13-migration-once.sql	Yes (but intended once)	One-time data migration for existing 0.11.6 databases. Backfills room_nick_jid_map, group_owners and recent_history_table from the new per-affiliation rows, and drops obsolete objects (room_nicknames view, update_room_nick_jid_map rule). Run after the legacy scripts and prosody-13-rules-triggers.sql have been applied.

Prerequisites

All scripts require the Prosody storage tables prosody and prosodyarchive to already exist. These are created by mod_storage_sql when Prosody first starts against the database. Therefore:

1. Point Prosody 13.0.6 at the database and start it once so the tables are created.
2. Run the appropriate script(s) below.

Every 13.0 script begins with a guard that aborts (\set ON_ERROR_STOP on + RAISE EXCEPTION) if the tables are missing.

Which script to run when

New deployment (empty database)

psql -f prosody-13-new-deployment.sql

That is the only script needed. It creates everything: tables, rules, views, triggers, indexes — all idempotent and adapted to 13.0.6.

Existing 0.11.6 database being upgraded to 13.0.6

Run all three in this order:

psql -f prosody-13-rules-triggers.sql      # 1. Replace changed rules/views/triggers
psql -f prosody-13-migration-once.sql       # 2. Backfill derived tables from new layout

The legacy scripts (prosody-queries-noowner.sql, prosody-trigger-noowner.sql) must have already been applied to the database under 0.11.6 — they created the derived tables and the majority of rules/triggers that are unaffected by the storage change. prosody-13-rules-triggers.sql only overwrites the objects that changed; prosody-13-migration-once.sql reconciles the data.

If the legacy scripts were never applied (unlikely for an existing 0.11.6 database), run them first, then the two 13.0 scripts above.

Re-running / drift correction

All 13.0 scripts are safe to re-run (idempotent). The legacy scripts are not idempotent and must not be re-run on a database that already has the objects.

Detecting the database state

Run this detection query to determine which scenario applies:

-- 1. Prosody storage tables exist?
select exists (select 1 from information_schema.tables where table_name = 'prosody') as prosody_table_exists;

-- 2. Derived tables exist (any db-customization ever applied)?
select exists (select 1 from information_schema.tables where table_name = 'processed_messages') as derived_tables_exist;

-- 3. room_membership view: old (_affiliations) or new (per-row) format?
select pg_get_viewdef('room_membership'::regclass, true) as room_membership_def;

-- 4. room_nicknames view still exists? (dropped by 13.0 scripts)
select exists (select 1 from information_schema.views where table_name = 'room_nicknames') as room_nicknames_view_exists;

-- 5. update_group_owners: legacy RULE vs 13.0 TRIGGER?
select 'rule' as kind from pg_rules where rulename = 'update_group_owners'
union all
select 'trigger' as kind from pg_trigger where tgname = 'update_group_owners';

-- 6. update_room_nick_jid trigger WHEN clause: _affiliations (old) or _data (new)?
select pg_get_triggerdef(oid) from pg_trigger where tgname = 'update_room_nick_jid';

Decision tree

1. prosody table exists?
   NO  → Start Prosody 13.0.6 once so mod_storage_sql creates the tables,
         then re-run detection.
   YES → continue to 2

2. derived tables exist (processed_messages etc.)?
   NO  → NEW DEPLOYMENT
         Run: psql -f prosody-13-new-deployment.sql
   YES → continue to 3

3. room_membership view references '_affiliations' / jsonb_object_keys?
   YES → 0.11.6 LEGACY, NOT YET MIGRATED
         (legacy scripts were applied under 0.11.6, 13.0 scripts not yet run)
         Run: psql -f prosody-13-rules-triggers.sql
              psql -f prosody-13-migration-once.sql
   NO  → already on 13.0 format → continue to 4

4. update_group_owners legacy RULE still present (alongside the TRIGGER)?
    YES → UPGRADED FROM 0.11.6, 13.0 scripts already applied
          The dead rule MUST be dropped: PostgreSQL rejects
          `INSERT ... ON CONFLICT ... DO UPDATE` on any table that has a
          conditional (WHERE) rule, even one whose WHERE never matches, so
          the dead `update_group_owners` rule blocks every kv upsert on the
          `prosody` table (error: "INSERT with ON CONFLICT clause cannot be
          used with table that has INSERT or UPDATE rules"). Re-running
          `prosody-13-rules-triggers.sql` drops it and converts the other
          conditional DO rules on `prosody` to triggers.
    NO  → continue to 5

5. room_nicknames view exists?
   YES → INCOMPLETE MIGRATION (legacy objects not cleaned up)
         Re-run: psql -f prosody-13-rules-triggers.sql
   NO  → CLEAN 13.0 STATE (new deployment or fully migrated)
         All scripts are in sync. Re-run any 13.0 script for
         drift correction if desired (all are idempotent).

Quick reference: detection signals per scenario

Signal	New deployment	0.11.6, not migrated	Upgraded from 0.11.6	Clean 13.0
processed_messages table	absent	present	present	present
room_membership uses _affiliations	n/a	yes	no	no
room_membership uses per-row key like '%@%'	n/a	no	yes	yes
room_nicknames view exists	absent	yes	no	no
update_group_owners is a RULE	absent	yes	yes (must drop)	absent
update_group_owners is a TRIGGER	absent	no	yes	yes
update_room_nick_jid fires on _data	n/a	no	yes	yes
update_room_nick_jid fires on _affiliations	n/a	yes	no	no

What changed between 0.11.6 and 13.0.6

Under 0.11.6, MUC affiliations were stored as a single JSON blob in one row (key='_affiliations', value = JSON object of JID → affiliation). Under 13.0.6, each affiliation is a separate row (key=<bare-jid>, value=<affiliation>). The _occupants key (used for live occupant/nickname tracking) no longer exists in the DB.

This affected:

• room_membership view — rewritten to read per-row affiliations instead of jsonb_object_keys.
• room_nicknames view — dropped (depended on _occupants).
• update_room_nick_jid_map rule — dropped; replaced by a trigger on the _data row that rebuilds room_nick_jid_map from affiliations.
• update_group_owners rule — replaced by a trigger (the old rule conflicted with the upsert_group_owners INSTEAD rule).