Modelo de Datos: Análisis Clínicos

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

---

• 1. Introduccion
• 1.1. Principios
• 1.2. Clasificacion de Datos
• 2. Entidades del Cliente (LOCAL/SYNCED_E2E)
• 2.1. cli_clinical_analyses
  • 2.1.1. Clasificacion
  • 2.1.2. iOS - Swift/Realm
  • 2.1.3. Android - Kotlin/Room
• 2.2. cli_analysis_results
  • 2.2.1. Clasificacion
  • 2.2.2. iOS - Swift/Realm
  • 2.2.3. Android - Kotlin/Room
• 2.3. cli_analysis_parameters
  • 2.3.1. Clasificacion
  • 2.3.2. iOS - Swift/Realm
  • 2.3.3. Android - Kotlin/Room
• 2.4. cli_parameter_history
  • 2.4.1. Clasificacion
  • 2.4.2. iOS - Swift/Realm
  • 2.4.3. Android - Kotlin/Room
• 2.5. cli_analysis_alerts
  • 2.5.1. Clasificacion
  • 2.5.2. iOS - Swift/Realm
  • 2.5.3. Android - Kotlin/Room
• 2.6. cli_analysis_attachments
  • 2.6.1. Clasificacion
  • 2.6.2. iOS - Swift/Realm
  • 2.6.3. Android - Kotlin/Room
• 3. Entidades del Servidor (SYNCED_E2E Blobs)
• 3.1. srv_analysis_sync
  • 3.1.1. Esquema PostgreSQL
  • 3.1.2. Mapeo de Sincronizacion
• 3.2. srv_fhir_exports
  • 3.2.1. Esquema PostgreSQL
• 4. Diagrama ER del Modulo
• 5. Reglas de Negocio Reflejadas en Modelo
• 6. Consideraciones de Cifrado
• 6.1. Campos PHI Cifrados E2E
• 6.2. Archivos Adjuntos
• 7. Indices y Performance
• 7.1. Cliente (SQLite/Realm)
• 7.2. Servidor (PostgreSQL)
• 8. Migraciones
• 8.1. Migration 001: Create Analysis Schema (Servidor)
• 8.2. Migration 001: Create Analysis Schema (Cliente iOS)
• 8.3. Migration 001: Create Analysis Schema (Cliente Android)
• 9. Referencias Cruzadas

---

1. Introduccion

Este documento define el modelo de datos para el modulo de Analisis Clinicos de MedTime. Permite al paciente almacenar, organizar y visualizar resultados de analisis clinicos con capacidad de exportacion para segundas opiniones medicas.

1.1. Principios

Principio	Aplicacion
95% Local	Calculo de alertas, graficos, tendencias 100% en dispositivo
Zero-Knowledge	Servidor solo almacena blobs cifrados de analisis
Offline-First	Analisis funciona completamente sin conexion
Sync para Pro/Perfect	Solo usuarios Pro/Perfect sincronizan analisis

1.2. Clasificacion de Datos

Entidad	Clasificacion	Cifrado	Sync	Razon
cli_clinical_analyses	SYNCED_E2E	Si	Pro/Perfect	Datos de analisis son PHI critico
cli_analysis_results	SYNCED_E2E	Si	Pro/Perfect	Valores de parametros son PHI
cli_analysis_parameters	SYNCED_E2E	Si	Pro/Perfect	Configuracion de parametros rastreados es info medica
cli_parameter_history	SYNCED_E2E	Si	Pro/Perfect	Historial de valores es PHI
cli_analysis_alerts	LOCAL_ONLY	Si	No	Alertas se calculan localmente, no requieren sync
cli_analysis_attachments	SYNCED_E2E	Si	Pro/Perfect	Archivos adjuntos contienen PHI
srv_analysis_sync	SERVER_ONLY	N/A	N/A	Almacena blobs cifrados
srv_fhir_exports	SERVER_ONLY	No	N/A	Exportaciones FHIR (Perfect)

---

2. Entidades del Cliente (LOCAL/SYNCED_E2E)

2.1. cli_clinical_analyses

Analisis clinicos del usuario.

2.1.1. Clasificacion

Campo	Clasificacion	Razon
userId	LOCAL_ONLY	Identificador local
analysisType	SYNCED_E2E	Tipo de analisis revela condicion medica
analysisName	SYNCED_E2E	Nombre del estudio es PHI
datePerformed	SYNCED_E2E	Fecha del analisis es PHI
laboratory	SYNCED_E2E	Laboratorio es informacion medica
prescriber	SYNCED_E2E	Medico solicitante es PHI
fileUrl	SYNCED_E2E	URL local de archivo cifrado
notes	SYNCED_E2E	Notas pueden contener info medica

2.1.2. iOS - Swift/Realm

// ============================================================
// MODELO: ClinicalAnalysis
// Descripcion: Analisis clinico del usuario
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

class ClinicalAnalysis: Object, Identifiable {
    // Identificadores
    @Persisted(primaryKey: true) var id: String = UUID().uuidString
    @Persisted var userId: String = ""
    @Persisted var profileId: String?  // Para pacientes dependientes

    // Tipo de analisis
    @Persisted var analysisType: String = ""
    // blood, urine, imaging, specialized, other

    @Persisted var analysisName: String = ""  // Nombre del estudio

