Talking to SD card *adapter* from FPGA
In particular, I wonder what do the adapters do besides exposing the pads of SD cards to jumpers?

The adapter in the pictures also does the 5V-&gt;3.3V level translation for 5V arduino.
Which is probably not what you want on an FPGA that supports 3.3V I/O directly.

can I just treat the adapted SD card like an SPI flash, without worrying about all the nasty details of the SD card specification

Nope! SD cards power up in SD mode, and must be actively switched into SPI mode (using CMD0). With multiple devices on the SPI bus, you would need to initialize the SD card first before you can treat it as an SPI slave.

Which mode should I use with an adapter, then?

SPI, since the faster SD modes would probably be above the HF frequency limits of your adapter cables.
Note that you could simply &quot;wire&quot; an SD card holder to your FPGA directly, and this could enable you to run at higher SD speed due to a better and shorter signal path.