Saltar a contenido

Base de datos

Esquema físico en SQL Server 2022, versionado con Flyway. Todas las migraciones de BeneficiosCenter son aditivas: ninguna elimina ni renombra una columna existente (SPEC §6).

Diagrama de tablas

erDiagram
    tipo_cliente ||--o{ beneficio_tipo_cliente : ""
    beneficio ||--o{ beneficio_tipo_cliente : ""
    sucursal ||--o{ beneficio_sucursal : ""
    beneficio ||--o{ beneficio_sucursal : ""
    beneficio ||--o{ beneficio_dia_semana : ""
    beneficio ||--o{ beneficio_tipo_factura : ""
    beneficio ||--o{ beneficio_codigo_extra : ""
    consulta ||--o{ consulta_beneficio : ""
    beneficio ||--o{ consulta_beneficio : ""

    tipo_cliente {
        bigint id PK
        varchar codigo UK
        varchar nombre
        varchar payment_method_id UK
        bit activo
    }
    sucursal {
        bigint id PK
        varchar codigo_externo UK
        varchar nombre
        bit activa
    }
    beneficio {
        bigint id PK
        varchar nombre
        varchar descripcion
        decimal porcentaje
        date vigencia_desde
        date vigencia_hasta
        time hora_desde
        time hora_hasta
        decimal monto_minimo
        decimal tope_descuento
        int prioridad
        varchar estado
    }
    usuario {
        bigint id PK
        varchar usuario UK
        varchar hash_password
        varchar rol
        bit activo
    }
    consulta {
        bigint id PK
        varchar request_id
        varchar branch_id
        datetimeoffset recibida_en
        int latencia_ms
        nvarchar request_json
        nvarchar response_json
        nvarchar evaluaciones_json
        varchar resultado
        bit repetida
        bit sucursal_desconocida
        nvarchar beneficios_aplicados
    }
    consulta_beneficio {
        bigint consulta_id PK_FK
        bigint benefit_id PK_FK
        decimal descuento_ofrecido
    }
    resultado_pago {
        bigint id PK
        varchar request_id UK
        varchar estado
        bigint payment_method_id
        bigint benefit_id
        decimal descuento_aplicado
        decimal importe_original
        decimal importe_final
        varchar motivo_rechazo
        datetimeoffset recibido_en
    }

V1 — esquema inicial

Tablas base: tipo_cliente, sucursal, beneficio (con sus tablas de asociación beneficio_dia_semana, beneficio_tipo_factura, beneficio_codigo_extra, beneficio_tipo_cliente, beneficio_sucursal), usuario, consulta.

Tabla Nota
tipo_cliente payment_method_id con UNIQUE propio, además de codigo — INV-D04.
sucursal codigo_externo es el branchId que envía el gateway.
beneficio CHECK sobre estado IN ('ACTIVO','INACTIVO'), porcentaje > 0 AND <= 100, vigencia_desde <= vigencia_hasta.
beneficio_dia_semana CHECK sobre los siete días en inglés (MONDAY..SUNDAY).
usuario hash_password guarda solo BCrypt (INV-S02); CHECK sobre rol IN ('ADMIN','OPERADOR').
consulta Fuera de la ruta caliente — la escribe el consumidor de la cola de auditoría, nunca el controller de resolución. recibida_en es DATETIMEOFFSET(7), no DATETIME2: así lo mapea Hibernate por defecto para java.time.Instant bajo ddl-auto=validate. Índices sobre request_id, recibida_en y branch_id.

V2 — resultado ERROR_INTERNO

Agrega ERROR_INTERNO a los valores permitidos de consulta.resultado, para que el manejador 500 de una falla inesperada del resolver pueda auditarla. SQL Server no soporta agregar un valor a un CHECK existente: la migración reemplaza el constraint completo, pero ningún valor previamente aceptado deja de aceptarse.

V3 — índice compuesto de auditoría

Agrega ix_consulta_recibida_en_branch_id (recibida_en, branch_id): sostiene tanto las agregaciones del tablero (AC-29, range scan sobre recibida_en) como el filtro por sucursal de la búsqueda de auditoría sobre el mismo scan. recibida_en va primero para que un filtro solo por rango de fechas siga usando el índice.

También agrega la columna consulta.beneficios_aplicados (NVARCHAR(4000), nullable): identificadores de beneficio ganadores, separados por coma, para el top-5 del tablero sin parsear response_json por fila en cada request. NULL, nunca string vacío, cuando ningún beneficio aplicó.

V4 — tabla consulta_beneficio

Normaliza los benefitId ganadores de cada consulta en una tabla propia, para que el top-5 del tablero sea una consulta SQL agrupada (GROUP BY benefit_id) en vez de parsear beneficios_aplicados fila por fila en Java. beneficios_aplicados no se elimina (regla aditiva): queda por si algo más depende de ella.

ON DELETE CASCADE desde consulta_beneficio hacia consulta: PurgaAuditoriaJob borra filas de consulta en lote (AC-32); sin cascada, una purga fallaría por violación de FK en cuanto una consulta purgada todavía tuviera filas acá.

V5 — tabla resultado_pago (entrega 3, Q2)

Tabla nueva, sin relación con consulta: resultado_pago no se llena desde la ruta caliente sino desde POST /api/v1/beneficios/resultado, que el GatewayQR llama después de que el autorizador ya respondió (ver Pipeline de resultado de pago). Se correlaciona con su consulta por request_id, en la aplicación, no con una FK — las dos tablas se escriben desde caminos completamente distintos y en momentos distintos.

request_id tiene una UNIQUE propia (uq_resultado_pago_request_id): un reintento del gateway con el mismo requestId hace upsert sobre la misma fila (AC-48), nunca inserta una segunda. estado tiene un CHECK sobre APROBADO/RECHAZADO. Todas las columnas de negocio (payment_method_id, benefit_id, los tres importes, motivo_rechazo) son NULL-ables: el gateway puede no tener todavía ese dato (por ejemplo benefit_id sin round-trip resuelto) sin que eso bloquee guardar el resultado.

V6 — índice de resultado_pago

Agrega ix_resultado_pago_recibido_en (recibido_en): sostiene las agregaciones por rango de fecha de GET /api/admin/tablero/resultados (AC-51), mismo criterio que V3 para el tablero gemelo de consulta.

V7 — índices de resultado_pago para el tablero (AC-67)

Agrega ix_resultado_pago_estado_benefit_id (estado, benefit_id) e ix_resultado_pago_estado_payment_method_id (estado, payment_method_id). Respaldan dos agregaciones que filtran por estado además del rango: el ranking por beneficio del tablero de captura (GET /api/admin/tablero/captura, AC-55, agrupa por benefit_id con estado = 'APROBADO') y el desglose por medio de pago del panel de resultados (GET /api/admin/tablero/resultados, AC-51, agrupa por payment_method_id). ix_resultado_pago_recibido_en (V6) se reutiliza sin duplicar para el WHERE del job de purga.

V8 — consulta_beneficio.descuento_ofrecido

Agrega descuento_ofrecido DECIMAL(19, 2) NULL a consulta_beneficio: el monto de descuento que el resolver cotizó para ese beneficio ofrecido (ResolvedBenefit.discountAmount), persistido fuera de la ruta caliente por el consumidor de la cola de auditoría. Es la mitad "consultado" de la comparación consultado-vs-otorgado del Panel de resultados.

NULL en toda fila anterior a esta migración — el monto ofrecido nunca se persistió antes, así que no se puede reconstruir con el histórico real. La suma de esta columna en el panel usa COALESCE(...,0).

Retención y purga (AC-67)

bc.auditoria.retencion-dias (por defecto 365 días) también aplica a resultado_pago, no solo a consulta: PurgaAuditoriaJob borra ambas tablas en la misma pasada semanal. resultado_pago no tiene ON DELETE CASCADE hacia ninguna otra tabla (a diferencia de consulta_beneficio sobre consulta), así que se purga en forma directa e independiente, con el mismo batcheo y el mismo criterio de nunca borrar dentro del período vigente. Detalle operativo completo en Despliegue.

Dónde sigue