Building Startup (Pocketbook) with KMM [3/n] — Database and System Design
Greetings and welcome back to our startup building series with KMM! In our last blog, we talked about the initial ideation and MVP design of Pocketbook, our revolutionary finance and budgeting app. Now, it’s time to delve into the technical aspects and explore the backend system design in detail. In this blog, I’ll take you through the UML diagrams and microservices architecture that we’ll use to bring Pocketbook to fruition. Get ready to join us on this exhilarating journey, and let’s kick things off!
The ChatGPT only had limited use in this case, but most of its properties were very generic. It is not much autonomus as I expected. Not something out of the box. Though it was really useful during my research. Though the name
PocketBookgiven by ChatGPT.
Simple System Design
Here is what I have an overall idea of the system. I am trying to over-engineer everything, so it is a learning opportunity for me, the comprehensive end-to-end flow.
The Clients hit the API Gateway, which will discover the service and pass it to that service. We also have a caching layer with Redis Datastore. Then we have to follow a microservice pattern based on the domain they are divided into. Now for the database part, I will be using Postgres and Elasticsearch using Postgres for all the POST/PATCH/DELETE requests. Since this app is expected to have many graphs, so querying data at a large scale would be an issue, so we will be syncing data with Elasticsearch. This will help us to make our queries (for graphs) and text searches faster. We will be later some other use cases where Elasticsearch will be beneficial. We are using gRPC for inter-service and overall communication to have a more unified and resilient contact, smaller payload size and faster functionality over REST.
Some References for what I did and why I did
Postgres Database Design
The next step was to plan a basic database schema. So It took me a while to decide what to consider and want not to do. Initially, I intended to make a Database per service; however, it was really complicated stuff that made it easier for me to make a monolithic database to handle all the foreign keys and cascading things for the MVP.
Trying to reduce the monolithic database by design, making sure that only a specific service can access the specific table so that it is easier to separate in the long run.
You check the DB schema and commands here: https://dbdiagram.io/d/63a9b6ea7d39e42284e791dd
I added Postgres Indexes and tried to normalize the tables as much as possible, it follows the basics of designing the database. Have a look at the detailed explanation of the design. Do comment if you have some views.
Will be using a managed service, for now, PlanetScale really great free tier. For the authentication layer, I will be using Auth0 and some managed service for the Redis Layer from Upstash (Not Sure presently).
Elasticsearch Design
Keeping this section simple, I will be doing one-to-one mapping tables in Postgres with Elasticsearch. Have a look at this article it is really great, have a look at why I jumped into Elasticsearch.
Here is an example of how you might create a mapping for the users table from your database schema:
PUT users
{
"mappings": {
"properties": {
"id": {
"type": "integer"
},
"email": {
"type": "text"
},
"password_hash": {
"type": "text"
},
"first_name": {
"type": "text"
},
"last_name": {
"type": "text"
},
"date_of_birth": {
"type": "date"
},
"gender": {
"type": "keyword"
},
"phone": {
"type": "text"
},
"profile_picture_url": {
"type": "text"
},
"created_at": {
"type": "date"
},
"updated_at": {
"type": "date"
}
}
}
}You can repeat this process for each of the other tables in your database schema, defining the fields and their data types in the Elasticsearch mapping as needed.
It’s worth noting that you should choose data types for your Elasticsearch mapping based on the types of queries you will be running against your data. For example, if you want to be able to search for exact values in a field, you might want to use the keyword data type, while if you want to be able to perform full-text searches on a field, you might want to use the text data type.
I’ve covered the essential steps of basic system design and database structure in creating an MVP. In the next blog, we’ll continue our journey by diving into the development of our MVP. Who knows where this journey will take us? Stay tuned as we turn our side project into a potentially successful startup (or at least have a bit of fun along the way).
For any doubts and suggestions, you can reach out on my Instagram, or LinkedIn. Follow me for Kotlin content and more. Happy Coding!
I will well appreciate one of these 👏
