Fecha: 27 de Diciembre, 2025
Estado: NORMATIVO - INFRAESTRUCTURA MULTI-TENANT
Autor: Carlos Alberto Torres Camargo
database_config_v2Cada tenant tiene su propia BASE DE DATOS Oracle independiente.
La configuración se almacena en la tabla database_config_v2 (en SimappeAdmin), que contiene:
url: JDBC URL completa (jdbc:oracle:thin:@host:port:SID)username/password: Credenciales específicasdatabase: Nombre de la base de datos del tenantmaxPoolSize, minIdle, connectionTimeout: Configuración HikariCPOracle Server (10.120.0.2:1521)
├── TENANT_CENTRICA_PROD (database completa)
│ ├── BUSINESS_PARTNERS (tablespace/user)
│ ├── ACCOUNTING (tablespace/user)
│ └── INVENTORY (tablespace/user)
├── TENANT_ACME_PROD (database completa)
│ ├── BUSINESS_PARTNERS (tablespace/user)
│ ├── ACCOUNTING (tablespace/user)
│ └── INVENTORY (tablespace/user)
└── TENANT_XYZ_DEV (database completa)
├── BUSINESS_PARTNERS (tablespace/user)
└── ACCOUNTING (tablespace/user)
Ventajas:
// SimappeModel: DatabaseConfigV2Dto.java (CÓDIGO REAL)
package com.catcsoft.simappe.model.core.database.dto;
import com.catcsoft.simappe.model.core.database.enums.ConnectionContext;
import com.catcsoft.simappe.model.core.database.enums.ConnectionStatus;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DatabaseConfigV2Dto {
private Long id;
private String name;
private String code;
private String description;
// ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
// ⭐ CONFIGURACIÓN CRÍTICA DATABASE-BASED
// ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
private String url; // ← jdbc:oracle:thin:@10.120.0.2:1521:tenant_centrica_prod
private String username; // ← tenant_centrica_user
private String password; // ← encrypted_password
private String database; // ← tenant_centrica_prod
private String schema; // ← business_partners (schema dentro de tenant_centrica_prod)
// ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
private String type; // Oracle, MySQL, MongoDB
private String clientId; // ID del cliente
private String environment; // prod, dev, qa
@Builder.Default
private Integer maxPoolSize = 10;
@Builder.Default
private Integer minIdle = 5;
@Builder.Default
private Long connectionTimeout = 30000L;
private String driverClassName; // oracle.jdbc.OracleDriver
private String hibernateDialect; // org.hibernate.dialect.OracleDialect
private String host;
private Long port;
private ConnectionStatus status;
@Builder.Default
private ConnectionContext connectionContext = ConnectionContext.BUSINESS;
public boolean isValid() {
return url != null && !url.isEmpty()
&& username != null && !username.isEmpty()
&& password != null && !password.isEmpty();
}
}
// SimappeCommons: TenantDatabaseManager.java (CÓDIGO REAL líneas 273-308)
package com.catcsoft.simappe.commons.api.v1.core.multitenant;
import javax.sql.DataSource;
import com.catcsoft.simappe.model.core.database.dto.DatabaseConfigV2Dto;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
@Slf4j
@Component
public class TenantDatabaseManager {
@Autowired
private SimappeAdminClientService adminClientService;
@Autowired
private DataSource dataSource; // Este es TenantAwareDataSource
/**
* Inicializa conexión para un tenant basado en JWT.
* FLUJO:
* 1. Extrae tenantId del JWT (clientId_environment)
* 2. Obtiene DatabaseConfigV2Dto desde SimappeAdmin
* 3. Crea HikariDataSource con URL específica del tenant
* 4. Registra en TenantAwareDataSource
*/
public void initializeTenantConnection(String token) throws SimappeException {
try {
String tenantId = extractTenantId(token); // ← "123_prod"
log.debug("Processing tenant connection initialization for: {}", tenantId);
// Verificar si ya existe conexión válida (cache)
if (isConnectionStateValid(tenantId)) {
log.debug("Valid connection state found for tenant: {}", tenantId);
TenantContext.setCurrentTenant(tenantId);
return;
}
// Obtener configuración desde SimappeAdmin
var response = adminClientService.getConnection(token).block();
var config = SimappeUtilities.getInternalClientResponse(response);
if (!config.isValid()) {
throw new SimappeException("Invalid database configuration for tenant: " + tenantId);
}
// Setup del DataSource específico del tenant
setupTenantDataSource(tenantId, config);
} catch (Exception e) {
log.error("Failed to initialize tenant connection", e);
throw new SimappeException("Error initializing tenant connection", e);
}
}
/**
* Crea DataSource específico para un tenant.
* ⭐ AQUÍ SE CONFIGURA LA BASE DE DATOS INDEPENDIENTE
*/
private void setupTenantDataSource(String tenantId, DatabaseConfigV2Dto config) throws SimappeException {
if (dataSource instanceof TenantAwareDataSource) {
TenantAwareDataSource tenantAwareDataSource = (TenantAwareDataSource) dataSource;
// Crear HikariCP DataSource con configuración del tenant
DataSource tenantDataSource = createDataSource(config);
// Registrar en el mapa de datasources
tenantAwareDataSource.addTenantDataSource(tenantId, tenantDataSource);
log.info("Tenant connection initialized successfully for: {}", tenantId);
}
}
/**
* Crea HikariDataSource con configuración DATABASE-BASED.
* Cada tenant tiene su propia URL de base de datos.
*/
private DataSource createDataSource(DatabaseConfigV2Dto config) {
HikariDataSource ds = new HikariDataSource();
// ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
// ⭐ CONFIGURACIÓN DATABASE-BASED
// URL puede apuntar a diferentes hosts/puertos/databases
// ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ds.setJdbcUrl(config.getUrl()); // jdbc:oracle:thin:@10.120.0.2:1521:tenant_centrica_prod
ds.setUsername(config.getUsername());
ds.setPassword(config.getPassword());
// Configuración del pool de conexiones
ds.setMaximumPoolSize(config.getMaxPoolSize() == null ? 10 : config.getMaxPoolSize());
ds.setMinimumIdle(config.getMinIdle() == null ? 5 : config.getMinIdle());
ds.setConnectionTimeout(config.getConnectionTimeout() == null ? 30000 : config.getConnectionTimeout());
// Schema dentro de la base de datos del tenant
ds.setSchema(config.getSchema()); // business_partners, accounting, etc.
return ds;
}
/**
* Extrae tenantId del JWT.
* Formato: {clientId}_{environment}
* Ejemplo: 123_prod, 456_dev
*/
public String extractTenantId(String token) throws SimappeException {
try {
var userSession = SimappeJwt.getInstance().getUserSession(token);
String clientId = userSession.getCustomerId().toString();
String environment = userSession.getEnvironment(); // prod, dev, qa
if (clientId == null || environment == null) {
throw new SimappeException("Invalid tenant information in token");
}
return String.format("%s_%s", clientId.toLowerCase(), environment.toLowerCase());
} catch (Exception e) {
throw new SimappeException("Error extracting tenant ID from token", e);
}
}
}
Paso 1: Request llega con JWT
POST /api/v1/tercero/query HTTP/1.1
Host: 10.120.0.2:8090
Authorization: Bearer eyJh...
Content-Type: application/json
{
"filters": [{"field": "razonSocial", "operator": "LIKE", "value": "ACME"}],
"page": 0,
"size": 20
}
Paso 2: TenantDatabaseManager extrae tenant del JWT
// JWT Claims:
{
"sub": "usuario@centrica.com",
"customerId": 123, // ← clientId
"environment": "prod", // ← environment
"roles": ["ADMIN"],
"exp": 1735349044
}
// tenantId = "123_prod"
Paso 3: Consulta a SimappeAdmin /get-connection
GET /simappe-admin/api/v1/database-config/get-connection HTTP/1.1
Host: simappe-admin-server:9010
Authorization: Bearer eyJh...
Response:
{
"success": true,
"data": {
"id": 1,
"name": "Centrica Prod DB",
"code": "CENTRICA_PROD",
"url": "jdbc:oracle:thin:@10.120.0.2:1521:tenant_123_prod",
"username": "user_centrica",
"password": "encrypted_xyz",
"database": "tenant_123_prod",
"schema": "business_partners",
"maxPoolSize": 20,
"minIdle": 5,
"connectionTimeout": 30000,
"clientId": "123",
"environment": "prod",
"status": "ACTIVE"
}
}
Paso 4: Creación de HikariDataSource
HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl("jdbc:oracle:thin:@10.120.0.2:1521:tenant_123_prod"); // ← BASE DATOS ESPECÍFICA
ds.setUsername("user_centrica");
ds.setPassword("encrypted_xyz");
ds.setSchema("business_partners");
ds.setMaximumPoolSize(20);
ds.setMinimumIdle(5);
ds.setConnectionTimeout(30000);
// Registrar en TenantAwareDataSource
tenantAwareDataSource.addTenantDataSource("123_prod", ds);
Paso 5: Query ejecutada
-- Oracle recibe:
-- 1. Conexión a base de datos tenant_123_prod
-- 2. Schema set a BUSINESS_PARTNERS
ALTER SESSION SET CURRENT_SCHEMA = BUSINESS_PARTNERS;
SELECT t.*
FROM tercero t
WHERE t.razon_social LIKE '%ACME%'
AND t.activo = true
ORDER BY t.razon_social ASC
LIMIT 20 OFFSET 0;
database_config_v2 EN SIMAPPE_ADMINCREATE TABLE admin.database_config_v2 (
id BIGSERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
code VARCHAR(50) NOT NULL UNIQUE,
description TEXT,
-- Conexión DATABASE-BASED (URLs apuntan a Oracle para tenants)
url VARCHAR(500) NOT NULL, -- JDBC URL completa (ej: jdbc:oracle:thin:@host:1521:SID)
username VARCHAR(100) NOT NULL,
password VARCHAR(255) NOT NULL, -- Encriptada
database VARCHAR(100),
type VARCHAR(50), -- Oracle, MySQL, MongoDB
-- Multi-tenancy
client_id BIGINT NOT NULL,
company_id BIGINT,
environment VARCHAR(20) NOT NULL, -- prod, dev, qa
-- HikariCP Config
max_pool_size INT DEFAULT 10,
min_idle INT DEFAULT 5,
connection_timeout BIGINT DEFAULT 30000,
-- Driver
driver_class_name VARCHAR(255),
hibernate_dialect VARCHAR(255),
host VARCHAR(255),
port BIGINT,
-- Status
status VARCHAR(20) DEFAULT 'ACTIVE',
connection_context VARCHAR(20) DEFAULT 'BUSINESS',
-- Audit
created_by BIGINT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_by BIGINT,
updated_at TIMESTAMP,
CONSTRAINT uk_client_env UNIQUE(client_id, environment)
);
CREATE INDEX idx_dbconfig_client_env ON admin.database_config_v2(client_id, environment);
CREATE INDEX idx_dbconfig_status ON admin.database_config_v2(status);
INSERT INTO admin.database_config_v2 (
name, code, url, username, password, database,
client_id, environment, max_pool_size, status
) VALUES
-- Tenant Centrica Producción (Oracle)
('Centrica Prod DB', 'CENTRICA_PROD',
'jdbc:oracle:thin:@10.120.0.2:1521:TENANT_CENTRICA_PROD',
'user_centrica', 'encrypted_password_1',
'TENANT_CENTRICA_PROD',
123, 'prod', 20, 'ACTIVE'),
-- Tenant ACME Producción (diferente servidor Oracle)
('ACME Prod DB', 'ACME_PROD',
'jdbc:oracle:thin:@10.120.0.3:1521:TENANT_ACME_PROD',
'user_acme', 'encrypted_password_2',
'TENANT_ACME_PROD',
456, 'prod', 15, 'ACTIVE'),
-- Tenant Centrica Desarrollo (mismo servidor Oracle, diferente DB)
('Centrica Dev DB', 'CENTRICA_DEV',
'jdbc:oracle:thin:@10.120.0.2:1521:TENANT_CENTRICA_DEV',
'user_centrica_dev', 'encrypted_password_3',
'TENANT_CENTRICA_DEV',
123, 'dev', 10, 'ACTIVE');
┌─────────────────────────────────────────────────┐
│ SimappeAdmin (CONFIG DB - PostgreSQL) │
│ 10.120.0.2:5432/simappe_admin │
│ │
│ database_config_v2: │
│ ├─ tenant_123_prod → 10.120.0.2:1521 │
│ ├─ tenant_456_prod → 10.120.0.3:1521 │
│ └─ tenant_789_prod → 10.120.0.4:1521 │
└─────────────────────────────────────────────────┘
↓ consulta DatabaseConfigV2Dto
┌──────────────────────────┬────────────────────────┬──────────────────────┐
│ Oracle Server 1 │ Oracle Server 2 │ Oracle Server 3 │
│ 10.120.0.2:1521 │ 10.120.0.3:1521 │ 10.120.0.4:1521 │
│ │ │ │
│ ├─ tenant_123_prod │ ├─ tenant_456_prod │ ├─ tenant_789_prod │
│ ├─ tenant_124_prod │ ├─ tenant_457_prod │ └─ tenant_790_prod │
│ └─ tenant_125_prod │ └─ tenant_458_prod │ │
└──────────────────────────┴────────────────────────┴──────────────────────┘
| Aspecto | Beneficio |
|---|---|
| Escalabilidad | Ilimitada - agregar nuevos servidores Oracle |
| Performance | Aislamiento total - un tenant no afecta a otros |
| Backup/Restore | Independiente por tenant - Oracle Data Pump Export/Import |
| Migraciones | Flyway por tenant - no afecta a otros |
| Seguridad | Credenciales diferentes por tenant |
| Costos | Optimizar recursos por tier (Premium → más RAM, Free → compartido) |
// SimappeAdmin: DatabaseConfigController.java
@RestController
@RequestMapping("/api/v1/database-config")
public class DatabaseConfigController {
@Autowired
private DatabaseConfigService service;
@PostMapping("/create-tenant-database")
@Operation(summary = "Crear nueva base de datos para tenant")
public ResponseEntity<DatabaseConfigV2Dto> createTenantDatabase(
@RequestBody TenantDatabaseCreationRequest request) {
// 1. Validar request
validateRequest(request);
// 2. Crear base de datos Oracle
String dbName = String.format("TENANT_%s_%s",
request.getClientId().toUpperCase(),
request.getEnvironment().toUpperCase());
createOracleDatabase(dbName, request.getServerHost());
// 3. Ejecutar migraciones Flyway
runFlywayMigrations(dbName, request.getServerHost());
// 4. Crear registro en database_config_v2
DatabaseConfigV2Dto config = DatabaseConfigV2Dto.builder()
.name(request.getTenantName())
.code(request.getCode())
.url(String.format("jdbc:oracle:thin:@%s:1521:%s",
request.getServerHost(), dbName))
.username(generateUsername(request.getClientId()))
.password(encryptPassword(generatePassword()))
.database(dbName)
.schema("business_partners")
.clientId(request.getClientId().toString())
.environment(request.getEnvironment())
.maxPoolSize(request.getMaxPoolSize())
.status(ConnectionStatus.ACTIVE)
.build();
DatabaseConfigV2Dto saved = service.save(config);
return ResponseEntity.ok(saved);
}
private void createOracleDatabase(String dbName, String host) {
// SQL Oracle:
// CREATE TABLESPACE tenant_123_prod DATAFILE 'tenant_123_prod.dbf' SIZE 500M;
// CREATE USER user_123 IDENTIFIED BY 'xxx' DEFAULT TABLESPACE tenant_123_prod;
// GRANT CONNECT, RESOURCE TO user_123;
}
}
-- Ver todas las configuraciones de tenants
SELECT
id, name, code, url, database, schema, environment, status,
max_pool_size, client_id
FROM admin.database_config_v2
WHERE status = 'ACTIVE'
ORDER BY client_id, environment;
// TenantAwareDataSource expone métricas
@Autowired
private TenantAwareDataSource tenantAwareDataSource;
public void logActivePools() {
Map<Object, Object> pools = tenantAwareDataSource.getResolvedDataSources();
pools.forEach((tenantId, dataSource) -> {
if (dataSource instanceof HikariDataSource) {
HikariDataSource hikari = (HikariDataSource) dataSource;
HikariPoolMX pool = hikari.getHikariPoolMXBean();
log.info("Tenant: {}, Active: {}, Idle: {}, Total: {}, Waiting: {}",
tenantId,
pool.getActiveConnections(),
pool.getIdleConnections(),
pool.getTotalConnections(),
pool.getThreadsAwaitingConnection());
}
});
}
Simappe usa DATABASE-BASED multi-tenancy porque:
database_config_v2 almacena URL completa por tenantTenantDatabaseManager.createDataSource() crea HikariCP con URL específicaNO es schema-based porque:
ALTER SESSION SET CURRENT_SCHEMA para cambiar schema como mecanismo de tenancydatabase_config_v2 apunta a databases diferentesDocumento Normativo - Multi-Tenancy DATABASE-Based v1.0 (CORREGIDO)
| Version | Fecha | Autor | Descripcion |
|---|---|---|---|
| 1.1.0 | 2026-03-04 | Carlos Torres | Revision, sanitizacion y publicacion en Wiki Arquitectura Centrica. |
| 1.0.0 | 2025-12-27 | Carlos Torres | Creacion del documento. |