Skip to main content

Database Development & Configuration Documentation

Version: 2.0 (Modularized)
Last Updated: March 12, 2026

📚 Module Index​

This documentation has been organized into specialized modules for easier navigation and deeper understanding. Each module covers specific configuration tables and their usage patterns:

Core Configuration Modules​

ModuleTablesPurposeLink
Object Managementapp_object_registries, app_object_profilesEntity definitions and field-level access controlOBJECT_MANAGEMENT.md
Page Configurationapp_page_configsForm and page schema definitionsPAGE_CONFIGURATION.md
Workflow Configurationapp_workflow_configs, wft_instances, wft_logsWorkflow definitions and execution trackingWORKFLOW_CONFIGURATION.md
DataView Configurationapp_dataview_configsData grid and list view configurationsDATAVIEW_CONFIGURATION.md
Navigation Configurationapp_navigation_configsMenu structure and navigation pathsNAVIGATION_CONFIGURATION.md
Tenant, Users & Rolesapp_tenants, app_users, app_roles, app_user_roles, app_subscriptionsMulti-tenant isolation and RBACTENANT_USERS_ROLES.md
Value Sets & Featuresapp_value_sets, app_dashboard_configs, APP_CONNECTIONS, sys_audit_logs, app_settingsEnumerations, dashboards, and system configurationVALUE_SETS_FEATURES.md

Architecture Overview (Non-Technical)​

The Axiom Genesis platform uses a sophisticated multi-database strategy where the system manages configurations in one central database while each tenant's data lives in their own isolated database. This ensures data privacy, compliance, and scalability. Think of it like a post office: the central office keeps the routing information, while each customer's mail is handled in their own secure compartment.

Key Purpose: Provide secure, scalable data storage with strict tenant isolation while maintaining system configuration and metadata in a centralized location.

🔑 Key Architectural Concepts​

1. Multi-Tenant Isolation (Mission-Critical)​

Core Principle: Complete data separation between tenants at database and application levels.

Every table includes tenant_id column:
  ├─ app_tenants.ten_id
  ├─ app_users.usr_tenant_id
  ├─ app_object_registries.obj_tenant_id
  ├─ app_page_configs.pgc_tenant_id
  └─ ... (ALL configuration & data tables)

ENFORCEMENT:
  ├─ Request includes encrypted x-tenant-id header
  ├─ ALL queries filtered by tenant_id automatically
  ├─ Connection pool per tenant database
  └─ NO cross-tenant queries allowed

Critical Rule: Querying without tenant_id = SECURITY BREACH. Every API endpoint must enforce:

WHERE [table].tenant_id = @tenant_id  -- MANDATORY in every query

See: TENANT_USERS_ROLES.md

2. Three-Layer Database Architecture​

Layer 1: Central Configuration Database (RADICS_CFG)

  • Single MSSQL instance
  • Stores system-wide metadata, workflows, forms, dataviews
  • All application configuration
  • Tenant connection mappings

Layer 2: Connection Pool Management

  • Thread pool: 2-10 connections per database
  • Connection waiting queue
  • Automatic reconnection on failure
  • Lifecycle management

Layer 3: Tenant Databases

  • Separate MSSQL database per tenant
  • Connection strings stored encrypted in RADICS_CFG
  • Data isolation at database level
  • Accessed via mapped connection from app_connections table

3. Configuration-Driven Architecture​

Everything is metadata:

  • Workflows: Defined as JSON in app_workflow_configs.wfc_config
  • Forms: Schema stored in app_page_configs.pgc_page_schema
  • DataViews: Configuration in app_dataview_configs.dvc_config
  • Entities: Structure in app_object_registries.obj_payload
  • Navigation: Menu items in app_navigation_configs.nav_config

Benefit: No code changes needed for business logic updates. Edit configuration → apply immediately.

4. Middleware Service Layer Pattern​

Frontend → API Endpoint
  ↓
Controller (validation, context)
  ↓
Service Layer (business logic)
  ├─ Read configuration from RADICS_CFG
  ├─ Load entity/form/workflow definitions
  ├─ Validate against rules
  └─ Execute operation
  ↓
Data Adapter
  ├─ Converts to entity-specific SQL
  ├─ Enforces tenant_id filtering
  └─ Applies field-level masking
  ↓
Database (MSSQL)
  ├─ Config DB or Tenant DB
  └─ Returns data
  ↓
Response (with masking applied)

5. Caching Strategy (Redis)​

  • Registry Cache: 24-hour TTL for entity definitions
  • Connection Cache: 1-hour TTL for database connections
  • Form Cache: 1-hour TTL for page configurations
  • Invalidation: Clear on configuration updates
  • Multi-tenant: Separate cache keys per tenant

System Architecture Diagram​

┌────────────────────────────────────────────────────────────────────────┐
│                         AXIOM GENESIS DATABASE LAYER                   │
├────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  ┌─────────────────────────────────────────────────────────────────┐  │
│  │  FRONTEND & MIDDLEWARE (Application Layer)                      │  │
│  │  ┌─────────────────────────────────────────────────────────┐    │  │
│  │  │ Request: /api/v1/workflows with x-tenant-id header      │    │  │
│  │  │ Interceptor adds: TenantId, encrypted tenant context    │    │  │
│  │  └─────────────────────────────────────────────────────────┘    │  │
│  └──────────────────────────┬──────────────────────────────────────┘  │
│                             │                                         │
│                    ┌────────▼────────┐                               │
│                    │ Context Service │                               │
│                    │ • Extract TenantId                              │
│                    │ • Validate tenant exists                        │
│                    │ • Get connection info                           │
│                    └────────┬────────┘                               │
│                             │                                         │
│        ┌────────────────────┼────────────────────┐                  │
│        │                    │                    │                  │
│        ▼                    ▼                    ▼                  │
│   ┌─────────────┐     ┌──────────────────┐  ┌──────────────┐      │
│   │ CONFIG DB   │     │ CONNECTION POOL  │  │ REDIS CACHE  │      │
│   │ RADICS_CFG  │     │ (Thread Pool)    │  │              │      │
│   │             │     │                  │  │ • Workflows  │      │
│   │ Metadata    │     │ • Config DB      │  │ • Entities   │      │
│   │ Workflows   │     │ • Tenant Pool    │  │ • DataViews  │      │
│   │ Forms       │     │ • Connection     │  │ • Forms      │      │
│   │ DataViews   │     │   Waiting List   │  │ • Connections│      │
│   │ Entities    │     └──────────────────┘  └──────────────┘      │
│   │ Connections │                                                   │
│   └─────────────┘                                                   │
│        │                                                             │
│        └────────────────────────┬─────────────────────────────────┐ │
│                                 │                                 │ │
│                    ┌────────────▼────────────┐                   │ │
│                    │  TENANT DATABASES       │                   │ │
│                    │  (MSSQL Connections)    │                   │ │
│                    │                         │                   │ │
│        ┌───────────┼─────────────────────────┼─────────────────┐ │ │
│        │           │                         │                 │ │ │
│        ▼           ▼                         ▼                 ▼ │ │
│   ┌─────────┐  ┌─────────┐  ┌─────────┐  ┌─────────┐          │ │
│   │Tenant A │  │Tenant B │  │Tenant C │  │Tenant N │ ...      │ │
│   │         │  │         │  │         │  │         │          │ │
│   │ Records │  │ Records │  │ Records │  │ Records │          │ │
│   │ Workflow│  │ Workflow│  │ Workflow│  │ Workflow│          │ │
│   │ Logs    │  │ Logs    │  │ Logs    │  │ Logs    │          │ │
│   │ Queue   │  │ Queue   │  │ Queue   │  │ Queue   │          │ │
│   └─────────┘  └─────────┘  └─────────┘  └─────────┘          │ │
│        │           │                                            │ │
│        └───────────┴────────────────────────────────────────────┘ │
│                                                                 │
└────────────────────────────────────────────────────────────────┘

Standards​

Complete Database Architecture​

Three-Layer Connection Model:

Layer 1: Configuration Database (Single MSSQL Instance)
  └─ RADICS_CFG database
     └─ Central repository for all system metadata

