Tenant, Users & Roles Configuration Documentation

Module: Multi-Tenant Isolation, Users, and Role-Based Access Control
Version: 1.0
Last Updated: March 12, 2026

---

📌 Overview

Tenant & User Management handles the core access control and multi-tenant isolation in Axiom Genesis. It defines:

• Tenants: isolated customer environments with separate data
• Users: user accounts with authentication and profile data
• Roles: permissions and access control groups
• Subscriptions: feature licensing and entitlements
• Tenant Isolation: enforced separation of data across all tables

Core Tables:

• app_tenants - Tenant organizations
• app_users - User accounts
• app_roles - Role definitions
• app_user_roles - User-to-role assignments
• app_subscriptions - Tenant feature licensing

Critical: Every data table includes *_tenant_id column for isolation enforcement.

---

app_tenants - Tenant Organizations

Purpose

Represents distinct customer organizations, each with isolated data and separate database connections.

Schema

SELECT
  ten_id (INT),                          -- Primary Key
  ten_code (VARCHAR(128)),               -- Unique tenant identifier
  ten_name (VARCHAR(256)),               -- Display name
  ten_database_id (VARCHAR),             -- Connection ID (from app_connections)
  ten_is_active (BIT),                   -- 1=Active, 0=Suspended
  ten_owner_email (VARCHAR),             -- Primary contact
  ten_subscription_id (INT),             -- Links to app_subscriptions
  ten_storage_quota_mb (BIGINT),         -- Storage limit
  ten_created_at (DATETIME2),            -- Creation timestamp
  ten_updated_at (DATETIME2),            -- Last update
  ten_is_deleted (BIT),                  -- Soft delete flag
  ten_audit_data (JSON),                 -- Audit trail
  ten_add_dtls (JSON)                    -- Additional metadata
FROM app_tenants
WHERE ten_is_deleted = 0
ORDER BY ten_created_at DESC;

Example Queries

-- Get active tenant
SELECT * FROM app_tenants
WHERE ten_code = 'ACME_CORP' AND ten_is_active = 1;

-- Get tenant details with subscription
SELECT
  t.ten_id, t.ten_code, t.ten_name,
  s.sub_plan_type, s.sub_feature_set
FROM app_tenants t
LEFT JOIN app_subscriptions s ON t.ten_subscription_id = s.sub_id
WHERE t.ten_id = 5;

---

app_users - User Accounts

Purpose

Stores user accounts with authentication credentials, profile information, and contact details.

Schema

SELECT
  usr_id (INT),                          -- Primary Key
  usr_tenant_id (INT),                   -- Tenant isolation - CRITICAL
  usr_email (VARCHAR),                   -- Unique email per tenant
  usr_full_name (VARCHAR),               -- Display name
  usr_given_name (VARCHAR),              -- First name
  usr_family_name (VARCHAR),             -- Last name
  usr_password_hash (VARCHAR),           -- Hashed password (bcrypt/argon2)
  usr_password_salt (VARCHAR),           -- Salt for hashing
  usr_phone (VARCHAR),                   -- Contact phone
  usr_avatar_url (VARCHAR),              -- Profile picture
  usr_is_active (BIT),                   -- 1=Active, 0=Disabled
  usr_last_login_at (DATETIME2),         -- Last login timestamp
  usr_failed_login_count (INT),          -- Lockout tracking
  usr_is_locked (BIT),                   -- Account locked flag
  usr_locked_until (DATETIME2),          -- Lockout expiration
  usr_password_expires_at (DATETIME2),   -- Password expiration
  usr_created_at (DATETIME2),            -- Creation timestamp
  usr_updated_at (DATETIME2),            -- Last update
  usr_is_deleted (BIT),                  -- Soft delete flag
  usr_audit_data (JSON),                 -- Audit trail
  usr_add_dtls (JSON)                    -- Custom fields (SSO ID, dept, etc.)
FROM app_users
WHERE usr_tenant_id = @tenant_id AND usr_is_deleted = 0;

Password Hashing (Best Practice)

// From: axiom-genesis-middleware/services/auth.service.js
const bcrypt = require("bcrypt");

AuthService.hashPassword = async (plainPassword) => {
  const salt = await bcrypt.genSalt(10); // Cost factor 10
  return await bcrypt.hash(plainPassword, salt);
};

AuthService.verifyPassword = async (plainPassword, hash) => {
  return await bcrypt.compare(plainPassword, hash);
};

// On user creation
const newUser = {
  usr_email: "user@example.com",
  usr_full_name: "John Doe",
  usr_password_hash: await AuthService.hashPassword(plainPassword),
  usr_password_salt: null, // bcrypt handles salt internally
};

