Modelo de Datos: Interacciones Medicamentosas¶

Identificador: MDL-INT-001 Version: 2.0.0 Fecha: 2025-12-08 Estado: En Revision - ACTUALIZADO POR DIRECTIVA DEL DIRECTOR Autor: DatabaseDrone / SpecQueen Technical Division Modulo Funcional: MTS-INT-001 Iteracion: IT-10

1. Resumen y Trazabilidad
1.1. Arquitectura Dual
1.2. Principios de Diseno
2. Clasificacion de Datos
2.1. Tabla de Clasificacion por Entidad
2.2. Justificacion de Clasificaciones
3. Esquema SERVIDOR (Catalogos y Logs)
3.1. srv_interaction_catalog
- 3.1.1. Columnas
- 3.1.2. Indices
- 3.1.3. Constraints
3.2. srv_drugbank_cache
- 3.2.1. Columnas
- 3.2.2. Indices
- 3.2.3. Politica de Expiracion
3.3. srv_ia_analysis_log
- 3.3.1. Columnas
- 3.3.2. Indices
- 3.3.3. Retencion
4. Esquema CLIENTE (Detecciones y Historial)
4.1. cli_critical_interactions_cache
- 4.1.1. Estructura (Conceptual)
- 4.1.2. SQLite (iOS/Android)
- 4.1.3. Realm (iOS)
- 4.1.4. Room (Android)
4.2. cli_detected_interactions
- 4.2.1. Estructura (Conceptual)
- 4.2.2. SQLite (iOS/Android)
- 4.2.3. Realm (iOS)
- 4.2.4. Room (Android)
4.3. cli_interaction_acknowledgments
- 4.3.1. Estructura (Conceptual)
- 4.3.2. SQLite (iOS/Android)
- 4.3.3. Realm (iOS)
- 4.3.4. Room (Android)
4.4. cli_interaction_history
- 4.4.1. Estructura (Conceptual)
- 4.4.2. SQLite (iOS/Android)
- 4.4.3. Realm (iOS)
- 4.4.4. Room (Android)
4.5. cli_interaction_consent
- 4.5.1. Estructura (Conceptual)
- 4.5.2. SQLite (iOS/Android)
- 4.5.3. Realm (iOS)
- 4.5.4. Room (Android)
4.6. cli_pending_verifications
- 4.6.1. Estructura (Conceptual)
- 4.6.2. SQLite (iOS/Android)
- 4.6.3. Realm (iOS)
- 4.6.4. Room (Android)
5. Mapeo de Sincronizacion
5.1. Cache Critico de Interacciones (Embebido en App)
5.2. Detecciones de Usuario (Bidireccional E2E)
5.3. Confirmaciones (Bidireccional E2E)
5.4. Historial (Bidireccional E2E)
5.5. Consentimiento de Busqueda (Bidireccional E2E)
6. Diagrama ER del Modulo
6.1. Servidor
6.2. Cliente
7. Consideraciones de Cifrado
7.1. Campos Cifrados E2E
7.2. Blind Indexes (No Aplica)
7.3. Proceso de Sincronizacion
8. Reglas de Validacion
8.1. Nivel Servidor
8.2. Nivel Cliente
9. Politicas RLS (Row Level Security)
9.1. srv_interaction_catalog (Sin RLS)
9.2. srv_drugbank_cache
9.3. srv_ia_analysis_log
10. Queries de Ejemplo
10.1. Buscar Interaccion en Cache Local
10.2. Detectar Interacciones para Medicamentos del Usuario
10.3. Obtener Historial de Alertas (6 anos HIPAA)
10.4. Verificaciones Pendientes (Fallback)
10.5. Consultar Cache DrugBank (Servidor)
11. Migraciones
11.1. Migration 001: Create Interactions Schema (Servidor)
11.2. Migration 001: Create Interactions Schema (Cliente SQLite)
12. Integracion con Otros Modulos
12.1. MDL-MED-001 (Medicamentos)
12.2. MDL-ALT-001 (Alertas)
12.3. MDL-USR-001 (Usuarios)
13. Metricas y Monitoreo
13.1. Metricas de Cache
13.2. Metricas de Deteccion
13.3. Metricas de Verificacion Online
14. Referencias
14.1. Documentos Tecnicos
14.2. Documentos Funcionales
14.3. Estandares Externos

1. Resumen y Trazabilidad¶

Campo	Valor
Ref Funcional	MTS-INT-001
Dominio	INT (Interacciones)
Iteracion	IT-10
Dependencias	MDL-MED-001, MDL-ALT-001, MDL-USR-001
Prioridad	ALTA (Seguridad del paciente)

1.1. Arquitectura Dual¶

┌─────────────────────────────────────────────────────────────┐
│                  DISPOSITIVO (LOCAL - 95%)                   │
│                                                              │
│  [Cache CRITICO <100 interacciones CONTRAINDICADAS]         │
│  • Embebido en app (NO descargado)                          │
│  • Datos en CLARO                                            │
│  • Severidad 4 (CONTRAINDICADO) mas comunes                 │
│  • Deteccion offline O(n^2) para casos criticos              │
│                                                              │
│  [Detecciones del usuario]                                   │
│  • PHI - Cifradas E2E antes de sync                          │
│  • Historial 6 anos (HIPAA)                                  │
│  • Incluye resultados de busquedas online                    │
│                                                              │
│  [Confirmaciones y Consentimiento]                           │
│  • PHI - Decisiones medicas cifradas E2E                     │
│  • Consentimiento de disclaimer de privacidad                │
│                                                              │
└─────────────────────────────────────────────────────────────┘
                          │
                Cifrado E2E (AES-256-GCM)
                          ↓
┌─────────────────────────────────────────────────────────────┐
│                   SERVIDOR (5% - Coordinacion)               │
│                                                              │
│  [Catalogo maestro de interacciones]                        │
│  • Datos publicos (no PHI)                                   │
│  • ~1,000,000+ pares (DrugBank + RxNorm + propios)          │
│  • Busquedas online (Pro/Perfect con consentimiento)         │
│                                                              │
│  [Cache de consultas DrugBank]                              │
│  • Metadata de consultas exitosas                            │
│  • TTL 7 dias                                                │
│                                                              │
│  [Log de analisis IA]                                        │
│  • Metricas sin PHI                                          │
│  • Perfect tier only                                         │
│                                                              │
└─────────────────────────────────────────────────────────────┘

1.2. Principios de Diseno¶

Principio	Implementacion
Offline Critico	Cache local <100 CONTRAINDICADAS mas criticas (embebido en app)
Online con Consentimiento	Busquedas online (Pro/Perfect) requieren aceptar disclaimer de privacidad
Zero-Knowledge	Servidor NUNCA ve medicamentos del usuario en claro
Retencion HIPAA	Historial de alertas se mantiene 6 anos
Sanitizacion IA	Datos enviados a Claude NUNCA incluyen PII
Regla 100 Registros	Ningun catalogo >100 registros se descarga (Directiva Director)

2. Clasificacion de Datos¶

2.1. Tabla de Clasificacion por Entidad¶

Entidad	Clasificacion	Cifrado	Sync	Razon
SERVIDOR
srv_interaction_catalog	SERVER_SOURCE	No	Busqueda online	Catalogo publico de interacciones (~1M+)
srv_drugbank_cache	SERVER_ONLY	No	No	Metadata de consultas API
srv_ia_analysis_log	SERVER_ONLY	No	No	Metricas sin PHI
CLIENTE
cli_critical_interactions_cache	LOCAL_ONLY	No	Embebido en app	<100 CONTRAINDICADAS criticas (NO descargado)
cli_detected_interactions	SYNCED_E2E	Si	Bidireccional	PHI - medicamentos detectados + busquedas online
cli_interaction_acknowledgments	SYNCED_E2E	Si	Bidireccional	PHI - decisiones medicas
cli_interaction_history	SYNCED_E2E	Si	Bidireccional	PHI - historial 6 anos
cli_interaction_consent	SYNCED_E2E	Si	Bidireccional	Consentimiento de disclaimer de privacidad
cli_pending_verifications	LOCAL_ONLY	No	No	Cola temporal (max 10 verificaciones pendientes)

2.2. Justificacion de Clasificaciones¶

Dato	Clasificacion	Justificacion
Catalogo critico <100	LOCAL_ONLY	<100 interacciones CONTRAINDICADAS mas criticas. Embebido en app (NO descargado). Cumple Regla 100
Catalogo completo servidor	SERVER_SOURCE	~1M+ interacciones. Solo busquedas online (Pro/Perfect con consentimiento). NUNCA se descarga
Interacciones detectadas	SYNCED_E2E	Revela medicamentos del usuario (PHI). Incluye resultados de busquedas online. Servidor solo blob cifrado
Confirmaciones de usuario	SYNCED_E2E	Decisiones medicas sensibles (PHI). Requiere confirmacion explicita en CONTRAINDICADO
Consentimiento de busqueda	SYNCED_E2E	Aceptacion del disclaimer de privacidad para busquedas online. PHI (vinculado a usuario)
Historial de alertas	SYNCED_E2E	HIPAA requiere retencion 6 anos. PHI que debe cifrarse
Cola de verificaciones	LOCAL_ONLY	Max 10 verificaciones pendientes (no descarga masiva). Cola temporal, no PHI
Cache DrugBank	SERVER_ONLY	Metadata operativa sin PHI. No sale del servidor
Log IA	SERVER_ONLY	Solo metricas (latencia, tokens). Datos enviados a IA sanitizados (sin PII)

3. Esquema SERVIDOR (Catalogos y Logs)¶

3.1. srv_interaction_catalog¶

Catalogo maestro de interacciones medicamento-medicamento.

Clasificacion: SERVER_SOURCE (publico)

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

    -- Medicamentos Involucrados
    drug_a_id           UUID NOT NULL,  -- FK a srv_drug_catalog
    drug_b_id           UUID NOT NULL,  -- FK a srv_drug_catalog
    principle_a         VARCHAR(200) NOT NULL,  -- Principio activo A
    principle_b         VARCHAR(200) NOT NULL,  -- Principio activo B

    -- Clasificacion
    severity            VARCHAR(20) NOT NULL CHECK (severity IN ('CONTRAINDICADO', 'SERIO', 'MODERADO', 'MENOR')),
    severity_code       SMALLINT NOT NULL CHECK (severity_code BETWEEN 1 AND 4),  -- 1=MENOR, 4=CONTRAINDICADO
    interaction_type    VARCHAR(20) NOT NULL CHECK (interaction_type IN ('FARMACOCINETICA', 'FARMACODINAMICA', 'MIXTA')),
    category            VARCHAR(100),  -- CYP450, QT prolongation, etc.

    -- Descripcion
    description         TEXT NOT NULL,
    mechanism           TEXT,
    expected_effect     TEXT,
    possible_symptoms   TEXT[],

    -- Recomendacion
    clinical_recommendation TEXT,
    suggested_action    VARCHAR(20) CHECK (suggested_action IN ('EVITAR', 'MONITOREAR', 'AJUSTAR_DOSIS', 'ESPACIAR', 'INFORMAR')),
    monitoring_required TEXT,

    -- Fuente y Evidencia
    primary_source      VARCHAR(100) NOT NULL,  -- DrugBank, RxNorm, manual, etc.
    additional_sources  TEXT[],
    evidence_level      VARCHAR(10) CHECK (evidence_level IN ('ALTA', 'MEDIA', 'BAJA')),
    pubmed_refs         TEXT[],
    last_updated_date   DATE NOT NULL,

    -- Metadata Regional
    applicable_countries TEXT[] DEFAULT ARRAY['MX', 'US', 'BR'],  -- ISO 3166-1 alpha-2
    regional_notes      JSONB,  -- {"MX": "...", "US": "..."}
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,

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

    -- Constraint: evitar duplicados (A-B == B-A)
    CONSTRAINT unique_pair CHECK (drug_a_id < drug_b_id)
);

