Skip to main content

Middleware Development Documentation

Overview (Non-Technical)​

The Axiom Genesis Middleware is a Node.js/Express backend service that powers the entire platform. It handles multi-tenant request processing, workflow execution, database connectivity, and business logic orchestration. Think of it as the "brain" that coordinates all operations across different tenants while maintaining security and isolation.

Key Purpose: Process requests from the frontend, execute workflows, manage data operations, and coordinate with external databases while maintaining strict tenant isolation and security.

Standards​

Project Structure​

middleware/
├── auth/                      # Authentication & encryption utilities
├── config/                    # Configuration management
│   ├── db/                   # Database connection pooling
│   ├── index.js              # Environment-based config loader
│   └── constants.js          # App constants
├── controllers/              # Request handlers (route controllers)
├── services/                 # Business logic layer
│   ├── entity/               # Dynamic CRUD operations
│   ├── workflow.service.js   # Workflow execution engine
│   ├── ms-sql.service.js     # MSSQL operations
│   ├── context.service.js    # Request context & tenant resolution
│   └── [18+ domain services] # Specialized services
├── repositories/             # Data access layer (optional)
├── routes/                   # API route definitions
├── middleware/               # Express middleware (auth, tenant, validation)
├── utils/                    # Utility functions
│   ├── logger.util.js        # Structured logging
│   ├── errors.util.js        # Custom error classes
│   └── validators.util.js    # Input validation rules
├── workers/                  # Background job processors
├── crons/                    # Scheduled tasks
├── jobs/                     # Job queue definitions
├── migrations/               # Database schema migrations
├── tests/                    # Test files (*.test.js)
├── index.js                  # Application entry point
├── package.json
├── jest.config.js
└── swagger.js                # API documentation config

Naming Conventions​

  • Files: camelCase (e.g., workflow.service.js, entity.controller.js)
  • Classes/Functions: camelCase functions, PascalCase classes (e.g., class WorkflowService {})
  • Constants: UPPER_SNAKE_CASE (e.g., WORKFLOW_STATUS, ERROR_CODES)
  • Routes: kebab-case paths (e.g., /api/v1/workflows, /api/v1/app-connections)
  • Environment Variables: UPPER_SNAKE_CASE (e.g., NODE_ENV, DB_HOST)

Service Layer Architecture​

// Pattern: services/ folder contains domain-specific logic
// Each service exports a class or object with methods

// Example: workflow.service.js
class WorkflowService {
  async startWorkflow(workflowId, { inputData, version, userId }) {}
  async pauseWorkflow(instanceId) {}
  async getWorkflowStatus(instanceId) {}
  async getWorkflowLogs(instanceId, filter) {}
}

module.exports = new WorkflowService();

Database Access Standards​

  • MSSQL Connections: Via connection pooling in config/db/db_connections.js
  • Parameterized Queries: Always use .input('paramName', value) - never string concat
  • Transaction Support: Use connection transactions for multi-step operations
  • Error Handling: Log query errors with context (tenant_id, query type)

Middleware Architecture (Express)​

// src/middleware/ contains:
-tenantMiddleware.ts - // Extract & validate tenant from headers
  authMiddleware.ts - // JWT validation & user context
  errorHandler.ts - // Centralized error response formatting
  requestLogger.ts - // Request/response logging
  rateLimiter.ts; // API rate limiting per tenant

Do's and Don'ts​

✅ DO​

  • Always validate tenant ID: Every request must have encrypted tenant ID in x-tenant-id
  • Use services for business logic: Never put logic in controllers
  • Parameterize all SQL queries: Prevents SQL injection
  • Log with context: Include tenant_id, user_id, operation type
  • Use custom error classes: ValidationError, NotFoundError, ServiceError, ConflictError
  • Implement try-catch in async functions: Handle errors gracefully
  • Use connection pooling: Get connections from config, don't create new ones
  • Implement audit trails: Log user actions with timestamp in JSON columns
  • Use environment variables: Never hardcode credentials or URLs
  • Version your API endpoints: Always use /v1/ prefix
  • Implement health checks: /api/v1/health endpoint for monitoring