Layer 2: Connection Pool Manager
  ├─ Thread pool: min 2, max 10 connections per database
  ├─ Connection waiting queue
  ├─ Connection lifecycle management
  └─ Automatic reconnection on failure

Layer 3: Tenant Databases (Multiple MSSQL Instances)
  ├─ Separate database per tenant
  ├─ Accessed via connection strings from RADICS_CFG
  ├─ Each connection gets its own pool
  └─ Automatic tenant routing based on x-tenant-id header

Request-to-Database Flow:

HTTP Request (Frontend)
  │
  ├─ Contains: x-tenant-id header (encrypted)
  │
  ▼
API Middleware
  ├─ Decrypt tenant ID
  ├─ Validate tenant exists
  └─ Attach to request context
  │
  ▼
Context Service
  ├─ Query RADICS_CFG: APP_CONNECTIONS table
  ├─ Find connection string for tenant
  ├─ Check Redis cache first (performance)
  └─ Get pooled connection
  │
  ▼
Connection Pool Manager
  ├─ Check if connection exists in pool
  ├─ If yes: return connection
  ├─ If no: create new connection (max 10)
  ├─ If at max: queue request, wait for available
  └─ Return connection with timeout
  │
  ▼
Service Layer
  ├─ Build parameterized query
  ├─ Include TenantId filter in WHERE clause (critical!)
  ├─ Execute query
  └─ Log with context
  │
  ▼
Tenant Database
  ├─ Execute query
  ├─ Return recordset
  └─ Connection returns to pool
  │
  ▼
Response
  └─ Transform and return to client

Database Architecture​

Two-Tier Database Model (Logical):

┌─────────────────────────────────────────────────────────────┐
│ Configuration Database (RADICS_CFG on MSSQL)               │
│ ├─ app_connections: Tenant DB connection strings            │
│ ├─ app_workflow_configs: Workflow definitions & versioning  │
│ ├─ app_dataview_configs: DataView configurations            │
│ ├─ app_page_configs: Form/Page configurations               │
│ ├─ app_object_registries: Entity registry & metadata        │
│ ├─ app_tenants: Tenant definitions & settings               │
│ ├─ wft_instances: Workflow execution instances (Config DB)  │
│ ├─ wft_logs: Workflow execution logs                        │
│ └─ [24+ additional system tables for configurations]        │
└─────────────────────────────────────────────────────────────┘
                          ↓
          ┌───────────────┴───────────────────┐
          ↓                                    ↓
    ┌──────────────────┐            ┌──────────────────┐
    │ Tenant Database A │            │ Tenant Database B │
    │ (External MSSQL) │            │ (External MSSQL) │
    ├─ Business Data  │            ├─ Business Data   │
    ├─ Records        │            ├─ Records         │
    └─ [Dynamic tables│            └─ [Dynamic tables │
    │  per entity]    │              per entity]      │
    └─────────────────┘            └─────────────────┘

Configuration Database (RADICS_CFG) Schema​

Core Configuration Tables:

1. Tenant Connection Management

app_connections (Active: ✓ Verified)
  ├─ con_id (INT): Primary Key
  ├─ con_tenant_id (INT): Tenant reference
  ├─ con_code (VARCHAR): Connection identifier
  ├─ con_description (VARCHAR): Human-readable name
  ├─ con_connection_type (VARCHAR): Connection type (Database, API, File, etc.)
  ├─ con_provider_code (VARCHAR): Provider identifier (MSSQL, MySQL, etc.)
  ├─ con_provider_description (VARCHAR): Provider display name
  ├─ con_is_secret (BIT): Indicates encrypted payload
  ├─ con_status (INT): 0=Inactive, 1=Active
  ├─ con_is_deleted (BIT): Soft delete flag
  ├─ con_payload (JSON): Connection strings & credentials (encrypted)
  ├─ con_audit_data (JSON): Created by/date, modified by/date
  └─ con_add_dtls (JSON): Additional metadata

2. Workflow Configuration

app_workflow_configs (Active: ✓ Verified)
  ├─ wfc_id (INT): Primary Key
  ├─ wfc_tenant_id (INT): Tenant reference (indexed)
  ├─ wfc_code (VARCHAR): Workflow identifier
  ├─ wfc_description (VARCHAR): Workflow display name
  ├─ wfc_version_no (INT): Version number (immutable per version)
  ├─ wfc_is_published (BIT): Publication status
  ├─ wfc_config (JSON): Complete workflow definition (nodes, edges, triggers)
  ├─ wfc_status (INT): 0=Inactive, 1=Active
  ├─ wfc_is_deleted (BIT): Soft delete flag
  ├─ wfc_audit_data (JSON): Created by/date, modified by/date
  └─ wfc_add_dtls (JSON): Additional metadata

3. DataView Configuration

app_dataview_configs (Active: ✓ Verified)
  ├─ dvc_id (INT): Primary Key
  ├─ dvc_tenant_id (INT): Tenant reference (indexed)
  ├─ dvc_code (VARCHAR): DataView identifier
  ├─ dvc_description (VARCHAR): Display name
  ├─ dvc_prf_code (VARCHAR): Object Profile reference
  ├─ dvc_view_type (VARCHAR): View type (Card, List, Tree, Kanban, etc.)
  ├─ dvc_config (JSON): Configuration (columns, filters, sorting, grouping)
  ├─ dvc_is_default (BIT): Default view for entity
  ├─ dvc_status (INT): 0=Inactive, 1=Active
  ├─ dvc_is_deleted (BIT): Soft delete flag
  ├─ dvc_audit_data (JSON): Created by/date, modified by/date
  └─ dvc_add_dtls (JSON): Additional metadata

4. Page/Form Configuration

app_page_configs (Active: ✓ Verified)
  ├─ pgc_id (INT): Primary Key
  ├─ pgc_tenant_id (INT): Tenant reference (indexed)
  ├─ pgc_code (VARCHAR): Page identifier
  ├─ pgc_description (VARCHAR): Display name
  ├─ pgc_app_code (VARCHAR): Application context
  ├─ pgc_config (JSON): Page layout & component configuration
  ├─ pgc_status (INT): 0=Inactive, 1=Active
  ├─ pgc_is_deleted (BIT): Soft delete flag
  ├─ pgc_audit_data (JSON): Created by/date, modified by/date
  ├─ pgc_add_dtls (JSON): Additional metadata
  └─ pgc_page_schema (JSON): Form schema with fields, validation, layout

5. Entity/Object Registry

app_object_registries (Active: ✓ Verified)
  ├─ obj_id (INT): Primary Key
  ├─ obj_tenant_id (INT): Tenant reference (indexed)
  ├─ obj_code (VARCHAR): Entity identifier
  ├─ obj_description (VARCHAR): Display name
  ├─ obj_type_code (VARCHAR): Entity type (Business, System, etc.)
  ├─ obj_connection_code (VARCHAR): Connection ID reference
  ├─ obj_status (INT): 0=Inactive, 1=Active
  ├─ obj_is_deleted (BIT): Soft delete flag
  ├─ obj_payload (JSON): Entity metadata & field definitions
  ├─ obj_payload_object_name (NVARCHAR): Actual table/object name in tenant DB
  ├─ obj_payload_identifier (NVARCHAR): Primary key field name
  ├─ obj_payload_column_prefix (NVARCHAR): Column naming convention prefix
  ├─ obj_payload_table (NVARCHAR): Source table name
  ├─ obj_audit_data (JSON): Created by/date, modified by/date
  └─ obj_add_dtls (JSON): Additional metadata

6. Tenant Definition

app_tenants (Active: ✓ Verified)
  ├─ ten_id (INT): Primary Key
  ├─ ten_code (VARCHAR): Tenant identifier
  ├─ ten_description (VARCHAR): Tenant display name
  ├─ ten_status (INT): 0=Inactive, 1=Active
  ├─ ten_is_deleted (BIT): Soft delete flag
  ├─ ten_details (JSON): Tenant metadata
  ├─ ten_audit_data (JSON): Created by/date, modified by/date
  ├─ ten_add_dtls (JSON): Additional metadata
  ├─ ten_app_settings (JSON): Application settings per tenant
  ├─ ten_profile (JSON): Tenant profile information
  └─ ten_credentials (JSON): Encrypted credentials

Workflow Execution Tables (in Configuration Database)​