3.1.1. Columnas¶

Columna	Tipo	Nullable	Descripcion
interaction_id	UUID	No	Identificador unico
drug_a_id	UUID	No	FK a srv_drug_catalog (menor UUID)
drug_b_id	UUID	No	FK a srv_drug_catalog (mayor UUID)
principle_a	VARCHAR(200)	No	Principio activo del medicamento A
principle_b	VARCHAR(200)	No	Principio activo del medicamento B
severity	VARCHAR(20)	No	CONTRAINDICADO, SERIO, MODERADO, MENOR
severity_code	SMALLINT	No	1=MENOR, 2=MODERADO, 3=SERIO, 4=CONTRAINDICADO
interaction_type	VARCHAR(20)	No	FARMACOCINETICA, FARMACODINAMICA, MIXTA
category	VARCHAR(100)	Si	Categoria de interaccion (CYP450, QT prolongation, etc.)
description	TEXT	No	Descripcion de la interaccion
mechanism	TEXT	Si	Mecanismo de la interaccion
expected_effect	TEXT	Si	Efecto esperado de la interaccion
possible_symptoms	TEXT[]	Si	Sintomas posibles
clinical_recommendation	TEXT	Si	Recomendacion clinica
suggested_action	VARCHAR(20)	Si	EVITAR, MONITOREAR, AJUSTAR_DOSIS, ESPACIAR, INFORMAR
monitoring_required	TEXT	Si	Monitoreo requerido
primary_source	VARCHAR(100)	No	Fuente primaria (DrugBank, RxNorm, manual)
additional_sources	TEXT[]	Si	Fuentes adicionales
evidence_level	VARCHAR(10)	Si	ALTA, MEDIA, BAJA
pubmed_refs	TEXT[]	Si	Referencias PubMed
last_updated_date	DATE	No	Fecha ultima actualizacion de la fuente
applicable_countries	TEXT[]	Si	Paises donde aplica (ISO 3166-1 alpha-2)
regional_notes	JSONB	Si	Notas especificas por pais
is_active	BOOLEAN	No	Si esta activa en el catalogo
created_at	TIMESTAMPTZ	No	Fecha creacion en servidor
updated_at	TIMESTAMPTZ	No	Ultima actualizacion

3.1.2. Indices¶

-- Busqueda bidireccional por par de medicamentos
CREATE INDEX idx_interaction_drugs ON srv_interaction_catalog(drug_a_id, drug_b_id);
CREATE INDEX idx_interaction_drugs_reverse ON srv_interaction_catalog(drug_b_id, drug_a_id);

-- Busqueda por principio activo
CREATE INDEX idx_interaction_principles ON srv_interaction_catalog(principle_a, principle_b);

-- Busqueda por severidad
CREATE INDEX idx_interaction_severity ON srv_interaction_catalog(severity_code DESC);

-- Busqueda por categoria
CREATE INDEX idx_interaction_category ON srv_interaction_catalog(category) WHERE category IS NOT NULL;

-- Filtro de activos
CREATE INDEX idx_interaction_active ON srv_interaction_catalog(is_active) WHERE is_active = TRUE;

-- Actualizacion reciente (para OTA)
CREATE INDEX idx_interaction_updated ON srv_interaction_catalog(updated_at DESC);

-- Full-text search en descripcion
CREATE INDEX idx_interaction_search ON srv_interaction_catalog USING GIN(to_tsvector('spanish', description));

3.1.3. Constraints¶

-- Trigger para actualizar updated_at
CREATE OR REPLACE FUNCTION update_interaction_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_interaction_updated_at
    BEFORE UPDATE ON srv_interaction_catalog
    FOR EACH ROW
    EXECUTE FUNCTION update_interaction_updated_at();

-- Constraint adicional: drug_a_id != drug_b_id
ALTER TABLE srv_interaction_catalog
    ADD CONSTRAINT different_drugs CHECK (drug_a_id != drug_b_id);

3.2. srv_drugbank_cache¶

Cache de consultas exitosas a DrugBank API (Pro/Perfect).

Clasificacion: SERVER_ONLY

