This page contains information regarding Star Trek: Picard, and thus may contain spoilers.
Rios took ownership of La Sirena some time after his departure from Starfleet in 2390 or 2391. He initialized the ship's five Emergency Holograms through the self-scan option, then later deleted a number of memories they inherited from him, disrupting some of their normal functionality in the process. (PIC: "Broken Pieces")
In 2399, retired admiral Jean-Luc Picard hired Rios and La Sirena for his mission to find Dr. Bruce Maddox and, later, Dr. Soji Asha. Raffi Musiker, who had recommended Rios's services to Picard, decided to hitch a ride on the ship, since they were all headed to Freecloud. Dr. Agnes Jurati unexpectedly asked Picard to join his mission, and came along when he beamed up from Earth. (PIC: "The End is the Beginning")
On the way to Freecloud, Elnor and Seven of Nine became temporary passengers on the ship, though they both soon left to go their own ways. (PIC: "Absolute Candor", "Stardust City Rag", "Nepenthe") After finding Soji, she convinced Rios and Picard to take her to her homeworld, Coppelius, which required flying through a transwarp conduit. (PIC: "Broken Pieces")
Upon reaching Coppelius, La Sirena was enveloped by an orchid, a weapon created by the synths living on the planet, which drained all its power. Any protective effect the orchid might have had was knocked out when the Artifact, piloted by Seven of Nine, caused a shockwave upon entering the atmosphere, so La Sirena crashed onto the surface of the planet. (PIC: "Et in Arcadia Ego, Part 1")
Initially, Rios was unable to restore power, since the ship's intermix reactor was fused. Unfusing it was impossible, as was replacing it, since the power outage meant the maintenance replicator was offline as well. However, the synths of Coppelius Station gave Raffi Musiker a fundamental field replicator, which she and Rios used to repair the intermix reactor and restore power to the ship.
The next day, after Rios, Musiker, and Narek and Elnor, who had joined them, returned to Coppelius Station, Picard and Jurati came on board the ship, looking for the rest of their crew. When they discovered the Romulan Fleet, led by the Zhat Vash operative General Oh, was about to attack Coppelius, Picard piloted La Sirena into the battle. Jurati used the synth replicator to create the illusion of many copies of La Sirena dropping out of warp and engaging the Romulans. (PIC: "Et in Arcadia Ego, Part 2")
After the end of Picard's mission and their time on Coppelius, Rios set out from the planet on La Sirena, joined by Jurati, Elnor, Musiker, Seven of Nine, Soji, and Picard.
Following Rios' return to Starfleet, ownership of the ship passed to Seven of Nine for use in helping others through her work with the Fenris Rangers. In tribute, Rios kept a gold-plated model of the La Sirena on display in the observation lounge aboard the USS Stargazer.
During her time as captain, Seven of Nine merged the ship's five Emergency Holograms into one and neglected (at least) cosmetic repairs in favour of her humanitarian work. Upon viewing La Sirena again for the first time in 2401, Rios was displeased by the battered condition of the ship, but Seven commented that "a few dents look good on her." (PIC: "The Star Gazer")
In 2401, pirates boarded La Sirena trying to steal medical supplies which Seven of Nine was transporting. The crates of supplies, property of the Fenris Rangers, bore insignia of Starfleet Medical and the Mariposas Medical Movement, probably indicatin who had provided them to the Rangers. When the pirates were unwilling to negotiate, Seven neutralized them With the help of Emmet, the Emergency Tactical Hologram.
The main body of the ship was comprised of two decks. The upper deck housed the bridge, living quarters, and a holodeck. The lower deck held the mess and sickbay. Cargo was stored around the open spaces of both decks.
La Sirena was equipped with a transporter, a holomatrix, and an Emergency Hologram basic installation. The dining area had a large replicator, though the ship could replicate items elsewhere, such as on the tactical console on the bridge.
In addition to a number of physical consoles and touch screens, La Sirena was mainly operated through holographic controls which were mainly found on the bridge and in sickbay, but occasionally even on the support struts along the upper deck. Significantly, the piloting controls surrounding the captain's chair were entirely holographic.
During Rios's time as captain, La Sirena's distinct logo in the shape of a mermaid and cresting wave was in frequent use around the ship. It was printed on the mugs in the mess (though not the ones produced by the replicator) and on every cargo container. Early in his travels with Picard, Rios refitted the bridge chairs and added the ship's insignia to their headrests. (PIC: "The End is the Beginning", "Absolute Candor")
|La Sirena emergency holograms|
|Emil • Emmet • Enoch • Ian • Mister Hospitality|
|Kaplan F17 starships|
|SS La Sirena|
|CSS Ahuizotl • CSS Cetus • CSS Charybdis • CSS Hydra • CSS Kappa • CSS La Sirena • CSS Makara • CSS Scylla • CSS Selkie • CSS Zin Kibaru|
The La Sirena studio model was designed by Mark Yang. The name is Spanish for "the mermaid" or "the siren" and was originally named Arana Cosmica (a slightly mistyped version of the Spanish "araña cósmica", "cosmic spider"). 
The La Sirena set was designed by production designer Todd Cherniawsky. Cherniawsky noted that La Sirena was a non-Federation ship that had become "retrofit[ted] with Federation technology" over time, the holodeck and staterooms in particular. Cherniawsky also suggested the weapons rack had "accumulated a variety of weapons that probably date back as many as 150 years." ("La Sirena Set Tour", TRR: "Away Mission Special")
In a story from his Instagram account, showrunner Michael Chabon said that La Sirena was manufactured at the Kaplan Schiffbau on the Hotzenplotz colony world. It had a dedication plaque; however, the plaque was missing by the time Rios acquired the ship. 
Yang produced a number of exterior paint schemes for La Sirena, in which Cherniawsky eventually picked red. Yang later noted "for some reason, red just jumped out at him, so we gave it a very striking paint job."  WhatCulture.com noted the design closely resembles Eddie Van Halen's Frankenstrat guitar. 
According to Issue #1 of the Star Trek Universe: The Official Starships Collection, La Sirena was launched in the 23rd century. It initially had an impulse engine in the back, but this was blocked by a later edition of a towing coupling. Since the impulse engines could not function when the ship towed cargo like a tug, additional impulse engines were added on the side of the nacelles.
A tweet from Star Trek on Paramount+ called the ship "SS La Sirena" and gave it the registry number NAR-93131, matching the CSS La Sirena's NCC registry.  While this might indicate the ship will be registered in season 3, as of the end of season 2, there had been no confirmation that the ship's unregistered status had changed, nor that the name and registry listed in the tweet would be made canon.