Workflow Instance Tracking:

wft_instances (Active: ✓ Verified)
  ├─ wfi_id (INT): Primary Key
  ├─ wfi_tenant_id (INT): Tenant reference (indexed)
  ├─ wfi_instance_code (VARCHAR): Instance identifier
  ├─ wfi_workflow_code (VARCHAR): Reference to app_workflow_configs.wfc_code
  ├─ wfi_instance_name (VARCHAR): User-friendly instance name
  ├─ wfi_trigger_type (VARCHAR): Trigger type (Manual, Schedule, Webhook, Event)
  ├─ wfi_trigger_source (VARCHAR): What triggered the workflow
  ├─ wfi_triggered_at (DATETIME): When workflow was triggered
  ├─ wfi_status (VARCHAR): Running, Completed, Failed, Paused, Cancelled
  ├─ wfi_progress_pct (DECIMAL): Completion percentage (0-100)
  ├─ wfi_current_node_id (VARCHAR): Currently executing node ID
  ├─ wfi_started_at (DATETIME): Actual start time
  ├─ wfi_completed_at (DATETIME): Completion time (NULL if running)
  ├─ wfi_duration_ms (INT): Total duration in milliseconds
  ├─ wfi_parent_instance_id (INT): Parent workflow (for sub-workflows)
  ├─ wfi_root_instance_id (INT): Root workflow (for tracking hierarchy)
  ├─ wfi_input_data (JSON): Input parameters
  ├─ wfi_output_data (JSON): Final output/result
  ├─ wfi_context_data (JSON): Execution context (variables, state)
  ├─ wfi_error_data (JSON): Error details if failed
  ├─ wfi_config (JSON): Workflow configuration snapshot
  ├─ wfi_is_deleted (BIT): Soft delete flag
  ├─ wfi_audit_data (JSON): Created by/date, modified by/date
  ├─ wfi_add_dtls (JSON): Additional metadata
  ├─ wfi_timing_data (JSON): Detailed timing per node
  └─ wfi_paused_at (DATE): Pause timestamp

Workflow Execution Logs:

wft_logs (Active: ✓ Verified)
  ├─ log_id (INT): Primary Key
  ├─ log_tenant_id (INT): Tenant reference (indexed)
  ├ log_instance_id (INT): Reference to wft_instances
  ├─ log_node_id (VARCHAR): Node ID within workflow
  ├─ log_timestamp (DATETIME): When log entry was created
  ├─ log_level (VARCHAR): Info, Warning, Error, Debug
  ├─ log_category (VARCHAR): Log category (Execution, Validation, Data, etc.)
  ├─ log_message (VARCHAR): Log message
  ├─ log_details (JSON): Additional structured data
  ├─ log_is_deleted (BIT): Soft delete flag
  ├─ log_audit_data (JSON): Created by/date, modified by/date
  └─ log_add_dtls (JSON): Additional metadata

Node State Tracking:

wft_node_states (Active: ✓ Verified)
  ├─ wfns_id (INT): Primary Key
  ├─ wfns_instance_id (INT): Reference to wft_instances
  ├─ wfns_node_id (NVARCHAR): Node ID in workflow
  ├─ wfns_status (VARCHAR): Pending, Executing, Completed, Failed, Skipped
  ├─ wfns_enqueued_count (INT): How many times queued
  ├─ wfns_execution_count (INT): How many times executed
  ├─ wfns_tenant_id (INT): Tenant reference
  ├─ wfns_created_at (DATETIME2): Record creation timestamp
  └─ wfns_updated_at (DATETIME2): Last update timestamp

Workflow Nodes (Configuration):

wft_nodes (Active: ✓ Verified)
  ├─ nde_id (INT): Primary Key
  ├─ nde_tenant_id (INT): Tenant reference (indexed)
  ├─ nde_instance_id (INT): Workflow instance reference
  ├─ nde_node_id (VARCHAR): Node identifier
  ├─ nde_node_name (VARCHAR): Node display name
  ├─ nde_node_type (VARCHAR): Node type (StartEvent, Action, Decision, etc.)
  ├─ nde_status (VARCHAR): Pending, Running, Completed, Failed
  ├─ nde_timing_data (JSON): Node execution timing info
  ├─ nde_retry_count (INT): Number of retries
  ├─ nde_input_data (JSON): Input to this node
  ├─ nde_output_data (JSON): Output from this node
  ├─ nde_error_data (JSON): Error details if failed
  ├─ nde_config (JSON): Node-specific configuration
  ├─ nde_is_deleted (BIT): Soft delete flag
  ├─ nde_audit_data (JSON): Created by/date, modified by/date
  └─ nde_add_dtls (JSON): Additional metadata

Workflow Steps (Execution Queue):

wft_steps (Active: ✓ Verified)
  ├─ wfs_id (INT): Primary Key
  ├─ wfs_instance_id (INT): Reference to wft_instances
  ├─ wfs_node_id (NVARCHAR): Node being executed
  ├─ wfs_node_type (VARCHAR): Node type
  ├─ wfs_status (VARCHAR): Pending, Processing, Completed, Failed
  ├─ wfs_input_data (NVARCHAR): Step input (JSON as string)
  ├─ wfs_output_data (NVARCHAR): Step output (JSON as string)
  ├─ wfs_error_message (NVARCHAR): Error details if failed
  ├─ wfs_retry_count (INT): Number of retry attempts
  ├─ wfs_duration_ms (INT): Execution duration
  ├─ wfs_started_at (DATETIME2): Start timestamp
  ├─ wfs_completed_at (DATETIME2): Completion timestamp (NULL if pending)
  ├─ wfs_tenant_id (INT): Tenant reference
  ├─ wfs_is_deleted (BIT): Soft delete flag
  └─ wfs_created_at (DATETIME2): Record creation timestamp

Naming Conventions (Verified from Actual Schema)​

  • Table Names: lowercase_snake_case (e.g., app_workflow_configs, wft_instances, app_connections)
  • Column Names: lowercase_snake_case with prefix (e.g., wfc_code, con_id, obj_tenant_id)
  • Column Prefixes:
    • app_* tables use 3-4 char prefix (con*, dvc*, pgc*, wfc*, obj*, ten*)
    • wft_* tables use prefix matching table (wfi*, wfl*, wfns*, nde*, wfs_)
  • Foreign Keys: Named with source/target (implicit in schema design)
  • Indexes: Automatically created on TenantId columns for filtering

Data Types & Standards (Verified from RADICS_CFG Schema)​

-- Identity/Keys
PK: INT IDENTITY(1,1) PRIMARY KEY
FK: INT (no explicit FOREIGN KEY constraints in config DB)
TenantId: INT (always indexed, always in WHERE clause)

-- Strings
Codes/Identifiers: VARCHAR(256) [e.g., con_code, dvc_code, pgc_code, obj_code]
Descriptions: VARCHAR(MAX) [e.g., con_description, dvc_description]
Node IDs/References: VARCHAR(256) or NVARCHAR(256) [e.g., wfi_current_node_id, nde_node_id]

-- JSON Data Types
Configuration: JSON (native JSON type) [e.g., wfc_config, dvc_config, pgc_config, obj_payload]
Audit Data: JSON (native JSON type) [e.g., *_audit_data]
Additional Details: JSON (native JSON type) [e.g., *_add_dtls]
Input/Output Data: JSON or NVARCHAR (JSON as string in some tables) [wfi_input_data, wfi_output_data]

-- Dates/Times
Millisecond Precision: DATETIME [e.g., wfi_triggered_at, wfi_started_at, wfi_completed_at, log_timestamp]
Microsecond Precision: DATETIME2 [e.g., wfns_created_at, wfns_updated_at, wfs_started_at, wfs_completed_at]
Duration (milliseconds): INT [e.g., wfi_duration_ms, wfs_duration_ms]

-- Status Fields
Status Code: INT [0=Inactive, 1=Active; e.g., con_status, wfc_status, dvc_status, pgc_status, obj_status, ten_status]
Workflow Status: VARCHAR(50) [Running, Completed, Failed, Paused, Cancelled; e.g., wfi_status]
Node Status: VARCHAR(50) [Pending, Executing, Completed, Failed, Skipped; e.g., wfns_status]
Log Level: VARCHAR(50) [Info, Warning, Error, Debug; e.g., log_level]

