Diagrama de Base de Datos - Sherlock-docs¶

Descripción¶

Diagrama entidad-relación (ER) del esquema SQLite con soporte FTS5 para búsqueda full-text. Incluye tablas principales, índices, triggers y tabla virtual FTS5.

Motor: SQLite 3.9+ con extensión FTS5 Modo: WAL (Write-Ahead Logging) para mejor concurrencia

Diagrama ER¶

erDiagram
    documents {
        TEXT id PK "UUID v4"
        TEXT file_path "Ruta absoluta al PDF"
        TEXT file_name "Nombre del archivo"
        TEXT document_type "tutela|habeas_corpus|unknown"
        TEXT status "pending|processing|processed|validated|error"
        TEXT content "Texto extraído por OCR o nativo"
        REAL ocr_confidence "0.0-1.0 confianza OCR"
        TEXT file_hash "SHA-256 binario del PDF"
        INTEGER file_size_bytes "Tamaño del PDF en bytes"
        TEXT demandante "Entidad extraída por NER"
        TEXT demandado "Entidad extraída por NER"
        TEXT radicado "Número de radicado"
        TEXT juzgado "Juzgado asignado"
        TEXT fecha_documento "Fecha del documento"
        TEXT correo "Email extraído por NER"
        TEXT cedula "CC/CE/NIT extraído por NER"
        TEXT hora_radicacion "HH:MM:SS auto-generado"
        TEXT numero_acta "~7 dígitos manual"
        TEXT observaciones "Texto libre notas"
        TEXT etiquetas "JSON array tags"
        TEXT direccion_demandante "Dirección NER+manual"
        TEXT direccion_demandado "Dirección NER+manual"
        TEXT secuencia_anterior "Referencia doc previo"
        TEXT coordenada "Coordenada geográfica"
        INTEGER has_provisional_measure "1 si medida provisional"
        TEXT provisional_keywords "JSON keywords detectadas"
        INTEGER has_hechos "1 si sección hechos detectada"
        INTEGER has_pretensiones "1 si sección pretensiones detectada"
        INTEGER is_manual_entry "1 si ingreso manual"
        TEXT processing_cancelled_at "Timestamp si cancelado"
        TEXT imported_at "Timestamp de importación Excel"
        TEXT created_at "ISO 8601 timestamp"
        TEXT updated_at "ISO 8601 timestamp"
    }

    corrections {
        INTEGER id PK "AUTOINCREMENT"
        TEXT document_id FK "Referencias documents.id"
        TEXT field_name "Campo corregido"
        TEXT original_value "Valor original NER"
        TEXT corrected_value "Valor corregido usuario"
        TEXT corrected_by "username autenticado (GOB-05)"
        TEXT created_at "ISO 8601 timestamp"
    }

    processing_stats {
        INTEGER id PK "AUTOINCREMENT"
        TEXT document_id FK "Referencias documents.id"
        TEXT ocr_engine "tesseract|paddleocr|native"
        REAL ocr_time_ms "Tiempo OCR en ms"
        REAL ner_time_ms "Tiempo NER en ms"
        REAL dedup_time_ms "Tiempo dedup en ms"
        REAL total_time_ms "Tiempo total pipeline"
        INTEGER pages_processed "Páginas procesadas"
        INTEGER file_size_bytes "Tamaño del PDF"
        TEXT spacy_model_version "Versión modelo SpaCy"
        TEXT ocr_engine_version "Versión motor OCR"
        TEXT dedup_config_snapshot "JSON config dedup"
        TEXT created_at "ISO 8601 timestamp"
    }

    documents_fts {
        TEXT content "Texto indexado FTS5"
        TEXT demandante "Indexado FTS5"
        TEXT demandado "Indexado FTS5"
        TEXT radicado "Indexado FTS5"
        TEXT juzgado "Indexado FTS5"
        TEXT cedula "Indexado FTS5"
        TEXT correo "Indexado FTS5"
        TEXT numero_acta "Indexado FTS5"
        TEXT observaciones "Indexado FTS5"
    }

    logs {
        INTEGER id PK "AUTOINCREMENT"
        TEXT timestamp "ISO 8601 del evento"
        TEXT level "DEBUG|INFO|WARNING|ERROR|CRITICAL"
        INTEGER level_no "Nivel numérico (10-50)"
        TEXT logger_name "Nombre del logger"
        TEXT module "Módulo Python"
        TEXT func_name "Función origen"
        INTEGER line_no "Línea de código"
        TEXT message "Mensaje de log"
        TEXT exception "Stack trace si aplica"
        INTEGER process_id "PID del proceso"
        INTEGER thread_id "ID del thread"
        TEXT thread_name "Nombre del thread"
        TEXT created_at "ISO 8601 de inserción"
    }

    audit_events {
        INTEGER id PK "AUTOINCREMENT"
        TEXT user_id "Usuario que ejecutó la acción"
        TEXT action "document_processed|document_corrected|etc"
        TEXT resource_type "document|import|export|session"
        TEXT resource_id "ID del recurso afectado"
        TEXT details "JSON detalles adicionales"
        TEXT ip_address "IP del cliente"
        TEXT created_at "ISO 8601 timestamp"
    }

    documents ||--o{ corrections : "tiene (ON DELETE CASCADE)"
    documents ||--o| processing_stats : "tiene (ON DELETE CASCADE)"
    documents ||--|| documents_fts : "sincroniza via triggers"
    documents ||--o{ audit_events : "genera"

Tablas Principales¶

1. documents - Tabla Principal¶

Almacena todos los documentos legales procesados.

33 columnas (21 originales + 12 agregadas en Sprints 8-19)

Índices (6): - idx_documents_hash en file_hash — Búsqueda rápida de duplicados (gate pre-OCR) - idx_documents_status en status — Filtrado por estado - idx_documents_type en document_type — Filtrado por tipo - idx_documents_radicado en radicado — Búsqueda por radicado (import, CLI, GUI) - idx_documents_cedula en cedula — Búsqueda por cédula (historial accionante) - idx_documents_correo en correo — Búsqueda por correo (historial accionante)

2. documents_fts - Tabla Virtual FTS5¶

Tabla virtual para búsqueda full-text usando FTS5.

Nota: direccion_demandante y direccion_demandado se almacenan en BD pero no se indexan en FTS5 (no son criterio de búsqueda útil).

Configuración FTS5 (9 campos):

CREATE VIRTUAL TABLE documents_fts USING fts5(
    content,
    demandante,
    demandado,
    radicado,
    juzgado,
    cedula,
    correo,
    numero_acta,
    observaciones,
    content='documents',
    content_rowid='rowid'
);

Sincronización: Mediante triggers automáticos (INSERT, UPDATE, DELETE).

3. corrections - Correcciones de Usuario¶

Almacena el historial de correcciones para feedback loop y mejora del NER.

Índice: idx_corrections_document en document_id

Uso: Calcular accuracy del NER por campo, generar datos de entrenamiento.

4. processing_stats - Estadísticas de Procesamiento¶

Métricas de rendimiento del pipeline de procesamiento.

Índice: idx_processing_stats_document en document_id

FK: document_id → documents.id ON DELETE CASCADE

5. logs - Logs de Aplicación¶

Almacena logs de la aplicación para auditoría, debugging y análisis.

Índices: - idx_logs_level en level - Filtrado por severidad - idx_logs_timestamp en timestamp - Búsqueda temporal - idx_logs_logger en logger_name - Filtrado por componente

Uso: Auditoría, debugging, análisis de errores, monitoreo de rendimiento.

6. audit_events - Eventos de Auditoría (GOB-06)¶

Registra acciones de usuario para trazabilidad y cumplimiento de gobernanza IA.

Índices (4): - idx_audit_events_user en user_id — Filtrado por usuario - idx_audit_events_action en action — Filtrado por tipo de acción - idx_audit_events_resource en resource_type, resource_id — Búsqueda por recurso - idx_audit_events_created en created_at — Búsqueda temporal

Uso: Trazabilidad GOB-06, reportes de auditoría GOB-11 (CLI audit-report), cumplimiento gobernanza IA.

Triggers de Sincronización FTS5¶

-- INSERT: Sincroniza nuevos documentos al índice FTS5
CREATE TRIGGER documents_ai AFTER INSERT ON documents BEGIN
    INSERT INTO documents_fts(
        rowid, content, demandante, demandado, radicado, juzgado,
        cedula, correo, numero_acta, observaciones
    )
    VALUES (
        NEW.rowid, NEW.content, NEW.demandante, NEW.demandado, NEW.radicado, NEW.juzgado,
        NEW.cedula, NEW.correo, NEW.numero_acta, NEW.observaciones
    );
END;

-- DELETE: Elimina del índice FTS5
CREATE TRIGGER documents_ad AFTER DELETE ON documents BEGIN
    INSERT INTO documents_fts(
        documents_fts, rowid, content, demandante, demandado, radicado, juzgado,
        cedula, correo, numero_acta, observaciones
    )
    VALUES (
        'delete', OLD.rowid, OLD.content, OLD.demandante, OLD.demandado, OLD.radicado, OLD.juzgado,
        OLD.cedula, OLD.correo, OLD.numero_acta, OLD.observaciones
    );
END;

-- UPDATE: Actualiza el índice FTS5 (delete + insert)
CREATE TRIGGER documents_au AFTER UPDATE ON documents BEGIN
    INSERT INTO documents_fts(
        documents_fts, rowid, content, demandante, demandado, radicado, juzgado,
        cedula, correo, numero_acta, observaciones
    )
    VALUES (
        'delete', OLD.rowid, OLD.content, OLD.demandante, OLD.demandado, OLD.radicado, OLD.juzgado,
        OLD.cedula, OLD.correo, OLD.numero_acta, OLD.observaciones
    );
    INSERT INTO documents_fts(
        rowid, content, demandante, demandado, radicado, juzgado,
        cedula, correo, numero_acta, observaciones
    )
    VALUES (
        NEW.rowid, NEW.content, NEW.demandante, NEW.demandado, NEW.radicado, NEW.juzgado,
        NEW.cedula, NEW.correo, NEW.numero_acta, NEW.observaciones
    );
END;

Campos corregibles: demandante, demandado, radicado, juzgado, fecha_documento, numero_acta, observaciones, etiquetas, direccion_demandante, direccion_demandado

Diagrama de Flujo de Datos¶

flowchart LR
    subgraph Input["📄 Entrada"]
        PDF[PDF Document]
    end

    subgraph Pipeline["⚙️ Pipeline"]
        OCR[OCR Engine]
        NER[NER Extractor]
        Hash[SHA-256 Hash]
    end

    subgraph Storage["💾 Almacenamiento"]
        Documents[(documents)]
        FTS[(documents_fts)]
        Stats[(processing_stats)]
        Logs[(logs)]
        AuditEvents[(audit_events)]
    end

    subgraph Feedback["🔄 Feedback Loop"]
        User[Usuario]
        Corrections[(corrections)]
    end

    subgraph Logging["📋 Logging"]
        Logger[Logger Service]
    end

    PDF --> OCR
    OCR --> NER
    OCR --> Hash
    NER --> Documents
    Hash --> Documents

    Documents -->|Triggers| FTS
    OCR -->|Métricas| Stats
    NER -->|Métricas| Stats

    Documents --> User
    User -->|Corrección| Corrections
    Corrections -->|Actualiza| Documents

    OCR -.->|Logs| Logger
    NER -.->|Logs| Logger
    Logger -->|Persiste| Logs

    NER -->|Audit trail| AuditEvents
    User -->|Corrección audit| AuditEvents

    style Documents fill:#e8f5e9,stroke:#2e7d32
    style FTS fill:#e3f2fd,stroke:#1565c0
    style Corrections fill:#fff3e0,stroke:#ef6c00
    style Stats fill:#f3e5f5,stroke:#7b1fa2
    style Logs fill:#fce4ec,stroke:#c2185b
    style AuditEvents fill:#fff8e1,stroke:#f9a825

Consultas Comunes¶

Búsqueda Full-Text (FTS5)¶

-- Búsqueda en todos los campos indexados
SELECT d.* FROM documents d
JOIN documents_fts fts ON d.rowid = fts.rowid
WHERE documents_fts MATCH 'término de búsqueda'
ORDER BY rank
LIMIT 10;

Búsqueda por Hash (Deduplicación)¶

-- Encontrar documento duplicado por hash exacto
SELECT * FROM documents WHERE file_hash = ?;

Estadísticas de Correcciones¶

-- Correcciones por campo
SELECT field_name, COUNT(*) as count
FROM corrections
GROUP BY field_name;

-- Accuracy estimada por campo
SELECT field_name,
       1.0 - (COUNT(*) * 1.0 / (SELECT COUNT(*) FROM documents)) as accuracy
FROM corrections
GROUP BY field_name;

Documentos por Estado¶

-- Conteo por estado
SELECT status, COUNT(*) as count
FROM documents
GROUP BY status;

Consultas de Logs¶

-- Logs de errores recientes
SELECT * FROM logs
WHERE level IN ('ERROR', 'CRITICAL')
ORDER BY timestamp DESC
LIMIT 50;

-- Logs por componente
SELECT * FROM logs
WHERE logger_name LIKE 'SherlockDocs.ocr%'
ORDER BY timestamp DESC;

-- Conteo de errores por día
SELECT DATE(timestamp) as day, COUNT(*) as error_count
FROM logs
WHERE level = 'ERROR'
GROUP BY DATE(timestamp)
ORDER BY day DESC;

Consideraciones de Rendimiento¶

Migración a PostgreSQL (Futuro)¶

El esquema está diseñado para facilitar migración:

Última actualización: 2026-03-06 (GOB-05 auth + GOB-06 audit_events + GOB-07 cols + GOB-11 audit-report)