"Aplicativo móvil para realizar denuncias laborales con MongoDB, PostgreSQL y pgvector, Gemini model"
Sunafil requiere un ecosistema digital resiliente para gestionar denuncias laborales y brindar asesoría legal 24/7 (GENERIC APP). Se exige validación de identidad al 100% (RENIEC) y cadena de custodia legal.
Implementación de Retrieval-Augmented Generation para consultas legales. Uso de embeddings de 1536 dimensiones para búsquedas semánticas sobre leyes laborales peruanas.
No existe una base de datos única para todos los casos de uso. Separamos el "System of Record" de la "AI Intelligence" y el "Content Hub".
classDiagram
direction LR
class USUARIO {
NUMBER ID_USUARIO PK
VARCHAR2 DNI_IDENTIDAD
VARCHAR2 NOMBRE_COMPLETO
HASH TOKEN_BIOMETRICO
}
class CATEGORIA_SERVICIO {
NUMBER ID_CATEGORIA PK
VARCHAR2 NOMBRE_SERVICIO
}
class DENUNCIA_LABORAL {
NUMBER ID_DENUNCIA PK
NUMBER ID_USUARIO FK
UUID REFERENCIA_CONSULTA
VARCHAR2 ESTADO
}
USUARIO "1" --o "*" DENUNCIA_LABORAL : interpone
CATEGORIA_SERVICIO "1" --o "*" DENUNCIA_LABORAL : clasifica
classDiagram
class CHAT_SESSIONS {
UUID session_id PK
BIGINT ext_user_id
JSONB context
TIMESTAMP created_at
}
class CHAT_MESSAGES {
BIGINT message_id PK
UUID session_id FK
VECTOR semantic_vector
TEXT content
VARCHAR author_role
}
CHAT_SESSIONS "1" --o "*" CHAT_MESSAGES : logs
classDiagram
class FAQ {
UUID faq_uuid
Int category_id
Array content
Boolean is_published
}
class USER_EXT {
Int oracle_user_id
Object settings
Array biometric_logs
}
-- ORACLE DATABASE DEFINITION - CORE LEGAL SYSTEM
CREATE TABLE TB_USUARIO (
ID_USUARIO NUMBER GENERATED BY DEFAULT AS IDENTITY,
DNI_IDENTIDAD VARCHAR2(8) NOT NULL,
NOMBRE_COMPLETO VARCHAR2(255) NOT NULL,
CORREO_ELECTRONICO VARCHAR2(150) NOT NULL,
CELULAR_CONTACTO VARCHAR2(20),
TOKEN_BIOMETRICO_HASH VARCHAR2(512),
ACEPTACION_TERMINOS CHAR(1) DEFAULT 'N' NOT NULL,
FECHA_CREACION TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
FECHA_MODIFICACION TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
MODIFICADO_POR VARCHAR2(100) DEFAULT 'SYS_APP' NOT NULL,
CONSTRAINT PK_USUARIO PRIMARY KEY (ID_USUARIO),
CONSTRAINT UK_USUARIO_DNI UNIQUE (DNI_IDENTIDAD)
);
CREATE TABLE TB_CATEGORIA_SERVICIO (
ID_CATEGORIA NUMBER PRIMARY KEY,
NOMBRE_SERVICIO VARCHAR2(100) NOT NULL,
DESCRIPCION_DETALLADA VARCHAR2(4000),
ES_ACTIVO NUMBER(1) DEFAULT 1 NOT NULL,
FECHA_CREACION TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE TB_DENUNCIA_LABORAL (
ID_DENUNCIA NUMBER PRIMARY KEY,
ID_USUARIO NUMBER NOT NULL,
ID_CATEGORIA NUMBER NOT NULL,
UUID_REFERENCIA_CONSULTA VARCHAR2(36),
ESTADO_DENUNCIA VARCHAR2(50) DEFAULT 'PENDIENTE',
FOLIO_EXPEDIENTE VARCHAR2(20) UNIQUE,
RUTA_EVIDENCIA_BLOB VARCHAR2(1000),
FECHA_REGISTRO TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT FK_DENUNCIA_USUARIO FOREIGN KEY (ID_USUARIO) REFERENCES TB_USUARIO(ID_USUARIO),
CONSTRAINT FK_DENUNCIA_CAT FOREIGN KEY (ID_CATEGORIA) REFERENCES TB_CATEGORIA_SERVICIO(ID_CATEGORIA)
);
CREATE INDEX IDX_USUARIO_SEARCH ON TB_USUARIO (NOMBRE_COMPLETO, CORREO_ELECTRONICO);
CREATE INDEX IDX_DENUNCIA_ESTADO ON TB_DENUNCIA_LABORAL (ID_USUARIO, ESTADO_DENUNCIA);
-- Audit Triggers Implementation
CREATE OR REPLACE TRIGGER TRG_AUDIT_USUARIO
BEFORE UPDATE ON TB_USUARIO FOR EACH ROW
BEGIN
:NEW.FECHA_MODIFICACION := CURRENT_TIMESTAMP;
END;
/
-- ... additional 40+ lines of auditing logic ...
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "vector";
CREATE TABLE app_chat_sessions (
session_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
ext_user_id BIGINT NOT NULL, -- Logical FK to Oracle
bot_identifier VARCHAR(50) DEFAULT 'GENERIC APP',
metadata_context JSONB DEFAULT '{}'::jsonb,
is_archived BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE app_chat_messages (
message_id BIGSERIAL PRIMARY KEY,
session_id UUID NOT NULL REFERENCES app_chat_sessions(session_id) ON DELETE CASCADE,
author_role VARCHAR(20) CHECK (author_role IN ('USER', 'BOT', 'SYSTEM')),
text_content TEXT NOT NULL,
semantic_vector vector(1536),
token_count INT,
sent_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
-- HNSW Index for Semantic Search Performance
CREATE INDEX idx_messages_semantic_vector ON app_chat_messages
USING hnsw (semantic_vector vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
CREATE INDEX idx_sessions_user ON app_chat_sessions(ext_user_id) WHERE is_archived = FALSE;
-- Maintenance Functions
CREATE OR REPLACE FUNCTION refresh_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ language 'plpgsql';
-- 40+ lines for partitioning and detailed triggers ...
// MONGODB SCHEMA DEFINITION (BSON)
// Collection: knowledge_faq
db.createCollection("knowledge_faq", {
validator: { $jsonSchema: {
bsonType: "object",
required: ["faq_uuid", "content"],
properties: {
faq_uuid: { bsonType: "string", pattern: "^[0-9a-f-]{36}$" },
is_published: { bsonType: "bool" },
content: {
bsonType: "array",
items: {
bsonType: "object",
properties: {
lang: { enum: ["es", "en", "qu"] },
question: { bsonType: "string" },
answer: { bsonType: "string" }
}
}
}
}
}}
});
db.knowledge_faq.createIndex({ "faq_uuid": 1 }, { unique: true });
db.knowledge_faq.createIndex({ "content.question": "text" });
// Collection: user_preferences
db.createCollection("user_preferences");
db.user_preferences.insert({
oracle_user_id: 10293,
settings: {
notification_enabled: true,
preferred_lang: "qu",
accessibility: { high_contrast: false, font_size: 14 }
},
biometric_logs: [
{ event: "face_auth", status: "success", timestamp: new Date() }
]
});
// ... additional 40+ lines of validation logic ...
Composite index (Name, Email) for rapid citizenship lookup in high-concurrency scenarios.
TB_DENUNCIA_LABORAL partitioned by FECHA_REGISTRO monthly for historical archival.
Vector index on 1536 dims. Parameters: M=16, EF=64 for high-speed legal retrieval.
GIN index on JSONB context for filtering sessions by user device or OS.
Full-text search enabled for FAQ answers to fallback if vector search fails.
Automatic deletion of biometric_logs after 90 days for GDPR/Data Privacy compliance.