    // Fechas
    @Persisted var datePerformed: Date = Date()  // Fecha del analisis
    @Persisted var dateUploaded: Date = Date()   // Fecha de carga

    // Origen
    @Persisted var laboratory: String?  // Laboratorio/Institucion
    @Persisted var prescriber: String?  // Medico que lo solicito

    // Archivo
    @Persisted var hasFile: Bool = false
    @Persisted var fileUrl: String?  // URL local cifrada
    @Persisted var fileType: String?  // pdf, jpg, png
    @Persisted var fileSizeBytes: Int = 0

    // Notas
    @Persisted var notes: String?

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

    // 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()

    // Relationships
    @Persisted var results: List<AnalysisResult>
    @Persisted var attachments: List<AnalysisAttachment>
    @Persisted var alerts: List<AnalysisAlert>
}

2.1.3. Android - Kotlin/Room

// ============================================================
// MODELO: ClinicalAnalysis
// Descripcion: Analisis clinico del usuario
// 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_clinical_analyses",
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = ["userId"],
            childColumns = ["userId"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [
        Index(value = ["userId"]),
        Index(value = ["analysisType"]),
        Index(value = ["datePerformed"]),
        Index(value = ["isActive"])
    ]
)
data class ClinicalAnalysis(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

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

    // Tipo de analisis
    @ColumnInfo(name = "analysis_type")
    val analysisType: AnalysisType = AnalysisType.BLOOD,

    @ColumnInfo(name = "analysis_name")
    val analysisName: String = "",

    // Fechas
    @ColumnInfo(name = "date_performed")
    val datePerformed: Instant = Instant.now(),

    @ColumnInfo(name = "date_uploaded")
    val dateUploaded: Instant = Instant.now(),

    // Origen
    @ColumnInfo(name = "laboratory")
    val laboratory: String? = null,

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

    // Archivo
    @ColumnInfo(name = "has_file")
    val hasFile: Boolean = false,

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

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

    @ColumnInfo(name = "file_size_bytes")
    val fileSizeBytes: Int = 0,

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

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

    @ColumnInfo(name = "is_deleted")
    val isDeleted: 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()
)

enum class AnalysisType {
    BLOOD,          // Sangre
    URINE,          // Orina
    IMAGING,        // Imagen (RX, TAC, MRI, etc.)
    SPECIALIZED,    // Estudios especializados
    OTHER           // Otros
}

---

2.2. cli_analysis_results

Resultados individuales de parametros de un analisis.

2.2.1. Clasificacion

Campo	Clasificacion	Razon
analysisId	SYNCED_E2E	FK a analisis (PHI)
parameterId	SYNCED_E2E	FK a parametro rastreado (PHI)
parameterName	SYNCED_E2E	Nombre del parametro es PHI
value	SYNCED_E2E	Valor del resultado es PHI critico
unit	SYNCED_E2E	Unidad revela tipo de analisis
referenceMin	SYNCED_E2E	Rangos de referencia son PHI
referenceMax	SYNCED_E2E	Rangos de referencia son PHI
isOutOfRange	SYNCED_E2E	Estado fuera de rango es PHI

2.2.2. iOS - Swift/Realm

// ============================================================
// MODELO: AnalysisResult
// Descripcion: Resultado individual de parametro
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Referencias
    @Persisted(originProperty: "results") var analysis: LinkingObjects<ClinicalAnalysis>
    @Persisted var analysisId: String = ""

    @Persisted var parameterId: String?  // FK a cli_analysis_parameters (opcional)

    // Parametro
    @Persisted var parameterName: String = ""  // Glucosa, Hemoglobina, etc.
    @Persisted var parameterCode: String?      // Codigo LOINC (opcional)

    // Resultado
    @Persisted var valueNumeric: Double?  // Valor numerico
    @Persisted var valueText: String?     // Valor texto (ej: "Positivo", "Negativo")
    @Persisted var unit: String?          // mg/dL, g/dL, etc.

    // Rangos de referencia
    @Persisted var referenceMin: Double?
    @Persisted var referenceMax: Double?
    @Persisted var referenceText: String?  // Ej: "< 100 mg/dL"

    // Estado
    @Persisted var isOutOfRange: Bool = false
    @Persisted var severity: String?  // low, normal, high, critical

    // Notas
    @Persisted var notes: String?

    // 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
}

2.2.3. Android - Kotlin/Room

