Multi-tenant

Multi-tenant - documentazione operativa e tecnica

advancedstable

Overview

Multi-tenant

Per-user connection routing: each tenant has its own connection strings to separate metadata and data DBs, while preserving full backward compatibility for existing single-tenant installations.

Scope

The multi-tenant module allows a single backend to serve multiple tenants — each on distinct metadata and data DBs — without duplicating code or starting separate instances. When the flag is OFF behavior is identical to pre-multi-tenant (zero overhead, caches unchanged). When ON the framework:

  • resolves the right connection string at runtime based on the logged-in user's id_azienda.
  • segregates server caches (Application[]) and client caches (Dexie + localStorage) per tenant.
  • propagates TenantScope.Current across all queries, scheduler and background jobs.

Enablement

In appsettings.json (AppSettings section):

  • "multiConnectionEnabled": "true" — activates tenant-aware routing in MultiTenantHelpers + TenantScope middleware in Startup.cs.
  • "enableCookieAuthentication": "true" — required by the multi-tenant flow: the k-user v:2 cookie carries azienda_id + azienda_id_user for anti-hijack.

Stand-alone templates appsettings.multi-tenant.mssql.json and appsettings.multi-tenant.mysql.json are available (6 connection strings: 2 default + 4 tenant Tenant1_Meta/Tenant1_Data/Tenant2_Meta/Tenant2_Data).

Data model

The id_azienda → (data, meta) mapping lives on two columns of the Aziende table on the primary metadata DB:

  • Aziende.Connessione_DB_Dati: NAME of an entry in appsettings.ConnectionStrings for the tenant data DB (NOT the literal connection string).
  • Aziende.CONNESSIONE_DB_Meta: NAME of an entry in appsettings.ConnectionStrings for the tenant metadata DB.

Example population:

  • Aziende.ID_Azienda=1, Connessione_DB_Dati='Tenant1_Data', CONNESSIONE_DB_Meta='Tenant1_Meta'.
  • Aziende.ID_Azienda=100 (Sede principale), Connessione_DB_Dati='DataSQLConnection', CONNESSIONE_DB_Meta='MetaDataSQLConnection' (default aliases → covered by scope 0).

Entry-name indirection:

  • no clear-text passwords in the DB.
  • credentials portable per environment (same DB, different appsettings.<env>.json).
  • consistent with _metadati__tabelle.md_conn_name.

Login fallback

When a user does NOT exist on the primary metadata DB, login falls back via _login_index:

1. user lookup on MetaDataSQLConnection (primary) → found? login.

2. miss → query _login_index (SHA-256(LOWER(username)) → id_azienda).

3. for each candidate id_azienda: push TenantScope, retry metaQuery.login on the tenant metadata DB.

4. first match wins → cookie issued with the tenant's azienda_id.