Account Lockout Logic

// Lock account after 5 failed attempts
AuthService.recordLoginFailure = async (userId, tenantId) => {
  const db = getConnection();

  const user = await db
    .request()
    .input("usr_id", userId)
    .input("usr_tenant_id", tenantId)
    .query(
      `SELECT usr_failed_login_count FROM app_users WHERE usr_id = @usr_id`,
    );

  const failCount = user.recordset[0].usr_failed_login_count + 1;

  if (failCount >= 5) {
    // Lock for 15 minutes
    await db
      .request()
      .input("usr_id", userId)
      .input("usr_is_locked", 1)
      .input("usr_locked_until", new Date(Date.now() + 15 * 60000))
      .input("usr_failed_login_count", failCount).query(`
        UPDATE app_users
        SET usr_is_locked = @usr_is_locked,
            usr_locked_until = @usr_locked_until,
            usr_failed_login_count = @usr_failed_login_count
        WHERE usr_id = @usr_id
      `);
  } else {
    // Increment counter
    await db
      .request()
      .input("usr_id", userId)
      .input("usr_failed_login_count", failCount).query(`
        UPDATE app_users
        SET usr_failed_login_count = @usr_failed_login_count
        WHERE usr_id = @usr_id
      `);
  }
};

// Reset on successful login
AuthService.recordLoginSuccess = async (userId, tenantId) => {
  await db
    .request()
    .input("usr_id", userId)
    .input("usr_last_login_at", new Date())
    .input("usr_failed_login_count", 0)
    .input("usr_is_locked", 0).query(`
      UPDATE app_users
      SET usr_last_login_at = @usr_last_login_at,
          usr_failed_login_count = @usr_failed_login_count,
          usr_is_locked = @usr_is_locked
      WHERE usr_id = @usr_id
    `);
};

---

app_roles - Role Definitions

Purpose

Defines roles and their associated permissions for role-based access control (RBAC).

Schema

SELECT
  rol_id (INT),                          -- Primary Key
  rol_tenant_id (INT),                   -- Tenant isolation
  rol_code (VARCHAR(128)),               -- Identifier (e.g., 'ADMIN', 'MANAGER')
  rol_name (VARCHAR(256)),               -- Display name
  rol_description (VARCHAR),             -- Role purpose
  rol_permissions (JSON),                -- Permission array
  rol_navigation_access (JSON),        -- Role-wise nav entry allow/deny by nav_code (see below)
  rol_is_system (BIT),                   -- 1=Built-in, 0=Custom
  rol_is_active (BIT),                   -- 1=Active, 0=Inactive
  rol_created_at (DATETIME2),            -- Creation timestamp
  rol_updated_at (DATETIME2),            -- Last update
  rol_is_deleted (BIT),                  -- Soft delete flag
  rol_audit_data (JSON),                 -- Audit trail
  rol_add_dtls (JSON)                    -- Additional metadata
FROM app_roles
WHERE rol_tenant_id = @tenant_id AND rol_is_deleted = 0;

rol_permissions Structure (JSON)

{
  "roleId": "MANAGER",
  "roleName": "Manager",
  "permissions": [
    {
      "permissionId": "perm_customers_read",
      "permissionName": "Read Customers",
      "resource": "customers",
      "action": "read",
      "entityCode": "CUSTOMERS"
    },
    {
      "permissionId": "perm_customers_write",
      "permissionName": "Create/Update Customers",
      "resource": "customers",
      "action": "write",
      "entityCode": "CUSTOMERS"
    },
    {
      "permissionId": "perm_orders_read",
      "permissionName": "Read All Orders",
      "resource": "orders",
      "action": "read",
      "entityCode": "ORDERS"
    },
    {
      "permissionId": "perm_reports_analytics",
      "permissionName": "Access Analytics",
      "resource": "reports",
      "action": "read"
    },
    {
      "permissionId": "perm_team_manage",
      "permissionName": "Manage Team Members",
      "resource": "users",
      "action": "manage",
      "scope": "team"
    }
  ]
}

rol_navigation_access Structure (JSON)

Optional column on app_roles (added by migration 003_app_roles_rol_navigation_access.sql). It restricts which top-level navigation entries a role may see, keyed by nav_code (same identifier as navigation rows / TVF output code), separate from entity RBAC in rol_permissions.

{
  "mode": "allowlist",
  "navCodes": ["MAIN_MENU", "SETTINGS_MENU"]
}

