Diseño de Base de Datos — GNL CRM

Documento técnico de referencia — Describe el esquema completo, las relaciones entre tablas, los índices, la generación de códigos, el sistema de migraciones, la importación masiva desde Excel y la API REST expuesta por el servidor.

1. Arquitectura General

GNL CRM es una aplicación web de una sola página (Single-Page Application) con arquitectura de tres capas:

• Capa de datos: SQLite 3, fichero único crm.db con modo WAL y claves foráneas activas.
• Capa de servidor: Node.js + Express.js v5 que expone una API REST JSON y sirve los ficheros estáticos del frontend.
• Capa de presentación: HTML5 + CSS3 + JavaScript Vanilla (sin frameworks de frontend), con un único punto de entrada index.html.

Tecnologías y versiones

Modo WAL y claves foráneas

Al iniciar la aplicación, database.js ejecuta los siguientes pragmas antes de cualquier otra operación:

db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

• WAL (Write-Ahead Logging): permite lecturas concurrentes mientras se realiza una escritura, mejorando el rendimiento en entornos con múltiples peticiones HTTP simultáneas.
• foreign_keys = ON: SQLite no activa las claves foráneas por defecto; este pragma es indispensable para que las restricciones de integridad referencial se apliquen. Debe activarse en cada conexión que abra el fichero.

2. Modelo Entidad-Relación

El modelo de datos gira en torno a la entidad central Entidades (empresas / contrapartes). El resto de tablas de negocio dependen de ella mediante claves foráneas. La tabla Pais actúa como catálogo geográfico referenciado desde Entidades. La tabla Usuarios es completamente independiente y gestiona el control de acceso.

Cardinalidades

3. Descripción Detallada de Cada Tabla

Leyenda de etiquetas:

• PK — Clave primaria
• FK — Clave foránea
• IDX — Columna con índice adicional

3a. Tabla Pais

Catálogo de países con información geopolítica y comercial. Actúa como tabla de referencia (lookup table) para la columna CodigoPaisNormalizado de Entidades. La clave primaria es el código ISO 3166-1 alpha-3 (tres letras), que garantiza unicidad y compatibilidad internacional.

CREATE TABLE IF NOT EXISTS Pais (
  CodigoPaisNormalizado    TEXT PRIMARY KEY,
  Nombre                   TEXT,
  Region                   TEXT,
  ReferenciaIndice         TEXT,
  LinkFichaPais            TEXT,
  PersonaReferenciaOportun TEXT,
  Comentarios              TEXT
);

3b. Tabla Entidades

Tabla central del CRM. Representa empresas, grupos empresariales o contrapartes con las que GNL trabaja o pretende trabajar. Cada entidad tiene un código único de formato libre asignado manualmente (ej. ENT-00001). Todas las tablas de detalle (Contactos, Oportunidades, Documentos) apuntan a esta tabla.

CREATE TABLE IF NOT EXISTS Entidades (
  CodigoEntidad         TEXT PRIMARY KEY,
  Compania              TEXT NOT NULL,
  Region                TEXT,
  Tipo                  TEXT,
  CodigoPaisNormalizado TEXT REFERENCES Pais(CodigoPaisNormalizado),
  FiscalCode            TEXT,
  LEI                   TEXT,
  Ticker                TEXT,
  DunsNumber            TEXT,
  Direccion             TEXT,
  Comentarios           TEXT
);

3c. Tabla Contactos

Personas de contacto vinculadas a una entidad. Cada contacto recibe un código correlativo auto-generado con formato CON-00001. Una entidad puede tener múltiples contactos. La columna ProbabilidadExito permite priorizar el seguimiento comercial.

