Basically, is the melee attack roll involved in the Tentacle Rod's description a weapon attack roll or a spell attack roll?

I'm tempted to say spell because rods are not weapons, and it's done using a Magic Action.

What do you think?

I wrote and removed two different things. Let's try again.

I have two different options/opinions.

Option A
(EDIT: I prefer this option, based on the legacy Tentacle Rod)

I think, by RAW, it's none of them, or "a tentacle melee attack".

It's similar to the new 2025 MM, where it's not defined whether a monster's attacks are weapon attacks or spell attacks (you can try to guess based on the action's name).

For this magic item, it's just a melee attack with a +9 bonus.

Option B
(EDIT: personally, I think this option isn't the right one because, for it to be a spell attack, it should be explicitly stated)

It could be a spell attack roll for the same reasons you already gave, and also because it's a Magic Item, so:

Magical Effect
An effect is magical if it is created by a spell, a magic item, or a phenomenon that a rule labels as magical.

And:

Spell Attack
A spell attack is an attack roll made as part of a spell or another magical effect. See also chapter 7 (“Casting Spells”).