Base de Datos

◄ Anterior: Arquitectura | Índice | Siguiente: API Endpoints ►

---

Tabla de Contenidos

• Tabla: background_jobs
• Tabla: notifications
• Función PostgreSQL: notify_job_update() (Fase 2)

---

Tabla: background_jobs

Nivel Multi-tenant: LEVEL_SUCURSAL (CRÍTICO)

Justificación del nivel:

• Cada sucursal debe ver solo sus jobs
• Jobs pueden tener datos sensibles en payload
• Aislamiento completo por schema

Propagación de schema: X-Schema header → ConnectionManager → search_path PostgreSQL

---

Campo	Tipo	Constraints	Descripción
id	SERIAL	PRIMARY KEY	ID único 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 falló)
user_id	INTEGER	NOT NULL	ID del usuario que creó el job
schema	VARCHAR(50)	NOT NULL	Schema PostgreSQL donde ejecutar (CRÍTICO)
created_at	TIMESTAMP	NOT NULL DEFAULT NOW()	Fecha de creación
started_at	TIMESTAMP	NULL	Fecha de inicio de ejecución
completed_at	TIMESTAMP	NULL	Fecha de finalización

Índices

Nombre	Columnas	Tipo	Propósito
idx_background_jobs_user_id	user_id	BTREE	Jobs por usuario
idx_background_jobs_status	status	BTREE	Jobs pendientes/running
idx_background_jobs_created_at	created_at	BTREE	Búsquedas por fecha
idx_background_jobs_type	type	BTREE	Jobs por tipo
idx_background_jobs_user_status	(user_id, status)	BTREE	Conteo de pendientes (DOS protection)

Constraints Adicionales

-- Check: status solo puede tener valores válidos
CHECK (status IN ('pending', 'running', 'completed', 'failed'))

-- Check: schema tiene formato válido
CHECK (schema ~ '^suc[0-9]{4}(caja[0-9]{3})?$')

-- Check: si completed, debe tener completed_at
CHECK (
  (status = 'completed' AND completed_at IS NOT NULL) OR
  (status != 'completed')
)

-- Check: si failed, debe tener error
CHECK (
  (status = 'failed' AND error IS NOT NULL) OR
  (status != 'failed')
)

---

Tabla: notifications

Nivel Multi-tenant: LEVEL_SUCURSAL (CRÍTICO)

Justificación del nivel:

• Cada sucursal debe ver solo sus notificaciones
• Aislamiento de datos sensibles en mensajes

---

Campo	Tipo	Constraints	Descripción
id	SERIAL	PRIMARY KEY	ID único autogenerado
user_id	INTEGER	NOT NULL	Usuario destinatario
type	VARCHAR(20)	NOT NULL	Tipo: success | error | info
title	VARCHAR(200)	NOT NULL	Título breve
message	TEXT	NOT NULL	Mensaje detallado
metadata	JSONB	NULL	Datos adicionales (ej: job_id, result)
is_read	BOOLEAN	NOT NULL DEFAULT FALSE	Si fue leída
created_at	TIMESTAMP	NOT NULL DEFAULT NOW()	Fecha de creación
read_at	TIMESTAMP	NULL	Fecha de lectura

Índices

Nombre	Columnas	Tipo	Propósito
idx_notifications_user_id	user_id	BTREE	Notificaciones por usuario
idx_notifications_is_read	is_read	BTREE	Notificaciones no leídas
idx_notifications_created_at	created_at	BTREE	Ordenamiento por fecha
idx_notifications_user_read	(user_id, is_read)	BTREE	Badge de notificaciones (COUNT unread)

Constraints Adicionales

-- Check: type solo puede tener valores válidos
CHECK (type IN ('success', 'error', 'info'))

-- Check: si is_read=true, debe tener read_at
CHECK (
  (is_read = TRUE AND read_at IS NOT NULL) OR
  (is_read = FALSE AND read_at IS NULL)
)

---

Función PostgreSQL: notify_job_update() (Fase 2)

Propósito: Enviar notificación NOTIFY cuando un job se actualiza

Trigger: AFTER UPDATE en background_jobs

SQL:

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

CREATE TRIGGER background_jobs_update_trigger
AFTER UPDATE ON background_jobs
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION notify_job_update();

Uso en PHP (Fase 2):

// JobStreamController escucha canal
$pdo->exec("LISTEN job_updates_{$jobId}");

while (true) {
    $notification = pg_get_notify($pdo);
    if ($notification) {
        echo "data: {$notification['payload']}\n\n";
        ob_flush(); flush();
    }
}

---

◄ Anterior: Arquitectura | Índice | Siguiente: API Endpoints ►