Value Sets & Additional Features Documentation

Module: Enumeration Values, Dashboard Configuration, and Other System Features
Version: 1.0
Last Updated: March 12, 2026

---

📌 Overview

This module covers supporting configuration tables that enhance the core functionality:

• Value Sets: Predefined enumeration values for dropdowns and selections
• Dashboards: Dashboard widget definitions and layouts
• Connections: External database connections for multi-database support
• Audit Logs: System-wide audit trail
• Settings: Application-wide configuration

---

app_value_sets - Enumeration Values

Purpose

Stores reusable enumeration values (picklists) for dropdown fields across the application.

Schema

SELECT
  vs_id (INT),                           -- Primary Key
  vs_tenant_id (INT),                    -- Tenant isolation
  vs_code (VARCHAR(256)),                -- Identifier (e.g., 'STATUS_TYPES')
  vs_name (VARCHAR),                     -- Display name
  vs_description (VARCHAR),              -- Purpose
  vs_values (JSON),                      -- Value array
  vs_sort_order (INT),                   -- Display order
  vs_is_active (BIT),                    -- 1=Active, 0=Inactive
  vs_is_deleted (BIT),                   -- Soft delete flag
  vs_audit_data (JSON),                  -- Audit trail
  vs_add_dtls (JSON)                     -- Additional metadata
FROM app_value_sets
WHERE vs_tenant_id = @tenant_id AND vs_is_deleted = 0
ORDER BY vs_sort_order;

vs_values Structure (JSON)

{
  "valueSetCode": "CUSTOMER_STATUS",
  "valueSetName": "Customer Status",
  "values": [
    {
      "value": "ACTIVE",
      "label": "Active",
      "description": "Active customer",
      "color": "success",
      "icon": "check_circle",
      "sortOrder": 1
    },
    {
      "value": "INACTIVE",
      "label": "Inactive",
      "description": "Inactive customer",
      "color": "secondary",
      "icon": "cancel",
      "sortOrder": 2
    },
    {
      "value": "SUSPENDED",
      "label": "Suspended",
      "description": "Account suspended",
      "color": "warning",
      "icon": "pause_circle",
      "sortOrder": 3
    },
    {
      "value": "ARCHIVED",
      "label": "Archived",
      "description": "Historical record",
      "color": "disabled",
      "icon": "archive",
      "sortOrder": 4
    }
  ]
}

Query Value Sets

-- Get value set by code
SELECT
  vs_code,
  vs_name,
  vs_values
FROM app_value_sets
WHERE vs_code = 'CUSTOMER_STATUS'
  AND vs_tenant_id = 1
  AND vs_is_active = 1;

-- Get all active value sets
SELECT
  vs_code,
  vs_name,
  JSON_EXTRACT(vs_values, '$[0].value') as first_value
FROM app_value_sets
WHERE vs_tenant_id = 1
  AND vs_is_active = 1
ORDER BY vs_sort_order;

Frontend Usage

// From: axiom-genesis-frontend/src/services/valueset.service.ts
export class ValueSetService {
  static async getValueSet(vsCode: string): Promise<ValueSet> {
    const response = await api.get(`/api/v1/valuesets/${vsCode}`);
    return response.data.data;
  }

  static async getValueSetValues(vsCode: string): Promise<ValueOption[]> {
    const vs = await this.getValueSet(vsCode);
    return vs.values.map(v => ({
      value: v.value,
      label: v.label,
      color: v.color,
      icon: v.icon
    }));
  }
}

// Usage in form field
<SelectField
  label="Customer Status"
  fieldCode="status"
  valueSetCode="CUSTOMER_STATUS"  // Auto-loads values
  onChange={onChange}
/>

---

app_dashboard_configs - Dashboard Definitions

Purpose

Stores dashboard widget layouts and configurations for analytics and monitoring.

Schema

