This app allowed you to create your own 'Choose your own adventure story'. After writing your story, you could publish it for anyone to read. You could rate other peoples stories as well as follow them and other Social Media features. You could search and filter by Popular, Recent, and Categories of stories.
Challenges
The tree view was very difficult for me to build. I actually built the whole app in React Native originally, but the SVG plugin for React native was too slow at the time. So I shifted the whole application to Capacitor and pure React so that I could use all of the native web SVG features and it was lighting fast.
SQL or NoSQL?
One of the benefits of not having a deadline for my own apps is I can complexly re-do features if I don't like the way I implemented them. I originally wrote the app using SQL, but I noticed the complex tree view was very complex in SQL. I realized that all that complexity was just breaking the tree view apart and storing it in SQL tables, then my queries just re-assembled them. Basically I was just saving a file without any relational logic, so why am I using SQL? So I ripped out my whole backend and wrote it with Google Firestore NoSQL. The tree view went from ridiculously complex to super easy.
But the other areas of the app were Social media like, and so by nature, very relational. They began to struggle quickly with the NoSql approach. Once again I restarted my backend code, this time doing a mixed approach. Just the tree logic was in NoSql, then everything else was in Sql. The project took much longer than it should have because I basically built it three separate times. But the end result was extremely fast, saleable, and queryable.
Fuzzy Search
I wanted to improve my SQL so I decided to write all my SQL in raw SQL. I didn't use any search providers like Algolia, but I still wanted Fuzzy search (if a user mispells a word or writes a similar word it should still work).
After doing a lot of research, I built my search algorithm around the SQL SOUNDEX() feature. This allowed me to search based on the phonetic sound of a word, not the actual letters. I then created my own ranking system for search, weighting things like the title and author name higher than description and so on. The result was a long but powerful SQL stored proccedure that worked well. A lot more work than using Algolia or Azure Search, but I learned a lot!