CREATE TABLE srv_drugbank_cache (
    -- Primary Key
    cache_id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Medicamentos Consultados (hash para no revelar combinaciones especificas)
    drug_pair_hash      VARCHAR(64) NOT NULL UNIQUE,  -- SHA-256(drug_a_id + drug_b_id)

    -- Metadata de la Consulta
    tier                VARCHAR(10) NOT NULL CHECK (tier IN ('Pro', 'Perfect')),
    http_status         SMALLINT NOT NULL,
    response_time_ms    INT,
    tokens_used         INT,  -- Si aplica (API con billing por tokens)

    -- Resultado (sin PHI)
    interactions_found  INT NOT NULL DEFAULT 0,
    max_severity_code   SMALLINT CHECK (max_severity_code BETWEEN 0 AND 4),  -- 0=sin interaccion

    -- Timestamp y Expiracion
    queried_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at          TIMESTAMPTZ NOT NULL,  -- TTL 7 dias

    -- Metadata
    source_version      VARCHAR(20),  -- Version de DrugBank consultada
    is_error            BOOLEAN NOT NULL DEFAULT FALSE,
    error_message       TEXT,

    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

3.2.1. Columnas¶

Columna	Tipo	Nullable	Descripcion
cache_id	UUID	No	Identificador unico
drug_pair_hash	VARCHAR(64)	No	SHA-256 del par de medicamentos (blind index)
tier	VARCHAR(10)	No	Pro o Perfect
http_status	SMALLINT	No	Codigo HTTP de respuesta
response_time_ms	INT	Si	Tiempo de respuesta en ms
tokens_used	INT	Si	Tokens consumidos (si aplica)
interactions_found	INT	No	Numero de interacciones encontradas
max_severity_code	SMALLINT	Si	Maxima severidad encontrada (0-4)
queried_at	TIMESTAMPTZ	No	Timestamp de la consulta
expires_at	TIMESTAMPTZ	No	Expiracion del cache (TTL 7 dias)
source_version	VARCHAR(20)	Si	Version de DrugBank
is_error	BOOLEAN	No	Si la consulta resulto en error
error_message	TEXT	Si	Mensaje de error (si aplica)
created_at	TIMESTAMPTZ	No	Fecha creacion

3.2.2. Indices¶

-- Busqueda por hash (lookup de cache)
CREATE UNIQUE INDEX idx_drugbank_cache_hash ON srv_drugbank_cache(drug_pair_hash);

-- Limpieza de expirados
CREATE INDEX idx_drugbank_cache_expires ON srv_drugbank_cache(expires_at);

-- Metricas por tier
CREATE INDEX idx_drugbank_cache_tier ON srv_drugbank_cache(tier, queried_at);

-- Errores
CREATE INDEX idx_drugbank_cache_errors ON srv_drugbank_cache(is_error) WHERE is_error = TRUE;

3.2.3. Politica de Expiracion¶

-- Funcion para limpiar cache expirado (ejecutar con cron diario)
CREATE OR REPLACE FUNCTION cleanup_expired_drugbank_cache()
RETURNS void AS $$
BEGIN
    DELETE FROM srv_drugbank_cache
    WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql;

-- TTL default: 7 dias
CREATE OR REPLACE FUNCTION set_drugbank_cache_expiry()
RETURNS TRIGGER AS $$
BEGIN
    NEW.expires_at = NEW.queried_at + INTERVAL '7 days';
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_drugbank_cache_expiry
    BEFORE INSERT ON srv_drugbank_cache
    FOR EACH ROW
    WHEN (NEW.expires_at IS NULL)
    EXECUTE FUNCTION set_drugbank_cache_expiry();

3.3. srv_ia_analysis_log¶

Log de consultas al motor IA (Claude - Perfect tier).

Clasificacion: SERVER_ONLY (sin PHI)

CREATE TABLE srv_ia_analysis_log (
    -- Primary Key
    log_id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),

    -- Metadata de Consulta (SIN PHI)
    session_token       VARCHAR(64) NOT NULL,  -- Token efimero, no vinculado a user_id
    num_medications     SMALLINT NOT NULL,     -- Numero de medicamentos en la consulta
    severity_detected   VARCHAR(20),           -- Maxima severidad detectada

    -- Metricas de Rendimiento
    request_time_ms     INT,
    tokens_input        INT,
    tokens_output       INT,
    tokens_total        INT,

    -- Resultado
    http_status         SMALLINT NOT NULL,
    is_error            BOOLEAN NOT NULL DEFAULT FALSE,
    error_type          VARCHAR(50),  -- timeout, rate_limit, api_error, etc.

    -- Timestamp
    analyzed_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Metadata
    anthropic_version   VARCHAR(20),  -- Version del modelo Claude

    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

3.3.1. Columnas¶

Columna	Tipo	Nullable	Descripcion
log_id	UUID	No	Identificador unico
session_token	VARCHAR(64)	No	Token efimero ALEATORIO (no vinculado a user)
num_medications	SMALLINT	No	Numero de medicamentos analizados
severity_detected	VARCHAR(20)	Si	Maxima severidad detectada por IA
request_time_ms	INT	Si	Tiempo de respuesta en ms
tokens_input	INT	Si	Tokens de entrada
tokens_output	INT	Si	Tokens de salida
tokens_total	INT	Si	Tokens totales
http_status	SMALLINT	No	Codigo HTTP de respuesta
is_error	BOOLEAN	No	Si hubo error
error_type	VARCHAR(50)	Si	Tipo de error
analyzed_at	TIMESTAMPTZ	No	Timestamp del analisis
anthropic_version	VARCHAR(20)	Si	Version de Claude
created_at	TIMESTAMPTZ	No	Fecha creacion

3.3.2. Indices¶

-- Metricas por fecha
CREATE INDEX idx_ia_log_analyzed ON srv_ia_analysis_log(analyzed_at DESC);

-- Errores
CREATE INDEX idx_ia_log_errors ON srv_ia_analysis_log(is_error) WHERE is_error = TRUE;

-- Uso de tokens (para facturacion)
CREATE INDEX idx_ia_log_tokens ON srv_ia_analysis_log(tokens_total) WHERE tokens_total IS NOT NULL;

3.3.3. Retencion¶

-- Retencion: 90 dias (metricas operativas, no PHI)
CREATE OR REPLACE FUNCTION cleanup_old_ia_logs()
RETURNS void AS $$
BEGIN
    DELETE FROM srv_ia_analysis_log
    WHERE analyzed_at < NOW() - INTERVAL '90 days';
END;
$$ LANGUAGE plpgsql;

4. Esquema CLIENTE (Detecciones y Historial)¶

4.1. cli_critical_interactions_cache¶

Cache local CRITICO de interacciones CONTRAINDICADAS mas comunes (<100 registros).

Clasificacion: LOCAL_ONLY (embebido en app, NO descargado dinamicamente)

DIRECTIVA DEL DIRECTOR - REGLA 100 REGISTROS: - Maximo <100 interacciones CONTRAINDICADAS - Solo severidad 4 (CONTRAINDICADO) - Interacciones mas criticas y comunes - Embebido en el codigo de la app (NO descarga dinamica) - Cumple con: "Ningun catalogo >100 registros se descarga al dispositivo" - Referencia: 02-arquitectura-cliente-servidor.md seccion 3.2.3.1

4.1.1. Estructura (Conceptual)¶

Campo	Tipo	Descripcion
cache_id	UUID	Identificador unico
drug_a_id	UUID	Medicamento A (del catalogo local)
drug_b_id	UUID	Medicamento B (del catalogo local)
principle_a	String	Principio activo A
principle_b	String	Principio activo B
severity	Enum	SIEMPRE 'CONTRAINDICADO' (solo nivel 4)
severity_code	Int	SIEMPRE 4
interaction_type	Enum	FARMACOCINETICA, FARMACODINAMICA, MIXTA
category	String	Categoria de interaccion
description	Text	Descripcion
mechanism	Text	Mecanismo
expected_effect	Text	Efecto esperado
clinical_recommendation	Text	Recomendacion clinica
suggested_action	Enum	SIEMPRE 'EVITAR'
primary_source	String	DrugBank, RxNorm, MedTime
evidence_level	Enum	ALTA, MEDIA, BAJA
embedded_version	String	Version del codigo embebido

4.1.2. SQLite (iOS/Android)¶

-- Cliente SQLite
-- NOTA: Maximo <100 registros. Solo CONTRAINDICADO (severity_code = 4)
CREATE TABLE cli_critical_interactions_cache (
    cache_id            TEXT PRIMARY KEY,
    drug_a_id           TEXT NOT NULL,
    drug_b_id           TEXT NOT NULL,
    principle_a         TEXT NOT NULL,
    principle_b         TEXT NOT NULL,
    severity            TEXT NOT NULL DEFAULT 'CONTRAINDICADO' CHECK (severity = 'CONTRAINDICADO'),
    severity_code       INTEGER NOT NULL DEFAULT 4 CHECK (severity_code = 4),
    interaction_type    TEXT NOT NULL CHECK (interaction_type IN ('FARMACOCINETICA', 'FARMACODINAMICA', 'MIXTA')),
    category            TEXT,
    description         TEXT NOT NULL,
    mechanism           TEXT,
    expected_effect     TEXT,
    clinical_recommendation TEXT,
    suggested_action    TEXT NOT NULL DEFAULT 'EVITAR' CHECK (suggested_action = 'EVITAR'),
    primary_source      TEXT NOT NULL,
    evidence_level      TEXT CHECK (evidence_level IN ('ALTA', 'MEDIA', 'BAJA')),
    embedded_version    TEXT NOT NULL,  -- Version del codigo embebido
    UNIQUE(drug_a_id, drug_b_id),
    -- Constraint para garantizar max 100 registros (no soportado en SQLite, debe validarse en codigo)
    CHECK ((SELECT COUNT(*) FROM cli_critical_interactions_cache) <= 100)
);

-- Indices
CREATE INDEX idx_critical_cache_drugs ON cli_critical_interactions_cache(drug_a_id, drug_b_id);
CREATE INDEX idx_critical_cache_principles ON cli_critical_interactions_cache(principle_a, principle_b);

4.1.3. Realm (iOS)¶

// Realm Model (iOS)
// NOTA: Maximo <100 registros. Solo CONTRAINDICADO (severityCode = 4)
class CriticalInteractionCache: Object {
    @Persisted(primaryKey: true) var cacheId: String
    @Persisted var drugAId: String
    @Persisted var drugBId: String
    @Persisted var principleA: String
    @Persisted var principleB: String
    @Persisted var severity: String = "CONTRAINDICADO" // SIEMPRE CONTRAINDICADO
    @Persisted var severityCode: Int = 4 // SIEMPRE 4
    @Persisted var interactionType: String // FARMACOCINETICA, FARMACODINAMICA, MIXTA
    @Persisted var category: String?
    @Persisted var descriptionText: String
    @Persisted var mechanism: String?
    @Persisted var expectedEffect: String?
    @Persisted var clinicalRecommendation: String?
    @Persisted var suggestedAction: String = "EVITAR" // SIEMPRE EVITAR
    @Persisted var primarySource: String
    @Persisted var evidenceLevel: String? // ALTA, MEDIA, BAJA
    @Persisted var embeddedVersion: String // Version del codigo embebido
}

4.1.4. Room (Android)¶

// Room Entity (Android)
// NOTA: Maximo <100 registros. Solo CONTRAINDICADO (severityCode = 4)
@Entity(
    tableName = "cli_critical_interactions_cache",
    indices = [
        Index(value = ["drug_a_id", "drug_b_id"], unique = true),
        Index(value = ["principle_a", "principle_b"])
    ]
)
data class CriticalInteractionCache(
    @PrimaryKey val cacheId: String,
    @ColumnInfo(name = "drug_a_id") val drugAId: String,
    @ColumnInfo(name = "drug_b_id") val drugBId: String,
    @ColumnInfo(name = "principle_a") val principleA: String,
    @ColumnInfo(name = "principle_b") val principleB: String,
    val severity: String = "CONTRAINDICADO", // SIEMPRE CONTRAINDICADO
    @ColumnInfo(name = "severity_code") val severityCode: Int = 4, // SIEMPRE 4
    @ColumnInfo(name = "interaction_type") val interactionType: String,
    val category: String?,
    val description: String,
    val mechanism: String?,
    @ColumnInfo(name = "expected_effect") val expectedEffect: String?,
    @ColumnInfo(name = "clinical_recommendation") val clinicalRecommendation: String?,
    @ColumnInfo(name = "suggested_action") val suggestedAction: String = "EVITAR", // SIEMPRE EVITAR
    @ColumnInfo(name = "primary_source") val primarySource: String,
    @ColumnInfo(name = "evidence_level") val evidenceLevel: String?,
    @ColumnInfo(name = "embedded_version") val embeddedVersion: String // Version del codigo embebido
)

4.2. cli_detected_interactions¶

Interacciones detectadas para los medicamentos del usuario.

Clasificacion: SYNCED_E2E (PHI)

4.2.1. Estructura (Conceptual)¶

Campo	Tipo	Descripcion
detection_id	UUID	Identificador unico
user_id	UUID	FK a usuarios (LOCAL)
medication_a_id	UUID	FK a medicamentos del usuario
medication_b_id	UUID	FK a medicamentos del usuario
interaction_cache_id	UUID	FK a cache de interacciones
severity	Enum	CONTRAINDICADO, SERIO, MODERADO, MENOR
severity_code	Int	1-4
status	Enum	ACTIVE, ACKNOWLEDGED, RESOLVED, DISMISSED
detected_at	DateTime	Timestamp de deteccion
resolved_at	DateTime	Timestamp de resolucion
sync_status	Enum	PENDING, SYNCED, CONFLICT
last_sync_version	Int	Version de sincronizacion
created_at	DateTime	Timestamp creacion
updated_at	DateTime	Ultima actualizacion

4.2.2. SQLite (iOS/Android)¶

-- Cliente SQLite
CREATE TABLE cli_detected_interactions (
    detection_id        TEXT PRIMARY KEY,
    user_id             TEXT NOT NULL,
    medication_a_id     TEXT NOT NULL,
    medication_b_id     TEXT NOT NULL,
    interaction_cache_id TEXT,  -- FK a cli_critical_interactions_cache (opcional, solo si fue detectada offline)
    severity            TEXT NOT NULL,
    severity_code       INTEGER NOT NULL,
    status              TEXT NOT NULL CHECK (status IN ('ACTIVE', 'ACKNOWLEDGED', 'RESOLVED', 'DISMISSED')),
    detected_at         TEXT NOT NULL,  -- ISO 8601
    resolved_at         TEXT,
    sync_status         TEXT NOT NULL DEFAULT 'PENDING',
    last_sync_version   INTEGER DEFAULT 0,
    created_at          TEXT NOT NULL,
    updated_at          TEXT NOT NULL,
    FOREIGN KEY (medication_a_id) REFERENCES cli_medications(medication_id),
    FOREIGN KEY (medication_b_id) REFERENCES cli_medications(medication_id),
    FOREIGN KEY (interaction_cache_id) REFERENCES cli_critical_interactions_cache(cache_id)
);

-- Indices
CREATE INDEX idx_detected_user ON cli_detected_interactions(user_id);
CREATE INDEX idx_detected_status ON cli_detected_interactions(status) WHERE status = 'ACTIVE';
CREATE INDEX idx_detected_sync ON cli_detected_interactions(sync_status) WHERE sync_status = 'PENDING';
CREATE INDEX idx_detected_medications ON cli_detected_interactions(medication_a_id, medication_b_id);

4.2.3. Realm (iOS)¶

// Realm Model (iOS)
class DetectedInteraction: Object {
    @Persisted(primaryKey: true) var detectionId: String
    @Persisted var userId: String
    @Persisted var medicationAId: String
    @Persisted var medicationBId: String
    @Persisted var interactionCacheId: String?
    @Persisted var severity: String
    @Persisted var severityCode: Int
    @Persisted var status: String // ACTIVE, ACKNOWLEDGED, RESOLVED, DISMISSED
    @Persisted var detectedAt: Date
    @Persisted var resolvedAt: Date?
    @Persisted var syncStatus: String
    @Persisted var lastSyncVersion: Int
    @Persisted var createdAt: Date
    @Persisted var updatedAt: Date
}

4.2.4. Room (Android)¶

// Room Entity (Android)
@Entity(
    tableName = "cli_detected_interactions",
    foreignKeys = [
        ForeignKey(
            entity = Medication::class,
            parentColumns = ["medication_id"],
            childColumns = ["medication_a_id"]
        ),
        ForeignKey(
            entity = Medication::class,
            parentColumns = ["medication_id"],
            childColumns = ["medication_b_id"]
        ),
        ForeignKey(
            entity = InteractionCache::class,
            parentColumns = ["cache_id"],
            childColumns = ["interaction_cache_id"]
        )
    ],
    indices = [
        Index(value = ["user_id"]),
        Index(value = ["status"]),
        Index(value = ["sync_status"]),
        Index(value = ["medication_a_id", "medication_b_id"])
    ]
)
data class DetectedInteraction(
    @PrimaryKey val detectionId: String,
    @ColumnInfo(name = "user_id") val userId: String,
    @ColumnInfo(name = "medication_a_id") val medicationAId: String,
    @ColumnInfo(name = "medication_b_id") val medicationBId: String,
    @ColumnInfo(name = "interaction_cache_id") val interactionCacheId: String?,
    val severity: String,
    @ColumnInfo(name = "severity_code") val severityCode: Int,
    val status: String,
    @ColumnInfo(name = "detected_at") val detectedAt: String,
    @ColumnInfo(name = "resolved_at") val resolvedAt: String?,
    @ColumnInfo(name = "sync_status") val syncStatus: String,
    @ColumnInfo(name = "last_sync_version") val lastSyncVersion: Int,
    @ColumnInfo(name = "created_at") val createdAt: String,
    @ColumnInfo(name = "updated_at") val updatedAt: String
)

4.3. cli_interaction_acknowledgments¶

Confirmaciones del usuario para interacciones detectadas.

Clasificacion: SYNCED_E2E (PHI - decisiones medicas)

4.3.1. Estructura (Conceptual)¶

Campo	Tipo	Descripcion
ack_id	UUID	Identificador unico
detection_id	UUID	FK a cli_detected_interactions
user_id	UUID	FK a usuarios
acknowledged_at	DateTime	Timestamp de confirmacion
checkbox_confirmed	Boolean	Si marco el checkbox de entendimiento
consulted_doctor	Boolean	Si consulto con medico (CONTRAINDICADO)
notes	Text	Notas del usuario
sync_status	Enum	PENDING, SYNCED, CONFLICT
last_sync_version	Int	Version de sincronizacion
created_at	DateTime	Timestamp creacion

4.3.2. SQLite (iOS/Android)¶

-- Cliente SQLite
CREATE TABLE cli_interaction_acknowledgments (
    ack_id              TEXT PRIMARY KEY,
    detection_id        TEXT NOT NULL,
    user_id             TEXT NOT NULL,
    acknowledged_at     TEXT NOT NULL,  -- ISO 8601
    checkbox_confirmed  INTEGER NOT NULL DEFAULT 0,  -- Boolean
    consulted_doctor    INTEGER NOT NULL DEFAULT 0,  -- Boolean
    notes               TEXT,
    sync_status         TEXT NOT NULL DEFAULT 'PENDING',
    last_sync_version   INTEGER DEFAULT 0,
    created_at          TEXT NOT NULL,
    FOREIGN KEY (detection_id) REFERENCES cli_detected_interactions(detection_id)
);

-- Indices
CREATE UNIQUE INDEX idx_ack_detection ON cli_interaction_acknowledgments(detection_id);
CREATE INDEX idx_ack_user ON cli_interaction_acknowledgments(user_id);
CREATE INDEX idx_ack_sync ON cli_interaction_acknowledgments(sync_status) WHERE sync_status = 'PENDING';

4.3.3. Realm (iOS)¶

// Realm Model (iOS)
class InteractionAcknowledgment: Object {
    @Persisted(primaryKey: true) var ackId: String
    @Persisted var detectionId: String
    @Persisted var userId: String
    @Persisted var acknowledgedAt: Date
    @Persisted var checkboxConfirmed: Bool
    @Persisted var consultedDoctor: Bool
    @Persisted var notes: String?
    @Persisted var syncStatus: String
    @Persisted var lastSyncVersion: Int
    @Persisted var createdAt: Date
}

4.3.4. Room (Android)¶

// Room Entity (Android)
@Entity(
    tableName = "cli_interaction_acknowledgments",
    foreignKeys = [
        ForeignKey(
            entity = DetectedInteraction::class,
            parentColumns = ["detection_id"],
            childColumns = ["detection_id"]
        )
    ],
    indices = [
        Index(value = ["detection_id"], unique = true),
        Index(value = ["user_id"]),
        Index(value = ["sync_status"])
    ]
)
data class InteractionAcknowledgment(
    @PrimaryKey val ackId: String,
    @ColumnInfo(name = "detection_id") val detectionId: String,
    @ColumnInfo(name = "user_id") val userId: String,
    @ColumnInfo(name = "acknowledged_at") val acknowledgedAt: String,
    @ColumnInfo(name = "checkbox_confirmed") val checkboxConfirmed: Boolean,
    @ColumnInfo(name = "consulted_doctor") val consultedDoctor: Boolean,
    val notes: String?,
    @ColumnInfo(name = "sync_status") val syncStatus: String,
    @ColumnInfo(name = "last_sync_version") val lastSyncVersion: Int,
    @ColumnInfo(name = "created_at") val createdAt: String
)

4.4. cli_interaction_history¶

Historial de alertas de interacciones mostradas al usuario (retencion 6 anos HIPAA).

Clasificacion: SYNCED_E2E (PHI)

4.4.1. Estructura (Conceptual)¶

Campo	Tipo	Descripcion
history_id	UUID	Identificador unico
user_id	UUID	FK a usuarios
detection_id	UUID	FK a cli_detected_interactions
alert_type	Enum	BLOCKING, PROMINENT, WARNING, INFO
severity	Enum	CONTRAINDICADO, SERIO, MODERADO, MENOR
shown_at	DateTime	Timestamp de cuando se mostro
dismissed_at	DateTime	Timestamp de cuando se descarto
user_action	Enum	ACKNOWLEDGED, DISMISSED, CONSULTED_DOCTOR, IGNORED
sync_status	Enum	PENDING, SYNCED, CONFLICT
last_sync_version	Int	Version de sincronizacion
created_at	DateTime	Timestamp creacion

4.4.2. SQLite (iOS/Android)¶

-- Cliente SQLite
CREATE TABLE cli_interaction_history (
    history_id          TEXT PRIMARY KEY,
    user_id             TEXT NOT NULL,
    detection_id        TEXT NOT NULL,
    alert_type          TEXT NOT NULL CHECK (alert_type IN ('BLOCKING', 'PROMINENT', 'WARNING', 'INFO')),
    severity            TEXT NOT NULL,
    shown_at            TEXT NOT NULL,  -- ISO 8601
    dismissed_at        TEXT,
    user_action         TEXT CHECK (user_action IN ('ACKNOWLEDGED', 'DISMISSED', 'CONSULTED_DOCTOR', 'IGNORED')),
    sync_status         TEXT NOT NULL DEFAULT 'PENDING',
    last_sync_version   INTEGER DEFAULT 0,
    created_at          TEXT NOT NULL,
    FOREIGN KEY (detection_id) REFERENCES cli_detected_interactions(detection_id)
);

-- Indices
CREATE INDEX idx_history_user ON cli_interaction_history(user_id);
CREATE INDEX idx_history_detection ON cli_interaction_history(detection_id);
CREATE INDEX idx_history_shown ON cli_interaction_history(shown_at DESC);
CREATE INDEX idx_history_sync ON cli_interaction_history(sync_status) WHERE sync_status = 'PENDING';

-- Politica de retencion: 6 anos (HIPAA)
-- Se implementa mediante job que elimina registros > 6 anos

4.4.3. Realm (iOS)¶

// Realm Model (iOS)
class InteractionHistory: Object {
    @Persisted(primaryKey: true) var historyId: String
    @Persisted var userId: String
    @Persisted var detectionId: String
    @Persisted var alertType: String // BLOCKING, PROMINENT, WARNING, INFO
    @Persisted var severity: String
    @Persisted var shownAt: Date
    @Persisted var dismissedAt: Date?
    @Persisted var userAction: String?
    @Persisted var syncStatus: String
    @Persisted var lastSyncVersion: Int
    @Persisted var createdAt: Date
}

4.4.4. Room (Android)¶

// Room Entity (Android)
@Entity(
    tableName = "cli_interaction_history",
    foreignKeys = [
        ForeignKey(
            entity = DetectedInteraction::class,
            parentColumns = ["detection_id"],
            childColumns = ["detection_id"]
        )
    ],
    indices = [
        Index(value = ["user_id"]),
        Index(value = ["detection_id"]),
        Index(value = ["shown_at"]),
        Index(value = ["sync_status"])
    ]
)
data class InteractionHistory(
    @PrimaryKey val historyId: String,
    @ColumnInfo(name = "user_id") val userId: String,
    @ColumnInfo(name = "detection_id") val detectionId: String,
    @ColumnInfo(name = "alert_type") val alertType: String,
    val severity: String,
    @ColumnInfo(name = "shown_at") val shownAt: String,
    @ColumnInfo(name = "dismissed_at") val dismissedAt: String?,
    @ColumnInfo(name = "user_action") val userAction: String?,
    @ColumnInfo(name = "sync_status") val syncStatus: String,
    @ColumnInfo(name = "last_sync_version") val lastSyncVersion: Int,
    @ColumnInfo(name = "created_at") val createdAt: String
)

4.5. cli_interaction_consent¶

Registro del consentimiento del usuario para disclaimer de privacidad en busquedas online.

Clasificacion: SYNCED_E2E (PHI - vinculado a usuario)

4.5.1. Estructura (Conceptual)¶

Campo	Tipo	Descripcion
consent_id	UUID	Identificador unico
user_id	UUID	FK a usuarios (nullable para Free sin cuenta)
consent_type	Enum	AUTOMATIC_SEARCH, MANUAL_SEARCH
consented_at	DateTime	Timestamp de cuando acepto
ip_hash	String	Blind index del IP (HMAC-SHA256)
app_version	String	Version de la app
sync_status	Enum	PENDING, SYNCED, CONFLICT
last_sync_version	Int	Version de sincronizacion
created_at	DateTime	Timestamp creacion

4.5.2. SQLite (iOS/Android)¶

-- Cliente SQLite
CREATE TABLE cli_interaction_consent (
    consent_id          TEXT PRIMARY KEY,
    user_id             TEXT,  -- Nullable para Free sin cuenta
    consent_type        TEXT NOT NULL CHECK (consent_type IN ('AUTOMATIC_SEARCH', 'MANUAL_SEARCH')),
    consented_at        TEXT NOT NULL,  -- ISO 8601
    ip_hash             TEXT,  -- Blind index
    app_version         TEXT NOT NULL,
    sync_status         TEXT NOT NULL DEFAULT 'PENDING',
    last_sync_version   INTEGER DEFAULT 0,
    created_at          TEXT NOT NULL
);

-- Indices
CREATE INDEX idx_consent_user ON cli_interaction_consent(user_id);
CREATE INDEX idx_consent_type ON cli_interaction_consent(consent_type);
CREATE INDEX idx_consent_sync ON cli_interaction_consent(sync_status) WHERE sync_status = 'PENDING';

4.5.3. Realm (iOS)¶

// Realm Model (iOS)
class InteractionConsent: Object {
    @Persisted(primaryKey: true) var consentId: String
    @Persisted var userId: String?  // Nullable para Free sin cuenta
    @Persisted var consentType: String // AUTOMATIC_SEARCH, MANUAL_SEARCH
    @Persisted var consentedAt: Date
    @Persisted var ipHash: String?
    @Persisted var appVersion: String
    @Persisted var syncStatus: String
    @Persisted var lastSyncVersion: Int
    @Persisted var createdAt: Date
}

4.5.4. Room (Android)¶

// Room Entity (Android)
@Entity(
    tableName = "cli_interaction_consent",
    indices = [
        Index(value = ["user_id"]),
        Index(value = ["consent_type"]),
        Index(value = ["sync_status"])
    ]
)
data class InteractionConsent(
    @PrimaryKey val consentId: String,
    @ColumnInfo(name = "user_id") val userId: String?,  // Nullable para Free sin cuenta
    @ColumnInfo(name = "consent_type") val consentType: String,
    @ColumnInfo(name = "consented_at") val consentedAt: String,
    @ColumnInfo(name = "ip_hash") val ipHash: String?,
    @ColumnInfo(name = "app_version") val appVersion: String,
    @ColumnInfo(name = "sync_status") val syncStatus: String,
    @ColumnInfo(name = "last_sync_version") val lastSyncVersion: Int,
    @ColumnInfo(name = "created_at") val createdAt: String
)

4.6. cli_pending_verifications¶

Cola temporal de verificaciones pendientes cuando DrugBank API falla (fallback).

CUMPLE REGLA 100: Esta tabla es una cola temporal limitada a max 10 verificaciones pendientes. No es un catalogo que se descarga masivamente.

Clasificacion: LOCAL_ONLY (no se sincroniza)

4.6.1. Estructura (Conceptual)¶

Campo	Tipo	Descripcion
verification_id	UUID	Identificador unico
medication_a_id	UUID	FK a medicamentos del usuario
medication_b_id	UUID	FK a medicamentos del usuario
added_at	DateTime	Timestamp de cuando se agrego a la cola
last_attempt_at	DateTime	Ultimo intento de verificacion
attempts	Int	Numero de reintentos (max 5)
status	Enum	PENDING, VERIFIED, FAILED
error_type	Enum	TIMEOUT, API_ERROR, RATE_LIMIT, NO_CONNECTION
next_retry_at	DateTime	Timestamp de proximo reintento
created_at	DateTime	Timestamp creacion

4.6.2. SQLite (iOS/Android)¶

-- Cliente SQLite
CREATE TABLE cli_pending_verifications (
    verification_id     TEXT PRIMARY KEY,
    medication_a_id     TEXT NOT NULL,
    medication_b_id     TEXT NOT NULL,
    added_at            TEXT NOT NULL,  -- ISO 8601
    last_attempt_at     TEXT,
    attempts            INTEGER NOT NULL DEFAULT 0,
    status              TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'VERIFIED', 'FAILED')),
    error_type          TEXT CHECK (error_type IN ('TIMEOUT', 'API_ERROR', 'RATE_LIMIT', 'NO_CONNECTION')),
    next_retry_at       TEXT,
    created_at          TEXT NOT NULL,
    FOREIGN KEY (medication_a_id) REFERENCES cli_medications(medication_id),
    FOREIGN KEY (medication_b_id) REFERENCES cli_medications(medication_id),
    UNIQUE(medication_a_id, medication_b_id)
);

