Saltar a contenido

Modelo de Datos: Recetas Médicas

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

1. Introduccion

Este documento define el modelo de datos para el modulo de Recetas Medicas de MedTime. Permite al paciente digitalizar, organizar y gestionar recetas medicas con OCR local/servidor y vinculacion automatica a medicamentos.

1.1. Principios

Principio Aplicacion
95% Local OCR basico local (iOS Vision / Android ML Kit), vinculacion de medicamentos en dispositivo
Zero-Knowledge Servidor solo almacena blobs cifrados de recetas. OCR avanzado requiere anonimizacion manual
Offline-First Recetas y OCR basico funcionan completamente sin conexion
Sync para Pro/Perfect Solo usuarios Pro/Perfect sincronizan recetas. OCR avanzado solo Perfect

1.2. Clasificacion de Datos

Entidad Clasificacion Cifrado Sync Razon
cli_prescriptions SYNCED_E2E Si Pro/Perfect Datos de recetas son PHI critico
cli_prescription_medications SYNCED_E2E Si Pro/Perfect Medicamentos prescritos son PHI
cli_prescriber SYNCED_E2E Si Pro/Perfect Datos del medico son PHI
cli_issuing_institution SYNCED_E2E Si Pro/Perfect Institucion emisora es info medica
cli_prescription_images SYNCED_E2E Si Pro/Perfect Imagenes de recetas contienen PHI critico
cli_ocr_results SYNCED_E2E Si Pro/Perfect Texto extraido es PHI
srv_prescription_sync SERVER_ONLY N/A N/A Almacena blobs cifrados
srv_ocr_requests SERVER_ONLY No N/A Solicitudes de OCR avanzado (Perfect)

2. Entidades del Cliente (LOCAL/SYNCED_E2E)

2.1. cli_prescriptions

Recetas medicas del usuario.

2.1.1. Clasificacion

Campo Clasificacion Razon
userId LOCAL_ONLY Identificador local
prescriberId SYNCED_E2E FK a medico prescriptor (PHI)
institutionId SYNCED_E2E FK a institucion emisora (PHI)
prescriptionDate SYNCED_E2E Fecha de prescripcion es PHI
diagnosis SYNCED_E2E Diagnostico es PHI critico
notes SYNCED_E2E Notas pueden contener info medica

2.1.2. iOS - Swift/Realm

// ============================================================
// MODELO: Prescription
// Descripcion: Receta medica del usuario
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Prescriptor
    @Persisted var prescriberId: String?  // FK a cli_prescriber
    @Persisted var prescriberName: String?  // Nombre del medico (desnormalizado)

    // Institucion
    @Persisted var institutionId: String?  // FK a cli_issuing_institution
    @Persisted var institutionName: String?  // Nombre de institucion (desnormalizado)

    // Fechas
    @Persisted var prescriptionDate: Date = Date()  // Fecha de emision
    @Persisted var validUntil: Date?  // Fecha de vencimiento (opcional)
    @Persisted var dateUploaded: Date = Date()  // Fecha de carga

    // Diagnostico y tratamiento
    @Persisted var diagnosis: String?  // Diagnostico
    @Persisted var treatmentPurpose: String?  // Proposito del tratamiento
    @Persisted var notes: String?  // Notas adicionales

    // Metadata
    @Persisted var isRecurring: Bool = false  // Receta recurrente
    @Persisted var recurringType: String?  // monthly, quarterly, etc.

    // Imagenes
    @Persisted var hasImages: Bool = false
    @Persisted var imageCount: Int = 0

    // OCR
    @Persisted var hasOcr: Bool = false
    @Persisted var ocrProcessed: Bool = false
    @Persisted var ocrSource: String?  // local, server

    // Vinculacion a medicamentos
    @Persisted var linkedMedicationsCount: Int = 0
    @Persisted var allMedicationsLinked: Bool = false

    // Estado
    @Persisted var isActive: Bool = true
    @Persisted var isExpired: Bool = false
    @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 medications: List<PrescriptionMedication>
    @Persisted var images: List<PrescriptionImage>
    @Persisted var ocrResults: List<OcrResult>
}

2.1.3. Android - Kotlin/Room

