Since the in-game wiki keeps the full explanation of the tarot cards a mystery (for a good and fun reason, I approve), I chose to start working on a full rundown of them myself. The tarot cards are sorted from lowest to highest Luck value. Depending on the card you draw, the card sets your Luck from a range of 1 to 12, which influences the Lookout and Alicorn events, and may have other effects behind the scenes.

The information below has been majorly discovered by PachiLord who has since become the contributor to the guide. Huge kudos to them for their work! Their sources of information were based on discussions from the community, information from the Fandom wiki and from PachiLord's own observations in games with and without cheats.

Once the Full Investigation has started, drawing the Death card has a chance to increase your Sus by 1 or 2 points.
If at least half the town is dead and the Disciple is alive, drawing the Death card has a significant chance of making the Disciple turn on you.
If you have a kidnapping victim in your basement, drawing Death has a significant change of allowing them to escape. This can be prevented by torturing them that night, if you are able.
Luck value: 1

As long as the Reporter is active, the Tower will make the Reporter visit your house during the night, if able. This means they will find any remains you did not destroy, or a kidnapping victim if you have one. Drawing multiple Tower cards cannot force the Reporter to visit your house twice within a few days, but you can be visited twice in a few days if one visit was not the result of drawing The Tower.

Luck value: 2

If the gallows have not been completed, drawing this card will reduce the time until completion by one day.

If the gallows are complete, drawing this card has a significant chance to cause a lynch mob.

Luck value: 3

Drawing The Fool will make a random pony besides you lose 1 Sanity point.

Luck value: 4

The Moon will make you to lose 1 Sanity point.

Luck value: 5

The Star allows you to choose any shop interaction without risking Sanity or Sus. It's symbolized by the card fading out in the interaction wheel. It does not guarantee that a pony will accept an invitation.

Luck value: 6

If the Lookout is alive, The Sun card has a good chance to prevent the Lookout from protecting the street they should be watching, although they may still be stationed at a house. This does not mean they will be home. During Night Phase, the town is visibly shrouded in darkness. The next day, the Reporter makes note of this, explaining it as a blackout.

If the Lookout is dead, The Sun will usually increase your Sanity by 1 point.

Luck value: 7

The Lovers tarot card is currently being reworked, and thus has no effect.

Luck value: 8

If the Changeling is alive, The Knave card makes it easier to identify them by changing their eye color in the Roster for that day. The change happens instantly, so it's best you have a screenshot of the whole roster ready in preparation for this card.

If the Changeling Spy is dead, drawing the Knave will decrease a hidden countdown. If the countdown hits zero, the Changeling Drone will visit your house as an event. The event will give you a choice to either let the Drone avenge their spy or stop them from doing so.

Luck value: 9

The Magician causes all of the normal investigative roles (Deputy, Detective, Sheriff) to stay at home for the night.

Luck value: 10

If the Noble has not ascended yet, drawing The Princess will push back their ascension by one day.

If the Noble has ascended, drawing this card card causes the Prince/Princess to stay home for the night, although they will likely be protected by the Guard if the Guard is alive.

Luck value: 11

The Ace of Knives card reduces your Sus by 1 point. It's also the card with the maximum Luck value.

Luck value: 12

The guide will continue to grow as I play the game more and discover its contents. Feel free to share your own discoveries as well!