// ============================================================
// MODELO: AnalysisResult
// Descripcion: Resultado individual de parametro
// 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_analysis_results",
    foreignKeys = [
        ForeignKey(
            entity = ClinicalAnalysis::class,
            parentColumns = ["id"],
            childColumns = ["analysis_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = AnalysisParameter::class,
            parentColumns = ["id"],
            childColumns = ["parameter_id"],
            onDelete = ForeignKey.SET_NULL
        )
    ],
    indices = [
        Index(value = ["analysis_id"]),
        Index(value = ["parameter_id"]),
        Index(value = ["parameter_name"]),
        Index(value = ["is_out_of_range"])
    ]
)
data class AnalysisResult(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Referencias
    @ColumnInfo(name = "analysis_id", index = true)
    val analysisId: String = "",

    @ColumnInfo(name = "parameter_id", index = true)
    val parameterId: String? = null,

    // Parametro
    @ColumnInfo(name = "parameter_name")
    val parameterName: String = "",

    @ColumnInfo(name = "parameter_code")
    val parameterCode: String? = null,  // LOINC code

    // Resultado
    @ColumnInfo(name = "value_numeric")
    val valueNumeric: Double? = null,

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

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

    // Rangos de referencia
    @ColumnInfo(name = "reference_min")
    val referenceMin: Double? = null,

    @ColumnInfo(name = "reference_max")
    val referenceMax: Double? = null,

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

    // Estado
    @ColumnInfo(name = "is_out_of_range")
    val isOutOfRange: Boolean = false,

    @ColumnInfo(name = "severity")
    val severity: ResultSeverity? = null,

    // Notas
    @ColumnInfo(name = "notes")
    val notes: 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 ResultSeverity {
    LOW,
    NORMAL,
    HIGH,
    CRITICAL
}

---

2.3. cli_analysis_parameters

Parametros de salud que el usuario desea rastrear a lo largo del tiempo.

2.3.1. Clasificacion

Campo	Clasificacion	Razon
userId	LOCAL_ONLY	Identificador local
parameterName	SYNCED_E2E	Nombre del parametro revela condicion rastreada
parameterCode	SYNCED_E2E	Codigo LOINC es info medica
unit	SYNCED_E2E	Unidad revela tipo de parametro
referenceMin	SYNCED_E2E	Rangos personalizados son PHI
referenceMax	SYNCED_E2E	Rangos personalizados son PHI
targetValue	SYNCED_E2E	Objetivo terapeutico es PHI

2.3.2. iOS - Swift/Realm

// ============================================================
// MODELO: AnalysisParameter
// Descripcion: Parametro de salud rastreado
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Parametro
    @Persisted var parameterName: String = ""  // Glucosa, Hemoglobina, etc.
    @Persisted var parameterCode: String?      // Codigo LOINC
    @Persisted var category: String = ""       // blood, urine, hormonal, etc.

    // Unidad y rangos
    @Persisted var unit: String = ""  // mg/dL, g/dL, etc.
    @Persisted var referenceMin: Double?
    @Persisted var referenceMax: Double?
    @Persisted var targetValue: Double?  // Objetivo terapeutico

    // Configuracion de rastreo
    @Persisted var isTracked: Bool = true
    @Persisted var trackingSince: Date = Date()
    @Persisted var alertOnOutOfRange: Bool = true

    // Personalizacion
    @Persisted var customName: String?  // Nombre personalizado
    @Persisted var color: String?       // Color para graficos (hex)
    @Persisted var icon: String?        // Icono

    // Notas
    @Persisted var notes: String?

    // Estado
    @Persisted var isActive: Bool = true
    @Persisted var isDeleted: 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()

    // Relationships
    @Persisted var history: List<ParameterHistory>
}

2.3.3. Android - Kotlin/Room

// ============================================================
// MODELO: AnalysisParameter
// Descripcion: Parametro de salud rastreado
// 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_analysis_parameters",
    indices = [
        Index(value = ["user_id"]),
        Index(value = ["parameter_name"]),
        Index(value = ["category"]),
        Index(value = ["is_tracked"])
    ]
)
data class AnalysisParameter(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Parametro
    @ColumnInfo(name = "parameter_name")
    val parameterName: String = "",

    @ColumnInfo(name = "parameter_code")
    val parameterCode: String? = null,  // LOINC code

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

    // Unidad y rangos
    @ColumnInfo(name = "unit")
    val unit: String = "",

    @ColumnInfo(name = "reference_min")
    val referenceMin: Double? = null,

    @ColumnInfo(name = "reference_max")
    val referenceMax: Double? = null,

    @ColumnInfo(name = "target_value")
    val targetValue: Double? = null,

    // Configuracion de rastreo
    @ColumnInfo(name = "is_tracked")
    val isTracked: Boolean = true,

    @ColumnInfo(name = "tracking_since")
    val trackingSince: Instant = Instant.now(),

    @ColumnInfo(name = "alert_on_out_of_range")
    val alertOnOutOfRange: Boolean = true,

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

    @ColumnInfo(name = "color")
    val color: String? = null,  // Hex color

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

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

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

    @ColumnInfo(name = "is_deleted")
    val isDeleted: 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()
)

---

2.4. cli_parameter_history

Historial de valores de parametros para graficos de tendencia.

2.4.1. Clasificacion

Campo	Clasificacion	Razon
parameterId	SYNCED_E2E	FK a parametro (PHI)
analysisId	SYNCED_E2E	FK a analisis (PHI)
resultId	SYNCED_E2E	FK a resultado (PHI)
value	SYNCED_E2E	Valor del parametro es PHI critico
dateRecorded	SYNCED_E2E	Fecha del registro es PHI

2.4.2. iOS - Swift/Realm

// ============================================================
// MODELO: ParameterHistory
// Descripcion: Historial de valores de parametro
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Referencias
    @Persisted(originProperty: "history") var parameter: LinkingObjects<AnalysisParameter>
    @Persisted var parameterId: String = ""

    @Persisted var analysisId: String?  // FK a analisis (opcional)
    @Persisted var resultId: String?    // FK a resultado (opcional)

    // Valor
    @Persisted var value: Double = 0.0
    @Persisted var unit: String = ""
    @Persisted var dateRecorded: Date = Date()

    // Estado
    @Persisted var isOutOfRange: Bool = false
    @Persisted var severity: String?  // low, normal, high, critical

    // Notas
    @Persisted var notes: String?

    // 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.4.3. Android - Kotlin/Room

