Modelo de Datos: Medicamentos

Identificador: MDL-MED-001 Version: 1.0.0 Fecha: 2025-12-07 Estado: Aprobado Autor: DatabaseDrone / SpecQueen Technical Division Modulo Funcional: MTS-MED-001

---

• 1. Introduccion
• 1.1. Principios
• 1.2. Clasificacion de Datos
• 2. Entidades del Servidor (Catalogos Publicos)
• 2.1. srv_drug_catalog
  • 2.1.1. Columnas
• 2.2. srv_drug_forms
• 2.3. srv_drug_interactions
• 3. Entidades del Cliente (SYNCED_E2E)
• 3.1. cli_medications
  • 3.1.1. Campos PHI (Cifrados E2E)
• 3.2. cli_schedules
  • 3.2.1. Tipos de Frecuencia
• 3.3. cli_dose_logs
  • 3.3.1. Estados de Dosis
  • 3.3.2. Razones de Skip
• 3.4. cli_inventory
• 3.5. cli_refill_history
• 4. Diagrama ER del Modulo
• 5. Funciones de Base de Datos
• 5.1. Busqueda de Medicamentos
• 5.2. Verificar Interacciones
• 6. Migraciones
• 6.1. Migration 001: Create Medications Schema
• 7. Referencias Cruzadas

---

1. Introduccion

Este documento define el modelo de datos para el modulo de medicamentos de MedTime. Incluye tablas del servidor (PostgreSQL) para catalogos publicos y tablas del cliente (SQLite/Realm) para datos del usuario cifrados E2E.

1.1. Principios

Principio	Aplicacion
Catalogo Publico	srv_drug_catalog es accesible sin autenticacion
Datos Usuario E2E	cli_medications y relacionados se cifran en cliente
Zero-Knowledge	Servidor solo almacena blobs opacos de medicamentos del usuario
Offline-First	Cliente mantiene copia local para funcionamiento sin conexion

1.2. Clasificacion de Datos

Entidad	Clasificacion	Cifrado	Sync
srv_drug_catalog	SERVER_SOURCE	No	Download only
srv_drug_forms	SERVER_SOURCE	No	Download only
srv_drug_interactions	SERVER_SOURCE	No	Download only
cli_medications	SYNCED_E2E	Si	Bidireccional
cli_schedules	SYNCED_E2E	Si	Bidireccional
cli_dose_logs	SYNCED_E2E	Si	Bidireccional
cli_inventory	SYNCED_E2E	Si	Bidireccional
cli_refill_history	SYNCED_E2E	Si	Bidireccional

---

2. Entidades del Servidor (Catalogos Publicos)

2.1. srv_drug_catalog

Catalogo de medicamentos basado en RxNorm y fuentes locales (Mexico).

CREATE TABLE srv_drug_catalog (
    -- Primary Key
    drug_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Identificadores Externos
    rxnorm_code     VARCHAR(20) UNIQUE,     -- RxNorm CUI (USA)
    ndc_code        VARCHAR(20),            -- National Drug Code (USA)
    ean_code        VARCHAR(20),            -- EAN/UPC barcode
    cofepris_reg    VARCHAR(50),            -- Registro sanitario COFEPRIS (Mexico)

    -- Nombres
    name_generic    VARCHAR(500) NOT NULL,  -- Nombre generico
    name_brand      VARCHAR(500),           -- Nombre comercial
    name_synonyms   TEXT[],                 -- Sinonimos y variantes

    -- Clasificacion
    atc_code        VARCHAR(10),            -- Anatomical Therapeutic Chemical code
    category        VARCHAR(100),           -- Categoria terapeutica
    therapeutic_class VARCHAR(200),         -- Clase terapeutica
    pharmacological_class VARCHAR(200),     -- Clase farmacologica

    -- Regulacion
    requires_rx     BOOLEAN NOT NULL DEFAULT FALSE,  -- Requiere receta
    controlled      BOOLEAN NOT NULL DEFAULT FALSE,  -- Sustancia controlada
    controlled_schedule VARCHAR(20),                 -- Fraccion (I, II, III, IV, V)

    -- Metadata
    source          VARCHAR(50) NOT NULL DEFAULT 'manual',  -- rxnorm, manual, import
    source_version  VARCHAR(20),
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Full-text search
    search_vector   TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('spanish', COALESCE(name_generic, '')), 'A') ||
        setweight(to_tsvector('spanish', COALESCE(name_brand, '')), 'B') ||
        setweight(to_tsvector('spanish', COALESCE(array_to_string(name_synonyms, ' '), '')), 'C')
    ) STORED
);

-- Indexes
CREATE UNIQUE INDEX idx_drugs_rxnorm ON srv_drug_catalog(rxnorm_code) WHERE rxnorm_code IS NOT NULL;
CREATE INDEX idx_drugs_cofepris ON srv_drug_catalog(cofepris_reg) WHERE cofepris_reg IS NOT NULL;
CREATE INDEX idx_drugs_atc ON srv_drug_catalog(atc_code);
CREATE INDEX idx_drugs_category ON srv_drug_catalog(category);
CREATE INDEX idx_drugs_controlled ON srv_drug_catalog(controlled) WHERE controlled = TRUE;
CREATE INDEX idx_drugs_active ON srv_drug_catalog(is_active);
CREATE INDEX idx_drugs_search ON srv_drug_catalog USING GIN(search_vector);