-- Indices
CREATE INDEX idx_verification_status ON cli_pending_verifications(status) WHERE status = 'PENDING';
CREATE INDEX idx_verification_retry ON cli_pending_verifications(next_retry_at) WHERE status = 'PENDING';

4.6.3. Realm (iOS)¶

// Realm Model (iOS)
class PendingVerification: Object {
    @Persisted(primaryKey: true) var verificationId: String
    @Persisted var medicationAId: String
    @Persisted var medicationBId: String
    @Persisted var addedAt: Date
    @Persisted var lastAttemptAt: Date?
    @Persisted var attempts: Int
    @Persisted var status: String // PENDING, VERIFIED, FAILED
    @Persisted var errorType: String?
    @Persisted var nextRetryAt: Date?
    @Persisted var createdAt: Date
}

4.6.4. Room (Android)¶

// Room Entity (Android)
@Entity(
    tableName = "cli_pending_verifications",
    foreignKeys = [
        ForeignKey(
            entity = Medication::class,
            parentColumns = ["medication_id"],
            childColumns = ["medication_a_id"]
        ),
        ForeignKey(
            entity = Medication::class,
            parentColumns = ["medication_id"],
            childColumns = ["medication_b_id"]
        )
    ],
    indices = [
        Index(value = ["medication_a_id", "medication_b_id"], unique = true),
        Index(value = ["status"]),
        Index(value = ["next_retry_at"])
    ]
)
data class PendingVerification(
    @PrimaryKey val verificationId: String,
    @ColumnInfo(name = "medication_a_id") val medicationAId: String,
    @ColumnInfo(name = "medication_b_id") val medicationBId: String,
    @ColumnInfo(name = "added_at") val addedAt: String,
    @ColumnInfo(name = "last_attempt_at") val lastAttemptAt: String?,
    val attempts: Int,
    val status: String,
    @ColumnInfo(name = "error_type") val errorType: String?,
    @ColumnInfo(name = "next_retry_at") val nextRetryAt: String?,
    @ColumnInfo(name = "created_at") val createdAt: String
)