// ============================================================
// MODELO: Prescription
// Descripcion: Receta medica 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_prescriptions",
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = ["userId"],
            childColumns = ["userId"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Prescriber::class,
            parentColumns = ["id"],
            childColumns = ["prescriber_id"],
            onDelete = ForeignKey.SET_NULL
        ),
        ForeignKey(
            entity = IssuingInstitution::class,
            parentColumns = ["id"],
            childColumns = ["institution_id"],
            onDelete = ForeignKey.SET_NULL
        )
    ],
    indices = [
        Index(value = ["user_id"]),
        Index(value = ["prescriber_id"]),
        Index(value = ["institution_id"]),
        Index(value = ["prescription_date"]),
        Index(value = ["is_active"])
    ]
)
data class Prescription(
    @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,

    // Prescriptor
    @ColumnInfo(name = "prescriber_id", index = true)
    val prescriberId: String? = null,

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

    // Institucion
    @ColumnInfo(name = "institution_id", index = true)
    val institutionId: String? = null,

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

    // Fechas
    @ColumnInfo(name = "prescription_date")
    val prescriptionDate: Instant = Instant.now(),

    @ColumnInfo(name = "valid_until")
    val validUntil: Instant? = null,

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

    // Diagnostico y tratamiento
    @ColumnInfo(name = "diagnosis")
    val diagnosis: String? = null,

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

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

    // Metadata
    @ColumnInfo(name = "is_recurring")
    val isRecurring: Boolean = false,

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

    // Imagenes
    @ColumnInfo(name = "has_images")
    val hasImages: Boolean = false,

    @ColumnInfo(name = "image_count")
    val imageCount: Int = 0,

    // OCR
    @ColumnInfo(name = "has_ocr")
    val hasOcr: Boolean = false,

    @ColumnInfo(name = "ocr_processed")
    val ocrProcessed: Boolean = false,

    @ColumnInfo(name = "ocr_source")
    val ocrSource: OcrSource? = null,

    // Vinculacion a medicamentos
    @ColumnInfo(name = "linked_medications_count")
    val linkedMedicationsCount: Int = 0,

    @ColumnInfo(name = "all_medications_linked")
    val allMedicationsLinked: Boolean = false,

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

    @ColumnInfo(name = "is_expired")
    val isExpired: Boolean = false,

    @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 OcrSource {
    LOCAL,    // iOS Vision / Android ML Kit
    SERVER    // OCR avanzado (Perfect tier)
}

2.2. cli_prescription_medications

Medicamentos en la receta.

2.2.1. Clasificacion

Campo Clasificacion Razon
prescriptionId SYNCED_E2E FK a receta (PHI)
medicationId SYNCED_E2E FK a medicamento (PHI)
medicationName SYNCED_E2E Nombre de medicamento es PHI
dosage SYNCED_E2E Dosis es PHI critico
instructions SYNCED_E2E Instrucciones son PHI

2.2.2. iOS - Swift/Realm

// ============================================================
// MODELO: PrescriptionMedication
// Descripcion: Medicamento en receta
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Referencias
    @Persisted(originProperty: "medications") var prescription: LinkingObjects<Prescription>
    @Persisted var prescriptionId: String = ""

    // Medicamento
    @Persisted var medicationId: String?  // FK a cli_medications (si vinculado)
    @Persisted var medicationName: String = ""  // Nombre del medicamento
    @Persisted var activeIngredient: String?  // Principio activo
    @Persisted var brandName: String?  // Marca comercial

    // Dosis
    @Persisted var dosage: String = ""  // ej: "500mg"
    @Persisted var dosageQuantity: Double?
    @Persisted var dosageUnit: String?  // mg, ml, UI, etc.

    // Forma farmaceutica
    @Persisted var pharmaceuticalForm: String?  // tablet, capsule, syrup, etc.

    // Instrucciones
    @Persisted var instructions: String?  // ej: "Tomar cada 8 horas"
    @Persisted var frequency: String?  // every_8h, daily, weekly, etc.
    @Persisted var duration: String?  // ej: "14 dias"
    @Persisted var route: String?  // oral, topical, IV, etc.

    // Cantidad prescrita
    @Persisted var quantity: Int?  // Numero de unidades
    @Persisted var refillable: Bool = false  // Puede resurtirse
    @Persisted var refillsAllowed: Int = 0  // Numero de resurtidos

    // Vinculacion
    @Persisted var isLinkedToMedication: Bool = false
    @Persisted var linkedAt: Date?

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

// ============================================================
// MODELO: PrescriptionMedication
// Descripcion: Medicamento en receta
// 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_prescription_medications",
    foreignKeys = [
        ForeignKey(
            entity = Prescription::class,
            parentColumns = ["id"],
            childColumns = ["prescription_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Medication::class,
            parentColumns = ["id"],
            childColumns = ["medication_id"],
            onDelete = ForeignKey.SET_NULL
        )
    ],
    indices = [
        Index(value = ["prescription_id"]),
        Index(value = ["medication_id"]),
        Index(value = ["medication_name"]),
        Index(value = ["is_linked_to_medication"])
    ]
)
data class PrescriptionMedication(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Referencias
    @ColumnInfo(name = "prescription_id", index = true)
    val prescriptionId: String = "",

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

    @ColumnInfo(name = "medication_name")
    val medicationName: String = "",

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

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

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

    @ColumnInfo(name = "dosage_quantity")
    val dosageQuantity: Double? = null,

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

    // Forma farmaceutica
    @ColumnInfo(name = "pharmaceutical_form")
    val pharmaceuticalForm: String? = null,

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

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

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

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

    // Cantidad prescrita
    @ColumnInfo(name = "quantity")
    val quantity: Int? = null,

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

    @ColumnInfo(name = "refills_allowed")
    val refillsAllowed: Int = 0,

    // Vinculacion
    @ColumnInfo(name = "is_linked_to_medication")
    val isLinkedToMedication: Boolean = false,

    @ColumnInfo(name = "linked_at")
    val linkedAt: Instant? = 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()
)

2.3. cli_prescriber

Datos del medico prescriptor.

2.3.1. Clasificacion

Campo Clasificacion Razon
userId LOCAL_ONLY Identificador local
fullName SYNCED_E2E Nombre del medico es PHI
licenseNumber SYNCED_E2E Cedula profesional es info medica
specialty SYNCED_E2E Especialidad revela condicion tratada
contact SYNCED_E2E Contacto del medico es info personal

2.3.2. iOS - Swift/Realm

// ============================================================
// MODELO: Prescriber
// Descripcion: Medico prescriptor
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Datos del medico
    @Persisted var fullName: String = ""
    @Persisted var licenseNumber: String?  // Cedula profesional
    @Persisted var specialty: String?  // Especialidad

    // Contacto
    @Persisted var phoneNumber: String?
    @Persisted var email: String?
    @Persisted var address: String?

    // Institucion
    @Persisted var primaryInstitutionId: String?  // FK a cli_issuing_institution
    @Persisted var primaryInstitutionName: String?

    // Metadata
    @Persisted var prescriptionCount: Int = 0  // Numero de recetas emitidas

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

2.3.3. Android - Kotlin/Room

// ============================================================
// MODELO: Prescriber
// Descripcion: Medico prescriptor
// 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_prescriber",
    foreignKeys = [
        ForeignKey(
            entity = IssuingInstitution::class,
            parentColumns = ["id"],
            childColumns = ["primary_institution_id"],
            onDelete = ForeignKey.SET_NULL
        )
    ],
    indices = [
        Index(value = ["user_id"]),
        Index(value = ["full_name"]),
        Index(value = ["specialty"]),
        Index(value = ["primary_institution_id"])
    ]
)
data class Prescriber(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Datos del medico
    @ColumnInfo(name = "full_name")
    val fullName: String = "",

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

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

    // Contacto
    @ColumnInfo(name = "phone_number")
    val phoneNumber: String? = null,

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

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

    // Institucion
    @ColumnInfo(name = "primary_institution_id", index = true)
    val primaryInstitutionId: String? = null,

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

    // Metadata
    @ColumnInfo(name = "prescription_count")
    val prescriptionCount: Int = 0,

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

Institucion emisora de la receta.

2.4.1. Clasificacion

Campo Clasificacion Razon
userId LOCAL_ONLY Identificador local
name SYNCED_E2E Nombre de institucion es info medica
type SYNCED_E2E Tipo de institucion revela tratamiento
address SYNCED_E2E Direccion es info personal

2.4.2. iOS - Swift/Realm

// ============================================================
// MODELO: IssuingInstitution
// Descripcion: Institucion emisora de recetas
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Datos de la institucion
    @Persisted var name: String = ""
    @Persisted var type: String?  // hospital, clinic, pharmacy, etc.

    // Contacto
    @Persisted var phoneNumber: String?
    @Persisted var email: String?
    @Persisted var website: String?

    // Direccion
    @Persisted var address: String?
    @Persisted var city: String?
    @Persisted var state: String?
    @Persisted var country: String?
    @Persisted var postalCode: String?

    // Metadata
    @Persisted var prescriptionCount: Int = 0  // Numero de recetas emitidas

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

2.4.3. Android - Kotlin/Room

// ============================================================
// MODELO: IssuingInstitution
// Descripcion: Institucion emisora de recetas
// 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_issuing_institution",
    indices = [
        Index(value = ["user_id"]),
        Index(value = ["name"]),
        Index(value = ["type"])
    ]
)
data class IssuingInstitution(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Datos de la institucion
    @ColumnInfo(name = "name")
    val name: String = "",

    @ColumnInfo(name = "type")
    val type: InstitutionType? = null,

    // Contacto
    @ColumnInfo(name = "phone_number")
    val phoneNumber: String? = null,

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

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

    // Direccion
    @ColumnInfo(name = "address")
    val address: String? = null,

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

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

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

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

    // Metadata
    @ColumnInfo(name = "prescription_count")
    val prescriptionCount: Int = 0,

    // 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 InstitutionType {
    HOSPITAL,
    CLINIC,
    PHARMACY,
    LABORATORY,
    OTHER
}

