With the recent releases of DSi exploits like unlaunch, frogminer and Memory Pit it's time to sort the information.
Let's start with the easy things.

DS vs DSi mode

We have to differentiate between two modes of the DSi: the DS mode and the DSi mode. The DS mode mostly has access to the hardware that the original DS has (touchscreen, microphone, 4MB RAM etc) and is used if you launch a DS cartridge with a DSi. It has no way to access anything beyond that. The latter of both modes unleashes the true power of the DSi (WPA secured wifi, 16MB RAM, cameras etc) but is much better secured. Access to the SD card/ mass storage of the DSi is even further restricted. It can only be accessed from specific DSi ware games. That made it really hard to get a convenient way to run unsigned code on the DSi.
Software that runs in DSi mode can be (and most often is) region locked.

ROMs of commercial games

DS ROMs
My recommendation is to buy a DS mode flashcart that works with your DSi system. Personally I suggest the r4i gold 3ds plus by r4ids.cn, make sure to check the URL on the sticker of the card. In addition to that the installation of the flashcart is much simpler.
There are however a few other flashcarts that might be interesting for you:

• Supercard DSTWO (Plus): has a GBA/SNES Emulator and a few other extra features. Doesn't get produced anymore so good luck finding one
• Any 2014+ card from r4isdhc.com (Dual-Core, Gold Pro, RTS Lite etc): very cheap but less game compatibility and less features than the r4i gold 3ds plus. Has a timebomb
• R4i-sdhc.com R4i 3DS RTS: same as r4isdhc.com but more expensive. Has a timebomb

All of the mentioned cards will work with the latest DSi/3DS firmware version. To use it put a MicroSD card with DS roms/ homebrew and the kernel from the website into the flashcart. The cards from r4isdhc.com/r4i-sdhc.com have worse homebrew compatibility.
There is a free option to run NDS ROMs on DSi/3DS: TwiLightMenu++. But many games need manual patches in order to work and it has (in some regards) less features. It's definetly worth it to keep it in the eyes but it might be impractical for your purposes.

DSi Exclusive ROMs (not to be confused with DSi Ware from the DSi Shop)

Warning: Spoilers inside!

First we have to declare the meaning of DSi enhanced and exclusive ROMs.
DSi exclusive ROMs are games that can be played exclusively with a DSi or 3DS. They have a white cartridge and the Nintendo DSi logo on the sticker. Launching them in an original DS/ DS lite results in an error: "For use with Nintendo DSi systems only."
There is no public way to launch backups of these games*.

DSi enhanced ROMs
This part gets really tricky.
DSi enhanced ROMs can be played in DS mode but have additional features (like camera/ WPA support) in DSi mode. Their cartridges look like normal NDS games (at least in Europe) but their serial numbers start with "TWL" instead of "NTR". Some examples are Pokemon Black/ White 1 and 2.
DSi enhanced ROMs can be launched with most DS flashcarts and twilightmenu++ but miss the enhanced functionalities in that case.
Due to problems with dumping the DSi enhanced part of the ROM many dumps are incomplete and miss the DSi enhanced features. However this problem gets better from year to year as many games get rereleased with more complete dumps that contain the missing part.
There is an option to run DSi enhanced ROMs in DSi mode: the CycloDS iEvolution Flashcart. But it is expensive, has many problems (only compatible with about 50% of the Proper dumps, no Wifi capabilities in DSi mode, no SD card/ NAND access etc) and only works up to version 1.4.3 in DSi mode (or with a custom firmware). It's not worth it in most cases.

DSi Ware (from the DSi store)
I suggest to run DSi ware with TWiLightMenu++. More about that one later.

3DS ROMs
You can't play 3DS games on a DSi.

Homebrew Exploits

Running homebrew (mostly free games by independed people like you and me) in DS mode is as easy as to launch it with a DS mode flashcart. Nothing special required. Running your own code in DSi mode is a bit more complicated. All of these exploits work with the latest DSi version.

UGOPWN/ Flipnote Lenny
This exploit uses the flipnote studio to gain access to the system. It was the first way to run homebrew on a DSi with 1.4.5 for free (if you had the flipnote studio). I suggest to use Memory Pit instead.

Failzone
This exploit uses the DSi Nintendo Zone to gain access to the system. It was never released. I suggest to use Memory Pit instead.

Memory Pit
This is probably the easiest launching the homebrew menu in DSi mode will get. Full instructions are at https://gbatemp.net/threads/memory-pit-a-new-dsi-exploit-for-dsi-camera.539432/ (seriously, it's just five steps). You can get the latest hbmenu at https://github.com/devkitPro/nds-hb-menu/releases; use the BOOT.NDS. The exploit needs to be redone each time you exit the homebrew launcher via the power button.
You can use the DS Homebrew Menu to launch homebrew applications from your DSi's SD card in DSi mode. Keep in mind that many (especially older) DS homebrew applications might not work properly because they weren't made for the DSi mode. This is your first step to install a custom firmware on the DSi.

Custom firmwares

Custom firmwares always give you more options for your system. On the DSi these options are among others brick protection, compatibility with old flashcarts (even ones that never worked with a DSi), installing homebrew directly to the DSi menu, playing DSi enhanced and exclusive games from other regions etc.
The next step is to install unlaunch, a custom firmware and bootcode exploit collection. The bootcode exploit allows you to exploit the system directly while the DSi boots so that you don't have to press anything to launch a custom firmware. I'd suggest to use the hiya custom firmware.
There is a good guide to install a CFW (first unlaunch, then hiya and twilightmenu++) on a DSi already: https://dsi.cfw.guide/installing-unlaunch . However I wouldn't replace the DSi menu with twilightmenu++ as described at the end of the guide.

Homebrew

Finally a few homebrew apps that you can use in DSi mode:

• NTR Launcher: allows you to launch flashcarts that don't have an actual game as header (pre DSi cards)
• Wood Dumper DSi: can be used to dump DS games to the DSi's SD card
• Twilightmenu++: menu/ ROM launcher which includes GBARunner2 among other features.
• NDSi-Savedumper: dumps save games from cartridges. Needs to be launched with Twilightmenu++
• NitroHax: cheat menu for cartridges
• Title Manager for HiyaCFW (TMFH) : for Installing DSiWare and homebrew onto your EmuNand home menu.
• GodMode9i : file manager for the DSi where you can browse files, including deleting, copying, and moving files.

The famous moonshell media player doesn't work in DSi mode. You can use the RocketVideoPlayer (https://github.com/RocketRobz/RocketVideoPlayer/releases/) instead but I didn't test it.
I made a (not much used) spreadsheet for DSi Mode Homebrew Compatibility a while back. Feel free to look if you find anything that you want to play or add games you tested: https://docs.google.com/spreadsheets/d/1x7Z2rz3XObOCLIZOX9vbT0PDKFkKIxkkwyaTP3dRYKw/edit?usp=sharing

GBA

you can also play GBA games with gbarunner2 on a hacked DSi.

• GBARunner2

*Feel free to post corrections/ additions etc