Database Architecture

Overview

The Axiom Genesis Database uses MSSQL 2019+ and implements logical multi-tenancy with a centralized configuration database (RADICS_CFG) that manages all system metadata: entity definitions, access profiles, page configurations, workflows, dataviews, and more.

Current Production Setup:

• Primary Database: RADICS_CFG at 64.227.173.43:1433 (MSSQL 2019+)
• Registered Objects: 31 total (29 TABLEs, 1 VIEW, 1 FUNCTION)
• Access Profiles: 37 system profiles across all objects
• Tenant Isolation: Logical via tenant_id columns with NULL = system-wide
• Status: ✅ Production-Ready | Oracle/PostgreSQL drivers in development

Key Characteristics:

• 📊 Configuration-driven entity mapping (no code changes for new objects)
• 🔒 Field-level access control via object profiles with masking policies
• 📝 Complete audit trails in JSON columns (created_by, updated_by, timestamps)
• 🗑️ Soft deletes (is_deleted flag) for data recovery
• ⚡ Redis caching of configurations with automatic invalidation

info

For detailed configuration documentation covering entities, workflows, pages, dataviews, and more, see the Configuration Documentation.

---

Current Technology Stack ✅

Component	Technology	Version	Purpose
Primary RDBMS	MSSQL Server	2019+	Production configuration database
Config Database	RADICS_CFG	Latest	Centralized metadata store
Tenant Databases	MSSQL	2019+	Logical separation per tenant
Connection Pooling	MSSQL Native Pooling	Built-in	Efficient connection reuse
Caching Layer	Redis	6.0+	Configuration and query result caching
Backup Strategy	MSSQL Native Backup	Built-in	Daily full + hourly incremental
Migrations	SQL Scripts	Versioned	Schema versioning and rollback
Monitoring	MSSQL DMVs	Built-in	Performance and health monitoring
Alternative	Oracle 12c+	Planned	Enterprise compatibility (future)

---

Database Architecture

Multi-Tenant Approach

Logical Isolation - Single centralized configuration database (RADICS_CFG) with all tables including tenant_id column to logically isolate customer data and configurations. System-wide configurations use NULL tenant_id.

MSSQL Server: 64.227.173.43:1433
├── Database: RADICS_CFG (Configuration Database)
│   ├── Core Configuration Tables (11 tables)
│   │   ├─ app_tenants (tenant definitions)
│   │   ├─ app_users (user accounts)
│   │   ├─ app_roles (role definitions)
│   │   ├─ app_object_registries (entity metadata - 31 objects)
│   │   ├─ app_object_profiles (field access control - 37 profiles)
│   │   ├─ app_page_configs (form/dashboard/report definitions)
│   │   ├─ app_workflow_configs (workflow automation definitions)
│   │   ├─ app_dataview_configs (data grid display configurations)
│   │   ├─ app_navigation_configs (menu and navigation structure)
│   │   ├─ app_connections (database connection strings)
│   │   └─ app_value_sets (enumeration values)
│   │
│   └── Features Across All Tables
│       ├─ Tenant Isolation: {table}_tenant_id (NULL = system-wide)
│       ├─ Soft Deletes: {table}_is_deleted (BIT)
│       ├─ Status: {table}_status (INT: 1=Active, 0=Inactive)
│       ├─ Audit Trail: {table}_audit_data (JSON)
│       └─ Metadata: {table}_add_dtls (JSON)
│
└── Redis Cache (External)
    ├─ registry:{tenantId}:{code} (24h TTL)
    ├─ profile:{tenantId}:{code} (24h TTL)
    ├─ page:{tenantId}:{code} (5h TTL)
    ├─ workflow:{tenantId}:{code} (24h TTL)
    └─ [+5 more entity caches]
```text

**Per-Tenant Databases** (Optional):
- Separate MSSQL database per tenant for transaction data
- Maintains logical isolation of business data
- Configuration still stored in centralized RADICS_CFG

### **Table Organization**

#### **Core Configuration Tables** ✅

**Production Database: RADICS_CFG (MSSQL 2019+)**

```sql
-- Tenant Management
app_tenants
├─ tnt_id (INT, PK)
├─ tnt_tenant_code (VARCHAR, Unique)
├─ tnt_tenant_name (VARCHAR)
├─ tnt_description (VARCHAR)
├─ tnt_status (INT: 1=Active, 0=Inactive)
├─ tnt_is_deleted (BIT)
├─ tnt_audit_data (JSON - created_by, created_at, updated_by, etc.)
└─ tnt_add_dtls (JSON - additional metadata)