Field	Type	Description
mode	string	"allowlist" — only navCodes may appear; "denylist" — navCodes are hidden.
navCodes	string[]	Navigation entry codes to allow or deny.
version	number (optional)	Reserved for forward-compatible parsers.

NULL column: no extra filter from this column; navigation behaves as before this feature.

Edge cases:

• allowlist + empty navCodes: no navigation entries (fully locked menu for that role).
• denylist + empty navCodes: nothing removed by this column alone.

Precedence with menu JSON: If app_navigation_configs.nav_config (or TVF output) also expresses role visibility (e.g. visibleTo on items), the effective set is the intersection of both: a row must pass navigation config rules and rol_navigation_access when the column is set. See NAVIGATION_CONFIGURATION.md.

Runtime: Filtering is applied in SQL (e.g. inside fn_get_app_navigation_configs_by_tenant_role) after loading the role row for @rol_code / tenant. See comments in 003_app_roles_rol_navigation_access.sql and the filter pattern in 003_app_roles_rol_navigation_access_TVF_FILTER_PATTERN.sql.

Permission Types

Permission	Scope	Description
read	Entity	View records
write	Entity	Create/update records
delete	Entity	Delete records
approve	Entity	Approve workflows
export	Entity	Export data
manage	System	Manage users/roles
configure	System	Configure app
audit	System	View audit logs

---

app_user_roles - User-to-Role Assignments

Purpose

Maps users to roles (many-to-many relationship).

Schema

SELECT
  usr_rol_id (INT),                      -- Primary Key
  usr_rol_user_id (INT),                 -- Foreign key to app_users
  usr_rol_role_id (INT),                 -- Foreign key to app_roles
  usr_rol_tenant_id (INT),               -- Tenant isolation
  usr_rol_assigned_at (DATETIME2),       -- Assignment date
  usr_rol_assigned_by (INT),             -- Admin who assigned
  usr_rol_expires_at (DATETIME2),        -- Expiration (optional)
  usr_rol_is_active (BIT),               -- 1=Active, 0=Inactive
  usr_rol_is_deleted (BIT),              -- Soft delete flag
  CONSTRAINT fk_user FOREIGN KEY (usr_rol_user_id) REFERENCES app_users(usr_id),
  CONSTRAINT fk_role FOREIGN KEY (usr_rol_role_id) REFERENCES app_roles(rol_id)
FROM app_user_roles
WHERE usr_rol_tenant_id = @tenant_id AND usr_rol_is_deleted = 0;

Query User Permissions

-- Get all roles for a user
SELECT DISTINCT
  r.rol_code,
  r.rol_name,
  r.rol_permissions
FROM app_users u
INNER JOIN app_user_roles ur ON u.usr_id = ur.usr_rol_user_id
INNER JOIN app_roles r ON ur.usr_rol_role_id = r.rol_id
WHERE u.usr_id = 42
  AND u.usr_tenant_id = 1
  AND ur.usr_rol_is_active = 1
  AND r.rol_is_active = 1;

-- Check if user has permission
SELECT 1 FROM app_user_roles ur
INNER JOIN app_roles r ON ur.usr_rol_role_id = r.rol_id
WHERE ur.usr_rol_user_id = 42
  AND ur.usr_rol_tenant_id = 1
  AND JSON_CONTAINS(r.rol_permissions,
      JSON_OBJECT('permissionId', 'perm_customers_write'))
  AND ur.usr_rol_is_active = 1;

---

app_subscriptions - Feature Licensing

Purpose

Define what features are available to each tenant based on their subscription plan.

Schema

SELECT
  sub_id (INT),                          -- Primary Key
  sub_tenant_id (INT),                   -- Tenant isolation
  sub_code (VARCHAR(128)),               -- Subscription code
  sub_plan_type (VARCHAR),               -- 'STARTER', 'PROFESSIONAL', 'ENTERPRISE'
  sub_status (VARCHAR),                  -- 'ACTIVE', 'SUSPENDED', 'EXPIRED'
  sub_max_users (INT),                   -- Maximum users allowed
  sub_max_workflows (INT),               -- Maximum workflows
  sub_feature_set (JSON),                -- Available features
  sub_storage_limit_gb (INT),            -- Storage limit
  sub_api_rate_limit (INT),              -- API calls per minute
  sub_started_at (DATE),                 -- Subscription start
  sub_expires_at (DATE),                 -- Subscription end
  sub_price_per_month (DECIMAL),         -- Monthly cost
  sub_is_deleted (BIT),                  -- Soft delete flag
  sub_audit_data (JSON)                  -- Audit trail
