Entity Relationship Diagram (ERD)

1. Purpose

This document presents the logical entity relationship model for SaludVital. It is based on the approved Database Design and represents the relationships between the main entities of the application.

The ERD is organized into three logical areas to improve readability:

1. Users and doctor schedules.
2. Appointments and clinical records.
3. Notifications, WhatsApp integration, and auditing.

The diagrams represent the logical database design. They do not constitute database migrations or SQL implementation.

2. Notation

• PK: Primary Key.
• FK: Foreign Key.
• UK: Unique Key.
• One-to-many relationships indicate that one record may be associated with multiple records in another entity.
• Optional relationships are identified where applicable.
• Referential integrity and deletion policies are defined in the Database Design document.

3. Users and Doctor Schedules

This section represents user roles, patient and doctor profiles, medical specialties, doctor availability, and schedule exceptions.

erDiagram
    roles ||--o{ users : "assigns"
    users o|--o| patients : "has profile"
    users ||--o| doctors : "has profile"
    specialties ||--o{ doctors : "classifies"
    doctors ||--o{ doctor_availability : "defines"
    doctors ||--o{ doctor_schedule_exceptions : "has exceptions"
    users o|--o{ doctor_schedule_exceptions : "creates"

    roles {
        INT id PK
    }

    users {
        INT id PK
        INT role_id FK
    }

    patients {
        INT id PK
        INT user_id FK
    }

    doctors {
        INT id PK
        INT user_id FK
        INT specialty_id FK
    }

    specialties {
        INT id PK
    }

    doctor_availability {
        INT id PK
        INT doctor_id FK
    }

    doctor_schedule_exceptions {
        INT id PK
        INT doctor_id FK
        INT created_by FK
    }

Relationship rules

• Each user is assigned a role.
• A user may have a patient profile or a doctor profile, according to the assigned role.
• Each doctor is associated with a medical specialty.
• A doctor may have multiple availability records and schedule exceptions.
• A schedule exception may record the user who created it.
• Schedule records and user profiles must preserve historical integrity according to the database deletion policies.

4. Source of Truth

The Database Design Wiki page remains the authoritative reference for complete attributes, data types, primary keys, foreign keys, unique constraints, indexes, business rules, and deletion policies.

The ERD must remain consistent with that specification as the design evolves.

4. Appointments and Clinical Records

This section represents appointment scheduling, appointment status tracking, historical events, consultations, diagnoses, prescriptions, and prescribed medication items.

erDiagram
    patients ||--o{ appointments : "books"
    doctors ||--o{ appointments : "attends"
    specialties ||--o{ appointments : "categorizes"
    appointment_statuses ||--o{ appointments : "defines status"
    appointments ||--o{ appointment_history : "records events"
    appointments ||--o| consultations : "may have"
    doctors ||--o{ consultations : "conducts"
    consultations ||--o{ diagnoses : "contains"
    consultations ||--o{ prescriptions : "generates"
    prescriptions ||--|{ prescription_items : "contains"
    users o|--o{ appointment_history : "performs action"

    appointments {
        INT id PK
        INT patient_id FK
        INT doctor_id FK
        INT specialty_id FK
        INT status_id FK
    }

    appointment_statuses {
        INT id PK
    }

    appointment_history {
        INT id PK
        INT appointment_id FK
        INT actor_user_id FK
    }

    consultations {
        INT id PK
        INT appointment_id FK
        INT doctor_id FK
    }

    diagnoses {
        INT id PK
        INT consultation_id FK
    }

    prescriptions {
        INT id PK
        INT consultation_id FK
    }

    prescription_items {
        INT id PK
        INT prescription_id FK
    }

Relationship rules

• Each appointment belongs to one patient, one doctor, one specialty, and one appointment status.
• An appointment may have a consultation. Each consultation is associated with one appointment.
• A consultation is conducted by a doctor and may contain multiple diagnoses and prescriptions.
• A prescription contains one or more prescription items.
• Appointment history records events associated with an appointment and may identify the user who performed an action.
• Appointment, consultation, diagnosis, and prescription records must preserve their historical and clinical integrity according to the Database Design specification.

5. Notifications, WhatsApp Integration, and Auditing

This section represents patient notification preferences, notification records, WhatsApp conversations and messages, WhatsApp verification, appointment requests, and audit logs.

erDiagram
    specialties o|--o{ whatsapp_appointment_requests : "requested specialty"
    doctors o|--o{ whatsapp_appointment_requests : "requested doctor"
    whatsapp_conversations ||--o{ whatsapp_appointment_requests : "produces"
    patients ||--o{ emergency_contacts : "has contacts"
    patients ||--o{ notification_preferences : "configures"
    patients ||--o{ notifications : "receives"
    appointments o|--o{ notifications : "relates to"
    patients o|--o{ whatsapp_conversations : "participates in"
    whatsapp_conversations ||--o{ whatsapp_messages : "contains"
    patients ||--o{ whatsapp_verifications : "verifies"
    patients ||--o{ whatsapp_appointment_requests : "requests"
    whatsapp_appointment_requests o|--o| appointments : "may create"
    users o|--o{ audit_logs : "performs actions"
    users ||--o{ password_reset_tokens : "has reset tokens"

    emergency_contacts {
        INT id PK
        INT patient_id FK
    }

    notification_preferences {
        INT id PK
        INT patient_id FK
    }

    notifications {
        INT id PK
        INT patient_id FK
        INT appointment_id FK
    }

    whatsapp_conversations {
        INT id PK
        INT patient_id FK
    }

    whatsapp_messages {
        INT id PK
        INT conversation_id FK
    }

    whatsapp_verifications {
        INT id PK
        INT patient_id FK
    }

    whatsapp_appointment_requests {
        INT id PK
        INT patient_id FK
        INT conversation_id FK
        INT requested_specialty_id FK
        INT requested_doctor_id FK
        INT appointment_id FK
    }

    audit_logs {
        INT id PK
        INT actor_user_id FK
    }

    password_reset_tokens {
        INT id PK
        INT user_id FK
    }

Relationship rules

• Patients may configure notification preferences and receive notifications.
• A notification may be associated with an appointment or may exist without one.
• A WhatsApp conversation may contain multiple messages.
• A WhatsApp verification or appointment request may be associated with a patient when the patient has been identified.
• A WhatsApp appointment request may result in an appointment, subject to validation and successful booking.
• Audit logs record relevant actions and may reference the user who performed them.
• WhatsApp message deduplication, request idempotency, foreign-key policies, and data-retention rules must follow the Database Design specification.

6. Validation and Consistency

Before approving the ERD, verify that:

• Every relationship corresponds to a foreign key or an explicitly documented logical relationship.
• Optional relationships match the nullable fields defined in the database model.
• Primary and foreign key names are consistent with the Database Design specification.
• Unique constraints and business rules remain documented in the Database Design page.
• Historical appointment and clinical records are preserved.
• The diagrams do not introduce entities or relationships that have not been approved in the model.

The complete Database Design specification remains the authoritative source for the final schema.