SELECT
  dsh_id (INT),                          -- Primary Key
  dsh_tenant_id (INT),                   -- Tenant isolation
  dsh_code (VARCHAR(256)),               -- Identifier (e.g., 'SALES_DASHBOARD')
  dsh_name (VARCHAR),                    -- Display name
  dsh_description (VARCHAR),             -- Purpose
  dsh_config (JSON),                     -- Widget layout and definitions
  dsh_owner_id (INT),                    -- Creator user ID
  dsh_is_default (BIT),                  -- 1=Default dashboard per role
  dsh_is_public (BIT),                   -- 1=Shareable with others
  dsh_status (INT),                      -- 1=Active, 0=Inactive
  dsh_is_deleted (BIT),                  -- Soft delete flag
  dsh_audit_data (JSON),                 -- Audit trail
  dsh_add_dtls (JSON)                    -- Additional metadata
FROM app_dashboard_configs
WHERE dsh_tenant_id = @tenant_id AND dsh_is_deleted = 0;

dsh_config Structure (JSON)

{
  "dashboardId": "SALES_DASHBOARD",
  "dashboardName": "Sales Overview",
  "dashboardDescription": "Key sales metrics and KPIs",

  "layout": {
    "gridColumns": 12,
    "gridGap": 16,
    "responsive": true
  },

  "widgets": [
    {
      "widgetId": "widget_total_revenue",
      "widgetName": "Total Revenue",
      "widgetType": "kpi",
      "position": { "col": 0, "row": 0, "width": 3, "height": 2 },
      "config": {
        "metric": "total_revenue",
        "dataviewCode": "ORDERS_SUMMARY",
        "aggregation": "sum",
        "filter": "status = 'COMPLETED'",
        "format": "currency",
        "currencyCode": "USD",
        "comparison": "previous_month"
      }
    },
    {
      "widgetId": "widget_orders_count",
      "widgetName": "Total Orders",
      "widgetType": "kpi",
      "position": { "col": 3, "row": 0, "width": 3, "height": 2 },
      "config": {
        "metric": "order_count",
        "dataviewCode": "ORDERS_SUMMARY",
        "aggregation": "count",
        "filter": "created_at >= DATEADD(month, -1, GETDATE())"
      }
    },
    {
      "widgetId": "widget_sales_trend",
      "widgetName": "Sales Trend",
      "widgetType": "chart",
      "position": { "col": 0, "row": 2, "width": 6, "height": 4 },
      "config": {
        "chartType": "line",
        "dataviewCode": "SALES_BY_MONTH",
        "xAxis": "month",
        "yAxis": "revenue",
        "series": [
          {
            "name": "Revenue",
            "field": "revenue",
            "color": "#2196F3"
          },
          {
            "name": "Target",
            "field": "target_revenue",
            "color": "#FF9800",
            "dasharray": "5,5"
          }
        ]
      }
    },
    {
      "widgetId": "widget_orders_table",
      "widgetName": "Recent Orders",
      "widgetType": "dataview",
      "position": { "col": 6, "row": 2, "width": 6, "height": 4 },
      "config": {
        "dataviewCode": "ORDERS_LIST",
        "pageSize": 10,
        "columns": [
          "order_id",
          "customer_name",
          "amount",
          "status",
          "created_at"
        ],
        "sort": [{ "field": "created_at", "direction": "desc" }]
      }
    }
  ]
}

Widget Types

Type	Purpose	Config
kpi	Key performance indicator	metric, aggregation, format, comparison
chart	Graph visualization	chartType, dataviewCode, xAxis, yAxis, series
dataview	Embedded data grid	dataviewCode, pageSize, columns, filters
gauge	Progress gauge	min, max, value, thresholds
text	Static text/HTML	content, markdown
iframe	External content	url, height
custom	Custom React component	componentName, props

---

APP_CONNECTIONS - Database Connections

Purpose

Stores external database connection strings for multi-database architecture.

Schema

