Diagrama Entidad-Relacion MedTime

Identificador: DB-ERD-001 Version: 1.0.0 Fecha: 2025-12-07 Estado: Aprobado Autor: DatabaseDrone / SpecQueen Technical Division

---

• 1. Introduccion
• 1.1. Principios de Diseno
• 1.2. Clasificacion de Datos
• 2. Diagrama ER Completo
• 2.1. Servidor - Autenticacion y Usuarios
• 2.2. Servidor - Blobs Cifrados E2E
• 2.3. Servidor - Catalogos Publicos
• 2.4. Servidor - Auditoria
• 2.5. Cliente - Usuario y Perfil
• 2.6. Cliente - Medicamentos
• 2.7. Cliente - Recetas, Diagnosticos y Citas
• 2.8. Cliente - Local Only
• 3. Resumen de Entidades
• 3.1. Entidades del Servidor
• 3.2. Entidades del Cliente
• 4. Relaciones Principales
• 4.1. Relaciones de Usuario (Servidor)
• 4.2. Relaciones de Medicamentos (Cliente)
• 4.3. Relaciones de Catalogo (Servidor)
• 5. Cardinalidades
• 6. Indices Recomendados
• 6.1. Indices del Servidor
• 6.2. Indices del Cliente
• 7. Referencias Cruzadas

---

1. Introduccion

Este documento define el modelo entidad-relacion completo de MedTime, separando claramente las entidades que residen en el cliente(LOCAL/SYNCED_E2E) de las que residen en elservidor (SERVER_SOURCE/SYNCED_PLAIN).

1.1. Principios de Diseno

Principio	Descripcion
Zero-Knowledge	El servidor NUNCA almacena datos en claro que revelen PHI/PII
Client-Heavy	95% de la logica y datos en cliente, 5% en servidor
Encrypted Blobs	Datos SYNCED_E2E se almacenan como blobs opacos en servidor
Blind Indexes	Campos buscables (email, telefono) usan hash para lookup
RLS Everywhere	Row Level Security en todas las tablas del servidor

1.2. Clasificacion de Datos

Clasificacion	Ubicacion	Cifrado	Ejemplo
LOCAL_ONLY	Solo cliente	N/A	Cache UI, preferencias locales
SYNCED_E2E	Cliente + Servidor (blob)	AES-256-GCM	Medicamentos, recetas, diagnosticos
SYNCED_HASH	Cliente + Servidor	SHA-256 (blind index)	Email, telefono
SYNCED_PLAIN	Cliente + Servidor	No	Timestamps sync, version, tier
SERVER_SOURCE	Solo servidor	No	Catalogos publicos, audit logs

---

2. Diagrama ER Completo

2.1. Servidor - Autenticacion y Usuarios

