Multi-Tenant Architecture

┌─────────────────────────────────────────┐
│         Master Database                 │
│    (vigia_master or configured)         │
│                                         │
│  ┌─────────────────────────────────┐   │
│  │   clientes_saas                 │   │
│  │  - id, ruc, subdominio          │   │
│  │  - db_name, correo_acceso       │   │
│  │  - plan, limits, config         │   │
│  └─────────────────────────────────┘   │
└─────────────────────────────────────────┘
                 │
                 │ References
                 ├──────────────┬──────────────┬─────────────
                 │              │              │
                 ▼              ▼              ▼
    ┌──────────────────┐  ┌──────────────────┐  ┌──────────────────┐
    │ vigia_empresa1   │  │ vigia_empresa2   │  │ vigia_empresa3   │
    │                  │  │                  │  │                  │
    │ - users          │  │ - users          │  │ - users          │
    │ - icsr           │  │ - icsr           │  │ - icsr           │
    │ - products       │  │ - products       │  │ - products       │
    │ - ...            │  │ - ...            │  │ - ...            │
    └──────────────────┘  └──────────────────┘  └──────────────────┘

@property
def db_name(self) -> str:
    """Physical database name: vigia_{subdominio}"""
    return f"vigia_{(self.subdominio or '').strip().lower()}"

@property
def db_url(self) -> str:
    """Full connection string using TENANT_DB_TEMPLATE"""
    return settings.TENANT_DB_TEMPLATE.format(db_name=self.db_name)

@property
def hostname(self) -> str:
    """Full hostname: {subdominio}.{SAAS_BASE_DOMAIN}"""
    base = settings.SAAS_BASE_DOMAIN or ""
    if base:
        return f"{self.subdominio}.{base}"
    return self.subdominio

cliente = ClienteSaaS(
    ruc="20123456789",
    razon_social="Farmacia Mi Salud S.A.C.",
    subdominio="misalud",
    correo_acceso="[email protected]",
    contacto_nombre="Juan Pérez",
    plan="ilimitado",
    limitar_emision=False,
    entorno="produccion",
    is_active=True
)

# Computed values:
# cliente.db_name → "vigia_misalud"
# cliente.db_url → "postgresql+psycopg2://user:pass@localhost/vigia_misalud"
# cliente.hostname → "misalud.midominio.com"

# Master database (stores tenant registry)
MASTER_DATABASE_URL=postgresql+psycopg2://postgres:password@localhost:5432/vigia_master

# Template for tenant databases (must include {db_name} placeholder)
TENANT_DB_TEMPLATE=postgresql+psycopg2://postgres:password@localhost:5432/{db_name}

# Legacy/default database (for backward compatibility)
DATABASE_URL=postgresql+psycopg2://postgres:password@localhost:5432/vigiadb

# Base domain for tenant URLs
SAAS_BASE_DOMAIN=midominio.com
# Local development:
# SAAS_BASE_DOMAIN=localhost:5173

@field_validator("TENANT_DB_TEMPLATE", mode="after")
@classmethod
def _validate_tenant_template(cls, v, info):
    if not v:
        return v
    if "{db_name}" not in v:
        raise ValueError(
            "TENANT_DB_TEMPLATE debe contener el placeholder {db_name}, "
            "ej: postgresql+psycopg2://user:pass@localhost/{db_name}"
        )
    return v

def _create_database_if_not_exists(db_name: str) -> None:
    logger.info("[tenants] creating db=%s", db_name)
    create_sql = text(f'CREATE DATABASE "{db_name}"')
    
    try:
        with admin_engine.connect() as conn:
            conn.execute(create_sql)
        logger.info("[tenants] created db=%s", db_name)
    except ProgrammingError as e:
        if "already exists" in str(e).lower():
            logger.info("[tenants] db already exists db=%s", db_name)
        else:
            raise

def _init_tenant_schema(cliente: ClienteSaaS) -> None:
    logger.info("[tenants] init schema sub=%s db=%s", 
                cliente.subdominio, cliente.db_name)
    
    engine_tenant = create_engine(cliente.db_url, pool_pre_ping=True)
    
    try:
        # Create all tables from Base.metadata
        Base.metadata.create_all(bind=engine_tenant)
        logger.info("[tenants] schema ok db=%s", cliente.db_name)
    finally:
        engine_tenant.dispose()