-- No RLS - public catalog

2.1.1. Columnas

Columna	Tipo	Nullable	Descripcion
drug_id	UUID	No	Identificador unico
rxnorm_code	VARCHAR(20)	Si	Codigo RxNorm (USA)
ndc_code	VARCHAR(20)	Si	National Drug Code
ean_code	VARCHAR(20)	Si	Codigo de barras
cofepris_reg	VARCHAR(50)	Si	Registro COFEPRIS (Mexico)
name_generic	VARCHAR(500)	No	Nombre generico
name_brand	VARCHAR(500)	Si	Nombre comercial
name_synonyms	TEXT[]	Si	Sinonimos
atc_code	VARCHAR(10)	Si	Codigo ATC
category	VARCHAR(100)	Si	Categoria terapeutica
therapeutic_class	VARCHAR(200)	Si	Clase terapeutica
pharmacological_class	VARCHAR(200)	Si	Clase farmacologica
requires_rx	BOOLEAN	No	Requiere receta
controlled	BOOLEAN	No	Sustancia controlada
controlled_schedule	VARCHAR(20)	Si	Fraccion de control
source	VARCHAR(50)	No	Fuente de datos
is_active	BOOLEAN	No	Activo en catalogo
created_at	TIMESTAMPTZ	No	Fecha creacion
updated_at	TIMESTAMPTZ	No	Ultima actualizacion

---

2.2. srv_drug_forms

Formas farmaceuticas y presentaciones.

