Saltar a contenido

Modelo de Datos: Calendario de Medicamentos

Identificador: MDL-CAL-001 Version: 1.0.0 Fecha: 2025-12-08 Estado: Borrador Autor: DatabaseDrone (Doce de Quince) / SpecQueen Technical Division Modulo Funcional: MTS-CAL-001

1. Introduccion

Este documento define el modelo de datos para el modulo de Calendario de Medicamentos de MedTime. El calendario gestiona eventos de toma, habitos del usuario, momentos del dia personalizados y modo viaje.

1.1. Principios

Principio Aplicacion
95% Local Calculo de horarios, agrupacion, alertas 100% en dispositivo
Zero-Knowledge Servidor solo almacena blobs cifrados de eventos
Offline-First Calendario funciona completamente sin conexion
Sync para Pro/Perfect Solo usuarios Pro/Perfect sincronizan calendario

1.2. Clasificacion de Datos

Entidad Clasificacion Cifrado Sync Razon
cli_user_habits SYNCED_E2E Si Pro/Perfect Preferencias personales revelan rutina
cli_day_moments SYNCED_E2E Si Pro/Perfect Momentos personalizados son rutina del usuario
cli_scheduled_doses SYNCED_E2E Si Pro/Perfect Contiene referencias a medicamentos (PHI)
cli_dose_events SYNCED_E2E Si Pro/Perfect Agrupaciones de tomas (PHI)
cli_dose_records SYNCED_E2E Si Pro/Perfect Historial de tomas es PHI critico
cli_schedule_adjustments SYNCED_E2E Si Pro/Perfect Cambios de horario revelan comportamiento
cli_travel_mode LOCAL_ONLY Si No Configuracion temporal, no requiere sync
srv_calendar_sync SERVER_ONLY N/A N/A Almacena blobs cifrados
srv_caregiver_calendar_access SERVER_ONLY No N/A Metadata de permisos (no PHI)

2. Entidades del Cliente (LOCAL/SYNCED_E2E)

2.1. cli_user_habits

Habitos del usuario (horas de despertar, comidas, dormir) para calcular horarios optimos.

2.1.1. Clasificacion

Campo Clasificacion Razon
userId LOCAL_ONLY Identificador local
wakeUpTime SYNCED_E2E Revela rutina diaria
breakfastTime SYNCED_E2E Revela rutina diaria
lunchTime SYNCED_E2E Revela rutina diaria
dinnerTime SYNCED_E2E Revela rutina diaria
bedTime SYNCED_E2E Revela rutina diaria
workDays SYNCED_E2E Revela patron laboral
hasFlexibleSchedule SYNCED_E2E Revela estilo de vida

2.1.2. iOS - Swift/Realm

// ============================================================
// MODELO: UserHabits
// Descripcion: Habitos del usuario para calculo de horarios
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

class UserHabits: Object, Identifiable {
    // Identificadores
    @Persisted(primaryKey: true) var id: String = "user_habits"
    @Persisted var userId: String = ""

    // Tipo de dia (pueden tener habitos diferentes)
    @Persisted var dayType: String = "weekday"  // weekday, weekend

    // Horarios habituales (formato HH:mm)
    @Persisted var wakeUpTime: String = "07:00"
    @Persisted var breakfastTime: String? = "08:00"
    @Persisted var lunchTime: String? = "14:00"
    @Persisted var dinnerTime: String? = "20:00"
    @Persisted var bedTime: String = "22:00"

    // Dias laborales (0=Domingo, 1=Lunes, ..., 6=Sabado)
    @Persisted var workDays: List<Int>  // [1,2,3,4,5] por defecto

    // Flexibilidad
    @Persisted var hasFlexibleSchedule: Bool = false
    @Persisted var typicalVariationMinutes: Int = 30  // Variacion tipica

    // Sync
    @Persisted var syncStatus: String = "pending"  // pending, synced, conflict
    @Persisted var lastSyncAt: Date?
    @Persisted var version: Int = 1

    // Timestamps
    @Persisted var createdAt: Date = Date()
    @Persisted var updatedAt: Date = Date()
    @Persisted var isDeleted: Bool = false
}

2.1.3. Android - Kotlin/Room

// ============================================================
// MODELO: UserHabits
// Descripcion: Habitos del usuario para calculo de horarios
// Almacenamiento: Room cifrado (SQLCipher)
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

package com.medtime.data.entities

import androidx.room.*
import java.time.Instant

@Entity(
    tableName = "cli_user_habits",
    indices = [
        Index(value = ["user_id", "day_type"], unique = true)
    ]
)
data class UserHabits(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = "user_habits",

    @ColumnInfo(name = "user_id")
    val userId: String = "",

    // Tipo de dia
    @ColumnInfo(name = "day_type")
    val dayType: DayType = DayType.WEEKDAY,

    // Horarios (formato HH:mm)
    @ColumnInfo(name = "wake_up_time")
    val wakeUpTime: String = "07:00",

    @ColumnInfo(name = "breakfast_time")
    val breakfastTime: String? = "08:00",

    @ColumnInfo(name = "lunch_time")
    val lunchTime: String? = "14:00",

    @ColumnInfo(name = "dinner_time")
    val dinnerTime: String? = "20:00",

    @ColumnInfo(name = "bed_time")
    val bedTime: String = "22:00",

    // Dias laborales
    @ColumnInfo(name = "work_days")
    val workDays: List<Int> = listOf(1, 2, 3, 4, 5),  // TypeConverter

    // Flexibilidad
    @ColumnInfo(name = "has_flexible_schedule")
    val hasFlexibleSchedule: Boolean = false,

    @ColumnInfo(name = "typical_variation_minutes")
    val typicalVariationMinutes: Int = 30,

    // Sync
    @ColumnInfo(name = "sync_status")
    val syncStatus: SyncStatus = SyncStatus.PENDING,

    @ColumnInfo(name = "last_sync_at")
    val lastSyncAt: Instant? = null,

    @ColumnInfo(name = "version")
    val version: Int = 1,

    // Timestamps
    @ColumnInfo(name = "created_at")
    val createdAt: Instant = Instant.now(),

    @ColumnInfo(name = "updated_at")
    val updatedAt: Instant = Instant.now(),

    @ColumnInfo(name = "is_deleted")
    val isDeleted: Boolean = false
)

enum class DayType {
    WEEKDAY,
    WEEKEND
}

2.2. cli_day_moments

Momentos del dia personalizados (desayuno, almuerzo, cena, etc.) basados en habitos.

2.2.1. Clasificacion

Campo Clasificacion Razon
momentType SYNCED_E2E Tipo de momento es parte de rutina
scheduledTime SYNCED_E2E Hora especifica revela rutina
windowMinutes SYNCED_E2E Ventana de flexibilidad es patron personal
customName SYNCED_E2E Nombres personalizados revelan rutina

2.2.2. iOS - Swift/Realm

// ============================================================
// MODELO: DayMoment
// Descripcion: Momentos del dia personalizados
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