// ============================================================
// MODELO: ParameterHistory
// Descripcion: Historial de valores de parametro
// 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_parameter_history",
    foreignKeys = [
        ForeignKey(
            entity = AnalysisParameter::class,
            parentColumns = ["id"],
            childColumns = ["parameter_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = ClinicalAnalysis::class,
            parentColumns = ["id"],
            childColumns = ["analysis_id"],
            onDelete = ForeignKey.SET_NULL
        ),
        ForeignKey(
            entity = AnalysisResult::class,
            parentColumns = ["id"],
            childColumns = ["result_id"],
            onDelete = ForeignKey.SET_NULL
        )
    ],
    indices = [
        Index(value = ["parameter_id", "date_recorded"]),
        Index(value = ["analysis_id"]),
        Index(value = ["result_id"]),
        Index(value = ["is_out_of_range"])
    ]
)
data class ParameterHistory(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Referencias
    @ColumnInfo(name = "parameter_id", index = true)
    val parameterId: String = "",

    @ColumnInfo(name = "analysis_id", index = true)
    val analysisId: String? = null,

    @ColumnInfo(name = "result_id", index = true)
    val resultId: String? = null,

    // Valor
    @ColumnInfo(name = "value")
    val value: Double = 0.0,

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

    @ColumnInfo(name = "date_recorded")
    val dateRecorded: Instant = Instant.now(),

    // Estado
    @ColumnInfo(name = "is_out_of_range")
    val isOutOfRange: Boolean = false,

    @ColumnInfo(name = "severity")
    val severity: ResultSeverity? = null,

    // Notas
    @ColumnInfo(name = "notes")
    val notes: 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 = "is_deleted")
    val isDeleted: Boolean = false
)

---

2.5. cli_analysis_alerts

Alertas de valores fuera de rango (LOCAL_ONLY).

2.5.1. Clasificacion

Campo	Clasificacion	Razon
ALL	LOCAL_ONLY	Alertas se calculan localmente, no requieren sincronizacion

NOTA: Esta entidad esLOCAL_ONLY porque las alertas se generan localmente y no necesitan sincronizarse. El servidor no participa en el sistema de alertas de analisis.

2.5.2. iOS - Swift/Realm

// ============================================================
// MODELO: AnalysisAlert
// Descripcion: Alerta de valor fuera de rango
// Almacenamiento: Realm cifrado
// Sync: LOCAL_ONLY - NO se sincroniza
// ============================================================

import RealmSwift

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

    // Referencias
    @Persisted(originProperty: "alerts") var analysis: LinkingObjects<ClinicalAnalysis>
    @Persisted var analysisId: String = ""
    @Persisted var resultId: String?    // FK a resultado
    @Persisted var parameterId: String? // FK a parametro

    // Tipo de alerta
    @Persisted var alertType: String = ""  // out_of_range, critical, pattern_detected

    // Detalles
    @Persisted var parameterName: String = ""
    @Persisted var value: Double = 0.0
    @Persisted var unit: String = ""
    @Persisted var referenceMin: Double?
    @Persisted var referenceMax: Double?
    @Persisted var severity: String = ""  // low, high, critical

    // Mensaje
    @Persisted var title: String = ""
    @Persisted var message: String = ""
    @Persisted var disclaimerShown: Bool = false

    // Estado
    @Persisted var isRead: Bool = false
    @Persisted var isDismissed: Bool = false
    @Persisted var userAcknowledgedAt: Date?

    // Timestamps
    @Persisted var createdAt: Date = Date()
    @Persisted var expiresAt: Date?
}

2.5.3. Android - Kotlin/Room

// ============================================================
// MODELO: AnalysisAlert
// Descripcion: Alerta de valor fuera de rango
// Almacenamiento: Room cifrado (SQLCipher)
// Sync: LOCAL_ONLY - NO se sincroniza
// ============================================================

package com.medtime.data.entities

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

