ER Diagram

er_diagram.md

erDiagram
    USERS {
        bigint id PK
        string first_name
        string last_name
        string email
        string phone_number
        string phone_number_country_code
        boolean phone_number_verified
        string preferred_locale
        string stripe_customer_id
        boolean freezed
        string freeze_reason
        integer earned_amount
        timestamp created_at
        timestamp updated_at
    }
    
    HOMIES {
        bigint id PK
        string first_name
        string last_name
        string phone_number
        string email
        bigint city_id FK
        integer homie_service_id FK
        string gender
        string profile_picture
        date birthdate
        boolean driving_licence
        text languages
        timestamp created_at
        timestamp updated_at
    }
    
    CITIES {
        bigint id PK
        string name
        string country_code
        string currency
        string manager_name
        string manager_email
        string manager_phone_number
        boolean open
        string manager_picture
        string default_background_image
        timestamp created_at
        timestamp updated_at
    }
    
    HOMIE_SERVICES {
        bigint id PK
        string name
        text opening_days
        integer opening_hour
        integer closing_hour
        integer minimum_bookable_hour
        bigint city_id FK
        boolean open
        integer position
        string icon_image
        string header_background_image
        string icon_hex_color
        timestamp created_at
        timestamp updated_at
    }
    
    SERVICES {
        bigint id PK
        string type
        string frequency
        string status
        string frequent_mission_day
        integer frequent_mission_time
        timestamp mission_date
        string plan
        integer quantity
        bigint user_id FK
        bigint visitor_id FK
        bigint order_id FK
        bigint city_id FK
        integer homie_service_id FK
        bigint address_id FK
        text user_comment
        timestamp created_at
        timestamp updated_at
    }
    
    MISSIONS {
        bigint id PK
        string status
        timestamp meeting_date
        integer price
        integer original_price
        string payment_method
        bigint service_id FK
        bigint homie_service_id FK
        bigint user_id FK
        bigint address_id FK
        bigint homie_id FK
        string plan
        integer quantity
        string payment_recovery_uid
        integer payment_attempts_count
        timestamp last_payment_attempt_at
        timestamp created_at
        timestamp updated_at
    }
    
    PAYMENTS {
        bigint id PK
        bigint user_id FK
        bigint mission_id FK
        string stripe_charge_id
        integer amount
        string currency
        string status
        string stripe_receipt_url
        string stripe_card_id
        string invoice
        integer invoice_number
        timestamp created_at
        timestamp updated_at
    }
    
    ADDRESSES {
        bigint id PK
        string name
        string line1
        string line2
        string street_number
        string house_number
        string city
        string zip_code
        string state
        string country
        string country_code
        string additional_information
        bigint user_id FK
        integer visitor_id FK
        boolean deleted
        timestamp created_at
        timestamp updated_at
    }
    
    CARDS {
        bigint id PK
        string last4
        string brand
        integer exp_month
        integer exp_year
        string fingerprint
        string funding
        bigint user_id FK
        boolean default
        string stripe_id
        boolean deleted
        timestamp created_at
        timestamp updated_at
    }
    
    ORDERS {
        bigint id PK
        bigint user_id FK
        bigint visitor_id FK
        bigint city_id FK
        timestamp expires_at
        timestamp created_at
        timestamp updated_at
    }
    
    VISITORS {
        bigint id PK
        string x_token_visitor
        string contact_email
        timestamp expires_at
        timestamp created_at
        timestamp updated_at
    }
    
    COUPONS {
        bigint id PK
        string name
        string type
        timestamp starts_at
        timestamp expires_at
        integer discount
        integer amount
        integer sponsor_id FK
        boolean code_is_uniq
        timestamp created_at
        timestamp updated_at
    }
    
    COUPON_ENTRIES {
        bigint id PK
        bigint coupon_id FK
        bigint user_id FK
        string code_name
        boolean consummed
        timestamp created_at
        timestamp updated_at
    }
    
    COUPON_LABELS {
        bigint id PK
        string code
        bigint coupon_id FK
        timestamp created_at
        timestamp updated_at
    }

    %% Relationships
    USERS ||--o{ SERVICES : creates
    USERS ||--o{ MISSIONS : orders
    USERS ||--o{ PAYMENTS : makes
    USERS ||--o{ ADDRESSES : has
    USERS ||--o{ CARDS : owns
    USERS ||--o{ ORDERS : places
    USERS ||--o{ COUPON_ENTRIES : uses
    
    VISITORS ||--o{ SERVICES : creates
    VISITORS ||--o{ ADDRESSES : has
    VISITORS ||--o{ ORDERS : places
    
    HOMIES ||--o{ MISSIONS : performs
    HOMIES }o--|| CITIES : "works in"
    HOMIES }o--|| HOMIE_SERVICES : "specializes in"
    
    CITIES ||--o{ HOMIE_SERVICES : contains
    CITIES ||--o{ SERVICES : "available in"
    CITIES ||--o{ ORDERS : "placed in"
    
    SERVICES ||--|| MISSIONS : generates
    SERVICES }o--|| HOMIE_SERVICES : "type of"
    SERVICES }o--|| ADDRESSES : "delivered to"
    SERVICES }o--|| ORDERS : "part of"
    
    MISSIONS ||--o{ PAYMENTS : "paid by"
    MISSIONS }o--|| HOMIE_SERVICES : "service type"
    MISSIONS }o--|| ADDRESSES : "performed at"
    MISSIONS }o--|| HOMIES : "assigned to"
    
    COUPONS ||--o{ COUPON_ENTRIES : "used as"
    COUPONS ||--o{ COUPON_LABELS : "has codes"
    COUPONS }o--|| USERS : "sponsored by"

Loading