-- User Management
app_users (referenced as 'users' in object registry)
├─ usr_id (INT, PK)
├─ usr_tenant_id (INT, FK to app_tenants)
├─ usr_email_id (VARCHAR, Unique)
├─ usr_first_name (VARCHAR)
├─ usr_last_name (VARCHAR)
├─ usr_password_hash (VARCHAR, encrypted)
├─ usr_status (INT: 1=Active, 0=Inactive)
├─ usr_is_deleted (BIT)
├─ usr_audit_data (JSON)
└─ usr_add_dtls (JSON)

-- Role-Based Access Control
app_roles (referenced as 'roles' in object registry)
├─ rol_id (INT, PK)
├─ rol_tenant_id (INT, FK to app_tenants)
├─ rol_code (VARCHAR, Unique)
├─ rol_description (VARCHAR)
├─ rol_status (INT: 1=Active, 0=Inactive)
├─ rol_is_deleted (BIT)
├─ rol_audit_data (JSON)
└─ rol_add_dtls (JSON)

-- Object Registry (Entity Configuration - 31 registered objects)
app_object_registries
├─ obj_id (INT, PK)
├─ obj_tenant_id (INT, nullable - NULL = system-wide)
├─ obj_code (VARCHAR, Entity identifier: users, roles, app_tenants, etc.)
├─ obj_description (VARCHAR, Display name)
├─ obj_type_code (VARCHAR: TABLE|VIEW|FUNCTION)
├─ obj_connection_code (VARCHAR, Links to app_connections - e.g., 'RADICS_CFG_DB')
├─ obj_payload (JSON, optional field metadata & definitions)
├─ obj_payload_object_name (NVARCHAR, Actual table/view/function name)
├─ obj_payload_identifier (NVARCHAR, Primary key field - e.g., usr_id)
├─ obj_payload_column_prefix (NVARCHAR, Field prefix - e.g., usr_, rol_, tnt_)
├─ obj_payload_table (NVARCHAR, Source table name)
├─ obj_status (INT: 1=Active, 0=Inactive)
├─ obj_is_deleted (BIT)
├─ obj_audit_data (JSON)
└─ obj_add_dtls (JSON)

-- Registered Objects (by type - 31 total):
-- TABLEs (29): app_tenants, app_users, app_roles, app_object_registries, 
--              app_object_profiles, page_configs, workflow_configs, 
--              dataview_configs, navigation_configs, value_sets, connections, +19 more
-- VIEWs (1): dataview_complete_config_read
-- FUNCTIONs (1): System-level function

-- Object Access Profiles (Field-Level Security)
app_object_profiles
├─ prf_id (INT, PK)
├─ prf_tenant_id (INT, nullable)
├─ prf_code (VARCHAR, Profile identifier: user_profile, profiles_full_access, etc.)
├─ prf_description (VARCHAR)
├─ prf_object_code (VARCHAR, FK to app_object_registries.obj_code)
├─ prf_mode_code (VARCHAR: FULL|READ|WRITE|EXECUTE)
├─ prf_status (INT: 1=Active, 0=Inactive)
├─ prf_is_deleted (BIT)
├─ prf_policy (JSON, nullable - field masking, projection rules)
├─ prf_payload (JSON, nullable - access configuration)
├─ prf_policy_tenantpolicy (JSON - multi-tenant constraints)
├─ prf_policy_projection (JSON - field inclusion/exclusion)
├─ prf_policy_encryption_required (NVARCHAR - encryption level)
├─ prf_policy_audit_level (NVARCHAR - audit verbosity)
├─ prf_audit_data (JSON)
└─ prf_add_dtls (JSON)

-- Real Profile Examples (37 total profiles in system):
-- user_profile (mode: FULL, object: users)
-- profiles_full_access (mode: FULL, object: object_profiles)
-- registries_read_access (mode: READ, object: object_registries)
-- registries_full_access (mode: FULL, object: object_registries)
-- roles_full_access (mode: FULL, object: roles)

-- Page Configurations (Forms, Dashboards, Reports)
app_page_configs
├─ pgc_id (INT, PK)
├─ pgc_tenant_id (INT, FK to app_tenants)
├─ pgc_code (VARCHAR, Unique identifier)
├─ pgc_name (VARCHAR)
├─ pgc_type (VARCHAR: form|dashboard|report|content)
├─ pgc_object_code (VARCHAR, FK to app_object_registries)
├─ pgc_schema (JSON - complete configuration)
├─ pgc_status (INT: 1=Active, 0=Inactive)
├─ pgc_is_deleted (BIT)
├─ pgc_audit_data (JSON)
└─ pgc_add_dtls (JSON)