CREATE TABLE srv_drug_forms (
    -- Primary Key
    form_id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Foreign Key
    drug_id         UUID NOT NULL REFERENCES srv_drug_catalog(drug_id) ON DELETE CASCADE,

    -- Forma Farmaceutica
    form_name       VARCHAR(100) NOT NULL,  -- tableta, capsula, jarabe, etc.
    form_code       VARCHAR(20),            -- Codigo estandar de forma

    -- Via de Administracion
    route           VARCHAR(50) NOT NULL,   -- oral, topica, inyectable, etc.
    route_code      VARCHAR(20),            -- Codigo estandar de via

    -- Concentracion
    strength        VARCHAR(100),           -- "500 mg", "250 mg/5ml"
    strength_value  DECIMAL(10,4),          -- Valor numerico
    strength_unit   VARCHAR(20),            -- mg, ml, mcg, etc.

    -- Presentacion
    package_size    INT,                    -- Unidades por empaque
    package_unit    VARCHAR(50),            -- tabletas, ml, etc.

    -- Metadata
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_forms_drug ON srv_drug_forms(drug_id);
CREATE INDEX idx_forms_route ON srv_drug_forms(route);
CREATE INDEX idx_forms_form ON srv_drug_forms(form_name);

-- No RLS - public catalog

---

2.3. srv_drug_interactions

Interacciones medicamento-medicamento.

CREATE TABLE srv_drug_interactions (
    -- Primary Key
    interaction_id  UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Foreign Keys (bidirectional relationship)
    drug_a_id       UUID NOT NULL REFERENCES srv_drug_catalog(drug_id) ON DELETE CASCADE,
    drug_b_id       UUID NOT NULL REFERENCES srv_drug_catalog(drug_id) ON DELETE CASCADE,

    -- Severidad
    severity        VARCHAR(20) NOT NULL CHECK (severity IN (
        'contraindicated',  -- Contraindicacion absoluta
        'severe',           -- Interaccion severa
        'moderate',         -- Interaccion moderada
        'minor',            -- Interaccion menor
        'unknown'           -- Severidad desconocida
    )),

    -- Descripcion
    description     TEXT NOT NULL,          -- Descripcion de la interaccion
    mechanism       TEXT,                   -- Mecanismo de interaccion
    clinical_effect TEXT,                   -- Efecto clinico esperado
    management      TEXT,                   -- Recomendaciones de manejo

    -- Evidencia
    evidence_level  VARCHAR(20) CHECK (evidence_level IN (
        'established',      -- Bien establecida
        'probable',         -- Probable
        'suspected',        -- Sospechada
        'possible',         -- Posible
        'unlikely'          -- Improbable
    )),
    references      TEXT[],                 -- Referencias bibliograficas

    -- Metadata
    source          VARCHAR(50) NOT NULL,   -- drugbank, rxnorm, manual
    source_id       VARCHAR(100),           -- ID en fuente original
    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Constraints
    CONSTRAINT chk_different_drugs CHECK (drug_a_id != drug_b_id),
    CONSTRAINT uq_interaction UNIQUE (drug_a_id, drug_b_id)
);

-- Indexes
CREATE INDEX idx_interactions_drug_a ON srv_drug_interactions(drug_a_id);
CREATE INDEX idx_interactions_drug_b ON srv_drug_interactions(drug_b_id);
CREATE INDEX idx_interactions_severity ON srv_drug_interactions(severity);
CREATE INDEX idx_interactions_active ON srv_drug_interactions(is_active) WHERE is_active = TRUE;

-- Function to check interaction bidirectionally
CREATE OR REPLACE FUNCTION get_drug_interactions(p_drug_id UUID)
RETURNS TABLE (
    interaction_id UUID,
    other_drug_id UUID,
    other_drug_name VARCHAR,
    severity VARCHAR,
    description TEXT,
    management TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        i.interaction_id,
        CASE WHEN i.drug_a_id = p_drug_id THEN i.drug_b_id ELSE i.drug_a_id END,
        CASE WHEN i.drug_a_id = p_drug_id THEN d.name_generic ELSE da.name_generic END,
        i.severity,
        i.description,
        i.management
    FROM srv_drug_interactions i
    JOIN srv_drug_catalog d ON d.drug_id = CASE WHEN i.drug_a_id = p_drug_id THEN i.drug_b_id ELSE i.drug_a_id END
    LEFT JOIN srv_drug_catalog da ON da.drug_id = i.drug_a_id
    WHERE (i.drug_a_id = p_drug_id OR i.drug_b_id = p_drug_id)
      AND i.is_active = TRUE;
END;
$$ LANGUAGE plpgsql;

---

3. Entidades del Cliente (SYNCED_E2E)

3.1. cli_medications

Medicamentos del usuario (cifrado E2E).

// iOS - Swift/Realm
class Medication: Object {
    @Persisted(primaryKey: true) var medicationId: String = UUID().uuidString

    // Owner
    @Persisted var userId: String

    // Referencia a catalogo (opcional)
    @Persisted var catalogDrugId: String?

    // Datos del medicamento
    @Persisted var customName: String        // Nombre personalizado o del catalogo
    @Persisted var dosage: String            // "500 mg", "2 tabletas"
    @Persisted var unit: String              // mg, ml, tabletas
    @Persisted var form: String              // tableta, capsula, jarabe
    @Persisted var route: String             // oral, topica, etc.

    // Instrucciones
    @Persisted var instructions: String?     // "Tomar con alimentos"
    @Persisted var prescriber: String?       // Nombre del medico
    @Persisted var pharmacy: String?         // Farmacia de surtido

    // Estado
    @Persisted var isActive: Bool = true
    @Persisted var startDate: Date?
    @Persisted var endDate: Date?
    @Persisted var reason: String?           // Razon de uso

    // Sync
    @Persisted var createdAt: Date = Date()
    @Persisted var updatedAt: Date = Date()
    @Persisted var syncVersion: Int64 = 0
    @Persisted var isDeleted: Bool = false

    // Relationships
    @Persisted var schedules: List<Schedule>
    @Persisted var inventory: Inventory?
}

// Android - Kotlin/Room
@Entity(
    tableName = "cli_medications",
    foreignKeys = [
        ForeignKey(
            entity = User::class,
            parentColumns = ["userId"],
            childColumns = ["userId"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    indices = [Index("userId"), Index("isActive")]
)
data class Medication(
    @PrimaryKey
    val medicationId: String = UUID.randomUUID().toString(),

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

    val catalogDrugId: String?,

    val customName: String,
    val dosage: String,
    val unit: String,
    val form: String,
    val route: String,

    val instructions: String?,
    val prescriber: String?,
    val pharmacy: String?,

    val isActive: Boolean = true,
    val startDate: Long?,
    val endDate: Long?,
    val reason: String?,

    val createdAt: Long = System.currentTimeMillis(),
    val updatedAt: Long = System.currentTimeMillis(),
    val syncVersion: Long = 0,
    val isDeleted: Boolean = false
)

3.1.1. Campos PHI (Cifrados E2E)

Campo	Nivel PHI	Razon
catalogDrugId	Alto	Revela que farmaco usa el usuario (inferencia de condicion)
customName	Alto	Revela condicion medica
dosage	Alto	Revela tratamiento
instructions	Alto	Detalles de tratamiento
prescriber	Medio	Datos de profesional
reason	Alto	Revela diagnostico

NOTA: catalogDrugId se incluye en el blob cifrado E2E. El servidor solo ve un blob opaco y NO puede determinar que medicamento del catalogo usa el usuario.

IMPORTANTE - Regla de 100 Registros: El cliente NO descarga el catalogo completo de medicamentos (50K+ registros). En su lugar: 1. El usuario busca medicamentos online via API /v1/catalog/drugs?q=... 2. El servidor retorna resultados paginados (maximo 50 por pagina) 3. El usuario selecciona un medicamento de los resultados 4. Solo el catalogDrugId seleccionado se guarda localmente (cifrado E2E) 5. El servidor VE la busqueda pero NO sabe quien selecciono que (ver INV-015)

Ver 02-arquitectura-cliente-servidor.md seccion 3.2.3.1 para detalles de la Regla de 100 Registros.

---

3.2. cli_schedules

Horarios de medicamentos.

// iOS - Swift/Realm
class Schedule: Object {
    @Persisted(primaryKey: true) var scheduleId: String = UUID().uuidString

    // Foreign Key
    @Persisted(originProperty: "schedules") var medication: LinkingObjects<Medication>

    // Tipo de Frecuencia
    @Persisted var frequencyType: String     // daily, weekly, interval, as_needed

    // Configuracion Daily
    @Persisted var timesPerDay: Int = 1
    @Persisted var specificTimes: List<String>  // ["08:00", "14:00", "20:00"]

    // Configuracion Weekly
    @Persisted var daysOfWeek: List<Int>     // [1,3,5] = Lun, Mie, Vie

    // Configuracion Interval
    @Persisted var intervalHours: Int?       // Cada N horas

    // Vigencia
    @Persisted var startDate: Date
    @Persisted var endDate: Date?
    @Persisted var isActive: Bool = true

    // Recordatorios
    @Persisted var reminderOffset: Int = 0   // Minutos antes (-) o despues (+)
    @Persisted var reminderEnabled: Bool = true

    // Sync
    @Persisted var createdAt: Date = Date()
    @Persisted var updatedAt: Date = Date()
    @Persisted var syncVersion: Int64 = 0

    // Relationships
    @Persisted var doseLogs: List<DoseLog>
}

3.2.1. Tipos de Frecuencia

Tipo	Descripcion	Campos Usados
daily	Diario a horas fijas	timesPerDay, specificTimes
weekly	Dias especificos de la semana	daysOfWeek, specificTimes
interval	Cada N horas	intervalHours
as_needed	Segun necesidad (PRN)	(ninguno adicional)
cyclic	Ciclos (ej: 21 dias + 7 descanso)	(extension futura)

---

3.3. cli_dose_logs

Registro de tomas de medicamentos.

// iOS - Swift/Realm
class DoseLog: Object {
    @Persisted(primaryKey: true) var logId: String = UUID().uuidString

    // Foreign Key
    @Persisted(originProperty: "doseLogs") var schedule: LinkingObjects<Schedule>

    // Tiempos
    @Persisted var scheduledTime: Date       // Hora programada
    @Persisted var actualTime: Date?         // Hora real de toma
    @Persisted var respondedAt: Date?        // Cuando respondio el usuario

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

    // Detalles (si skipped)
    @Persisted var skippedReason: String?    // forgot, side_effects, ran_out, other
    @Persisted var skippedNotes: String?

    // Confirmacion
    @Persisted var confirmedBy: String?      // user_id de quien confirmo
    @Persisted var confirmedByRole: String?  // PI, CR, CS

    // Ubicacion (opcional, requiere consentimiento explicito)
    // Ver seccion 3.3.3 para requisitos de consentimiento
    @Persisted var locationLat: Double?
    @Persisted var locationLon: Double?
    @Persisted var locationAccuracy: Double?

    // Notas
    @Persisted var notes: String?

    // Sync
    @Persisted var createdAt: Date = Date()
    @Persisted var syncVersion: Int64 = 0
}

3.3.1. Estados de Dosis

Estado	Descripcion	Transiciones Permitidas
pending	Esperando accion	taken, skipped, missed
taken	Confirmada como tomada	(ninguna)
skipped	Saltada intencionalmente	(ninguna)
missed	No tomada, ventana cerrada	(ninguna)

3.3.2. Razones de Skip

Razon	Descripcion
forgot	Olvido
side_effects	Efectos secundarios
ran_out	Se acabo el medicamento
feeling_better	Se siente mejor
doctor_advice	Indicacion medica
other	Otra razon

3.3.3. Consentimiento de Ubicacion

Los campos de ubicacion (locationLat, locationLon, locationAccuracy) son opcionalesy requierenconsentimiento explicito del usuario antes de ser capturados.

Requisitos de Consentimiento:

Aspecto	Requisito
Tipo	Consentimiento explicito (opt-in)
Granularidad	Por registro de dosis individual
Revocable	Si, en cualquier momento
Default	Deshabilitado (null)
Persistencia	LOCAL_ONLY (nunca sincroniza al servidor)

Flujo de Consentimiento:

1. Usuario activa opcion "Registrar ubicacion" en configuracion
2. App muestra explicacion de uso: "La ubicacion se usa para patrones de adherencia locales"
3. App solicita permiso de ubicacion del sistema operativo
4. Si usuario acepta, ubicacion se registra con cada dosis tomada
5. Datos de ubicacion NUNCA salen del dispositivo (LOCAL_ONLY)

Clasificacion de Datos:

locationLat, locationLon, locationAccuracy:
- Clasificacion: LOCAL_ONLY
- Sincronizacion: NUNCA
- Proposito: Analisis de patrones locales (ML on-device)
- GDPR/LGPD: Datos de ubicacion son datos personales
- Retencion: Hasta eliminacion de cuenta o revocacion de consentimiento

Uso Permitido:

• Analisis de patrones locales por ML on-device
• Deteccion de contexto (casa, trabajo) para optimizar alertas

Uso Prohibido:

• Sincronizacion con servidor
• Compartir con terceros
• Tracking continuo

---

3.4. cli_inventory

Inventario de medicamentos.

// iOS - Swift/Realm
class Inventory: Object {
    @Persisted(primaryKey: true) var inventoryId: String = UUID().uuidString

    // Foreign Key (1:1 with Medication)
    @Persisted(originProperty: "inventory") var medication: LinkingObjects<Medication>

    // Cantidades
    @Persisted var quantityCurrent: Double   // Cantidad actual
    @Persisted var quantityInitial: Double   // Cantidad inicial del lote
    @Persisted var unit: String              // tabletas, ml, etc.

    // Informacion del Lote
    @Persisted var expirationDate: Date?
    @Persisted var lotNumber: String?
    @Persisted var barcode: String?

    // Alertas
    @Persisted var lowStockThreshold: Double = 7  // Dias o unidades
    @Persisted var lowStockAlertEnabled: Bool = true
    @Persisted var expiryAlertDays: Int = 30      // Dias antes de caducidad

    // Sync
    @Persisted var updatedAt: Date = Date()
    @Persisted var syncVersion: Int64 = 0

    // Relationships
    @Persisted var refillHistory: List<RefillHistory>

    // Computed
    var daysRemaining: Int {
        // Calcular basado en schedule de medication
        return 0 // placeholder
    }
}

---

3.5. cli_refill_history

Historial de resurtidos.

// iOS - Swift/Realm
class RefillHistory: Object {
    @Persisted(primaryKey: true) var refillId: String = UUID().uuidString

    // Foreign Key
    @Persisted(originProperty: "refillHistory") var inventory: LinkingObjects<Inventory>

    // Resurtido
    @Persisted var quantityAdded: Double     // Cantidad agregada
    @Persisted var unit: String              // Unidad

    // Informacion de Compra
    @Persisted var pharmacy: String?
    @Persisted var cost: Double?
    @Persisted var currency: String = "MXN"
    @Persisted var prescriptionNumber: String?

    // Lote
    @Persisted var lotNumber: String?
    @Persisted var expirationDate: Date?

    // Metadata
    @Persisted var refillDate: Date = Date()
    @Persisted var notes: String?

    // Sync
    @Persisted var createdAt: Date = Date()
    @Persisted var syncVersion: Int64 = 0
}

---

4. Diagrama ER del Modulo

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

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

    cli_medications }o--o| srv_drug_catalog : "references"

    srv_drug_catalog {
        uuid drug_id PK
        varchar rxnorm_code UK
        varchar name_generic
        varchar name_brand
        boolean requires_rx
        boolean controlled
    }

    cli_medications {
        uuid medication_id PK
        uuid user_id FK
        uuid catalog_drug_id FK
        string custom_name
        string dosage
        boolean is_active
    }

    cli_schedules {
        uuid schedule_id PK
        uuid medication_id FK
        string frequency_type
        json specific_times
        boolean is_active
    }

    cli_dose_logs {
        uuid log_id PK
        uuid schedule_id FK
        datetime scheduled_time
        datetime actual_time
        string status
    }

---

5. Funciones de Base de Datos

5.1. Busqueda de Medicamentos

CREATE OR REPLACE FUNCTION search_drugs(
    p_query TEXT,
    p_limit INT DEFAULT 20
) RETURNS TABLE (
    drug_id UUID,
    name_generic VARCHAR,
    name_brand VARCHAR,
    atc_code VARCHAR,
    requires_rx BOOLEAN,
    rank REAL
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        d.drug_id,
        d.name_generic,
        d.name_brand,
        d.atc_code,
        d.requires_rx,
        ts_rank(d.search_vector, websearch_to_tsquery('spanish', p_query)) as rank
    FROM srv_drug_catalog d
    WHERE d.search_vector @@ websearch_to_tsquery('spanish', p_query)
      AND d.is_active = TRUE
    ORDER BY rank DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

5.2. Verificar Interacciones

CREATE OR REPLACE FUNCTION check_interactions(
    p_drug_ids UUID[]
) RETURNS TABLE (
    drug_a_name VARCHAR,
    drug_b_name VARCHAR,
    severity VARCHAR,
    description TEXT,
    management TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        da.name_generic,
        db.name_generic,
        i.severity,
        i.description,
        i.management
    FROM srv_drug_interactions i
    JOIN srv_drug_catalog da ON da.drug_id = i.drug_a_id
    JOIN srv_drug_catalog db ON db.drug_id = i.drug_b_id
    WHERE i.drug_a_id = ANY(p_drug_ids)
      AND i.drug_b_id = ANY(p_drug_ids)
      AND i.is_active = TRUE
    ORDER BY
        CASE i.severity
            WHEN 'contraindicated' THEN 1
            WHEN 'severe' THEN 2
            WHEN 'moderate' THEN 3
            WHEN 'minor' THEN 4
            ELSE 5
        END;
END;
$$ LANGUAGE plpgsql;

---

6. Migraciones

6.1. Migration 001: Create Medications Schema

-- Migration: 001_create_medications_schema
-- Date: 2025-12-07
-- Author: DatabaseDrone

BEGIN;

-- Drug Catalog
CREATE TABLE IF NOT EXISTS srv_drug_catalog (
    drug_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    rxnorm_code VARCHAR(20) UNIQUE,
    ndc_code VARCHAR(20),
    ean_code VARCHAR(20),
    cofepris_reg VARCHAR(50),
    name_generic VARCHAR(500) NOT NULL,
    name_brand VARCHAR(500),
    name_synonyms TEXT[],
    atc_code VARCHAR(10),
    category VARCHAR(100),
    therapeutic_class VARCHAR(200),
    pharmacological_class VARCHAR(200),
    requires_rx BOOLEAN NOT NULL DEFAULT FALSE,
    controlled BOOLEAN NOT NULL DEFAULT FALSE,
    controlled_schedule VARCHAR(20),
    source VARCHAR(50) NOT NULL DEFAULT 'manual',
    source_version VARCHAR(20),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Drug Forms
CREATE TABLE IF NOT EXISTS srv_drug_forms (
    form_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    drug_id UUID NOT NULL REFERENCES srv_drug_catalog(drug_id) ON DELETE CASCADE,
    form_name VARCHAR(100) NOT NULL,
    form_code VARCHAR(20),
    route VARCHAR(50) NOT NULL,
    route_code VARCHAR(20),
    strength VARCHAR(100),
    strength_value DECIMAL(10,4),
    strength_unit VARCHAR(20),
    package_size INT,
    package_unit VARCHAR(50),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Drug Interactions
CREATE TABLE IF NOT EXISTS srv_drug_interactions (
    interaction_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    drug_a_id UUID NOT NULL REFERENCES srv_drug_catalog(drug_id) ON DELETE CASCADE,
    drug_b_id UUID NOT NULL REFERENCES srv_drug_catalog(drug_id) ON DELETE CASCADE,
    severity VARCHAR(20) NOT NULL CHECK (severity IN ('contraindicated', 'severe', 'moderate', 'minor', 'unknown')),
    description TEXT NOT NULL,
    mechanism TEXT,
    clinical_effect TEXT,
    management TEXT,
    evidence_level VARCHAR(20) CHECK (evidence_level IN ('established', 'probable', 'suspected', 'possible', 'unlikely')),
    references TEXT[],
    source VARCHAR(50) NOT NULL,
    source_id VARCHAR(100),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_different_drugs CHECK (drug_a_id != drug_b_id),
    CONSTRAINT uq_interaction UNIQUE (drug_a_id, drug_b_id)
);

-- Indexes
CREATE UNIQUE INDEX idx_drugs_rxnorm ON srv_drug_catalog(rxnorm_code) WHERE rxnorm_code IS NOT NULL;
CREATE INDEX idx_drugs_atc ON srv_drug_catalog(atc_code);
CREATE INDEX idx_drugs_active ON srv_drug_catalog(is_active);
CREATE INDEX idx_forms_drug ON srv_drug_forms(drug_id);
CREATE INDEX idx_interactions_drug_a ON srv_drug_interactions(drug_a_id);
CREATE INDEX idx_interactions_drug_b ON srv_drug_interactions(drug_b_id);
CREATE INDEX idx_interactions_severity ON srv_drug_interactions(severity);

COMMIT;

---

7. Privacidad en Busqueda de Catalogos (DV2-P3)

7.1. Problema: Metadata Attack en Busquedas Anonimas

DB-BAJO-001: Aunque el servidor NO puede ver que medicamentos usa el usuario (cifrados E2E), las busquedas en srv_drug_catalog pueden revelar patrones:

• Usuario busca "insulina" → Servidor infiere posible diabetes
• Usuario busca "metformina", "glibenclamida" → Patron de diabetes tipo 2
• Frecuencia de busquedas puede revelar urgencia o monitoreo activo

7.2. Mitigaciones Implementadas

7.2.1. Busquedas Anonimas (Sin Autenticacion)

Caracteristica	Implementacion	Justificacion
Endpoint publico	/v1/catalog/drugs?q=...	NO requiere token JWT
Sin user_id	Servidor NO registra quien busca	Elimina vinculo identidad-busqueda
Rate limit por IP	60 req/min por IP	Previene abuse, no tracking individual
Sin logs de busqueda	NO se almacena historial de queries	Privacidad by design
Cache compartido	CDN cachea resultados populares	Reduce visibilidad de busquedas individuales

7.2.2. Busqueda Prefetch (Opcional)

Para usuarios Pro/Perfect, el cliente puede implementar prefetch de resultados populares:

FLUJO PREFETCH:
1. Cliente descarga top 1000 medicamentos mas buscados (anonimizados)
2. Almacena localmente como cache read-only
3. Busquedas iniciales se resuelven localmente
4. Busquedas no encontradas van al servidor (anonimas)

Ventajas:

• Reduce busquedas al servidor (menos metadata)
• Mejora latencia de busqueda
• Funciona offline para medicamentos comunes

Limitaciones:

• Solo cubre medicamentos populares
• Cache puede desactualizarse (refresh semanal)

7.2.3. Query Obfuscation (Futuro)

Estrategia avanzada (no implementada en v1.0):

• Cliente envia multiples queries "dummy" junto con query real
• Servidor NO puede distinguir cual es la query real
• Ratio: 1 query real + 3 queries dummy (ratio 1:3)
• Requiere mayor ancho de banda

7.3. Analisis de Riesgo Residual

Riesgo	Probabilidad	Impacto	Mitigacion Actual	Riesgo Residual
Tracking por IP	Bajo	Medio	Rate limit + sin logs	BAJO
Correlacion temporal	Bajo	Bajo	Busquedas anonimas	BAJO
Fingerprinting de usuario	Muy Bajo	Bajo	Sin user_id	MUY BAJO

Conclusion: Riesgo residual es ACEPTABLE para v1.0. Busquedas anonimas + sin logs + rate limit por IP son suficientes para proteger privacidad de busquedas.

---

8. Indices de Busqueda para Catalogos Locales (DV2-P3)

8.1. Contexto: DB-BAJO-005

Problema: Los usuarios construyen catálogos locales personales al seleccionar medicamentos de búsquedas. Estos catálogos deben ser buscables eficientemente sin requerir búsquedas online constantes.

8.2. Catalogo Local del Usuario

El cliente mantiene un catálogo local de medicamentos que el usuario ha usado o seleccionado:

FUENTES DEL CATALOGO LOCAL:
1. Medicamentos seleccionados de búsquedas online (srv_drug_catalog)
2. Medicamentos ingresados manualmente por el usuario
3. Medicamentos extraídos de recetas OCR

8.3. Esquema de Catalogo Local (Cliente)

8.3.1. iOS - Realm

// Catalogo local de medicamentos del usuario
class LocalDrugCatalog: Object {
    @Persisted(primaryKey: true) var localDrugId: String = UUID().uuidString

    // Usuario propietario
    @Persisted var userId: String

    // Referencia a catalogo publico (si aplica)
    @Persisted var catalogDrugId: String?  // FK a srv_drug_catalog (si proviene de busqueda)

    // Datos del medicamento (cifrados E2E)
    @Persisted var nameGeneric: String
    @Persisted var nameBrand: String?
    @Persisted var nameSynonyms: List<String>

    // Clasificacion
    @Persisted var atcCode: String?
    @Persisted var category: String?
    @Persisted var therapeuticClass: String?

    // Forma farmaceutica
    @Persisted var pharmaceuticalForm: String?
    @Persisted var route: String?
    @Persisted var strength: String?

    // Metadata
    @Persisted var source: String  // online_search, manual_entry, ocr_extraction
    @Persisted var timesUsed: Int = 0  // Numero de veces que usuario lo ha usado
    @Persisted var lastUsedAt: Date?
    @Persisted var isFavorite: Bool = false

    // Busqueda
    @Persisted var searchableText: String  // Texto combinado para busqueda

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

8.3.2. Android - Room

@Entity(
    tableName = "cli_local_drug_catalog",
    indices = [
        Index(value = ["user_id"]),
        Index(value = ["catalog_drug_id"]),
        Index(value = ["name_generic"]),
        Index(value = ["name_brand"]),
        Index(value = ["atc_code"]),
        Index(value = ["category"]),
        Index(value = ["source"]),
        Index(value = ["times_used"]),
        Index(value = ["is_favorite"])
    ]
)
@Fts4(contentEntity = LocalDrugCatalog::class)  // Full-text search
data class LocalDrugCatalog(
    @PrimaryKey
    val localDrugId: String = UUID.randomUUID().toString(),

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

    @ColumnInfo(name = "catalog_drug_id", index = true)
    val catalogDrugId: String?,

    @ColumnInfo(name = "name_generic", index = true)
    val nameGeneric: String,

    @ColumnInfo(name = "name_brand", index = true)
    val nameBrand: String?,

    @ColumnInfo(name = "name_synonyms")
    val nameSynonyms: List<String>,

    @ColumnInfo(name = "atc_code", index = true)
    val atcCode: String?,

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

    @ColumnInfo(name = "therapeutic_class")
    val therapeuticClass: String?,

    @ColumnInfo(name = "pharmaceutical_form")
    val pharmaceuticalForm: String?,

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

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

    @ColumnInfo(name = "source", index = true)
    val source: DrugSource,

    @ColumnInfo(name = "times_used", index = true)
    val timesUsed: Int = 0,

    @ColumnInfo(name = "last_used_at")
    val lastUsedAt: Instant?,

    @ColumnInfo(name = "is_favorite", index = true)
    val isFavorite: Boolean = false,

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

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

enum class DrugSource {
    ONLINE_SEARCH,   // De busqueda en srv_drug_catalog
    MANUAL_ENTRY,    // Ingresado manualmente por usuario
    OCR_EXTRACTION   // Extraido de receta OCR
}

8.4. Indices de Busqueda

8.4.1. iOS - Realm Indices

// Indices automaticos en Realm
class LocalDrugCatalog: Object {
    @Persisted(indexed: true) var userId: String
    @Persisted(indexed: true) var nameGeneric: String
    @Persisted(indexed: true) var nameBrand: String?
    @Persisted(indexed: true) var atcCode: String?
    @Persisted(indexed: true) var category: String?
    @Persisted(indexed: true) var timesUsed: Int
    @Persisted(indexed: true) var isFavorite: Bool
}

// Busqueda Full-Text (Realm no tiene FTS nativo, usar predicates)
func searchLocalDrugs(query: String) -> Results<LocalDrugCatalog> {
    let realm = try! Realm()

    return realm.objects(LocalDrugCatalog.self)
        .filter("userId == %@", currentUserId)
        .filter("nameGeneric CONTAINS[c] %@ OR nameBrand CONTAINS[c] %@", query, query)
        .sorted(byKeyPath: "timesUsed", ascending: false)
}

8.4.2. Android - Room FTS4

// Tabla FTS para busqueda full-text
@Fts4(contentEntity = LocalDrugCatalog::class)
@Entity(tableName = "cli_local_drug_catalog_fts")
data class LocalDrugCatalogFts(
    @PrimaryKey
    @ColumnInfo(name = "rowid")
    val rowid: Int,

    @ColumnInfo(name = "name_generic")
    val nameGeneric: String,

    @ColumnInfo(name = "name_brand")
    val nameBrand: String?,

    @ColumnInfo(name = "name_synonyms")
    val nameSynonyms: String  // Concatenado con espacios
)

// DAO con busqueda FTS
@Dao
interface LocalDrugCatalogDao {
    @Query("""
        SELECT c.* FROM cli_local_drug_catalog c
        JOIN cli_local_drug_catalog_fts fts ON c.local_drug_id = fts.rowid
        WHERE fts.cli_local_drug_catalog_fts MATCH :query
        AND c.user_id = :userId
        ORDER BY c.times_used DESC, c.is_favorite DESC
        LIMIT :limit
    """)
    suspend fun searchDrugs(query: String, userId: String, limit: Int = 50): List<LocalDrugCatalog>

    @Query("""
        SELECT * FROM cli_local_drug_catalog
        WHERE user_id = :userId
        AND is_favorite = 1
        ORDER BY last_used_at DESC
    """)
    suspend fun getFavoriteDrugs(userId: String): List<LocalDrugCatalog>

    @Query("""
        SELECT * FROM cli_local_drug_catalog
        WHERE user_id = :userId
        ORDER BY times_used DESC
        LIMIT :limit
    """)
    suspend fun getMostUsedDrugs(userId: String, limit: Int = 10): List<LocalDrugCatalog>
}

8.5. Performance de Indices

8.5.1. Metricas de Performance

Operacion	Sin Indices	Con Indices	Mejora
Busqueda por nombre (1000 registros)	~50ms	~5ms	10x
Busqueda full-text (1000 registros)	~80ms	~8ms	10x
Filtro por favoritos	~30ms	~3ms	10x
Ordenar por mas usados	~40ms	~4ms	10x

Nota: Metricas basadas en dispositivos mid-range (2021+).

8.5.2. Tamano de Indices

Indice	Overhead	Justificacion
userId	~5 KB/1K registros	Critico: filtra por usuario
nameGeneric	~20 KB/1K registros	Critico: busqueda principal
nameBrand	~15 KB/1K registros	Importante: busqueda secundaria
atcCode	~10 KB/1K registros	Util: filtro por categoria
timesUsed	~8 KB/1K registros	Importante: ordenamiento por popularidad
isFavorite	~5 KB/1K registros	Util: filtro rapido
TOTAL	~63 KB/1K registros	Aceptable para catalogos locales

Conclusion: Overhead de indices es minimo comparado con beneficios de performance.

8.6. Estrategia de Poblacion del Catalogo Local

// Agregar medicamento seleccionado de busqueda online
func addDrugFromOnlineSearch(catalogDrugId: String, drugData: DrugData) async {
    let realm = try! Realm()

    try! await realm.write {
        // Verificar si ya existe
        if let existing = realm.objects(LocalDrugCatalog.self)
            .filter("userId == %@ AND catalogDrugId == %@", currentUserId, catalogDrugId)
            .first {
            // Ya existe, incrementar contador
            existing.timesUsed += 1
            existing.lastUsedAt = Date()
            return
        }

        // Crear nuevo registro en catalogo local
        let localDrug = LocalDrugCatalog()
        localDrug.userId = currentUserId
        localDrug.catalogDrugId = catalogDrugId
        localDrug.nameGeneric = drugData.nameGeneric
        localDrug.nameBrand = drugData.nameBrand
        localDrug.atcCode = drugData.atcCode
        localDrug.category = drugData.category
        localDrug.source = "online_search"
        localDrug.timesUsed = 1
        localDrug.lastUsedAt = Date()

        realm.add(localDrug)
    }
}

// Agregar medicamento de entrada manual
func addDrugFromManualEntry(name: String, form: String?, strength: String?) async {
    let realm = try! Realm()

    try! await realm.write {
        let localDrug = LocalDrugCatalog()
        localDrug.userId = currentUserId
        localDrug.catalogDrugId = nil  // No hay referencia a catalogo publico
        localDrug.nameGeneric = name
        localDrug.pharmaceuticalForm = form
        localDrug.strength = strength
        localDrug.source = "manual_entry"
        localDrug.timesUsed = 1
        localDrug.lastUsedAt = Date()

        realm.add(localDrug)
    }
}

8.7. Sincronizacion del Catalogo Local

IMPORTANTE: El catalogo local NO se sincroniza al servidor como tabla separada. En su lugar:

• Los medicamentos seleccionados se sincronizan como parte de cli_medications (SYNCED_E2E)
• El catalogo local es una cache derivada para mejorar UX de busqueda
• Se reconstruye localmente en cada dispositivo basado en medicamentos usados

Ventaja: No se expone patron de busquedas (metadata attack) ya que solo se sincronizan medicamentos activamente usados.

---

9. Referencias Cruzadas

Documento	Relacion
DB-ERD-001	Diagrama ER completo
MTS-MED-001	Especificacion funcional
MTS-ALT-001	Alertas de medicamentos
MTS-ADH-001	Metricas de adherencia
04-seguridad-cliente.md	Cifrado E2E de medicamentos
INV-003	DrugBank API
INV-004	RxNorm API

---

Documento generado por DatabaseDrone - SpecQueen Technical Division "Cada medicamento bien modelado es una dosis de seguridad."