5. Mapeo de Sincronizacion¶

5.1. Cache Critico de Interacciones (Embebido en App)¶

Campo LOCAL	→ SERVIDOR	Formato
cli_critical_interactions_cache	N/A	EMBEBIDO en codigo (NO sincroniza)

Flujo:

Cache de <100 interacciones CONTRAINDICADAS esta embebido en el codigo de la app
Se actualiza solo con nuevas versiones de la app (NO OTA)
NO hay sincronizacion dinamica (cumple Regla 100)

Busquedas Online (Pro/Perfect):

Usuario con >2 medicamentos activos puede optar por busqueda online
Requiere aceptar disclaimer de privacidad (cli_interaction_consent)
Busqueda se hace contra srv_interaction_catalog via API
Resultados se guardan en cli_detected_interactions (cifrado E2E)

5.2. Detecciones de Usuario (Bidireccional E2E)¶

Campo LOCAL	→ SERVIDOR	Formato
detection_id, medication_a_id, medication_b_id, severity, status, detected_at	encrypted_data	Blob E2E (AES-256-GCM)
sync_status	N/A	LOCAL_ONLY
last_sync_version	sync_version	Plaintext (metadata)

Flujo de Sync:

PUSH (Cliente → Servidor):
1. Filtrar detecciones con sync_status = PENDING
2. Cifrar blob: {detection_id, medication_a_id, medication_b_id, severity, status, detected_at, ...}
3. POST /v1/sync/interactions/push con encrypted_blob
4. Actualizar sync_status = SYNCED, last_sync_version

PULL (Servidor → Cliente):
1. GET /v1/sync/interactions/pull?since=<last_sync_version>
2. Recibir blobs cifrados
3. Descifrar con master_key local
4. Merge con detecciones locales (resolver conflictos)

5.3. Confirmaciones (Bidireccional E2E)¶

Campo LOCAL	→ SERVIDOR	Formato
ack_id, detection_id, acknowledged_at, checkbox_confirmed, consulted_doctor, notes	encrypted_data	Blob E2E
sync_status	N/A	LOCAL_ONLY
last_sync_version	sync_version	Plaintext

Igual que detecciones: Push/Pull bidireccional con cifrado E2E.

5.4. Historial (Bidireccional E2E)¶

Campo LOCAL	→ SERVIDOR	Formato
history_id, detection_id, alert_type, severity, shown_at, dismissed_at, user_action	encrypted_data	Blob E2E
sync_status	N/A	LOCAL_ONLY
last_sync_version	sync_version	Plaintext

Retencion: 6 anos (HIPAA). El servidor mantiene blobs cifrados por 6 anos.

5.5. Consentimiento de Busqueda (Bidireccional E2E)¶

Campo LOCAL	→ SERVIDOR	Formato
consent_id, user_id, consent_type, consented_at, app_version	encrypted_data	Blob E2E
ip_hash	blind_index	HMAC-SHA256
sync_status	N/A	LOCAL_ONLY
last_sync_version	sync_version	Plaintext

Flujo:

Usuario inicia busqueda online de interacciones
Si no ha aceptado disclaimer, se muestra dialogo de consentimiento
Al aceptar, se crea registro en cli_interaction_consent
Se sincroniza cifrado E2E al servidor (obligacion legal)
ip_hash se usa como blind index (no vincula a usuario, solo metadata)

6. Diagrama ER del Modulo¶

6.1. Servidor¶

+--------------------------------+
|  srv_interaction_catalog       |
+--------------------------------+
| PK: interaction_id             |
| FK: drug_a_id → srv_drug_catalog |
| FK: drug_b_id → srv_drug_catalog |
|     principle_a                |
|     principle_b                |
|     severity                   |
|     severity_code              |
|     ...                        |
+--------------------------------+
           ↓ (referenciado por hash)
+--------------------------------+
|  srv_drugbank_cache            |
+--------------------------------+
| PK: cache_id                   |
|     drug_pair_hash (blind idx) |
|     tier                       |
|     interactions_found         |
|     max_severity_code          |
|     expires_at                 |
|     ...                        |
+--------------------------------+

+--------------------------------+
|  srv_ia_analysis_log           |
+--------------------------------+
| PK: log_id                     |
|     session_token (efimero)    |
|     num_medications            |
|     tokens_total               |
|     ...                        |
+--------------------------------+

6.2. Cliente¶

+----------------------------------------+
| cli_critical_interactions_cache        |
| (<100 CONTRAINDICADAS - EMBEBIDO)      |
+----------------------------------------+
| PK: cache_id                           |
|     drug_a_id                          |
|     drug_b_id                          |
|     severity = 'CONTRAINDICADO'        |
|     severity_code = 4                  |
|     embedded_version                   |
|     ...                                |
+----------------------------------------+
        ↓ (referenciado por)
+----------------------------------------+
| cli_detected_interactions              |
| (PHI - SYNCED_E2E)                     |
+----------------------------------------+
| PK: detection_id                       |
| FK: medication_a_id → cli_medications  |
| FK: medication_b_id → cli_medications  |
| FK: interaction_cache_id (optional)    |
|     severity                           |
|     status                             |
|     sync_status                        |
|     ...                                |
+----------------------------------------+
        ↓ (1:1)
+----------------------------------------+
| cli_interaction_acknowledgments        |
| (PHI - SYNCED_E2E)                     |
+----------------------------------------+
| PK: ack_id                             |
| FK: detection_id (unique)              |
|     acknowledged_at                    |
|     checkbox_confirmed                 |
|     consulted_doctor                   |
|     ...                                |
+----------------------------------------+

+----------------------------------------+
| cli_interaction_history                |
| (PHI - SYNCED_E2E - 6 anos HIPAA)      |
+----------------------------------------+
| PK: history_id                         |
| FK: detection_id                       |
|     alert_type                         |
|     shown_at                           |
|     ...                                |
+----------------------------------------+

+----------------------------------------+
| cli_interaction_consent (NUEVO)        |
| (PHI - SYNCED_E2E)                     |
+----------------------------------------+
| PK: consent_id                         |
|     user_id (nullable)                 |
|     consent_type                       |
|     consented_at                       |
|     ip_hash (blind index)              |
|     ...                                |
+----------------------------------------+

+----------------------------------------+
| cli_pending_verifications              |
| (LOCAL_ONLY - max 10 registros)        |
+----------------------------------------+
| PK: verification_id                    |
| FK: medication_a_id                    |
| FK: medication_b_id                    |
|     status                             |
|     attempts                           |
|     ...                                |
+----------------------------------------+

7. Consideraciones de Cifrado¶

7.1. Campos Cifrados E2E¶

Las siguientes entidades se cifran COMPLETAS antes de sincronizacion:

Entidad	Campos Cifrados
cli_detected_interactions	TODO el registro (detection_id, medication_a_id, medication_b_id, severity, status, etc.)
cli_interaction_acknowledgments	TODO el registro (ack_id, detection_id, acknowledged_at, checkbox_confirmed, consulted_doctor, notes)
cli_interaction_history	TODO el registro (history_id, detection_id, alert_type, severity, shown_at, dismissed_at, user_action)

Algoritmo: AES-256-GCM

Proceso:

Serializar registro completo a JSON
Generar nonce aleatorio (96 bits)
Cifrar con master_key del usuario
Empaquetar: nonce || ciphertext || auth_tag
Calcular SHA-256 del blob cifrado (integridad)

Servidor recibe:

{
  "entity_type": "detected_interaction",
  "entity_id": "uuid",
  "encrypted_blob": "base64...",
  "blob_hash": "sha256:abc123...",
  "sync_version": 123456,
  "updated_at": "2025-12-08T10:30:00Z"
}

NO se usan blind indexes para interacciones detectadas porque:

No hay necesidad de busqueda por parte del servidor
El servidor NUNCA necesita saber que medicamentos especificos tienen interacciones
Toda la deteccion es LOCAL en el dispositivo

Excepcion: srv_drugbank_cache.drug_pair_hash es un blind index, pero NO contiene datos del usuario (solo metadata de consultas API).

7.3. Proceso de Sincronizacion¶

flowchart TD
    A[Usuario agrega medicamento B] --> B[Motor local detecta interaccion con medicamento A]
    B --> C[Crear registro cli_detected_interactions]
    C --> D[sync_status = PENDING]

    D --> E{Hay conexion?}
    E -->|No| F[Mantener PENDING, reintentar luego]
    E -->|Si| G[Serializar registro a JSON]

    G --> H[Obtener master_key de Keychain]
    H --> I[Cifrar JSON con AES-256-GCM]
    I --> J[Calcular SHA-256 del blob]

    J --> K[POST /v1/sync/interactions/push]
    K --> L{Respuesta 200?}

    L -->|Si| M[sync_status = SYNCED]
    L -->|Conflicto 409| N[Resolver conflicto manual]
    L -->|Error 5xx| O[Reintentar con backoff]

    M --> P[last_sync_version++]