❌ DON'T​

  • Make unvalidated database calls: Always validate input first
  • Store sensitive data in logs: Mask passwords, tokens, encrypting PII
  • Hardcode database connection strings: Use config/ loader
  • Return raw database errors to clients: Translate to user-friendly messages
  • Allow cross-tenant data access: Validate tenant context on every query
  • Use deprecated v0 endpoints: All new endpoints must use /v1
  • Block on long-running operations: Use background jobs/queues
  • Skip input sanitization: Validate & sanitize all user inputs
  • Create global state in services: Services must be stateless
  • Ignore error responses from external APIs: Handle gracefully
  • Deploy without environment validation: Check all required env vars on startup

Technical Details​

Environment Configuration​

// config/index.js - Loads based on NODE_ENV
// Development (.env) → Staging (environments/staging.env) → Production (environments/production.env)

process.env.NODE_ENV = 'staging|production' (default: development)

// Required Variables
DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME   // Config database
REDIS_URL                                           // Cache connection
JWT_SECRET                                          // Token signing
TENANT_ENCRYPTION_KEY                               // Tenant ID encryption
API_PORT                                            // Server port (default 8008)
LOG_LEVEL                                           // 'debug|info|warn|error'

Request-Response Cycle​

Incoming Request Flow:

1. tenantMiddleware
   ├─ Extract x-tenant-id header
   ├─ Decrypt tenant ID
   ├─ Set req.tenantId = decrypted ID
   └─ Validate tenant exists in config DB
2. authMiddleware
   ├─ Extract JWT from Authorization header
   ├─ Validate token signature
   ├─ Set req.user = decoded token
   └─ Check user has permission for resource
3. Controller
   ├─ Validate request payload
   ├─ Call service with context
   └─ Return response
4. Error Handler
   ├─ Catch any thrown errors
   ├─ Format error response
   └─ Send status + message to client

Response Format:

// Success (200)
{
  success: true,
  data: { /* response data */ },
  meta: { timestamp, requestId }
}

// Error (400+)
{
  success: false,
  error: {
    code: 'VALIDATION_ERROR',
    message: 'User-friendly error message',
    details: { /* field-specific errors */ }
  },
  meta: { timestamp, requestId }
}

Workflow Execution Engine​

// WorkflowService API (973-line service)
class WorkflowService {
  // Create & execute workflow instance
  async startWorkflow(workflowId, { inputData, version, userId, tenantId }) {
    // 1. Validate workflow exists & user has access
    // 2. Create instance record in wft_instances table
    // 3. Queue workflow steps to wft_queue
    // 4. Trigger background worker
    // 5. Return instance ID
  }

  // Pause execution (preserves state)
  async pauseWorkflow(instanceId, { reason, userId }) {
    // Update instance status to 'paused'
    // Log pause event with reason
  }

  // Resume from pause
  async resumeWorkflow(instanceId, { userId }) {
    // Validate instance is paused
    // Resume queue processing
  }

  // Terminate execution
  async cancelWorkflow(instanceId, { reason, userId }) {
    // Update instance status to 'cancelled'
    // Clear queue items
    // Log cancellation
  }

  // Get current execution status
  async getWorkflowStatus(instanceId) {
    // Return: instance data + current step + progress
  }

  // Fetch execution logs
  async getWorkflowLogs(instanceId, { filters, limit, offset }) {
    // Query wft_logs table with tenant isolation
    // Return sorted logs with pagination
  }
}

Database Pooling & Connection Management​

// config/db/db_connections.js
const { getConnection } = require("./db_connections");

async function queryWorkflow(tenantId, workflowId) {
  const connection = await getConnection(tenantId);
  try {
    const result = await connection
      .request()
      .input("workflowId", mssql.Int, workflowId)
      .query("SELECT * FROM wft_workflows WHERE id = @workflowId");
    return result.recordset[0];
  } finally {
    // Connection auto-returns to pool
  }
}

Authentication & Authorization​

// JWT Token Payload
{
  sub: 'user_id',
  email: 'user@example.com',
  tennr: 'tenant_id',           // Tenant number
  roles: ['admin', 'designer'],  // User roles
  permissions: ['create:workflow', 'read:dataview'],
  iat: timestamp,
  exp: timestamp + 24h
}

// Verification Process
1. Extract token from Authorization: Bearer <token>
2. Verify signature with JWT_SECRET
3. Check expiration
4. Extract user context (id, roles, permissions)
5. Validate against required permissions per route

