Welcome to 904 N. Water Street! This magnificent riverfront home along the Allegheny River has been completely transformed from top to bottom, including a stunning new addition. From the moment you step inside, you'll be impressed by the exceptional craftsmanship, luxury finishes, and thoughtful details found throughout every inch of this remarkable property. The chef's kitchen is truly a dream, featuring quartz countertops, solid maple cabinetry, a ceramic farmhouse sink with garbage disposal, Bosch ultra-quiet dishwasher, built-in 48" stainless steel refrigerator/freezer with topside compressor, ice maker and water filtration system, and an impressive 60" six-burner gas range with double convection ovens and professional-grade stainless steel exhaust. The spacious dining room provides the perfect setting for hosting holiday gatherings and dinner parties, while the breathtaking living room showcases soaring cathedral ceilings, LED lighting, and a custom built-in entertainment center. The main-floor primary suite offers a luxurious private retreat with cathedral ceilings, an expansive walk-in closet, and a spa-inspired ensuite bath featuring an oversized dual shower with rain shower head, full-length jetted soaking tub, dual vanities, and a Bidet King self-cleaning bidet and commode. A convenient main-floor laundry room with Electrolux washer and dryer completes the main floor. Elegant hickory stairs lead to the second level where you'll find two beautifully appointed guest suites. Designed with comfort and privacy in mind, each suite features generous bedroom space, ample closet storage, and its own fully updated ensuite bathroom. These private retreats provide the perfect accommodations for family and guests, offering hotel-like comfort and convenience while maintaining a warm and inviting atmosphere. Large windows allow for abundant natural light, creating bright and welcoming spaces throughout. The finished lower level was designed for entertaining on a grand scale. The spacious game room showcases a stunning stone accent wall from the original home, blending historic character with modern luxury. The custom-built bar is a true centerpiece, complete with a full second kitchen, granite countertops, custom bar top, wine cooler, large humidor, cappuccino and coffee maker, and plenty of seating space for gatherings of any size. Whether hosting game-day parties, holiday celebrations, or casual get-togethers, this incredible entertainment area is sure to impress. The lower level also includes a full bathroom and a private sauna featuring a Havara stove, creating the perfect place to relax and unwind after a long day. With ample space for recreation, media viewing, and entertaining, this level adds tremendous versatility and value to the home. Comfort is ensured year-round with an in-floor Warmboard heating system powered by a high-efficiency boiler with four separate heating zones serving the main-level addition, primary bath, game room floor, and garage. The heated two-car garage is pre-wired for an electric vehicle charging station. Outside, the attention to detail continues with stamped concrete driveways and sidewalks featuring inlaid dusk-to-dawn low-voltage lighting. The gated driveway, custom garage doors with HomeLink remote operation, 2Krew long-range security cameras, Ring camera system, and fully fenced yard provide both convenience and peace of mind. A 30-foot bronze-finished stainless steel flagpole, walk-up attic, 10x16 storage shed, and abundant closet space offer even more functionality. As an added bonus, all furnishings, six wall-mounted flat-screen televisions, and yard maintenance equipment—including a Cub Cadet zero-turn mower, snow blower, pressure washer, and Honda generator—are included at the asking price, making this an extraordinary turnkey opportunity. Luxury, comfort, security, and riverfront living come together seamlessly in this one-of-a-kind property.