class DayMoment: Object, Identifiable {
    // Identificadores
    @Persisted(primaryKey: true) var id: String = UUID().uuidString
    @Persisted var userId: String = ""

    // Tipo de momento (predefinidos)
    @Persisted var momentType: String = ""
    // wake_up, breakfast, mid_morning, lunch, snack, dinner, bedtime, night

    // Configuracion
    @Persisted var scheduledTime: String = ""  // HH:mm
    @Persisted var windowMinutes: Int = 30  // Ventana de tiempo

    // Personalizacion
    @Persisted var customName: String?  // "Mi desayuno", "Comida en oficina"
    @Persisted var isActive: Bool = true
    @Persisted var displayOrder: Int = 0  // Orden en UI

    // Restricciones de medicamentos
    @Persisted var allowsWithFood: Bool = true
    @Persisted var allowsWithoutFood: Bool = true
    @Persisted var allowsFasting: Bool = false

    // Sync
    @Persisted var syncStatus: String = "pending"
    @Persisted var lastSyncAt: Date?
    @Persisted var version: Int = 1

    // Timestamps
    @Persisted var createdAt: Date = Date()
    @Persisted var updatedAt: Date = Date()
    @Persisted var isDeleted: Bool = false

    // Computed
    var displayName: String {
        return customName ?? momentType.capitalized
    }
}

2.2.3. Android - Kotlin/Room

// ============================================================
// MODELO: DayMoment
// Descripcion: Momentos del dia personalizados
// Almacenamiento: Room cifrado (SQLCipher)
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

package com.medtime.data.entities

import androidx.room.*
import java.time.Instant
import java.util.UUID

@Entity(
    tableName = "cli_day_moments",
    indices = [
        Index(value = ["user_id", "moment_type"]),
        Index(value = ["is_active"])
    ]
)
data class DayMoment(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

    @ColumnInfo(name = "user_id")
    val userId: String = "",

    // Tipo de momento
    @ColumnInfo(name = "moment_type")
    val momentType: MomentType = MomentType.BREAKFAST,

    // Configuracion
    @ColumnInfo(name = "scheduled_time")
    val scheduledTime: String = "",  // HH:mm

    @ColumnInfo(name = "window_minutes")
    val windowMinutes: Int = 30,

    // Personalizacion
    @ColumnInfo(name = "custom_name")
    val customName: String? = null,

    @ColumnInfo(name = "is_active")
    val isActive: Boolean = true,

    @ColumnInfo(name = "display_order")
    val displayOrder: Int = 0,

    // Restricciones
    @ColumnInfo(name = "allows_with_food")
    val allowsWithFood: Boolean = true,

    @ColumnInfo(name = "allows_without_food")
    val allowsWithoutFood: Boolean = true,

    @ColumnInfo(name = "allows_fasting")
    val allowsFasting: Boolean = false,

    // Sync
    @ColumnInfo(name = "sync_status")
    val syncStatus: SyncStatus = SyncStatus.PENDING,

    @ColumnInfo(name = "last_sync_at")
    val lastSyncAt: Instant? = null,

    @ColumnInfo(name = "version")
    val version: Int = 1,

    // Timestamps
    @ColumnInfo(name = "created_at")
    val createdAt: Instant = Instant.now(),

    @ColumnInfo(name = "updated_at")
    val updatedAt: Instant = Instant.now(),

    @ColumnInfo(name = "is_deleted")
    val isDeleted: Boolean = false
)

enum class MomentType {
    WAKE_UP,
    BREAKFAST,
    MID_MORNING,
    LUNCH,
    SNACK,
    DINNER,
    BEDTIME,
    NIGHT
}

2.3. cli_scheduled_doses

Tomas programadas individuales (antes de agrupar en eventos).

2.3.1. Clasificacion

Campo Clasificacion Razon
medicationId SYNCED_E2E Referencia a medicamento (PHI)
scheduleId SYNCED_E2E Referencia a horario (PHI)
scheduledTime SYNCED_E2E Hora especifica de toma (PHI)
dosage SYNCED_E2E Dosis a tomar (PHI)
instructions SYNCED_E2E Instrucciones de toma (PHI)

2.3.2. iOS - Swift/Realm

// ============================================================
// MODELO: ScheduledDose
// Descripcion: Toma programada individual
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

class ScheduledDose: Object, Identifiable {
    // Identificadores
    @Persisted(primaryKey: true) var id: String = UUID().uuidString
    @Persisted var userId: String = ""

    // Referencias
    @Persisted var medicationId: String = ""  // FK a cli_medications
    @Persisted var scheduleId: String = ""    // FK a cli_schedules

    // Fecha y hora
    @Persisted var date: Date = Date()
    @Persisted var scheduledTime: Date = Date()  // Timestamp completo

    // Ventana de toma
    @Persisted var windowStartMinutes: Int = -60  // -60 min (1 hora antes)
    @Persisted var windowEndMinutes: Int = 120    // +120 min (2 horas despues)

    // Detalles de la dosis
    @Persisted var dosage: String = ""
    @Persisted var unit: String = ""
    @Persisted var instructions: String?

    // Relacion con momento del dia (opcional)
    @Persisted var momentId: String?  // FK a cli_day_moments

    // Estado
    @Persisted var status: String = "pending"
    // pending, taken, skipped, missed, postponed

    @Persisted var isActive: Bool = true

    // Sync
    @Persisted var syncStatus: String = "pending"
    @Persisted var lastSyncAt: Date?
    @Persisted var version: Int = 1

    // Timestamps
    @Persisted var createdAt: Date = Date()
    @Persisted var updatedAt: Date = Date()
    @Persisted var isDeleted: Bool = false

    // Relationships (via LinkingObjects)
    @Persisted(originProperty: "scheduledDoses") var event: LinkingObjects<DoseEvent>
    @Persisted(originProperty: "scheduledDose") var record: LinkingObjects<DoseRecord>
}

2.3.3. Android - Kotlin/Room

// ============================================================
// MODELO: ScheduledDose
// Descripcion: Toma programada individual
// Almacenamiento: Room cifrado (SQLCipher)
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

package com.medtime.data.entities

import androidx.room.*
import java.time.Instant
import java.time.LocalDate
import java.util.UUID

