I've been musing about stuff like this here: Thinking through the design of a TTL video card: what memory chips and how to manage memory? and the people didn't like it. I never get the value of shutting down explorative discussions. Ah well.
I think you need to consider the clock speed your system will run on. If you go for 640x480 you clock at 25.175 MHz as you said, and that isn't a speed that the 6502 is normally run on, the W65C02S can do 14 MHz.
If you really want to run the CPU in 1 MHz, your video circuit should make the clock signal for the CPU and give it whatever is left over. But I figure if you want raster line interrupts and light pen strobe interrupts, you want to do better than run the CPU in the video blank phases.
I think the constraints and math goes like this: your video card sets the beat, since it cannot wait and keep in sync. So the video card in any way generates the clock for the CPU. That's settled.
The video card needs a lot of bandwidth from RAM. In monochrome mode that would be access for one byte every 8 pixels. 3.14 MHz, 318 ns. Double that for CPU to get in on the interleaved cycles and you will need 150 ns RAM. That is what I have read these DRAMS could do squeezing the refresh in as well. I could be wrong on the exact numbers.
For a 4 color palette mode with palette changeable for each 8x8 pixel block (e.g. C64 VIC-II style) you need 2 bits per pixel, so your run on the RAM doubles down. This is why the C64 could not do more than 320x200.
If you wanted to run everything at full blast in 25.175 MHz with one byte per pixel, and the CPU in interleaving cycles, you would need the RAM to squeeze into 19 ns cycles. Is there any SRAM chip that can do that? I doubt it.
I have my eyes on the HM628512P-5 with 512k x 8bit at 55 ns access time. No way to come even close!
This is where my thinking has moved to use dedicated video memory and do it in parallel. My favorite would be a 1Mx4 bit chip, and I would use 3 in parallel for a 12 bit color space. But even then, at 640x480 I would need it to respond in 39.8 ns. Which is still too fast! So perhaps a 1M x 1 bit organization and I would use 9 in parallel to have 3 bits for each color channel, MCM6227 or CY7C107D perhaps? That CY can do 10 ns. With that we could even do wider. But I would really like to stick to DIP packages, not surface mount.
What I would like is an exhaustive list of all SRAMs that are still available that are DIP packages and I would find my size and speed.
As for all those other "modes" they are just variations on the theme how you end up with your RGB values to send to the monitor. If you get one byte for an 80 character wide text mode display, your memory bandwidth goes down to 39.25 kHz. The bits you get from the CHAR-ROM, and then the bits to pixels using whatever color palettes from RAM or internal registers.
Now have you thought about the design of your sprite overlays already? With mask (alpha channel?) and pixels? I have, would need an adder to add the offset and then for each sprite read the memory or internal registers for XOR mask OR pixels on the fly to determine the final RGB value. This lead me to really appreciate what the VIC-II was all doing at the same time, and makes me think even more about having separate video RAM instead of using the regular CPU's memory.
By the way, did you think about how to interface the video RAM with the CPU? I would hate to write through a little port setting pixel address and pixel value one by one. I am thinking of mapping the VRAM into the CPU's address space, write to it, and then un-map it to continue working with the normal RAM underneath.
And to complete the brainstorming here, in my hunt for RAM chips, I also saw dual-port RAM. They went up to 8k x 8bit, and have two sets of separate A and D pins. I don't remember the speed.