FROM app_subscriptions
WHERE sub_tenant_id = @tenant_id;

sub_feature_set Structure (JSON)

{
  "subscriptionId": "SUB_PROF_001",
  "planType": "PROFESSIONAL",
  "features": [
    {
      "featureId": "feature_users",
      "featureName": "Multi-User Support",
      "enabled": true,
      "limit": 50
    },
    {
      "featureId": "feature_workflows",
      "featureName": "Workflow Automation",
      "enabled": true,
      "limit": 100
    },
    {
      "featureId": "feature_api",
      "featureName": "REST API Access",
      "enabled": true,
      "limit": 1000 // Requests per minute
    },
    {
      "featureId": "feature_sso",
      "featureName": "Single Sign-On (SSO)",
      "enabled": true
    },
    {
      "featureId": "feature_audit",
      "featureName": "Advanced Audit Logs",
      "enabled": true,
      "retention_days": 90
    },
    {
      "featureId": "feature_support",
      "featureName": "Priority Support",
      "enabled": true,
      "sla_hours": 4
    }
  ]
}

---

Multi-Tenant Isolation (Critical Implementation)

1. Tenant ID in Every Request

// From: axiom-genesis-middleware/middleware/context.middleware.js
router.use((req, res, next) => {
  // Extract tenant ID from encrypted header
  const encryptedTenantId = req.headers["x-tenant-id"];

  if (!encryptedTenantId) {
    return res.status(401).json({
      error: "Missing tenant ID header",
    });
  }

  try {
    // Decrypt tenant ID
    const tenantId = decrypt(encryptedTenantId);
    req.tenantId = tenantId;

    // Store in context
    ContextService.setContext({
      tenantId,
      userId: req.user?.id,
      userRoles: req.user?.roles,
    });
  } catch (error) {
    return res.status(401).json({
      error: "Invalid tenant ID",
    });
  }

  next();
});

2. Automatic Tenant Filtering in All Queries

// ALL database queries MUST include tenant_id filter
// BAD (never do this):
const result = await db
  .request()
  .query("SELECT * FROM app_users WHERE usr_id = 42");

// GOOD (always required):
const result = await db
  .request()
  .input("usr_id", 42)
  .input("usr_tenant_id", req.tenantId).query(`
    SELECT * FROM app_users 
    WHERE usr_id = @usr_id 
      AND usr_tenant_id = @usr_tenant_id
  `);

3. Tenant-Aware Connection Selection

// From: axiom-genesis-middleware/config/db/db_connections.js
const getTenantDatabaseConnection = async (tenantId) => {
  // 1. Get tenant record from config DB (RADICS_CFG)
  const configDb = getConfigConnection();
  const tenantRes = await configDb
    .request()
    .input("ten_id", tenantId)
    .query(`SELECT ten_database_id FROM app_tenants WHERE ten_id = @ten_id`);

  if (!tenantRes.recordset[0]) {
    throw new NotFoundError("Tenant not found");
  }

  const databaseId = tenantRes.recordset[0].ten_database_id;

  // 2. Get connection string from APP_CONNECTIONS
  const connRes = await configDb
    .request()
    .input("conn_id", databaseId)
    .query(`SELECT conn_string FROM APP_CONNECTIONS WHERE conn_id = @conn_id`);

  const connectionString = decrypt(connRes.recordset[0].conn_string);

  // 3. Create or return pooled connection
  return getOrCreateConnection(connectionString);
};

---

Authentication & Authorization Service