CREATE TABLE IF NOT EXISTS Contactos (
  id                    INTEGER PRIMARY KEY AUTOINCREMENT,
  CodigoContacto        TEXT,
  CodigoEntidad         TEXT NOT NULL REFERENCES Entidades(CodigoEntidad),
  Nombre                TEXT,
  Cargo                 TEXT,
  Email                 TEXT,
  Telefono1             TEXT,
  Telefono2             TEXT,
  Via                   TEXT,
  FechaUltimoContacto   TEXT,
  DemorarContactoAfecha TEXT,
  ProbabilidadExito     TEXT,
  Linkedin              TEXT,
  Comentarios           TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_contactos_codigo
  ON Contactos(CodigoContacto);

3d. Tabla Oportunidades

Registra oportunidades comerciales de compra/venta de GNL asociadas a una entidad. Cada oportunidad recibe un código correlativo auto-generado OPO-00001. Contiene información operativa y comercial específica del negocio de gas natural licuado.

CREATE TABLE IF NOT EXISTS Oportunidades (
  id                        INTEGER PRIMARY KEY AUTOINCREMENT,
  CodigoOportunidad         TEXT,
  CodigoEntidad             TEXT NOT NULL REFERENCES Entidades(CodigoEntidad),
  Contraparte               TEXT,
  OwnerAccount              TEXT,
  Entrega                   TEXT,
  Periodo                   TEXT,
  Volumen                   TEXT,
  Precio                    TEXT,
  SpecsContrapartePCS       TEXT,
  ProximosPasosNTGY         TEXT,
  ProximosPasosContraparte  TEXT,
  Timing                    TEXT,
  Origen                    TEXT,
  Comentarios               TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_oportunidades_codigo
  ON Oportunidades(CodigoOportunidad);

3e. Tabla Documentos

Registro del estado documental y de cumplimiento (compliance) de cada entidad. Almacena si existen los acuerdos legales fundamentales (KYC, NDA, MSPA) y enlaces a los mismos. Una entidad puede tener múltiples registros de documentos, aunque lo habitual es uno por entidad.

CREATE TABLE IF NOT EXISTS Documentos (
  id                 INTEGER PRIMARY KEY AUTOINCREMENT,
  CodigoDocumento    TEXT,
  CodigoEntidad      TEXT NOT NULL REFERENCES Entidades(CodigoEntidad),
  KYC_S_N            TEXT,
  KYC_link           TEXT,
  NDA_S_N            TEXT,
  FechaExpiracionNDA TEXT,
  NDALink            TEXT,
  MSPASN             TEXT,
  LinkMSPA           TEXT,
  Comentarios        TEXT
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_documentos_codigo
  ON Documentos(CodigoDocumento);

3f. Tabla Usuarios

Tabla de control de acceso. Completamente independiente del modelo de negocio. Los usuarios se autentican contra esta tabla; las contraseñas deben almacenarse hasheadas (nunca en texto plano). El campo rol determina los permisos de la sesión.

CREATE TABLE IF NOT EXISTS Usuarios (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  nombre   TEXT,
  rol      TEXT NOT NULL DEFAULT 'user'
);

4. Índices y Rendimiento

SQLite crea automáticamente un índice B-tree para cada PRIMARY KEY y para cada columna declarada como UNIQUE. Adicionalmente, la aplicación define los siguientes índices explícitos:

Índices implícitos (creados por SQLite automáticamente)

Consideraciones de rendimiento con SQLite en WAL

• Lecturas concurrentes: el modo WAL permite que múltiples lectores accedan simultáneamente mientras hay una escritura en curso, lo que es adecuado para un CRM con pocos usuarios concurrentes.
• Escrituras serializadas: SQLite solo permite una escritura a la vez. Para un volumen de usuarios pequeño-medio (hasta ~50 concurrentes), esto no es un cuello de botella significativo.
• Sin índices en FK: las columnas CodigoEntidad en las tablas hijas no tienen índice explícito. Si la aplicación crece en volumen de datos, puede mejorar el rendimiento de las consultas de detalle de entidad añadiendo índices como CREATE INDEX idx_contactos_entidad ON Contactos(CodigoEntidad).
• Tamaño de la BD: para el volumen de datos esperado en un CRM comercial de GNL (cientos de entidades, miles de contactos), el rendimiento de SQLite es más que suficiente sin configuración adicional.

5. Códigos Auto-Generados (CON / OPO / DOC)

Tres tablas usan un identificador de negocio legible por humanos además del id numérico interno: Contactos (CON-NNNNN), Oportunidades (OPO-NNNNN) y Documentos (DOC-NNNNN).

Algoritmo de generación

La lógica reside en database.js y se ejecuta en el mismo proceso Node.js (síncrono, gracias a better-sqlite3) justo antes de cada INSERT. El algoritmo es:

1. Obtener el código máximo existente en la tabla con una consulta SELECT MAX(CodigoContacto) (o equivalente para OPO/DOC).
2. Extraer el número del código (los últimos 5 dígitos tras el guion).
3. Incrementar en 1.
4. Formatear con ceros a la izquierda hasta 5 dígitos.
5. Componer el nuevo código: prefijo + '-' + número formateado.

// Ejemplo conceptual en database.js (Contactos)
function generateCodigoContacto() {
  const row = db.prepare(
    "SELECT MAX(CodigoContacto) as max FROM Contactos"
  ).get();

  if (!row.max) return 'CON-00001';

  // Extraer numero: 'CON-00042' -> 42
  const num = parseInt(row.max.split('-')[1], 10);
  const next = num + 1;

  // Formatear a 5 digitos: 43 -> '00043'
  return 'CON-' + String(next).padStart(5, '0');
}

Características del formato

Si el número de registros superara los 99.999, sería suficiente ampliar el padStart(5, '0') a padStart(6, '0') o más, sin cambiar el esquema de la base de datos.

6. Migraciones de Esquema

La aplicación no usa un framework de migraciones externo (como Flyway o Liquibase). En su lugar, database.js implementa una función runMigrations() que se ejecuta al arrancar el servidor, después de la creación inicial de tablas.

Estrategia de migración

Cada migración consiste en un ALTER TABLE ... ADD COLUMN ... envuelto en un bloque try/catch. Si la columna ya existe, SQLite lanza un error que se captura y se ignora silenciosamente. Esto hace que las migraciones sean idempotentes: se pueden ejecutar múltiples veces sin efectos secundarios.

function runMigrations() {
  const migrations = [
    `ALTER TABLE Entidades ADD COLUMN DunsNumber TEXT`,
    `ALTER TABLE Contactos ADD COLUMN DemorarContactoAfecha TEXT`,
    `ALTER TABLE Oportunidades ADD COLUMN SpecsContrapartePCS TEXT`,
  ];

  for (const sql of migrations) {
    try {
      db.prepare(sql).run();
      console.log('[DB] Migration OK:', sql);
    } catch (e) {
      // Column already exists -- safe to ignore
      if (!e.message.includes('duplicate column name')) {
        console.warn('[DB] Migration warning:', e.message);
      }
    }
  }
}

Flujo de arranque de la base de datos

1. Abrir la conexión: new Database('crm.db')
2. Activar pragmas: WAL + foreign_keys = ON
3. Crear tablas: sentencias CREATE TABLE IF NOT EXISTS para las 6 tablas
4. Crear índices: sentencias CREATE UNIQUE INDEX IF NOT EXISTS
5. Ejecutar migraciones: runMigrations() — añade columnas nuevas si no existen
6. Crear usuario admin por defecto: si la tabla Usuarios está vacía, inserta un usuario inicial

Cómo añadir una nueva columna

1. Añadir la sentencia ALTER TABLE NombreTabla ADD COLUMN NuevaColumna TEXT al array migrations en database.js.
2. Actualizar los helpers CRUD de database.js para incluir la nueva columna.
3. Actualizar los formularios HTML en index.html y la lógica de formularios en app.js.
4. Reiniciar la aplicación. La migración se aplicará automáticamente en el arranque.

7. Importación Masiva desde Excel

El endpoint POST /api/import permite cargar datos masivamente desde un fichero Excel (.xlsx). La lógica de importación reside en database.js (función bulkImport) y el servidor usa multer para la recepción del fichero.

Flujo de importación

1. El usuario selecciona un fichero .xlsx en la interfaz y hace clic en «Importar».
2. El frontend envía el fichero al servidor mediante POST /api/import con multipart/form-data.
3. Multer guarda el fichero temporalmente en la carpeta /uploads.
4. El servidor llama a xlsx.readFile() para parsear el Excel.
5. Se itera sobre las hojas del fichero Excel, cada una correspondiente a una tabla.
6. La función bulkImport() procesa los datos y ejecuta las operaciones de base de datos.
7. El fichero temporal en /uploads se elimina automáticamente al finalizar la importación.
8. El servidor devuelve un resumen de las operaciones realizadas (insertados, actualizados, errores).

Estrategia UPSERT por tipo de tabla

Implementación del UPSERT para tablas padre

SQLite soporta la sintaxis INSERT OR REPLACE. La implementación usa esta forma:

// UPSERT para Pais
const upsertPais = db.prepare(`
  INSERT OR REPLACE INTO Pais
    (CodigoPaisNormalizado, Nombre, Region, ReferenciaIndice,
     LinkFichaPais, PersonaReferenciaOportun, Comentarios)
  VALUES
    (@CodigoPaisNormalizado, @Nombre, @Region, @ReferenciaIndice,
     @LinkFichaPais, @PersonaReferenciaOportun, @Comentarios)
`);

// UPSERT para Entidades
const upsertEntidad = db.prepare(`
  INSERT OR REPLACE INTO Entidades
    (CodigoEntidad, Compania, Region, Tipo, CodigoPaisNormalizado,
     FiscalCode, LEI, Ticker, DunsNumber, Direccion, Comentarios)
  VALUES
    (@CodigoEntidad, @Compania, @Region, @Tipo, @CodigoPaisNormalizado,
     @FiscalCode, @LEI, @Ticker, @DunsNumber, @Direccion, @Comentarios)
`);

Estructura esperada del fichero Excel

El fichero Excel debe tener una hoja por tabla, con los nombres de columna exactos en la primera fila (cabecera). El orden recomendado de hojas es:

1. Pais — con columna CodigoPaisNormalizado como primera columna.
2. Entidades — con columna CodigoEntidad como primera columna.
3. Contactos — con columna CodigoEntidad (referencia a entidad existente).
4. Oportunidades — con columna CodigoEntidad.
5. Documentos — con columna CodigoEntidad.

Carpeta /uploads

Multer usa la carpeta /uploads como almacenamiento temporal. El fichero se elimina inmediatamente tras ser procesado mediante fs.unlinkSync(). La carpeta debe existir en el servidor y tener permisos de escritura para el proceso Node.js. No se almacenan ficheros de datos del usuario de forma permanente en el servidor.

8. API REST — Tabla de Endpoints

Todos los endpoints (salvo POST /api/login) requieren sesión activa. Los marcados como Admin requieren adicionalmente rol = 'admin'. Las respuestas son siempre JSON.

Autenticación y sesión

Dashboard y búsqueda

Países

Entidades

Contactos

Oportunidades

Documentos

Usuarios (requiere rol admin)

Perfil propio e importación

9. Estructura de Ficheros del Proyecto

/CRM
├── server.js                  # Servidor Express.js: rutas API, middleware auth,
│                              # gestion de sesiones, multer para uploads
├── database.js                # Modulo SQLite: inicializacion del esquema,
│                              # runMigrations(), helpers CRUD, generacion de
│                              # codigos, bulkImport(), procesador AI
├── crm.db                     # Fichero de base de datos SQLite (WAL mode)
│                              # Contiene todos los datos. Se genera en el
│                              # primer arranque si no existe.
├── package.json               # Dependencias Node.js y scripts de arranque
├── GUIA_IMPLEMENTACION.md     # Documentacion de despliegue para IT
│
├── /public                    # Ficheros estaticos servidos por Express
│   ├── index.html             # SPA shell: estructura HTML, sidebar de navegacion,
│   │                          # todos los divs de vista, modal generico
│   ├── /js
│   │   └── app.js             # Toda la logica frontend: autenticacion, navegacion,
│   │                          # operaciones CRUD, mapa Leaflet, chat IA, formularios,
│   │                          # importacion Excel, gestion de toasts y modales
│   ├── /css
│   │   └── style.css          # Estilos completos: sidebar, tablas, modales,
│   │                          # toasts, mapa, formularios, responsive
│   ├── guia.html              # Guia de implementacion IT (documento estatico)
│   ├── guia-usuario.html      # Manual de usuario (documento estatico)
│   ├── diseno-bbdd.html       # Este documento: diseno de BD (documento estatico)
│   └── presentacion.html      # Presentacion del proyecto (documento estatico)
│
└── /uploads                   # Carpeta temporal para Excel importados
                               # Los ficheros se eliminan tras cada importacion.

Descripción de los ficheros principales

server.js

Punto de entrada de la aplicación. Define y registra todos los middlewares de Express (sesión, JSON parser, ficheros estáticos, multer) y todas las rutas de la API REST. Contiene los middlewares requireAuth y requireAdmin que protegen las rutas privadas. Llama a las funciones de database.js para las operaciones de datos.

database.js

Módulo central de acceso a datos. Abre la conexión SQLite, inicializa el esquema, ejecuta las migraciones y exporta funciones CRUD para cada tabla. También contiene la función bulkImport() para la importación desde Excel y el procesador de consultas en lenguaje natural para el chat IA.

crm.db

Fichero binario de SQLite. Es la base de datos completa de la aplicación. Se genera automáticamente en el primer arranque. En modo WAL, SQLite puede crear también los ficheros auxiliares crm.db-wal y crm.db-shm durante las operaciones de escritura; estos ficheros se consolidan en el .db principal cuando se cierra correctamente la conexión.

public/js/app.js

Fichero JavaScript de frontend más importante. Contiene toda la lógica del cliente: gestión del estado de autenticación, navegación entre vistas (sin router), llamadas fetch() a la API, renderizado dinámico de tablas, gestión del formulario modal, inicialización del mapa Leaflet, interfaz del chat IA y lógica de importación Excel.

10. Consideraciones para Reimplementación

Esta sección recoge los aspectos críticos a tener en cuenta si se desea portar la aplicación a otro lenguaje de programación, framework web o sistema de base de datos.

10.1 Modo WAL de SQLite

Si se mantiene SQLite como motor de base de datos, el pragma journal_mode = WAL debe activarse en cada apertura de conexión. En frameworks que usan connection pooling, esto implica ejecutar el pragma en el evento de creación de cada conexión del pool (hook on_connect o equivalente), no una sola vez al arrancar el servidor.

10.2 Activación de Claves Foráneas en SQLite

SQLite no activa las claves foráneas por defecto por compatibilidad hacia atrás. El pragma PRAGMA foreign_keys = ON es obligatorio en cada conexión. Sin él, los REFERENCES declarados en el esquema son ignorados silenciosamente y es posible insertar datos huérfanos. En PostgreSQL y MySQL, las claves foráneas están activas por defecto.

-- Siempre ejecutar al abrir la conexion SQLite:
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

10.3 Lógica de UPSERT en la Importación Masiva

La sintaxis exacta del UPSERT varía según el motor de base de datos:

10.4 Generación de Códigos Correlativos (CON / OPO / DOC)

La implementación actual usa SELECT MAX(CodigoCampo) + incremento sincrónico. Esto funciona porque better-sqlite3 es síncrono y Node.js es de un único hilo. Al reimplementar en un entorno con drivers asíncronos o múltiples hilos/procesos, se necesita alguna de estas alternativas:

• Transacción explícita: envolver el SELECT MAX + INSERT en una transacción serializable.
• Secuencias de BD: usar SEQUENCE (PostgreSQL) o AUTO_INCREMENT con función de formato.
• Tabla de contadores: mantener una tabla Contadores(entidad TEXT PK, ultimo_numero INTEGER) y actualizar con un UPDATE ... RETURNING atómico.
• UUID: sustituir los códigos CON/OPO/DOC por UUIDs si la legibilidad humana no es un requisito.

10.5 Gestión de Sesiones

La aplicación usa express-session con almacenamiento en memoria del proceso Node.js. Esto implica:

• Las sesiones se pierden al reiniciar el servidor (los usuarios tendrán que hacer login de nuevo).
• No es compatible con escalado horizontal (múltiples instancias del servidor no comparten sesiones).
• Para entornos de producción con alta disponibilidad, sustituir por un store persistente: connect-redis (Redis), connect-pg-simple (PostgreSQL) o better-sqlite3-session-store (SQLite).

Al reimplementar, evaluar el uso de JWT (JSON Web Tokens) si se prefiere una arquitectura sin estado (stateless), aunque esto requiere una estrategia de revocación de tokens.

10.6 Campos de Fecha como TEXT

Los campos de fecha en todas las tablas son de tipo TEXT en SQLite. La aplicación no valida el formato de las fechas: el usuario puede introducir texto libre en campos como FechaUltimoContacto. Al migrar a PostgreSQL o MySQL, considerar cambiar estos campos a DATE o TIMESTAMP y añadir validación en el servidor.

10.7 Restricciones de Integridad No Modeladas en BD

Algunas reglas de negocio se aplican a nivel de código (no a nivel de base de datos mediante CHECK CONSTRAINT):

• ProbabilidadExito: valores controlados Muy Alta, Alta, Media, Baja (validación en frontend).
• KYC_S_N, NDA_S_N, MSPASN: valores Sí / No (validación en frontend).
• rol en Usuarios: valores admin o comercial (validación en servidor).

Al reimplementar, se recomienda añadir CHECK CONSTRAINT en la base de datos para reforzar estas restricciones a nivel de motor, eliminando la dependencia de la validación de capa de aplicación.

10.8 Seguridad a Considerar

• Hashing de contraseñas: usar bcrypt, argon2 o similar. Nunca almacenar contraseñas en texto plano.
• Inyección SQL: better-sqlite3 usa sentencias preparadas con parámetros con nombre (@param), protegiendo contra SQL injection. Mantener esta práctica en cualquier reimplementación.
• CSRF: la aplicación usa SameSite=Lax para las cookies de sesión. En reimplementaciones donde el frontend se sirva desde un dominio diferente, añadir tokens CSRF explícitos.
• Rate limiting: no implementado actualmente. Recomendable añadir en el endpoint POST /api/login para evitar ataques de fuerza bruta.
• HTTPS: la aplicación sirve solo HTTP. En producción, desplegar detrás de un proxy inverso (nginx, IIS, Azure Front Door) que gestione el certificado TLS.

10.9 Backup y Recuperación

Al ser la base de datos un único fichero (crm.db), la estrategia de backup es sencilla:

• Copia del fichero: copiar crm.db cuando el servidor está detenido.
• SQLite backup API: better-sqlite3 expone el método db.backup('crm_backup.db') que realiza una copia segura sin detener la aplicación (online backup).
• Exportación SQL: sqlite3 crm.db .dump > crm_dump.sql genera un script SQL con toda la estructura y los datos.

GNL CRM — Documento de Diseño de Base de Datos — Generado el 01/03/2026
Referencia técnica interna — No para distribución externa