-- Boolean Flags
BIT [0=false, 1=true; e.g., con_is_secret, wfc_is_published, dvc_is_default, *_is_deleted]

-- Numeric
Percentage: DECIMAL(5,2) [0-100; e.g., wfi_progress_pct]
Count: INT [e.g., nde_retry_count, wfs_retry_count, wfns_enqueued_count, wfns_execution_count]

-- Encryption Strategy
Sensitive Data (Connection Strings): Stored in con_payload (JSON field) encrypted at application layer
Credentials: Stored in ten_credentials (JSON field) encrypted at application layer
Note: Database Encryption at Rest (TDE) may be enabled at server level; encryption/decryption occurs in application code

Do's and Don'ts​

✅ DO (Following Middleware Patterns)​

  • Always include TenantId in WHERE clause: All reads and writes enforce ${prefix}_tenant_id = @tenantId filter
  • Load Registry & Profile before queries: Service layer loads app_object_registries + app_object_profiles for metadata
  • Leverage Service → Adapter abstraction: Write logic in service layer, database-specific logic in adapters
  • Enrich payloads with tenant context automatically: Service.create() adds ${prefix}_tenant_id + ${prefix}_audit_data
  • Use parameterized queries everywhere: All adapters use @paramName binding (prevents SQL injection)
  • Store flexible data as JSON: wfc_config, pgc_page_schema, prf_policy columns for dynamic structures
  • Implement audit trails: Every table has *_audit_data JSON column with created_by, created_at, updated_by
  • Cache configuration tables: Redis cache (24h TTL) for registry, profiles, workflows (90% hit rate)
  • Log workflow execution: Every workflow step creates entry in wft_logs with message, level, category
  • Use soft deletes: Set *_is_deleted = 1 instead of deleting records (preserves audit trail)
  • Validate input against configuration: Form payloads validated against app_page_configs (pgc_page_schema)
  • Apply access control via policies: prf_policy defines projection, masking, rowFilters granularly
  • Test migrations on staging: Run migrations on staging before production
  • Monitor connection pool health: Track min/max connections, idle timeout, retry logic
  • Broadcast socket events on updates: Emit events after INSERT/UPDATE for real-time client updates