8. Reglas de Validacion¶

8.1. Nivel Servidor¶

Regla	Descripcion
VR-INT-001	interaction_id debe ser UUID valido
VR-INT-002	drug_a_id < drug_b_id (evitar duplicados A-B == B-A)
VR-INT-003	drug_a_id != drug_b_id
VR-INT-004	severity_code debe estar entre 1-4
VR-INT-005	expires_at en srv_drugbank_cache debe ser futura
VR-INT-006	session_token en srv_ia_analysis_log debe ser unico por consulta

8.2. Nivel Cliente¶

Regla	Descripcion
VR-INT-010	Antes de agregar a cli_detected_interactions, verificar que medicamentos existan en cli_medications
VR-INT-011	status = ACKNOWLEDGED requiere registro en cli_interaction_acknowledgments
VR-INT-012	severity = CONTRAINDICADO requiere checkbox_confirmed = TRUE en acknowledgment
VR-INT-013	cli_pending_verifications.attempts no debe exceder 5
VR-INT-014	cli_interaction_history debe registrarse SIEMPRE que se muestre alerta

9. Politicas RLS (Row Level Security)¶

9.1. srv_interaction_catalog (Sin RLS)¶

Razon: Catalogo publico, accesible sin autenticacion.

-- No RLS policies needed
-- Public catalog, read-only access for all authenticated users

9.2. srv_drugbank_cache¶

Razon: Metadata operativa, solo backend puede leer/escribir.

-- Enable RLS
ALTER TABLE srv_drugbank_cache ENABLE ROW LEVEL SECURITY;

-- Policy: Solo backend puede leer/escribir
CREATE POLICY backend_only_drugbank_cache ON srv_drugbank_cache
    FOR ALL
    USING (false)  -- Usuarios no pueden acceder
    WITH CHECK (false);

-- Grant para service role (backend)
GRANT ALL ON srv_drugbank_cache TO service_role;

9.3. srv_ia_analysis_log¶

Razon: Log interno, solo backend.

-- Enable RLS
ALTER TABLE srv_ia_analysis_log ENABLE ROW LEVEL SECURITY;

-- Policy: Solo backend
CREATE POLICY backend_only_ia_log ON srv_ia_analysis_log
    FOR ALL
    USING (false)
    WITH CHECK (false);

-- Grant para service role
GRANT ALL ON srv_ia_analysis_log TO service_role;

Nota sobre datos sincronizados E2E:

Los datos de usuario (cli_detected_interactions, cli_interaction_acknowledgments, cli_interaction_history) NO se almacenan en tablas individuales en el servidor. Se almacenan en la tabla generica encrypted_user_data (ver MDL-SYNC-001) como blobs cifrados.

Por lo tanto, NO requieren RLS especificas aqui. Las RLS de encrypted_user_data aplican:

-- Politica en encrypted_user_data (de MDL-SYNC-001)
CREATE POLICY user_encrypted_data_policy ON encrypted_user_data
    FOR ALL
    USING (user_id = auth.uid())
    WITH CHECK (user_id = auth.uid());

10. Queries de Ejemplo¶

10.1. Buscar Interaccion CRITICA en Cache Local (<100)¶

-- SQLite (Cliente)
-- Buscar interaccion CONTRAINDICADA en cache embebido
-- NOTA: Solo <100 interacciones criticas estan embebidas
SELECT
    cache_id,
    severity,
    severity_code,
    description,
    mechanism,
    clinical_recommendation,
    suggested_action,
    primary_source
FROM cli_critical_interactions_cache
WHERE (drug_a_id = ? AND drug_b_id = ?)
   OR (drug_a_id = ? AND drug_b_id = ?)
LIMIT 1;

-- SQLite (Cliente)
-- Buscar interaccion CONTRAINDICADA por principios activos
SELECT
    cache_id,
    severity,
    severity_code,
    description,
    clinical_recommendation
FROM cli_critical_interactions_cache
WHERE (principle_a = ? AND principle_b = ?)
   OR (principle_a = ? AND principle_b = ?)
LIMIT 1;

-- SQLite (Cliente)
-- Verificar si usuario ha dado consentimiento para busqueda online
SELECT consent_id, consented_at
FROM cli_interaction_consent
WHERE user_id = ?
  AND consent_type = 'AUTOMATIC_SEARCH'
ORDER BY consented_at DESC
LIMIT 1;

10.2. Detectar Interacciones para Medicamentos del Usuario¶

-- SQLite (Cliente)
-- Algoritmo O(n^2): comparar todos los pares de medicamentos activos
WITH active_meds AS (
    SELECT medication_id, drug_id, principle_active
    FROM cli_medications
    WHERE user_id = ?
      AND status = 'ACTIVE'
      AND deleted_at IS NULL
)
SELECT
    a.medication_id AS med_a_id,
    b.medication_id AS med_b_id,
    ic.cache_id,
    ic.severity,
    ic.severity_code,
    ic.description,
    ic.clinical_recommendation
FROM active_meds a
CROSS JOIN active_meds b
INNER JOIN cli_critical_interactions_cache ic
    ON (
        (ic.principle_a = a.principle_active AND ic.principle_b = b.principle_active)
        OR
        (ic.principle_a = b.principle_active AND ic.principle_b = a.principle_active)
    )
WHERE a.medication_id < b.medication_id  -- Evitar duplicados y self-join
ORDER BY ic.severity_code DESC;

10.3. Obtener Historial de Alertas (6 anos HIPAA)¶

-- SQLite (Cliente)
-- Historial de alertas de interacciones en los ultimos 6 anos
SELECT
    h.history_id,
    h.detection_id,
    h.alert_type,
    h.severity,
    h.shown_at,
    h.dismissed_at,
    h.user_action,
    d.medication_a_id,
    d.medication_b_id
FROM cli_interaction_history h
INNER JOIN cli_detected_interactions d
    ON h.detection_id = d.detection_id
WHERE h.user_id = ?
  AND h.shown_at >= datetime('now', '-6 years')
ORDER BY h.shown_at DESC;

-- SQLite (Cliente)
-- Cleanup de historial > 6 anos (ejecutar periodicamente)
DELETE FROM cli_interaction_history
WHERE shown_at < datetime('now', '-6 years');

10.4. Verificaciones Pendientes (Fallback)¶

-- SQLite (Cliente)
-- Obtener verificaciones pendientes para reintentar
SELECT
    verification_id,
    medication_a_id,
    medication_b_id,
    attempts,
    next_retry_at
FROM cli_pending_verifications
WHERE status = 'PENDING'
  AND attempts < 5
  AND next_retry_at <= datetime('now')
ORDER BY added_at ASC
LIMIT 10;

-- SQLite (Cliente)
-- Actualizar intento fallido
UPDATE cli_pending_verifications
SET
    last_attempt_at = datetime('now'),
    attempts = attempts + 1,
    error_type = ?,
    next_retry_at = datetime('now', '+1 hour')  -- Backoff exponencial
WHERE verification_id = ?;

-- SQLite (Cliente)
-- Marcar como verificado exitosamente
UPDATE cli_pending_verifications
SET
    status = 'VERIFIED',
    last_attempt_at = datetime('now')
WHERE verification_id = ?;

10.5. Consultar Cache DrugBank (Servidor)¶

-- PostgreSQL (Servidor)
-- Buscar en cache de DrugBank por hash de par
SELECT
    cache_id,
    interactions_found,
    max_severity_code,
    queried_at,
    expires_at
FROM srv_drugbank_cache
WHERE drug_pair_hash = ?
  AND expires_at > NOW()
LIMIT 1;

-- PostgreSQL (Servidor)
-- Insertar resultado de consulta DrugBank
INSERT INTO srv_drugbank_cache (
    drug_pair_hash,
    tier,
    http_status,
    response_time_ms,
    interactions_found,
    max_severity_code,
    queried_at,
    source_version
) VALUES (?, ?, ?, ?, ?, ?, NOW(), ?)
ON CONFLICT (drug_pair_hash)
DO UPDATE SET
    http_status = EXCLUDED.http_status,
    response_time_ms = EXCLUDED.response_time_ms,
    interactions_found = EXCLUDED.interactions_found,
    max_severity_code = EXCLUDED.max_severity_code,
    queried_at = NOW(),
    expires_at = NOW() + INTERVAL '7 days';

11. Migraciones¶

11.1. Migration 001: Create Interactions Schema (Servidor)¶

-- Migration: 001_create_interactions_schema.sql
-- Version: 1.0.0
-- Date: 2025-12-08
-- Author: DatabaseDrone

BEGIN;

-- ====================
-- srv_interaction_catalog
-- ====================
CREATE TABLE srv_interaction_catalog (
    interaction_id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    drug_a_id           UUID NOT NULL,
    drug_b_id           UUID NOT NULL,
    principle_a         VARCHAR(200) NOT NULL,
    principle_b         VARCHAR(200) NOT NULL,
    severity            VARCHAR(20) NOT NULL CHECK (severity IN ('CONTRAINDICADO', 'SERIO', 'MODERADO', 'MENOR')),
    severity_code       SMALLINT NOT NULL CHECK (severity_code BETWEEN 1 AND 4),
    interaction_type    VARCHAR(20) NOT NULL CHECK (interaction_type IN ('FARMACOCINETICA', 'FARMACODINAMICA', 'MIXTA')),
    category            VARCHAR(100),
    description         TEXT NOT NULL,
    mechanism           TEXT,
    expected_effect     TEXT,
    possible_symptoms   TEXT[],
    clinical_recommendation TEXT,
    suggested_action    VARCHAR(20) CHECK (suggested_action IN ('EVITAR', 'MONITOREAR', 'AJUSTAR_DOSIS', 'ESPACIAR', 'INFORMAR')),
    monitoring_required TEXT,
    primary_source      VARCHAR(100) NOT NULL,
    additional_sources  TEXT[],
    evidence_level      VARCHAR(10) CHECK (evidence_level IN ('ALTA', 'MEDIA', 'BAJA')),
    pubmed_refs         TEXT[],
    last_updated_date   DATE NOT NULL,
    applicable_countries TEXT[] DEFAULT ARRAY['MX', 'US', 'BR'],
    regional_notes      JSONB,
    is_active           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT unique_pair CHECK (drug_a_id < drug_b_id),
    CONSTRAINT different_drugs CHECK (drug_a_id != drug_b_id)
);

CREATE INDEX idx_interaction_drugs ON srv_interaction_catalog(drug_a_id, drug_b_id);
CREATE INDEX idx_interaction_drugs_reverse ON srv_interaction_catalog(drug_b_id, drug_a_id);
CREATE INDEX idx_interaction_principles ON srv_interaction_catalog(principle_a, principle_b);
CREATE INDEX idx_interaction_severity ON srv_interaction_catalog(severity_code DESC);
CREATE INDEX idx_interaction_category ON srv_interaction_catalog(category) WHERE category IS NOT NULL;
CREATE INDEX idx_interaction_active ON srv_interaction_catalog(is_active) WHERE is_active = TRUE;
CREATE INDEX idx_interaction_updated ON srv_interaction_catalog(updated_at DESC);
CREATE INDEX idx_interaction_search ON srv_interaction_catalog USING GIN(to_tsvector('spanish', description));

