For the german Youtube series “7 vs. Wild” I wanted to build a community bingo at sevenvsbingo.de. Before the series started, the community was asked to submit their ideas for the bingo cards and were able to create their own bingo cards using the submitted ideas. Each participant earned points based on the number of bingos and difficulty of the choosen Bingo card. You can check out the final result here and the winner board here.
In total the site had:
- 1143 People who created a account using discord or twitch
- 820 People who created a bingo card
- 108 Bingo Cards created by the community
- Around 150 - 300 unique visitors per day
- Average visit duration of around 3 min
- Over 10k page views
Table of contents
Open Table of contents
T3 Stack
The main website was built using the T3 Stack. The t3 stack was popularized by Theo and typesafe way to build fullstack web-applications
The stack uses the following technologies:
- Nextjs as the main framework with react and typescript
- Tailwindcss for good looking and fast styling
- tRPC for the frontend to backend communication with awesome dx
- prisma to connect and communicate with the database
- and next-auth for authentication
Using the t3 stack was an absolut blast. Thanks to tRPC building API endpoints was easy. To create a new endpoint I only had to follow the already generated structure form the create-t3-app and add a new entry to the router. Then I could simply query the database from the frontend:
//example router used to query the top 10 players with a bingo board
export const pointsRouter = createRouter()
.query("highscore", {
async resolve({ ctx }) {
return await ctx.prisma.score.findMany({
orderBy: {
score: "desc",
},
take: 10,
});
},
})//frontend code to query the code
const Board: React.FC = () => {
const { data: entries } = trpc.useQuery(["highscore"]);
return (
<div className="w-48 text-sm font-medium rounded-lg border bg-gray-700 border-gray-600 text-white">
<div
className="py-2 px-4 w-full font-medium text-left text-white rounded-t-lg border-b cursor-pointer focus:outline-none bg-gray-800 border-gray-600"
>
Highscore
</div>
{entries?.map((entry, index) => (
<div
key={entry.id}
className="block py-2 px-4 w-full rounded-b-lg cursor-pointer focus:outline-none focus:ring-2 border-gray-600 hover:bg-gray-600 hover:text-white focus:ring-gray-500 focus:text-white"
>
{index + 1}. {entry.userName} - {entry.score}
</div>
))}
</div>
);
};
export default Board;Also using prisma to query the database was a breeze. I only had to create a new model and add the fields I wanted to store in the database. Then I could use the generated prisma client to query the database completely type safe. For example this is the model used for the player score table seen above:
model Score {
id String @id @default(cuid())
userId String @unique
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
userName String
score Int @default(0)
position Int @default(0)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}tRPC directly integrates react-query which is awesome because it makes caching and invalidation of data very easy. Also you get a lot of nice features like pagination, optimistic updates and more out of the box. The application feels a lot more responsive because of this.
For the /points and /cards page I used the getStaticProps function from Nextjs to pre-render the pages. This way the page loads instantly and the data is always up to date.
I also used next-auth to handle the authentication. This provided example generated by the create-t3-app was a great starting point. I only had to add the discord and twitch authentication.
Hosting
Website
Instead of using vercel to host the application I decided to use a self hosted server and try out coolify.
Coolify is an open-source & self-hostable Heroku / Netlify alternative. It connects to your git repository and automatically deploys your application. It also takes care of the SSL certificates and provides a nice dashboard to manage your applications.
Database
I always wanted to try planet scale and this was the perfect opportunity. Planet scale is a database as a service that provides a mysql compatible database and advertises a lot of nice features like automatic failover, replication and more. It manages the database simlar like a git repository. You can create branches of table changes and merge them to add the changes to the main branch. This makes it easy to test changes without breaking the main branches and interrupting the service.
In my experience the database was very fast and in combination with prisma the pages feel really snappy. My use case was not very demanding but I would definitely use it again for a bigger project. Sadly planet scale had one outage on my replication server which caused the website to be down for a few hours. After reaching out to the support the issue was resolved quickly.
I started with the free plan on planet scale and upgraded to the paid plan after my usage crossed the 1 billion rows read limit.
.
I didn’t implement any caching in the t3 stack which would have helped a lot with the read limit.
When viewing the /points or /board page the application would query the database for every single player. This is not very efficiently and if I would do it again I would implement some caching to reduce read operation.
Cloudflare
I used cloudflare to manage the DNS and provide basic DDOS protection. Cloudflare also provided basic caching of static assets.
Analytics
For analitics i used plausible. Plausible is a privacy friendly analytics tool that does not track users. It only tracks page views and does not use cookies. I self-hosted plausible on my local k3s cluster.
Most of the page views correlated to the release of new episodes of 7 vs. Wild. In the first week we did some promotion on twitter and reached a lot of users:
Conclusion
I really loved working with the t3 stack. The template provides a great starting point. Next time I would definitely implement some caching to reduce the read operation of the database. Nextjs 13 provides a lot of awesome new features to make these kind of operations a lot easier and i can’t wait to see how the t3 stack will evolve in the future to take advantage of these new features. Also using planet-scale was a great experience, but maybe a bit overkill for my use case. Next time would probably stick to using sqlite which is plenty fast for most use cases.