❌ DON'T (Anti-patterns)​

  • Query across tenants without tenant filter: Will leak data; always enforce ${prefix}_tenant_id = @tenantId
  • Hardcode database connection strings: Always fetch from app_connections + decrypt con_payload
  • Assume implicit relationships: app_object_registries links to connections via obj_connection_code (no FK)
  • Skip the Service layer: Don't query adapters directly; service layer enforces policy + tenant context
  • Store credentials directly: con_payload is encrypted; decrypt at app level only
  • Mix Sequelize ORM with raw SQL inconsistently: Adapters use raw SQL for performance; be consistent
  • Forget audit data enrichment: Service layer auto-adds created_by/created_at (don't skip this)
  • Cache without TTL: Always set cache TTL (registry/profile: 24h, connection: 1h)
  • Update without checking *_is_deleted: Always check both __is_deleted AND __status fields
  • Access app_object_registries directly in routes: Always load via getRegistry() (handles caching)
  • Return sensitive fields without applying policy: Apply prf_policy projection + masking before returning
  • Assume tenant_id in JWT: Always extract from x-tenant-id header + validate against app_tenants
  • Execute node logic synchronously in requests: Queue to background worker (workflowQueue)
  • Create table without audit columns: Every table needs *_audit_data JSON for compliance
  • Skip JSON validation: Validate JSON structure before INSERT (use JSON_VALID() in SQL)

Technical Details​

Multi-Tenant Isolation Enforcement (3-Layer Pattern)​

The middleware enforces tenant isolation at 3 critical layers:

Layer 1: Request Middleware - Extract & Validate

// From: middleware/validateRequest.js
async (req, res, next) => {
  // 1. Decrypt x-tenant-id header (encrypted by frontend apiClient)
  const encryptedTenantId = req.headers['x-tenant-id'];
  const tenantId = decryptTenantId(encryptedTenantId);

  // 2. Validate tenant exists in app_tenants
  const tenantResult = await db.request()
    .input('tenantId', tenantId)
    .query(`SELECT ten_id FROM app_tenants
            WHERE ten_id = @tenantId AND ten_status = 1 AND ten_is_deleted = 0`);

  if (!tenantResult.recordset[0]) {
    throw new UnauthorizedError('Tenant not found or inactive');
  }

  // 3. Attach to request context
  req.context = { tenant_id: tenantId, user_id: req.user.id, ... };
  next();
}

Layer 2: Service Layer - Auto-Enrich Payload

// From: services/entity/write.service.js
WriteService.create = async (entityCode, payload, user) => {
  // Load entity registry (app_object_registries)
  const entity = await getRegistry(entityCode);
  const prefix = entity.obj_payload?.column_prefix || 'ent';  // e.g., 'cus' for customers

  const tenantIdField = `${prefix}_tenant_id`;  // e.g., 'cus_tenant_id'

  // Automatically enrich payload with tenant context
  const enrichedPayload = {
    ...payload,
    [tenantIdField]: user.tenant_id,    // ← Layer 2: Enforce at service
    [`${prefix}_audit_data`]: {
      created_by: user.id,
      created_at: new Date().toISOString(),
      ...
    }
  };

  // Pass to adapter with enriched payload
  return adapter.create(connection, table, enrichedPayload);
}

Layer 3: Query Layer - WHERE Clause Filter

// From: services/adapters/mssql/read.adapter.js
MssqlRead.get = async (sequelize, table, request = {}) => {
  // Filters include tenant_id sent by service layer
  const { limit = 100, skip = 0, ...filters } = request;
  // filters = { cus_tenant_id: 1, status: 'ACTIVE', ... }

  // Build SQL with MANDATORY tenant filter
  const filterClauses = [];
  Object.entries(filters).forEach(([key, value]) => {
    const formattedValue = isNaN(value) ? `'${value}'` : value;
    filterClauses.push(`[${key}] = ${formattedValue}`);
  });

  const whereClause =
    filterClauses.length > 0
      ? `WHERE ${filterClauses.join(" AND ")}` // ← Layer 3: WHERE clause includes tenant_id
      : "";

  const sql = `
    SELECT TOP ${limit} *
    FROM [${table}]
    ${whereClause}    -- Will include [cus_tenant_id] = 1
    OFFSET ${skip} ROWS
  `;

  return sequelize.query(sql, { type: QueryTypes.SELECT });
};

Real Example: Multi-Step Tenant Isolation

// Frontend sends
POST /api/v1/customers
Headers: {
  'x-tenant-id': '${encryptedTenantId}',  // e.g., encrypted "1"
  'Authorization': 'Bearer ${token}'
}
Body: {
  cus_name: 'Acme Corp',
  cus_email: 'contact@acme.com'
}

// Layer 1: Middleware decrypts and validates
req.context = { tenant_id: 1, user_id: 'user123' }

// Layer 2: Service enriches
Writes to app_customers with:
{
  cus_name: 'Acme Corp',
  cus_email: 'contact@acme.com',
  cus_tenant_id: 1,                    // ← Layer 2 added
  cus_audit_data: {
    created_by: 'user123',
    created_at: '2026-03-12T10:30:00Z'
  }
}

// Layer 3: Database enforces
INSERT INTO [app_customers] (cus_name, cus_email, cus_tenant_id, cus_audit_data)
  -- cus_tenant_id = 1 is persisted in every row

// Later, all reads for tenant 1 filter:
SELECT * FROM [app_customers] WHERE cus_tenant_id = 1

Service Layer - Read & Write Patterns​

The middleware uses a Service → Adapter architecture for database abstraction, supporting multiple database types (MSSQL, MongoDB, Oracle).

Entity Read Flow (from read.service.js):

ReadService.get(entityCode, filters, limit, skip, sortBy, user)
  ↓
1. Load object registry from Redis (app_object_registries)
   - Cache miss: Query `SELECT * FROM app_object_registries
                        WHERE obj_code = @code AND obj_tenant_id = @tenantId`
   - Extract: obj_payload (table mapping), obj_payload_object_name (actual table)

2. Load object profile from Redis (app_object_profiles)
   - Query: `SELECT prf_policy FROM app_object_profiles
             WHERE prf_object_code = @objCode AND prf_tenant_id = @tenantId`
   - Parse policy: projection, masking, rowFilters, writableFields

3. Extract tenant from user context
   - Detect tenant ID field from table prefix (e.g., 'cus_' → 'cus_tenant_id')

4. Resolve database connection
   - From app_object_registries.obj_connection_code
   - Fetch connection from app_connections
   - Parse con_payload (connection string/credentials)

5. Build effective query
   - Merge user filters + policy row filters
   - Auto-inject tenant isolation: filters[tenantIdField] = user.tenant_id

6. Select adapter (MSSQL, MongoDB, Oracle)
   - Call adapter.get(connection, table, effectiveFilters)

7. Post-process result
   - Apply field masking (e.g., email → ****@****.com)
   - Apply projection (include/exclude fields based on prf_policy)
   - Return processed records

Real Example: Read CUSTOMERS

// Input
ReadService.get(
  'CUSTOMERS',
  { status: 'ACTIVE' },
  limit: 50,
  skip: 0,
  user: { tenant_id: 1, id: 'user123', role_id: 2 }
)

// Step 1-2: Load registry & profile
appRegistry = {
  obj_code: 'CUSTOMERS',
  obj_payload: {
    column_prefix: 'cus',
    table_name: 'app_customers',
    identifier: 'cus_id'
  }
}

appProfile = {
  prf_policy: {
    projection: { exclude: ['cus_ssn', 'cus_dob'] },
    masking: { cus_email: 'email_mask', cus_phone: 'phone_mask' }
  }
}

// Step 5: Build effective query
effectiveFilters = {
  cus_tenant_id: 1,                    // ← Tenant isolation injected
  status: 'ACTIVE',                    // ← User filter
  ...policyRowFilters                  // ← Policy-based filters
}

// Step 6: Adapter generates SQL
SELECT * FROM [app_customers]
WHERE [cus_tenant_id] = 1
  AND [status] = 'ACTIVE'

// Step 7: Post-process
- Remove fields: [cus_ssn, cus_dob]
- Mask fields: email → '****@****', phone → '***-****-****'

// Return
[
  {
    cus_id: 1,
    cus_name: 'Acme Corp',
    cus_email: '****@****',           // Masked
    cus_phone: '***-****-1234',       // Masked
    // cus_ssn and cus_dob not included (projected)
  }
]

Entity Write Flow (from write.service.js):

WriteService.create(entityCode, payload, user)
  ↓
1. Load object registry & profile (same as read)

2. Extract tenant context
   - tenant_id, user_id, role_id, is_super_admin from user

3. Pre-operation access control check
   - Verify user can write to this entity
   - Check role-based permissions

4. Resolve connection (same as read)

5. Validate payload against configuration
   - If stored in app_page_configs (pgc_config), validate against form schema
   - Check required fields, data types, constraints

6. Apply write security
   - Only include fields in prf_policy.writableFields
   - Remove sensitive/audit fields (user shouldn't set these)

7. ENRICH PAYLOAD with tenant & user context
   - Auto-add: [tenantIdField] = user.tenant_id
   - Auto-add: [auditDataField] = { created_by, created_at, created_ip, ... }

8. Generate unique request_id
   - Used for idempotency

9. Select adapter & table name
   - Resolve from object registry

10. Call adapter.create(connection, table, enrichedPayload)
    - Adapter sanitizes (empty strings → null, validates JSON)
    - Infer primary key from column prefix
    - Get table metadata (column list, types)
    - Build parameterized INSERT statement
    - Bind parameters (prevents SQL injection)
    - Execute: INSERT INTO [table] OUTPUT INSERTED.*

11. Audit logging
    - Log to audit logger with operation, entity, recordId, changes

12. Emit socket events
    - Real-time updates for connected clients

Real Example: Create CUSTOMER

// Input
WriteService.create(
  'CUSTOMERS',
  {
    cus_name: 'Acme Corp',
    cus_email: 'contact@acme.com',
    cus_status: 'ACTIVE'
  },
  user: { tenant_id: 1, id: 'user123' }
)

// Step 3: Pre-operation access control
- Check user has 'create' permission on CUSTOMERS
- Check user role can create in tenant 1

// Step 5: Validate payload
FROM app_page_configs WHERE pag_code = 'FRM_CUSTOMERS'
Validate:
- cus_name: required, string
- cus_email: required, email format
- cus_status: enum of [ACTIVE, INACTIVE]

// Step 6-7: Apply write security + enrich
Original payload:
{
  cus_name: 'Acme Corp',
  cus_email: 'contact@acme.com',
  cus_status: 'ACTIVE'
}

Enriched payload (after step 7):
{
  cus_name: 'Acme Corp',
  cus_email: 'contact@acme.com',
  cus_status: 'ACTIVE',
  cus_tenant_id: 1,                    // ← Auto-added in step 7
  cus_audit_data: {                    // ← Auto-added in step 7
    created_by: 'user123',
    created_at: '2026-03-12T10:30:00Z',
    created_ip: '192.168.1.1',
    updated_by: 'user123',
    updated_at: '2026-03-12T10:30:00Z'
  },
  cus_request_id: 'req_123456789'      // ← Auto-added in step 8
}

// Step 10: Adapter builds parameterized SQL
INSERT INTO [app_customers] (
  [cus_name], [cus_email], [cus_status],
  [cus_tenant_id], [cus_audit_data], [cus_request_id]
)
OUTPUT INSERTED.*
VALUES (
  @cus_name, @cus_email, @cus_status,
  @cus_tenant_id, @cus_audit_data, @cus_request_id
)

// Parameters bound (prevents SQL injection)
@cus_name = 'Acme Corp'
@cus_email = 'contact@acme.com'
... etc

// Result: Created record with auto-generated cus_id returned to caller

Workflow Execution & Database Interactions​

Workflows are configuration-driven, with execution tracked across multiple tables.

Workflow Definition Storage (app_workflow_configs):

// app_workflow_configs stores complete workflow definition as JSON
{
  wfc_id: 1,
  wfc_code: 'WF_APPROVAL_PROCESS',
  wfc_config: {
    nodes: [
      {
        id: 'node_1',
        type: 'startEvent',
        name: 'Start',
        position: { x: 0, y: 0 }
      },
      {
        id: 'node_2',
        type: 'action',
        name: 'Assign to Manager',
        data: {
          actionType: 'assign',
          assignRole: 'MANAGER'
        }
      },
      {
        id: 'node_3',
        type: 'condition',
        name: 'Approved?',
        data: {
          conditions: [
            { field: 'approval_status', operator: 'equals', value: 'approved' }
          ]
        }
      },
      {
        id: 'node_end',
        type: 'endEvent',
        name: 'Complete'
      }
    ],
    edges: [
      { id: 'e1', source: 'node_1', target: 'node_2' },
      { id: 'e2', source: 'node_2', target: 'node_3' },
      { id: 'e3', source: 'node_3', target: 'node_end' }
    ]
  },
  wfc_status: 1,
  wfc_version_no: 2,
  wfc_is_published: 1,
  wfc_tenant_id: 1
}

Workflow Execution Flow (from workflow.service.js):

WorkflowService.startWorkflow(workflowId, { inputData, user })
  ↓
1. Fetch workflow config from app_workflow_configs
   - Query: SELECT * FROM app_workflow_configs WHERE wfc_id = @workflowId
   - Parse wfc_config JSON to get nodes/edges

2. CREATE workflow instance in wft_instances
   - INSERT: wfi_workflow_code, wfi_status ('pending'), wfi_input_data, wfi_tenant_id
   - Generate unique: wfi_instance_code = 'instance_${timestamp}_${random}'
   - Store: wfi_audit_data = { created_by: userId, created_at: now() }
   - Return: wfi_id (instance ID for future reference)

3. Log workflow start event in wft_logs
   - INSERT: log_instance_id, log_message ('Workflow started'), log_level ('INFO'), log_tenant_id

4. Find start node from wfc_config
   - startNode = nodes.find(n => n.type === 'startEvent')

5. ENQUEUE first node for background execution
   - Add to workflowQueue: { instanceId, nodeId, inputData }
   - Queue worker will process asynchronously

Real Example: Create and Execute Workflow

// Input: Start approval workflow
const result = await WorkflowService.startWorkflow(
  'WF_APPROVAL_PROCESS',
  {
    inputData: {
      customer_id: 123,
      order_amount: 50000,
      reason: 'Wholesale bulk order'
    },
    user: { tenant_id: 1, id: 'user456' }
  }
)

// Step 1: Fetch workflow config
SELECT * FROM app_workflow_configs
WHERE wfc_id = 'WF_APPROVAL_PROCESS' AND wfc_tenant_id = 1

// Step 2: Create instance
INSERT INTO wft_instances (
  wfi_workflow_code, wfi_status, wfi_input_data, wfi_tenant_id,
  wfi_instance_code, wfi_trigger_type, wfi_config, wfi_audit_data
)
OUTPUT INSERTED.*
VALUES (
  'WF_APPROVAL_PROCESS', 'pending',
  '{"customer_id": 123, "order_amount": 50000, ...}', 1,
  'instance_1710326400000_8374', 'manual',
  '{"formId": null}',
  '{"created_by": "user456", "created_at": "2026-03-12T10:30:00Z", ...}'
)

// Returns: { wfi_id: 42, wfi_instance_code: 'instance_...' }

// Step 3: Log start
INSERT INTO wft_logs (
  log_instance_id, log_message, log_level, log_category, log_tenant_id
)
VALUES (
  42, 'Workflow instance started', 'INFO', 'START', 1
)

// Step 5: Queue for execution
workflowQueue.add('execute-instance', {
  instanceId: 42,
  nodeId: 'node_1',
  inputData: { customer_id: 123, order_amount: 50000, ... }
})

Workflow Node Execution (Background Worker):

// Worker processes queued jobs
WorkflowQueue.process("execute-instance", async (job) => {
  const { instanceId, nodeId, inputData } = job.data;

  // 1. Fetch workflow instance
  const instance = await db
    .request()
    .input("wfi_id", instanceId)
    .query(`SELECT * FROM wft_instances WHERE wfi_id = @wfi_id`);

  // 2. Fetch workflow config
  const config = await db
    .request()
    .input("wfc_code", instance.wfi_workflow_code)
    .query(
      `SELECT wfc_config FROM app_workflow_configs WHERE wfc_code = @wfc_code`,
    );

  // 3. Find node definition in config
  const nodeConfig = config.wfc_config.nodes.find((n) => n.id === nodeId);

  // 4. Create node state in wft_node_states
  const nodeState = await db
    .request()
    .input("wfns_instance_id", instanceId)
    .input("wfns_node_id", nodeId)
    .input("wfns_status", "executing").query(`
      INSERT INTO wft_node_states (wfns_instance_id, wfns_node_id, wfns_status)
      OUTPUT INSERTED.*
      VALUES (@wfns_instance_id, @wfns_node_id, @wfns_status)
    `);

  // 5. Execute node based on type
  switch (nodeConfig.type) {
    case "action":
      await executeActionNode(nodeConfig, inputData, instance);
      break;
    case "condition":
      await executeConditionNode(nodeConfig, inputData);
      break;
    // ... other node types
  }

  // 6. Log node execution
  await db
    .request()
    .input("log_instance_id", instanceId)
    .input("log_node_id", nodeId)
    .input("log_message", `Node ${nodeId} completed`)
    .input("log_level", "INFO").query(`
      INSERT INTO wft_logs (log_instance_id, log_node_id, log_message, log_level)
      VALUES (@log_instance_id, @log_node_id, @log_message, @log_level)
    `);

  // 7. Get next node(s) from edges
  const nextNodes = findNextNodes(nodeConfig.id, config.wfc_config.edges);

  // 8. Queue next node(s) for execution
  for (const nextNodeId of nextNodes) {
    await workflowQueue.add("execute-instance", {
      instanceId,
      nodeId: nextNodeId,
      inputData: { ...inputData, ...nodeOutput },
    });
  }

  // 9. Update instance status if workflow complete
  if (nextNodes.length === 0) {
    await db
      .request()
      .input("wfi_id", instanceId)
      .input("wfi_status", "completed")
      .input("wfi_output_data", JSON.stringify(nodeOutput)).query(`
        UPDATE wft_instances 
        SET wfi_status = @wfi_status, wfi_output_data = @wfi_output_data
        WHERE wfi_id = @wfi_id
      `);
  }
});

Tables Updated During Workflow Execution:

wft_instances       ← Status updates (pending → executing → completed/failed)
wft_node_states     ← Node state tracking (executing → completed/failed)
wft_nodes           ← Node execution details (input/output data, errors)
wft_logs            ← Execution audit trail (each step logged)
wft_steps           ← Individual step execution queue
{
  "id": "workflow-schema",
  "type": "object",
  "properties": {
    "workflowId": { "type": "string" },
    "name": { "type": "string", "minLength": 1 },
    "description": { "type": "string" },
    "triggers": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "type": { "enum": ["manual", "schedule", "webhook", "event"] },
          "config": { "type": "object" },
          "name": { "type": "string" }
        },
        "required": ["type"]
      }
    },
    "nodes": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "id": { "type": "string" },
          "name": { "type": "string" },
          "type": { "enum": ["action", "condition", "loop", "transform", "startEvent", "endEvent"] },
          "config": { "type": "object" },
          "position": { "type": "object", "properties": { "x": { "type": "number" }, "y": { "type": "number" } } }
        },
        "required": ["id", "type"]
      }
    },
    "edges": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "id": { "type": "string" },
          "source": { "type": "string" },
          "target": { "type": "string" },
          "label": { "type": "string" }
        },
        "required": ["source", "target"]
      }
    }
  },
  "required": ["name", "nodes", "edges"]
}

