Enhancement: Migrate from firebase to postgreSQL

[vaibhavyadav-dev]

I'm planning to migrate backend from Firebase (Firestore) to PostgreSQL to improve data integrity, enhance querying capabilities, and better support structured relational data for Bharat Seva+.

Benefits of Migrating:

Advanced Querying Capabilities: PostgreSQL provides advanced SQL querying options, making it easier to handle complex queries

• Fetching patient profiles based on multiple parameters (e.g., age, health conditions).
• Running aggregation and trend analysis over time for personalized health insights.

Structured Data Management: With PostgreSQL it is better suited for storing structured data like

• User profiles, settings, logs (where logged in), vaccination schedules.
• Establishing relationships between tables such as providers and schemes.

Scalability: PostgreSQL supports large-scale deployments with options for table partitioning, indexing, and horizontal/vertical scaling, making it suitable for growing user bases and data sizes (this one is really important).

Cost Efficiency: Firebase's read/write costs can increase significantly with scale, especially when hosted on cloud providers like AWS RDS

Industry standard: PostgreSQL is the choice when we need a robust, scalable, relational database with strong querying, data consistency, and enterprise-level performance.

Impact:

All Firebase-dependent code will be refactored to work with PostgreSQL. This includes data models, querying mechanisms, and CRUD operations.

Next Steps:

• Design PostgreSQL schema for structured data (patients, healthcare providers, appointments, logs).
• Refactor backend code to integrate PostgreSQL.
• Test migration for data consistency and ensure all critical functions (like appointments and health record logs) are unaffected.
• Deploy the updated version on staging for testing.
• Write test cases to test overall functionality.

Contributors: Please comment below if you're interested in helping with this migration or have suggestions on the process.

[namansharma3007]

I can handle this issue. Please assign it to me

[namansharma3007]

If you are comfortable I am also willing to use Prisma for the same. It makes querying easier and in the backend it uses postgreSQL

[vaibhavyadav-dev]

Ok @namansharma3007 you can move forward with it, if you've any issue you can drop your message in org. discussion.

[vaibhavyadav-dev]

@namansharma3007 can you share status of your work, have you run into any issue or want any help from my side, it is good to interact and share your approach, in case you're struggling with it. I'll be happy to help