SELECT
  conn_id (VARCHAR(36)),                 -- UUID Primary Key
  conn_tenant_id (INT),                  -- Tenant isolation
  conn_code (VARCHAR(256)),              -- Identifier (e.g., 'RADICS_CFG')
  conn_name (VARCHAR),                   -- Display name
  conn_type (VARCHAR),                   -- 'MSSQL', 'POSTGRES', 'MONGODB', 'ORACLE'
  conn_server (VARCHAR),                 -- Server address
  conn_port (INT),                       -- Port number
  conn_database (VARCHAR),               -- Database/schema name
  conn_username (VARCHAR),               -- Connection username
  conn_string (VARCHAR),                 -- Full encrypted connection string
  conn_is_active (BIT),                  -- 1=Active, 0=Inactive
  conn_is_default (BIT),                 -- 1=Primary database
  conn_cache_ttl_minutes (INT),          -- Cache timeout
  conn_created_at (DATETIME2),           -- Creation timestamp
  conn_is_deleted (BIT),                 -- Soft delete flag
  conn_audit_data (JSON)                 -- Audit trail
FROM APP_CONNECTIONS
WHERE conn_is_deleted = 0
ORDER BY conn_is_default DESC;

Connection Management Service

// From: axiom-genesis-middleware/services/connection.service.js
const ConnectionService = {
  // Get or create pooled connection
  getConnection: async (connId) => {
    const cacheKey = `connection:${connId}`;
    let pool = await redis.get(cacheKey);

    if (pool) {
      return pool;
    }

    // Load connection config
    const configDb = getConfigConnection();
    const result = await configDb
      .request()
      .input("conn_id", connId)
      .query(
        `SELECT conn_string, conn_type FROM APP_CONNECTIONS WHERE conn_id = @conn_id`,
      );

    const connString = decrypt(result.recordset[0].conn_string);
    const connType = result.recordset[0].conn_type;

    // Create pool based on type
    let pool;
    switch (connType) {
      case "MSSQL":
        pool = new sql.ConnectionPool(connString);
        break;
      case "POSTGRES":
        pool = new pg.Pool({ connectionString: connString });
        break;
      // Handle other types
    }

    // Cache pool for reuse
    await redis.setex(cacheKey, 3600, pool); // 1 hour

    return pool;
  },

  // Test connection
  testConnection: async (connConfig) => {
    try {
      const pool = new sql.ConnectionPool(connConfig.conn_string);
      await pool.connect();
      await pool.close();
      return { success: true };
    } catch (error) {
      return { success: false, error: error.message };
    }
  },
};

---

sys_audit_logs - Audit Trail

Purpose

System-wide audit log for compliance and security tracking.

Schema

SELECT
  audit_id (BIGINT),                     -- Primary Key
  audit_tenant_id (INT),                 -- Tenant isolation
  audit_user_id (INT),                   -- Who performed action
  audit_timestamp (DATETIME2),           -- When action occurred
  audit_entity (VARCHAR),                -- Entity being modified
  audit_entity_id (VARCHAR),             -- Record ID
  audit_action (VARCHAR),                -- CREATE, UPDATE, DELETE, READ (sensitive)
  audit_old_values (JSON),               -- Before values
  audit_new_values (JSON),               -- After values
  audit_ip_address (VARCHAR),            -- Source IP
  audit_user_agent (VARCHAR),            -- Browser/client info
  audit_status (VARCHAR),                -- SUCCESS, FAILURE
  audit_error_message (VARCHAR),         -- If failure
  audit_is_deleted (BIT)                 -- Soft delete flag
FROM sys_audit_logs
WHERE audit_tenant_id = @tenant_id
ORDER BY audit_timestamp DESC;

Audit Logging Service