erDiagram
    srv_users {
        uuid user_id PK
        varchar firebase_uid UK
        char email_hash
        char phone_hash
        varchar role
        varchar tier
        timestamptz created_at
        timestamptz updated_at
        timestamptz deleted_at
    }

    srv_sessions {
        uuid session_id PK
        uuid user_id FK
        uuid device_id FK
        timestamptz created_at
        timestamptz expires_at
        timestamptz last_activity
        timestamptz revoked_at
        varchar revoked_reason
        char ip_hash
        char user_agent_hash
        boolean mfa_verified
    }

    srv_devices {
        uuid device_id PK
        uuid user_id FK
        bytea device_name_enc
        varchar platform
        varchar os_version
        varchar app_version
        bytea push_token_enc
        boolean push_enabled
        timestamptz last_sync_at
        bigint sync_version
        timestamptz registered_at
        timestamptz last_seen_at
        timestamptz deactivated_at
    }

    srv_user_relations {
        uuid relation_id PK
        uuid owner_user_id FK
        uuid related_user_id FK
        varchar relation_type
        bytea permissions_enc
        timestamptz created_at
        uuid created_by FK
        timestamptz revoked_at
        uuid revoked_by FK
    }

    srv_invitations {
        uuid invitation_id PK
        uuid inviter_user_id FK
        char invitee_email_hash
        char invitee_phone_hash
        char token_hash
        varchar status
        bytea permissions_enc
        timestamptz created_at
        timestamptz expires_at
        timestamptz responded_at
        uuid accepted_user_id FK
    }

    srv_users ||--o{ srv_sessions : "has"
    srv_users ||--o{ srv_devices : "owns"
    srv_users ||--o{ srv_user_relations : "owner"
    srv_users ||--o{ srv_user_relations : "related"
    srv_users ||--o{ srv_invitations : "invites"
    srv_sessions }o--|| srv_devices : "from"

2.2. Servidor - Blobs Cifrados E2E

erDiagram
    srv_encrypted_blobs {
        uuid blob_id PK
        uuid user_id FK
        varchar entity_type "CHECK constraint"
        char entity_id_hash
        bytea blob_data
        char checksum
        bigint version
        timestamptz created_at
        timestamptz updated_at
    }

    srv_sync_metadata {
        uuid sync_id PK
        uuid blob_id FK
        uuid user_id FK
        varchar entity_type
        timestamptz client_timestamp
        timestamptz server_timestamp
        varchar sync_status
        jsonb conflict_data
    }

    srv_blob_versions {
        uuid version_id PK
        uuid blob_id FK
        bigint version_num
        bytea blob_data
        char checksum
        timestamptz created_at
    }

    srv_users ||--o{ srv_encrypted_blobs : "owns"
    srv_encrypted_blobs ||--|| srv_sync_metadata : "metadata"
    srv_encrypted_blobs ||--o{ srv_blob_versions : "versions"

CHECK Constraint para entity_type:

-- entity_type debe ser uno de los valores validos
ALTER TABLE srv_encrypted_blobs
ADD CONSTRAINT chk_entity_type CHECK (
    entity_type IN (
        'user_profile',
        'medical_profile',
        'medication',
        'schedule',
        'dose_log',
        'inventory',
        'prescription',
        'diagnosis',
        'appointment',
        'emergency_contact',
        'dependent',
        'caregiver_permission',
        'alert_config',
        'notification_history',
        'pattern_data',
        'insight'
    )
);

2.3. Servidor - Catalogos Publicos

erDiagram
    srv_drug_catalog {
        uuid drug_id PK
        varchar rxnorm_code UK
        varchar name_generic
        varchar name_brand
        varchar atc_code
        varchar category
        boolean requires_rx
        boolean controlled
        varchar source
        timestamptz updated_at
    }

    srv_drug_forms {
        uuid form_id PK
        uuid drug_id FK
        varchar form_name
        varchar route
        varchar strength
        varchar unit
    }

    srv_drug_interactions {
        uuid interaction_id PK
        uuid drug_a_id FK
        uuid drug_b_id FK
        varchar severity
        text description
        varchar evidence_level
        varchar source
        timestamptz updated_at
    }

    srv_drug_catalog ||--o{ srv_drug_forms : "has"
    srv_drug_catalog ||--o{ srv_drug_interactions : "drug_a"
    srv_drug_catalog ||--o{ srv_drug_interactions : "drug_b"

2.4. Servidor - Auditoria

erDiagram
    srv_audit_trail {
        uuid audit_id PK
        uuid user_id FK
        varchar entity_type
        char entity_id_hash
        varchar action
        char old_value_hash
        char new_value_hash
        char ip_hash
        char user_agent_hash
        timestamptz timestamp
        uuid request_id
    }

    srv_access_logs {
        uuid log_id PK
        uuid user_id FK
        uuid device_id FK
        varchar action
        char ip_hash
        char user_agent_hash
        timestamptz timestamp
        boolean success
        varchar error_code
    }

    srv_users ||--o{ srv_audit_trail : "audits"
    srv_users ||--o{ srv_access_logs : "logs"
    srv_devices ||--o{ srv_access_logs : "from"

2.5. Cliente - Usuario y Perfil

erDiagram
    cli_user {
        uuid user_id PK
        string email
        string display_name
        string phone
        string role
        string tier
        string photo_url
        string locale
        string timezone
        datetime created_at
        int64 sync_version
    }

    cli_medical_profile {
        uuid profile_id PK
        uuid user_id FK
        string blood_type
        json allergies
        json conditions
        date birth_date
        float weight_kg
        float height_cm
        string notes
        datetime updated_at
    }

    cli_emergency_contact {
        uuid contact_id PK
        uuid user_id FK
        string name
        string phone
        string relationship
        int priority
        datetime created_at
    }

    cli_dependents {
        uuid dependent_id PK
        uuid guardian_user_id FK
        string name
        date birth_date
        string relationship
        json medical_profile
        datetime created_at
    }

    cli_caregiver_perms {
        uuid permission_id PK
        uuid patient_user_id FK
        uuid caregiver_user_id FK
        string permission_type
        datetime granted_at
        datetime revoked_at
        uuid granted_by FK
    }

    cli_user ||--|| cli_medical_profile : "has"
    cli_user ||--o{ cli_emergency_contact : "has"
    cli_user ||--o{ cli_dependents : "manages"
    cli_user ||--o{ cli_caregiver_perms : "patient"
    cli_user ||--o{ cli_caregiver_perms : "caregiver"

2.6. Cliente - Medicamentos

erDiagram
    cli_medications {
        uuid medication_id PK
        uuid user_id FK
        uuid catalog_drug_id FK
        string custom_name
        string dosage
        string unit
        string form
        string route
        string instructions
        string prescriber
        string pharmacy
        boolean is_active
        datetime created_at
        int64 sync_version
    }

    cli_schedules {
        uuid schedule_id PK
        uuid medication_id FK
        string frequency_type
        int times_per_day
        json specific_times
        json days_of_week
        int interval_hours
        date start_date
        date end_date
        boolean is_active
        int reminder_offset
        datetime created_at
    }

    cli_dose_logs {
        uuid log_id PK
        uuid schedule_id FK
        datetime scheduled_time
        datetime actual_time
        string status
        string skipped_reason
        uuid confirmed_by FK
        float location_lat
        float location_lon
        string notes
        datetime created_at
    }

    cli_inventory {
        uuid inventory_id PK
        uuid medication_id FK
        float quantity_current
        float quantity_initial
        string unit
        date expiration_date
        string lot_number
        float low_stock_alert
        datetime updated_at
    }

    cli_refill_history {
        uuid refill_id PK
        uuid inventory_id FK
        float quantity_added
        string pharmacy
        float cost
        string prescription_num
        date refill_date
        datetime created_at
    }

    cli_user ||--o{ cli_medications : "has"
    cli_medications ||--o{ cli_schedules : "scheduled"
    cli_schedules ||--o{ cli_dose_logs : "logs"
    cli_medications ||--|| cli_inventory : "inventory"
    cli_inventory ||--o{ cli_refill_history : "refills"

2.7. Cliente - Recetas, Diagnosticos y Citas

erDiagram
    cli_prescriptions {
        uuid prescription_id PK
        uuid user_id FK
        string prescriber_name
        string prescriber_lic
        string institution
        date issue_date
        date expiry_date
        json medications
        string ocr_image_ref
        string ocr_raw_text
        boolean verified
        datetime created_at
        int64 sync_version
    }

    cli_diagnoses {
        uuid diagnosis_id PK
        uuid user_id FK
        string icd10_code
        string description
        date diagnosed_date
        string status
        string treating_doctor
        string notes
        datetime created_at
        int64 sync_version
    }

    cli_appointments {
        uuid appointment_id PK
        uuid user_id FK
        string title
        string doctor_name
        string specialty
        string location
        datetime scheduled_at
        int duration_min
        int reminder_before
        string status
        string notes
        datetime created_at
        int64 sync_version
    }

    cli_user ||--o{ cli_prescriptions : "has"
    cli_user ||--o{ cli_diagnoses : "has"
    cli_user ||--o{ cli_appointments : "has"

2.8. Cliente - Local Only

erDiagram
    cli_preferences {
        uuid preference_id PK
        uuid user_id FK
        string theme
        boolean notification_on
        string reminder_sound
        boolean reminder_vibrate
        string language
        boolean biometric_on
        string pin_hash
        datetime updated_at
    }

    cli_ui_cache {
        uuid cache_id PK
        string cache_key UK
        blob cache_value
        datetime expires_at
        datetime created_at
    }

    cli_sync_queue {
        uuid queue_id PK
        string entity_type
        uuid entity_id
        string operation
        bytea payload_enc
        string status
        int retry_count
        datetime created_at
        datetime last_attempt
    }

    cli_user ||--|| cli_preferences : "has"

---

3. Resumen de Entidades

3.1. Entidades del Servidor

Entidad	Tabla	Clasificacion	Contiene PHI	RLS
Usuario (metadata)	srv_users	SYNCED_HASH	No (solo hashes)	Si
Sesiones	srv_sessions	SYNCED_PLAIN	No	Si
Dispositivos	srv_devices	SYNCED_E2E	No (nombre cifrado)	Si
Relaciones Usuario	srv_user_relations	SYNCED_E2E	No (permisos cifrados)	Si
Invitaciones	srv_invitations	SYNCED_HASH	No (email hash)	Si
Blobs Cifrados	srv_encrypted_blobs	SYNCED_E2E	Si (opaco)	Si
Metadata Sync	srv_sync_metadata	SYNCED_PLAIN	No	Si
Versiones Blob	srv_blob_versions	SYNCED_E2E	Si (opaco)	Si
Catalogo Drogas	srv_drug_catalog	SERVER_SOURCE	No	No
Interacciones Drogas	srv_drug_interactions	SERVER_SOURCE	No	No
Formas Farmaceuticas	srv_drug_forms	SERVER_SOURCE	No	No
Audit Trail	srv_audit_trail	SERVER_SOURCE	No (hashes)	Si
Access Logs	srv_access_logs	SERVER_SOURCE	No (hashes)	Si

3.2. Entidades del Cliente

Entidad	Tabla	Clasificacion	Sync	Cifrado Local
Usuario	cli_user	SYNCED_E2E	Si	Si
Perfil Medico	cli_medical_profile	SYNCED_E2E	Si	Si
Contacto Emergencia	cli_emergency_contact	SYNCED_E2E	Si	Si
Dependientes	cli_dependents	SYNCED_E2E	Si	Si
Permisos Cuidador	cli_caregiver_perms	SYNCED_E2E	Si	Si
Medicamentos	cli_medications	SYNCED_E2E	Si	Si
Horarios	cli_schedules	SYNCED_E2E	Si	Si
Logs de Dosis	cli_dose_logs	SYNCED_E2E	Si	Si
Inventario	cli_inventory	SYNCED_E2E	Si	Si
Historial Resurtido	cli_refill_history	SYNCED_E2E	Si	Si
Recetas	cli_prescriptions	SYNCED_E2E	Si	Si
Diagnosticos	cli_diagnoses	SYNCED_E2E	Si	Si
Citas	cli_appointments	SYNCED_E2E	Si	Si
Preferencias	cli_preferences	LOCAL_ONLY	No	Si
Cache UI	cli_ui_cache	LOCAL_ONLY	No	No
Cola Sync	cli_sync_queue	LOCAL_ONLY	No	Si

---

4. Relaciones Principales

4.1. Relaciones de Usuario (Servidor)

flowchart TD
    subgraph Servidor
        U[srv_users] -->|1:N| S[srv_sessions]
        U -->|1:N| D[srv_devices]
        U -->|1:N owner| R[srv_user_relations]
        U -->|1:N related| R
        U -->|1:N| I[srv_invitations]
        U -->|1:N| B[srv_encrypted_blobs]
        U -->|1:N| AT[srv_audit_trail]
        U -->|1:N| AL[srv_access_logs]
        S -->|N:1| D
        D -->|1:N| AL
    end

4.2. Relaciones de Medicamentos (Cliente)

flowchart TD
    subgraph Cliente
        CU[cli_user] -->|1:N| M[cli_medications]
        M -->|1:N| SC[cli_schedules]
        SC -->|1:N| DL[cli_dose_logs]
        M -->|1:1| INV[cli_inventory]
        INV -->|1:N| RH[cli_refill_history]
    end

4.3. Relaciones de Catalogo (Servidor)

flowchart TD
    subgraph Catalogos_Publicos
        DC[srv_drug_catalog] -->|1:N| DF[srv_drug_forms]
        DC -->|M:N drug_a| DI[srv_drug_interactions]
        DC -->|M:N drug_b| DI
    end

---

5. Cardinalidades

Relacion	Cardinalidad	Descripcion
user → sessions	1:N	Un usuario puede tener multiples sesiones activas
user → devices	1:N	Un usuario puede tener multiples dispositivos (segun tier)
user → medications	1:N	Un usuario puede tener multiples medicamentos
medication → schedules	1:N	Un medicamento puede tener multiples horarios
schedule → dose_logs	1:N	Un horario genera multiples logs de dosis
medication → inventory	1:1	Un medicamento tiene un inventario
inventory → refill_history	1:N	Un inventario tiene historial de resurtidos
user → dependents	1:N	Un CR puede tener multiples dependientes
user → caregiver_perms	M:N	Relacion muchos a muchos paciente-cuidador
drug → interactions	M:N	Interacciones son bidireccionales entre drogas

---

6. Indices Recomendados

6.1. Indices del Servidor

Tabla	Indice	Tipo	Proposito
srv_users	idx_users_firebase_uid	UNIQUE	Lookup por Firebase UID
srv_users	idx_users_email_hash	BTREE	Blind index para email
srv_users	idx_users_phone_hash	BTREE	Blind index para telefono
srv_sessions	idx_sessions_user_device	COMPOSITE	Lookup sesiones por usuario/dispositivo
srv_sessions	idx_sessions_expires	BTREE	Limpieza de sesiones expiradas
srv_encrypted_blobs	idx_blobs_user_type	COMPOSITE	Filtrar blobs por usuario y tipo
srv_encrypted_blobs	idx_blobs_entity_hash	BTREE	Lookup por entity_id hash
srv_sync_metadata	idx_sync_user_timestamp	COMPOSITE	Sincronizacion incremental
srv_audit_trail	idx_audit_user_timestamp	COMPOSITE	Queries de auditoria
srv_drug_catalog	idx_drugs_rxnorm	UNIQUE	Lookup por codigo RxNorm
srv_drug_catalog	idx_drugs_name_gin	GIN	Busqueda full-text en nombres

6.2. Indices del Cliente

Tabla	Indice	Proposito
cli_medications	idx_med_user_active	Medicamentos activos del usuario
cli_schedules	idx_sched_med_active	Horarios activos por medicamento
cli_dose_logs	idx_logs_sched_date	Logs por horario y fecha
cli_appointments	idx_appt_user_date	Citas por usuario y fecha
cli_sync_queue	idx_queue_status	Operaciones pendientes de sync

---

7. Referencias Cruzadas

Documento	Relacion
MDL-AUTH-001	Modelo detallado de autenticacion
MDL-MED-001	Modelo detallado de medicamentos
MDL-USR-001	Modelo detallado de usuarios
03-estrategia-datos.md	Clasificacion y sincronizacion
04-seguridad-cliente.md	Cifrado E2E cliente
05-seguridad-servidor.md	RLS y auditoria servidor
MTS-AUTH-001	Especificacion funcional AUTH
MTS-MED-001	Especificacion funcional MED
MTS-USR-001	Especificacion funcional USR

---

Documento generado por DatabaseDrone - SpecQueen Technical Division "Los datos bien modelados son datos bien protegidos."