Error Handling & Logging​

// Custom Error Classes
class ValidationError extends Error {
  constructor(message, details) {
    this.code = "VALIDATION_ERROR";
    this.status = 400;
    this.details = details; // Field-level errors
  }
}

class NotFoundError extends Error {
  constructor(resource, id) {
    this.code = "NOT_FOUND";
    this.message = `${resource} with id ${id} not found`;
    this.status = 404;
  }
}

// Logging with Context
logger.info("workflow.started", {
  category: "workflow",
  tenantId: req.tenantId,
  userId: req.user.id,
  workflowId: workflowDefId,
  instanceId: createdInstance.id,
});

// Error Logging
logger.error("ms-sql.query.failed", {
  category: "database",
  tenantId: req.tenantId,
  error: err.message,
  query: sanitizeQuery(queryString), // Don't log credentials
});

Background Job Processing​

// Worker Modes (controlled by --mode flag)
npm run dev               // Runs all: API + Worker + Cron
npm run dev:api          // API server only
npm run dev:worker       // Background job processor
npm run dev:redis        // Start Redis + API

// Queue Pattern
// 1. Service enqueues job to Redis queue
// 2. Worker picks up job from queue
// 3. Worker executes job logic (workflow steps, exports, etc.)
// 4. Worker updates job status (completed/failed)
// 5. UI polls for status changes

// Example: Workflow Step Execution
// enqueue: wft_queue table → Redis backup
// process: Worker grabs job, executes step
// complete: wft_logs updated, result stored

Multi-Tenant Isolation Enforced At​

// 1. API Route Level
router.get('/workflows', requireAuth, async (req, res) => {
  // req.tenantId automatically set by middleware
  const workflows = await workflowService.getWorkflows(req.tenantId);
});

// 2. Database Query Level
query: 'SELECT * FROM wft_workflows WHERE tenant_id = @tenantId'
input('tenantId', mssql.Int, req.tenantId)

// 3. Cache Key Level
redisKey = `workflows:${tenantId}:${workflowId}`

// 4. Logging Level
logger logs include tenantId for audit trails

Caching Strategy (Redis)​

// Cache Keys Pattern
"workflows:${tenantId}:${workflowId}";
"dataviews:${tenantId}:${viewId}";
"connections:${tenantId}:${connectionId}";
"entities:${tenantId}:entities-map"; // Entity registry

// Invalidation Events
// On workflow update → invalidate workflows:* keys
// On dataview update → invalidate dataviews:* keys
// Script: scripts/clear-redis-cache.js for manual purge

Entity Management (CRUD Operations)​

Purpose: The entity management system provides unified, configuration-driven CRUD operations for all registered objects in Axiom Genesis. Entity registry and access profiles are stored in the RADICS_CFG database and integrated into the service layer through a multi-layered architecture supporting multiple database backends.

Architecture Overview:

Entity operations follow a standardized flow that bridges configuration metadata with runtime behavior:

Client Request (GET/POST/PUT/DELETE)
↓
Entity Controller (Validate request)
↓
Entity Service Gateway (Route to appropriate handler)
↓
ConfigurationService (Load entity config from database)
↓
ReadService / WriteService / DeleteService (Orchestrate operation)
↓
Policy & Access Control (Apply field-level security)
↓
Database Adapter (MSSQL / MongoDB / Oracle)
↓
Result Processing (Masking, Pagination, Projection)
↓
Response to Client

Key Components:

ComponentFileResponsibility
ConfigurationServiceservices/entity/configuration.service.jsLoads entity configuration from database (app_object_registries + app_object_profiles); merges base + tenant-specific configs
ReadServiceservices/entity/read.service.jsOrchestrates read operations; applies configuration filters, access policies, field masking, pagination
WriteServiceservices/entity/write.service.jsHandles create/update with validation, access control, concurrency handling, audit trail
DeleteServiceservices/entity/delete.service.jsManages deletion with soft-delete pattern (is_deleted BIT flag), cascade behavior, audit logging
EntityServiceGatewayservices/entity/gateway/entity-service-gateway.service.jsHigh-level orchestration; routes operations to appropriate service
OptimizedEntityServiceservices/optimized-entity.service.jsWraps entity operations with caching, pagination, performance optimization

Configuration Integration:

Entity operations integrate with two configuration tables in RADICS_CFG:

// Database: RADICS_CFG
// Table 1: app_object_registries (defines entity properties)
{
  obj_id: 1,
  obj_code: 'app_users',  // Unique entity identifier
  obj_name: 'Users',
  obj_description: 'Application users and access management',
  obj_payload: { /* JSON configuration defining fields, types, constraints */ },
  obj_db_key: 'usr_id',  // Primary key column
  obj_is_deleted: 0      // Soft delete flag
}

// Table 2: app_object_profiles (defines access control)
{
  prf_id: 1,
  prf_code: 'admin_profile',
  prf_description: 'Administrator profile with full access',
  prf_policy: { /* JSON configuration for field-level access, masking, visibility */ },
  prf_access_mode: 'FULL',  // FULL | READ | WRITE | EXECUTE
  obj_id: 1  // Foreign key to app_object_registries.obj_id
}

See Object Management for detailed object registry structure and Database Schema for complete column specifications.

Read Operations (ReadService):

ReadService orchestrates entity read operations with multi-layered configuration and security:

// services/entity/read.service.js
class ReadService {
  async get(
    entityCode,
    {
      filters = {},
      limit = 100,
      skip = 0,
      sortBy = null,
      sortOrder = "asc",
      user = null, // Contains userId, tenantId, roles
    },
  ) {
    // Step 1: Load entity registry from Redis cache
    //         Pattern: getRegistry(entity_code) → Returns app_object_registries metadata
    const registry = await getRegistry(entityCode);
    if (!registry) throw new NotFoundError("Entity", entityCode);

    // Step 2: Load entity access profile from Redis cache
    //         Pattern: getProfile(entity_code) → Returns app_object_profiles metadata
    const profile = await getProfile(entityCode);

    // Step 3: Load entity configuration (base + tenant overrides)
    //         ConfigurationService merges:
    //         - Base config: obj_payload from app_object_registries
    //         - Tenant config: tenant-specific field mappings, validations
    const configService = new ConfigurationService();
    const config = await configService.load(
      connection,
      registry.obj_db_type, // 'mssql' | 'mongodb' | 'oracle'
      entityCode,
      user.tenantId,
    );

    // Step 4: Extract tenant context and validate access
    //         PolicyHelper applies prf_policy rules:
    //         - Field-level visibility (hide PII fields based on role)
    //         - Field-level masking (partially mask sensitive data)
    //         - Field-level projection (return only allowed columns)
    const tenantContext = TenantContext.extract(user);
    ServiceAccessControl.validateAccess(user, registry, profile);

    // Step 5: Resolve database connection from global pool
    //         Each tenant's external database accessed via connection string
    //         stored in app_connections table referenced by obj_connection_id
    const dbConnection = getConnection(
      tenantContext.tenantId,
      registry.obj_connection_id,
    );

    // Step 6: Delegate to appropriate database adapter
    //         Supports: MSSQLRead, MongoRead, OracleRead
    //         Each adapter translates generic query to database-specific syntax
    const adapter = getAdapter(registry.obj_db_type);
    const rawResults = await adapter.query({
      entityCode,
      config,
      filters,
      limit,
      skip,
      sortBy,
      sortOrder,
      connection: dbConnection,
    });

    // Step 7: Apply configuration filters
    //         Filters from config (e.g., exclude soft-deleted: is_deleted = 0)
    const filtered = configService.applyConfigFilters(
      rawResults,
      config,
      tenantContext.tenantId,
    );

    // Step 8: Apply access policy (field masking, projection)
    //         Hides fields user doesn't have access to
    //         Masks sensitive data (phone → XXX-XXX-1234)
    //         Projects only allowed columns
    const results = PolicyHelper.applyPolicy(filtered, profile, user);

    return {
      records: results,
      total: rawResults.count,
      limit,
      skip,
      meta: { entityCode, retrievedAt: new Date() },
    };
  }
}

// Example: API Usage
// GET /api/v1/app_users?filters[usr_status]=ACTIVE&limit=50&skip=0&sortBy=usr_name&sortOrder=asc
// → ReadService.get('app_users', { filters: {...}, limit: 50, skip: 0, ... })
// → Returns: { records: [...], total: 245, meta: {...} }

Write Operations (WriteService):

