Datenbankschema - ER-Diagramm

Last Updated: Juli 2025
Version: 4.0 - Vollständiges Schema mit OrderRequests und Notification System
Status: ✅ Aktuell - Basierend auf aktueller PostgreSQL-Datenbank

Vollständiges Schema-Diagramm

📋 Mermaid-Code kopieren

erDiagram
    %% ============================================
    %% HOFFNUNGSTRÄGER SPRACHBRÜCKE - DATABASE SCHEMA
    %% Complete Entity Relationship Diagram
    %% ============================================

    %% === CORE USER SYSTEM ===
    USERS {
        uuid id PK
        string email UK
        string state "Spatie States: Requested/Active/Deactivated"
        string first_name
        string last_name
        string salutation "herr/frau/divers"
        date birth_date
        string phone
        string gender
        timestamp accepted_at
        timestamp last_login_at
        timestamp email_verified_at
        timestamp created_at
        timestamp updated_at
    }

    ROLES {
        bigint id PK
        string name "admin/requester/Sprachmittler"
        string guard_name
        timestamp created_at
        timestamp updated_at
    }

    MODEL_HAS_ROLES {
        uuid model_id FK
        bigint role_id FK
        string model_type "App\\Models\\User"
    }

    %% === USER PROFILES ===
    CLIENT_PROFILES {
        uuid id PK
        uuid user_id FK
        string organization_name
        string organization_type "government/school/hospital/ngo"
        string billing_email
        string billing_reference
        text notes
        timestamp created_at
        timestamp updated_at
    }

    INTERPRETER_PROFILES {
        uuid id PK
        uuid user_id FK
        uuid address_id FK
        string tax_id
        string bank_account_owner
        string bank_account_iban
        string bank_account_bic
        json availability_settings
        json availability_whitelist
        json topic_blacklist
        integer max_distance_km
        text qualifications
        integer experience_years
        text specializations
        text notes
        timestamp created_at
        timestamp updated_at
    }

    %% === LANGUAGE SYSTEM ===
    SYSTEM_LANGUAGES {
        uuid id PK
        string code UK "ISO language code: de, en, fr"
        string name_de "Deutsch, Englisch, Französisch"
        string name_en "German, English, French"
        string flag_emoji "🇩🇪 🇬🇧 🇫🇷"
        boolean is_active "Active/Inactive control"
        integer sort_order "Display order"
        timestamp created_at
        timestamp updated_at
    }

    INTERPRETER_LANGUAGES {
        uuid interpreter_profile_id FK
        string language_code FK "References system_languages.code"
        string proficiency_level "native/advanced/intermediate/basic"
        decimal hourly_rate
        boolean is_certified
        text certification_details
        timestamp created_at
        timestamp updated_at
    }

    %% === CATEGORY SYSTEM ===
    CATEGORIES {
        bigint id PK
        string name
        string slug UK
        text description
        boolean is_active
        integer sort_order
        integer _lft "Nested Set Left Boundary"
        integer _rgt "Nested Set Right Boundary"
        integer parent_id "Self-Reference for Tree"
        json tags "Category tags"
        timestamp created_at
        timestamp updated_at
    }

    %% === CORE ORDER SYSTEM ===
    ORDERS {
        uuid id PK
        uuid requester_id FK "Client who creates order"
        string state "Spatie States: Open/Assigned/Completed"
        string order_number UK "ORD-2025-001"
        bigint appointment_category_id FK
        uuid location_address_id FK
        uuid desired_interpreter_id FK "Preferred Sprachmittler"
        string desired_gender "no_preference/männlich/weiblich/divers"
        date appointment_date
        time start_time
        time end_time
        string client_salutation
        string client_first_name
        string client_last_name
        string client_mobile
        text interpreter_notes
        boolean is_30km_plus "Allow >30km distance"
        text notes
        decimal total_amount
        timestamp requested_at
        timestamp scheduled_at
        timestamp completed_at
        timestamp check_in_time
        timestamp check_out_time
        bigint applied_tariff_rate_id FK "Tariff snapshot"
        decimal applied_hourly_rate
        decimal applied_kilometer_rate
        decimal applied_tax_rate
        decimal applied_minimum_billable_hours
        integer applied_payment_terms_days
        timestamp tariff_applied_at
        timestamp created_at
        timestamp updated_at
        timestamp deleted_at "Soft Delete"
    }

    %% === ORDER LANGUAGE RELATIONSHIPS ===
    ORDER_CLIENT_LANGUAGES {
        bigint id PK
        uuid order_id FK
        uuid system_language_id FK
        timestamp created_at
        timestamp updated_at
    }

    ORDER_TARGET_LANGUAGES {
        bigint id PK
        uuid order_id FK
        uuid system_language_id FK
        timestamp created_at
        timestamp updated_at
    }

    %% === ORDER REQUESTS (SPRACHMITTLER MATCHING) ===
    ORDER_REQUESTS {
        uuid id PK
        uuid order_id FK
        uuid interpreter_id FK "Targeted Sprachmittler"
        string state "Spatie States: Requested/Accepted/Rejected"
        string service_type "interpretation/translation/consultation"
        uuid source_language_id FK
        uuid target_language_id FK
        text description "Service description"
        text requirements "Special requirements"
        text location "Service location"
        timestamp requested_start_time
        timestamp requested_end_time
        integer estimated_duration_minutes
        boolean is_urgent "Urgent request flag"
        json additional_info "Extra information"
        timestamp responded_at
        text rejection_reason
        integer matching_score "Algorithm score 0-100"
        string request_type "planned/urgent"
        boolean is_first_choice "Primary choice for order"
        timestamp timeout_at "Request expires at"
        integer timeout_minutes "Timeout duration"
        timestamp notification_sent_at
        timestamp notification_delivered_at
        integer attempt_number "Retry attempt"
        json previous_rejections "Rejection history"
        string notification_channel "email/push/sms"
        text admin_notes
        timestamp created_at
        timestamp updated_at
    }

    %% === ASSIGNMENTS (ACCEPTED WORK) ===
    ASSIGNMENTS {
        uuid id PK
        uuid order_id FK
        uuid interpreter_id FK
        string status "pending/accepted/rejected/in_progress/completed"
        text notes
        decimal calculated_cost "Final calculated cost"
        decimal hourly_rate "Rate used for calculation"
        integer actual_duration_minutes "Real service duration"
        timestamp assigned_at
        timestamp accepted_at
        timestamp rejected_at
        timestamp started_at "Service start (check-in)"
        timestamp completed_at "Service end (check-out)"
        timestamp cancelled_at
        text rejection_reason
        json feedback "Post-service feedback"
        timestamp created_at
        timestamp updated_at
    }

    %% === BILLING SYSTEM ===
    INVOICES {
        uuid id PK
        uuid order_id FK
        uuid client_id FK "Billed to"
        uuid interpreter_id FK "Work performed by"
        string invoice_number UK "INV-2025-001"
        string state "Spatie States: Draft/Submitted/Approved/Paid"
        integer version "Versioning support"
        decimal subtotal
        decimal tax_rate
        decimal tax_amount
        decimal total_amount
        date invoice_date
        date due_date
        date paid_date
        text notes
        json line_items "Invoice line items"
        string payment_method
        string payment_reference
        timestamp submitted_at
        timestamp approved_at
        timestamp rejected_at
        uuid created_by FK
        uuid approved_by FK
        uuid rejected_by FK
        string rejection_reason
        text rejection_notes
        decimal base_amount "Base service cost"
        decimal hours_worked "Total hours"
        decimal travel_kilometers "Travel distance"
        decimal additional_costs_total "Sum of additional costs"
        timestamp created_at
        timestamp updated_at
        timestamp deleted_at
    }

    ADDITIONAL_COSTS {
        uuid id PK
        uuid order_id FK
        string description
        string category "travel/overtime/parking/materials/equipment"
        decimal amount
        string approval_status "pending/approved/rejected"
        uuid created_by FK
        uuid approved_by FK
        text approval_notes
        string receipt_url "Receipt image URL"
        timestamp approved_at
        timestamp created_at
        timestamp updated_at
    }

    TARIFF_RATES {
        bigint id PK
        decimal hourly_rate "Base hourly rate"
        decimal kilometer_rate "Travel cost per km"
        decimal tax_rate
        decimal minimum_billable_hours "Minimum billing hours"
        integer payment_terms_days "Payment terms"
        timestamp effective_from
        timestamp effective_until
        boolean is_active "Current rate flag"
        text description
        timestamp created_at
        timestamp updated_at
    }

    %% === ADDRESS SYSTEM ===
    ADDRESSES {
        uuid id PK
        string addressable_type "Polymorphic Model Type"
        uuid addressable_id "Polymorphic Model ID"
        string type "billing/shipping/service"
        string street
        string number
        string city
        string zip
        string state
        string country
        boolean is_primary "Primary address flag"
        timestamp created_at
        timestamp updated_at
    }

    %% === NOTIFICATION SYSTEM ===
    DEVICES {
        uuid id PK
        uuid user_id FK
        string device_token UK "Platform device token"
        string platform "ios/android/web"
        string device_name "User device name"
        string app_version
        string os_version
        boolean is_active "Device active status"
        string onesignal_player_id UK "OneSignal player ID"
        timestamp last_used_at
        timestamp created_at
        timestamp updated_at
    }

    NOTIFICATIONS {
        uuid id PK
        string type "Laravel notification type"
        string notifiable_type "Polymorphic target type"
        uuid notifiable_id "Polymorphic target ID"
        text data "Notification data JSON"
        timestamp read_at
        timestamp created_at
        timestamp updated_at
    }

    %% ============================================
    %% RELATIONSHIPS
    %% ============================================

    %% === USER MANAGEMENT ===
    USERS ||--o{ MODEL_HAS_ROLES : "has roles"
    ROLES ||--o{ MODEL_HAS_ROLES : "assigned to users"
    USERS ||--o| CLIENT_PROFILES : "client extends"
    USERS ||--o| INTERPRETER_PROFILES : "Sprachmittler extends"
    USERS ||--o{ DEVICES : "registers devices"
    USERS ||--o{ NOTIFICATIONS : "receives notifications"

    %% === LANGUAGE SYSTEM ===
    SYSTEM_LANGUAGES ||--o{ ORDER_CLIENT_LANGUAGES : "order source languages"
    SYSTEM_LANGUAGES ||--o{ ORDER_TARGET_LANGUAGES : "order target languages"
    SYSTEM_LANGUAGES ||--o{ INTERPRETER_LANGUAGES : "Sprachmittler skills"
    SYSTEM_LANGUAGES ||--o{ ORDER_REQUESTS : "source language"
    SYSTEM_LANGUAGES ||--o{ ORDER_REQUESTS : "target language"
    INTERPRETER_PROFILES ||--o{ INTERPRETER_LANGUAGES : "language proficiency"

    %% === CATEGORY SYSTEM ===
    CATEGORIES ||--o{ CATEGORIES : "parent-child tree"
    CATEGORIES ||--o{ ORDERS : "service category"

    %% === CORE ORDER WORKFLOW ===
    USERS ||--o{ ORDERS : "creates orders (requester)"
    ORDERS ||--o{ ORDER_CLIENT_LANGUAGES : "multiple source languages"
    ORDERS ||--o{ ORDER_TARGET_LANGUAGES : "multiple target languages"
    ORDERS ||--o{ ORDER_REQUESTS : "Sprachmittler matching requests"
    ORDERS ||--o{ ASSIGNMENTS : "accepted Sprachmittler assignments"
    ORDERS ||--o{ ADDITIONAL_COSTS : "extra costs"
    ORDERS ||--o{ INVOICES : "billing"

    %% === SPRACHMITTLER MATCHING ===
    USERS ||--o{ ORDER_REQUESTS : "receives requests (Sprachmittler)"
    ORDER_REQUESTS ||--|| ASSIGNMENTS : "successful requests become assignments"
    USERS ||--o{ ASSIGNMENTS : "accepts work (Sprachmittler)"

    %% === BILLING & PAYMENT ===
    USERS ||--o{ INVOICES : "billed to (client)"
    USERS ||--o{ INVOICES : "work performed by (Sprachmittler)"
    TARIFF_RATES ||--o{ ORDERS : "applies rates snapshot"
    USERS ||--o{ ADDITIONAL_COSTS : "created by"
    USERS ||--o{ ADDITIONAL_COSTS : "approved by"

    %% === ADDRESS SYSTEM (Polymorphic) ===
    ORDERS ||--o{ ADDRESSES : "service location"
    INTERPRETER_PROFILES ||--o{ ADDRESSES : "Sprachmittler address"

    %% === ACTIVITY & AUDIT ===
    USERS ||--o{ INVOICES : "created by"
    USERS ||--o{ INVOICES : "approved by"
    USERS ||--o{ INVOICES : "rejected by"

---

Tags: #database #schema #postgresql #sprachbruecke #entity-relationship #mermaid