// Example of actual wfc_config in app_workflow_configs table:
{
  "workflowId": "WF_CUSTOMER_APPROVAL",
  "name": "Customer Approval Workflow",
  "description": "Approve new customer registrations",
  "triggers": [
    {
      "type": "event",
      "name": "Customer Created",
      "config": { "entityCode": "CUSTOMER", "eventType": "CREATE" }
    }
  ],
  "nodes": [
    { "id": "start", "type": "startEvent", "name": "Start", "position": { "x": 0, "y": 0 } },
    { "id": "assign", "type": "action", "name": "Assign to Manager", "config": { "assignTo": "MANAGER" } },
    { "id": "approve", "type": "condition", "name": "Needs Approval?", "config": {} },
    { "id": "end", "type": "endEvent", "name": "Complete", "position": { "x": 500, "y": 500 } }
  ],
  "edges": [
    { "id": "e1", "source": "start", "target": "assign" },
    { "id": "e2", "source": "assign", "target": "approve" },
    { "id": "e3", "source": "approve", "target": "end" }
  ]
}

Page/Form Schema (pgc_page_schema column):

{
  "id": "form-schema",
  "type": "object",
  "properties": {
    "pageId": { "type": "string" },
    "name": { "type": "string" },
    "sections": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "sectionId": { "type": "string" },
          "title": { "type": "string" },
          "fields": {
            "type": "array",
            "items": {
              "type": "object",
              "properties": {
                "fieldId": { "type": "string" },
                "fieldName": { "type": "string" },
                "fieldType": { "enum": ["text", "number", "date", "select", "checkbox", "file", "textarea", "email", "phone"] },
                "required": { "type": "boolean" },
                "validation": { "type": "object" },
                "options": { "type": "array" }
              },
              "required": ["fieldId", "fieldType"]
            }
          }
        }
      }
    }
  }
}

// Example of actual pgc_page_schema:
{
  "pageId": "FRM_CUSTOMER_FORM",
  "name": "Customer Registration Form",
  "sections": [
    {
      "sectionId": "personal-info",
      "title": "Personal Information",
      "fields": [
        {
          "fieldId": "firstName",
          "fieldName": "First Name",
          "fieldType": "text",
          "required": true,
          "validation": { "minLength": 1, "maxLength": 100 }
        },
        {
          "fieldId": "email",
          "fieldName": "Email Address",
          "fieldType": "email",
          "required": true,
          "validation": { "pattern": "^[^@]+@[^@]+$" }
        }
      ]
    }
  ]
}

