Tenant Isolation

Overview

Q01 Core APIs implement multi-tenant architecture where multiple tenants share the same database but data is completely isolated. Every record belongs to a specific tenant (identified by source field), and users can only access data from their own tenant.

Benefits:

✅ Complete data isolation (zero cross-tenant access)
✅ Single database (efficient resource usage)
✅ Automatic filtering (transparent to application code)
✅ Zero trust model (all queries filtered)
✅ Tenant-specific customization

Architecture:

Database (shared)
├── Tenant A (source='tenantA')
│   ├── Products (PRD_SOURCE='tenantA')
│   ├── Orders (ORD_SOURCE='tenantA')
│   └── Customers (CUST_SOURCE='tenantA')
├── Tenant B (source='tenantB')
│   ├── Products (PRD_SOURCE='tenantB')
│   ├── Orders (ORD_SOURCE='tenantB')
│   └── Customers (CUST_SOURCE='tenantB')
└── Tenant C (source='tenantC')
    ├── Products (PRD_SOURCE='tenantC')
    └── Orders (ORD_SOURCE='tenantC')

The `source` Field

What is `source`?

source is the tenant identifier that appears in every record and session context.

Examples:

productManagement - Product management application
warehouse - Warehouse management system
ecommerce - E-commerce storefront
storePos - Point-of-sale system
mobileApp - Mobile application

Source in Table Schema

Every dimension table includes source field:

CREATE TABLE TB_ANAG_PRD00 (
    PRD_ID VARCHAR(36) PRIMARY KEY,
    XPRD01 VARCHAR(255),              -- Product name
    XPRD02 DECIMAL(10,2),             -- Price
    PRD_SOURCE VARCHAR(50) NOT NULL,  -- ⭐ Tenant identifier
    PRD_CENTRO_DETT VARCHAR(50),      -- Organizational unit
    PRD_PESO VARCHAR(1),              -- User level
    PRD_AMBIENTE VARCHAR(20),         -- Environment
    TREC VARCHAR(1),
    CREATED_BY VARCHAR(255),
    CREATED_AT VARCHAR(14),
    UPDATED_BY VARCHAR(255),
    UPDATED_AT VARCHAR(14)
);

CREATE INDEX idx_prd_source ON TB_ANAG_PRD00(PRD_SOURCE);
CREATE INDEX idx_prd_tenant ON TB_ANAG_PRD00(PRD_SOURCE, PRD_CENTRO_DETT, PRD_AMBIENTE);

Naming Convention:

Field name: {DIM}_SOURCE
Examples: PRD_SOURCE, ORD_SOURCE, CUST_SOURCE

Source in Session Context

Source travels with every request:

{
  "user_id": "user@example.com",
  "tenant_id": "tenant_123",
  "source": "productManagement",
  "centro_dett": "admin",
  "peso": "1",
  "ambiente": "production",
  "grants": ["products.read", "products.write"]
}

Set during login:

POST /auth/login
{
  "username": "user@example.com",
  "password": "secure_password",
  "source": "productManagement",     # ⭐ Tenant identifier
  "centro_dett": "admin"
}

Automatic Tenant Filtering

Read Operations (GET)

Every query automatically filters by source:

-- User request: GET /api/v4/core/PRD

-- CoreQuery adds WHERE clause automatically
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'productManagement'  -- ⭐ Automatic filtering
  AND PRD_CENTRO_DETT = 'admin'
  AND PRD_AMBIENTE = 'production'
  AND TREC != 'C';

CoreQuery Implementation:

// Modules.php
public function buildQuery(string $dimension, array $sessionContext): string {
    $source = $sessionContext['source'];
    $centroDett = $sessionContext['centro_dett'];
    $ambiente = $sessionContext['ambiente'];

    $sql = "SELECT * FROM TB_ANAG_{$dimension}00 WHERE 1=1";

    // ⭐ Automatic tenant filtering
    $sql .= " AND {$dimension}_SOURCE = :source";
    $sql .= " AND {$dimension}_CENTRO_DETT = :centro_dett";
    $sql .= " AND {$dimension}_AMBIENTE = :ambiente";
    $sql .= " AND TREC != 'C'";

    return $sql;
}

Result:

User with source='tenantA' sees only records with PRD_SOURCE='tenantA'
User with source='tenantB' sees only records with PRD_SOURCE='tenantB'
No possibility of cross-tenant access

Write Operations (POST/PUT/PATCH)

Source automatically set on creation:

POST /api/v4/core/PRD
Authorization: Bearer {token with source='productManagement'}
{
  "data": {
    "XPRD01": "Widget Pro",
    "XPRD02": 99.99
    // PRD_SOURCE not specified by client
  }
}

CoreWrite adds source automatically:

// DataStore.php
public function insert(string $dimension, array $data): array {
    $sessionContext = $this->getSessionContext();

    // ⭐ Inject tenant fields automatically
    $data["{$dimension}_SOURCE"] = $sessionContext['source'];
    $data["{$dimension}_CENTRO_DETT"] = $sessionContext['centro_dett'];
    $data["{$dimension}_PESO"] = $sessionContext['peso'];
    $data["{$dimension}_AMBIENTE"] = $sessionContext['ambiente'];

    // Insert record
    return $this->executeInsert($dimension, $data);
}

Response:

{
  "status": "success",
  "data": {
    "PRD_ID": "uuid-...",
    "XPRD01": "Widget Pro",
    "XPRD02": 99.99,
    "PRD_SOURCE": "productManagement",  // ⭐ Automatically set
    "PRD_CENTRO_DETT": "admin",
    "PRD_PESO": "1",
    "PRD_AMBIENTE": "production",
    "TREC": "N"
  }
}

Updates also verify source:

-- PUT /api/v4/core/PRD/123
UPDATE TB_ANAG_PRD00
SET XPRD02 = 89.99
WHERE PRD_ID = '123'
  AND PRD_SOURCE = 'productManagement'  -- ⭐ Only update if same tenant
  AND PRD_CENTRO_DETT = 'admin'
  AND PRD_AMBIENTE = 'production';

-- If source doesn't match → 404 Not Found (not 403)

Cross-Tenant Access Prevention

Attempt to Access Other Tenant's Data

Scenario: User from tenantA tries to access tenantB record

# User session: source='tenantA'
GET /api/v4/core/PRD/123

# Record 123 belongs to tenantB (PRD_SOURCE='tenantB')

Query Executed:

SELECT * FROM TB_ANAG_PRD00
WHERE PRD_ID = '123'
  AND PRD_SOURCE = 'tenantA'  -- ⭐ No match!
  AND PRD_CENTRO_DETT = 'admin'
  AND PRD_AMBIENTE = 'production'
  AND TREC != 'C';

-- Returns: 0 rows

Response:

{
  "error": "NotFoundError",
  "message": "Product not found: 123",
  "code": "RECORD_NOT_FOUND",
  "status": 404
}

Result:

User receives 404 Not Found (not 403 Forbidden)
No indication that record exists in different tenant
Complete data isolation maintained

Attempt to Modify Source

Scenario: User tries to change source field

POST /api/v4/core/PRD
Authorization: Bearer {token with source='tenantA'}
{
  "data": {
    "XPRD01": "Widget",
    "PRD_SOURCE": "tenantB"  // ⭐ Trying to set different tenant
  }
}

CoreWrite Response:

{
  "error": "ValidationError",
  "message": "Field not allowed: PRD_SOURCE",
  "code": "FIELD_NOT_CREATEABLE",
  "status": 400,
  "field": "PRD_SOURCE"
}

Reason:

PRD_SOURCE has COD_ON_OFF without 'N' or 'M' flags
Field is read-only, automatically managed
User cannot override tenant isolation

Multi-Tenant Examples

Example 1: E-Commerce Platform

Scenario: Multiple stores on same platform

Database
├── Store A (source='storeA')
│   ├── Products (100 items)
│   ├── Orders (500 orders)
│   └── Customers (200 customers)
├── Store B (source='storeB')
│   ├── Products (80 items)
│   ├── Orders (300 orders)
│   └── Customers (150 customers)
└── Store C (source='storeC')
    ├── Products (120 items)
    └── Orders (400 orders)

User from Store A:

# Login with source='storeA'
POST /auth/login
{
  "username": "admin@storeA.com",
  "password": "...",
  "source": "storeA"
}

# Query products
GET /api/v4/core/PRD
Authorization: Bearer {token}

# Response: Only Store A's 100 products

Example 2: SaaS Application

Scenario: Multiple companies using same SaaS product

Database
├── Company X (source='companyX')
│   ├── Projects (50 projects)
│   └── Tasks (500 tasks)
├── Company Y (source='companyY')
│   ├── Projects (30 projects)
│   └── Tasks (300 tasks)
└── Company Z (source='companyZ')
    ├── Projects (70 projects)
    └── Tasks (800 tasks)

Complete Isolation:

Company X users see only Company X data
No cross-company data leakage
Each company's data customizable independently

Tenant-Specific Customization

Metadata Per Tenant

Different field configurations per tenant:

-- Store A: Price required
INSERT INTO TB_COST (COD_DIM, COD_VAR, REQUIRED, COD_ON_OFF, SOURCE)
VALUES ('PRD', 'XPRD02', '1', 'LDRNM', 'storeA');

-- Store B: Price optional
INSERT INTO TB_COST (COD_DIM, COD_VAR, REQUIRED, COD_ON_OFF, SOURCE)
VALUES ('PRD', 'XPRD02', '0', 'LDRNM', 'storeB');

Result:

Store A: Must provide price when creating products
Store B: Price is optional

Tenant-Specific Workflows

Different validation rules per tenant:

// Custom validation per tenant
public function validateProduct(array $data, string $source): void {
    switch ($source) {
        case 'storeA':
            // Store A requires SKU
            if (empty($data['XPRD03'])) {
                throw new ValidationException('SKU required for Store A');
            }
            break;

        case 'storeB':
            // Store B requires barcode
            if (empty($data['XPRD_BARCODE'])) {
                throw new ValidationException('Barcode required for Store B');
            }
            break;
    }
}

Source and Centro_Dett

Hierarchical Isolation

source + centro_dett provide two-level isolation:

Tenant (source='retailChain')
├── Headquarters (centro_dett='hq')
│   └── Can see all stores
├── Store 001 (centro_dett='store_001')
│   └── Can see only Store 001 data
├── Store 002 (centro_dett='store_002')
│   └── Can see only Store 002 data
└── Warehouse (centro_dett='warehouse')
    └── Can see only warehouse data

Query Example:

-- HQ user (can see all)
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'retailChain'
  AND PRD_CENTRO_DETT IN ('hq', 'store_001', 'store_002', 'warehouse')
  AND PRD_AMBIENTE = 'production';

-- Store 001 user (only their store)
SELECT * FROM TB_ANAG_PRD00
WHERE PRD_SOURCE = 'retailChain'
  AND PRD_CENTRO_DETT = 'store_001'  -- ⭐ Limited scope
  AND PRD_AMBIENTE = 'production';

Best Practices

✅ DO:

Always set source during login:

// ✅ Good - explicit tenant
await login(username, password, 'storeA', 'admin');

Trust automatic filtering:

// ✅ Good - Core APIs handle filtering
const products = await getProducts();
// Only current tenant's products returned

Use source for tenant-specific logic:

// ✅ Good - tenant customization
if (sessionContext.source === 'premiumStore') {
  enablePremiumFeatures();
}

Verify source in session context:

// ✅ Good - validate context
if (!sessionContext.source) {
  throw new Error('Missing tenant identifier');
}

❌ DON'T:

Don't try to override source:

// ❌ Bad - will be rejected
await createProduct({
  XPRD01: 'Widget',
  PRD_SOURCE: 'otherTenant'  // Validation error!
});

Don't query without authentication:

# ❌ Bad - no tenant context
GET /api/v4/core/PRD
# 401 Unauthorized

Don't assume cross-tenant access:

// ❌ Bad - will return 404
await getProduct(otherTenantProductId);

Don't hardcode source in code:

// ❌ Bad - inflexible
const source = 'storeA';

// ✅ Good - from session
const source = sessionContext.source;

Summary

✅ Multi-tenant architecture with single shared database
✅ source field identifies tenant for every record
✅ Automatic filtering on all queries (read and write)
✅ Complete data isolation (zero cross-tenant access)
✅ Source set automatically from session context
✅ Cannot override or modify source field
✅ Tenant-specific metadata and customization
✅ source + centro_dett provide hierarchical isolation

Key Takeaways:

Every record belongs to exactly one tenant (via source field)
Session context defines tenant for all operations
Core APIs automatically filter by source on every query
Cross-tenant access impossible (404 Not Found)
Source cannot be overridden by client
Same database, complete logical isolation
Tenant-specific customization via metadata

Tenant Isolation Flow:

Login with source='tenantA'
    ↓
JWT token includes source='tenantA'
    ↓
API request with token
    ↓
CoreQuery/CoreWrite extract source
    ↓
Automatic WHERE clause: SOURCE='tenantA'
    ↓
Only tenantA's data visible/accessible

Authentication - JWT tokens with source
Authorization - TB_MENU grants
Context Security - centro_dett/peso/ambiente
Security Overview - Multi-level security
MAP Framework - Metadata tables

Tenant Isolation

Overview​

The source Field​

What is source?​

Source in Table Schema​

Source in Session Context​

Automatic Tenant Filtering​

Read Operations (GET)​

Write Operations (POST/PUT/PATCH)​

Cross-Tenant Access Prevention​

Attempt to Access Other Tenant's Data​

Attempt to Modify Source​

Multi-Tenant Examples​

Example 1: E-Commerce Platform​

Example 2: SaaS Application​

Tenant-Specific Customization​

Metadata Per Tenant​

Tenant-Specific Workflows​

Source and Centro_Dett​

Hierarchical Isolation​

Best Practices​

✅ DO:​

❌ DON'T:​

Summary​

Related Concepts​

Overview

The `source` Field

What is `source`?

Source in Table Schema

Source in Session Context

Automatic Tenant Filtering

Read Operations (GET)

Write Operations (POST/PUT/PATCH)

Cross-Tenant Access Prevention

Attempt to Access Other Tenant's Data

Attempt to Modify Source

Multi-Tenant Examples

Example 1: E-Commerce Platform

Example 2: SaaS Application

Tenant-Specific Customization

Metadata Per Tenant

Tenant-Specific Workflows

Source and Centro_Dett

Hierarchical Isolation

Best Practices

✅ DO:

❌ DON'T:

Summary

Related Concepts