While my first inclination would be to recommend the Marina district, there are actually very few places in that neighborhood right on the water - save for the homes you see along Marina Blvd. I would try looking at some of the new(er) buildings in South Beach and Mission Bay - try Portside or Madrone.
If you are comfortable being near a marina, then yes, the Marina district would be ideal. While it isn't in San Francisco, the town of Sausalito is very close by in Marin county and there is a whole neighborhood of houseboats (er, floating homes) located there.
Better Homes & Gardens/Mason-McDuffie Real Estate
2200 Union Street
San Francisco, CA 94123