Base de Datos

◄ Anterior: Arquitectura | Indice | Siguiente: API Endpoints ►

---

Actualizado 2026-02-24: Las tablas background_jobs y notifications fueron migradas de schemas por sucursal (LEVEL_SUCURSAL) a la base de datos central DB_INI (schema public). Ver ADR-002 Enmienda para la justificacion completa.

Tabla de Contenidos

• Ubicacion y conexion
• Tupla de identidad multi-tenant
• Tabla: background_jobs
• Tabla: notifications
• Funcion PostgreSQL: notify_background_job_update()

---

Ubicacion y conexion

Base de datos: DB_INI (base de datos central del sistema) Schema: publicConexion en PHP: ConnectionManager::getDbal('ini')

Ambas tablas (background_jobs y notifications) residen en la base de datos central, no en schemas de sucursal. Esto permite queries consolidadas sin UNION cross-schema.

Migraciones (directorio migrations/system/):

• 20260224000001_create_background_jobs_central.php
• 20260224000002_create_notifications_central.php
• 20260227000001_job_scope_lock_index_and_constraint_fix.php — Agrega idx_background_jobs_scope_active; corrige chk_background_jobs_schema_format (3-digit → 4-digit caja)

---

Tupla de identidad multi-tenant

Dado que las tablas son centrales (no aisladas por schema), el aislamiento multi-tenant se garantiza mediante una tupla de 5 campos presente en ambas tablas:

Campo	Tipo	Descripcion
nro_sistema	INTEGER NOT NULL	Identifica el sistema/empresa
user_id	INTEGER NOT NULL	Identifica al usuario
db	VARCHAR(100) NOT NULL	Base de datos de la empresa (ej: empresa_xyz, empresa_xyz_p)
schema	VARCHAR(50) NOT NULL	Schema PostgreSQL de origen (ej: suc0001, suc0001caja001, public)
prueba	BOOLEAN NOT NULL DEFAULT FALSE	Distingue modo prueba (true) de modo oficial (false)

Regla critica: Toda query de lectura DEBE filtrar por al menos nro_sistema + user_id para garantizar aislamiento. Los repositorios JobRepository y NotificationRepository encapsulan este patron.

---

Tabla: background_jobs

Ubicacion: DB_INI.public.background_jobsRepositorio: App\Core\Repositories\JobRepository (conexion getDbal('ini'))

---

Campo	Tipo	Constraints	Descripcion
id	SERIAL	PRIMARY KEY	ID unico autogenerado
type	VARCHAR(100)	NOT NULL	Tipo de job (ej: batch_invoicing)
status	VARCHAR(20)	NOT NULL DEFAULT 'pending'	Estado: pending | running | completed | failed
payload	JSONB	NOT NULL	Datos necesarios para ejecutar el job
result	JSONB	NULL	Resultado del job (null hasta que complete)
error	TEXT	NULL	Mensaje de error (null si no fallo)
user_id	INTEGER	NOT NULL	ID del usuario que creo el job
nro_sistema	INTEGER	NOT NULL	Numero de sistema/empresa
prueba	BOOLEAN	NOT NULL DEFAULT FALSE	Modo prueba (true) o modo oficial (false)
db	VARCHAR(100)	NOT NULL	Base de datos de la empresa
schema	VARCHAR(50)	NOT NULL	Schema PostgreSQL donde ejecutar el job
progress	DECIMAL(5,2)	NOT NULL DEFAULT 0	Porcentaje de progreso (0.00 a 100.00)
retry_count	INTEGER	NOT NULL DEFAULT 0	Numero de reintentos ejecutados
max_retries	INTEGER	NOT NULL DEFAULT 3	Maximo de reintentos permitidos
next_retry_at	TIMESTAMP	NULL	Proximo reintento programado
started_at	TIMESTAMP	NULL	Fecha de inicio de ejecucion
completed_at	TIMESTAMP	NULL	Fecha de finalizacion
created_at	TIMESTAMP	NOT NULL	Fecha de creacion