-- Trigger para updated_at
CREATE OR REPLACE FUNCTION update_interaction_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_interaction_updated_at
    BEFORE UPDATE ON srv_interaction_catalog
    FOR EACH ROW
    EXECUTE FUNCTION update_interaction_updated_at();

-- ====================
-- srv_drugbank_cache
-- ====================
CREATE TABLE srv_drugbank_cache (
    cache_id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    drug_pair_hash      VARCHAR(64) NOT NULL UNIQUE,
    tier                VARCHAR(10) NOT NULL CHECK (tier IN ('Pro', 'Perfect')),
    http_status         SMALLINT NOT NULL,
    response_time_ms    INT,
    tokens_used         INT,
    interactions_found  INT NOT NULL DEFAULT 0,
    max_severity_code   SMALLINT CHECK (max_severity_code BETWEEN 0 AND 4),
    queried_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at          TIMESTAMPTZ NOT NULL,
    source_version      VARCHAR(20),
    is_error            BOOLEAN NOT NULL DEFAULT FALSE,
    error_message       TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_drugbank_cache_hash ON srv_drugbank_cache(drug_pair_hash);
CREATE INDEX idx_drugbank_cache_expires ON srv_drugbank_cache(expires_at);
CREATE INDEX idx_drugbank_cache_tier ON srv_drugbank_cache(tier, queried_at);
CREATE INDEX idx_drugbank_cache_errors ON srv_drugbank_cache(is_error) WHERE is_error = TRUE;

-- Trigger para expires_at
CREATE OR REPLACE FUNCTION set_drugbank_cache_expiry()
RETURNS TRIGGER AS $$
BEGIN
    NEW.expires_at = NEW.queried_at + INTERVAL '7 days';
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_drugbank_cache_expiry
    BEFORE INSERT ON srv_drugbank_cache
    FOR EACH ROW
    WHEN (NEW.expires_at IS NULL)
    EXECUTE FUNCTION set_drugbank_cache_expiry();

-- Funcion de limpieza
CREATE OR REPLACE FUNCTION cleanup_expired_drugbank_cache()
RETURNS void AS $$
BEGIN
    DELETE FROM srv_drugbank_cache WHERE expires_at < NOW();
END;
$$ LANGUAGE plpgsql;

-- ====================
-- srv_ia_analysis_log
-- ====================
CREATE TABLE srv_ia_analysis_log (
    log_id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_token       VARCHAR(64) NOT NULL,
    num_medications     SMALLINT NOT NULL,
    severity_detected   VARCHAR(20),
    request_time_ms     INT,
    tokens_input        INT,
    tokens_output       INT,
    tokens_total        INT,
    http_status         SMALLINT NOT NULL,
    is_error            BOOLEAN NOT NULL DEFAULT FALSE,
    error_type          VARCHAR(50),
    analyzed_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    anthropic_version   VARCHAR(20),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_ia_log_analyzed ON srv_ia_analysis_log(analyzed_at DESC);
CREATE INDEX idx_ia_log_errors ON srv_ia_analysis_log(is_error) WHERE is_error = TRUE;
CREATE INDEX idx_ia_log_tokens ON srv_ia_analysis_log(tokens_total) WHERE tokens_total IS NOT NULL;

-- Funcion de limpieza (90 dias)
CREATE OR REPLACE FUNCTION cleanup_old_ia_logs()
RETURNS void AS $$
BEGIN
    DELETE FROM srv_ia_analysis_log WHERE analyzed_at < NOW() - INTERVAL '90 days';
END;
$$ LANGUAGE plpgsql;

-- ====================
-- RLS Policies
-- ====================

-- srv_interaction_catalog: publico, sin RLS
-- (usuarios autenticados pueden leer)

-- srv_drugbank_cache: solo backend
ALTER TABLE srv_drugbank_cache ENABLE ROW LEVEL SECURITY;
CREATE POLICY backend_only_drugbank_cache ON srv_drugbank_cache FOR ALL USING (false) WITH CHECK (false);
GRANT ALL ON srv_drugbank_cache TO service_role;

-- srv_ia_analysis_log: solo backend
ALTER TABLE srv_ia_analysis_log ENABLE ROW LEVEL SECURITY;
CREATE POLICY backend_only_ia_log ON srv_ia_analysis_log FOR ALL USING (false) WITH CHECK (false);
GRANT ALL ON srv_ia_analysis_log TO service_role;

COMMIT;

11.2. Migration 001: Create Interactions Schema (Cliente SQLite)¶

-- Migration: 001_create_interactions_schema_client.sql
-- Version: 2.0.0
-- Date: 2025-12-08
-- Platform: SQLite (iOS/Android)
-- Author: DatabaseDrone
-- DIRECTIVA DEL DIRECTOR: Regla 100 Registros

BEGIN TRANSACTION;

-- ====================
-- cli_critical_interactions_cache
-- DIRECTIVA: <100 CONTRAINDICADAS (embebido en app)
-- ====================
CREATE TABLE cli_critical_interactions_cache (
    cache_id            TEXT PRIMARY KEY,
    drug_a_id           TEXT NOT NULL,
    drug_b_id           TEXT NOT NULL,
    principle_a         TEXT NOT NULL,
    principle_b         TEXT NOT NULL,
    severity            TEXT NOT NULL DEFAULT 'CONTRAINDICADO' CHECK (severity = 'CONTRAINDICADO'),
    severity_code       INTEGER NOT NULL DEFAULT 4 CHECK (severity_code = 4),
    interaction_type    TEXT NOT NULL CHECK (interaction_type IN ('FARMACOCINETICA', 'FARMACODINAMICA', 'MIXTA')),
    category            TEXT,
    description         TEXT NOT NULL,
    mechanism           TEXT,
    expected_effect     TEXT,
    clinical_recommendation TEXT,
    suggested_action    TEXT NOT NULL DEFAULT 'EVITAR' CHECK (suggested_action = 'EVITAR'),
    primary_source      TEXT NOT NULL,
    evidence_level      TEXT CHECK (evidence_level IN ('ALTA', 'MEDIA', 'BAJA')),
    embedded_version    TEXT NOT NULL,
    UNIQUE(drug_a_id, drug_b_id),
    CHECK ((SELECT COUNT(*) FROM cli_critical_interactions_cache) <= 100)
);

CREATE INDEX idx_critical_cache_drugs ON cli_critical_interactions_cache(drug_a_id, drug_b_id);
CREATE INDEX idx_critical_cache_principles ON cli_critical_interactions_cache(principle_a, principle_b);

-- ====================
-- cli_detected_interactions
-- ====================
CREATE TABLE cli_detected_interactions (
    detection_id        TEXT PRIMARY KEY,
    user_id             TEXT NOT NULL,
    medication_a_id     TEXT NOT NULL,
    medication_b_id     TEXT NOT NULL,
    interaction_cache_id TEXT,
    severity            TEXT NOT NULL,
    severity_code       INTEGER NOT NULL,
    status              TEXT NOT NULL CHECK (status IN ('ACTIVE', 'ACKNOWLEDGED', 'RESOLVED', 'DISMISSED')),
    detected_at         TEXT NOT NULL,
    resolved_at         TEXT,
    sync_status         TEXT NOT NULL DEFAULT 'PENDING',
    last_sync_version   INTEGER DEFAULT 0,
    created_at          TEXT NOT NULL,
    updated_at          TEXT NOT NULL,
    FOREIGN KEY (medication_a_id) REFERENCES cli_medications(medication_id),
    FOREIGN KEY (medication_b_id) REFERENCES cli_medications(medication_id),
    FOREIGN KEY (interaction_cache_id) REFERENCES cli_critical_interactions_cache(cache_id)
);

CREATE INDEX idx_detected_user ON cli_detected_interactions(user_id);
CREATE INDEX idx_detected_status ON cli_detected_interactions(status) WHERE status = 'ACTIVE';
CREATE INDEX idx_detected_sync ON cli_detected_interactions(sync_status) WHERE sync_status = 'PENDING';
CREATE INDEX idx_detected_medications ON cli_detected_interactions(medication_a_id, medication_b_id);

-- ====================
-- cli_interaction_acknowledgments
-- ====================
CREATE TABLE cli_interaction_acknowledgments (
    ack_id              TEXT PRIMARY KEY,
    detection_id        TEXT NOT NULL,
    user_id             TEXT NOT NULL,
    acknowledged_at     TEXT NOT NULL,
    checkbox_confirmed  INTEGER NOT NULL DEFAULT 0,
    consulted_doctor    INTEGER NOT NULL DEFAULT 0,
    notes               TEXT,
    sync_status         TEXT NOT NULL DEFAULT 'PENDING',
    last_sync_version   INTEGER DEFAULT 0,
    created_at          TEXT NOT NULL,
    FOREIGN KEY (detection_id) REFERENCES cli_detected_interactions(detection_id)
);

CREATE UNIQUE INDEX idx_ack_detection ON cli_interaction_acknowledgments(detection_id);
CREATE INDEX idx_ack_user ON cli_interaction_acknowledgments(user_id);
CREATE INDEX idx_ack_sync ON cli_interaction_acknowledgments(sync_status) WHERE sync_status = 'PENDING';

-- ====================
-- cli_interaction_history
-- ====================
CREATE TABLE cli_interaction_history (
    history_id          TEXT PRIMARY KEY,
    user_id             TEXT NOT NULL,
    detection_id        TEXT NOT NULL,
    alert_type          TEXT NOT NULL CHECK (alert_type IN ('BLOCKING', 'PROMINENT', 'WARNING', 'INFO')),
    severity            TEXT NOT NULL,
    shown_at            TEXT NOT NULL,
    dismissed_at        TEXT,
    user_action         TEXT CHECK (user_action IN ('ACKNOWLEDGED', 'DISMISSED', 'CONSULTED_DOCTOR', 'IGNORED')),
    sync_status         TEXT NOT NULL DEFAULT 'PENDING',
    last_sync_version   INTEGER DEFAULT 0,
    created_at          TEXT NOT NULL,
    FOREIGN KEY (detection_id) REFERENCES cli_detected_interactions(detection_id)
);

CREATE INDEX idx_history_user ON cli_interaction_history(user_id);
CREATE INDEX idx_history_detection ON cli_interaction_history(detection_id);
CREATE INDEX idx_history_shown ON cli_interaction_history(shown_at DESC);
CREATE INDEX idx_history_sync ON cli_interaction_history(sync_status) WHERE sync_status = 'PENDING';

-- ====================
-- cli_interaction_consent (NUEVO)
-- ====================
CREATE TABLE cli_interaction_consent (
    consent_id          TEXT PRIMARY KEY,
    user_id             TEXT,  -- Nullable para Free sin cuenta
    consent_type        TEXT NOT NULL CHECK (consent_type IN ('AUTOMATIC_SEARCH', 'MANUAL_SEARCH')),
    consented_at        TEXT NOT NULL,
    ip_hash             TEXT,
    app_version         TEXT NOT NULL,
    sync_status         TEXT NOT NULL DEFAULT 'PENDING',
    last_sync_version   INTEGER DEFAULT 0,
    created_at          TEXT NOT NULL
);

CREATE INDEX idx_consent_user ON cli_interaction_consent(user_id);
CREATE INDEX idx_consent_type ON cli_interaction_consent(consent_type);
CREATE INDEX idx_consent_sync ON cli_interaction_consent(sync_status) WHERE sync_status = 'PENDING';

-- ====================
-- cli_pending_verifications
-- DIRECTIVA: Max 10 registros (cumple Regla 100)
-- ====================
CREATE TABLE cli_pending_verifications (
    verification_id     TEXT PRIMARY KEY,
    medication_a_id     TEXT NOT NULL,
    medication_b_id     TEXT NOT NULL,
    added_at            TEXT NOT NULL,
    last_attempt_at     TEXT,
    attempts            INTEGER NOT NULL DEFAULT 0,
    status              TEXT NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'VERIFIED', 'FAILED')),
    error_type          TEXT CHECK (error_type IN ('TIMEOUT', 'API_ERROR', 'RATE_LIMIT', 'NO_CONNECTION')),
    next_retry_at       TEXT,
    created_at          TEXT NOT NULL,
    FOREIGN KEY (medication_a_id) REFERENCES cli_medications(medication_id),
    FOREIGN KEY (medication_b_id) REFERENCES cli_medications(medication_id),
    UNIQUE(medication_a_id, medication_b_id)
);