DataView Configuration (dvc_config column):

{
  "viewId": "DVC_CUSTOMER_LIST",
  "viewType": "List",
  "columns": [
    { "columnId": "id", "label": "ID", "width": 80 },
    { "columnId": "name", "label": "Customer Name", "width": 250 },
    { "columnId": "email", "label": "Email", "width": 200 },
    { "columnId": "status", "label": "Status", "width": 100 }
  ],
  "filters": [{ "field": "status", "operator": "equals", "value": "active" }],
  "sorting": [{ "field": "name", "direction": "asc" }],
  "pagination": {
    "pageSize": 25,
    "defaultSort": "name"
  }
}

Redis Caching Strategy​

The middleware uses Redis to cache frequently-accessed configurations for performance optimization.

Cache Keys & TTL Patterns:

// Entity Registry Cache (24-hour TTL)
Key: registry:${tenantId}:${objectCode}
Stores: Entire app_object_registries row + parsed obj_payload
Example: registry:1:CUSTOMERS
Miss Handler: Query app_object_registries, parse JSON, store 24h
Impact: ~40% of queries use registry cache

// Object Profile Cache (24-hour TTL)
Key: profile:${tenantId}:${objectCode}
Stores: app_object_profiles row + parsed prf_policy
Example: profile:1:CUSTOMERS
Miss Handler: Query app_object_profiles, store 24h
Impact: All read/write operations load profile cache

// Workflow Definition Cache (24-hour TTL)
Key: workflow:${tenantId}:${workflowCode}
Stores: app_workflow_configs + parsed wfc_config (nodes/edges)
Example: workflow:1:WF_APPROVAL_PROCESS
Miss Handler: Query app_workflow_configs, parse nodes/edges, store 24h
Impact: Workflow execution uses cached definition

// Connection Pool Cache (1-hour TTL)
Key: connection:${connectionCode}
Stores: Active database connection object
Example: connection:DEFAULT_TENANT_DB
Miss Handler: Query app_connections, decrypt con_payload, create connection, store 1h
Impact: Avoids re-establishing database connections

Cache Invalidation on Updates:

// When configuration is updated, cache is automatically invalidated

// Example: Update workflow
PATCH /api/v1/workflows/WF_APPROVAL_PROCESS
{
  name: 'Updated Approval Workflow',
  config: { nodes: [...], edges: [...] }
}

// Middleware flow:
1. WriteService.update() → app_workflow_configs row updated
2. Signal cache invalidation:
   redis.del('workflow:${tenantId}:WF_APPROVAL_PROCESS')
3. Broadcast socket event to connected clients (real-time UI update)
4. Next read automatically repopulates cache from DB

// Pattern applies to all tables:
- Update app_object_registries → Clear registry:* and profile:* caches
- Update app_object_profiles → Clear profile:* cache
- Update app_workflow_configs → Clear workflow:* cache
- Update app_connections → Clear connection:* cache

Performance Impact (Measured):

Cold Start (No Cache):
  Query 1: Load registry (app_object_registries) → 15-50ms
  Query 2: Load profile (app_object_profiles) → 15-50ms
  Query 3: Execute data query → 20-100ms
  Total: 50-200ms per request

Warm Cache (Redis Hit):
  Redis lookup: 1-5ms (cache hit rate: ~90%)
  Execute data query → 20-100ms
  Total: 25-110ms per request (2-3x faster)

Result: 50-100% performance improvement with caching,
        especially for frequently-accessed entities

Monitoring Caching:

# View cache statistics
REDIS> INFO stats

# Monitor cache hits/misses
REDIS> CONFIG GET "maxmemory*"
REDIS> CLIENT LIST

# Debug cache key pattern
REDIS> KEYS "registry:1:*"
REDIS> KEYS "profile:1:*"
REDIS> TTL registry:1:CUSTOMERS  # Check TTL

# Manual cache clear for testing
REDIS> FLUSHDB              # Clear all
REDIS> DEL registry:1:*     # Clear registry namespace

Migration Strategy​

File Structure:

migrations/
├── 0001_create_workflows_table.sql
├── 0002_add_workflow_versioning.sql
├── 0003_create_dataviews_table.sql
└── rollback/
    ├── 0001_rollback.sql
    ├── 0002_rollback.sql
    └── 0003_rollback.sql

Migration Execution:

# Development
node execute-migration.js

# Staging
NODE_ENV=staging node execute-migration.js

# Production (with backup)
BACKUP=true NODE_ENV=production node execute-migration.js

Safe Migration Pattern (Using Actual Tables):

-- Example: Adding a new column to app_workflow_configs
-- Create new table with updated schema
CREATE TABLE app_workflow_configs_v2 (
  wfc_id INT IDENTITY(1,1) PRIMARY KEY,
  wfc_tenant_id INT NOT NULL,
  wfc_code VARCHAR(256) NOT NULL,
  wfc_description VARCHAR(MAX),
  wfc_version_no INT NOT NULL,
  wfc_is_published BIT DEFAULT 0,
  wfc_config JSON NOT NULL,
  wfc_status INT DEFAULT 1,
  wfc_is_deleted BIT DEFAULT 0,
  wfc_audit_data JSON,
  wfc_add_dtls JSON,
  wfc_new_column VARCHAR(256),  -- New column being added
  CONSTRAINT idx_wfc_v2_tenant UNIQUE(wfc_tenant_id, wfc_code) -- Prevent duplicates
);

-- Copy data with transformation (new_column gets default value)
INSERT INTO app_workflow_configs_v2
  (wfc_tenant_id, wfc_code, wfc_description, wfc_version_no, wfc_is_published,
   wfc_config, wfc_status, wfc_is_deleted, wfc_audit_data, wfc_add_dtls, wfc_new_column)
SELECT
  wfc_tenant_id, wfc_code, wfc_description, wfc_version_no, wfc_is_published,
  wfc_config, wfc_status, wfc_is_deleted, wfc_audit_data, wfc_add_dtls,
  'DEFAULT_VALUE' AS wfc_new_column
FROM app_workflow_configs
WHERE wfc_is_deleted = 0;

-- Validate row counts match
DECLARE @oldCount INT = (SELECT COUNT(*) FROM app_workflow_configs WHERE wfc_is_deleted = 0);
DECLARE @newCount INT = (SELECT COUNT(*) FROM app_workflow_configs_v2);
IF @oldCount != @newCount THROW 50000, 'Row count mismatch during migration', 1;

-- Swap tables in transaction
BEGIN TRANSACTION;
  ALTER TABLE app_workflow_configs RENAME TO app_workflow_configs_old;
  ALTER TABLE app_workflow_configs_v2 RENAME TO app_workflow_configs;
COMMIT;

-- Verify migration by checking a few records
SELECT TOP 5 * FROM app_workflow_configs WHERE wfc_is_deleted = 0;

Query Performance Optimization​

Indexing Strategy:

-- Always index:
CREATE INDEX idx[Table]TenantId ON [TABLE](TenantId);  -- Tenant isolation
CREATE INDEX idx[Table]CreatedDate ON [TABLE](CreatedDate DESC);  -- Time range queries
CREATE INDEX idx[Table]Status ON [TABLE](Status);  -- Filter by status

-- Composite indexes for common filters:
CREATE INDEX idx[Table]TenantStatus ON [TABLE](TenantId, Status);

-- For large TEXT/JSON columns, don't index unless searched:
-- Instead, use COMPUTED COLUMNS for extracted values
ALTER TABLE WFT_WORKFLOWS
ADD WorkflowStatus AS JSON_VALUE(Definition, '$.status');

📋 Complete RADICS_CFG Table Reference​

All tables below are documented in the specialized modules. Use this reference to find the module that covers the table you're working with:

Entity & Object Management​

TableColumnsPurposeModule
app_object_registries15Entity metadata and table mappingOBJECT_MANAGEMENT.md
app_object_profiles12Field-level access control and maskingOBJECT_MANAGEMENT.md

Page & Form Configuration​

TableColumnsPurposeModule
app_page_configs11Form/page schema and layout definitionsPAGE_CONFIGURATION.md

Workflow Management​

