Skip to main content

12. Modelo de Datos y Arquitectura de Base de Datos: MDX-Cloud

Esta sección especifica la estructura física de la base de datos en PostgreSQL. El diseño implementa un aislamiento estricto por 10 esquemas lógicos para soportar la arquitectura de Monolito Modular, basándose en los principios de Domain-Driven Design (DDD) donde cada esquema representa una "Isla de Información" enfocada a garantizar consistencia de datos a gran escala.

12.1 Definición de Esquemas y Diccionario de Tablas​

1. Esquema: catalogos (El Núcleo)​

  • catalogos.categorias_producto: Clasifica productos (control térmico, lote, caducidad).
  • catalogos.unidades_medida: Unidades de intercambio, referenciando las claves del SAT.
  • catalogos.laboratorios: Catálogo de fabricantes.
  • catalogos.productos: Catálogo central global y abstracto.
  • catalogos.producto_presentaciones: Variantes de un producto (Caja c/10, etc.).

2. Esquema: fiscal (Soberanía Gubernamental)​

  • fiscal.sat_unidades: Catálogo oficial de claves de unidad del SAT.
  • fiscal.sat_productos_servicios: Catálogo oficial de productos y servicios del SAT.

3. Esquema: ventas (El Ingreso - Front-Office)​

  • ventas.clientes: Perfil comercial y datos de facturación (Régimen, Código Postal).
  • ventas.matriz_precios: Cálculo dinámico de márgenes de ganancia.
  • ventas.pedidos y _detalle: Transacciones de órdenes de venta con firma de idempotencia.

4. Esquema: compras (El Egreso - Back-Office)​

  • compras.proveedores: Entidades comerciales proveedoras de mercancía o servicios.
  • compras.ordenes_compra y _detalle: Emisión formal de la intención de compra.

5. Esquema: wms (La Realidad Física)​

  • wms.cedis: Entidades físicas (Sucursales, Matriz).
  • wms.lotes: Registro sanitario y caducidades expedidas.
  • wms.inventario: El núcleo operativo físico. Existencia condicionada por cedis + producto + presentacion + lote.
  • wms.tipos_movimiento, wms.movimientos_inventario y _detalle: El Kardex inmutable.
  • wms.motivos_ajuste, ajustes_inventario y _detalle: Registro forense de incidencias.
  • wms.transferencias y _detalle: Flujo de mercancía inter-sucursales.
  • wms.alertas_lotes: Bloqueos sanitarios automáticos o manuales.

6. Esquema: credito_cobranza (Cuentas por Cobrar)​

  • credito_cobranza.deudas: Registro maestro de dinero adeudado por los clientes.
  • credito_cobranza.aplicacion_pagos: Transacciones de amortización de abonos (Algoritmo FIFO).

7. Esquema: tesoreria (Cuentas por Pagar y Flujo)​

  • tesoreria.cuentas_por_pagar: Dinero que MDX-Cloud le debe a sus proveedores por mercancía o servicios (Fase 2).
  • tesoreria.pagos_emitidos: Egresos de efectivo y caja.

8. Esquema: cfdi (Bóveda Electrónica)​

  • cfdi.comprobantes: Almacena los metadatos fiscales de las facturas timbradas (UUID), junto con los enlaces hacia la Bóveda R2 (para el XML) y hacia el PAC (para el PDF).
  • cfdi.despachos: Control de remisiones y despachos de mostrador.

9. Esquema: personal (Identidad y Capital Humano)​

  • personal.empleados: Expediente completo del personal operativo y administrativo.
  • personal.usuarios: Identidades digitales de acceso y roles de usuario.

10. Esquema: auditoria (Seguridad Forense)​

  • auditoria.logs: Bitácora forense en formato JSONB.

12.2 Script de Inicialización Estricta (DDL SQL)​

-- Habilitar extensión para generación distribuida de IDs universales
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE SCHEMA IF NOT EXISTS catalogos;
CREATE SCHEMA IF NOT EXISTS fiscal;
CREATE SCHEMA IF NOT EXISTS ventas;
CREATE SCHEMA IF NOT EXISTS compras;
CREATE SCHEMA IF NOT EXISTS wms;
CREATE SCHEMA IF NOT EXISTS credito_cobranza;
CREATE SCHEMA IF NOT EXISTS tesoreria;
CREATE SCHEMA IF NOT EXISTS cfdi;
CREATE SCHEMA IF NOT EXISTS personal;
CREATE SCHEMA IF NOT EXISTS auditoria;