// From: axiom-genesis-middleware/services/audit.service.js
AuditService.log = async (auditData) => {
  const {
    tenantId,
    userId,
    entity,
    entityId,
    action,
    oldValues,
    newValues,
    ipAddress,
    userAgent,
    status = "SUCCESS",
    errorMessage,
  } = auditData;

  const db = getConnection();

  await db
    .request()
    .input("audit_tenant_id", tenantId)
    .input("audit_user_id", userId)
    .input("audit_entity", entity)
    .input("audit_entity_id", entityId)
    .input("audit_action", action)
    .input("audit_old_values", JSON.stringify(oldValues))
    .input("audit_new_values", JSON.stringify(newValues))
    .input("audit_ip_address", ipAddress)
    .input("audit_user_agent", userAgent)
    .input("audit_status", status)
    .input("audit_error_message", errorMessage)
    .input("audit_timestamp", new Date()).query(`
      INSERT INTO sys_audit_logs (
        audit_tenant_id, audit_user_id, audit_entity, audit_entity_id,
        audit_action, audit_old_values, audit_new_values,
        audit_ip_address, audit_user_agent, audit_status, audit_error_message,
        audit_timestamp
      )
      VALUES (
        @audit_tenant_id, @audit_user_id, @audit_entity, @audit_entity_id,
        @audit_action, @audit_old_values, @audit_new_values,
        @audit_ip_address, @audit_user_agent, @audit_status, @audit_error_message,
        @audit_timestamp
      )
    `);
};

// Middleware to auto-log all entity updates
const auditMiddleware = async (req, res, next) => {
  const originalSend = res.send;

  res.send = function (data) {
    if (req.method !== "GET" && res.statusCode < 400) {
      AuditService.log({
        tenantId: req.tenantId,
        userId: req.user?.id,
        entity: req.params.entity,
        entityId: req.body?.id || req.params.id,
        action: req.method === "POST" ? "CREATE" : "UPDATE",
        newValues: req.body,
        ipAddress: req.ip,
        userAgent: req.headers["user-agent"],
      }).catch((err) => logger.error("Audit log failed:", err));
    }

    originalSend.call(this, data);
  };

  next();
};

---

app_settings - Application Configuration

Purpose

System-wide settings and configuration parameters.

Schema

SELECT
  set_id (INT),                          -- Primary Key
  set_tenant_id (INT),                   -- Tenant isolation (NULL = global)
  set_key (VARCHAR(256)),                -- Setting key (e.g., 'SMTP_SERVER')
  set_value (VARCHAR),                   -- Setting value (encrypted if sensitive)
  set_is_encrypted (BIT),                -- 1=Encrypted, 0=Plain
  set_type (VARCHAR),                    -- 'string', 'number', 'boolean', 'json'
  set_description (VARCHAR),             -- Setting purpose
  set_created_at (DATETIME2),            -- Creation timestamp
  set_updated_at (DATETIME2),            -- Last update
  CONSTRAINT pk_settings PRIMARY KEY (set_id),
  CONSTRAINT uq_settings UNIQUE (set_tenant_id, set_key)
FROM app_settings
ORDER BY set_tenant_id, set_key;

Common Settings

Key	Type	Purpose
SMTP_SERVER	string	Email server hostname
SMTP_PORT	number	Email server port
SMTP_USERNAME	string	Email authentication
SMTP_PASSWORD	string	Email password (encrypted)
SESSION_TIMEOUT_MINUTES	number	Session expiration
PASSWORD_EXPIRY_DAYS	number	Password policy
MAX_FAILED_LOGINS	number	Account lockout threshold
TIMEZONE	string	Default timezone
LOGO_URL	string	Company logo
THEME_PRIMARY_COLOR	string	Brand color

Settings Service

