Method createAuditTriggers

src/utils/postgresDatabase.js:433–495

()

    }

    async createAuditTriggers() {
        try {
            const functionQuery = `
                CREATE OR REPLACE FUNCTION update_updated_at_column()
                RETURNS TRIGGER AS $$
                BEGIN
                    NEW.updated_at = CURRENT_TIMESTAMP;
                    RETURN NEW;
                END;
                $$ language 'plpgsql';
            `;
            
            await this.pool.query(functionQuery);
            
            const triggers = [
                { name: 'update_guilds_updated_at', table: pgConfig.tables.guilds },
                { name: 'update_users_updated_at', table: pgConfig.tables.users },
                { name: 'update_welcome_configs_updated_at', table: pgConfig.tables.welcome_configs },
                { name: 'update_leveling_configs_updated_at', table: pgConfig.tables.leveling_configs },
                { name: 'update_user_levels_updated_at', table: pgConfig.tables.user_levels },
                { name: 'update_economy_updated_at', table: pgConfig.tables.economy },
                { name: 'update_application_roles_updated_at', table: pgConfig.tables.application_roles },
                { name: 'update_invite_tracking_updated_at', table: pgConfig.tables.invite_tracking },
                { name: 'update_guild_users_updated_at', table: pgConfig.tables.guild_users },
                { name: 'update_birthdays_updated_at', table: pgConfig.tables.birthdays },
                { name: 'update_giveaways_updated_at', table: pgConfig.tables.giveaways },
                { name: 'update_tickets_updated_at', table: pgConfig.tables.tickets },
                { name: 'update_afk_status_updated_at', table: pgConfig.tables.afk_status },
            ];

            const allowedTriggerIdentifiers = new Set(triggers.map(trigger => trigger.name));

            for (const trigger of triggers) {
                try {
                    const safeTriggerIdentifier = assertAllowlistedIdentifier(
                        trigger.name,
                        allowedTriggerIdentifiers,
                        'Trigger identifier'
                    );
                    const safeTableIdentifier = assertAllowlistedIdentifier(
                        trigger.table,
                        this.allowedTableIdentifiers,
                        'Trigger table identifier'
                    );

                    await this.pool.query(
                        `DROP TRIGGER IF EXISTS ${quoteIdentifier(safeTriggerIdentifier)} ON ${quoteIdentifier(safeTableIdentifier)};`
                    );
                    await this.pool.query(
                        `CREATE TRIGGER ${quoteIdentifier(safeTriggerIdentifier)}
                         BEFORE UPDATE ON ${quoteIdentifier(safeTableIdentifier)}
                         FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();`
                    );
                } catch (error) {
                    logger.warn(`Error creating trigger ${trigger.name} on ${trigger.table}: ${error.message}`);
                }
            }