@Entity(
    tableName = "cli_analysis_alerts",
    foreignKeys = [
        ForeignKey(
            entity = ClinicalAnalysis::class,
            parentColumns = ["id"],
            childColumns = ["analysis_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = AnalysisResult::class,
            parentColumns = ["id"],
            childColumns = ["result_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = AnalysisParameter::class,
            parentColumns = ["id"],
            childColumns = ["parameter_id"],
            onDelete = ForeignKey.SET_NULL
        )
    ],
    indices = [
        Index(value = ["analysis_id"]),
        Index(value = ["result_id"]),
        Index(value = ["parameter_id"]),
        Index(value = ["is_read"]),
        Index(value = ["severity"])
    ]
)
data class AnalysisAlert(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Referencias
    @ColumnInfo(name = "analysis_id", index = true)
    val analysisId: String = "",

    @ColumnInfo(name = "result_id", index = true)
    val resultId: String? = null,

    @ColumnInfo(name = "parameter_id", index = true)
    val parameterId: String? = null,

    // Tipo de alerta
    @ColumnInfo(name = "alert_type")
    val alertType: AlertType = AlertType.OUT_OF_RANGE,

    // Detalles
    @ColumnInfo(name = "parameter_name")
    val parameterName: String = "",

    @ColumnInfo(name = "value")
    val value: Double = 0.0,

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

    @ColumnInfo(name = "reference_min")
    val referenceMin: Double? = null,

    @ColumnInfo(name = "reference_max")
    val referenceMax: Double? = null,

    @ColumnInfo(name = "severity")
    val severity: ResultSeverity = ResultSeverity.NORMAL,

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

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

    @ColumnInfo(name = "disclaimer_shown")
    val disclaimerShown: Boolean = false,

    // Estado
    @ColumnInfo(name = "is_read")
    val isRead: Boolean = false,

    @ColumnInfo(name = "is_dismissed")
    val isDismissed: Boolean = false,

    @ColumnInfo(name = "user_acknowledged_at")
    val userAcknowledgedAt: Instant? = null,

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

    @ColumnInfo(name = "expires_at")
    val expiresAt: Instant? = null
)

enum class AlertType {
    OUT_OF_RANGE,
    CRITICAL,
    PATTERN_DETECTED
}

---

2.6. cli_analysis_attachments

Archivos adjuntos de analisis (PDFs, imagenes).

2.6.1. Clasificacion

Campo	Clasificacion	Razon
analysisId	SYNCED_E2E	FK a analisis (PHI)
fileName	SYNCED_E2E	Nombre de archivo puede contener info
fileUrl	SYNCED_E2E	URL local cifrada
encryptedBlob	SYNCED_E2E	Archivo cifrado es PHI

2.6.2. iOS - Swift/Realm

// ============================================================
// MODELO: AnalysisAttachment
// Descripcion: Archivo adjunto de analisis
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Referencias
    @Persisted(originProperty: "attachments") var analysis: LinkingObjects<ClinicalAnalysis>
    @Persisted var analysisId: String = ""

    // Archivo
    @Persisted var fileName: String = ""
    @Persisted var fileType: String = ""  // pdf, jpg, png
    @Persisted var fileSizeBytes: Int = 0
    @Persisted var mimeType: String = ""

    // Almacenamiento local
    @Persisted var fileUrl: String = ""  // URL local cifrada
    @Persisted var thumbnailUrl: String?  // Thumbnail (opcional)

    // Hash para integridad
    @Persisted var fileHash: String = ""  // SHA-256

    // OCR (opcional)
    @Persisted var hasOcrText: Bool = false
    @Persisted var ocrText: String?
    @Persisted var ocrConfidence: Double?

    // Estado
    @Persisted var isDeleted: 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()
}

2.6.3. Android - Kotlin/Room

// ============================================================
// MODELO: AnalysisAttachment
// Descripcion: Archivo adjunto de analisis
// 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_analysis_attachments",
    foreignKeys = [
        ForeignKey(
            entity = ClinicalAnalysis::class,
            parentColumns = ["id"],
            childColumns = ["analysis_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [
        Index(value = ["analysis_id"]),
        Index(value = ["file_type"])
    ]
)
data class AnalysisAttachment(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Referencias
    @ColumnInfo(name = "analysis_id", index = true)
    val analysisId: String = "",

    // Archivo
    @ColumnInfo(name = "file_name")
    val fileName: String = "",

    @ColumnInfo(name = "file_type")
    val fileType: String = "",

    @ColumnInfo(name = "file_size_bytes")
    val fileSizeBytes: Int = 0,

    @ColumnInfo(name = "mime_type")
    val mimeType: String = "",

    // Almacenamiento local
    @ColumnInfo(name = "file_url")
    val fileUrl: String = "",

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

    // Hash para integridad
    @ColumnInfo(name = "file_hash")
    val fileHash: String = "",  // SHA-256

    // OCR (opcional)
    @ColumnInfo(name = "has_ocr_text")
    val hasOcrText: Boolean = false,

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

    @ColumnInfo(name = "ocr_confidence")
    val ocrConfidence: Double? = null,

    // Estado
    @ColumnInfo(name = "is_deleted")
    val isDeleted: 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()
)

---

3. Entidades del Servidor (SYNCED_E2E Blobs)

3.1. srv_analysis_sync

Almacena blobs cifrados de analisis para sincronizacion (Pro/Perfect).

3.1.1. Esquema PostgreSQL

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

CREATE TABLE srv_analysis_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,
    -- clinical_analysis, analysis_result, analysis_parameter,
    -- parameter_history, analysis_attachment

    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_analysis_entity UNIQUE (user_id, entity_type, entity_id)
);

-- Indices
CREATE INDEX idx_analysis_sync_user ON srv_analysis_sync(user_id, entity_type);
CREATE INDEX idx_analysis_sync_updated ON srv_analysis_sync(updated_at);
CREATE INDEX idx_analysis_sync_deleted ON srv_analysis_sync(is_deleted)
    WHERE is_deleted = FALSE;

-- RLS
ALTER TABLE srv_analysis_sync ENABLE ROW LEVEL SECURITY;

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

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

-- Funcion para limpieza de registros eliminados (ejecutar periodicamente)
CREATE OR REPLACE FUNCTION cleanup_deleted_analysis_sync()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    -- Eliminar registros marcados como deleted hace mas de 90 dias
    DELETE FROM srv_analysis_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_clinical_analyses	clinical_analysis	analysisType, analysisName, datePerformed, laboratory, prescriber, fileUrl, notes	user_id, version, timestamps
cli_analysis_results	analysis_result	analysisId, parameterId, parameterName, value, unit, referenceMin, referenceMax, isOutOfRange, notes	user_id, version, isDeleted
cli_analysis_parameters	analysis_parameter	parameterName, parameterCode, category, unit, referenceMin, referenceMax, targetValue, notes	user_id, version, isDeleted
cli_parameter_history	parameter_history	parameterId, analysisId, resultId, value, unit, dateRecorded, isOutOfRange, notes	user_id, version, isDeleted
cli_analysis_attachments	analysis_attachment	analysisId, fileName, fileType, fileSizeBytes, fileUrl, fileHash, ocrText	user_id, version, isDeleted

NOTA CRITICA: Las alertas (cli_analysis_alerts) sonLOCAL_ONLYyNUNCA se sincronizan.

---

3.2. srv_fhir_exports

Exportaciones FHIR para segunda opinion (Perfect tier).

3.2.1. Esquema PostgreSQL

-- ============================================================
-- TABLA: srv_fhir_exports
-- Descripcion: Exportaciones FHIR (Perfect)
-- RLS: Usuario solo ve sus propias exportaciones
-- ============================================================

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

    -- Tipo de exportacion
    export_type     VARCHAR(50) NOT NULL,  -- bundle, diagnostic_report
    fhir_version    VARCHAR(10) NOT NULL DEFAULT 'R4',

    -- Contenido FHIR (cifrado E2E)
    encrypted_fhir  BYTEA NOT NULL,
    encryption_version VARCHAR(10) NOT NULL DEFAULT 'v1',
    nonce           BYTEA NOT NULL,
    auth_tag        BYTEA NOT NULL,

    -- Metadata de exportacion (sin PHI)
    resource_count  INTEGER NOT NULL,
    date_range_start DATE,
    date_range_end  DATE,

    -- Anonimizacion
    is_anonymized   BOOLEAN DEFAULT FALSE,
    anonymization_level VARCHAR(20),  -- partial, full

    -- Acceso temporal
    access_token    VARCHAR(64) UNIQUE,  -- Token para acceso temporal
    expires_at      TIMESTAMPTZ,
    access_count    INTEGER DEFAULT 0,
    max_access      INTEGER DEFAULT 1,

    -- Estado
    is_active       BOOLEAN DEFAULT TRUE,
    is_deleted      BOOLEAN DEFAULT FALSE,

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

    -- Constraints
    CONSTRAINT chk_access_count CHECK (access_count <= max_access)
);