WriteService handles entity creation and updates with validation, audit trails, and concurrency control:

// services/entity/write.service.js
class WriteService {
  async create(entityCode, payload, { user }) {
    // Step 1: Load configuration (base + tenant)
    const configService = new ConfigurationService();
    const config = await configService.load(
      connection,
      dbType,
      entityCode,
      user.tenantId,
    );

    // Step 2: Validate payload against configuration rules
    //         Validates: required fields, data types, constraints, custom validations
    const validationErrors = await configService.validatePayload(
      payload,
      config,
      "create",
    );
    if (validationErrors.length > 0) {
      throw new ValidationError("Payload validation failed", {
        fields: validationErrors,
      });
    }

    // Step 3: Check access control (user must have CREATE permission)
    const profile = await getProfile(entityCode);
    ServiceAccessControl.validateAccess(user, "CREATE", profile);

    // Step 4: Transform payload using config field mappings
    //         Maps: Frontend field names → Database column names
    //         Example: firstName → usr_first_name
    const dbPayload = configService.transformPayload(payload, config);

    // Step 5: Insert record into database
    const dbConnection = getConnection(user.tenantId);
    const result = await connection
      .request()
      .input("payload", mssql.NVarChar, JSON.stringify(dbPayload))
      .input("tenantId", mssql.Int, user.tenantId)
      .input("createdBy", mssql.NVarChar, user.id).query(`
        INSERT INTO ${entityCode} (\`${Object.keys(dbPayload).join("`, `")}\`, tenant_id, created_by, created_at)
        VALUES (${Object.keys(dbPayload)
          .map((k) => "@" + k)
          .join(", ")}, @tenantId, @createdBy, GETDATE())
      `);

    // Step 6: Log audit trail
    logger.info("entity.created", {
      category: "entity",
      entityCode,
      tenantId: user.tenantId,
      userId: user.id,
      recordId: result.insertId,
      changes: { before: null, after: dbPayload },
    });

    // Step 7: Invalidate cache for this entity type
    await invalidateEntityCache(entityCode, user.tenantId);

    return { id: result.insertId, ...payload };
  }

  async update(entityCode, id, payload, { user }) {
    // Similar flow to create, but:
    // 1. Load existing record to create before/after audit
    // 2. Validate update against config (some fields may be immutable)
    // 3. Use UPDATE query with WHERE clause on id + tenantId
    // 4. Handle concurrency: Check last_modified timestamp to prevent lost updates
    // 5. Log audit trail with before/after comparison

    const existing = await this.get(entityCode, id, user);
    const validationErrors = await configService.validatePayload(
      payload,
      config,
      "update",
      existing,
    );
    if (validationErrors.length > 0) {
      throw new ValidationError("Update validation failed", {
        fields: validationErrors,
      });
    }

    // Concurrency check
    if (payload.lastModified !== existing.lastModified) {
      throw new ConflictError(
        "Record has been modified by another user. Please reload and try again.",
      );
    }

    const dbPayload = configService.transformPayload(payload, config);
    const result = await connection
      .request()
      .input("id", mssql.Int, id)
      .input("tenantId", mssql.Int, user.tenantId)
      .query(
        `UPDATE ${entityCode} SET ${Object.keys(dbPayload)
          .map((k) => k + " = @" + k)
          .join(
            ", ",
          )}, last_modified = GETDATE() WHERE id = @id AND tenant_id = @tenantId`,
      );

    logger.info("entity.updated", {
      category: "entity",
      entityCode,
      tenantId: user.tenantId,
      userId: user.id,
      recordId: id,
      changes: { before: existing, after: { ...existing, ...payload } },
    });

    await invalidateEntityCache(entityCode, user.tenantId);
    return { id, ...payload };
  }
}

// Example: API Usage
// POST /api/v1/app_users
// Body: { usr_name: 'john.doe', usr_email: 'john@example.com', usr_role: 'viewer' }
// → WriteService.create('app_users', { ... }, { user: req.user })
// → Response: { id: 42, usr_name: 'john.doe', ... }

// PUT /api/v1/app_users/42
// Body: { usr_role: 'editor', lastModified: '2025-02-12T10:30:00Z' }
// → WriteService.update('app_users', 42, { ... }, { user: req.user })
// → Response: { id: 42, usr_role: 'editor', ... }

