ADR-002: Tablas por Schema (Multi-Tenant)

Fecha: 2026-02-05 Estado: Enmendado (2026-02-24) Deciders: Architecture Team, Security Team

ENMIENDA 2026-02-24: La decision original (Opcion A: LEVEL_SUCURSAL) fue revertida. Se adopto una tabla central en DB_INI (schema public) con tupla de identidad multi-tenant (nro_sistema, user_id, db, schema, prueba). Ver seccion Enmienda al final.

Contexto y Problema

Sistema Bautista usa PostgreSQL schema-based multi-tenancy:

• Cada sucursal tiene su schema: suc0001, suc0002, etc.
• Cada caja tiene su schema: suc0001caja001, suc0001caja002, etc.
• Aislamiento completo de datos por schema

Necesidad: Tabla background_jobs debe respetar aislamiento multi-tenant porque:

• Jobs contienen datos sensibles (ej: IDs de clientes, montos de facturas)
• Sucursal A NO debe ver jobs de sucursal B (seguridad)
• Violación de aislamiento = vulnerabilidad de seguridad crítica

Pregunta: ¿En qué nivel crear la tabla background_jobs?

Opciones Consideradas

Opción A: LEVEL_SUCURSAL (SELECCIONADA)

Descripción:

• Tabla background_jobs se crea en CADA schema de sucursal
• suc0001.background_jobs, suc0002.background_jobs, etc.
• Aislamiento automático por search_path de PostgreSQL

Pros:

• ✅ Aislamiento completo (sucursal A no ve jobs de B)
• ✅ Consistente con arquitectura existente
• ✅ CERO queries cross-schema accidentales
• ✅ Seguridad por diseño (imposible acceder a otro schema sin cambiar search_path)
• ✅ Permisos de DB enforzados automáticamente

Contras:

• ❌ NO hay vista consolidada de todos los jobs (admin global)
• ❌ Cada schema tiene su propia tabla (más rows totales en cluster)
• ❌ Queries analíticas cross-tenant requieren UNION de múltiples schemas

---

Opción B: LEVEL_EMPRESA con columna sucursal_id

Descripción:

• Tabla public.background_jobs única para toda la empresa
• Columna sucursal_id identifica a qué sucursal pertenece el job
• Filtro WHERE sucursal_id = :current_sucursal en TODAS las queries

Pros:

• ✅ Vista consolidada fácil (1 query para todos los jobs)
• ✅ Menos tablas totales
• ✅ Queries analíticas simples

Contras:

• ❌ VIOLA aislamiento multi-tenant (todas las sucursales en misma tabla)
• ❌ Filtro WHERE sucursal_id debe estar en TODAS las queries (propenso a errores)
• ❌ Olvido de filtro = data leakage CRÍTICO
• ❌ Permisos de DB NO enfuerzan aislamiento (depende de código)
• ❌ Testing complejo (simular multi-tenant con mocks)

Veredicto: ❌ Descartado (viola principio de aislamiento)

---

Opción C: Row Level Security (RLS)

Descripción:

• Tabla public.background_jobs única
• PostgreSQL RLS policy: CREATE POLICY ... USING (schema = current_setting('app.current_schema'))
• DB enforza aislamiento automáticamente

Pros:

• ✅ Vista consolidada fácil
• ✅ Aislamiento enforzado por DB (no depende de código)
• ✅ Una sola tabla

Contras:

• ❌ Performance overhead (RLS evalúa policy en cada query)
• ❌ Complejidad adicional (configurar session var app.current_schema)
• ❌ Debugging difícil (policies ocultas en queries)
• ❌ NO consistente con arquitectura existente (resto del sistema NO usa RLS)

Veredicto: ❌ Descartado (complejidad sin suficiente beneficio)

---

Decisión

Seleccionamos Opción A: LEVEL_SUCURSAL

Justificación:

• Consistente con arquitectura multi-tenant existente (TODAS las tablas transaccionales son LEVEL_SUCURSAL)
• Aislamiento garantizado por diseño (imposible acceder a otro schema sin cambiar search_path explícitamente)
• Menos propenso a errores (NO depende de filtros WHERE en código)
• Security-first approach (mejor aislamiento que facilidad de queries analíticas)

Consecuencias

Positivas

• ✅ Aislamiento completo de datos entre sucursales
• ✅ Consistente con patrón existente (facturacion, movimientos, etc.)
• ✅ Seguridad por diseño (no depende de código)
• ✅ Tests de multi-tenancy simples (cambiar schema, verificar aislamiento)

Negativas

• ❌ Dashboard global de jobs requiere UNION de múltiples schemas
• ❌ Cada sucursal tiene su tabla (más overhead de storage)