Indices

Columnas	Tipo	Condicion	Proposito
user_id	BTREE	-	Jobs por usuario
status	BTREE	-	Jobs pendientes/running
created_at	BTREE	-	Busquedas por fecha
type	BTREE	-	Jobs por tipo
(user_id, status)	BTREE	-	Conteo de pendientes por usuario (DOS protection)
(status, retry_count)	BTREE	-	Jobs fallidos con reintentos disponibles
(nro_sistema, user_id, status)	BTREE	-	Filtro multi-tenant principal
progress	BTREE	WHERE status = 'running'	Parcial: progreso de jobs activos
next_retry_at	BTREE	WHERE status = 'pending' AND next_retry_at IS NOT NULL	Parcial: scheduler de reintentos
(type, db, schema, status)	BTREE	WHERE status IN ('pending', 'running')	Parcial: lookup scope-activo para guardia de dedup. Ver nota abajo.

Nota: idx_background_jobs_scope_active

Agregado en la migracion 20260227000001_job_scope_lock_index_and_constraint_fix. Proposito:

1. Optimizacion de query: Acelera findActiveByDbAndRootSchema() que filtra por (type, db, schema) + status activo.
2. Ventana de race condition (exact-schema): Dos requests concurrentes con (type, db, schema) identico que ambos pasen la guardia PHP antes de que alguno haga commit encontraran el segundo INSERT fallando con violacion de unicidad — solo si el index fuera UNIQUE. Nota: en la implementacion actual es no-UNIQUE (performance optimization), por lo que actua como safety net de query pero NO como garantia de unicidad. La guardia PHP maneja el caso cross-caja (ej: suc0001 vs suc0001caja0001); la unicidad en exact-schema bajo race condition muy estrecha queda como riesgo conocido y de baja probabilidad.

Para deployments en produccion donde zero-downtime es critico: CREATE INDEX CONCURRENTLY puede usarse pero requiere correr fuera de una transaccion Phinx (via psql directo).

Constraints CHECK

-- Status solo valores validos
CONSTRAINT chk_background_jobs_status
  CHECK (status IN ('pending', 'running', 'completed', 'failed'))

-- Schema tiene formato valido de multi-tenant (ACTUALIZADO 2026-02-27: corregido caja[0-9]{3} → {4})
CONSTRAINT chk_background_jobs_schema_format
  CHECK (schema ~ '^(public|suc[0-9]{4}(caja[0-9]{4})?)$')

-- Progreso entre 0 y 100
CONSTRAINT chk_background_jobs_progress
  CHECK (progress >= 0 AND progress <= 100)

-- Retry count no negativo
CONSTRAINT chk_background_jobs_retry_count
  CHECK (retry_count >= 0)

-- Si completed, debe tener completed_at
CONSTRAINT chk_background_jobs_completed_has_timestamp
  CHECK (
    (status = 'completed' AND completed_at IS NOT NULL)
    OR (status != 'completed')
  )

-- Si failed, debe tener error
CONSTRAINT chk_background_jobs_failed_has_error
  CHECK (
    (status = 'failed' AND error IS NOT NULL)
    OR (status != 'failed')
  )

Nota: chk_background_jobs_schema_format — Correccion aplicada en migracion 20260227000001_job_scope_lock_index_and_constraint_fix. El constraint original usaba caja[0-9]{3} (3 digitos) en lugar del patron correcto caja[0-9]{4} (4 digitos). Los workers siempre emitieron nombres de caja de 4 digitos (ej: caja0001), por lo que las filas existentes ya cumplen el patron corregido. El constraint fue dropeado y re-creado con el patron correcto en la misma migracion.

---

Tabla: notifications

Ubicacion: DB_INI.public.notificationsRepositorio: App\Core\Repositories\NotificationRepository (conexion getDbal('ini'))

---