2.5. cli_prescription_images

Imagenes de recetas (originales y anonimizadas).

2.5.1. Clasificacion

Campo Clasificacion Razon
prescriptionId SYNCED_E2E FK a receta (PHI)
fileName SYNCED_E2E Nombre de archivo puede contener info
originalFileUrl SYNCED_E2E URL local cifrada (imagen original)
anonymizedFileUrl SYNCED_E2E URL local cifrada (imagen anonimizada)

NOTA: Se almacenan DOS versiones: original (nunca sale del dispositivo) y anonimizada (para OCR servidor).

2.5.2. iOS - Swift/Realm

// ============================================================
// MODELO: PrescriptionImage
// Descripcion: Imagen de receta (original + anonimizada)
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Referencias
    @Persisted(originProperty: "images") var prescription: LinkingObjects<Prescription>
    @Persisted var prescriptionId: String = ""

    // Archivo ORIGINAL (NUNCA se envia a servidor)
    @Persisted var originalFileName: String = ""
    @Persisted var originalFileUrl: String = ""  // URL local cifrada
    @Persisted var originalFileSizeBytes: Int = 0
    @Persisted var originalFileHash: String = ""  // SHA-256

    // Archivo ANONIMIZADO (para OCR servidor - Perfect)
    @Persisted var hasAnonymized: Bool = false
    @Persisted var anonymizedFileUrl: String?  // URL local cifrada
    @Persisted var anonymizedFileSizeBytes: Int?
    @Persisted var anonymizedFileHash: String?  // SHA-256

    // Metadata
    @Persisted var fileType: String = ""  // jpg, png, pdf
    @Persisted var mimeType: String = ""
    @Persisted var imageWidth: Int?
    @Persisted var imageHeight: Int?

    // Anonimizacion
    @Persisted var anonymizationConfirmed: Bool = false
    @Persisted var anonymizationMethod: String?  // manual, auto
    @Persisted var anonymizedAt: Date?

    // OCR
    @Persisted var ocrProcessed: Bool = false
    @Persisted var ocrResultId: String?  // FK a cli_ocr_results

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

// ============================================================
// MODELO: PrescriptionImage
// Descripcion: Imagen de receta (original + anonimizada)
// 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_prescription_images",
    foreignKeys = [
        ForeignKey(
            entity = Prescription::class,
            parentColumns = ["id"],
            childColumns = ["prescription_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = OcrResult::class,
            parentColumns = ["id"],
            childColumns = ["ocr_result_id"],
            onDelete = ForeignKey.SET_NULL
        )
    ],
    indices = [
        Index(value = ["prescription_id"]),
        Index(value = ["ocr_result_id"]),
        Index(value = ["file_type"])
    ]
)
data class PrescriptionImage(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Referencias
    @ColumnInfo(name = "prescription_id", index = true)
    val prescriptionId: String = "",

    // Archivo ORIGINAL (NUNCA se envia a servidor)
    @ColumnInfo(name = "original_file_name")
    val originalFileName: String = "",

    @ColumnInfo(name = "original_file_url")
    val originalFileUrl: String = "",

    @ColumnInfo(name = "original_file_size_bytes")
    val originalFileSizeBytes: Int = 0,

    @ColumnInfo(name = "original_file_hash")
    val originalFileHash: String = "",

    // Archivo ANONIMIZADO (para OCR servidor - Perfect)
    @ColumnInfo(name = "has_anonymized")
    val hasAnonymized: Boolean = false,

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

    @ColumnInfo(name = "anonymized_file_size_bytes")
    val anonymizedFileSizeBytes: Int? = null,

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

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

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

    @ColumnInfo(name = "image_width")
    val imageWidth: Int? = null,

    @ColumnInfo(name = "image_height")
    val imageHeight: Int? = null,

    // Anonimizacion
    @ColumnInfo(name = "anonymization_confirmed")
    val anonymizationConfirmed: Boolean = false,

    @ColumnInfo(name = "anonymization_method")
    val anonymizationMethod: AnonymizationMethod? = null,

    @ColumnInfo(name = "anonymized_at")
    val anonymizedAt: Instant? = null,

    // OCR
    @ColumnInfo(name = "ocr_processed")
    val ocrProcessed: Boolean = false,

    @ColumnInfo(name = "ocr_result_id", index = true)
    val ocrResultId: String? = 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()
)