-- Workflow Configurations
app_workflow_configs
├─ wft_id (INT, PK)
├─ wft_tenant_id (INT, FK to app_tenants)
├─ wft_code (VARCHAR, Unique identifier)
├─ wft_name (VARCHAR)
├─ wft_description (VARCHAR)
├─ wft_schema (JSON - trigger, steps, actions, error handling)
├─ wft_status (INT: 1=Active, 0=Inactive)
├─ wft_is_deleted (BIT)
├─ wft_version (INT)
├─ wft_audit_data (JSON)
└─ wft_add_dtls (JSON)

-- DataView Configurations
app_dataview_configs
├─ dv_id (INT, PK)
├─ dv_tenant_id (INT, FK to app_tenants)
├─ dv_code (VARCHAR, Unique identifier)
├─ dv_name (VARCHAR)
├─ dv_object_code (VARCHAR, FK to app_object_registries)
├─ dv_schema (JSON - columns, filters, sorting, actions)
├─ dv_status (INT: 1=Active, 0=Inactive)
├─ dv_is_deleted (BIT)
├─ dv_audit_data (JSON)
└─ dv_add_dtls (JSON)

-- Navigation & Menu Configuration
app_navigation_configs
├─ nav_id (INT, PK)
├─ nav_tenant_id (INT, FK to app_tenants)
├─ nav_code (VARCHAR, Unique identifier)
├─ nav_structure (JSON - menu hierarchy, pages, workflows, dataviews)
├─ nav_status (INT: 1=Active, 0=Inactive)
├─ nav_is_deleted (BIT)
├─ nav_audit_data (JSON)
└─ nav_add_dtls (JSON)

-- Value Sets (Enumerations)
app_value_sets
├─ vs_id (INT, PK)
├─ vs_tenant_id (INT, nullable)
├─ vs_code (VARCHAR, Unique identifier)
├─ vs_description (VARCHAR)
├─ vs_values (JSON - array of {code, label, description} objects)
├─ vs_status (INT: 1=Active, 0=Inactive)
├─ vs_is_deleted (BIT)
├─ vs_audit_data (JSON)
└─ vs_add_dtls (JSON)

-- Database Connections
app_connections
├─ con_id (INT, PK)
├─ con_tenant_id (INT, nullable - NULL = system-wide)
├─ con_code (VARCHAR, Unique: RADICS_CFG_DB, TENANT_DB_{code}, etc.)
├─ con_connection_string (VARCHAR, encrypted)
├─ con_connection_type (VARCHAR: mssql|mysql|postgresql|oracle|etc.)
├─ con_status (INT: 1=Active, 0=Inactive)
├─ con_is_deleted (BIT)
├─ con_audit_data (JSON)
└─ con_add_dtls (JSON)
```text

#### **Security & Access Control** ✅

```sql
-- Role-Based Access Control (app_roles table - see above)
-- Stores role definitions with role_code and role_description

-- Object-Level Access Policies
-- Defined in app_object_profiles with prf_mode_code (FULL|READ|WRITE|EXECUTE)

-- Field-Level Access through prf_policy (within app_object_profiles)
-- prf_policy JSON contains:
├─ projection: {"type": "include|exclude", "fields": ["field1", "field2"]}
├─ masking: {"field1": "hash|mask|redact", "field2": "partial"}
├─ writableFields: ["field1", "field3"]
└─ rowFilters: {"where": "department = @user.department"}

-- Example Profile Policy:
{
  "prf_mode_code": "READ",
  "projection": {
    "type": "exclude",
    "excludeFields": ["usr_password_hash", "usr_ssn"]
  },
  "masking": {
    "usr_email_id": "partial",
    "usr_phone": "mask"
  },
  "rowFilters": {
    "where": "usr_tenant_id = @tenant_id AND usr_status = 1"
  }
}
```text

#### **Audit & Compliance** ✅

```sql
-- Audit Data (Embedded in JSON columns across all tables)
-- Each configuration table (app_object_registries, app_object_profiles, etc.) includes:
-- Column: {table_prefix}_audit_data (JSON)
-- Structure: {
--   "created_by": "user@example.com",
--   "created_at": "2026-03-12T10:30:00Z",
--   "updated_by": "user@example.com",
--   "updated_at": "2026-03-12T14:45:00Z",
--   "version": 1
-- }

