I'm curious about the limitations of modding a game. I know that things like swapping assets and changing up values can be done. I know that whole levels can be made from pre-existing assets. But can that go farther? Can you, say, add online functionality to games that never had it? Can you add support for the Game Boy Advance in some way? Maybe allow reading from an SD card reader? I have some ideas for projects, but I don't want to bother fantasizing about it if it's simply impossible. I know from the hardware side that it should, but what do you think?
I would classify some of those examples of hypotheticals as easier than some things I have seen done by swapping assets and changing up values.
They also vary massively. Adding a basic online high score board. Trivial really, could probably walk in knowing my general ROM hacking skills and have it done... midnight now so might just be getting light.
Adding local co-op multiplayer and having it work on laggy internet...
https://www.gamasutra.com/view/feature/3374/the_internet_sucks_or_what_i_.php is very old (some 22 years or so at this point) but just as relevant today as it was back then and will continue to be until... physics as we know it takes a break.
GBA support. Coding in a new game it speaks to in link mode... hard. Coding in some kind of controller, map and item screen (think Final Fantasy Crystal Chronicles)... far easier.
SD reader. The GC memory ports already have adapters, coding something to pull a file from it, maybe write a save. Back to that just as it is getting light thing really. Some kind of complicated interplay with a nice database on the SD card... bit harder if I also have to understand the game well enough to do it.
Anyway I have tried to categorise difficulties in different styles of mods for the layman before and don't think I have ever really succeeded. Most ROM hackers will be able to categorise a project in very short order, better ones maybe even having some system specific knowledge*, but as far as teaching it... that is a bit harder.
Your limitations are really what the system is theoretically capable of (as a general rule if a game/homebrew does it then go with that, you can however spend time contemplating the limits of the hardware if you want like you are the demoscene/gamecube equivalent of
http://www.catb.org/jargon/html/story-of-mel.html and may get further still), I will skip the what emulators allow that might not work on real hardware for now (there are those doing high res texture replacement though). The less you have to change the game the easier it gets, barring the devs having done something to your target game that makes it hard for whatever reason -- compression, odd coding styles, already brushing up against processing limits of the system, used all the space available on the disc), for instance you can change tetris to be a MMORPG with visual novel elements if you like but you will be in for the long haul (and anybody sensible would say make it as a homebrew game instead).
So yeah
1) Working within the parameters established by the game and probably being light on any assembly work.
2) Working within the general bounds of a game.
Edges of 1) and 2) get very blurry as devs do things like compression or complicated coding setups that punt it either way, or some superfans make tools to allow people to do hacks (changing say mario kart without knowing the model format, collisions, nature of the AI navigation, animations, pickup placement and more is going to be a long one, if someone learns all that and codes up a 3d model viewer/editor then you might reduce it to "knows how to 3d model edit and use object placer like anybody that has ever played an RTS game).
3) You start colouring way outside the lines or massively changing functionality.
*on the DS the vast majority of games will use the SDAT sound format. This then means various things you can do with it that would be nightmare hacks on the NES or SNES where devs could and would write their own custom engines from the ground on up. To that end I can say things like replacing audio, unless it does not use SDAT but even then you still stand a reasonable chance, is likely easy.