Update on Saheb Development Plan

Purpose of This Documentation

This presentation covers sections that were not discussed in the last meeting, as well as sections that required further research.

Key Points

This is the list of topics we will cover:

1. Database Technology

2. Logging, Security, and Strategy

3. Listening to Kafka Events with NestJS

4. OneSignal or Expo

5. Keycloak

6. Versioning Strategy

1. Database Technology

Choice: PostgreSQL

Why

• Our data is structured, not dynamic, so a relational DB fits naturally.

• We have complex queries, especially in the database translation system, which benefit from SQL capabilities.

• Strong support for views and transactions ensures data consistency and simplifies reporting or multi-step operations

PostgresSQL (SQL) VS MongoDB (NoSQL)

Based on this amazon article this is the key differences:

Feature	MongoDB (NoSQL)	PostgreSQL (Relational/ORDBMS)	Key Takeaways
Data Model	Stores data as JSON-like documents in collections. Flexible and can store unstructured, evolving, or dynamic data.	Stores data in tables with rows and columns. Structured, with strong data integrity and predefined schema.	MongoDB is for flexible, changing data; PostgreSQL is for structured, relational data.
Basic Unit of Storage	Document (JSON/BSON). Can include nested objects and arrays.	Row in a table, with defined column types.	MongoDB keeps all data for a record in one document; PostgreSQL stores data in structured rows.
Schema	Flexible, no enforced schema.	Strict predefined schema.	PostgreSQL ensures consistency; MongoDB allows easy changes but may risk inconsistency.
Query Language	MongoDB Query Language (MQL). Supports aggregations, projections, geospatial, text search.	SQL (Postgres variant), fully compatible with standard SQL.	SQL is standard and powerful for joins; MQL is document-oriented and different from SQL.
Transactions / ACID	Multi-document ACID transactions since v4.0, but less core than PostgreSQL.	Fully ACID-compliant, reliable for multi-step operations.	PostgreSQL is safer for critical operations requiring strong consistency.
Complex Queries / Joins	Limited joins; often requires embedding data (denormalization).	Supports multi-table joins and complex queries naturally.	MongoDB may repeat data; PostgreSQL handles relational data elegantly.
Views	Read-only views supported.	Full support for views, like saved queries.	PostgreSQL is stronger for reporting or abstracted queries.
Concurrency	Document-level atomicity, optimistic locking, MVCC for multiple users.	MVCC with data snapshots, flexible isolation levels, write-ahead logging (WAL).	Both handle concurrent access well; PostgreSQL is mature and battle-tested.
Scalability	Horizontal scaling (adding more machines) via sharding; can handle huge distributed datasets.	Partitioning, connection pooling, and load balancing; primarily vertical scaling (adding more power).	MongoDB excels for horizontal scaling; PostgreSQL scales well but often vertically.
Use Cases	CMS, streaming data, IoT, unstructured content, high-concurrency apps.	Data warehousing, ecommerce, transactional systems, structured data analytics.	Choose based on the type of data and access patterns. (we have structured data)
Data Relationships	No predefined relationships; uses denormalization (embed related data).	Strong relationships via foreign keys; joins across tables.	PostgreSQL is better for relational-heavy systems; MongoDB works best with self-contained documents.

PostgresSQL vs Mysql Benchmarks

A greate video by Anton Putra called MySQL vs PostgreSQL Performance Benchmark.

First Benchmark overview