-- Example from app_object_registries:
obj_audit_data JSON:
{
  "created_by": "system",
  "created_at": "2026-01-01T00:00:00Z",
  "updated_by": "admin@example.com",
  "updated_at": "2026-03-10T14:30:00Z"
}

-- Soft Delete Flag
-- All tables include: {table_prefix}_is_deleted (BIT)
-- Soft deletes allow recovery of deleted configurations without data loss
obj_is_deleted BIT (0=Active, 1=Deleted)
prf_is_deleted BIT (0=Active, 1=Deleted)
usr_is_deleted BIT (0=Active, 1=Deleted)

-- Status Codes (Standardized across all config tables)
{table_prefix}_status INT:
├─ 1 = Active (Record is active and usable)
├─ 0 = Inactive (Record exists but not in use)
└─ -1 = Archived (Historical, not deleted)

-- Additional Metadata
{table_prefix}_add_dtls JSON:
-- Stores supplementary information:
{
  "last_review_date": "2026-03-10",
  "reviewer": "audit@example.com",
  "compliance_status": "verified",
  "change_reason": "Updated field masking for GDPR compliance"
}
```text

---

## Real Schema Examples from Production Database

### **Example 1: Users Object (System Object)**

**Object Registry Entry:**

```sql
-- Actual record from app_object_registries
obj_id: 5
obj_code: 'users'
obj_description: 'System users with authentication'
obj_type_code: 'TABLE'
obj_connection_code: 'RADICS_CFG_DB'
obj_payload_object_name: 'app_users'
obj_payload_identifier: 'usr_id'
obj_payload_column_prefix: 'usr_'
obj_payload_table: 'app_users'
obj_status: 1
obj_tenant_id: NULL (system-wide object)

-- Underlying table structure
CREATE TABLE dbo.app_users (
  usr_id INT PRIMARY KEY IDENTITY(1,1),
  usr_tenant_id INT NOT NULL,
  usr_email_id VARCHAR(100) NOT NULL UNIQUE,
  usr_first_name VARCHAR(100),
  usr_last_name VARCHAR(100),
  usr_password_hash VARCHAR(255),
  usr_status INT DEFAULT 1,
  usr_is_deleted BIT DEFAULT 0,
  usr_audit_data JSON,
  usr_add_dtls JSON,
  INDEX idx_tenant_email (usr_tenant_id, usr_email_id)
);
```text

**Access Profile:**

```json
{
  "prf_id": 1,
  "prf_code": "user_profile",
  "prf_description": "User profile editing interface",
  "prf_object_code": "users",
  "prf_mode_code": "FULL",
  "prf_status": 1,
  "prf_policy": null,
  "prf_payload": null,
  "prf_policy_projection": null,
  "prf_policy_encryption_required": null,
  "prf_policy_audit_level": null
}
```text

**API Usage:**

```http
-- List Users (using READ profile)
GET /api/v1/users?status=1
Headers: { 'x-tenant-id': 'encrypted_value' }

-- Response includes all active users without password_hash
{
  "success": true,
  "count": 15,
  "data": [
    {
      "usr_id": 1,
      "usr_email_id": "john.doe@example.com",
      "usr_first_name": "John",
      "usr_last_name": "Doe",
      "usr_status": 1,
      "usr_audit_data": {...}
    }
  ]
}
```text

---

### **Example 2: Object Profiles (Configuration Object)**

**Object Registry Entry:**

```sql
obj_id: 6
obj_code: 'object_profiles'
obj_description: 'Object profile access control configuration'
obj_type_code: 'TABLE'
obj_connection_code: 'RADICS_CFG_DB'
obj_payload_object_name: 'app_object_profiles'
obj_payload_identifier: 'prf_id'
obj_payload_column_prefix: 'prf_'
obj_status: 1
obj_tenant_id: NULL