-- Indices
CREATE INDEX idx_fhir_exports_user ON srv_fhir_exports(user_id);
CREATE INDEX idx_fhir_exports_token ON srv_fhir_exports(access_token)
    WHERE is_active = TRUE AND expires_at > NOW();
CREATE INDEX idx_fhir_exports_expires ON srv_fhir_exports(expires_at)
    WHERE is_active = TRUE;

-- RLS
ALTER TABLE srv_fhir_exports ENABLE ROW LEVEL SECURITY;

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

-- Funcion para limpiar exportaciones expiradas
CREATE OR REPLACE FUNCTION cleanup_expired_fhir_exports()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    UPDATE srv_fhir_exports
    SET is_active = FALSE,
        is_deleted = TRUE
    WHERE expires_at < NOW()
      AND is_active = TRUE;

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

-- Funcion para incrementar contador de acceso
CREATE OR REPLACE FUNCTION increment_fhir_export_access(p_token VARCHAR)
RETURNS BOOLEAN AS $$
DECLARE
    export_record RECORD;
BEGIN
    SELECT * INTO export_record
    FROM srv_fhir_exports
    WHERE access_token = p_token
      AND is_active = TRUE
      AND expires_at > NOW()
      AND access_count < max_access
    FOR UPDATE;

    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;

    UPDATE srv_fhir_exports
    SET access_count = access_count + 1,
        accessed_at = NOW()
    WHERE id = export_record.id;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

---

4. Diagrama ER del Modulo