// From: axiom-genesis-middleware/services/auth.service.js
AuthService.login = async (email, password, tenantCode) => {
  const db = getConnection();

  // 1. Find tenant
  const tenantRes = await db
    .request()
    .input("ten_code", tenantCode)
    .query(`SELECT ten_id FROM app_tenants WHERE ten_code = @ten_code`);

  if (!tenantRes.recordset[0]) {
    throw new NotFoundError("Tenant not found");
  }

  const tenantId = tenantRes.recordset[0].ten_id;

  // 2. Find user within tenant
  const userRes = await db
    .request()
    .input("usr_email", email)
    .input("usr_tenant_id", tenantId).query(`
      SELECT usr_id, usr_password_hash, usr_is_active, usr_is_locked
      FROM app_users
      WHERE usr_email = @usr_email 
        AND usr_tenant_id = @usr_tenant_id
    `);

  if (!userRes.recordset[0]) {
    throw new UnauthorizedError("Invalid credentials");
  }

  const user = userRes.recordset[0];

  // 3. Check account status
  if (!user.usr_is_active || user.usr_is_locked) {
    throw new UnauthorizedError("Account is locked or inactive");
  }

  // 4. Verify password
  const passwordValid = await bcrypt.compare(password, user.usr_password_hash);
  if (!passwordValid) {
    await AuthService.recordLoginFailure(user.usr_id, tenantId);
    throw new UnauthorizedError("Invalid credentials");
  }

  // 5. Load user roles & permissions
  const rolesRes = await db
    .request()
    .input("usr_id", user.usr_id)
    .input("usr_tenant_id", tenantId).query(`
      SELECT r.rol_code, r.rol_name, r.rol_permissions
      FROM app_user_roles ur
      INNER JOIN app_roles r ON ur.usr_rol_role_id = r.rol_id
      WHERE ur.usr_rol_user_id = @usr_id
        AND ur.usr_rol_tenant_id = @usr_tenant_id
        AND ur.usr_rol_is_active = 1
    `);

  const roles = rolesRes.recordset.map((r) => r.rol_code);
  const permissions = rolesRes.recordset.flatMap(
    (r) => JSON.parse(r.rol_permissions).permissions,
  );

  // 6. Create JWT token
  const token = jwt.sign(
    {
      userId: user.usr_id,
      tenantId,
      roles,
      permissions,
    },
    process.env.JWT_SECRET,
    {
      expiresIn: "24h",
    },
  );

  // 7. Update last login
  await AuthService.recordLoginSuccess(user.usr_id, tenantId);

  return {
    token,
    user: {
      id: user.usr_id,
      email,
      tenantId,
      roles,
      permissions,
    },
  };
};

// Check permission
AuthService.hasPermission = (userPermissions, requiredPermission) => {
  return userPermissions.some((p) => p.permissionId === requiredPermission);
};

---

Permission Checking in Routes

// Middleware to check permissions
const requirePermission = (requiredPermission) => {
  return (req, res, next) => {
    const userPermissions = req.user.permissions;

    if (!AuthService.hasPermission(userPermissions, requiredPermission)) {
      return res.status(403).json({
        error: "Insufficient permissions",
      });
    }

    next();
  };
};

// Usage in routes
router.post(
  "/customers",
  requirePermission("perm_customers_write"),
  async (req, res) => {
    // Create customer
  },
);

---

API Examples

Login

POST /api/v1/auth/login
Body: {
  "email": "user@example.com",
  "password": "password123",
  "tenantCode": "ACME_CORP"
}

Response:
{
  "success": true,
  "data": {
    "token": "eyJhbGciOiJIUzI1NiIs...",
    "user": {
      "id": 42,
      "email": "user@example.com",
      "tenantId": 5,
      "roles": ["MANAGER", "ANALYST"],
      "permissions": [
        { "permissionId": "perm_customers_read", ... },
        { "permissionId": "perm_orders_read", ... }
      ]
    }
  }
}

Get User Profile

GET /api/v1/users/profile
Headers: {
  'x-tenant-id': 'encrypted_value',
  'Authorization': 'Bearer token'
}

Response:
{
  "success": true,
  "data": {
    "usr_id": 42,
    "usr_email": "user@example.com",
    "usr_full_name": "John Doe",
    "usr_roles": ["MANAGER", "ANALYST"],
    "usr_tenant_id": 5
  }
}

---

Do's and Don'ts

✅ DO

• Always include tenant_id in every query - Fundamental to isolation
• Hash passwords with bcrypt - Never store plaintext passwords
• Implement account lockout - After N failed login attempts
• Use temporary role expiration - For temporary assignments
• Audit user access changes - Log role assignments/removals
• Encrypt tenant ID in headers - Never transmit plaintext
• Cache user permissions - Minimize database queries
• Validate permissions at every endpoint - Defense in depth
• Rotate JWT tokens - Use short expiration + refresh tokens

❌ DON'T

• Query data without tenant_id filter - Massive security breach
• Store plaintext passwords - Criminal liability
• Skip permission checks - Assume authentication is enough
• Create cross-tenant user roles - Strict per-tenant isolation
• Share database connections across tenants - Use connection pooling per tenant
• Cache user permissions indefinitely - Cache for minutes, not hours
• Allow admin users to see other tenant data - Even admins are tenant-scoped
• Forget to validate tenant_id in parameters - Check every request

---

Related Documentation

• DATABASE.md - Core schema reference
• OBJECT_MANAGEMENT.md - Entity definitions
• PAGE_CONFIGURATION.md - Form pages
• NAVIGATION_CONFIGURATION.md - Menu system