-- Underlying table (already shown in Core Configuration Tables above)
CREATE TABLE dbo.app_object_profiles (
  prf_id INT PRIMARY KEY IDENTITY(1,1),
  prf_tenant_id INT,
  prf_code VARCHAR(100) UNIQUE,
  prf_description VARCHAR(500),
  prf_object_code VARCHAR(100),
  prf_mode_code VARCHAR(20),
  prf_status INT DEFAULT 1,
  prf_is_deleted BIT DEFAULT 0,
  prf_policy JSON,
  prf_payload JSON,
  prf_policy_encryption_required NVARCHAR(255),
  prf_policy_audit_level NVARCHAR(255),
  prf_audit_data JSON,
  prf_add_dtls JSON
);
```text

**Real Profiles (from database - 37 total):**

```json
[
  {
    "prf_id": 1,
    "prf_code": "user_profile",
    "prf_object_code": "users",
    "prf_mode_code": "FULL",
    "prf_description": "User profile editing interface"
  },
  {
    "prf_id": 2,
    "prf_code": "profiles_full_access",
    "prf_object_code": "object_profiles",
    "prf_mode_code": "FULL",
    "prf_description": "Full access to object profiles"
  },
  {
    "prf_id": 3,
    "prf_code": "registries_read_access",
    "prf_object_code": "object_registries",
    "prf_mode_code": "READ",
    "prf_description": "Read-only access to registries"
  }
]
```text

---

### **Example 3: Page Configuration (Form Definition)**

**Object Registry Entry:**

```sql
obj_code: 'page_configs'
obj_description: 'Page configuration definitions (forms, dashboards, reports)'
obj_type_code: 'TABLE'
obj_payload_identifier: 'pgc_id'
obj_payload_column_prefix: 'pgc_'

-- Table structure
CREATE TABLE dbo.app_page_configs (
  pgc_id INT PRIMARY KEY IDENTITY(1,1),
  pgc_tenant_id INT,
  pgc_code VARCHAR(100) UNIQUE,
  pgc_name VARCHAR(200),
  pgc_type VARCHAR(20),  -- form|dashboard|report|content
  pgc_object_code VARCHAR(100),
  pgc_schema JSON,
  pgc_status INT DEFAULT 1,
  pgc_is_deleted BIT DEFAULT 0,
  pgc_audit_data JSON,
  pgc_add_dtls JSON
);
```text

**Sample Page Configuration (JSON schema):**

```json
{
  "pgc_code": "frm_user_create",
  "pgc_name": "Create User",
  "pgc_type": "form",
  "pgc_object_code": "users",
  "pgc_schema": {
    "sections": [
      {
        "name": "User Information",
        "fields": [
          {
            "fieldCode": "usr_email_id",
            "fieldName": "Email",
            "fieldType": "email",
            "isRequired": true,
            "validation": {"pattern": "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"}
          },
          {
            "fieldCode": "usr_first_name",
            "fieldName": "First Name",
            "fieldType": "text",
            "isRequired": true,
            "validation": {"maxLength": 100}
          },
          {
            "fieldCode": "usr_last_name",
            "fieldName": "Last Name",
            "fieldType": "text",
            "isRequired": false
          },
          {
            "fieldCode": "usr_status",
            "fieldName": "Status",
            "fieldType": "select",
            "options": [
              {"value": 1, "label": "Active"},
              {"value": 0, "label": "Inactive"}
            ]
          }
        ]
      }
    ]
  }
}
```text

---

## Indexing Strategy ✅

```sql
-- High-Priority Indexes (for fast configuration lookups)

-- Object Registry Indexes
CREATE INDEX idx_obj_registry_tenant_code 
  ON app_object_registries(obj_tenant_id, obj_code)
  WHERE obj_is_deleted = 0 AND obj_status = 1;

CREATE INDEX idx_obj_registry_type 
  ON app_object_registries(obj_type_code, obj_status)
  WHERE obj_is_deleted = 0;

-- Object Profiles Indexes
CREATE INDEX idx_obj_profile_object_code 
  ON app_object_profiles(prf_object_code, prf_tenant_id)
  WHERE prf_is_deleted = 0 AND prf_status = 1;

CREATE INDEX idx_obj_profile_mode 
  ON app_object_profiles(prf_mode_code, prf_status)
  WHERE prf_is_deleted = 0;

-- Page Configuration Indexes
CREATE INDEX idx_page_config_tenant_code 
  ON app_page_configs(pgc_tenant_id, pgc_code)
  WHERE pgc_is_deleted = 0 AND pgc_status = 1;

CREATE INDEX idx_page_config_type 
  ON app_page_configs(pgc_type, pgc_object_code)
  WHERE pgc_is_deleted = 0;

-- User & Role Indexes
CREATE INDEX idx_users_tenant_email 
  ON app_users(usr_tenant_id, usr_email_id)
  WHERE usr_is_deleted = 0 AND usr_status = 1;