Delete Operations (DeleteService):

DeleteService manages entity deletion with soft-delete pattern and cascade behavior:

// services/entity/delete.service.js
class DeleteService {
  async delete(entityCode, id, { user, cascade = false }) {
    // Step 1: Load configuration
    const configService = new ConfigurationService();
    const config = await configService.load(
      connection,
      dbType,
      entityCode,
      user.tenantId,
    );

    // Step 2: Check access control (user must have DELETE permission)
    const profile = await getProfile(entityCode);
    ServiceAccessControl.validateAccess(user, "DELETE", profile);

    // Step 3: Load existing record for audit trail
    const existing = await this.get(entityCode, id, user);
    if (!existing) throw new NotFoundError("Record", id);

    // Step 4: Check for foreign key dependencies (cascade check)
    if (!cascade && config.hasForeignKeyReferences) {
      const dependents = await checkDependents(entityCode, id);
      if (dependents.length > 0) {
        throw new ConflictError(
          `Cannot delete record. ${dependents.length} dependent records exist.`,
          { dependentEntities: dependents },
        );
      }
    }

    // Step 5: Perform soft delete (set is_deleted = 1)
    //         Physical deletion not recommended for audit/compliance reasons
    const result = await connection
      .request()
      .input("id", mssql.Int, id)
      .input("tenantId", mssql.Int, user.tenantId)
      .input("deletedBy", mssql.NVarChar, user.id).query(`
        UPDATE ${entityCode}
        SET is_deleted = 1, deleted_by = @deletedBy, deleted_at = GETDATE()
        WHERE id = @id AND tenant_id = @tenantId
      `);

    // Step 6: Handle cascade deletes if needed
    if (cascade && config.cascadeRules) {
      for (const rule of config.cascadeRules) {
        await connection.request().query(rule.query); // Execute cascade rules
      }
    }

    // Step 7: Log audit trail
    logger.info("entity.deleted", {
      category: "entity",
      entityCode,
      tenantId: user.tenantId,
      userId: user.id,
      recordId: id,
      cascade,
      changes: { before: existing, after: null },
    });

    // Step 8: Invalidate cache
    await invalidateEntityCache(entityCode, user.tenantId);

    return { success: true, deletedAt: new Date() };
  }
}

// Example: API Usage
// DELETE /api/v1/app_users/42
// → DeleteService.delete('app_users', 42, { user: req.user })
// → Response: { success: true, deletedAt: '2025-02-12T10:45:00Z' }

// DELETE /api/v1/app_users/42?cascade=true
// → WithCascade to delete dependent records

Configuration Integration Details:

ConfigurationService bridges database metadata with runtime behavior:

// services/entity/configuration.service.js
class ConfigurationService {
  async load(connection, dbType, entityCode, tenantId) {
    const cacheKey = `config:${entityCode}:${tenantId}`;

    // Step 1: Check cache (TTL: 5 minutes by default)
    const cached = this.cache.get(cacheKey);
    if (cached && !cached.expired) {
      this.cacheStats.hits++;
      return cached.config;
    }

    // Step 2: Load base configuration from app_object_registries
    //         Only MSSQL supports configuration loading currently
    if (dbType !== 'mssql') {
      logger.warn('configuration.load.unsupported-db', { dbType, entityCode });
      return this.getDefaultConfig(entityCode);
    }

    const baseConfig = await ConfigLoader.loadBaseConfig(connection, entityCode);
    // baseConfig = {
    //   entityCode: 'app_users',
    //   fields: [
    //     { name: 'usr_id', type: 'INT', required: true, ... },
    //     { name: 'usr_name', type: 'NVARCHAR(100)', required: true, ... },
    //     { name: 'usr_email', type: 'NVARCHAR(255)', required: true, unique: true, ... }
    //   ],
    //   primaryKey: 'usr_id',
    //   validation: { /* rules from obj_payload */ },
    //   constraints: { /* FK, UNIQUE, etc. */ }
    // }

    // Step 3: Load tenant-specific overrides
    //         Allows tenants to customize entity schemas
    const tenantConfig = await ConfigLoader.loadTenantConfig(connection, entityCode, tenantId);
    // tenantConfig = {
    //   fieldOverrides: { usr_name: { required: false } },
    //   hiddenFields: ['usr_internal_id'],
    //   computedFields: { fullName: 'CONCAT(usr_first_name, " ", usr_last_name)' }
    // }

    // Step 4: Merge base + tenant configs
    //         Tenant overrides take precedence over base configuration
    const mergedConfig = ConfigLoader.mergeConfigurations(baseConfig, tenantConfig);

    // Step 5: Cache merged configuration
    this.cache.set(cacheKey, {
      config: mergedConfig,
      expiresAt: Date.now() + 300000  // 5 minutes
    });
    this.cacheStats.misses++;

    return mergedConfig;
  }