enum class AnonymizationMethod {
    MANUAL,    // Usuario anonimizo manualmente
    AUTO       // Anonimizacion automatica (futuro)
}

2.6. cli_ocr_results

Resultados de OCR (local o servidor).

2.6.1. Clasificacion

Campo Clasificacion Razon
prescriptionId SYNCED_E2E FK a receta (PHI)
imageId SYNCED_E2E FK a imagen (PHI)
rawText SYNCED_E2E Texto extraido es PHI critico
structuredData SYNCED_E2E Datos estructurados son PHI

2.6.2. iOS - Swift/Realm

// ============================================================
// MODELO: OcrResult
// Descripcion: Resultado de OCR (local o servidor)
// Almacenamiento: Realm cifrado
// Sync: E2E via EncryptedBlob (Pro/Perfect)
// ============================================================

import RealmSwift

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

    // Referencias
    @Persisted(originProperty: "ocrResults") var prescription: LinkingObjects<Prescription>
    @Persisted var prescriptionId: String = ""
    @Persisted var imageId: String = ""  // FK a cli_prescription_images

    // Fuente del OCR
    @Persisted var source: String = ""  // local, server
    @Persisted var ocrEngine: String?  // iOS Vision, ML Kit, Server Engine

    // Texto extraido
    @Persisted var rawText: String = ""  // Texto completo sin procesar
    @Persisted var confidence: Double?  // Confianza del OCR (0.0 - 1.0)

    // Datos estructurados (JSON)
    @Persisted var structuredData: String?  // JSON con datos parseados
    // { medications: [...], prescriber: {...}, date: "...", etc. }

    // Medicamentos detectados
    @Persisted var medicationsDetected: Int = 0
    @Persisted var medicationsLinked: Int = 0

    // Estado
    @Persisted var isParsed: Bool = false
    @Persisted var needsReview: Bool = false  // Requiere revision manual
    @Persisted var reviewedAt: Date?

    // Errores
    @Persisted var hasErrors: Bool = false
    @Persisted var errorMessage: 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.6.3. Android - Kotlin/Room