def _create_tenant_admin_user(cliente: ClienteSaaS, password_plano: str) -> None:
    engine_tenant = create_engine(cliente.db_url, pool_pre_ping=True)
    SessionTenant = sessionmaker(bind=engine_tenant)
    db_tenant = SessionTenant()
    
    try:
        # Create or get admin role
        admin_role = db_tenant.query(Role).filter(
            Role.name == RoleEnum.admin.value
        ).one_or_none()
        
        if not admin_role:
            admin_role = Role(name=RoleEnum.admin.value)
            db_tenant.add(admin_role)
            db_tenant.flush()
        
        # Create admin user
        user = User(
            username=cliente.correo_acceso,
            email=cliente.correo_acceso,
            hashed_password=get_password_hash(password_plano),
            is_active=True,
            roles=[admin_role.name]
        )
        
        db_tenant.add(user)
        db_tenant.commit()
    finally:
        db_tenant.close()
        engine_tenant.dispose()

def provisionar_tenant(cliente: ClienteSaaS, 
                       password_inicial: Optional[str] = None) -> str:
    if not settings.TENANT_DB_TEMPLATE:
        raise RuntimeError("TENANT_DB_TEMPLATE no está configurado en .env")
    
    # Generate password if not provided
    password_plano = (
        password_inicial.strip() if password_inicial 
        else secrets.token_urlsafe(10)
    )
    
    # Execute provisioning steps
    _create_database_if_not_exists(cliente.db_name)
    _init_tenant_schema(cliente)
    _create_tenant_admin_user(cliente, password_plano)
    
    return password_plano

GET /admin/clientes/?skip=0&limit=100&include_inactive=true

{
  "total": 3,
  "items": [
    {
      "id": 1,
      "ruc": "20123456789",
      "nombre": "Farmacia Mi Salud S.A.C.",
      "subdominio": "misalud",
      "hostname": "misalud.midominio.com",
      "correo_acceso": "[email protected]",
      "entorno": "produccion",
      "activo": true,
      "created_at": "2024-01-15T10:30:00Z",
      "updated_at": "2024-01-15T10:30:00Z"
    }
  ]
}

POST /admin/clientes/
Content-Type: application/json

{
  "ruc": "20987654321",
  "nombre": "Laboratorio Pharma Plus",
  "subdominio": "pharmaplus",
  "correo_acceso": "[email protected]",
  "contacto_nombre": "María García",
  "plan": "ilimitado",
  "limitar_emision": false,
  "max_usuarios": null,
  "entorno": "demo",
  "activo": true,
  "password_inicial": "TempPass123!"
}

@router.post("/clientes/", response_model=ClienteOut, status_code=201)
def create_cliente(payload: ClienteCreate, db: Session = Depends(get_master_db)):
    sub = payload.subdominio.strip().lower()
    
    # Validate uniqueness
    if db.scalar(select(ClienteSaaS).where(ClienteSaaS.ruc == payload.ruc)):
        raise HTTPException(400, "Ya existe un cliente con ese RUC")
    if db.scalar(select(ClienteSaaS).where(ClienteSaaS.subdominio == sub)):
        raise HTTPException(400, "Ya existe un cliente con ese subdominio")
    
    # Create tenant record
    cliente = ClienteSaaS(
        ruc=payload.ruc,
        razon_social=payload.nombre,
        subdominio=sub,
        correo_acceso=payload.correo_acceso,
        # ... other fields
    )
    
    db.add(cliente)
    db.commit()
    db.refresh(cliente)
    
    # Provision database and admin user
    try:
        provisionar_tenant(cliente, password_inicial=payload.password_inicial)
    except Exception as e:
        raise HTTPException(500, detail=f"Error al provisionar tenant: {e}")
    
    return cliente

PUT /admin/clientes/{cliente_id}
Content-Type: application/json

{
  "plan": "premium",
  "max_usuarios": 50,
  "entorno": "produccion"
}

POST /admin/clientes/{cliente_id}/toggle

DELETE /admin/clientes/{cliente_id}

DELETE /admin/clientes/{cliente_id}?hard=true