  validatePayload(payload, config, operation = 'create', existing = null) {
    const errors = [];

    for (const field of config.fields) {
      const value = payload[field.name];

      // Required field check
      if (field.required && (value === null || value === undefined)) {
        errors.push({ field: field.name, error: 'Required field missing' });
      }

      // Type validation
      if (value !== null && !this.validateType(value, field.type)) {
        errors.push({ field: field.name, error: `Invalid type. Expected ${field.type}` });
      }

      // Unique constraint check
      if (field.unique && operation === 'create') {
        const exists = await connection.request()
          .input('value', value)
          .query(`SELECT COUNT(*) as cnt FROM ${config.entityCode} WHERE ${field.name} = @value`);
        if (exists.recordset[0].cnt > 0) {
          errors.push({ field: field.name, error: 'Value must be unique' });
        }
      }

      // Custom validation rules
      if (field.validation) {
        const validation = this.validateCustomRule(value, field.validation);
        if (!validation.valid) {
          errors.push({ field: field.name, error: validation.message });
        }
      }
    }

    return errors;
  }

  checkAccess(user, config, operation) {
    // Check if user role has permission for operation
    const userRoles = user.roles || [];
    const allowedRoles = config.accessControl?.[operation]?.allowedRoles || [];

    if (!userRoles.some(r => allowedRoles.includes(r))) {
      throw new AuthorizationError(
        `User does not have ${operation} permission for entity ${config.entityCode}`
      );
    }
  }
}

Optimization & Performance:

OptimizedEntityService wraps entity operations with caching and pagination:

// services/optimized-entity.service.js
class OptimizedEntityService {
  // Cached read with pagination support
  static async getList(
    entityType,
    { filters = {}, page = 1, pageSize = 50, fetchFn, cacheTTL = 300 } = {},
  ) {
    const cacheKey = `list:${entityType}:${JSON.stringify(filters)}:${page}`;

    // Check cache
    const cached = CacheHelper.get(cacheKey);
    if (cached) {
      return cached;
    }

    // Fetch data with pagination
    const { offset } = getPagination(page, pageSize);
    const result = await fetchFn({
      filters,
      limit: pageSize,
      skip: offset,
      page,
      pageSize,
    });

    // Cache result with TTL
    CacheHelper.set(cacheKey, result, cacheTTL);
    PerformanceMonitor.recordOperation(
      "entity.list",
      result.records.length,
      Date.now(),
    );

    return result;
  }

  // Create with cache invalidation
  static async create(entityType, data, { createFn }) {
    const result = await createFn(data);

    // Invalidate all lists for this entity type
    await this.invalidateEntityLists(entityType);

    return result;
  }

  // Update with cache invalidation
  static async update(entityType, id, data, { updateFn }) {
    const result = await updateFn(id, data);

    // Invalidate affected caches
    await this.invalidateEntityLists(entityType);

    return result;
  }

  // Delete with cache invalidation
  static async delete(entityType, id, { deleteFn }) {
    const result = await deleteFn(id);

    // Invalidate affected caches
    await this.invalidateEntityLists(entityType);

    return result;
  }

  static async invalidateEntityLists(entityType) {
    const keys = RedisCache.keys(`list:${entityType}:*`);
    for (const key of keys) {
      RedisCache.del(key);
    }
  }
}

Best Practices for Entity Operations:

  1. Always validate tenant context: Ensure req.tenantId is set before entity operations
  2. Use ConfigurationService for metadata: Avoid hardcoding entity field names or database schema
  3. Implement pagination: Don't fetch all records; use limit+skip for large datasets
  4. Cache registry & profiles: Use Redis to avoid repeated database queries for configuration
  5. Log with audit context: Include entityCode, recordId, userId, changes in audit logs
  6. Handle validation errors gracefully: Return field-level validation errors to client
  7. Use parameterized queries: Prevent SQL injection with .input('paramName', value)
  8. Implement soft deletes: Mark deleted records instead of removing them physically
  9. Apply access policies: Always call PolicyHelper to enforce field-level security
  10. Invalidate caches appropriately: Clear relevant caches after create/update/delete operations