CREATE INDEX idx_roles_tenant_code 
  ON app_roles(rol_tenant_id, rol_code)
  WHERE rol_is_deleted = 0 AND rol_status = 1;

-- Workflow Configuration Indexes
CREATE INDEX idx_workflow_tenant_code 
  ON app_workflow_configs(wft_tenant_id, wft_code)
  WHERE wft_is_deleted = 0 AND wft_status = 1;

-- DataView Configuration Indexes
CREATE INDEX idx_dataview_object_code 
  ON app_dataview_configs(dv_object_code, dv_tenant_id)
  WHERE dv_is_deleted = 0 AND dv_status = 1;

-- Navigation Configuration Indexes
CREATE INDEX idx_navigation_tenant 
  ON app_navigation_configs(nav_tenant_id, nav_status)
  WHERE nav_is_deleted = 0;

-- Connection Indexes
CREATE INDEX idx_connections_tenant_code 
  ON app_connections(con_tenant_id, con_code)
  WHERE con_is_deleted = 0 AND con_status = 1;

-- Tenant Indexes
CREATE INDEX idx_tenants_code 
  ON app_tenants(tnt_tenant_code)
  WHERE tnt_is_deleted = 0 AND tnt_status = 1;
```text

---

## Performance Optimization ✅

### **Caching Strategy**
```text
Configuration Caching (Redis):
- Object registries (obj_code -> obj_id, obj_payload): 24 hours TTL
- Object profiles (prf_code -> prf_mode_code, prf_policy): 24 hours TTL
- Page configurations (pgc_code -> pgc_schema): 5 hours TTL
- Workflow definitions (wft_code -> wft_schema): 24 hours TTL
- DataView schemas (dv_code -> dv_schema): 5 hours TTL
- Navigation structures (nav_code -> nav_structure): 8 hours TTL
- Value sets (vs_code -> vs_values): 12 hours TTL

Cache Key Pattern: {entityType}:{tenantId}:{code}
  Example: registry:7:users
           profile:7:user_profile
           page:1:frm_user_create

Cache Invalidation Triggers:
- INSERT/UPDATE/DELETE on app_object_registries → Clear registry cache
- INSERT/UPDATE/DELETE on app_object_profiles → Clear profile cache
- INSERT/UPDATE/DELETE on app_page_configs → Clear page config cache
- System admin cache clear endpoint: POST /api/v1/admin/cache/clear
```text

### **Query Optimization**
```sql
-- ❌ SLOW: Missing indexes and tenant filtering
SELECT * FROM app_object_registries WHERE obj_code = @code

-- ✅ FAST: Uses composite index on tenant_id + code
SELECT * FROM app_object_registries 
WHERE obj_tenant_id = @tenant_id 
  AND obj_code = @code
  AND obj_status = 1 
  AND obj_is_deleted = 0

-- ❌ SLOW: Sequential scan on large profiles table
SELECT * FROM app_object_profiles WHERE prf_object_code = 'users'

-- ✅ FAST: Uses filtered index (prf_status and prf_is_deleted in WHERE)
SELECT * FROM app_object_profiles 
WHERE prf_object_code = 'users'
  AND prf_tenant_id = @tenant_id
  AND prf_status = 1 
  AND prf_is_deleted = 0
```text

### **Table Statistics & Maintenance**

```sql
-- Update statistics for optimal query plans
EXEC sp_updatestats  -- Weekly schedule

-- Reorganize indexes with fragmentation > 10%
ALTER INDEX idx_obj_registry_tenant_code ON app_object_registries 
REORGANIZE

-- Rebuild indexes with fragmentation > 30%
ALTER INDEX ALL ON app_object_profiles 
REBUILD WITH (FILLFACTOR = 90)
```text

---

## Backup & Recovery 🚀 **[PLANNED]**

> Implementation in progress. Current procedures are documented for reference.

### **Backup Strategy**

```text
RADICS_CFG Database (Configuration Database):
- Daily Full Backups: 2:00 AM UTC
- Hourly Incremental Backups: Every hour from 3 AM-11 PM UTC
- Transaction Logs: Every 15 minutes
- Backup Location: /backups/mssql/ on dedicated backup storage
- Retention: 30 days for full backups, 7 days for incremental
- Backup Verification: Automated daily RESTORE test to separate database
- Encryption: AES-256 for all backup files
```text

### **Recovery Time Objectives (RTO)**

```text
Tier 1 (Critical Config):
- app_object_registries, app_object_profiles, app_connections
- RTO: < 1 hour | RPO: < 15 minutes

