Phase 3 & 4: AI Tutor - Done
Well It take me more a week to complete Phase 3 and Phase 4. Let dive into these phases and check what I build so far.
Phase 3: UNIVERSITIES DATABASE
It is clear from the title I made database schemas for universities. What I used was PostgreSQL. Why? well tbh in starting I don't have any idea which to use? Then there were some adventages of using PostgreSQL.
1. The "AI-First" Database (pgvector)
The most critical reason for this choice is pgvector. Since the app provides a personalized roadmap and university fit analysis, it needs to perform semantic searches (searching by meaning rather than just keywords).
PostgreSQL allows us to store AI-generated embeddings (vectors) directly alongside our standard relational data.
This eliminates the need for a separate, expensive vector database like Pinecone, keeping the architecture simple and the "zero expense" goal intact.
2. Multi-Model Flexibility (JSONB)
University data and student profiles can be messy and varied. PostgreSQL’s JSONB support allows us to store semi-structured data without losing the power of SQL.
We can store a university's specific scholarship details as a JSON object while still being able to query it with high performance.
It gives us the flexibility of a NoSQL database (like MongoDB) with the strict data integrity of a relational one.
3. Industry Standards & Integration
PostgreSQL is the "gold standard" for production-ready open-source databases.
Supabase Support: It integrates perfectly with the backend stack, providing managed hosting, real-time updates, and built-in authentication.
PostGIS & Scalability: If the project expands to include location-based university mapping for students in Gilgit-Baltistan, PostgreSQL’s specialized extensions are already built to handle it.
Reliability: Following industry standards ensures that as the technical lead, I am building a system that is robust, easily maintainable, and ready for deployment on platforms like Railway or AWS.
4. Performance at Zero Cost
By using PostgreSQL 15+, I get advanced features like improved sort performance and better compression for free.
What else?
In our database we have two main tables: universities and programs. Obviously, there are others as well but for now we stick to them.
For these tables we have to create Pydantic models so we can make sure our data is correct before it reaches the database by putting the type hinting and field validations.
We then added universities data top 5 to 10 universities of Pakistan. Create some endpoint like getting university along with their programs or getting programs of particular university, endpoint to add, update, and delete universities.
Well if you are wandering what is that small lock at the end of some endpoints then it is dependency injection to make sure only autherized users can access our backend.
Phase 4: SEARCH & FILTERING
In this, we added searching and filtering to our endpoints using query parameters and path parameters.
I did the following in this phase:
Create University Search Endpoint
Implement Eligibility Calculator
Add Search Filters Validation
Optimize Search Queries
Database Schema:
This is how it looks in Supabase. It may not be optimized, but it's good enough for MVP.