Campo	Tipo	Constraints	Descripcion
id	SERIAL	PRIMARY KEY	ID unico autogenerado
user_id	INTEGER	NOT NULL	Usuario destinatario
nro_sistema	INTEGER	NOT NULL	Numero de sistema/empresa
prueba	BOOLEAN	NOT NULL DEFAULT FALSE	Modo prueba (true) o modo oficial (false)
db	VARCHAR(100)	NOT NULL	Base de datos de la empresa
schema	VARCHAR(50)	NOT NULL	Schema PostgreSQL de origen
type	VARCHAR(20)	NOT NULL	Tipo: success | error | info
title	VARCHAR(200)	NOT NULL	Titulo breve
message	TEXT	NOT NULL	Mensaje detallado
metadata	JSONB	NULL	Datos adicionales (ej: job_id, resultado)
is_read	BOOLEAN	NOT NULL DEFAULT FALSE	Si fue leida
created_at	TIMESTAMP	NOT NULL	Fecha de creacion
read_at	TIMESTAMP	NULL	Fecha de lectura

Indices

Columnas	Tipo	Proposito
user_id	BTREE	Notificaciones por usuario
is_read	BTREE	Notificaciones no leidas
created_at	BTREE	Ordenamiento por fecha
(nro_sistema, user_id, db, schema, prueba, is_read)	BTREE	Filtro multi-tenant completo con estado de lectura

Constraints CHECK

-- Type solo valores validos
CONSTRAINT chk_notifications_type
  CHECK (type IN ('success', 'error', 'info'))

-- Consistencia is_read / read_at
CONSTRAINT chk_notifications_read_consistency
  CHECK (
    (is_read = TRUE AND read_at IS NOT NULL)
    OR (is_read = FALSE AND read_at IS NULL)
  )

---

Funcion PostgreSQL: notify_background_job_update()

Proposito: Emitir una notificacion NOTIFY de PostgreSQL cuando un job cambia de estado o progreso, permitiendo que el backend escuche cambios en tiempo real via SSE.

Nombre de la funcion: notify_background_job_update()

Canal NOTIFY: job_updates_{schema}_{id}

El canal incluye el schema del job ademas del ID. Esto permite que los listeners se suscriban a canales especificos por schema, facilitando el filtrado en entornos multi-tenant.

Trigger:

CREATE TRIGGER background_jobs_notify_trigger
AFTER UPDATE ON background_jobs
FOR EACH ROW
WHEN (
    OLD.status IS DISTINCT FROM NEW.status OR
    OLD.progress IS DISTINCT FROM NEW.progress
)
EXECUTE PROCEDURE notify_background_job_update();

El trigger se dispara cuando cambia status O progress (no solo status como en el diseno original).

Funcion:

CREATE OR REPLACE FUNCTION notify_background_job_update()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify(
        'job_updates_' || NEW.schema || '_' || NEW.id::text,
        json_build_object(
            'id',       NEW.id,
            'status',   NEW.status,
            'progress', NEW.progress,
            'result',   NEW.result,
            'error',    NEW.error
        )::text
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Diferencias con el diseno original:

Aspecto	Diseno original	Implementacion actual
Canal NOTIFY	job_updates_{id}	job_updates_{schema}_{id}
Trigger condition	Solo cambio de status	Cambio de status O progress
Payload JSON	id, status, result, error	id, status, progress, result, error
Nombre funcion	notify_job_update()	notify_background_job_update()

Uso en PHP:

// El listener se suscribe al canal especifico del schema y job
$pdo->exec("LISTEN job_updates_{$schema}_{$jobId}");

while (true) {
    $notification = pg_get_notify($pdo);
    if ($notification) {
        // Payload incluye: id, status, progress, result, error
        echo "data: {$notification['payload']}\n\n";
        ob_flush(); flush();
    }
}

---

◄ Anterior: Arquitectura | Indice | Siguiente: API Endpoints ►