-- ======================================================
-- ESQUEMA: PERSONAL (EMPLEADOS Y SEGURIDAD)
-- ======================================================

CREATE TABLE personal.empleados (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    facturacom_uid VARCHAR(50), 
    numero_empleado VARCHAR(50) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    apellido_paterno VARCHAR(100) NOT NULL,
    apellido_materno VARCHAR(100) NOT NULL,
    rfc VARCHAR(20) NOT NULL,
    curp VARCHAR(30) NOT NULL,
    nss VARCHAR(20),
    email VARCHAR(150) NOT NULL,
    telefono VARCHAR(30),
    puesto VARCHAR(100) NOT NULL,
    departamento VARCHAR(100) NOT NULL,
    fecha_ingreso DATE NOT NULL,
    salario_diario DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
    clabe_interbancaria VARCHAR(20),
    banco_sat_code VARCHAR(10),
    periodo_pago_sat VARCHAR(10),
    tipo_contrato_sat VARCHAR(10),
    regimen_fiscal_sat VARCHAR(10) NOT NULL DEFAULT '605',
    calle VARCHAR(150),
    numero_exterior VARCHAR(20),
    numero_interior VARCHAR(20),
    colonia VARCHAR(100),
    municipio VARCHAR(100),
    estado VARCHAR(100),
    codigo_postal VARCHAR(10) NOT NULL,
    pais VARCHAR(10) NOT NULL DEFAULT 'MEX',
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE personal.usuarios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    empleado_id UUID NOT NULL REFERENCES personal.empleados(id) ON DELETE CASCADE,
    username VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    rol VARCHAR(50) NOT NULL, -- Gerencia, Administrador, Compras, Ventas, Almacen...
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- ======================================================
-- ESQUEMA: FISCAL (SAT)
-- ======================================================

CREATE TABLE fiscal.sat_unidades (
    clave VARCHAR(20) PRIMARY KEY,
    nombre VARCHAR(250) NOT NULL,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fiscal.sat_productos_servicios (
    clave VARCHAR(20) PRIMARY KEY,
    descripcion TEXT NOT NULL,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- ======================================================
-- ESQUEMA: CATALOGOS (NÚCLEO DE DATOS)
-- ======================================================

CREATE TABLE catalogos.categorias_producto (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nombre VARCHAR(150) NOT NULL,
    descripcion TEXT,
    requiere_lote BOOLEAN DEFAULT TRUE,
    requiere_caducidad BOOLEAN DEFAULT TRUE,
    maneja_temperatura BOOLEAN DEFAULT FALSE,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE catalogos.unidades_medida (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clave VARCHAR(20) NOT NULL UNIQUE,
    nombre VARCHAR(100) NOT NULL,
    abreviacion VARCHAR(20),
    clave_sat VARCHAR(20) REFERENCES fiscal.sat_unidades(clave),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE catalogos.laboratorios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nombre VARCHAR(200) NOT NULL,
    contacto VARCHAR(150),
    telefono VARCHAR(30),
    email VARCHAR(150),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE catalogos.productos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku VARCHAR(100) NOT NULL UNIQUE,
    clave_sat VARCHAR(20) NOT NULL REFERENCES fiscal.sat_productos_servicios(clave),
    codigo_barras VARCHAR(150),
    nombre VARCHAR(250) NOT NULL,
    sustancia_activa VARCHAR(150),
    descripcion TEXT,
    categoria_id UUID NOT NULL REFERENCES catalogos.categorias_producto(id),
    laboratorio_id UUID REFERENCES catalogos.laboratorios(id),
    tipo_producto VARCHAR(50) NOT NULL,
    clasificacion_fiscal VARCHAR(100),
    requiere_receta BOOLEAN DEFAULT FALSE,
    controlado BOOLEAN DEFAULT FALSE,
    maneja_lotes BOOLEAN DEFAULT TRUE,
    maneja_caducidad BOOLEAN DEFAULT TRUE,
    maneja_series BOOLEAN DEFAULT FALSE,
    temperatura_min DECIMAL(10,2),
    temperatura_max DECIMAL(10,2),
    unidad_compra_id UUID REFERENCES catalogos.unidades_medida(id),
    unidad_venta_id UUID REFERENCES catalogos.unidades_medida(id),
    factor_conversion DECIMAL(18,6) DEFAULT 1.0,
    costo_base DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    stock_minimo DECIMAL(18,2) DEFAULT 0.0,
    stock_maximo DECIMAL(18,2),
    
    -- Parámetros Fiscales para CFDI 4.0
    objeto_imp VARCHAR(5) NOT NULL DEFAULT '02',
    impuesto_iva_tipo_factor VARCHAR(15) NOT NULL DEFAULT 'Tasa',
    impuesto_iva_tasa DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
    impuesto_ieps_tasa DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
    impuesto_iva_retencion_tasa DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
    impuesto_isr_retencion_tasa DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
    
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE catalogos.producto_presentaciones (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id) ON DELETE CASCADE,
    clave_presentacion VARCHAR(100),
    nombre VARCHAR(200) NOT NULL,
    contenido DECIMAL(18,4) NOT NULL,
    unidad_medida_id UUID REFERENCES catalogos.unidades_medida(id),
    precio_base DECIMAL(18,2) NOT NULL DEFAULT 0.00,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- ======================================================
-- ESQUEMA: VENTAS (COMERCIAL & B2B)
-- ======================================================

CREATE TABLE ventas.clientes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    facturacom_uid VARCHAR(50), 
    rfc VARCHAR(20) NOT NULL,
    razon_social VARCHAR(250) NOT NULL,
    nombre_comercial VARCHAR(250),
    regimen_fiscal VARCHAR(10) NOT NULL,
    uso_cfdi VARCHAR(10) NOT NULL DEFAULT 'G03',
    email VARCHAR(150) NOT NULL,
    email_alternativo1 VARCHAR(150),
    email_alternativo2 VARCHAR(150),
    telefono VARCHAR(30),
    contacto_nombre VARCHAR(150),
    contacto_apellidos VARCHAR(150),
    
    calle VARCHAR(250),
    numero_exterior VARCHAR(50),
    numero_interior VARCHAR(50),
    colonia VARCHAR(150),
    localidad VARCHAR(150),
    municipio_delegacion VARCHAR(150),
    estado VARCHAR(150),
    codigo_postal VARCHAR(10) NOT NULL,
    pais VARCHAR(10) NOT NULL DEFAULT 'MEX',
    
    perfil_cliente VARCHAR(30) NOT NULL DEFAULT 'PublicoGeneral',
    limite_credito DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    dias_credito INTEGER NOT NULL DEFAULT 0,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ventas.matriz_precios (
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id) ON DELETE CASCADE,
    perfil_cliente VARCHAR(30) NOT NULL,
    margen_ganancia NUMERIC(5,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (producto_id, perfil_cliente)
);

CREATE TABLE ventas.pedidos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    folio VARCHAR(100) NOT NULL UNIQUE,
    cliente_id UUID NOT NULL REFERENCES ventas.clientes(id),
    sucursal_id UUID NOT NULL, -- Logical Ref to WMS Cedis (Declarado despues de wms)
    fecha_pedido TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    estado VARCHAR(50) NOT NULL DEFAULT 'CAPTURADO',
    monto_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    idempotency_key UUID UNIQUE,
    observaciones TEXT,
    usuario_id UUID NOT NULL,
    
    forma_pago VARCHAR(5) NOT NULL DEFAULT '99',
    metodo_pago VARCHAR(5) NOT NULL DEFAULT 'PPD',
    uso_cfdi VARCHAR(10) NOT NULL DEFAULT 'G03',
    moneda VARCHAR(5) NOT NULL DEFAULT 'MXN',
    tipo_cambio DECIMAL(18,6) NOT NULL DEFAULT 1.000000,
    condiciones_pago VARCHAR(1000),
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ventas.pedidos_detalle (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    pedido_id UUID NOT NULL REFERENCES ventas.pedidos(id) ON DELETE CASCADE,
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id),
    presentacion_id UUID REFERENCES catalogos.producto_presentaciones(id),
    cantidad DECIMAL(18,4) NOT NULL,
    cantidad_facturada DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
    precio_unitario DECIMAL(12,2) NOT NULL,
    subtotal DECIMAL(12,2) GENERATED ALWAYS AS (cantidad * precio_unitario) STORED,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- ======================================================
-- ESQUEMA: COMPRAS (PROVEEDORES Y ÓRDENES)
-- ======================================================

CREATE TABLE compras.proveedores (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    razon_social VARCHAR(250) NOT NULL,
    nombre_comercial VARCHAR(250),
    rfc VARCHAR(20),
    telefono VARCHAR(30),
    email VARCHAR(150),
    direccion TEXT,
    dias_credito INTEGER DEFAULT 0,
    limite_credito DECIMAL(12,2) DEFAULT 0.00,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE compras.ordenes_compra (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    folio VARCHAR(100) NOT NULL UNIQUE,
    proveedor_id UUID NOT NULL REFERENCES compras.proveedores(id),
    fecha_emision TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    estado VARCHAR(50) NOT NULL DEFAULT 'PENDIENTE',
    observaciones TEXT,
    usuario_id UUID NOT NULL REFERENCES personal.usuarios(id),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE compras.ordenes_compra_detalle (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    orden_compra_id UUID NOT NULL REFERENCES compras.ordenes_compra(id) ON DELETE CASCADE,
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id),
    cantidad_solicitada DECIMAL(18,4) NOT NULL,
    cantidad_recibida DECIMAL(18,4) DEFAULT 0.0000,
    costo_unitario DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);


-- ======================================================
-- ESQUEMA: WMS (OPERACIÓN FÍSICA E INVENTARIOS)
-- ======================================================

CREATE TABLE wms.cedis (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clave VARCHAR(20) NOT NULL UNIQUE,
    nombre VARCHAR(150) NOT NULL,
    tipo VARCHAR(50) NOT NULL,
    estado VARCHAR(100),
    ciudad VARCHAR(100),
    direccion TEXT,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE ventas.pedidos ADD CONSTRAINT fk_ventas_pedidos_sucursal 
FOREIGN KEY (sucursal_id) REFERENCES wms.cedis(id);

CREATE TABLE wms.lotes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id),
    numero_lote VARCHAR(150) NOT NULL,
    fecha_fabricacion DATE,
    fecha_caducidad DATE NOT NULL,
    proveedor_id UUID REFERENCES compras.proveedores(id),
    registro_sanitario VARCHAR(150),
    estado VARCHAR(20) NOT NULL DEFAULT 'Activo',
    observaciones TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wms.inventario (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cedis_id UUID NOT NULL REFERENCES wms.cedis(id),
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id),
    presentacion_id UUID NOT NULL REFERENCES catalogos.producto_presentaciones(id),
    lote_id UUID NOT NULL REFERENCES wms.lotes(id),
    existencia DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
    stock_reservado DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
    stock_disponible DECIMAL(18,4) GENERATED ALWAYS AS (existencia - stock_reservado) STORED,
    costo_promedio DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
    ultimo_costo DECIMAL(18,4) NOT NULL DEFAULT 0.0000,
    ubicacion_pasillo VARCHAR(10),
    ubicacion_estante VARCHAR(10),
    ultima_entrada TIMESTAMPTZ,
    ultima_salida TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX uq_cedis_producto_presentacion_lote 
ON wms.inventario (cedis_id, producto_id, presentacion_id, lote_id);

CREATE INDEX IX_Inventario_FEFO 
ON wms.inventario (cedis_id, producto_id, stock_disponible);

CREATE TABLE wms.tipos_movimiento (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    clave VARCHAR(50) NOT NULL UNIQUE,
    nombre VARCHAR(150) NOT NULL,
    afecta_inventario INTEGER NOT NULL 
);

CREATE TABLE wms.movimientos_inventario (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    folio VARCHAR(100) NOT NULL UNIQUE,
    tipo_movimiento_id UUID NOT NULL REFERENCES wms.tipos_movimiento(id),
    cedis_origen_id UUID REFERENCES wms.cedis(id),
    cedis_destino_id UUID REFERENCES wms.cedis(id),
    fecha TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    referencia VARCHAR(150),
    observaciones TEXT,
    usuario_id UUID NOT NULL REFERENCES personal.usuarios(id),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wms.movimientos_inventario_detalle (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    movimiento_id UUID NOT NULL REFERENCES wms.movimientos_inventario(id) ON DELETE CASCADE,
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id),
    presentacion_id UUID REFERENCES catalogos.producto_presentaciones(id),
    lote_id UUID NOT NULL REFERENCES wms.lotes(id),
    cantidad DECIMAL(18,4) NOT NULL,
    costo_unitario DECIMAL(18,4),
    subtotal DECIMAL(18,4) GENERATED ALWAYS AS (cantidad * costo_unitario) STORED,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wms.motivos_ajuste (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nombre VARCHAR(150) NOT NULL,
    tipo VARCHAR(50) NOT NULL
);

CREATE TABLE wms.ajustes_inventario (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cedis_id UUID NOT NULL REFERENCES wms.cedis(id),
    motivo_id UUID NOT NULL REFERENCES wms.motivos_ajuste(id),
    fecha TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    observaciones TEXT,
    usuario_id UUID NOT NULL REFERENCES personal.usuarios(id),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wms.ajustes_inventario_detalle (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ajuste_id UUID NOT NULL REFERENCES wms.ajustes_inventario(id) ON DELETE CASCADE,
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id),
    lote_id UUID REFERENCES wms.lotes(id),
    cantidad DECIMAL(18,4) NOT NULL,
    costo DECIMAL(18,4)
);

CREATE TABLE wms.transferencias (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    folio VARCHAR(100) NOT NULL UNIQUE,
    cedis_origen_id UUID NOT NULL REFERENCES wms.cedis(id),
    cedis_destino_id UUID NOT NULL REFERENCES wms.cedis(id),
    fecha_solicitud TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    fecha_envio TIMESTAMPTZ,
    fecha_recepcion TIMESTAMPTZ,
    estado VARCHAR(50) DEFAULT 'SOLICITADA',
    observaciones TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wms.transferencias_detalle (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    transferencia_id UUID NOT NULL REFERENCES wms.transferencias(id) ON DELETE CASCADE,
    producto_id UUID NOT NULL REFERENCES catalogos.productos(id),
    lote_id UUID REFERENCES wms.lotes(id),
    cantidad DECIMAL(18,4) NOT NULL,
    cantidad_recibida DECIMAL(18,4)
);

CREATE TABLE wms.alertas_lotes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    lote_id UUID NOT NULL REFERENCES wms.lotes(id) ON DELETE CASCADE,
    tipo_alerta VARCHAR(50) NOT NULL,
    fecha_inicio TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    fecha_fin TIMESTAMPTZ,
    descripcion TEXT,
    activo BOOLEAN DEFAULT TRUE
);

-- ======================================================
-- ESQUEMA: CREDITO Y COBRANZA
-- ======================================================

CREATE TABLE credito_cobranza.deudas (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cliente_id UUID NOT NULL REFERENCES ventas.clientes(id),
    pedido_id UUID REFERENCES ventas.pedidos(id),
    
    subtotal NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    iva NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    ieps NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    retencion_iva NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    retencion_isr NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    monto_total NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    saldo_pendiente NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    
    fecha_emision TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_deuda VARCHAR(30) NOT NULL DEFAULT 'Vigente', 
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE credito_cobranza.aplicacion_pagos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    deuda_id UUID NOT NULL REFERENCES credito_cobranza.deudas(id),
    monto_applied NUMERIC(12,2) NOT NULL,
    fecha_aplicacion TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IX_Deudas_Antiguedad_Saldo 
ON credito_cobranza.deudas (cliente_id, fecha_emision ASC) 
WHERE saldo_pendiente > 0;

-- ======================================================
-- ESQUEMA: TESORERÍA (CUENTAS POR PAGAR)
-- ======================================================

CREATE TABLE tesoreria.cuentas_por_pagar (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proveedor_id UUID NOT NULL REFERENCES compras.proveedores(id),
    orden_compra_id UUID REFERENCES compras.ordenes_compra(id),
    
    folio_factura_proveedor VARCHAR(100),
    uuid_sat VARCHAR(50),
    monto_total NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    saldo_pendiente NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    
    fecha_emision DATE NOT NULL,
    fecha_vencimiento DATE,
    status VARCHAR(30) NOT NULL DEFAULT 'Vigente',
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tesoreria.pagos_emitidos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cuenta_pagar_id UUID NOT NULL REFERENCES tesoreria.cuentas_por_pagar(id),
    monto_pagado NUMERIC(12,2) NOT NULL,
    fecha_pago TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    referencia VARCHAR(150),
    usuario_id UUID NOT NULL REFERENCES personal.usuarios(id)
);

-- ======================================================
-- ESQUEMA: CFDI (TIMBRADO Y ARCHIVO HISTÓRICO)
-- ======================================================

CREATE TABLE cfdi.comprobantes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cliente_id UUID NOT NULL REFERENCES ventas.clientes(id),
    pedido_id UUID REFERENCES ventas.pedidos(id),
    
    -- Metadatos del PAC y SAT
    pac_factura_uid VARCHAR(50), 
    folio_fiscal_uuid VARCHAR(50) UNIQUE, 
    serie VARCHAR(10),
    folio INTEGER,
    no_certificado_sat VARCHAR(30),
    fecha_timbrado TIMESTAMPTZ,
    status_fiscal VARCHAR(30) NOT NULL DEFAULT 'Timbrada', 
    
    -- Enlaces de Almacenamiento
    xml_url_r2 VARCHAR(500), 
    pdf_url_pac VARCHAR(500), 
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE cfdi.despachos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    pedido_id UUID NOT NULL REFERENCES ventas.pedidos(id) ON DELETE RESTRICT,
    tipo_documento VARCHAR(15) NOT NULL, 
    estado_fiscal VARCHAR(20) NOT NULL, 
    token_facturacom VARCHAR(100)
);

-- ======================================================
-- ESQUEMA: AUDITORIA (LOGS)
-- ======================================================

CREATE TABLE auditoria.logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    usuario_id UUID NOT NULL REFERENCES personal.usuarios(id),
    fecha_hora TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    modulo VARCHAR(50) NOT NULL,
    entidad VARCHAR(50) NOT NULL,
    entidad_id UUID NOT NULL,
    accion VARCHAR(10) NOT NULL, 
    valores_anteriores JSONB NULL,
    valores_nuevos JSONB NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL
);

CREATE INDEX idx_audit_busqueda ON auditoria.logs(entidad, entidad_id);

12.3 Estrategia de Concurrencia y Control de Condiciones de Carrera (Race Conditions)​

Para asegurar la consistencia absoluta de los saldos financieros, estados de cuenta y stock físico de medicamentos en escenarios de alta densidad transaccional simultánea (mostrador y almacén), el sistema implementa un modelo de aislamiento híbrido en la capa de datos:

A. Concurrencia Pesimista (Bloqueo Físico de Filas)​

Se aplica obligatoriamente en transacciones críticas donde la colisión de datos altera inventarios o flujos de efectivo:

  • Mecanismo: El backend de .NET 10 inyectará la instrucción nativa FOR UPDATE.
  • Comportamiento: Bloquea físicamente el registro en disco poniendo en cola las peticiones concurrentes hasta ejecutar Commit o Rollback.

B. Concurrencia Optimista (Control de Versiones en Catálogos)​

Se aplica en tablas maestras con baja probabilidad de colisión simultánea pero que requieren protección contra sobreescritura:

  • Mecanismo: Se explota la columna del sistema nativa de PostgreSQL llamada xmin.
  • Comportamiento: Si otro usuario actualizó el registro un milisegundo antes, la transacción es rechazada de forma segura.

C. Mitigación en el Borde: Idempotencia Transaccional​

Como tercer escudo ante condiciones de carrera provocadas por la latencia de red:

  • Todo payload de mutación transaccional viaja firmado con Idempotency-Key (UUIDv4).
  • Peticiones concurrentes idénticas en 120 segundos son interceptadas y descartadas en el borde.

12.4 Trazabilidad de Auditoría Dual: Kardex Inmutable y Auditoría Dinámica (JSONB)​

Para cumplir con la normatividad sanitaria y garantizar el rastreo forense:

A. Trazabilidad de Inventario (El Kardex de Almacén)​

  • Regla de Oro: Solo permiten operaciones INSERT. Quedan estrictamente prohibidos los comandos UPDATE o DELETE.
  • Estructura: Relación maestro-detalle de wms.movimientos_inventario y wms.movimientos_inventario_detalle.

B. Trazabilidad de Sistema (Audit Log Polimórfico con JSONB)​

  • Estructura Centralizada Dinámica (auditoria.logs): Se explota el tipo de datos binario estructurado JSONB de PostgreSQL.
  • Automatización (.NET 10 Engine): Se sobreescribe el método SaveChangesAsync() en EF Core para guardar los JSONB automáticamente.
  • Seguridad y No-Repudio: El rol de conexión de la API no puede alterar o vaciar la tabla de logs. La persistencia se asegura enviando respaldos hacia Cloudflare R2.