erDiagram
    %% CLIENTE
    cli_user ||--o{ cli_clinical_analyses : "has"
    cli_clinical_analyses ||--o{ cli_analysis_results : "has"
    cli_clinical_analyses ||--o{ cli_analysis_attachments : "has"
    cli_clinical_analyses ||--o{ cli_analysis_alerts : "generates"
    cli_analysis_parameters ||--o{ cli_parameter_history : "has"
    cli_analysis_parameters ||--o{ cli_analysis_results : "references"
    cli_analysis_parameters ||--o{ cli_analysis_alerts : "triggers"
    cli_analysis_results ||--o{ cli_parameter_history : "creates"
    cli_analysis_results ||--o{ cli_analysis_alerts : "triggers"

    %% SERVIDOR
    srv_users ||--o{ srv_analysis_sync : "syncs"
    srv_users ||--o{ srv_fhir_exports : "exports"

    %% Entidades Cliente
    cli_clinical_analyses {
        string id PK
        string userId FK
        string analysisType
        string analysisName
        date datePerformed
        string laboratory
        string prescriber
        boolean hasFile
        string fileUrl
        string notes
        string syncStatus
    }

    cli_analysis_results {
        string id PK
        string userId
        string analysisId FK
        string parameterId FK
        string parameterName
        double valueNumeric
        string valueText
        string unit
        double referenceMin
        double referenceMax
        boolean isOutOfRange
        string severity
    }

    cli_analysis_parameters {
        string id PK
        string userId
        string parameterName
        string parameterCode
        string category
        string unit
        double referenceMin
        double referenceMax
        double targetValue
        boolean isTracked
        boolean alertOnOutOfRange
    }

    cli_parameter_history {
        string id PK
        string userId
        string parameterId FK
        string analysisId FK
        string resultId FK
        double value
        string unit
        date dateRecorded
        boolean isOutOfRange
    }

    cli_analysis_alerts {
        string id PK
        string userId
        string analysisId FK
        string resultId FK
        string parameterId FK
        string alertType
        string parameterName
        double value
        string severity
        boolean isRead
    }

    cli_analysis_attachments {
        string id PK
        string userId
        string analysisId FK
        string fileName
        string fileType
        int fileSizeBytes
        string fileUrl
        string fileHash
        boolean hasOcrText
    }

    %% Entidades Servidor
    srv_analysis_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_fhir_exports {
        uuid id PK
        uuid user_id FK
        string export_type
        string fhir_version
        bytea encrypted_fhir
        int resource_count
        string access_token
        timestamptz expires_at
        int access_count
    }

---

5. Reglas de Negocio Reflejadas en Modelo

ID	Regla de Negocio	Implementacion en Modelo
RN-ANA-001	Archivos de analisis se cifran en reposo	cli_analysis_attachments con fileUrl cifrada
RN-ANA-002	Analisis se mantienen indefinidamente	Sin fecha de expiracion en modelo
RN-ANA-003	Maximo 50MB por archivo	Validacion en logica de negocio, fileSizeBytes almacena tamaño
RN-ANA-004	Sistema NO realiza diagnosticos	cli_analysis_alerts solo indica fuera de rango con disclaimer
RN-ANA-005	Valores fuera de rango solo son indicativos	isOutOfRange flag + severity informativo
RN-ANA-006	Enlaces de compartir expiran en 24 horas	srv_fhir_exports.expires_at (Perfect)
RN-ANA-007	Exportacion para segunda opinion solo Perfect	srv_fhir_exports tabla completa
RN-ANA-008	Exportaciones se registran en log de auditoria	access_count, accessed_at en srv_fhir_exports
RN-ANA-009	Free: Solo almacenamiento local	No sincronizacion de datos (solo tier Pro/Perfect)
RN-ANA-010	Pro: Sync cloud + graficos	srv_analysis_sync + cli_parameter_history
RN-ANA-011	Perfect: Exportacion completa	srv_fhir_exports disponible

---

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_clinical_analyses	analysisType, analysisName, datePerformed, laboratory, prescriber, notes	Alto	Tipo y nombre de analisis revelan condicion medica
cli_analysis_results	parameterName, valueNumeric, valueText, unit, referenceMin, referenceMax	Critico	Valores de resultados son PHI critico
cli_analysis_parameters	parameterName, parameterCode, category, unit, targetValue	Alto	Parametros rastreados revelan condicion cronica
cli_parameter_history	parameterId, value, unit, dateRecorded	Critico	Historial de valores es PHI critico
cli_analysis_attachments	fileName, fileUrl, ocrText	Critico	Archivos contienen documentos medicos completos

CAMPOS LOCAL_ONLY (NUNCA SE SINCRONIZAN):

Entidad	Campos	Razon
cli_analysis_alerts	TODA LA ENTIDAD	Alertas se calculan localmente, no necesitan sync

6.2. Archivos Adjuntos

Los archivos adjuntos (PDFs, imagenes) se manejan de forma especial:

1. Almacenamiento Local:
2. Archivo original se cifra con AES-256-GCM
3. Se almacena en directorio seguro de la app

4. URL local apunta a archivo cifrado

5. Sincronizacion (Pro/Perfect):

6. Archivo cifrado se sube como blob binario
7. Servidor almacena blob opaco
8. Hash SHA-256 para verificar integridad

9. Thumbnail opcional (tambien cifrado)

10. OCR Opcional:

11. Se puede ejecutar OCR local (iOS Vision / Android ML Kit)
12. Texto extraido se almacena en ocrText (cifrado E2E)
13. Confianza del OCR en ocrConfidence

---

7. Indices y Performance

7.1. Cliente (SQLite/Realm)

// iOS Realm - Indices criticos
class ClinicalAnalysis: Object {
    @Persisted(indexed: true) var userId: String
    @Persisted(indexed: true) var analysisType: String
    @Persisted(indexed: true) var datePerformed: Date
    @Persisted(indexed: true) var isActive: Bool
}

class AnalysisResult: Object {
    @Persisted(indexed: true) var analysisId: String
    @Persisted(indexed: true) var parameterId: String
    @Persisted(indexed: true) var parameterName: String
    @Persisted(indexed: true) var isOutOfRange: Bool
}

class ParameterHistory: Object {
    @Persisted(indexed: true) var parameterId: String
    @Persisted(indexed: true) var dateRecorded: Date
}

// Android Room - Indices
@Entity(
    tableName = "cli_clinical_analyses",
    indices = [
        Index(value = ["user_id", "analysis_type"]),
        Index(value = ["date_performed"]),
        Index(value = ["is_active"])
    ]
)

@Entity(
    tableName = "cli_analysis_results",
    indices = [
        Index(value = ["analysis_id"]),
        Index(value = ["parameter_id"]),
        Index(value = ["parameter_name"]),
        Index(value = ["is_out_of_range"])
    ]
)

@Entity(
    tableName = "cli_parameter_history",
    indices = [
        Index(value = ["parameter_id", "date_recorded"])
    ]
)

7.2. Servidor (PostgreSQL)

-- Indices criticos para sync
CREATE INDEX idx_analysis_sync_user_type ON srv_analysis_sync(user_id, entity_type);
CREATE INDEX idx_analysis_sync_updated ON srv_analysis_sync(updated_at)
    WHERE is_deleted = FALSE;

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

-- Indices para FHIR exports
CREATE INDEX idx_fhir_exports_user ON srv_fhir_exports(user_id);
CREATE INDEX idx_fhir_exports_token ON srv_fhir_exports(access_token)
    WHERE is_active = TRUE AND expires_at > NOW();

---

8. Migraciones

8.1. Migration 001: Create Analysis Schema (Servidor)

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

BEGIN;

-- Tabla de sincronizacion de analisis
CREATE TABLE srv_analysis_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_analysis_entity UNIQUE (user_id, entity_type, entity_id)
);

-- Tabla de exportaciones FHIR
CREATE TABLE srv_fhir_exports (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES srv_users(id) ON DELETE CASCADE,
    export_type     VARCHAR(50) NOT NULL,
    fhir_version    VARCHAR(10) NOT NULL DEFAULT 'R4',
    encrypted_fhir  BYTEA NOT NULL,
    encryption_version VARCHAR(10) NOT NULL DEFAULT 'v1',
    nonce           BYTEA NOT NULL,
    auth_tag        BYTEA NOT NULL,
    resource_count  INTEGER NOT NULL,
    date_range_start DATE,
    date_range_end  DATE,
    is_anonymized   BOOLEAN DEFAULT FALSE,
    anonymization_level VARCHAR(20),
    access_token    VARCHAR(64) UNIQUE,
    expires_at      TIMESTAMPTZ,
    access_count    INTEGER DEFAULT 0,
    max_access      INTEGER DEFAULT 1,
    is_active       BOOLEAN DEFAULT TRUE,
    is_deleted      BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    accessed_at     TIMESTAMPTZ,
    CONSTRAINT chk_access_count CHECK (access_count <= max_access)
);

-- Indices
CREATE INDEX idx_analysis_sync_user ON srv_analysis_sync(user_id, entity_type);
CREATE INDEX idx_analysis_sync_updated ON srv_analysis_sync(updated_at);
CREATE INDEX idx_analysis_sync_deleted ON srv_analysis_sync(is_deleted)
    WHERE is_deleted = FALSE;

CREATE INDEX idx_fhir_exports_user ON srv_fhir_exports(user_id);
CREATE INDEX idx_fhir_exports_token ON srv_fhir_exports(access_token)
    WHERE is_active = TRUE AND expires_at > NOW();
CREATE INDEX idx_fhir_exports_expires ON srv_fhir_exports(expires_at)
    WHERE is_active = TRUE;

-- RLS
ALTER TABLE srv_analysis_sync ENABLE ROW LEVEL SECURITY;
ALTER TABLE srv_fhir_exports ENABLE ROW LEVEL SECURITY;

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

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

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

COMMIT;

8.2. Migration 001: Create Analysis 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: ClinicalAnalysis.className()) { oldObject, newObject in
                // Valores por defecto si vienen de version anterior
            }
        }
    }
)

Realm.Configuration.defaultConfiguration = config

8.3. Migration 001: Create Analysis 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 analisis
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS cli_clinical_analyses (
                id TEXT PRIMARY KEY NOT NULL,
                user_id TEXT NOT NULL,
                profile_id TEXT,
                analysis_type TEXT NOT NULL,
                analysis_name TEXT NOT NULL,
                date_performed INTEGER NOT NULL,
                date_uploaded INTEGER NOT NULL,
                laboratory TEXT,
                prescriber TEXT,
                has_file INTEGER NOT NULL DEFAULT 0,
                file_url TEXT,
                file_type TEXT,
                file_size_bytes INTEGER NOT NULL DEFAULT 0,
                notes TEXT,
                is_active INTEGER NOT NULL DEFAULT 1,
                is_deleted INTEGER NOT NULL DEFAULT 0,
                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
            )
        """)

        // Indices
        database.execSQL("""
            CREATE INDEX IF NOT EXISTS idx_analyses_user
            ON cli_clinical_analyses(user_id, analysis_type)
        """)

        database.execSQL("""
            CREATE INDEX IF NOT EXISTS idx_analyses_date
            ON cli_clinical_analyses(date_performed)
        """)

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

---

9. Referencias Cruzadas

Documento	Relacion
DB-ERD-001	Diagrama ER completo
MTS-ANA-001	Especificacion funcional
MDL-MED-001	Relacion con medicamentos
MDL-USR-001	FK a users
API-SYNC-001	Protocolo de sync
02-arquitectura-cliente-servidor.md	Arquitectura dual
04-seguridad-cliente.md	Cifrado E2E
05-seguridad-servidor.md	RLS policies

---

Documento generado por DatabaseDrone (Doce de Quince) - SpecQueen Technical Division "Los datos de salud son sagrados. LOCAL primero, SERVIDOR solo blobs cifrados."