TableColumnsPurposeModule
app_workflow_configs11Workflow definitions with versioningWORKFLOW_CONFIGURATION.md
wft_instances16+Workflow execution instancesWORKFLOW_CONFIGURATION.md
wft_logs10+Workflow execution audit logsWORKFLOW_CONFIGURATION.md
wft_node_states8+Node-level execution state trackingWORKFLOW_CONFIGURATION.md
wft_nodes9+Node configuration storageWORKFLOW_CONFIGURATION.md
wft_steps10+Step execution queueWORKFLOW_CONFIGURATION.md

Data Display Configuration​

TableColumnsPurposeModule
app_dataview_configs12Data grid columns, filters, sortingDATAVIEW_CONFIGURATION.md

Navigation & Menu​

TableColumnsPurposeModule
app_navigation_configs10Menu items and hierarchyNAVIGATION_CONFIGURATION.md

Tenant, Users & Security​

TableColumnsPurposeModule
app_tenants12Tenant organizations and isolationTENANT_USERS_ROLES.md
app_users18+User accounts and authenticationTENANT_USERS_ROLES.md
app_roles10Role definitions and permissionsTENANT_USERS_ROLES.md
app_user_roles8User-to-role assignmentsTENANT_USERS_ROLES.md
app_subscriptions12Tenant feature licensingTENANT_USERS_ROLES.md

System Configuration​

TableColumnsPurposeModule
app_value_sets10Enumeration values for dropdownsVALUE_SETS_FEATURES.md
app_dashboard_configs11Dashboard widget definitionsVALUE_SETS_FEATURES.md
APP_CONNECTIONS13External database connectionsVALUE_SETS_FEATURES.md
sys_audit_logs14System-wide audit trailVALUE_SETS_FEATURES.md
app_settings9Application configuration parametersVALUE_SETS_FEATURES.md

Getting Started with Database Development​

For Different Use Cases:​

I need to understand how entities/objects work: → Read OBJECT_MANAGEMENT.md

I'm building a form or updating form schema: → Read PAGE_CONFIGURATION.md

I'm creating or debugging a workflow: → Read WORKFLOW_CONFIGURATION.md

I'm building a data grid view: → Read DATAVIEW_CONFIGURATION.md

I need to add menu items or navigation: → Read NAVIGATION_CONFIGURATION.md

I'm implementing authentication or tenant management: → Read TENANT_USERS_ROLES.md

I need to use enumerations or dashboards: → Read VALUE_SETS_FEATURES.md

Critical Development Rules​

Network Layer​

✅ DO: Always encrypt tenant ID in headers
❌ DON'T: Transmit tenant ID in plaintext

Database Access​

✅ DO: Filter ALL queries by tenant_id
❌ DON'T: Query data without tenant isolation check

Configuration Updates​

✅ DO: Update JSON configs and reload from cache
❌ DON'T: Deploy code changes for business logic tweaks

Performance​

✅ DO: Cache configuration with proper TTL and invalidation
❌ DON'T: Query RADICS_CFG on every request without caching

Security​

✅ DO: Encrypt sensitive values (passwords, API keys, connection strings)
❌ DON'T: Store plaintext sensitive data

Audit & Compliance​

✅ DO: Log all data modifications with user context
❌ DON'T: Skip audit logging for performance

  • FRONTEND.md - Frontend architecture and Redux state management
  • MIDDLEWARE.md - Middleware service layer and API patterns
  • Home - Main documentation index CREATE INDEX idxWorkflowsStatus ON WFT_WORKFLOWS(WorkflowStatus);

**Query Optimization Checklist (With Actual Schema):**
```sql
-- Bad: Full table scan without tenant filter
SELECT * FROM app_workflow_configs WHERE wfc_code LIKE '%test%';

-- Good: TenantId filter first, specific columns
SELECT wfc_id, wfc_code, wfc_description, wfc_status
FROM app_workflow_configs
WHERE wfc_tenant_id = @tenantId
  AND wfc_status = 1
  AND wfc_is_deleted = 0
ORDER BY wfc_code;

-- Good: Filtered search with proper indexing
SELECT obj_id, obj_code, obj_description, obj_payload_table
FROM app_object_registries
WHERE obj_tenant_id = @tenantId
  AND obj_code = @objectCode
  AND obj_is_deleted = 0;

-- Good: Workflow instance query with proper filtering
SELECT wfi_id, wfi_instance_code, wfi_status, wfi_progress_pct, wfi_started_at
FROM wft_instances
WHERE wfi_tenant_id = @tenantId
  AND wfi_status != 'Cancelled'
  AND MONTH(wfi_started_at) = MONTH(GETDATE())
ORDER BY wfi_started_at DESC;

-- Performance: Index on tenant + status combination for faster filters
CREATE INDEX idx_wfi_tenant_status ON wft_instances(wfi_tenant_id, wfi_status);
CREATE INDEX idx_wfc_tenant_status ON app_workflow_configs(wfc_tenant_id, wfc_status);
CREATE INDEX idx_obj_tenant_code ON app_object_registries(obj_tenant_id, obj_code);

Backup & Recovery Strategy​

Configuration Database (RADICS_CFG):

  • Daily full backup (contains all tenant configurations)
  • Weekly backup retention
  • Point-in-time recovery enabled
  • Encrypted backup storage

Tenant Databases:

  • Nightly backups per tenant
  • 30-day retention minimum
  • Isolated backup storage per tenant
  • Recovery tested monthly

Verification Scripts:

# Backup validation
sqlcmd -S [server] -d RADICS_CFG -Q "DBCC CHECKDB"

# Restore test (monthly)
# Restore backup to test server
# Run data validation queries
# Verify row counts match

Key Files & Imports​

PurposeFileNotes
Config DB accessconfig/db/db_connections.jsConnection pooling
Migrationsmigrations/Schema changes, versioned
Repository layerrepositories/Data access patterns
Validatorsutils/validators.util.jsInput validation
Error handlingutils/errors.util.jsCustom error classes
Migration scriptexecute-migration.jsRun pending migrations

Common Issues & Solutions​

IssueCauseSolution
Cross-tenant data leakMissing wfc_tenant_id / obj_tenant_id filterAudit all queries; add tenant column to WHERE clause; use indexed columns
Workflow not appearingwfc_is_deleted = 1 or wfc_status = 0Check both flags: WHERE wfc_tenant_id = @tenantId AND wfc_is_deleted = 0 AND wfc_status = 1
Object registry lookup failsMissing obj_is_deleted = 0 checkAlways filter: WHERE obj_tenant_id = @tenantId AND obj_code = @code AND obj_is_deleted = 0
Slow workflow instance queriesMissing index on wft_instancesCreate: CREATE INDEX idx_wfi_tenant_status ON wft_instances(wfi_tenant_id, wfi_status)
Connection pool exhaustionConnections not returned to poolEnsure try-finally blocks: connection returned even on error
JSON schema validation failsInvalid JSON in wfc_config, dvc_config, pgc_configValidate JSON syntax before INSERT/UPDATE; use JSON_VALID() in SQL
Backup corruptionIncomplete backup processEnable CHECKSUM; test restore monthly
Migration rollback failureNo rollback scriptAlways create rollback script for each migration
Duplicate records after migrationNo unique constraint on key columnsAdd unique index before migration on (tenant_id, code)
Performance degradationStale statisticsUpdate table statistics: UPDATE STATISTICS app_workflow_configs
Workflow instance stuck in Runningwfi_status never updated to Completed/FailedCheck wft_logs for errors; verify worker process is running
DataView not loadingdvc_is_deleted = 1 or dvc_status = 0Verify: WHERE dvc_tenant_id = @tenantId AND dvc_is_deleted = 0 AND dvc_status = 1

Deployment Checklist​

  • Configuration database (RADICS_CFG) created
  • All core tables created (WFT_WORKFLOWS, APP_CONNECTIONS, etc.)
  • Indexes created on TenantId, dates, status fields
  • Tenant databases created for each customer
  • Connection strings encrypted and stored in APP_CONNECTIONS
  • Full backup of configuration database
  • Test restore of backup validates
  • Migrations verified on staging
  • Encryption keys secured and backed up
  • QueryTimeout configured appropriately
  • Connection pool size tuned for load
  • Monitoring alerts set up for query performance