See Database Configuration for complete object registry schema and Object Management for detailed entity design patterns.

Key Files & Imports​

PurposeFileNotes
Entry pointindex.jsApp init, middleware, routes, signal handling
Workflow engineservices/workflow.service.js973 lines, core execution logic
Entity read operationsservices/entity/read.service.jsOrchestrates multi-adapter entity reads with config/policy integration
Entity write operationsservices/entity/write.service.jsHandles create/update with validation, audit trails, concurrency
Entity delete operationsservices/entity/delete.service.jsManages soft-delete with cascade behavior and audit logging
Entity configurationservices/entity/configuration.service.jsLoads and merges base + tenant configs; validates payloads
Entity service gatewayservices/entity/gateway/entity-service-gateway.service.jsHigh-level orchestration of entity operations
Entity optimizationservices/optimized-entity.service.jsCaching, pagination, performance optimization wrapper
Config loaderconfig/index.jsEnvironment-based configuration
Error classesutils/errors.util.jsValidationError, NotFoundError, AuthorizationError, ServiceError, ConflictError
Loggingutils/logger.util.jsStructured logging with context (tenantId, userId, entityCode)
Policy enforcementutils/policy.helper.jsField-level access control, masking, projection based on prf_policy
Access controlutils/service-access-control.util.jsCRUD permission validation
DB accessconfig/db/db_connections.jsConnection pooling for config DB and tenant DBs
Routesroutes/API endpoint definitions for all entity operations
Controllerscontrollers/Request handlers that delegate to services
Servicesservices/Business logic (21+ domain-specific services)

Common Issues & Solutions​

| Issue | Cause | Solution | | ------------------------------------ | -------------------------------- | ------------------------------------------------------------------------------------------- | --- | ---------------------- | --------------------- | ----------------------------------------------- | | 401 Unauthorized on all requests | Missing/invalid JWT | Check token in Authorization header, validate JWT_SECRET matches | | 400 Decrypt tenant ID failed | Invalid tenant header | Ensure x-tenant-id is encrypted and matches TENANT_ENCRYPTION_KEY | | Query timeout on large datasets | No pagination | Implement offset/limit, add database indexes | | Workflow step hangs | Worker not running | Check npm run dev includes worker mode | | Redis connection refused | Redis not started | Ensure Redis running, check REDIS_URL correct | | SQL injection detected | String concatenation in query | Use parameterized queries with .input() | | Out of memory on startup | Large config database | Increase Node heap: NODE_OPTIONS=--max-old-space-size=4096 | | 404 on entity endpoint | Entity not registered | Check app_object_registries for entity obj_code | | 403 Forbidden on entity operation | Access policy denial | Check app_object_profiles prf_policy field-level rules or user roles | | Validation error on entity create | Config rule mismatch | Verify payload against ConfigurationService.validatePayload rules; check obj_payload schema | | Concurrent update conflict | Last modified timestamp mismatch | Reload record and retry; check for concurrent client updates | | Configuration cache stale | Manual config change in database | Run scripts/clear-redis-cache.js to invalidate configuration cache | | Entity read returns few columns | Policy masking applied | Check app_object_profiles prf_policy; may be hiding fields for user's role | | Delete fails with foreign key error | Dependent records exist | Try delete with cascade=true or delete dependent records first | | Data corruption after bulk operation | No transaction wrapper | Use database transactions for multi-step operations |

Deployment Checklist​

  • All environment variables set (.env or environments/*.env)
  • Redis running and accessible
  • MSSQL config database reachable
  • Connections table populated with tenant databases
  • JWT_SECRET configured and matches frontend
  • TENANT_ENCRYPTION_KEY set and secured
  • Worker mode enabled if using workflows
  • Cron jobs scheduled if needed
  • Health check endpoint responding (/api/v1/health)
  • Swagger docs accessible (/api-docs)
  • Logs being collected to file/service
  • Rate limiting configured appropriately