Picard is critically injured in an attack during a diplomatic mission, where he (apparently) dies on the operating table because, in part, he has an artificial heart. He finds himself in a white expanse where he is greeted by Q, who informs him wryly, in what Picard can only comprehend as a cosmic joke: "You're dead, this is the afterlife, and I'm God."
Q explains to Picard that his death might have been avoided had he had a real heart. Picard, of course, had that transplant as a result of being stabbed by a Nausicaan in a bar fight when he was a just-graduated ensign, a piece of backstory established in a small subplot way back in second season's "Samaritan Snare." Picard has numerous regrets with how he behaved as a young man, so Q gives him the opportunity to go back in time and live those crucial days over again and perhaps change the course of his life. (Naturally, Picard objects over the possibility that changing the past could have severe consequence on the future, so Q promises that any changes to the timeline will affect Picard alone.)
"Tapestry," like a lot of good stories, takes a simple premise and executes it straightforwardly. It twists It's a Wonderful Life around, while allowing Picard to rewrite his own origin story (and, yes, you might as well call the run-in with the Nausicaans the Picard origin story, given the significance it ends up having). The story wisely and crucially casts Patrick Stewart as the 21-year-old version of himself rather than going with a younger actor, which is a key decision for the story's impact (so key, indeed, that it was honestly the only viable option and thus shouldn't be seen as having had an alternative). The point here is that the older intellect of Picard has gone back to his youth with the benefit of perspective (though that perspective ends up being a liability instead of a benefit).
The story takes us back to a revenge plot involving Picard's friends, Cory (Ned Vaughn) and Marta (J.C. Brandy), who were cheated by the Nausicaans in a billiards-like gambling game. Instead of leading the charge in the revenge plot, however, Picard this time does everything he can to stop it, since that's what set the dominoes in motion for the fight and his nearly fatal injury. Meanwhile, Picard also realizes he has a do-over opportunity with Marta, who was a close friend but also stands in his mind as another regret because they weren't more than just friends.
Throughout all this is Q, who provides a running commentary on everything Picard once upon a time did and now attempts here to undo. John de Lancie and Patrick Stewart have perhaps never been so perfectly in sync as they are here, which is not surprising, since the stakes are so personally focused on Picard's character. Q's sardonic edge is in fine form, and their dialogue is both thoughtful and funny, even in its broader moments, as when Q poses as a florist ("Flowers! Is there a John Luck Pickerd here?").
The funny thing about do-overs, though, is that they don't necessarily lead to the outcomes you expect, even if you are able to successfully pull them off. Picard is able to parlay his friendship with Marta into romance, but finds the next day that in her mind it has only wrecked their friendship. Meanwhile, Picard has to completely betray Cory as a friend to stop him from starting the fight with the Nausicaans. And when Picard is able to stop the fight that got him stabbed and thus save himself from the injury that almost killed him...
Picard is whisked back to an alternate version of the present, where he finds he is alive, but is now a lowly lieutenant (junior grade!) who has lived a life and career of safe choices, nonexistent ambition, and unfulfilled goals. When he asks Riker and Troi to assess him as an officer, their praise, while sincere, is almost painful to hear. After letting Picard stew for a moment, Q tells him that not having his youthful brush with death made him a fundamentally different man who didn't take the risks that would've made his career, because he didn't view life as nearly fragile and finite. I guess that's the trouble with changing the past; you might just end up unraveling the tapestry of your life when you least expect it.
"Tapestry" is an essential Picard story. But I do have one problem with it, which is that is posits a lesson that seems sort of ... well, obvious. The message is that sometimes we can't see the forest for the trees. But Picard to me has always seemed like someone with the wisdom to know that who he once was — even if it was a guy he doesn't much respect now — made him what he is today. "Tapestry" tells a story that, in a way, reveals exactly the opposite of that notion. I suppose that's the point, and I guess if you're dead, you might reach into the past to see if you could take a different fork in the road. But this is a story that seems to regard an obvious lesson as a revelation that required Q, of all people, to teach Picard. Don't get me wrong; I like that Q is a teacher here. But this lesson is one Picard should've seen coming.