Tier 2 (Configuration):
- app_page_configs, app_workflow_configs, app_dataview_configs
- RTO: < 4 hours | RPO: < 1 hour

Tier 3 (User & Role Management):
- app_users, app_roles, app_tenants
- RTO: < 4 hours | RPO: < 1 hour
```text

### **Recovery Procedures**

```sql
-- Step 1: Identify recovery point in time
SELECT @@SERVERNAME, DB_NAME(), 
  CONVERT(VARCHAR(30), GETDATE(), 121) as recovery_time

-- Step 2: Check available backup history
DECLARE @db_name NVARCHAR(128) = 'RADICS_CFG'
SELECT 
  media_set_id,
  backup_set_id,
  type,
  backup_start_date,
  backup_finish_date
FROM msdb..backupset
WHERE database_name = @db_name
ORDER BY backup_finish_date DESC

-- Step 3: Restore from full backup
RESTORE DATABASE RADICS_CFG 
FROM DISK = '/backups/mssql/RADICS_CFG_full_20260312.bak'
WITH RECOVERY

-- Step 4: Verify data integrity
SELECT COUNT(*) as object_registries FROM app_object_registries
SELECT COUNT(*) as object_profiles FROM app_object_profiles
SELECT COUNT(*) as users FROM app_users

-- Step 5: Validate application connectivity
-- Run health check: GET /api/v1/health
```text

### **Tenant-Specific Recovery**

```sql
-- Export tenant configuration (for individual tenant recovery)
SELECT * INTO backup_objects FROM app_object_registries
WHERE obj_tenant_id = 7 AND obj_is_deleted = 0

SELECT * INTO backup_profiles FROM app_object_profiles
WHERE prf_tenant_id = 7 AND prf_is_deleted = 0

SELECT * INTO backup_pages FROM app_page_configs
WHERE pgc_tenant_id = 7 AND pgc_is_deleted = 0

-- Tenant recovery point metadata stored in additional_details
-- of corresponding configuration table records
```text

---

## Database Maintenance & Monitoring 🚀 **[PLANNED]**

> To be implemented as part of enterprise monitoring phase. Queries provided for reference.

### **Health Checks**

```sql
-- Check object registry completeness
SELECT COUNT(*) as total_objects, 
       SUM(CASE WHEN obj_type_code = 'TABLE' THEN 1 ELSE 0 END) as tables,
       SUM(CASE WHEN obj_type_code = 'VIEW' THEN 1 ELSE 0 END) as views,
       SUM(CASE WHEN obj_type_code = 'FUNCTION' THEN 1 ELSE 0 END) as functions
FROM app_object_registries 
WHERE obj_is_deleted = 0 AND obj_status = 1;

-- Verify profile coverage (each active object should have at least one profile)
SELECT obj_code, COUNT(p.prf_id) as profile_count
FROM app_object_registries r
LEFT JOIN app_object_profiles p ON r.obj_code = p.prf_object_code
WHERE r.obj_is_deleted = 0 AND r.obj_status = 1
GROUP BY obj_code
HAVING COUNT(p.prf_id) = 0;  -- Identifies objects without profiles

-- Check for orphaned configurations
SELECT pgc_code, pgc_object_code
FROM app_page_configs
WHERE pgc_object_code NOT IN (SELECT obj_code FROM app_object_registries)
AND pgc_is_deleted = 0;

-- Monitor tenant isolation compliance
SELECT prf_tenant_id, COUNT(*) as total_profiles
FROM app_object_profiles
WHERE prf_is_deleted = 0 AND prf_status = 1
GROUP BY prf_tenant_id
ORDER BY total_profiles DESC;
```text

### **Connection Pool Monitoring**

```sql
-- Monitor active connections to RADICS_CFG
SELECT 
  COUNT(*) as connection_count,
  SUM(CASE WHEN status = 'sleeping' THEN 1 ELSE 0 END) as idle_connections,
  SUM(CASE WHEN status = 'running' THEN 1 ELSE 0 END) as active_queries
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('RADICS_CFG');

-- Check for long-running queries
SELECT 
  session_id,
  status,
  command,
  DATEDIFF(SECOND, start_time, GETDATE()) as duration_seconds