Mitigaciones

Mitigaciones de negativos:

1. Dashboard global: Implementar vista consolidada si es necesario (Fase 3)

   SELECT * FROM suc0001.background_jobs
   UNION ALL
   SELECT * FROM suc0002.background_jobs
   -- etc.

2. Storage overhead: Insignificante comparado con tablas transaccionales (jobs se archivan después de 30 días)

Implementación

Migration:

class CreateBackgroundJobsTable extends ConfigurableMigration
{
    protected function getDefaultLevels(): array
    {
        return [self::LEVEL_SUCURSAL]; // CRÍTICO
    }

    public function change(): void
    {
        $table = $this->table('background_jobs');
        $table->addColumn('type', 'string')
              ->addColumn('status', 'string')
              ->addColumn('payload', 'json')
              ->addColumn('schema', 'string') // Para ejecutar en schema correcto
              // ...
              ->create();
    }
}

Campo schema CRÍTICO:

• Guarda el schema PostgreSQL donde debe ejecutarse el job
• Worker configura search_path ANTES de ejecutar handler
• Ver ADR-005: Schema Isolation en Background para detalles completos

---

Enmienda 2026-02-24: Migracion a tabla central en DB_INI

Contexto del cambio

La decision original (Opcion A: LEVEL_SUCURSAL) fue implementada inicialmente, pero durante el desarrollo se identificaron problemas practicos significativos:

1. Queries consolidadas complejas: Obtener una vista global de todos los jobs requeria UNION ALL cross-schema dinamico, generando SQL pesado y dificil de mantener.
2. Notificaciones cross-tenant: El sistema de notificaciones necesitaba operar a nivel de usuario global (un usuario puede operar en multiples sucursales), lo cual era incompatible con tablas aisladas por schema.
3. Simplicidad operativa: Monitoreo, limpieza de jobs antiguos y reportes administrativos se simplificaban drasticamente con una tabla unica.

Nueva decision: Tabla central en DB_INI

Se reemplaza LEVEL_SUCURSAL por una tabla unica en DB_INI.public para ambas tablas (background_jobs y notifications).

Aislamiento multi-tenant: En lugar de depender del schema PostgreSQL para aislar datos, se usa una tupla de identidad multi-tenant compuesta por 5 campos:

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

Seguridad del aislamiento:

• El indice compuesto (nro_sistema, user_id, status) en background_jobs y (nro_sistema, user_id, db, schema, prueba, is_read) en notifications garantizan queries eficientes filtradas por tenant.
• Los repositorios (JobRepository, NotificationRepository) SIEMPRE filtran por nro_sistema + user_id + db + schema + prueba, haciendo imposible el acceso accidental a datos de otro tenant.
• La conexion se obtiene via ConnectionManager::getDbal('ini'), apuntando directamente a DB_INI.

Migraciones implementadas

• 20260224000001_create_background_jobs_central.php - Crea background_jobs en DB_INI.public
• 20260224000002_create_notifications_central.php - Crea notifications en DB_INI.public

Ambas son migraciones de tipo system/ (no tenancy/), ejecutandose una unica vez en la base de datos central.

Comparacion con decision original

Aspecto	LEVEL_SUCURSAL (original)	DB_INI central (actual)
Aislamiento	Por schema PostgreSQL	Por tupla de identidad en codigo + indices
Vista consolidada	UNION ALL cross-schema	Query directa con filtros
Complejidad queries	Alta (SQL dinamico)	Baja (tabla unica)
Notificaciones cross-sucursal	Complejo	Directo
Monitoreo/admin	Requiere recorrer schemas	Query centralizada
Riesgo de data leakage	Bajo (schema enforza)	Mitigado por repositorios + indices

Justificacion

El cambio se justifica porque:

1. Los beneficios de la tabla central superan los riesgos: La complejidad de UNION cross-schema para un sistema de jobs/notificaciones (que necesita vistas consolidadas frecuentemente) era mayor que el riesgo mitigable de una tabla central con filtros correctos.
2. Los repositorios centralizan el acceso: JobRepository y NotificationRepository encapsulan TODOS los accesos a estas tablas, reduciendo el riesgo de queries sin filtro.
3. Los campos nro_sistema y prueba aportan una dimension de aislamiento adicional que no existia en el diseno original, permitiendo distinguir entre sistemas/empresas y entre modo prueba/oficial.

Referencias

• Architecture: Background Jobs
• Backend: Background Jobs
• Backend: Base de Datos (especificacion actualizada)
• ADR-005: Schema Isolation en Background
• Architecture: Multi-Tenant Database
• PostgreSQL Schema Documentation
• Multi-Tenant Architecture Patterns