// ============================================================
// MODELO: OcrResult
// Descripcion: Resultado de OCR (local o servidor)
// 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_ocr_results",
    foreignKeys = [
        ForeignKey(
            entity = Prescription::class,
            parentColumns = ["id"],
            childColumns = ["prescription_id"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = PrescriptionImage::class,
            parentColumns = ["id"],
            childColumns = ["image_id"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [
        Index(value = ["prescription_id"]),
        Index(value = ["image_id"]),
        Index(value = ["source"]),
        Index(value = ["needs_review"])
    ]
)
data class OcrResult(
    @PrimaryKey
    @ColumnInfo(name = "id")
    val id: String = UUID.randomUUID().toString(),

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

    // Referencias
    @ColumnInfo(name = "prescription_id", index = true)
    val prescriptionId: String = "",

    @ColumnInfo(name = "image_id", index = true)
    val imageId: String = "",

    // Fuente del OCR
    @ColumnInfo(name = "source")
    val source: OcrSource = OcrSource.LOCAL,

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

    // Texto extraido
    @ColumnInfo(name = "raw_text")
    val rawText: String = "",

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

    // Datos estructurados (JSON)
    @ColumnInfo(name = "structured_data")
    val structuredData: String? = null,

    // Medicamentos detectados
    @ColumnInfo(name = "medications_detected")
    val medicationsDetected: Int = 0,

    @ColumnInfo(name = "medications_linked")
    val medicationsLinked: Int = 0,

    // Estado
    @ColumnInfo(name = "is_parsed")
    val isParsed: Boolean = false,

    @ColumnInfo(name = "needs_review")
    val needsReview: Boolean = false,

    @ColumnInfo(name = "reviewed_at")
    val reviewedAt: Instant? = null,

    // Errores
    @ColumnInfo(name = "has_errors")
    val hasErrors: Boolean = false,

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

3. Entidades del Servidor (SYNCED_E2E Blobs)

3.1. srv_prescription_sync

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

3.1.1. Esquema PostgreSQL

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

CREATE TABLE srv_prescription_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,
    -- prescription, prescription_medication, prescriber,
    -- issuing_institution, prescription_image, ocr_result

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

-- Indices
CREATE INDEX idx_prescription_sync_user ON srv_prescription_sync(user_id, entity_type);
CREATE INDEX idx_prescription_sync_updated ON srv_prescription_sync(updated_at);
CREATE INDEX idx_prescription_sync_deleted ON srv_prescription_sync(is_deleted)
    WHERE is_deleted = FALSE;

-- RLS
ALTER TABLE srv_prescription_sync ENABLE ROW LEVEL SECURITY;

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

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

-- Funcion para limpieza de registros eliminados
CREATE OR REPLACE FUNCTION cleanup_deleted_prescription_sync()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    -- Eliminar registros marcados como deleted hace mas de 90 dias
    DELETE FROM srv_prescription_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_prescriptions prescription prescriberId, prescriptionDate, diagnosis, treatmentPurpose, notes user_id, version, timestamps
cli_prescription_medications prescription_medication prescriptionId, medicationId, medicationName, dosage, instructions user_id, version, isDeleted
cli_prescriber prescriber fullName, licenseNumber, specialty, phoneNumber, email, address user_id, version, isDeleted
cli_issuing_institution issuing_institution name, type, phoneNumber, email, address user_id, version, isDeleted
cli_prescription_images prescription_image prescriptionId, originalFileName, originalFileUrl, originalFileHash, anonymizedFileUrl, anonymizedFileHash user_id, version, isDeleted
cli_ocr_results ocr_result prescriptionId, imageId, rawText, structuredData user_id, version, isDeleted

3.2. srv_ocr_requests

Solicitudes de OCR avanzado (Perfect tier).

3.2.1. Esquema PostgreSQL

-- ============================================================
-- TABLA: srv_ocr_requests
-- Descripcion: Solicitudes de OCR avanzado (Perfect)
-- RLS: Usuario solo ve sus propias solicitudes
-- ============================================================

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

    -- Referencias a entidades cliente
    prescription_id UUID NOT NULL,  -- ID en cliente
    image_id        UUID NOT NULL,  -- ID en cliente

    -- Imagen anonimizada (temporal)
    anonymized_image BYTEA NOT NULL,
    image_format    VARCHAR(10) NOT NULL,  -- jpg, png
    image_size_bytes INTEGER NOT NULL,

    -- Confirmacion de anonimizacion
    anonymization_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
    anonymization_disclaimer_accepted BOOLEAN NOT NULL DEFAULT FALSE,

    -- Estado del procesamiento
    status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    -- pending, processing, completed, failed

    -- Resultado OCR (cifrado E2E)
    ocr_result_encrypted BYTEA,
    ocr_confidence  DOUBLE PRECISION,
    ocr_engine      VARCHAR(50),

    -- Metadata de cifrado resultado
    result_nonce    BYTEA,
    result_auth_tag BYTEA,

    -- Errores
    error_message   TEXT,
    retry_count     INTEGER DEFAULT 0,

    -- Limpieza automatica
    image_deleted_at TIMESTAMPTZ,  -- Imagen eliminada del servidor
    expires_at      TIMESTAMPTZ NOT NULL,  -- Solicitud expira en 24h

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

    -- Constraints
    CONSTRAINT chk_ocr_status CHECK (status IN ('pending', 'processing', 'completed', 'failed'))
);

-- Indices
CREATE INDEX idx_ocr_requests_user ON srv_ocr_requests(user_id);
CREATE INDEX idx_ocr_requests_status ON srv_ocr_requests(status)
    WHERE status IN ('pending', 'processing');
CREATE INDEX idx_ocr_requests_expires ON srv_ocr_requests(expires_at)
    WHERE status = 'pending';

-- RLS
ALTER TABLE srv_ocr_requests ENABLE ROW LEVEL SECURITY;

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

-- Funcion para eliminar imagen anonimizada despues de procesamiento
CREATE OR REPLACE FUNCTION delete_ocr_image_after_processing()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.status IN ('completed', 'failed') AND OLD.status NOT IN ('completed', 'failed') THEN
        NEW.anonymized_image := NULL;
        NEW.image_deleted_at := NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_delete_ocr_image
    BEFORE UPDATE ON srv_ocr_requests
    FOR EACH ROW
    WHEN (NEW.status IN ('completed', 'failed'))
    EXECUTE FUNCTION delete_ocr_image_after_processing();

-- Funcion para limpiar solicitudes expiradas (DV2-P3: DB-BAJO-002)
CREATE OR REPLACE FUNCTION cleanup_expired_ocr_requests()
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    -- Eliminar solicitudes expiradas hace mas de 7 dias
    DELETE FROM srv_ocr_requests
    WHERE expires_at < NOW() - INTERVAL '7 days';

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

-- Comentarios de Retention Policy (DV2-P3: DB-BAJO-002)
COMMENT ON TABLE srv_ocr_requests IS 'Solicitudes de OCR avanzado con retention automatico. Imagen anonimizada se elimina tras procesamiento (trigger). Solicitud completa se elimina 7 dias despues de expirar (cleanup job).';
COMMENT ON COLUMN srv_ocr_requests.expires_at IS 'Solicitud expira en 24 horas desde creacion. Cleanup job elimina solicitudes 7 dias despues de expirar.';
COMMENT ON COLUMN srv_ocr_requests.anonymized_image IS 'Imagen anonimizada TEMPORAL. Se elimina automaticamente al completar/fallar procesamiento (trigger trg_delete_ocr_image).';
COMMENT ON COLUMN srv_ocr_requests.image_deleted_at IS 'Timestamp de eliminacion de imagen. Si NULL, imagen aun presente (solo si status=pending/processing).';

4. Retention Policy y Limpieza Automatica (DV2-P3)

4.1. Politica de Retencion para srv_ocr_requests

DB-BAJO-002: Las solicitudes de OCR avanzado contienen imagenes anonimizadas temporales que DEBEN eliminarse para minimizar exposicion de datos.

4.1.1. Ciclo de Vida de Solicitud OCR

TIMELINE:
T=0h          T=procesamiento    T=24h (expires_at)    T=31 dias (cleanup)
  |                 |                    |                       |
CREATE          COMPLETE             EXPIRE              HARD DELETE
  |                 |                    |                       |
  v                 v                    v                       v
[PENDING] -------> [COMPLETED] -------> [EXPIRED] ----------> [DELETED]
  |                 |                    ^
  | anonymized_     | image_deleted=     | cleanup_expired_ocr_requests()
  | image present   | NOW()              | ejecuta cada hora
  |                 | image=NULL         |

4.1.2. Tabla de Retention

Componente Retencion Mecanismo Trigger/Job
anonymized_image Hasta procesamiento Automatico (trigger) trg_delete_ocr_image
ocr_result_encrypted 24 horas Expiracion expires_at = created_at + 24h
Solicitud completa 7 dias post-expiracion Cleanup job cleanup_expired_ocr_requests()
Metadata (logs) 90 dias Auditoria Separado en audit_trail

4.1.3. Cleanup Job Configuration

Frecuencia: Cada hora (cron: 0 * * * *)

Job SQL:

-- Ejecutar cada hora via pg_cron o scheduler externo
SELECT cleanup_expired_ocr_requests();

Monitoring:

-- Verificar solicitudes pendientes de limpieza
SELECT COUNT(*) as pending_cleanup,
       MIN(expires_at) as oldest_expired
FROM srv_ocr_requests
WHERE expires_at < NOW() - INTERVAL '7 days';

Alertas:

  • Si pending_cleanup > 1000: Alerta a DevOps (job no esta corriendo)
  • Si oldest_expired > 14 dias: Alerta critica (backlog de limpieza)

4.1.4. Emergency Cleanup

En caso de backlog critico:

-- Eliminar TODAS las solicitudes expiradas hace mas de 24 horas
-- (ignora grace period de 7 dias)
DELETE FROM srv_ocr_requests
WHERE status IN ('completed', 'failed')
  AND expires_at < NOW() - INTERVAL '24 hours';

4.2. Metricas de Retention

Metrica Target Alerta Si
Solicitudes en srv_ocr_requests < 10,000 > 50,000
Solicitudes con anonymized_image != NULL < 100 > 500
Solicitudes expiradas sin limpiar < 1,000 > 5,000
Tamano promedio de anonymized_image ~500 KB > 2 MB

5. Diagrama ER del Modulo

erDiagram
    %% CLIENTE
    cli_user ||--o{ cli_prescriptions : "has"
    cli_prescriptions ||--o{ cli_prescription_medications : "contains"
    cli_prescriptions ||--o{ cli_prescription_images : "has"
    cli_prescriptions ||--o{ cli_ocr_results : "generates"
    cli_prescriptions }o--|| cli_prescriber : "prescribed_by"
    cli_prescriptions }o--|| cli_issuing_institution : "issued_by"
    cli_prescription_medications }o--|| cli_medications : "links_to"
    cli_prescription_images ||--o| cli_ocr_results : "processed_by"
    cli_prescriber }o--|| cli_issuing_institution : "works_at"

    %% SERVIDOR
    srv_users ||--o{ srv_prescription_sync : "syncs"
    srv_users ||--o{ srv_ocr_requests : "requests"

    %% Entidades Cliente
    cli_prescriptions {
        string id PK
        string userId FK
        string prescriberId FK
        string institutionId FK
        date prescriptionDate
        string diagnosis
        string treatmentPurpose
        string notes
        boolean hasImages
        boolean hasOcr
        int linkedMedicationsCount
    }

    cli_prescription_medications {
        string id PK
        string userId
        string prescriptionId FK
        string medicationId FK
        string medicationName
        string dosage
        string instructions
        string frequency
        boolean isLinkedToMedication
    }

    cli_prescriber {
        string id PK
        string userId
        string fullName
        string licenseNumber
        string specialty
        string phoneNumber
        string email
        string primaryInstitutionId FK
    }

    cli_issuing_institution {
        string id PK
        string userId
        string name
        string type
        string address
        string phoneNumber
    }

    cli_prescription_images {
        string id PK
        string userId
        string prescriptionId FK
        string originalFileName
        string originalFileUrl
        string originalFileHash
        boolean hasAnonymized
        string anonymizedFileUrl
        boolean ocrProcessed
        string ocrResultId FK
    }

    cli_ocr_results {
        string id PK
        string userId
        string prescriptionId FK
        string imageId FK
        string source
        string rawText
        double confidence
        string structuredData
        int medicationsDetected
        boolean needsReview
    }

    %% Entidades Servidor
    srv_prescription_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_ocr_requests {
        uuid id PK
        uuid user_id FK
        uuid prescription_id
        uuid image_id
        bytea anonymized_image
        string status
        bytea ocr_result_encrypted
        double ocr_confidence
        timestamptz expires_at
        boolean anonymization_confirmed
    }

6. Reglas de Negocio Reflejadas en Modelo

ID Regla de Negocio Implementacion en Modelo
RN-RX-001 Imagen original NUNCA se envia a servidor cli_prescription_images almacena original y anonimizada por separado
RN-RX-002 OCR servidor requiere anonimizacion manual srv_ocr_requests.anonymization_confirmed obligatorio
RN-RX-003 Imagen en servidor se elimina tras procesamiento Trigger trg_delete_ocr_image en srv_ocr_requests
RN-RX-004 Solicitudes OCR expiran en 24 horas srv_ocr_requests.expires_at + funcion de cleanup
RN-RX-005 Medicamentos de receta pueden vincularse a calendario cli_prescription_medications.isLinkedToMedication + FK opcional
RN-RX-006 Free: Solo almacenamiento local No sincronizacion (solo tier Pro/Perfect)
RN-RX-007 Pro: OCR local + sync cloud OCR local siempre disponible
RN-RX-008 Perfect: OCR avanzado servidor srv_ocr_requests solo para Perfect
RN-RX-009 Recetas se mantienen indefinidamente Sin fecha de expiracion en modelo
RN-RX-010 Usuario debe aceptar disclaimer anonimizacion anonymization_disclaimer_accepted en srv_ocr_requests
RN-RX-011 Resultado OCR requiere revision manual si confianza < 80% cli_ocr_results.needsReview calculado por logica

7. Consideraciones de Cifrado y Anonimizacion

7.1. Campos PHI Cifrados E2E

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

Entidad Campos PHI Nivel PHI Razon
cli_prescriptions prescriptionDate, diagnosis, treatmentPurpose, notes Critico Diagnostico y tratamiento son PHI critico
cli_prescription_medications medicationName, dosage, instructions Critico Medicamentos prescritos son PHI critico
cli_prescriber fullName, licenseNumber, specialty, phoneNumber, email, address Alto Datos del medico son PHI
cli_issuing_institution name, type, address, phoneNumber Medio Institucion revela tratamiento
cli_prescription_images originalFileUrl, anonymizedFileUrl, fileHash Critico Imagenes contienen documentos medicos completos
cli_ocr_results rawText, structuredData Critico Texto extraido es PHI critico

7.2. Imagenes de Recetas

Las imagenes de recetas se manejan con un proceso de dos versiones:

7.2.1. Imagen Original (NUNCA sale del dispositivo)

FLUJO DE IMAGEN ORIGINAL:
+------------------------------------------------------------------+
| 1. Usuario toma foto de receta                                   |
| 2. Imagen se guarda en almacenamiento cifrado de la app          |
| 3. URL local: /app_data/prescriptions/original_<uuid>.jpg (cifrado) |
| 4. Hash SHA-256 para integridad                                  |
| 5. NUNCA se envia al servidor bajo NINGUNA circunstancia          |
+------------------------------------------------------------------+

7.2.2. Imagen Anonimizada (para OCR servidor - Perfect)

FLUJO DE IMAGEN ANONIMIZADA:
+------------------------------------------------------------------+
| 1. Usuario selecciona "OCR avanzado" (Perfect tier)              |
| 2. App muestra herramientas de anonimizacion:                    |
|    - Rectangulo de tachado (negro opaco)                         |
|    - Herramienta de blur/pixelado                                |
|    - Crop para recortar secciones                                |
| 3. Usuario anonimiza manualmente (tacha nombres, etc.)           |
| 4. App guarda VERSION ANONIMIZADA en almacenamiento cifrado      |
| 5. URL local: /app_data/prescriptions/anon_<uuid>.jpg (cifrado)  |
| 6. Usuario confirma "He revisado y anonimizado"                  |
| 7. Solo VERSION ANONIMIZADA se envia via TLS a servidor          |
| 8. Servidor procesa OCR y elimina imagen INMEDIATAMENTE          |
+------------------------------------------------------------------+

7.3. OCR Local vs Servidor

7.3.1. OCR Local (All Tiers)

Caracteristica Valor
Engine iOS Vision / Android ML Kit
Procesamiento 100% en dispositivo
Privacidad Imagen NUNCA sale del dispositivo
Precision Basica (70-85% confianza)
Idiomas Español, Ingles
Costo Gratis (incluido en OS)

7.3.2. OCR Avanzado Servidor (Perfect Tier Only)

Caracteristica Valor
Engine Google Cloud Vision AI / AWS Textract
Procesamiento Servidor (requiere GPU)
Privacidad Usuario DEBE anonimizar antes de enviar
Precision Alta (90-98% confianza)
Idiomas 100+ idiomas
Costo Incluido en tier Perfect
Retencion Imagen eliminada INMEDIATAMENTE tras procesamiento

Flujo de OCR Servidor con Anonimizacion:

sequenceDiagram
    participant U as Usuario
    participant A as App
    participant S as Servidor OCR

    U->>A: Toma foto de receta
    A->>A: Guardar imagen ORIGINAL (cifrada, local)
    A->>A: Ejecutar OCR local (basico)

    U->>A: "Quiero OCR avanzado"
    A->>A: Verificar tier = Perfect
    A->>A: Mostrar herramientas de anonimizacion

    U->>A: Anonimiza manualmente (tacha nombres, etc.)
    A->>A: Guardar imagen ANONIMIZADA (cifrada, local)
    A->>U: Mostrar disclaimer de anonimizacion

    U->>A: Confirma "He revisado y anonimizado"
    A->>S: POST /ocr/advanced {anonymized_image_encrypted}

    S->>S: Descifrar imagen anonimizada
    S->>S: Procesar OCR
    S->>S: Estructurar datos extraidos
    S->>S: ELIMINAR imagen inmediatamente
    S->>A: Cifrar resultado E2E
    S->>A: Retornar {ocr_result_encrypted}

    A->>A: Descifrar resultado
    A->>A: Parsear medicamentos, dosis, etc.
    A->>A: Guardar en cli_ocr_results (cifrado)
    A->>U: Mostrar resultados + sugerencias vinculacion

    Note over S: Servidor NO retiene la imagen<br/>procesamiento temporal, sin persistencia

8. Indices y Performance

8.1. Cliente (SQLite/Realm)

// iOS Realm - Indices criticos
class Prescription: Object {
    @Persisted(indexed: true) var userId: String
    @Persisted(indexed: true) var prescriberId: String
    @Persisted(indexed: true) var prescriptionDate: Date
    @Persisted(indexed: true) var isActive: Bool
}

class PrescriptionMedication: Object {
    @Persisted(indexed: true) var prescriptionId: String
    @Persisted(indexed: true) var medicationId: String
    @Persisted(indexed: true) var medicationName: String
    @Persisted(indexed: true) var isLinkedToMedication: Bool
}

class OcrResult: Object {
    @Persisted(indexed: true) var prescriptionId: String
    @Persisted(indexed: true) var imageId: String
    @Persisted(indexed: true) var source: String
    @Persisted(indexed: true) var needsReview: Bool
}

// Android Room - Indices
@Entity(
    tableName = "cli_prescriptions",
    indices = [
        Index(value = ["user_id", "prescription_date"]),
        Index(value = ["prescriber_id"]),
        Index(value = ["institution_id"]),
        Index(value = ["is_active"])
    ]
)

@Entity(
    tableName = "cli_prescription_medications",
    indices = [
        Index(value = ["prescription_id"]),
        Index(value = ["medication_id"]),
        Index(value = ["medication_name"]),
        Index(value = ["is_linked_to_medication"])
    ]
)

@Entity(
    tableName = "cli_ocr_results",
    indices = [
        Index(value = ["prescription_id"]),
        Index(value = ["image_id"]),
        Index(value = ["source"]),
        Index(value = ["needs_review"])
    ]
)

8.2. Servidor (PostgreSQL)

-- Indices criticos para sync
CREATE INDEX idx_prescription_sync_user_type ON srv_prescription_sync(user_id, entity_type);
CREATE INDEX idx_prescription_sync_updated ON srv_prescription_sync(updated_at)
    WHERE is_deleted = FALSE;

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

-- Indices para OCR requests
CREATE INDEX idx_ocr_requests_user ON srv_ocr_requests(user_id);
CREATE INDEX idx_ocr_requests_status ON srv_ocr_requests(status)
    WHERE status IN ('pending', 'processing');
CREATE INDEX idx_ocr_requests_expires ON srv_ocr_requests(expires_at);

9. Migraciones

9.1. Migration 001: Create Prescriptions Schema (Servidor)

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

BEGIN;

-- Tabla de sincronizacion de recetas
CREATE TABLE srv_prescription_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_prescription_entity UNIQUE (user_id, entity_type, entity_id)
);

-- Tabla de solicitudes OCR
CREATE TABLE srv_ocr_requests (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES srv_users(id) ON DELETE CASCADE,
    prescription_id UUID NOT NULL,
    image_id        UUID NOT NULL,
    anonymized_image BYTEA NOT NULL,
    image_format    VARCHAR(10) NOT NULL,
    image_size_bytes INTEGER NOT NULL,
    anonymization_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
    anonymization_disclaimer_accepted BOOLEAN NOT NULL DEFAULT FALSE,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    ocr_result_encrypted BYTEA,
    ocr_confidence  DOUBLE PRECISION,
    ocr_engine      VARCHAR(50),
    result_nonce    BYTEA,
    result_auth_tag BYTEA,
    error_message   TEXT,
    retry_count     INTEGER DEFAULT 0,
    image_deleted_at TIMESTAMPTZ,
    expires_at      TIMESTAMPTZ NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    processed_at    TIMESTAMPTZ,
    CONSTRAINT chk_ocr_status CHECK (status IN ('pending', 'processing', 'completed', 'failed'))
);

-- Indices
CREATE INDEX idx_prescription_sync_user ON srv_prescription_sync(user_id, entity_type);
CREATE INDEX idx_prescription_sync_updated ON srv_prescription_sync(updated_at);
CREATE INDEX idx_prescription_sync_deleted ON srv_prescription_sync(is_deleted)
    WHERE is_deleted = FALSE;

CREATE INDEX idx_ocr_requests_user ON srv_ocr_requests(user_id);
CREATE INDEX idx_ocr_requests_status ON srv_ocr_requests(status)
    WHERE status IN ('pending', 'processing');
CREATE INDEX idx_ocr_requests_expires ON srv_ocr_requests(expires_at)
    WHERE status = 'pending';

-- RLS
ALTER TABLE srv_prescription_sync ENABLE ROW LEVEL SECURITY;
ALTER TABLE srv_ocr_requests ENABLE ROW LEVEL SECURITY;

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

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

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

CREATE TRIGGER trg_delete_ocr_image
    BEFORE UPDATE ON srv_ocr_requests
    FOR EACH ROW
    WHEN (NEW.status IN ('completed', 'failed'))
    EXECUTE FUNCTION delete_ocr_image_after_processing();

COMMIT;

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

Realm.Configuration.defaultConfiguration = config

9.3. Migration 001: Create Prescriptions 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 recetas
        database.execSQL("""
            CREATE TABLE IF NOT EXISTS cli_prescriptions (
                id TEXT PRIMARY KEY NOT NULL,
                user_id TEXT NOT NULL,
                profile_id TEXT,
                prescriber_id TEXT,
                prescriber_name TEXT,
                institution_id TEXT,
                institution_name TEXT,
                prescription_date INTEGER NOT NULL,
                valid_until INTEGER,
                date_uploaded INTEGER NOT NULL,
                diagnosis TEXT,
                treatment_purpose TEXT,
                notes TEXT,
                is_recurring INTEGER NOT NULL DEFAULT 0,
                recurring_type TEXT,
                has_images INTEGER NOT NULL DEFAULT 0,
                image_count INTEGER NOT NULL DEFAULT 0,
                has_ocr INTEGER NOT NULL DEFAULT 0,
                ocr_processed INTEGER NOT NULL DEFAULT 0,
                ocr_source TEXT,
                linked_medications_count INTEGER NOT NULL DEFAULT 0,
                all_medications_linked INTEGER NOT NULL DEFAULT 0,
                is_active INTEGER NOT NULL DEFAULT 1,
                is_expired INTEGER NOT NULL DEFAULT 0,
                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_prescriptions_user
            ON cli_prescriptions(user_id, prescription_date)
        """)

        database.execSQL("""
            CREATE INDEX IF NOT EXISTS idx_prescriptions_prescriber
            ON cli_prescriptions(prescriber_id)
        """)

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

10. Referencias Cruzadas

Documento Relacion
DB-ERD-001 Diagrama ER completo
MTS-RX-001 Especificacion funcional
MDL-MED-001 Vinculacion de medicamentos
MDL-USR-001 FK a users
API-SYNC-001 Protocolo de sync
API-OCR-001 API de OCR avanzado
02-arquitectura-cliente-servidor.md Arquitectura dual
04-seguridad-cliente.md Cifrado E2E
05-seguridad-servidor.md RLS policies
INV-015-ocr-anonimizacion.md Proceso de anonimizacion de recetas

Documento generado por DatabaseDrone (Doce de Quince) - SpecQueen Technical Division "Las recetas son documentos medicos criticos. LOCAL primero, anonimizacion SIEMPRE antes de servidor."