SettingsService.getSetting = async (key, tenantId) => {
  const cacheKey = `setting:${tenantId}:${key}`;
  const cached = await redis.get(cacheKey);

  if (cached) return JSON.parse(cached);

  const db = getConnection();
  const result = await db
    .request()
    .input("set_key", key)
    .input("set_tenant_id", tenantId).query(`
      SELECT set_value, set_is_encrypted, set_type
      FROM app_settings
      WHERE set_key = @set_key
        AND (set_tenant_id = @set_tenant_id OR set_tenant_id IS NULL)
      ORDER BY set_tenant_id DESC
    `);

  if (!result.recordset[0]) {
    return null;
  }

  const setting = result.recordset[0];
  let value = setting.set_value;

  if (setting.set_is_encrypted) {
    value = decrypt(value);
  }

  if (setting.set_type === "json") {
    value = JSON.parse(value);
  } else if (setting.set_type === "number") {
    value = Number(value);
  } else if (setting.set_type === "boolean") {
    value = value.toLowerCase() === "true";
  }

  // Cache for 1 hour
  await redis.setex(cacheKey, 3600, JSON.stringify(value));

  return value;
};

---

Queries for Support Tables

Value Sets Query

SELECT
  vs_code,
  JSON_EXTRACT(vs_values, '$[*].value') as values,
  JSON_EXTRACT(vs_values, '$[*].label') as labels
FROM app_value_sets
WHERE vs_tenant_id = 1 AND vs_is_active = 1;

Connection Health Check

SELECT
  conn_code,
  conn_type,
  CASE
    WHEN DATEDIFF(DAY, conn_created_at, GETDATE()) < 7 THEN 'New'
    WHEN conn_is_active = 0 THEN 'Inactive'
    ELSE 'Active'
  END as status
FROM APP_CONNECTIONS
WHERE conn_is_deleted = 0
ORDER BY conn_is_default DESC;

Audit Log Query

SELECT TOP 100
  audit_timestamp,
  audit_action,
  audit_entity,
  ISNULL(audit_status, 'UNKNOWN') as status
FROM sys_audit_logs
WHERE audit_tenant_id = 1
ORDER BY audit_timestamp DESC;

---

API Examples

Get Value Set

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

Response:
{
  "success": true,
  "data": {
    "vs_code": "CUSTOMER_STATUS",
    "vs_name": "Customer Status",
    "values": [
      { "value": "ACTIVE", "label": "Active", "color": "success" },
      { "value": "INACTIVE", "label": "Inactive", "color": "secondary" }
    ]
  }
}

Get Dashboard Config

GET /api/v1/dashboards/SALES_DASHBOARD/config
Headers: {
  'x-tenant-id': 'encrypted_value',
  'Authorization': 'Bearer token'
}

Response:
{
  "success": true,
  "data": {
    "dsh_code": "SALES_DASHBOARD",
    "dsh_name": "Sales Overview",
    "widgets": [...]
  }
}

Get Audit Logs

GET /api/v1/audit-logs?entity=CUSTOMERS&limit=50
Headers: {
  'x-tenant-id': 'encrypted_value',
  'Authorization': 'Bearer token'
}

Response:
{
  "success": true,
  "data": [
    {
      "audit_timestamp": "2026-03-12T10:30:00Z",
      "audit_action": "UPDATE",
      "audit_entity": "CUSTOMERS",
      "audit_status": "SUCCESS"
    }
  ]
}

---

Do's and Don'ts

✅ DO

• Encrypt sensitive settings - Passwords, API keys, tokens
• Log all entity modifications - Required for compliance
• Cache value sets - Rarely change, frequently accessed
• Test database connections - Before adding to tenants
• Archive old audit logs - Don't let them grow indefinitely
• Use value sets for dropdowns - Maintain data consistency
• Monitor connection health - Track pool utilization

❌ DON'T

• Store passwords in plain text - Always hash/encrypt
• Log sensitive data - Redact passwords in audit logs
• Hardcode configuration - Use app_settings table
• Trust unencrypted connection strings - Always encrypt
• Disable audit logging - Required for security
• Mix global and tenant settings - Keep clear separation
• Cache settings indefinitely - Refresh frequently

---

Related Documentation

• DATABASE.md - Core schema reference
• OBJECT_MANAGEMENT.md - Entity definitions
• TENANT_USERS_ROLES.md - User management