FROM sys.dm_exec_requests
WHERE database_id = DB_ID('RADICS_CFG')
AND DATEDIFF(SECOND, start_time, GETDATE()) > 60
ORDER BY duration_seconds DESC;
```text

---

## Future Enhancements 🚀

### **Partitioning Strategy** (Q3 2026)
- Partition app_object_registries and app_object_profiles by tenant_id
- Partition configuration tables (page_configs, workflow_configs) by modification date
- Improve query performance for large multi-tenant datasets

### **Advanced Security** (Q3 2026)
- Transparent Data Encryption (TDE) for RADICS_CFG database
- Cell-level encryption for sensitive profiles (prf_policy, prf_payload)
- Dynamic data masking for sensitive columns (usr_email_id, usr_password_hash)

### **High Availability** (Q4 2026)
- Always-On availability groups for RADICS_CFG
- Read replicas for configuration queries
- Cross-region failover for disaster recovery

### **Extended Support** (Future)
- MongoDB driver for document-based workflows
- Elasticsearch driver for full-text search across configurations
- Data warehouse integration for analytics on configuration usage

---

## Migration & Version Control 🚀 **[PLANNED]**

> Schema versioning and migration automation to be implemented in Q2 2026. Manual migration procedures documented below.

### **Schema Versioning**

```text
migrations/
├── 001-initial-schema.sql          -- Core tables: registries, profiles, pages
├── 002-add-workflows.sql            -- Workflow configuration tables
├── 003-add-dataviews.sql            -- DataView configuration tables
├── 004-add-navigation.sql           -- Navigation configuration tables
├── 005-add-value-sets.sql           -- Value sets and enumerations
├── 006-add-connections.sql          -- Database connection management
├── 007-add-audit-fields.sql         -- Audit data JSON columns
├── 008-add-indexes.sql              -- Performance indexes
└── 009-add-constraints.sql          -- Foreign keys and constraints
```text

### **Applying Migrations**

```powershell
# Execute migrations in order
$migrations = Get-ChildItem "migrations/*.sql" | Sort-Object Name
foreach ($migration in $migrations) {
  sqlcmd -S "64.227.173.43,1433" `
         -U "sa" `
         -P "password" `
         -d "RADICS_CFG" `
         -i $migration.FullName `
         -V 16
}

# Verify migration
Invoke-Sqlcmd -ServerInstance "64.227.173.43,1433" `
              -Database "RADICS_CFG" `
              -Query "SELECT COUNT(*) as tables FROM INFORMATION_SCHEMA.TABLES"
```text

### **Rollback Procedures**

```sql
-- Generate rollback script (schema only, not data)
DECLARE @db NVARCHAR(128) = 'RADICS_CFG'
SELECT 'DROP TABLE ' + TABLE_NAME + ';' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'dbo' 
ORDER BY TABLE_NAME DESC  -- Drop in reverse creation order
```text

---

## Summary: Production Database Schema

**RADICS_CFG (MSSQL 2019+)** - Configuration Database

| Component | Details |
|-----------|---------|
| **Databases** | 1 config DB (RADICS_CFG) + N tenant databases |
| **Tables** | 31 registered objects (29 TABLEs, 1 VIEW, 1 FUNCTION) |
| **Core Tables** | app_object_registries, app_object_profiles, app_tenants, app_users, app_roles, app_page_configs, app_workflow_configs, app_dataview_configs, app_navigation_configs, app_connections, app_value_sets |
| **Access Profiles** | 37 system profiles with modes: FULL, READ, WRITE, EXECUTE |
| **Isolation** | Logical multi-tenancy via tenant_id column (NULL = system-wide) |
| **Authentication** | Object registry defines all entities; profiles control field-level access |
| **Audit Trail** | JSON audit_data in every configuration table |
| **Soft Delete** | is_deleted BIT flag on all tables (never hard delete configs) |
| **Encryption** | Password hashes encrypted; support for field-level encryption via profiles |
| **Backups** | Daily full + hourly incremental + 15-min transaction logs (30-day retention) |
| **Caching** | Redis with 5-24 hour TTL per entity type; cache key: {type}:{tenantId}:{code} |

---

## Related Documentation

- [Object Management Guide](../configuration/02-object-management.mdx) - Entity registry and profile configuration
- [Page Design](../configuration/03-page-configuration.mdx) - Form, dashboard, report definitions
- [Middleware Architecture](./middleware.mdx) - Data access layer and service implementation
- [Frontend Architecture](./frontend.mdx) - React client consuming database configurations
- [API Reference](../api/overview.md) - REST endpoints for configuration management