Company
📱 GutDiaries
Skill Area
📊 Data & AnalyticsProduct & Development
Tool Stack
PostgreSQLSupabasePostman (APIs)
Overview
🔍 Project context | A food and symptom tracking application for GERD and IBS patients built on Supabase with PostgreSQL. The backend manages user data, food entries, and symptom correlations to help users identify trigger foods. |
💡 Approach | Implemented relational database design with foreign key constraints to maintain data integrity. Created custom PostgreSQL RPC functions for efficient data retrieval and complex correlation analysis between symptoms and food intake. |
🚀 Key Achievements | Designed a flexible schema supporting both single food items and complex meals with ingredient arrays and JSON attributes. Built a personalized symptom management system allowing users to enable/disable tracking of specific symptoms. |
Implementation
The GutDiaries backend is built on PostgreSQL hosted in Supabase, featuring:
- Relational Data Model: Structured around users, symptoms, and meals with appropriate foreign key relationships
- Flexible Data Storage: Support for both structured data and JSON attributes to accommodate varied user input
- Custom RPC Functions: PostgreSQL functions that handle:
- Symptom-food correlation analysis
- Time-based symptom pattern recognition
- Personalized trigger food identification
Backend Architecture
Technical Highlights
- Scalable Schema Design: Support for user-defined symptoms while maintaining system defaults
- Performance Optimization: Efficient query design for real-time symptom correlation analysis
- Data Security: Row-level security policies ensuring users can only access their own food and symptom data, with rigid access controls, also making use of Supabase’s cloudflare add-ons for extra security
- Flexible Food Tracking: Support for both single ingredients and complex meals with arrays and JSON attributes
The PostgreSQL backend forms the foundation of GutDiaries, enabling users to log flexibly, identify correlations between their diet and digestive symptoms through sophisticated data analysis.