CREATE INDEX idx_verification_status ON cli_pending_verifications(status) WHERE status = 'PENDING';
CREATE INDEX idx_verification_retry ON cli_pending_verifications(next_retry_at) WHERE status = 'PENDING';

COMMIT;

12. Integracion con Otros Modulos¶

12.1. MDL-MED-001 (Medicamentos)¶

Integracion	Descripcion
FK cli_medications	cli_detected_interactions referencia medicamentos del usuario
Trigger de deteccion	Al agregar/modificar medicamento, ejecutar motor de interacciones
Principios activos	Usar principle_active de cli_medications para lookup en cache

Flujo:

1. Usuario agrega medicamento → Trigger en cli_medications
2. Motor obtiene lista de medicamentos ACTIVOS del usuario
3. Compara nuevo medicamento vs existentes usando cli_critical_interactions_cache (<100 CONTRAINDICADAS)
4. Si hay match CRITICO, crea registro en cli_detected_interactions
5. Si NO hay match y usuario es Pro/Perfect con consentimiento:
   - Agregar a cli_pending_verifications (max 10)
   - Buscar online en srv_interaction_catalog
   - Guardar resultado en cli_detected_interactions (cifrado E2E)
6. Genera alerta segun severidad

12.2. MDL-ALT-001 (Alertas)¶

Integracion	Descripcion
Generacion de alertas	cli_detected_interactions genera alertas segun severidad
Tipo de alerta	CONTRAINDICADO → Bloqueante, SERIO → Prominente, etc.
Historial	cli_interaction_history registra alertas mostradas

Mapeo de Severidad a Alerta:

Severidad	Tipo Alerta	Comportamiento
CONTRAINDICADO (4)	BLOCKING	Requiere confirmacion explicita
SERIO (3)	PROMINENT	Alerta prominente, ack requerido
MODERADO (2)	WARNING	Advertencia informativa
MENOR (1)	INFO	Nota discreta

12.3. MDL-USR-001 (Usuarios)¶

Integracion	Descripcion
user_id	Todas las entidades cliente tienen FK a usuarios
Tier verification	Verificar tier para funcionalidades Pro/Perfect (DrugBank, IA)
Sincronizacion	Datos E2E se sincronizan por user_id

13. Metricas y Monitoreo¶

13.1. Metricas de Cache Critico¶

Metrica	Query
Tamano del cache critico	`SELECT COUNT(*) FROM cli_critical_interactions_cache` (debe ser <100)
Version embebida	`SELECT DISTINCT embedded_version FROM cli_critical_interactions_cache`
Todas CONTRAINDICADAS	`SELECT COUNT(*) FROM cli_critical_interactions_cache WHERE severity_code = 4` (debe ser 100%)
Verificaciones pendientes	`SELECT COUNT(*) FROM cli_pending_verifications WHERE status = 'PENDING'` (max 10)

13.2. Metricas de Deteccion¶

Metrica	Query
Interacciones activas	`SELECT COUNT(*) FROM cli_detected_interactions WHERE status = 'ACTIVE'`
Interacciones CONTRAINDICADAS activas	`SELECT COUNT(*) FROM cli_detected_interactions WHERE status = 'ACTIVE' AND severity = 'CONTRAINDICADO'`
Tasa de confirmacion	`SELECT COUNT(ack_id) / COUNT(detection_id) FROM cli_detected_interactions LEFT JOIN cli_interaction_acknowledgments USING(detection_id)`

13.3. Metricas de Verificacion Online¶

Metrica	Query (Servidor)
Consultas DrugBank por tier	`SELECT tier, COUNT(*) FROM srv_drugbank_cache GROUP BY tier`
Tasa de error DrugBank	`SELECT COUNT() FILTER (WHERE is_error) / COUNT() FROM srv_drugbank_cache`
Tiempo promedio de respuesta	`SELECT AVG(response_time_ms) FROM srv_drugbank_cache WHERE is_error = FALSE`
Consultas IA (Perfect)	`SELECT COUNT(*) FROM srv_ia_analysis_log`
Tokens consumidos IA	`SELECT SUM(tokens_total) FROM srv_ia_analysis_log`

14. Referencias¶

14.1. Documentos Tecnicos¶

Documento	Proposito
02-arquitectura-cliente-servidor.md	CRITICO: Regla 100 Registros (seccion 3.2.3.1)
03-estrategia-datos.md	Clasificaciones, sincronizacion
04-seguridad-cliente.md	Cifrado E2E, AES-256-GCM
MDL-MED-001	Modelo de medicamentos
MDL-ALT-001	Modelo de alertas
API-INT-001	API de interacciones (proxima entrega)

14.2. Documentos Funcionales¶

Documento	Proposito
MTS-INT-001	Spec funcional de interacciones med-med
MTS-CAT-001	Catalogo de medicamentos
INV-010	Anonimizacion de datos

14.3. Estandares Externos¶

Estandar	URL
DrugBank	https://go.drugbank.com/documentation
RxNorm	https://rxnav.nlm.nih.gov/RxNormAPIs.html
HIPAA	https://www.hhs.gov/hipaa/for-professionals/security/index.html
ATC Classification	https://www.whocc.no/atc_ddd_index/

Validacion de Modelo: ACTUALIZADO v2.0.0 - DIRECTIVA DEL DIRECTOR

Cambios Criticos:

✅ cli_interaction_cache ELIMINADO (50,000 registros VIOLABAN Regla 100)
✅ cli_critical_interactions_cache NUEVO (<100 CONTRAINDICADAS, embebido en app)
✅ cli_interaction_consent NUEVO (consentimiento de disclaimer de privacidad)
✅ cli_pending_verifications REDUCIDO (max 10 registros, cola temporal)
✅ Busquedas online Pro/Perfect con consentimiento (srv_interaction_catalog ~1M+)

Clasificacion de Datos: COMPLETA

Todos los campos clasificados
Ningun PHI como SYNCED_PLAIN
Datos sensibles como LOCAL_ONLY o SYNCED_E2E
CUMPLE REGLA 100: Ningun catalogo >100 registros se descarga

Esquemas: COMPLETOS

Esquema SERVIDOR conceptual completo (PostgreSQL)
Esquema CLIENTE conceptual completo (SQLite/Realm/Room)
Mapeo de sincronizacion definido
Cache critico <100 embebido en app (NO descarga dinamica)
Nueva tabla de consentimiento para busquedas online

Zero-Knowledge: VALIDADO

Servidor NO puede ver medicamentos del usuario (solo blobs cifrados)
Cache critico es LOCAL_ONLY (embebido, <100 registros)
Busquedas online requieren consentimiento explicito
Blind Index solo en srv_drugbank_cache (metadata, no PHI)

Cumplimiento Directiva del Director:

Regla 100 Registros (02-arquitectura-cliente-servidor.md 3.2.3.1)
Cache critico <100 CONTRAINDICADAS mas comunes
NO descarga masiva de catalogos
Busquedas online solo con consentimiento (Pro/Perfect)

DatabaseDrone - Doce de Quince "Los datos son el nucleo. LOCAL primero, SERVIDOR solo blobs." Iteracion IT-10 - Modelo de Interacciones Medicamentosas Version 2.0.0 - Cumple Regla 100 del Director

Modelo de Datos: Interacciones Medicamentosas¶

1. Resumen y Trazabilidad¶

1.1. Arquitectura Dual¶

1.2. Principios de Diseno¶

2. Clasificacion de Datos¶

2.1. Tabla de Clasificacion por Entidad¶

2.2. Justificacion de Clasificaciones¶

3. Esquema SERVIDOR (Catalogos y Logs)¶

3.1. srv_interaction_catalog¶

3.1.1. Columnas¶

3.1.2. Indices¶

3.1.3. Constraints¶

3.2. srv_drugbank_cache¶

3.2.1. Columnas¶

3.2.2. Indices¶

3.2.3. Politica de Expiracion¶

3.3. srv_ia_analysis_log¶

3.3.1. Columnas¶

3.3.2. Indices¶

3.3.3. Retencion¶

4. Esquema CLIENTE (Detecciones y Historial)¶

4.1. cli_critical_interactions_cache¶

4.1.1. Estructura (Conceptual)¶

4.1.2. SQLite (iOS/Android)¶

4.1.3. Realm (iOS)¶

4.1.4. Room (Android)¶

4.2. cli_detected_interactions¶

4.2.1. Estructura (Conceptual)¶

4.2.2. SQLite (iOS/Android)¶

4.2.3. Realm (iOS)¶

4.2.4. Room (Android)¶

4.3. cli_interaction_acknowledgments¶

4.3.1. Estructura (Conceptual)¶

4.3.2. SQLite (iOS/Android)¶

4.3.3. Realm (iOS)¶

4.3.4. Room (Android)¶

4.4. cli_interaction_history¶

4.4.1. Estructura (Conceptual)¶

4.4.2. SQLite (iOS/Android)¶

4.4.3. Realm (iOS)¶

4.4.4. Room (Android)¶

4.5. cli_interaction_consent¶

4.5.1. Estructura (Conceptual)¶

4.5.2. SQLite (iOS/Android)¶

4.5.3. Realm (iOS)¶

4.5.4. Room (Android)¶

4.6. cli_pending_verifications¶

4.6.1. Estructura (Conceptual)¶

4.6.2. SQLite (iOS/Android)¶

4.6.3. Realm (iOS)¶

4.6.4. Room (Android)¶

5. Mapeo de Sincronizacion¶

5.1. Cache Critico de Interacciones (Embebido en App)¶

5.2. Detecciones de Usuario (Bidireccional E2E)¶

5.3. Confirmaciones (Bidireccional E2E)¶

5.4. Historial (Bidireccional E2E)¶

5.5. Consentimiento de Busqueda (Bidireccional E2E)¶

6. Diagrama ER del Modulo¶

6.1. Servidor¶

6.2. Cliente¶

7. Consideraciones de Cifrado¶

7.1. Campos Cifrados E2E¶

7.2. Blind Indexes (No Aplica)¶

7.3. Proceso de Sincronizacion¶

8. Reglas de Validacion¶

8.1. Nivel Servidor¶

8.2. Nivel Cliente¶

9. Politicas RLS (Row Level Security)¶

9.1. srv_interaction_catalog (Sin RLS)¶

9.2. srv_drugbank_cache¶

9.3. srv_ia_analysis_log¶

10. Queries de Ejemplo¶

10.1. Buscar Interaccion CRITICA en Cache Local (<100)¶

10.2. Detectar Interacciones para Medicamentos del Usuario¶

10.3. Obtener Historial de Alertas (6 anos HIPAA)¶

10.4. Verificaciones Pendientes (Fallback)¶

10.5. Consultar Cache DrugBank (Servidor)¶

11. Migraciones¶

11.1. Migration 001: Create Interactions Schema (Servidor)¶

11.2. Migration 001: Create Interactions Schema (Cliente SQLite)¶