The sp__login_index_upsert(@username, @idAzienda) stored procedure populates the index. SHA-256 hash computed server-side (aligned with C# client MultiTenantHelpers.ComputeUsernameHash on UTF-8).

Tenant switch (superadmin)

Superadmins can change the active tenant at runtime via MetaService.switchAzienda(idAzienda):

  • 401 if not superadmin.
  • 400 if idAzienda doesn't have both Aziende.Connessione_DB_Dati / CONNESSIONE_DB_Meta columns populated.
  • re-emits cookie with updated azienda_id, preserving azienda_id_user (snapshot at login).
  • Angular component <wuic-azienda-switcher> exposes the dropdown UI. Self-hides via @HostBinding('attr.hidden') for non-super users or flag OFF.

Tenant-scoped caches

Server-side (Application[] keys):

  • MultiTenantHelpers.TenantKey(baseKey) appends __a<id> suffix when flag ON and TenantScope.Current.AziendaId > 0.
  • affected keys: userList, roleList, userRoleList, companyList, storedMeta, storedTableMeta, storedTableActionMeta, storedConditionGroupMeta, storedTableStyleMeta, SysInfo.

Client-side (Angular framework lib):

  • Dexie name → MetaDB__a<id> and WuicClientSideCrudDB__a<id>.
  • localStorage keys → menu_<userId>__a<id>, wuic_custom_settings_<userId>__a<id>, etc.
  • orphan cleanup at MetadataProviderService init: enumerates indexedDB.databases() and drops DBs of tenants no longer current.

When flag OFF: TenantKey returns the baseKey unchanged → caches identical to pre-multi-tenant.

Anti-hijack

RawHelpers.authenticate() on every request:

  • requires cookie v:2 with azienda_id + azienda_id_user (legacy cookie → forced relogin).
  • for non-super users, cookie.azienda_id MUST match cookie.azienda_id_user (tampering → relogin + security log).
  • superadmins are exempt (their azienda_id legitimately changes via switchAzienda).
  • populates TenantScope.Current with (azienda_id, isSuperAdmin) for the rest of the request.

The HydrateLegacyPrincipalFromKUserCookie middleware in Startup.cs also populates TenantScope for AsmxProxy endpoints that don't call authenticate() explicitly (e.g. readCustomSettings, getMenuByUserID).

Tenant-aware scheduler

SchedulerHostedService enumerates tenants on every cycle (Scheduler:PollSeconds, default 15s):

  • scope 0 (primary / no-tenant) — always processed.
  • plus one scope per tenant with conn mapping ≠ default.
  • tenants mapped to default entries (MetaDataSQLConnection) are skipped (covered by scope 0).

All 4 action types are supported per-tenant:

  • 1 | sql: data connection via tenant-aware routing.
  • 2 | webservice: __scheduler user created in each tenant DB; per-tenant session cookie via switchAzienda HTTP call.
  • 3 | assembly method: TenantScope.Current propagated via AsyncLocal to the invoked method.
  • 4 | mailing: _mailing_lists / _mail_recipients read from the current tenant DB; SMTP settings overridable per-tenant via naming convention <key>__a<id> (e.g. email-host__a1).

The scheduler schema (scheduler, scheduler_execution) is validated per-tenant: tenants missing the tables are silently skipped until restart.

Cross-tenant scaffolding propagation

When a superadmin scaffolds a table or view on one tenant, by default the metadata (_metadati__tabelle + _metadati__colonne) is written only to the current tenant's metadata DB. To avoid manually repeating the scaffolding on every tenant, the scaffolding.scaffoldTable / scaffolding.scaffoldView endpoint exposes the boolean parameter propagateToTenants:

  • false (default): scaffold only on the current tenant (back-compat).
  • true: after scaffolding the current tenant, the backend iterates over all other active tenants (MultiTenantHelpers.GetAziendePrimary()) and replicates the scaffolding on each one's metadata DB. The schema is read from the target tenant's DATA DB resolved via Aziende.Connessione_DB_Dati. Tenants without the physical table are skipped with outcome SKIPPED:not-found → best-effort operation, not cross-tenant transactional.

Security:

  • gated to superadmin with multiConnectionEnabled=true flag — for others the parameter is silently ignored.
  • companies mapped to default entries (MetaDataSQLConnection) are skipped (covered by scope 0 of the current tenant if the superadmin is already there).

The scaffold result includes tenant_<id>: keys with value OK / SKIPPED:<reason> / FAILED:<message> for per-tenant diagnosis.

UI: the scaffolding dialog (md_id=1556) exposes a Propagate to all tenants checkbox on the form; the Scaffold Table action sends the flag to the backend. In single-tenant mode (multiConnectionEnabled=false) the backend filters the column out of the getTableMetadata response → the checkbox does not appear in the form (no useless UI to handle).

Non-superadmin user accounts across multiple tenants

The <wuic-azienda-switcher> selector is visible only to superadmins. For non-superadmin users the anti-hijack check (cookie.azienda_id != user.azienda_id_user → reject) prevents changing tenant at runtime: the cookie has azienda_id_user snapshotted at login and immutable.

Operational convention for scenarios where the same person needs to access multiple tenants as a standard user:

  • create a separate user per tenant, with a distinct username (e.g. mario.rossi@T1, mario.rossi@T2, or mario.rossi.t1 / mario.rossi.t2).
  • each user lives in utenti of its own tenant DB, with matching id_azienda.
  • _login_index maps each username to its id_azienda: no ambiguity in the fallback chain.
  • the user picks which tenant to access by changing username at login.

Anti-pattern to avoid:

  • same username on two tenants: the fallback login tries both _login_index candidates in ascending id_azienda order; the first one that validates the password wins. The user always lands on the tenant with the lowest id_azienda and cannot reach the other without the switcher widget. The password remains shared cross-tenant, exposed to brute-force on both DBs.

If the person is a superadmin, a single user on the primary DB (id_azienda = parent company id) is enough: the switcher widget covers runtime tenant changes.

Known limits

  • Uniform DBMS cross-tenant: all tenants MUST share the same DBMS as the primary (AppSettings.dbms / meta-dbms). The tenant-aware routing only routes the catalog (which DB), not which provider. Example: MSSQL backend + a Tenant2_Meta with a MySQL connection string → SqlException on first tenant access. A fail-soft validation (WARN log) runs at startup (MultiTenantHelpers.ValidateTenantConnectionStrings): for each cs referenced by Aziende.{Connessione_DB_Dati,CONNESSIONE_DB_Meta} it heuristically detects the target DBMS (keywords like Port=3306, Initial Catalog=, (HOST=...), etc.) and logs a warning if it differs from the configured one. Mismatch → boot proceeds but the mismatched tenant is unusable at runtime.
  • the __scheduler user created on tenant DBs has id_azienda=NULL (not populated): adequate for the webservice flow (login always starts on primary) but not resolvable via _login_index for reverse cases.
  • each new tenant requires an entry in appsettings.ConnectionStrings + redeploy/reload config.
  • scheduler jobs in tenants without the scheduler table are silently skipped (info-log at first poll).

Relevant AppSettings

Snippet (multi-tenant keys):

  • "AppSettings.multiConnectionEnabled": "true"
  • "AppSettings.enableCookieAuthentication": "true"
  • "ConnectionStrings.MetaDataSQLConnection": "..."
  • "ConnectionStrings.DataSQLConnection": "..."
  • "ConnectionStrings.Tenant1_Meta": "..."
  • "ConnectionStrings.Tenant1_Data": "..."
  • "ConnectionStrings.Tenant2_Meta": "..."
  • "ConnectionStrings.Tenant2_Data": "..."
  • (optional) "AppSettings.email-host__a1": "smtp.tenant1.example" — SMTP override for tenant 1.