Fit Factory – E-commerce Database Design

Key Features of the Database Design:

• User & Auth Management: Leveraging Supabase’s auth schema with tables like users, sessions, and refresh_tokens to handle authentication and secure user sessions.

• Product Catalog: Tables such as itemsInfo, itemPrice, itemImgs, categoryInfo, and itemCategory manage products, categories, images, and pricing efficiently.

• Shopping Cart & Orders: cartInfo and cartItem tables store active carts and cart items, supported by views like cart_total_price and full_cart_details for easy aggregation and reporting.

• Real-Time Features: realtime.messages and realtime.subscription tables enable live chat and notification functionalities.

• Storage & Media Management: Supabase storage schema handles media objects and buckets, supporting product images and other file storage.

• Security & Encryption: vault.secrets table and related functions ensure sensitive data is securely stored and managed.

• Reusable Views & Functions: Created optimized views (full_item_info, full_cart_details) and functions (prevent_multiple_active_carts) to simplify queries and maintain data integrity.

This database design ensures scalability, performance, and maintainability, allowing the Fit Factory app to handle multiple users, products, and real-time operations efficiently.