@router.delete("/clientes/{cliente_id}", status_code=204)
def delete_cliente(
    cliente_id: int,
    hard: bool = Query(False),
    db: Session = Depends(get_master_db),
):
    cliente = db.get(ClienteSaaS, cliente_id)
    if not cliente:
        raise HTTPException(404, "Cliente no encontrado")
    
    if not hard:
        # Soft delete
        logger.info("[admin_clientes] soft delete id=%s sub=%s", 
                   cliente.id, cliente.subdominio)
        cliente.is_active = False
        db.commit()
        return Response(status_code=204)
    
    # Hard delete: drop database
    logger.warning("[admin_clientes] HARD DELETE id=%s sub=%s db=%s",
                  cliente.id, cliente.subdominio, cliente.db_name)
    
    try:
        eliminar_tenant(cliente, drop_db=True)
    except Exception as e:
        raise HTTPException(500, detail=f"No se pudo eliminar la BD: {e}")
    
    db.delete(cliente)
    db.commit()
    return Response(status_code=204)

def drop_database_if_exists(db_name: str) -> None:
    logger.info("[tenants] drop db request db=%s", db_name)
    
    # Check if exists
    exists_before = _db_exists(db_name)
    if not exists_before:
        logger.info("[tenants] db does not exist, skip db=%s", db_name)
        return
    
    try:
        # Terminate all connections
        killed = _terminate_db_connections(db_name)
        logger.info("[tenants] terminated connections=%s db=%s", killed, db_name)
        
        # Drop database
        with admin_engine.connect() as conn:
            conn.execute(text(f'DROP DATABASE IF EXISTS "{db_name}"'))
        
        # Verify deletion
        exists_after = _db_exists(db_name)
        if exists_after:
            raise RuntimeError(f"No se pudo eliminar la BD {db_name}")
        
        logger.info("[tenants] drop ok db=%s", db_name)
    except Exception as e:
        logger.exception("[tenants] drop failed db=%s err=%s", db_name, e)
        raise

def _terminate_db_connections(db_name: str) -> int:
    sql = text("""
        SELECT count(*)::int
        FROM pg_stat_activity
        WHERE datname = :db_name
          AND pid <> pg_backend_pid();
    """)
    
    kill_sql = text("""
        SELECT pg_terminate_backend(pid)
        FROM pg_stat_activity
        WHERE datname = :db_name
          AND pid <> pg_backend_pid();
    """)
    
    with admin_engine.connect() as conn:
        cnt = conn.execute(sql, {"db_name": db_name}).scalar() or 0
        if cnt:
            conn.execute(kill_sql, {"db_name": db_name})
        return int(cnt)

def get_tenant_session(cliente: ClienteSaaS) -> Session:
    """Session connected to tenant database"""
    engine = get_tenant_engine(cliente)
    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
    return SessionLocal()

# Cache engines to avoid recreation
_ENGINE_CACHE: Dict[str, Engine] = {}

def get_tenant_engine(cliente: ClienteSaaS) -> Engine:
    db_name = cliente.db_name
    engine = _ENGINE_CACHE.get(db_name)
    if engine is None:
        engine = create_engine(cliente.db_url, pool_pre_ping=True)
        _ENGINE_CACHE[db_name] = engine
    return engine

# From backend/app/routers/auth.py:128-146
def _tenant_from_request_or_token(request: Request, 
                                   token: Optional[str] = None) -> Optional[str]:
    # 1) X-Tenant header (highest priority)
    raw = request.headers.get("x-tenant")
    sub = (raw or "").strip().lower()
    if sub and sub not in LEGACY_TENANT_ALIASES:
        return sub
    
    # 2) JWT token claim
    if token:
        try:
            payload = jwt.decode(token, settings.SECRET_KEY, 
                               algorithms=[settings.ALGORITHM])
            t = (payload.get("tenant") or "").strip().lower()
            if t and t not in LEGACY_TENANT_ALIASES:
                return t
        except Exception:
            pass
    
    # 3) Legacy mode
    return None

# From backend/app/core/config.py:262-276
@field_validator("MASTER_DATABASE_URL", mode="after")
@classmethod
def _default_master_db(cls, v, info):
    """If MASTER_DATABASE_URL not set, use DATABASE_URL (legacy mode)"""
    if v:
        return v
    db_url = info.data.get("DATABASE_URL")
    if not db_url:
        raise ValueError("Must configure MASTER_DATABASE_URL or DATABASE_URL")
    return db_url