@Entity(
    tableName = "cli_scheduled_doses",
    foreignKeys = [
        ForeignKey(
            entity = Medication::class,
            parentColumns = ["medication_id"],
            childColumns = ["medication_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [
        Index(value = ["medication_id"]),
        Index(value = ["date", "scheduled_time"]),
        Index(value = ["status"]),
        Index(value = ["moment_id"])
    ]
)
data class ScheduledDose(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

    @ColumnInfo(name = "user_id")
    val userId: String = "",

    // Referencias
    @ColumnInfo(name = "medication_id", index = true)
    val medicationId: String = "",

    @ColumnInfo(name = "schedule_id")
    val scheduleId: String = "",

    // Fecha y hora
    @ColumnInfo(name = "date")
    val date: LocalDate = LocalDate.now(),

    @ColumnInfo(name = "scheduled_time")
    val scheduledTime: Instant = Instant.now(),

    // Ventana de toma
    @ColumnInfo(name = "window_start_minutes")
    val windowStartMinutes: Int = -60,

    @ColumnInfo(name = "window_end_minutes")
    val windowEndMinutes: Int = 120,

    // Detalles de la dosis
    @ColumnInfo(name = "dosage")
    val dosage: String = "",

    @ColumnInfo(name = "unit")
    val unit: String = "",

    @ColumnInfo(name = "instructions")
    val instructions: String? = null,

    // Relacion con momento del dia
    @ColumnInfo(name = "moment_id")
    val momentId: String? = null,

    // Estado
    @ColumnInfo(name = "status")
    val status: DoseStatus = DoseStatus.PENDING,

    @ColumnInfo(name = "is_active")
    val isActive: Boolean = true,

    // Sync
    @ColumnInfo(name = "sync_status")
    val syncStatus: SyncStatus = SyncStatus.PENDING,

    @ColumnInfo(name = "last_sync_at")
    val lastSyncAt: Instant? = null,

    @ColumnInfo(name = "version")
    val version: Int = 1,

    // Timestamps
    @ColumnInfo(name = "created_at")
    val createdAt: Instant = Instant.now(),

    @ColumnInfo(name = "updated_at")
    val updatedAt: Instant = Instant.now(),

    @ColumnInfo(name = "is_deleted")
    val isDeleted: Boolean = false
)

enum class DoseStatus {
    PENDING,
    TAKEN,
    SKIPPED,
    MISSED,
    POSTPONED
}

2.4. cli_dose_events

Eventos que agrupan multiples tomas programadas en un mismo momento.

2.4.1. Clasificacion

Campo Clasificacion Razon
scheduledDoseIds SYNCED_E2E Lista de tomas agrupadas (PHI)
name SYNCED_E2E Nombre puede contener info de medicamentos
eventTime SYNCED_E2E Hora especifica revela rutina

NOTA PADDING: El campo scheduledDoseIds es una lista de longitud variable que DEBE aplicar padding antes del cifrado E2E para evitar metadata attacks. Ver seccion 6.3.

2.4.2. iOS - Swift/Realm

// ============================================================
// MODELO: DoseEvent
// Descripcion: Agrupacion de tomas en un mismo momento
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

class DoseEvent: Object, Identifiable {
    // Identificadores
    @Persisted(primaryKey: true) var id: String = UUID().uuidString
    @Persisted var userId: String = ""

    // Fecha y hora del evento
    @Persisted var date: Date = Date()
    @Persisted var eventTime: Date = Date()  // Hora central del evento

    // Momento del dia asociado (opcional)
    @Persisted var momentId: String?  // FK a cli_day_moments

    // Nombre del evento
    @Persisted var name: String = ""  // "Medicinas del desayuno"
    @Persisted var isCustomName: Bool = false

    // Ventana del evento (consolidada)
    @Persisted var windowStart: Date = Date()
    @Persisted var windowEnd: Date = Date()

    // Tomas incluidas en este evento
    @Persisted var scheduledDoses: List<ScheduledDose>

    // Estado consolidado
    @Persisted var status: String = "pending"
    // pending, completed, partial, skipped, missed

    @Persisted var completedDoses: Int = 0
    @Persisted var totalDoses: Int = 0

    // Confirmacion
    @Persisted var confirmedAt: Date?
    @Persisted var confirmedBy: String?  // user_id

    // Sync
    @Persisted var syncStatus: String = "pending"
    @Persisted var lastSyncAt: Date?
    @Persisted var version: Int = 1

    // Timestamps
    @Persisted var createdAt: Date = Date()
    @Persisted var updatedAt: Date = Date()
    @Persisted var isDeleted: Bool = false

    // Computed
    var isComplete: Bool {
        return completedDoses == totalDoses && totalDoses > 0
    }

    var isOverdue: Bool {
        return windowEnd < Date() && status == "pending"
    }
}

2.4.3. Android - Kotlin/Room

// ============================================================
// MODELO: DoseEvent
// Descripcion: Agrupacion de tomas en un mismo momento
// Almacenamiento: Room cifrado (SQLCipher)
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

package com.medtime.data.entities

import androidx.room.*
import java.time.Instant
import java.time.LocalDate
import java.util.UUID

@Entity(
    tableName = "cli_dose_events",
    indices = [
        Index(value = ["user_id", "date"]),
        Index(value = ["event_time"]),
        Index(value = ["moment_id"]),
        Index(value = ["status"])
    ]
)
data class DoseEvent(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

    @ColumnInfo(name = "user_id")
    val userId: String = "",

    // Fecha y hora
    @ColumnInfo(name = "date")
    val date: LocalDate = LocalDate.now(),

    @ColumnInfo(name = "event_time")
    val eventTime: Instant = Instant.now(),

    // Momento del dia
    @ColumnInfo(name = "moment_id")
    val momentId: String? = null,

    // Nombre
    @ColumnInfo(name = "name")
    val name: String = "",

    @ColumnInfo(name = "is_custom_name")
    val isCustomName: Boolean = false,

    // Ventana
    @ColumnInfo(name = "window_start")
    val windowStart: Instant = Instant.now(),

    @ColumnInfo(name = "window_end")
    val windowEnd: Instant = Instant.now(),

    // Estado consolidado
    @ColumnInfo(name = "status")
    val status: EventStatus = EventStatus.PENDING,

    @ColumnInfo(name = "completed_doses")
    val completedDoses: Int = 0,

    @ColumnInfo(name = "total_doses")
    val totalDoses: Int = 0,

    // Confirmacion
    @ColumnInfo(name = "confirmed_at")
    val confirmedAt: Instant? = null,

    @ColumnInfo(name = "confirmed_by")
    val confirmedBy: String? = null,

    // Sync
    @ColumnInfo(name = "sync_status")
    val syncStatus: SyncStatus = SyncStatus.PENDING,

    @ColumnInfo(name = "last_sync_at")
    val lastSyncAt: Instant? = null,

    @ColumnInfo(name = "version")
    val version: Int = 1,

    // Timestamps
    @ColumnInfo(name = "created_at")
    val createdAt: Instant = Instant.now(),

    @ColumnInfo(name = "updated_at")
    val updatedAt: Instant = Instant.now(),

    @ColumnInfo(name = "is_deleted")
    val isDeleted: Boolean = false
)

enum class EventStatus {
    PENDING,
    COMPLETED,
    PARTIAL,
    SKIPPED,
    MISSED
}

// Relacion many-to-many con ScheduledDose
@Entity(
    tableName = "cli_event_doses",
    primaryKeys = ["event_id", "dose_id"],
    foreignKeys = [
        ForeignKey(
            entity = DoseEvent::class,
            parentColumns = ["id"],
            childColumns = ["event_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = ScheduledDose::class,
            parentColumns = ["id"],
            childColumns = ["dose_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [
        Index(value = ["event_id"]),
        Index(value = ["dose_id"])
    ]
)
data class EventDoseCrossRef(
    @ColumnInfo(name = "event_id")
    val eventId: String,

    @ColumnInfo(name = "dose_id")
    val doseId: String,

    @ColumnInfo(name = "order")
    val order: Int = 0
)

2.5. cli_dose_records

Registro de tomas confirmadas/omitidas/pospuestas.

2.5.1. Clasificacion

Campo Clasificacion Razon
scheduledDoseId SYNCED_E2E Referencia a toma (PHI)
actualTime SYNCED_E2E Hora real de toma (PHI)
skipReason SYNCED_E2E Razon de omision (PHI)
notes SYNCED_E2E Notas pueden contener info medica (PHI)
location LOCAL_ONLY Datos de ubicacion NUNCA salen del dispositivo

2.5.2. iOS - Swift/Realm

// ============================================================
// MODELO: DoseRecord
// Descripcion: Registro de toma realizada/omitida
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

class DoseRecord: Object, Identifiable {
    // Identificadores
    @Persisted(primaryKey: true) var id: String = UUID().uuidString
    @Persisted var userId: String = ""

    // Referencia a toma programada
    @Persisted(originProperty: "record") var scheduledDose: LinkingObjects<ScheduledDose>
    @Persisted var scheduledDoseId: String = ""

    // Tiempos
    @Persisted var scheduledTime: Date = Date()  // Hora programada
    @Persisted var actualTime: Date?             // Hora real (si tomado)
    @Persisted var recordedAt: Date = Date()     // Cuando se registro

    // Estado
    @Persisted var status: String = "taken"
    // taken, skipped, missed, postponed

    // Detalles si skipped
    @Persisted var skipReason: String?
    // forgot, no_supply, side_effects, felt_sick, doctor_order, other

    @Persisted var notes: String?

    // Confirmacion
    @Persisted var confirmedBy: String?  // user_id (paciente o cuidador)
    @Persisted var confirmedByRole: String?  // PI, PD, CR, CS

    // Ubicacion (LOCAL_ONLY - NUNCA se sincroniza)
    @Persisted var locationLat: Double?
    @Persisted var locationLon: Double?
    @Persisted var locationAccuracy: Double?

    // Metricas
    @Persisted var delayMinutes: Int?  // Minutos de retraso (+) o adelanto (-)
    @Persisted var wasWithinWindow: Bool = false

    // Sync
    @Persisted var syncStatus: String = "pending"
    @Persisted var lastSyncAt: Date?
    @Persisted var version: Int = 1

    // Timestamps
    @Persisted var createdAt: Date = Date()
    @Persisted var isDeleted: Bool = false
}

2.5.3. Android - Kotlin/Room

// ============================================================
// MODELO: DoseRecord
// Descripcion: Registro de toma realizada/omitida
// Almacenamiento: Room cifrado (SQLCipher)
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

package com.medtime.data.entities

import androidx.room.*
import java.time.Instant
import java.util.UUID

@Entity(
    tableName = "cli_dose_records",
    foreignKeys = [
        ForeignKey(
            entity = ScheduledDose::class,
            parentColumns = ["id"],
            childColumns = ["scheduled_dose_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [
        Index(value = ["scheduled_dose_id"]),
        Index(value = ["status"]),
        Index(value = ["recorded_at"])
    ]
)
data class DoseRecord(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

    @ColumnInfo(name = "user_id")
    val userId: String = "",

    // Referencia
    @ColumnInfo(name = "scheduled_dose_id", index = true)
    val scheduledDoseId: String = "",

    // Tiempos
    @ColumnInfo(name = "scheduled_time")
    val scheduledTime: Instant = Instant.now(),

    @ColumnInfo(name = "actual_time")
    val actualTime: Instant? = null,

    @ColumnInfo(name = "recorded_at")
    val recordedAt: Instant = Instant.now(),

    // Estado
    @ColumnInfo(name = "status")
    val status: RecordStatus = RecordStatus.TAKEN,

    // Detalles si skipped
    @ColumnInfo(name = "skip_reason")
    val skipReason: SkipReason? = null,

    @ColumnInfo(name = "notes")
    val notes: String? = null,

    // Confirmacion
    @ColumnInfo(name = "confirmed_by")
    val confirmedBy: String? = null,

    @ColumnInfo(name = "confirmed_by_role")
    val confirmedByRole: String? = null,

    // Ubicacion (LOCAL_ONLY - NUNCA se sincroniza)
    @ColumnInfo(name = "location_lat")
    val locationLat: Double? = null,

    @ColumnInfo(name = "location_lon")
    val locationLon: Double? = null,

    @ColumnInfo(name = "location_accuracy")
    val locationAccuracy: Double? = null,

    // Metricas
    @ColumnInfo(name = "delay_minutes")
    val delayMinutes: Int? = null,

    @ColumnInfo(name = "was_within_window")
    val wasWithinWindow: Boolean = false,

    // Sync
    @ColumnInfo(name = "sync_status")
    val syncStatus: SyncStatus = SyncStatus.PENDING,

    @ColumnInfo(name = "last_sync_at")
    val lastSyncAt: Instant? = null,

    @ColumnInfo(name = "version")
    val version: Int = 1,

    // Timestamps
    @ColumnInfo(name = "created_at")
    val createdAt: Instant = Instant.now(),

    @ColumnInfo(name = "is_deleted")
    val isDeleted: Boolean = false
)

enum class RecordStatus {
    TAKEN,
    SKIPPED,
    MISSED,
    POSTPONED
}

enum class SkipReason {
    FORGOT,
    NO_SUPPLY,
    SIDE_EFFECTS,
    FELT_SICK,
    DOCTOR_ORDER,
    OTHER
}

2.6. cli_schedule_adjustments

Historial de ajustes de horario (temporal o permanente).

2.6.1. Clasificacion

Campo Clasificacion Razon
medicationId SYNCED_E2E Referencia a medicamento (PHI)
oldTime SYNCED_E2E Horario anterior revela rutina
newTime SYNCED_E2E Horario nuevo revela rutina
reason SYNCED_E2E Razon puede contener info medica

2.6.2. iOS - Swift/Realm

// ============================================================
// MODELO: ScheduleAdjustment
// Descripcion: Historial de ajustes de horario
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

class ScheduleAdjustment: Object, Identifiable {
    // Identificadores
    @Persisted(primaryKey: true) var id: String = UUID().uuidString
    @Persisted var userId: String = ""

    // Referencia
    @Persisted var medicationId: String = ""
    @Persisted var scheduleId: String = ""

    // Tipo de ajuste
    @Persisted var adjustmentType: String = "permanent"
    // permanent, temporary, one_time

    // Cambios
    @Persisted var oldTime: String = ""  // HH:mm
    @Persisted var newTime: String = ""  // HH:mm

    // Vigencia (para temporary)
    @Persisted var effectiveFrom: Date?
    @Persisted var effectiveUntil: Date?

    // Razon del cambio
    @Persisted var reason: String?
    // lifestyle_change, side_effects, doctor_recommendation, other

    @Persisted var notes: String?

    // Estado
    @Persisted var isActive: Bool = true
    @Persisted var wasApplied: Bool = false

    // Sync
    @Persisted var syncStatus: String = "pending"
    @Persisted var lastSyncAt: Date?
    @Persisted var version: Int = 1

    // Timestamps
    @Persisted var createdAt: Date = Date()
    @Persisted var appliedAt: Date?
    @Persisted var isDeleted: Bool = false
}

2.6.3. Android - Kotlin/Room

// ============================================================
// MODELO: ScheduleAdjustment
// Descripcion: Historial de ajustes de horario
// Almacenamiento: Room cifrado (SQLCipher)
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

package com.medtime.data.entities

import androidx.room.*
import java.time.Instant
import java.util.UUID

@Entity(
    tableName = "cli_schedule_adjustments",
    indices = [
        Index(value = ["medication_id"]),
        Index(value = ["schedule_id"]),
        Index(value = ["is_active"])
    ]
)
data class ScheduleAdjustment(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

    @ColumnInfo(name = "user_id")
    val userId: String = "",

    // Referencia
    @ColumnInfo(name = "medication_id", index = true)
    val medicationId: String = "",

    @ColumnInfo(name = "schedule_id")
    val scheduleId: String = "",

    // Tipo
    @ColumnInfo(name = "adjustment_type")
    val adjustmentType: AdjustmentType = AdjustmentType.PERMANENT,

    // Cambios
    @ColumnInfo(name = "old_time")
    val oldTime: String = "",  // HH:mm

    @ColumnInfo(name = "new_time")
    val newTime: String = "",  // HH:mm

    // Vigencia
    @ColumnInfo(name = "effective_from")
    val effectiveFrom: Instant? = null,

    @ColumnInfo(name = "effective_until")
    val effectiveUntil: Instant? = null,

    // Razon
    @ColumnInfo(name = "reason")
    val reason: AdjustmentReason? = null,

    @ColumnInfo(name = "notes")
    val notes: String? = null,

    // Estado
    @ColumnInfo(name = "is_active")
    val isActive: Boolean = true,

    @ColumnInfo(name = "was_applied")
    val wasApplied: Boolean = false,

    // Sync
    @ColumnInfo(name = "sync_status")
    val syncStatus: SyncStatus = SyncStatus.PENDING,

    @ColumnInfo(name = "last_sync_at")
    val lastSyncAt: Instant? = null,

    @ColumnInfo(name = "version")
    val version: Int = 1,

    // Timestamps
    @ColumnInfo(name = "created_at")
    val createdAt: Instant = Instant.now(),

    @ColumnInfo(name = "applied_at")
    val appliedAt: Instant? = null,

    @ColumnInfo(name = "is_deleted")
    val isDeleted: Boolean = false
)

enum class AdjustmentType {
    PERMANENT,
    TEMPORARY,
    ONE_TIME
}

enum class AdjustmentReason {
    LIFESTYLE_CHANGE,
    SIDE_EFFECTS,
    DOCTOR_RECOMMENDATION,
    OTHER
}

2.7. cli_travel_mode

Configuracion de modo viaje para ajuste de zonas horarias.

2.7.1. Clasificacion

Campo Clasificacion Razon
ALL LOCAL_ONLY Configuracion temporal, no requiere sync entre dispositivos

NOTA: Esta entidad esLOCAL_ONLY porque es una configuracion temporal que no necesita sincronizarse. Cada dispositivo puede tener su propio modo viaje activo (ej: usuario con iPad en casa y iPhone viajando).

2.7.2. iOS - Swift/Realm

// ============================================================
// MODELO: TravelMode
// Descripcion: Configuracion de modo viaje
// Almacenamiento: Realm cifrado
// Sync: LOCAL_ONLY - NO se sincroniza
// ============================================================

import RealmSwift

class TravelMode: Object, Identifiable {
    // Identificadores
    @Persisted(primaryKey: true) var id: String = "travel_mode"
    @Persisted var userId: String = ""

    // Estado
    @Persisted var isActive: Bool = false

    // Zona horaria
    @Persisted var homeTimezone: String = ""  // "America/Mexico_City"
    @Persisted var currentTimezone: String = ""  // "Europe/Madrid"
    @Persisted var timezoneOffsetMinutes: Int = 0  // Diferencia en minutos

    // Estrategia de ajuste
    @Persisted var strategy: String = "maintain_local"
    // maintain_local, maintain_interval, gradual_transition

    // Configuracion de transicion gradual
    @Persisted var transitionDays: Int = 3  // Dias para ajuste gradual
    @Persisted var currentTransitionDay: Int = 0

    // Duracion del viaje
    @Persisted var startDate: Date?
    @Persisted var estimatedEndDate: Date?
    @Persisted var actualEndDate: Date?

    // Medicamentos excluidos (mantienen horario original)
    @Persisted var excludedMedicationIds: List<String>

    // Timestamps
    @Persisted var activatedAt: Date?
    @Persisted var deactivatedAt: Date?
    @Persisted var updatedAt: Date = Date()
}

2.7.3. Android - Kotlin/Room

// ============================================================
// MODELO: TravelMode
// Descripcion: Configuracion de modo viaje
// Almacenamiento: Room cifrado (SQLCipher)
// Sync: LOCAL_ONLY - NO se sincroniza
// ============================================================

package com.medtime.data.entities

import androidx.room.*
import java.time.Instant

@Entity(
    tableName = "cli_travel_mode"
)
data class TravelMode(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = "travel_mode",

    @ColumnInfo(name = "user_id")
    val userId: String = "",

    // Estado
    @ColumnInfo(name = "is_active")
    val isActive: Boolean = false,

    // Zona horaria
    @ColumnInfo(name = "home_timezone")
    val homeTimezone: String = "",

    @ColumnInfo(name = "current_timezone")
    val currentTimezone: String = "",

    @ColumnInfo(name = "timezone_offset_minutes")
    val timezoneOffsetMinutes: Int = 0,

    // Estrategia
    @ColumnInfo(name = "strategy")
    val strategy: TravelStrategy = TravelStrategy.MAINTAIN_LOCAL,

    // Transicion gradual
    @ColumnInfo(name = "transition_days")
    val transitionDays: Int = 3,

    @ColumnInfo(name = "current_transition_day")
    val currentTransitionDay: Int = 0,

    // Duracion
    @ColumnInfo(name = "start_date")
    val startDate: Instant? = null,

    @ColumnInfo(name = "estimated_end_date")
    val estimatedEndDate: Instant? = null,

    @ColumnInfo(name = "actual_end_date")
    val actualEndDate: Instant? = null,

    // Medicamentos excluidos
    @ColumnInfo(name = "excluded_medication_ids")
    val excludedMedicationIds: List<String> = emptyList(),  // TypeConverter

    // Timestamps
    @ColumnInfo(name = "activated_at")
    val activatedAt: Instant? = null,

    @ColumnInfo(name = "deactivated_at")
    val deactivatedAt: Instant? = null,

    @ColumnInfo(name = "updated_at")
    val updatedAt: Instant = Instant.now()
)

enum class TravelStrategy {
    MAINTAIN_LOCAL,      // Mantener hora local del destino
    MAINTAIN_INTERVAL,   // Mantener intervalo entre tomas
    GRADUAL_TRANSITION   // Transicion gradual
}

3. Entidades del Servidor (SYNCED_E2E Blobs)

3.1. srv_calendar_sync

Almacena blobs cifrados de datos del calendario para sincronizacion (Pro/Perfect).

3.1.1. Esquema PostgreSQL

-- ============================================================
-- TABLA: srv_calendar_sync
-- Descripcion: Blobs cifrados de calendario (Pro/Perfect)
-- RLS: Usuario solo ve sus propios blobs
-- ============================================================

CREATE TABLE srv_calendar_sync (
    -- Identificadores
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES srv_users(id) ON DELETE CASCADE,

    -- Tipo de entidad cifrada
    entity_type     VARCHAR(50) NOT NULL,
    -- user_habits, day_moments, scheduled_doses, dose_events,
    -- dose_records, schedule_adjustments

    entity_id       UUID NOT NULL,  -- ID de la entidad en cliente

    -- Blob cifrado E2E (contiene JSON cifrado)
    encrypted_data  BYTEA NOT NULL,

    -- Metadata de cifrado (sin PHI)
    encryption_version VARCHAR(10) NOT NULL DEFAULT 'v1',
    nonce           BYTEA NOT NULL,  -- 96 bits
    auth_tag        BYTEA NOT NULL,  -- 128 bits

    -- Hash de integridad
    data_hash       VARCHAR(64) NOT NULL,  -- SHA-256 del blob cifrado

    -- Metadata operativa (sin PHI)
    blob_size_bytes INTEGER NOT NULL,
    is_deleted      BOOLEAN DEFAULT FALSE,

    -- Versionado para conflictos
    version         INTEGER NOT NULL DEFAULT 1,
    client_timestamp TIMESTAMPTZ NOT NULL,  -- Timestamp del cliente

    -- Timestamps servidor
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT uq_calendar_entity UNIQUE (user_id, entity_type, entity_id)
);

-- Indices
CREATE INDEX idx_calendar_sync_user ON srv_calendar_sync(user_id, entity_type);
CREATE INDEX idx_calendar_sync_updated ON srv_calendar_sync(updated_at);
CREATE INDEX idx_calendar_sync_deleted ON srv_calendar_sync(is_deleted)
    WHERE is_deleted = FALSE;

-- RLS
ALTER TABLE srv_calendar_sync ENABLE ROW LEVEL SECURITY;

CREATE POLICY calendar_sync_user_policy ON srv_calendar_sync
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::UUID);

-- Trigger updated_at
CREATE TRIGGER trg_calendar_sync_updated
    BEFORE UPDATE ON srv_calendar_sync
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- Funcion para limpieza de registros eliminados (ejecutar periodicamente)
CREATE OR REPLACE FUNCTION cleanup_deleted_calendar_sync()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    -- Eliminar registros marcados como deleted hace mas de 90 dias
    DELETE FROM srv_calendar_sync
    WHERE is_deleted = TRUE
      AND updated_at < NOW() - INTERVAL '90 days';

    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

3.1.2. Mapeo de Sincronizacion

Entidad Cliente entity_type Campos Cifrados en Blob Campos NO Cifrados
cli_user_habits user_habits wakeUpTime, breakfastTime, lunchTime, dinnerTime, bedTime, workDays, hasFlexibleSchedule user_id, version, timestamps
cli_day_moments day_moments momentType, scheduledTime, windowMinutes, customName, allowsWithFood, allowsWithoutFood, allowsFasting user_id, version, isDeleted
cli_scheduled_doses scheduled_doses medicationId, scheduleId, date, scheduledTime, dosage, unit, instructions, momentId, status user_id, version, isDeleted
cli_dose_events dose_events date, eventTime, momentId, name, windowStart, windowEnd, scheduledDoseIds, status, completedDoses, totalDoses user_id, version, isDeleted
cli_dose_records dose_records scheduledDoseId, scheduledTime, actualTime, status, skipReason, notes, confirmedBy, delayMinutes user_id, version (location NUNCA)
cli_schedule_adjustments schedule_adjustments medicationId, scheduleId, adjustmentType, oldTime, newTime, effectiveFrom, effectiveUntil, reason, notes user_id, version, isDeleted

NOTA CRITICA: Los campos location* de cli_dose_records son LOCAL_ONLYyNUNCA se incluyen en el blob cifrado. Ver seccion 6.1.

3.2. srv_caregiver_calendar_access

Permisos de cuidadores para acceder al calendario del paciente.

3.2.1. Esquema PostgreSQL

-- ============================================================
-- TABLA: srv_caregiver_calendar_access
-- Descripcion: Permisos de cuidadores para ver calendario
-- RLS: Paciente y cuidador ven sus relaciones
-- ============================================================

CREATE TABLE srv_caregiver_calendar_access (
    -- Identificadores
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    patient_id      UUID NOT NULL REFERENCES srv_users(id) ON DELETE CASCADE,
    caregiver_id    UUID NOT NULL REFERENCES srv_users(id) ON DELETE CASCADE,
    relation_id     UUID NOT NULL REFERENCES srv_user_relations(id) ON DELETE CASCADE,

    -- Permisos
    can_view_events BOOLEAN DEFAULT TRUE,
    can_view_history BOOLEAN DEFAULT FALSE,  -- Historial de tomas
    can_confirm_doses BOOLEAN DEFAULT FALSE,  -- Solo CR

    -- Notificaciones
    notify_on_missed BOOLEAN DEFAULT FALSE,
    notify_on_completed BOOLEAN DEFAULT FALSE,

    -- Vigencia
    granted_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ,  -- NULL = sin expiracion
    is_active       BOOLEAN DEFAULT TRUE,

    -- Revocacion
    revoked_at      TIMESTAMPTZ,
    revoked_by      UUID REFERENCES srv_users(id),
    revoke_reason   TEXT,

    -- Timestamps
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT uq_caregiver_calendar UNIQUE (patient_id, caregiver_id)
);

-- Indices
CREATE INDEX idx_caregiver_calendar_patient ON srv_caregiver_calendar_access(patient_id, is_active);
CREATE INDEX idx_caregiver_calendar_caregiver ON srv_caregiver_calendar_access(caregiver_id, is_active);

-- RLS
ALTER TABLE srv_caregiver_calendar_access ENABLE ROW LEVEL SECURITY;

CREATE POLICY caregiver_calendar_access_policy ON srv_caregiver_calendar_access
    FOR ALL
    USING (
        patient_id = current_setting('app.current_user_id')::UUID
        OR caregiver_id = current_setting('app.current_user_id')::UUID
    );

-- Trigger updated_at
CREATE TRIGGER trg_caregiver_calendar_updated
    BEFORE UPDATE ON srv_caregiver_calendar_access
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

4. Diagrama ER del Modulo

erDiagram
    %% CLIENTE
    cli_user_habits ||--o{ cli_day_moments : "define"
    cli_day_moments ||--o{ cli_scheduled_doses : "suggests"
    cli_medications ||--o{ cli_scheduled_doses : "has"
    cli_schedules ||--o{ cli_scheduled_doses : "generates"
    cli_scheduled_doses ||--o{ cli_dose_events : "grouped_in"
    cli_scheduled_doses ||--|| cli_dose_records : "recorded_as"
    cli_medications ||--o{ cli_schedule_adjustments : "has"

    %% SERVIDOR
    srv_users ||--o{ srv_calendar_sync : "syncs"
    srv_users ||--o{ srv_caregiver_calendar_access : "grants_as_patient"
    srv_users ||--o{ srv_caregiver_calendar_access : "receives_as_caregiver"
    srv_user_relations ||--|| srv_caregiver_calendar_access : "defines"

    %% Entidades Cliente
    cli_user_habits {
        string id PK
        string userId
        string dayType
        string wakeUpTime
        string breakfastTime
        string lunchTime
        string dinnerTime
        string bedTime
        list workDays
        boolean hasFlexibleSchedule
        string syncStatus
    }

    cli_day_moments {
        string id PK
        string userId
        string momentType
        string scheduledTime
        int windowMinutes
        string customName
        boolean isActive
    }

    cli_scheduled_doses {
        string id PK
        string userId
        string medicationId FK
        string scheduleId FK
        datetime scheduledTime
        string dosage
        string status
    }

    cli_dose_events {
        string id PK
        string userId
        datetime eventTime
        string momentId FK
        string name
        list scheduledDoseIds
        string status
    }

    cli_dose_records {
        string id PK
        string userId
        string scheduledDoseId FK
        datetime actualTime
        string status
        string skipReason
        double locationLat_LOCAL_ONLY
    }

    cli_schedule_adjustments {
        string id PK
        string userId
        string medicationId FK
        string adjustmentType
        string oldTime
        string newTime
    }

    %% Entidades Servidor
    srv_calendar_sync {
        uuid id PK
        uuid user_id FK
        string entity_type
        uuid entity_id
        bytea encrypted_data
        bytea nonce
        bytea auth_tag
        string data_hash
        int version
    }

    srv_caregiver_calendar_access {
        uuid id PK
        uuid patient_id FK
        uuid caregiver_id FK
        uuid relation_id FK
        boolean can_view_events
        boolean can_confirm_doses
        boolean is_active
    }

5. Reglas de Negocio Reflejadas en Modelo

ID Regla de Negocio Implementacion en Modelo
RN-CAL-001 Medicamentos se agrupan si estan dentro de 30 min cli_dose_events agrupa cli_scheduled_doses con windowMinutes
RN-CAL-002 Ventana de toma: 1h antes, 2h despues cli_scheduled_doses.windowStartMinutes = -60, windowEndMinutes = 120
RN-CAL-003 Maximo 8 momentos del dia configurables Validacion en logica de negocio (no constraint DB)
RN-CAL-004 Modo viaje ajusta por zona horaria cli_travel_mode almacena configuracion, logica en cliente
RN-CAL-005 Horarios fin de semana pueden diferir cli_user_habits.dayType = weekday/weekend
RN-CAL-006 Toma tardia cuenta si dentro de ventana cli_dose_records.wasWithinWindow flag
RN-CAL-007 Omision requiere motivo obligatorio cli_dose_records.skipReason NOT NULL cuando status=skipped

6. Consideraciones de Cifrado

6.1. Campos PHI Cifrados E2E

Los siguientes campos contienen PHI y DEBEN cifrarse antes de sincronizar:

Entidad Campos PHI Nivel PHI Razon
cli_user_habits wakeUpTime, breakfastTime, lunchTime, dinnerTime, bedTime, workDays Medio Revela rutina diaria y patron laboral
cli_day_moments momentType, scheduledTime, customName Medio Revela estilo de vida
cli_scheduled_doses medicationId, dosage, instructions Alto Referencia directa a medicamentos
cli_dose_events name, scheduledDoseIds Alto Agrupacion revela tratamiento
cli_dose_records scheduledDoseId, actualTime, skipReason, notes Alto Historial de tomas es PHI critico
cli_schedule_adjustments medicationId, oldTime, newTime, reason Medio Cambios revelan comportamiento medico

CAMPOS LOCAL_ONLY (NUNCA SE SINCRONIZAN):

Entidad Campos Razon
cli_dose_records locationLat, locationLon, locationAccuracy Datos de ubicacion son sensibles y no necesarios para sync
cli_travel_mode TODA LA ENTIDAD Configuracion temporal, cada dispositivo mantiene su estado

6.2. Blind Index para Busqueda

NO se implementan blind indexes para calendario porque:

  1. NO hay busqueda por servidor: El calendario se calcula completamente en cliente
  2. NO hay queries multi-usuario: Cada usuario solo ve su calendario
  3. Cuidadores reciben permisos: Via srv_caregiver_calendar_access (sin blind index)

6.3. Padding de Blobs

Los siguientes campos contienen listas de longitud variable y DEBEN aplicar padding antes del cifrado E2E:

Campo Razon
cli_dose_events.scheduledDoseIds Lista de tomas agrupadas revela cantidad de medicamentos
cli_user_habits.workDays Lista de dias laborales revela patron semanal
cli_travel_mode.excludedMedicationIds Lista revela cantidad de medicamentos del usuario

Algoritmo de Padding:

1. Serializar lista a JSON
2. Aplicar PKCS#7 padding a multiplos de 1KB
3. Cifrar con AES-256-GCM
4. Resultado: Todos los blobs son multiplos de 1KB, ocultando tamaño real

Ver 04-seguridad-cliente.md seccion 3.7 para detalles del algoritmo.

7. Indices y Performance

7.1. Cliente (SQLite/Realm)

// iOS Realm - Indices criticos
class ScheduledDose: Object {
    @Persisted(indexed: true) var scheduledTime: Date
    @Persisted(indexed: true) var status: String
    @Persisted(indexed: true) var date: Date
}

class DoseEvent: Object {
    @Persisted(indexed: true) var eventTime: Date
    @Persisted(indexed: true) var status: String
}

class DoseRecord: Object {
    @Persisted(indexed: true) var recordedAt: Date
    @Persisted(indexed: true) var scheduledDoseId: String
}

// Android Room - Indices
@Entity(
    tableName = "cli_scheduled_doses",
    indices = [
        Index(value = ["scheduled_time", "status"]),  // Query principal
        Index(value = ["date"]),                       // Vista por dia
        Index(value = ["medication_id"])               // Filtro por medicamento
    ]
)

7.2. Servidor (PostgreSQL)

-- Indices criticos para sync
CREATE INDEX idx_calendar_sync_user_type ON srv_calendar_sync(user_id, entity_type);
CREATE INDEX idx_calendar_sync_updated ON srv_calendar_sync(updated_at)
    WHERE is_deleted = FALSE;

-- Index para cleanup job
CREATE INDEX idx_calendar_sync_cleanup ON srv_calendar_sync(is_deleted, updated_at)
    WHERE is_deleted = TRUE;

-- Index para permisos cuidador
CREATE INDEX idx_caregiver_calendar_active ON srv_caregiver_calendar_access(patient_id, is_active)
    WHERE is_active = TRUE;

8. Migraciones

8.1. Migration 001: Create Calendar Schema (Servidor)

-- Migration: 001_create_calendar_schema
-- Date: 2025-12-08
-- Author: DatabaseDrone

BEGIN;

-- Tabla de sincronizacion de calendario
CREATE TABLE srv_calendar_sync (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES srv_users(id) ON DELETE CASCADE,
    entity_type     VARCHAR(50) NOT NULL,
    entity_id       UUID NOT NULL,
    encrypted_data  BYTEA NOT NULL,
    encryption_version VARCHAR(10) NOT NULL DEFAULT 'v1',
    nonce           BYTEA NOT NULL,
    auth_tag        BYTEA NOT NULL,
    data_hash       VARCHAR(64) NOT NULL,
    blob_size_bytes INTEGER NOT NULL,
    is_deleted      BOOLEAN DEFAULT FALSE,
    version         INTEGER NOT NULL DEFAULT 1,
    client_timestamp TIMESTAMPTZ NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_calendar_entity UNIQUE (user_id, entity_type, entity_id)
);

-- Tabla de permisos de cuidadores
CREATE TABLE srv_caregiver_calendar_access (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    patient_id      UUID NOT NULL REFERENCES srv_users(id) ON DELETE CASCADE,
    caregiver_id    UUID NOT NULL REFERENCES srv_users(id) ON DELETE CASCADE,
    relation_id     UUID NOT NULL REFERENCES srv_user_relations(id) ON DELETE CASCADE,
    can_view_events BOOLEAN DEFAULT TRUE,
    can_view_history BOOLEAN DEFAULT FALSE,
    can_confirm_doses BOOLEAN DEFAULT FALSE,
    notify_on_missed BOOLEAN DEFAULT FALSE,
    notify_on_completed BOOLEAN DEFAULT FALSE,
    granted_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at      TIMESTAMPTZ,
    is_active       BOOLEAN DEFAULT TRUE,
    revoked_at      TIMESTAMPTZ,
    revoked_by      UUID REFERENCES srv_users(id),
    revoke_reason   TEXT,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_caregiver_calendar UNIQUE (patient_id, caregiver_id)
);

-- Indices
CREATE INDEX idx_calendar_sync_user ON srv_calendar_sync(user_id, entity_type);
CREATE INDEX idx_calendar_sync_updated ON srv_calendar_sync(updated_at);
CREATE INDEX idx_calendar_sync_deleted ON srv_calendar_sync(is_deleted)
    WHERE is_deleted = FALSE;

CREATE INDEX idx_caregiver_calendar_patient ON srv_caregiver_calendar_access(patient_id, is_active);
CREATE INDEX idx_caregiver_calendar_caregiver ON srv_caregiver_calendar_access(caregiver_id, is_active);

-- RLS
ALTER TABLE srv_calendar_sync ENABLE ROW LEVEL SECURITY;
ALTER TABLE srv_caregiver_calendar_access ENABLE ROW LEVEL SECURITY;

CREATE POLICY calendar_sync_user_policy ON srv_calendar_sync
    FOR ALL
    USING (user_id = current_setting('app.current_user_id')::UUID);

CREATE POLICY caregiver_calendar_access_policy ON srv_caregiver_calendar_access
    FOR ALL
    USING (
        patient_id = current_setting('app.current_user_id')::UUID
        OR caregiver_id = current_setting('app.current_user_id')::UUID
    );

-- Triggers
CREATE TRIGGER trg_calendar_sync_updated
    BEFORE UPDATE ON srv_calendar_sync
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_caregiver_calendar_updated
    BEFORE UPDATE ON srv_caregiver_calendar_access
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

COMMIT;

8.2. Migration 001: Create Calendar Schema (Cliente iOS)

// Realm Migration - Version 1
let config = Realm.Configuration(
    schemaVersion: 1,
    migrationBlock: { migration, oldSchemaVersion in
        if oldSchemaVersion < 1 {
            // Primera version - crear esquema
            migration.enumerateObjects(ofType: UserHabits.className()) { oldObject, newObject in
                // Valores por defecto si vienen de version anterior
            }
        }
    }
)

Realm.Configuration.defaultConfiguration = config

8.3. Migration 001: Create Calendar Schema (Cliente Android)

// Room Migration - Version 1 to 2
val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Crear tablas de calendario
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS cli_user_habits (
                id TEXT PRIMARY KEY NOT NULL,
                user_id TEXT NOT NULL,
                day_type TEXT NOT NULL,
                wake_up_time TEXT NOT NULL,
                breakfast_time TEXT,
                lunch_time TEXT,
                dinner_time TEXT,
                bed_time TEXT NOT NULL,
                work_days TEXT NOT NULL,
                has_flexible_schedule INTEGER NOT NULL DEFAULT 0,
                typical_variation_minutes INTEGER NOT NULL DEFAULT 30,
                sync_status TEXT NOT NULL DEFAULT 'PENDING',
                last_sync_at INTEGER,
                version INTEGER NOT NULL DEFAULT 1,
                created_at INTEGER NOT NULL,
                updated_at INTEGER NOT NULL,
                is_deleted INTEGER NOT NULL DEFAULT 0
            )
        """)

        // Indices
        database.execSQL("""
            CREATE INDEX IF NOT EXISTS idx_habits_user_day
            ON cli_user_habits(user_id, day_type)
        """)

        // ... resto de tablas ...
    }
}

9. Referencias Cruzadas

Documento Relacion
DB-ERD-001 Diagrama ER completo
MTS-CAL-001 Especificacion funcional
MDL-MED-001 FK a medications, schedules
MDL-ADH-001 Usa dose_records para calcular metricas
MDL-ALT-001 Usa scheduled_doses para generar alertas
MDL-USR-001 FK a users, user_relations
API-SYNC-001 Protocolo de sync de blobs
02-arquitectura-cliente-servidor.md Arquitectura dual
04-seguridad-cliente.md Cifrado E2E, padding
05-seguridad-servidor.md RLS policies

Documento generado por DatabaseDrone (Doce de Quince) - SpecQueen Technical Division "El calendario es el corazon de MedTime. LOCAL primero, SERVIDOR solo blobs cifrados."