Skip to content

Database Schema

Entity-Relationship Diagram

Diagram needed

Place full ERD at assets/images/supabase/erd.png

erDiagram
    profiles ||--o| sellers : "pharmacy_id"
    profiles ||--o{ orders : "user_id"
    profiles ||--o{ addresses : "user_id"
    profiles ||--o{ reviews : "user_id"
    profiles ||--o{ notifications : "user_id"
    profiles ||--o{ chats : "via chat_participants"
    profiles ||--o{ wishlists : "user_id"
    sellers ||--o{ products : "seller_id"
    sellers ||--o{ featured_product_payments : "pharmacy_id"
    sellers ||--o{ pharmacy_subscriptions : "pharmacy_id"
    sellers ||--o{ analytics : "pharmacy_id"
    sellers ||--o{ offer_products : "pharmacy_id"
    categories ||--o{ categories : "parent_id"
    categories ||--o{ products : "category_id"
    brands ||--o{ products : "brand_id"
    products ||--o{ order_items : "product_id"
    products ||--o{ reviews : "product_id"
    products ||--o{ offer_products : "product_id"
    orders ||--o{ order_items : "order_id"
    orders ||--o{ payments : "order_id"
    orders ||--o{ order_status_history : "order_id"
    chats ||--o{ messages : "chat_id"
    promotions ||--o{ offer_products : "promotion_id"
    banners ||--o| promotions : "promotion_id"

Table Reference

# Table Schema Key Columns Description
1 profiles public id, email, role, fcm_token User profiles (extends auth.users)
2 sellers public id, user_id, store_name, approval_status Pharmacy registrations
3 categories public id, name, parent_id, sort_order Product categories (hierarchical)
4 brands public id, name, slug, logo Product manufacturers
5 products public id, seller_id, price, sku, images Medication/products
6 variant_options public id, product_id, option_name Variant definitions
7 variant_values public id, variant_option_id, value_name Variant values
8 variant_combinations public id, product_id, sku, price, stock SKU-level variants
9 variant_combination_values public id, combo_id, value_id Junction table
10 carts public id, user_id Shopping carts
11 cart_items public id, cart_id, product_id, quantity Cart line items
12 addresses public id, user_id, city, is_default Shipping addresses
13 orders public id, order_number, user_id, total, status Customer orders
14 order_items public id, order_id, product_id, seller_id, price Order line items
15 payments public id, order_id, tx_ref, amount, status Chapa transactions
16 order_status_history public id, order_id, old_status, new_status Status audit log
17 reviews public id, product_id, user_id, rating, comment Product reviews
18 wishlists public id, user_id User wishlists
19 wishlist_items public id, wishlist_id, product_id Wishlist items
20 chats public id, last_message, unread_count Chat conversations
21 chat_participants public id, chat_id, user_id Chat membership
22 messages public id, chat_id, sender_id, content Chat messages
23 notifications public id, user_id, type, title, data (jsonb) User notifications
24 banners public id, title, image_url, position, promotion_id Homepage banners
25 static_pages public id, title, slug, content CMS pages
26 settings public id, key, value, group System settings
27 analytics public id, pharmacy_id, order_id, metrics Seller analytics
28 featured_product_payments public id, product_id, pharmacy_id, status, amount Paid product boosts
29 pharmacy_subscriptions public id, pharmacy_id, type, status, amount Subscription plans
30 featured_product_access public id, product_id, date, views, clicks Access tracking
31 promotions public id, title, status, start_date, end_date Campaign timelines
32 offer_products public id, product_id, promotion_id, discount_type, offer_price Seller discounts

Indexes

Key performance indexes include: - idx_products_seller_id on products(seller_id) - idx_products_category_id on products(category_id) - idx_orders_user_id on orders(user_id) - idx_orders_order_status on orders(order_status) - idx_notifications_user_id on notifications(user_id) - idx_offer_products_product_id on offer_products(product_id) - idx_pharmacy_subscriptions_pharmacy_status on pharmacy_subscriptions(pharmacy_id, status) WHERE status = 'active'